Hi all
I have two SQl Server 2000 in two different location, at one point I wand to Transfer the information in one table (in Sql server 1) to the same table (in SQl server 2) using Stored Procedure.
Can any one through some input on how to write the SP?
Regards
Hi,
I need to move my database servers from one location to another. The issue is that I have over 200 databases to move and my clients can't afford a downtime. The collective volume of all the databases is over 2.5 TB and growing.
I am thinking to copy these databases in batches over the WAN to the new location and replicate them using Transactional replication till I have all the databases moved and synchronized.
Will it be wise enough to use replication for synchronizing 200 databases or is there a better approach which I can use to move these databases with minimum downtime and compromise on performance of applications.
Note: Migration is from SQL Server 2000 to SQL Server 2005.
Thanks in advance.
Ritesh
I have a master database containing data from many client databases located in various locations.
I have to synchronize data between the master & client databases. If data in client changes, the master needs to be updated. If the data in Master changes, the client needs to be updated.
Because of the security issue, the client location will initiate this process of Synchronization. It has to be automatic process scheduled once everyday. The Client databases are SQL-Express Databases and the Master is Sql Server 2000 database.
What is the industry standard or the best way to achieve this task ? Are their any tools that easily accomplish this? Can SQl Server Replication achieve this ?
Thanks
i am using C#..net
I am synchronizing the sql server data with sqlce.but i get an
error when i call synchronize function. i am using sqlcereplication
class.
the error is
"a call to sql server reconciler failed"
native error 29006
and sometimes
native error
29045.
Hi,
View 5 Replies View RelatedI have one Informix in OpenVMS machine and another MS SQL in Windows machine. I cannot touch the Informix server at all.
How to do the extract/update the data periodically from Informix to SQL? I was thinking of writing a service. But I don't know how to do the incremental update.
I was thinking of doing a pull subscription from MS SQL. Is it feasible? Is it a replication or integration service? I dunno. Please help.
Thank you.
Hi all,
I'm implementing Business Custom Logic Conflict Resolver. My confict resolver works just fine. But I have the following problem.
When UpdateConflictsHandler executes I receive as parameters only publisherDataSet and subscriberDataSet. In this datasets I receive actual data toward the current moment. But for my custom logic I need also the data from my last synchronization. How can I get it?
Here is why I need it f anyone is interested.
let's assume that we have the following row in my last synch.
aaa 12.10 DBNull.Value DBNull.Value 12451
after 5 days we start new sync. Just before the sync started we have follwoing data:
on Publisher (P)
bbb 16.10 DBNull.Value YYY (value) DBNull.Value
on Subscriber (S)
ccc 14.10 ZZZ(value) DBNull(value) 12451
It's obvious we have conflicts in col.1 and col.2 and I want to win the latest change. So in my customDataSet change my new row will be
bbb 16.10
For rest columns there are no conflict and I want to get those values that are not null except if they are not supposed to be null. Supposed to be null is illustrated in col.5. We can see that there is no change on S compared to value after last sync while on P value was deleted. So it's supposed to be DBNULL.Value
Someone can advice me just to take values from P. For my logic this is not good because in col.3 for example I want ZZZ (value) in my custom row.
So as I can see the only possible way for me to decide which value is good for my custom row is to have data after last sync.
Introduction: Data Synchronization in SQL Express
Data synchronization feature is available only in the SQL Server. In order to mimic that feature in SQL Express, currently we are using TableDiff utility method. This document proposes a new Stored Procedure method to speedup the entire data synchronization process as well as overcome some issues found in TableDiff utility method.
Data Synchronization using TableDiff utility method:
We can use TableDiff utility to generate a Transact-SQL script (containing delete/insert/update statements) to fix discrepancies at the destination server to bring the source and destination tables into convergence. Since this utility compare one table at a time, we need to call it in a loop in case we have N number of tables to synchronize. Within the loop, we have to accumulate (i.e., append) the generated Transact-SQL script into a local file (say CompleteFixSQL.sql). At end of the loop, we have a complete script file that need to be executed at destination server. sqlcmd utility can be used to run that script file (CompleteFixSQL.sql) into the destination server to bring the source and destination tables into convergence.
However, there are some drawbacks using these utilities
•Both TableDiff & sqlcmd utilities are external application need to be called from client application code. TableDiff need to be called N times if we have N number of tables to be synchronized which incurs I/O overhead.
•Sqlcmd utility executes statements that are contained in the CompleteFixSQL script one by one in a sequence manner which is a time consuming process in case we have large data to be synchronized at destination.
•TableDiff utility has some limitations. It would not generate FIX script file for LOB datatypes such as text, ntext & image.
Data Synchronization using Stored Procedure:
A Stored Procedure (SP) can be used to compare the difference between source and destination tables and then synchronize the destination tables with source table data. To bring the source and destination tables into convergence,
1.Find the records that need to be deleted from Destination database table
2.Find the records that need to be inserted into Destination database table
3.Find the records that need to be updated in the Destination database table
Subsequently, we have to execute delete, insert and update statements in the destination database for the records that are found in the above steps 1, 2 & 3 respectively.
The advantages of this SP method over TableDiff method are
•The stored procedure is already compiled and stored within the destination database. Client application code is just need to call this SP using the connection string
•Both table Compare & Synchronization will be done at a single query(one per each delete, insert & update)
•Records are processed (deleted, inserted & updated) in bulk manner.
•LOB datatypes are supported
Step 1: Records to be deleted from Destination database table
•Select the records that does not exist in Source database table, but exist in the Destination database table
•Then delete them from the Destination database table
delete from
DestinationDB.dbo.TableName DestinationDBTable
where
not exists
(select
1
from
SourceDB.dbo.TableName SourceDBTable
where
SourceDBTable.PrimaryColumnName1 = DestinationDBTable.PrimaryColumnName1 and
SourceDBTable.PrimaryColumnName2 = DestinationDBTable.PrimaryColumnName2 and
...
SourceDBTable.PrimaryColumnNameN = DestinationDBTable.PrimaryColumnNameN
)
If the table contains an Identity column then we can simply use that column rather than primary key column in the join condition of the WHERE clause. This will reduce the size of the join condition especially when the table having composite primary keys and an Identity column. Also it will improve the performance of the delete statement
delete from
DestinationDB.dbo.TableName DestinationDBTable
where
not exists
(select
1
from
SourceDB.dbo.TableName SourceDBTable
where
SourceDBTable.IdentityColumn = DestinationDBTable.IdentityColumn
)
Step 2: Records to be inserted into Destination database table
•Select the records that are exist in the Source database table, but does not exist in the Destination database table
•Then insert them into the Destination database table
insert into
DestinationDB.dbo.TableName DestinationDBTable
(ColumnList)
select
SourceDBTable.Columnlist
from
SourceDB.dbo.TableName SourceDBTable
where
not exists
(select
1
from
DestinationDB.dbo.TableName DestinationDBTable
where
DestinationDBTable.PrimaryColumnName1 = SourceDBTable.PrimaryColumnName1 and
DestinationDBTable.PrimaryColumnName2 = SourceDBTable.PrimaryColumnName2 and
...
DestinationDBTable.PrimaryColumnNameN = SourceDBTable.PrimaryColumnNameN
)
Column with TimeStamp datatype should be excluded from the Column list of the above insert statement as we cannot explicitly set values for TimeStamp column.
As specified in the Step 1 we can use identity column rather than primary key column as follows
insert into
DestinationDB.dbo.TableName DestinationDBTable
(ColumnList)
select
SourceDBTable.Columnlist
from
SourceDB.dbo.TableName SourceDBTable
where
not exists
(select
1
from
DestinationDB.dbo.TableName DestinationDBTable
where
DestinationDBTable.IdentityColumn = SourceDBTable. IdentityColumn
)
If the table having identity column then the above insert statement must be enclosed by the “set identity_insert on/off� as follows
set identity_insert TableName On
... above insert statement
set identity_insert TableName off
Step 3.Records to be updated in the Destination database table
•Select the records that are differ from Source & Destination database table
•Then update them in the Destination database table with the source database table data
update
TableName
set
ColumnName1 = SourceDBTable.ColumnName1,
ColumnName2 = SourceDBTable.ColumnName2,
...
ColumnNameN = SourceDBTable.ColumnNameN
from
DestinationDB.dbo.TableName DestinationDBTable,
(
select
max(TableName) as TableName, columnlist
from
(
select
'SourceTableName' as TableName, columnlist
from
SourceTableName
union all
select
'DestinationiTableName' as TableName, columnlist
from
DestinationTableName
) AliasName
group by
columnlist
having
count(*) = 1
and max(TableName) = 'SourceTableName'
) SourceDBTable
where
SourceDBTable.PrimaryColumnName1 = DestinationDBTable.PrimaryColumnName1 and
SourceDBTable.PrimaryColumnName2 = DestinationDBTable.PrimaryColumnName2 and
...
SourceDBTable.PrimaryColumnNameN = DestinationDBTable.PrimaryColumnNameN
Column with TimeStamp datatype should be excluded from the SET clause of the above update statement as we cannot explicitly set values for TimeStamp column.
Column with LOB datatypes (Text,nText & Image) should be converted to respective Large Value datatypes[Varchar(max),nVarchar(max) & varbinary(max)] from the queries that are combined by UNION ALL of the above statement. This is because the UNION ALL causes the sorting mechanism which prohibits LOB datatypes.
As specified in the Step 1 & 2 we can use identity column rather than primary key column in the WHERE clause of the update statement.
Stored procedure for Data Synchronization:
The below Data Synchronization - Beta Version - Script contains following view and stored procedures (SP) to implement the Data Synchronization
1.v_DTS_ColumnInformation
2.stp_DTS_GetCommaSeperatedColumnString
3.stp_DTS_GetIdentityOrPrimaryKeyColumnDetails
4.stp_DTS_SetDestinationColumnWithSourceColumnString
5.stp_DTS_DataSynchronization
v_DTS_ColumnInformation: This view will be used to populate the column details such as Data Type, Primary Key, Null constraint, Identity property, Column size constraint (Length for character datatype, Precision and scale of number datatype)
stp_DTS_GetCommaSeperatedColumnString: This SP generates various strings comma separated column string for a given table
stp_DTS_GetIdentityOrPrimaryKeyColumnDetails: This SP generates various strings for an Identity or Primary key columns of a given table.
stp_DTS_SetDestinationColumnWithSourceColumnString: This SP generates the SET clause for the update statement described in the step 3.
stp_DTS_DataSynchronization: This is the main SP will be used to synchronize the destination tables with the source tables data.
Details of the parameters used in all of the above procedures are described along with the header of each SP.
Assumption:
•Both source and destination tables’ schema are identical.
•Both source and destination data sources are different.
•Destination server has a linked server with source server in case both are remotely connected.
•All the SP’s and view listed above are stored & compiled in the destination database.
•All Foreign key constraints & Trigger (that affects another tables) of the destination tables are disabled before executing stp_DTS_DataSynchronization
•The main SP stp_DTS_DataSynchronization will be executed at destination database.
•User calls the main SP stp_DTS_DataSynchronization with valid parameters.
Limitation:
•Column with Timestamp datatype excluded from the data synchronization
.
Features of future version:
•The Stored Procedure will be extended to include parameter validation.
•It will be extended in such a way to disable the Foreign key constraints & Triggers of the destination tables before starting synchronization. After completion of data synchronization (either success or failure), all Foreign key constraints & Triggers will be enabled back to maintain data integrity.
•It will be extended to provide Log of the data synchronization process. So that user can know, how many records are deleted/inserted/updated and what are they. Hint: The OUTPUT clause can be used to achieve this.
Conclusion:
Data synchronization using Stored Procedure method is faster than the TableDiff method. The genuine feedback from readers will be helpful to achieve a better solution than this proposed method. Together we will meet that goal.
References:
1.The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION (http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx)
2.TableDiff Utility (http://technet.microsoft.com/en-us/library/ms162843.aspx)
Acknowledgment:
My sincere thanks to all the experts who participated and spend their valuable time to discuss the technique of Table Comparison in the Jeff's SQL Server Blog (http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx).
•Jeff: For his UNION ALL method to compare tables
•Click: For his NOT EXISTS method to compare tables
•David L. Penton: For his explanation of the issues found in NOT EXISTS method
•John: For his powerful coding to generate comma separated list with a single SELECT
Data Synchronization - Beta Version.sql - Script
/**********************************************************************
' FILE NAME : v_DTS_ColumnInformation.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This view populates column details
' This view used by most of the stp_DTS.xxx stored procedures
' COLUMN : 1. Table Name
' 2. Column Name
' 3. Data Type
' 4. A flag indicates whether the column is part of the Primary Key or not
' 5. A flag indicates whether the column is nullable or not
' 6. A flag indicates whether the table has identity column or not
' 7. Length of character data type column
' 8. Precision of numeric data type column
' 9. Sclae of numeric data type column
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Retrieve column information
select * from vColumnInformation
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_DTS_ColumnInformation]') )
drop view [dbo].[v_DTS_ColumnInformation]
GO
create view v_DTS_ColumnInformation
as
select
TableName = isc.table_name
,ColumnName = isc.column_name
,DataType = isc.data_type
,IsPrimaryKey = case when iskcu.ordinal_position is null then 0 else 1 end
,IsNullable = sc.isnullable
,IsIdentity = case when sc.status =128 then 1 else 0 end
,CharacterMaximumLength = isc.Character_Maximum_Length
,NumericPrecision = isc.Numeric_Precision
,NumericScale = isc.Numeric_Scale
from
information_schema.columns isc
left outer join information_schema.key_column_usage iskcu
on isc.table_name = iskcu.table_name and isc.column_name = iskcu.column_name
inner joinsysobjects so
on isc.table_name = so.name
inner join syscolumns sc
on so.id = sc.id and isc.column_name = sc.name
go
/**********************************************************************
' FILE NAME : stp_DTS_GetCommaSeperatedColumnString.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure generates the comma seperated
' column string for a given table
' This SP called from stp_DTS_DataSynchronization
' PARAMTERS : 1. Table Name (Input)
' 2. Column String (Output)
' 3. Column String without TimeStamp datatype column(Output)
' 4. Column String With casting LOB to Large Value Datatype (Output)
' 5. Column String With casting Large Value to LOB Datatype(Output)
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Execute stp_DTS_GetCommaSeperatedColumnString procedure
declare @v_ColumnString varchar(max), @v_ColumnStringWithoutTimeStampDataType varchar(max) , @v_ColumnStringWithCastingLOBToLargeValueDataType varchar(max), @v_ColumnStringWithCastingLargeValueToLOBDataType varchar(max)
exec dbo.stp_DTS_GetCommaSeperatedColumnString 'TableName',@v_ColumnString out, @v_ColumnStringWithoutTimeStampDataType out, @v_ColumnStringWithCastingLOBToLargeValueDataType out, @v_ColumnStringWithCastingLargeValueToLOBDataType out
select
ColumnString = @v_ColumnString
,ColumnStringWithoutTimeStampDataType = @v_ColumnStringWithoutTimeStampDataType
,ColumnStringWithCastingLOBToLargeValueDataType = @v_ColumnStringWithCastingLOBToLargeValueDataType
,ColumnStringWithCastingLargeValueToLOBDataType = @v_ColumnStringWithCastingLargeValueToLOBDataType
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].stp_DTS_GetCommaSeperatedColumnString') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_GetCommaSeperatedColumnString]
GO
create procedure stp_DTS_GetCommaSeperatedColumnString
(@p_TableName varchar(254)
,@p_ColumnString varchar(max) out
,@p_ColumnStringWithoutTimeStampDataType varchar(max) out
,@p_ColumnStringWithCastingLOBToLargeValueDataType varchar(max) out
,@p_ColumnStringWithCastingLargeValueToLOBDataType varchar(max) out
)
as
begin
set nocount on
select
@p_ColumnString = ''
,@p_ColumnStringWithCastingLOBToLargeValueDataType = ''
,@p_ColumnStringWithCastingLargeValueToLOBDataType = ''
,@p_ColumnStringWithoutTimeStampDataType = ''
select
@p_ColumnString = @p_ColumnString +
case
when len(@p_ColumnString)>0 then ', '
else ''
end +
'[' + ColumnName + ']'
,@p_ColumnStringWithoutTimeStampDataType = @p_ColumnStringWithoutTimeStampDatatype +
case
when len(@p_ColumnStringWithoutTimeStampDatatype)>0 and DataType != 'timestamp' then ', '
else ''
end +
case
when DataType = 'timestamp' then ''
else '[' + ColumnName + ']'
end
,@p_ColumnStringWithCastingLOBToLargeValueDataType = @p_ColumnStringWithCastingLOBToLargeValueDataType +
case
when len(@p_ColumnStringWithCastingLOBToLargeValueDataType)>0 then ', '
else ''
end +
case
when DataType = 'image' then 'convert(varbinary(max),[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'text' then 'convert(varchar(max),[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType in ('ntext','xml') then 'convert(nvarchar(max),[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
else '[' + ColumnName + ']'
end
,@p_ColumnStringWithCastingLargeValueToLOBDataType = @p_ColumnStringWithCastingLargeValueToLOBDataType +
case
when len(@p_ColumnStringWithCastingLargeValueToLOBDataType)>0 then ', '
else ''
end +
case
when DataType = 'image' then 'convert(image,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'text' then 'convert(text,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'ntext' then 'convert(ntext,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
when DataType = 'xml' then 'convert(xml,[' + ColumnName + ']) as ' + '[' + ColumnName + ']'
else '[' + ColumnName + ']'
end
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
end
go
/**********************************************************************
' FILE NAME : stp_DTS_GetIdentityOrPrimaryKeyColumnDetails.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure generates the various string
' for an Identity or Primary Key column for a given table
' This SP called from stp_DTS_DataSynchronization
' PARAMTERS : 1. Table Name (Input)
' 2. Equi Join String (Output)
' 3. Column Structure String for the temporary table (Output)
' 4. Comma seperated column strinig for DELETED table (Output)
' 5. Comma seperated column strinig for INSERTED table (Output)
' 6. Comma seperated column strinig for temporary table that hold detailed log (Output)
' 7. Comma seperated column string of Identity or Priamry key column (Output)
' 8. A flag indicates whether the table contains identity column or not (Output)
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Executes stp_DTS_GetIdentityOrPrimaryKeyColumnDetails procedure
declare
@p_EquiJoinString varchar(max),@p_StructureString varchar(max),@p_DeletedString varchar(max),@p_InsertedString varchar(max),@p_TmpTableColumnListString varchar(max),@p_IdentityOrPrimaryKeyColumnString varchar(max),@p_IsIdentity bit
exec stp_DTS_GetIdentityOrPrimaryKeyColumnDetails 'TableName',@p_EquiJoinString OUT, @p_StructureString OUT, @p_DeletedString OUT, @p_InsertedString OUT, @p_TmpTableColumnListString OUT,@p_IdentityOrPrimaryKeyColumnString out, @p_IsIdentity OUT
select
EquiJoinString = @p_EquiJoinString
,StructureString = @p_StructureString
,DeletedString = @p_DeletedString
,InsertedString = @p_InsertedString
,TmpTableColumnListString = @p_TmpTableColumnListString
,IdentityOrPrimaryKeyColumnString = @p_IdentityOrPrimaryKeyColumnString
,IsIdentity = @p_IsIdentity
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_DTS_GetIdentityOrPrimaryKeyColumnDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_GetIdentityOrPrimaryKeyColumnDetails]
GO
create procedure [dbo].[stp_DTS_GetIdentityOrPrimaryKeyColumnDetails]
(@p_TableName varchar(254)
,@p_EquiJoinString varchar(max) out
,@p_StructureString varchar(max) out
,@p_DeletedString varchar(max) out
,@p_InsertedString varchar(max) out
,@p_TmpTableColumnListString varchar(max) out
,@p_IdentityOrPrimaryKeyColumnString varchar(max) out
,@p_IsIdentity bit out
)
as
begin
set nocount on
select
@p_EquiJoinString = ''
,@p_StructureString = ''
,@p_DeletedString = ''
,@p_InsertedString = ''
,@p_TmpTableColumnListString = ''
,@p_IdentityOrPrimaryKeyColumnString = ''
,@p_IsIdentity=1
-- Construct the EquiJoinString using identity column if the table contains Identity column
select
@p_EquiJoinString = @p_EquiJoinString +
case
when len(@p_EquiJoinString)>0 then ' and '
else ''
end +
@p_TableName + '.[' + ColumnName + '] = SrcDBTable.[' + ColumnName + ']'
,@p_StructureString = @p_StructureString +
case
when len(@p_StructureString)>0 then ', '
else ''
end +
'[' + ColumnName + ']' + ' ' +
CASE DataType
WHEN 'char' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varchar' THEN Datatype + '('+convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nvarchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'decimal' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'numeric' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'float' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ')'
WHEN 'binary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varbinary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
else
DataType
END
,@p_DeletedString = @p_DeletedString +
case
when len(@p_DeletedString)>0 then ', '
else ''
end +
'deleted.[' + ColumnName + ']'
,@p_InsertedString = @p_InsertedString +
case
when len(@p_InsertedString)>0 then ', '
else ''
end +
'inserted.[' + ColumnName + ']'
,@p_TmpTableColumnListString = @p_TmpTableColumnListString +
case
when len(@p_TmpTableColumnListString)>0 then '+'',''+'
else ''
end +
'convert(varchar(max),[' + ColumnName + '])'
,@p_IdentityOrPrimaryKeyColumnString = @p_IdentityOrPrimaryKeyColumnString +
case
when len(@p_IdentityOrPrimaryKeyColumnString)>0 then ', '
else ''
end +
'[' + ColumnName + ']'
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
and IsIdentity = 1 -- Include only Identity column
-- Construct the EquiJoinString using primary column if the table does not contains Identity column
if @p_EquiJoinString = ''
begin
set @p_IsIdentity = 0
select
@p_EquiJoinString = @p_EquiJoinString +
case
when len(@p_EquiJoinString)>0 then ' and '
else ''
end +
@p_TableName + '.[' + ColumnName + '] = SrcDBTable.[' + ColumnName + ']'
,@p_StructureString = @p_StructureString +
case
when len(@p_StructureString)>0 then ', '
else ''
end +
'[' + ColumnName + ']' + ' ' +
CASE DataType
WHEN 'char' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varchar' THEN Datatype + '('+convert(varchar, CharacterMaximumLength) + ')'
WHEN 'nvarchar' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'decimal' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'numeric' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ',' +
convert(varchar, NumericScale) + ')'
WHEN 'float' THEN Datatype +'('+ convert(varchar, NumericPrecision) + ')'
WHEN 'binary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
WHEN 'varbinary' THEN Datatype +'('+ convert(varchar, CharacterMaximumLength) + ')'
else
DataType
END
,@p_DeletedString = @p_DeletedString +
case
when len(@p_DeletedString)>0 then ', '
else ''
end +
'deleted.[' + ColumnName + ']'
,@p_InsertedString = @p_InsertedString +
case
when len(@p_InsertedString)>0 then ', '
else ''
end +
'inserted.[' + ColumnName + ']'
,@p_TmpTableColumnListString = @p_TmpTableColumnListString +
case
when len(@p_TmpTableColumnListString)>0 then '+'',''+'
else ''
end +
'convert(varchar(max),[' + ColumnName + '])'
,@p_IdentityOrPrimaryKeyColumnString = @p_IdentityOrPrimaryKeyColumnString +
case
when len(@p_IdentityOrPrimaryKeyColumnString)>0 then ', '
else ''
end +
'[' + ColumnName + ']'
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
and IsPrimaryKey = 1 -- Include only primary key column
end
end
go
/**********************************************************************
' FILE NAME : stp_DTS_SetDestinationColumnWithSourceColumnString.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure generates the set string
' of an update statement for a given table
' This excludes TimeStamp datatype column
' This SP called from stp_DTS_DataSynchronization
' PARAMTERS : 1. Table Name (Input)
' 2. Set String (Output)
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Execute stp_DTS_SetDestinationColumnWithSourceColumnString procedure
declare @v_SetString varchar(max)
exec stp_DTS_SetDestinationColumnWithSourceColumnString 'TableName', @v_SetString out
select @v_SetString
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_DTS_SetDestinationColumnWithSourceColumnString]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_SetDestinationColumnWithSourceColumnString]
GO
create procedure stp_DTS_SetDestinationColumnWithSourceColumnString
(@p_TableName varchar(254)
,@p_SetString varchar(max) out
)
as
begin
set nocount on
set @p_SetString = ''
select
@p_SetString = @p_SetString +
case
when len(@p_SetString)>0 then ', '
else ''
end +
'[' + ColumnName + '] = SrcDBTable.[' + ColumnName + ']'
from
v_DTS_ColumnInformation
where
TableName = @p_TableName
and IsPrimaryKey = 0 -- Include only non key column
and IsIdentity = 0 -- Include only non identity column
and DataType != 'timestamp' -- Exclude TimeStamp data type column
end
go
/**********************************************************************
' FILE NAME : stp_DTS_DataSynchronization.sql
' VERSION : Beta version
' CREATED DATE : 05-Jan-2008
' WRITTEN BY : Ganesan Krishnan
' DESCRIPTION : This stored procedure synchronize the destination tables
' data with the Source tables data
' This SP should be executed at Destination database
' PARAMTERS : 1. Linked Server Name of the Source Database (Input)
' 2. Source Database Name (Input)
' 3. Comma seperated List of Tables to be synchronized (Input)
' 4. Program Mode (Input)
' -1 -> Table Compare only
'0 -> Table Compare & Data Synchronization
'1 -> Data Synchronization Only
'*********************************************************************
' Modification History
'*********************************************************************/
/*
-- Execute Data Synchronization procedure at destination database to sync Destination table with Source table data
-- Table Compare Only
-- exec [DestinationServerInstance].DestinationDB.dbo.stp_DTS_DataSynchronization 'SourceServerInstance','SourceDBName','TableName1,TableName2,TableName3',-1
-- Table Compare & Data Synchronization
-- exec [DestinationServerInstance].DestinationDB.dbo.stp_DTS_DataSynchronization 'SourceServerInstance','SourceDBName','TableName1,TableName2,TableName3',0
-- Data Synchronization Only
-- exec [DestinationServerInstance].DestinationDB.dbo.stp_DTS_DataSynchronization 'SourceServerInstance','SourceDBName','TableName1,TableName2,TableName3',1
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[stp_DTS_DataSynchronization]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[stp_DTS_DataSynchronization]
GO
create procedure stp_DTS_DataSynchronization
(
@p_LinkedServerNameofSourceDatabase varchar(254)
,@p_SourceDatabaseName varchar(254)
,@p_TablesToBeSynchronized varchar(max)
,@p_ProgramMode int = 1
)
as
begin
set nocount on
declare
@v_TableName varchar(254)
,@i_CommaPosition int
,@i_StringLength int
,@i_StartPosition int
,@i_EndPosition int
,@v_sql nvarchar(max)
,@v_ColumnList varchar(max)
,@v_ColumnlistWithoutTimeStampDataType varchar(max)
,@v_ColumnStringWithCastingLOBToLargeValueDataType varchar(max)
,@v_ColumnStringWithCastingLargeValueToLOBDataType varchar(max)
,@v_EquiJoinString varchar(max)
,@v_StructureString varchar(max)
,@v_DeletedString varchar(max)
,@v_InsertedString varchar(max)
,@v_TmpTableColumnListString varchar(max)
,@v_IdentityOrPrimaryKeyColumnString varchar(max)
,@v_SetDestinationColumnWithSourceColumnString varchar(max)
,@b_IsTableHavingIdentityColumn bit
,@i_errorcode int
,@v_ErrorMessage varchar(400)
,@i_return int
,@v_newline varchar(1)
,@v_DetailedLog varchar(max)
,@i_DeletedRowCount int
,@i_InsertedRowCount int
,@i_UpdatedRowCount int
,@i_TotalDeletedRowCount int
,@i_TotalInsertedRowCount int
,@i_TotalUpdatedRowCount int
,@i_TotalRowsSynchronized int
,@v_Line varchar(80)
,@v_Line1 varchar(80)
,@i_LogColumnWidth int
,@d_StartDate datetime
,@d_EndDate datetime
,@v_FullyQualifiedSourceTableName varchar(254)
,@v_FullyQualifiedDestinationTableName varchar(254)
,@v_LinkedServerNameofDestinationDatabase varchar(254)
,@v_DestinationDatabaseName varchar(254)
,@v_FullyQualifiedSourceDatabaseName varchar(254)
,@v_FullyQualifiedDestinationDatabaseName varchar(254)
,@b_SynchronizationTransactionStarted bit
-- Initialize local variables
select
@i_errorcode = 0
,@i_return = 0
,@v_newline = char(10)
,@i_TotalDeletedRowCount = 0
,@i_TotalInsertedRowCount = 0
,@i_TotalUpdatedRowCount = 0
,@i_TotalRowsSynchronized = 0
,@v_Line = replicate('*',80)
,@v_Line1 = replicate('-',32)
,@i_LogColumnWidth = 30
,@d_StartDate = getdate()
,@v_LinkedServerNameofDestinationDatabase = @@servername
,@v_DestinationDatabaseName = db_name()
,@b_SynchronizationTransactionStarted = 0
-- Derive the fully qualified name of the Database
select
@v_FullyQualifiedSourceDatabaseName = '[' + @p_LinkedServerNameofSourceDatabase + '].['+ @p_SourceDatabaseName + ']' + '.dbo.'
,@v_FullyQualifiedDestinationDatabaseName = '[' + @v_LinkedServerNameofDestinationDatabase + '].['+ @v_DestinationDatabaseName + ']' + '.dbo.'
-- start synchronization transaction only if the program mode is "Table Compare & Data Synchronization" or "Data Synchronization only"
if @p_ProgramMode >= 0
begin
-- Start Synchronization transaction
begin transaction Synchronization
set @b_SynchronizationTransactionStarted = 1
end
-- Loop through all the TableName from the 'Comma seperated List of Tables to be synchronized' input parameter
while 1=1
begin
select
@i_CommaPosition = patindex('%,%',@p_TablesToBeSynchronized)
,@i_StringLength = len(@p_TablesToBeSynchronized)
,@i_StartPosition = 1
select
@i_endPosition = case when @i_CommaPosition = 0 then @i_StringLength else @i_CommaPosition - 1 end
-- Derive the Table Name to be synchronized
select
@v_TableName = substring(@p_TablesToBeSynchronized,@i_StartPosition,@i_endPosition)
if @v_TableName is not null
begin
-- Derive the column list
exec stp_DTS_GetCommaSeperatedColumnString
@v_TableName
,@v_ColumnList out
,@v_ColumnlistWithoutTimeStampDataType out
,@v_ColumnStringWithCastingLOBToLargeValueDataType out
,@v_ColumnStringWithCastingLargeValueToLOBDataType out
-- Perform Table Compare
if @p_ProgramMode <= 0
begin
select
@v_FullyQualifiedSourceTableName = @v_FullyQualifiedSourceDatabaseName + @v_TableName
,@v_FullyQualifiedDestinationTableName = @v_FullyQualifiedDestinationDatabaseName + @v_TableName
set @v_sql = 'select ''' + @v_FullyQualifiedSourceTableName + ''' as TableName, ' + @v_ColumnStringWithCastingLOBToLargeValueDataType +
' from ' + @v_FullyQualifiedSourceTableName + ' union all select ''' + @v_FullyQualifiedDestinationTableName + ''' as TableName, ' +
@v_ColumnStringWithCastingLOBToLargeValueDataType + ' from ' + @v_FullyQualifiedDestinationTableName
set @v_sql = 'select max(TableName) as [TableName : ' + @v_TableName + '], ' + @v_ColumnStringWithCastingLargeValueToLOBDataType +
' from (' + @v_sql + ') a group by ' + @v_ColumnList +
' having count(*) = 1'
exec sp_executesql @v_sql
-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler
-- Jump to next iteration if the program mode is Table compare only
if @p_ProgramMode < 0
goto NextIteration
end
-- Populate the Identity Or Primary Key Column Details
exec stp_DTS_GetIdentityOrPrimaryKeyColumnDetails
@v_TableName
,@v_EquiJoinString OUT
,@v_StructureString OUT
,@v_DeletedString OUT
,@v_InsertedString OUT
,@v_TmpTableColumnListString OUT
,@v_IdentityOrPrimaryKeyColumnString out
,@b_IsTableHavingIdentityColumn out
-- Step 1.Records to be deleted from Destination database table
--Select the records that does not exist in Source database table, but exist in the Destination database table
--Then delete them from the Destination database table
-- Start constructing dynamic sql required for Step 1.
set @v_sql ='delete from' + ' '
+ @v_TableName + ' '
+ 'where not exists' + ' '
+ '(select 1 from' + ' '
+ @v_FullyQualifiedSourceDatabaseName + @v_TableName + ' '
+ 'as SrcDBTable' + ' '
+ 'where' + ' '
+ @v_EquiJoinString + ' '
+ ')'
-- Execute the constructed dynamic sql for Step 1
exec sp_executesql @v_sql
-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler
-- Step 2.Records to be inserted into Destination database table
--Select the records that are exist in the Source database table, but does not exist in the Destination database table
--Then insert them into the Destination database table
-- Start constructing dynamic sql required for Step 2.
-- 'set identity_insert on' before the insert statement if the table has an Identity column
if @b_IsTableHavingIdentityColumn = 1
set @v_sql = 'set identity_insert' + ' '
+ @v_TableName + ' ' +
+ 'on'
else
set @v_sql = ''
set @v_sql =@v_sql + ' '
+ 'insert into' + ' '
+ @v_TableName + ' '
+ '(' + @v_ColumnlistWithoutTimeStampDataType + ')' + ' '
+ 'select' + ' '
+ @v_ColumnlistWithoutTimeStampDataType + ' '
+ 'from' + ' '
+ @v_FullyQualifiedSourceDatabaseName + @v_TableName + ' '
+ 'as SrcDBTable' + ' '
+ 'where not exists ' + ' '
+ '(select 1 from' + ' '
+ @v_TableName + ' '
+ 'where' + ' '
+ @v_EquiJoinString + ' '
+ ')'
-- ''set identity_insert off' after the insert statement if the table has an Identity column
if @b_IsTableHavingIdentityColumn = 1
set @v_sql = @v_sql + ' '
+ 'set identity_insert' + ' '
+ @v_TableName + ' ' +
+ 'off'
-- Execute the constructed dynamic sql for Step 2
exec sp_executesql @v_sql
-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler
-- Step 3.Records to be updated in the Destination database table
--Select the records that are differ from Source & Destination database table
--Then update them in the Destination database table with the source database table data
-- Populate the set string
exec stp_DTS_SetDestinationColumnWithSourceColumnString
@v_TableName
,@v_SetDestinationColumnWithSourceColumnString out
-- if the table does not have a non key column then no need to do this step 3.
if isnull(@v_SetDestinationColumnWithSourceColumnString,'') = ''
begin
set @i_UpdatedRowCount = 0
goto NextIteration
end
-- Start constructing dynamic sql required for Step 3.
set @v_sql ='update' + ' '
+ @v_TableName + ' '
+ 'set' + ' '
+ @v_SetDestinationColumnWithSourceColumnString + ' '
+ 'from' + ' '
+ @v_TableName + ' '
+',' + ' '
+ '(select' + ' '
+ 'max(TableName) as TableName,' + ' '
+ @v_ColumnStringWithCastingLargeValueToLOBDataType + ' '
+ 'from' + ' '
+ '(' + ' '
+ 'select' + ' '
+ '''SourceDBTableName'' as TableName,' + ' '
+ @v_ColumnStringWithCastingLOBToLargeValueDataType + ' '
+ 'from' + ' '
+ @v_FullyQualifiedSourceDatabaseName + @v_TableName + ' '
+ 'union all' + ' '
+ 'select' + ' '
+ '''DestinationiTableName'' as TableName,' + ' '
+ @v_ColumnStringWithCastingLOBToLargeValueDataType + ' '
+ 'from' + ' '
+ @v_TableName + ' '
+ ') tmp' + ' '
+ 'group by' + ' '
+ @v_ColumnList + ' '
+ 'having' + ' '
+ 'count(*) = 1 and max(TableName) = ''SourceDBTableName''' + ' '
+ ')' + ' '
+ 'as SrcDBTable' + ' '
+ 'where' + ' '
+ @v_EquiJoinString
-- Execute the constructed dynamic sql for Step 3
exec sp_executesql @v_sql
-- If an error encountered then go to error handler for rollback the transaction
select
@i_errorcode = @@error
if @i_errorcode != 0
goto ErrorHandler
end
-- Derive the next iteration values
-- If no further iternation is required then exit the loop
NextIteration:
if @i_CommaPosition = 0
begin
-- Break the loop if no more tables exist to synchronize
break
end
else
begin
-- Derive the remaining Tables to be synchronized
select
@p_TablesToBeSynchronized = substring(@p_TablesToBeSynchronized, @i_CommaPosition + 1, @i_StringLength)
end
end
-- If the program mode is "Table Compare & Data Synchronization" or "Data Synchronization only"
-- then commit the synchronization transaction and log the remaining
if @p_ProgramMode >= 0
begin
-- Commit the synchronization transaction as no error occured
commit transaction Synchronization
end
-- Return success
return 0
-- Error Handler
ErrorHandler:
begin
-- check for error
if ( @i_errorcode <= 0 )
begin
raiserror 99999 @v_ErrorMessage
select @i_return = -100
end
else if ( @i_errorcode > 0 )
begin
select @i_return = -100
end
-- Rollback the synchronization transaction if it is started and an error occured
if @b_SynchronizationTransactionStarted = 1
rollback transaction Synchronization
-- Return Failure
return @i_return
end
end
go
Hello everybody,
There is some strange behaviour i've recently noticed while watching synchronization progress in Replication Monitor on SQL 2005 Server Standard with merge replication configured. The merge process seems to repeat several times.
This is the initial synchronizaion (reinitalization at the subsciber). Client is using Microsoft.SQLServer.Replication objects from .net framework assemblies.
The synchronization starts normally (status is "Running"). The last message of selected session box shows (among other messages): "Beginning evaluating partial replication filters" then "Finished evaluating partial replication filters" and finally "Merge completed after processing xxx changes... etc." after a few seconds. Status changes to "Completed" and then... the merge process starts again!! "Beginning evaluating partial replication filters" etc. And this repeats about 15-20 times.
And so whole process takes about 15 minutes instead about 45 seconds to complete initial synchronization. The number of changes is "Merge completed after processing ..." never change since the first such message.
Is this some bug in web synchronization or some invalid configuration setting? Why does merge process repeat itself so many times??
Please help, thanks in advance.
Kuba
Hi!
I've the following replication topology:
SrvA (Publisher/Distributor) -> (Transactional Publication/Push Synchronization) -> SrvB (Subscriber, Publisher/Distributor) -> (Merge Publication/Pull Synchronization via HTTPS for remote clients) -> Client (Anonymous Subscriber)
To test synchronization with this tier I do the following:
1. Update data on SrvA
2. Push changes to SrvB (there are transactions that replicated)
3. Run RMO Merge Agent (.NET application) on Client and get messsage "No data needed to be merged", although there were changes made on publisher SrvA and SrvB
I saw the thread where similar problem have been discussed (http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=664153&SiteID=17). The sugestion there was to create subscription using wizard rather then T-SQL script. But all my clients are remote and have only HTTPS connection to the IIS over 443. And in the MSDN there was the script that I used to create pull subscription for web synchronization.
Guys, HELP! I almost gave up - do not see the reason for the case in the documentation, settings and code.
Thank you.
Alexander.
I am creating a mobile application in which i am implementing data synchronization through merge replication method as per the step by step procedure given in the following link:-
http://msdn2.microsoft.com/en-us/library/ms171908.aspx
I am using SQL server Mobile Edition which is included in SQL server 2005 in this application, but the application suggests to use SQL server Compact Edition. I was able to successfully create the publication, but not able to run the sqlcesa30.dll file of the created virtual directory in the internet explorer. Is this problem because of i'm not using SQL server Compact Edition? and also let me know if i use SQL server Mobile Edition, do i need to install its Server Tools? If yes, please give me the link to download the tools needed.
Reply Soon.
I am designing a mobile application.I am having database in sql server 2005 and i want to synchronize this database with sql server ce database on pda.I am using merge replication method for synchronization.Please give step by step procedure for data synchronization using merge replication between sql server 2005 and sql server ce 2.0,also let me know whether sql server enterprise manager is needed or will it work without it,if needed give me the link from where to download it as it is not available in sql server 2005.
Reply soon.
Thanks in advance.
Hello,
I am designing a distributed application where a central SQL Server 2005 database will need to be synchronized with remote SQLExpress databases via a WebService. Data can be edited at the cental db (by our connected applications) or on the local SQLExpress dbs running on the users machines (by this disconnected application).
Now, how can I use the timestamp column to determine the most recent update. The most recent update to me is not the user that most recently invoked the syncing WebService, but the most recent time when the change was made to the data locally vs the change time at the central server. Because a user could make a change on his laptop on Monday but not synchronize it till Friday. I dont want the Mon data to overwrite data of Tues-Fri being overwritten simply based on assumption that a late sync is infact the most recent change.
The initial data will be downloaded to the local SQLExpress db via the WebService so the timestamp data in the table will come along with it.
When a user modifies data in this disconnected SQLExpress db, can that be compared to modifications on the central db using timestamps to determine if the users data being synced is older or newer than the data on the server? I understand timestamps are incremental values, but are they still sensitive to the users timezone since the central server will be a diff timezone then the user.
We have a requirment, we have different databases in different servers, we need to syncronize the data in data bases in different server. created Partitioned on these tables.Â
We tried some options:
1) Snapshot replication
2)CDC (Change Data Capture)Â
What are the best approaches to achieve the same.
Hello,
We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.
In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?
Thanks,
Phil
I changed one data source settings in my SSIS project to use Windows Authentication instead of SQL Authentication. This data source is used to create connection managers in all SSIS packages. Soon after the data source was changed, when I opened any package using the connection manager that is based on this changed data source, I got an alert message box like the one given below showing different connection strings in data source and package connection manager. That's understandable (why can't VS 2005 do this silently or give an option to let this happen silently? that would be nice.).
Here is the problem. Even after both data source and connection manager are synchronized, still the same message box keeps on coming when ever I open any affected package (about 20 packages!). It is very annoying as you can see that old and new connection strings ARE IDENTICAL, still VS 2005 is not able to recognize this. I installed VS2005 SP1 even before I ran into this problem. I asked my friends to open this project and check the behavior on his machine. Same thing. Is this a known bug? How to fix this problem? Thanks for your help.
=====
Message box "Synchronize Connection Strings" (VS 2005 status bar shows "Performing post package load operations"):
This package contains at least one connection which is based on a datasource. The connection string for conenctions and data sources listed below are currently not identical. Connection strings of connections will be updated to reflect those on the datasource.
Connection: SqlServer.NET.HBI_DW.hbiuser
Data source: SqlServer.NET.HBI_DW.hbiuser
Old Connection String: Data Source=SDAL1060DB;Initial Catalog=HBI_DW;Integrated Security=True;
New Connection String: Data Source=SDAL1060DB;Initial Catalog=HBI_DW;Integrated Security=True;
=====
I got this situation where my network admin observerd that there is a high network utilization between 2 nodes in our AG (the primary node & the DR site, 2 separate locations of course); then he advised to compress the data transfer between those 2 nodes as the previous DBA already did that before!
Ok, I have no clue about this, so decided to google it, got nothing. My backup is already compressed through some third party app (just in case if that matters to the subject).
Hello,
I have a SSIS package with a Data Flow task. This task transfers the data from SQL Server 2000 to a table in SQL Server 2005.
I deployed and tested this package on the Test Server. Then put this package in a job and executed it - Works fine.
On the production server- If I execute the package through DTEXECUI, it works fine. But when I try executing it through a job- the job fails and it gives me following error:
Description: The external metadata column collection is out of synchronization with the data source columns. The "external metadata column "T_FieldName" (82)" needs to be removed from the external metadata column collection....
What I don't understand is, why are there no errors displayed when I execute the package through DTEXECUI.
Can anyone help me to resolve this issue.
Thanks.
hi!
all.
i want to create a web application architechture which having multiple web server.
this all web server having sql server database.
now what i want is to Keep all data in all databases.
means i want to synchronize my all sql servers.
it is also possible ki my one webserver can have multiple clusted database.
hope to hear soon from u people.
Regards
Viky
Dear All,
How to do Sql Server Synchronization.
Say:
Server "A" contains "123" database.
Server "B" contains "123" database.
But changes is made on Server A. I want that every changes made on server A "123" database should reflect on Server B "123" database on real time basis.
whether it is done through replication or any other way, i want practical answer so that i can try it out.
Thanks
I want to realize these steps in .net2005 C# programme:
1) Start Sqlserver2005 publication snapshot agent.
2) View Sqlserver2005 snapshot agent status, if it finished then go on.
3) Start Sqlserver2005 Subscription synchronization agent.
4) View Sqlserver2005 synchronization agent status, if it done then continue.
I tried to find some SP, dll, method or script to realize this funtion, but failed.
I really need help.
A thousand thanks for your patience.
I have two SQL Server Database, one is at my company and one is at my company's customer. There are two related databases and two related tables in the servers. When I make a change in the table at my database, I want these changes to be done at the other server. I mean the related tables should have the same records all-time. I made a search and found that "Linked-Servers" is a good idea in this scenario. I made a linked server and run the following query in the Query Analyzer;insert into SAMPLE_LINKED_SERVER.SAMPLE_DATABASE.dbo.SAMPLE_TABLE(id,name) Select id,name From MY_SAMPLE_TABLE Where id Not In (Select id From SAMPLE_LINKED_SERVER.SAMPLE_DATABASE.dbo.SAMPLE_TABLE)This query works as I wanted. But I want this process to be done automaticaly without user interaction. Should I use Trigger? Do you suggest using Linked Servers?Or is there any other methods to make synchronization between two SQL Server Database?If I cannot find a good method in SQL Server, then I will be forced to make this synchronization by writing .NET code, but that would be difficult I think. Any suggestion and comment is wellcome.Thanks in advance.
View 2 Replies View Relatedhi!all
i want to synchronize 2 sql server database resides at diffrent locations.
i want steps to do synchronization with the help of sql server enterprize manager as well from Sql - Dmo and VB.
:confused:
Thanks all in advance.
We are working on data synchronization application. This application will synchronize data which are stored in flat file on several client machines generated by windows application to central database (Probably My SQL or SQL Server).
This application/agent will continuously monitor all the modifications done by windows application on client machine and then synchronized data with the central database.
Currently we are looking for unidirectional synchronization i.e. from client to server only.
If you have worked on similar kind of project, it would be great help if you share technology/architecture used in your application.
Thanks & Regards,
Jugal Shah
I have a VB app which stores information in an Access 2000 db. The VB apphandles multiple users (it's a logbook type of application, users sharelookup tables but don't share information among themselves - IOW, I couldn'tsee your log entries and you couldn't see mine).We're adding a web interface to the package - basically a scaled down dataentry interface to allow users to add log entries without being at theirworkstations. The web version will be storing data in a SQL Server 2000 db.I need to be able to synchronize data between the two - if a usersedits/adds a log entry in the desktop version, they need to be able tosynchronize the desktop and web (this is done via a button click, no"realtime" updates). Same scenario with the web side, but the web interfacewon't acutally be able to synch with the desktop - the user would have toinitiate the synch from their workstation. We've pretty much worked out thesynch logic path, and we're working on acutally interfacing with the SQLServer.Can this be done reliably using a standard ADO connection between thedesktop and the web site? I would imagine traffic would be somewhatminimal - on average, no more than 100 records would be added or updated.I've done some testing on this, and with 20 - 50 records it seems to workfine both ways.Suggestions of better/more reliable methods would be most appreciated, andthanks for your time
View 5 Replies View RelatedDoes Sql Server Compact Edition 3.5 support RDA synchronization with Sql Server 2000 database?
Hi
I am stuck while doing synchronization between SQL Server 2000 and SQL Server CE 2.0.
getting an Error "Header information is either corrupted or missing."
My Specification is follow :
Windows XP with SP2
Visual Studio 2005 Enterprise edition
SQL Server 2000 (SP3a)
SQL Server CE 2.0
Application is Smart Device application (Pocket PC 2003) using C#.
Microsoft ActiveSync 4.5
IIS 5.1
I followed steps as per given in this URL :
http://msdn2.microsoft.com/en-us/library/ms839425.aspx#sql_serverce_replication_net_topic2I performed eeach steps successfully, My code is as per given below.
private void button1_Click(object sender, EventArgs e)
{
getSyncReady();
}
SqlCeReplication loSqlCeReplication;
private void getSyncReady()
{
try
{
String lsDBFile = "Northwind.sdf";
loSqlCeReplication = new SqlCeReplication();
loSqlCeReplication.InternetUrl = "http://192.168.0.5/Northwind/sscesa20.dll";
loSqlCeReplication.Publisher = "C99";
loSqlCeReplication.PublisherSecurityMode = SecurityType.NTAuthentication;
loSqlCeReplication.Publication = "NorthwindProducts";
loSqlCeReplication.PublisherDatabase = "Northwind";
loSqlCeReplication.Subscriber = Dns.GetHostName();
loSqlCeReplication.SubscriberConnectionString = "Data Source=" + lsDBFile;
if (!System.IO.File.Exists(lsDBFile))
{
MessageBox.Show("The application requires synchronization", "Replicator",
MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
loSqlCeReplication.AddSubscription(AddOption.CreateDatabase);
}
loSqlCeReplication.Synchronize();
}
catch (Exception foException)
{
throw foException;
}
}
I am not getting whats going wrong.
My Firewall is disable and i have not installed any antivirus softwares (including Norton internet Security....in many forums i got this suggestion but i think its not an issue,even though for solving this issue i performed new installation of Windows XP SP-2 )
Hi there,
I need to create a synchoronization with the Server Mobile (displayed in the Pocket PC emulator) once the website (SQL server Database) is updated. Is there any coding available? How is it possible? PLSSSSS HELP ME....
Hello EveryoneWe have a third party tool which maintains its own customdatabase,DB1 on SQL Server 2000 on Server A (Windows 2000). We built aninhouse application which maintains a transactional database, DB2 alsoon Server A derived from DB1. Since the DB1 is frequently updated thesynchronisation between the databases is currently maintained by usingviews in DB2 which pulls in all the required data from across tables inDB1.But we found that the cost of employing views, referencing tablesacross databases has made the inhouse application slower and so as aapparent performance tuning measure we plan to maintain a subset ofdata(for a specific time period) from DB1 in actual tables with sameschema in our transactional database DB2. But since the tables in DB1are frequently updated during the day we are required to thesynchronise the corresponding tables in DB2 with their counterparts inDB1. The only constraint in this synchronization being that the thirdparty custom database,DB1 cannot not be subjected to any changes (likecreation of triggers) as it is maintained by a different group ofpersonnel.I read about Triggers,Maintenance Jobs and Replication but not surewhich would suffice our needs.Are there any recommendations on how thiscould be carried out? Any suggestion would be welcome.Thankschandra
View 1 Replies View RelatedHow can I keep a production SQL server and DR SQL server in sync? I'm not referring to data - I can use SAN replication for that. I'm referring to the many configuration settings and objects that are external to the databases..Server settings (xp_cmdshell, etc.)LoginsServer- and database-level rolesServer- and database-level permissionsObject level permissionsMaintenance plans/SSIS packagesSQL Agent jobsJob schedulesDatabase mail configurationObjects (proxies, alerts, credentials, operators)Linked serversScheduled tasksODBC connections
I realize that many of these items can be scripted out, and periodically run on the DR server; but not all of them can be. And some, although they can be scripted, may be problematic to restore on a DR server, such as SQL Agent jobs. And even with backup scripts, it seems like developing a process to refresh a DR server would be overwhelming given the number of elements and exceptions. I'm not adverse to doing the work to create a completely scripted solution, but was curious if one already exists that handles all or most of the elements in my list.
Hi
I am doing a task for synchronize data between SQL server 2005 and SQL Mobile.
Created a publication in my SQL server 2005
Created a subscription in my device thru coding. The code is below.
SqlCeReplication repl = null;
try
{
repl = new SqlCeReplication();
repl.InternetUrl = @"http://localhost/TestFirst/sqlcesa30.dll";
repl.Publisher = @"zcs6";
repl.PublisherDatabase = @"TestNow72743";
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.PublisherLogin = @"sa";
repl.PublisherPassword = @"Test";
repl.Publication = @"TestFirst";
repl.Subscriber = @"TestNow7274310";
repl.SubscriberConnectionString = @"Data source = 'TestFirst.sdf';";
if (!File.Exists(@"TestFirst.sdf"))
repl.AddSubscription(AddOption.CreateDatabase);
repl.Synchronize();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
repl.Dispose();
}
If I run this code from my device the replication work successfully. Now I am calling synchronize from device.
My question is How can i start synchronization from server side. Is it possible to do that from server side?
I create a distributed database for mobile application. I replicate a table that distribute on mobile device. I follow instruction how to create distributor, publication, replication, web synchronization, and subscriber database. I have done fine for synchronization between mobile database into desktop database (in this case SQL Server 2005 Standard Edition). But the problem is how can setup publication so it can bidirectional, not only from mobile database into desktop database, but also from desktop database into mobile database. So in the mobile database can have same data with desktop database even on mobile database lost some old data.
Its like data exchange between both engine. Desktop and mobile have same data. For filtering I can put filter on the desktop server for replicated table, so don't worry how I split the data.
Thanks a lot.
I am running SQL Server 2000 and SQL Server CE on the handheld device. I have created the publication on the SQL Server.
I can subscribe to the publication successfully on the handheld and it creates the .sdf file just fine.
When I then attempt to synchronize, the process will run for a long time and eventually error out. (The database has a couple large data tables that are used for value lookups)
The error says the connection was broken.
Any suggestions on correcting this?
I am thinking of breaking the replication database into 2. One with the large lookup files and one with the updated tables. I still need to get the large database with the lookup tables onto the handheld though.
Any thoughts, advice or similar experience handling a situation as this would be appreciated.
thanks,
Dan