Database developers often face the task to synchronize users’ data. Currently, to solve this task, there was developed quite a big number of various utilities. In this article, we will review the process of creating the new functionality to synchronize data exclusively with T-SQL.
Let’s split the synchronization process with T-SQL onto the sub-tasks:
- Retrieving the table structure of a master database
- Preparing data stored in the tables of the master database and saving it at the public locations
- Turning off relations and triggers between the tables of a target database for synchronization time
- Synchronization of data between tables
- Turning on relations and triggers between the tables of the target database
To make the process of creating the synchronization functionality comparatively easy to understand, let’s discuss some restrictions:
- Synchronization will be elapsing within one server, syntax will be supported on the level of MS SQL Server 2005 or higher
- The schemes between the synchronized databases are identical
- Only system data types are supported
- It’s possible to synchronize any amount of tables at a time
- The structure of tables is not essential
1) Retrieving the table structure of the master database
The list of tables can be obtained with the following query:
SELECT table_name = '[' + s.name + '].[' + t.name + ']' FROM sys.tables t JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
It’s important to remember that some system views may contain an excessive amount of connections and calls of system functions, that will negatively affect the run-time performance of the query, such as sys.tables:
SELECT * FROM sys.objects$ o LEFT JOIN sys.sysidxstats lob ON lob.id = o.[object_id] AND lob.indid <= 1 LEFT JOIN sys.syssingleobjrefs ds ON ds.depid = o.[object_id] AND ds.class = 8 AND ds.depsubid <= 1 LEFT JOIN sys.syssingleobjrefs rfs ON rfs.depid = o.[object_id] AND rfs.class = 42 AND rfs.depsubid = 0 LEFT JOIN sys.syspalvalues ts ON ts.class = 'LEOP' AND ts.value = o.lock_escalation_option WHERE o.[type] = 'U '
Therefore, you have to use as simple system views as possible. In our case, you can use sys.objects:
SELECT table_name = '[' + s.name + '].[' + t.name + ']' FROM sys.objects o JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U'
To make the query not to return excessive rows, we need to preliminary filter out the tables containing no data:
SELECT table_name = '[' + s.name + '].[' + t.name + ']' FROM ( SELECT o.[object_id] , o.[schema_id] , o.name FROM sys.objects o WHERE o.[type] = 'U ' AND EXISTS( SELECT 1 FROM sys.partitions p WHERE p.[object_id] = o.[object_id] AND p.[rows] > 0 ) ) t JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
The list of columns containing only system data types can be obtained in the following way:
SELECT table_name = '[' + s.name + '].[' + o.name + ']' , column_name = '[' + c.name + ']' FROM sys.columns c JOIN sys.objects o ON o.[object_id] = c.[object_id] JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id WHERE o.[type] = 'U'
Next, to minimize the amount of data that we pass for comparison, it is suggested not to take into account column values, that have default values and can’t be empty. So, the query will look as follows:
SELECT table_name = '[' + s.name + '].[' + o.name + ']' , column_name = CASE WHEN c2.[object_id] IS NULL THEN '[' + c.name + ']' ELSE '[' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')' END FROM sys.columns c JOIN sys.objects o ON o.[object_id] = c.[object_id] JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id] AND b.name IN ('int', 'bit', 'char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar') AND c.is_nullable = 0 WHERE o.[type] = 'U' AND c.is_computed = 0
2) Retrieving the data stored in the tables of the master database and saving it in public locations
First of all, we need some interim buffer, into which we will load data from a master database. The tempdb system database is public for any user database on the server, therefore, we can choose it.
Every time synchronization runs, we will check whether the table exists and create it if needed with the following query:
IF OBJECT_ID('tempdb.dbo.MergeTemp', 'U') IS NULL BEGIN CREATE TABLE tempdb.dbo.MergeTemp ( [object_name] NVARCHAR(300) NOT NULL , create_date DATETIME NOT NULL DEFAULT GETDATE() , data XML NULL , CONSTRAINT [PK_MergeTemp] PRIMARY KEY CLUSTERED ( id ASC, [object_name] ASC ) ON [PRIMARY] ) END
Then, it’s necessary to decide how to store data from the master database. The main difficulty within this task is that tables may have different structures. The most obvious option is to create a temporary table for each synchronized table and upload data into it. However, such option may be not optimal because we will have to execute many DDL commands.
Therefore, we suggest to solve this task by means of XML, that can describe almost any database entity. The disadvantage of the suggested method is that not quite a correct XML data structure may be generated for some data types. You can retrieve a list of such data types with the following query:
SELECT t.name FROM sys.types t WHERE t.user_type_id != t.system_type_id
Let’s write a query to create XML with dynamic structure:
DECLARE @Data NVARCHAR(4000) , @ObjectName NVARCHAR(300) DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR SELECT [object_name] , = ' INSERT INTO tempdb.dbo.MergeTemp([object_name], data) SELECT ''' + quota_object_name + ''', (SELECT ' + object_columns + ' FROM ' + quota_object_name + ' t FOR XML AUTO, ROOT(''' + [object_name] + '''))' FROM ( SELECT [object_name] = s.name + '.' + t.name , quota_object_name = '[' + s.name + '].[' + t.name + ']' , object_columns = STUFF(( SELECT ISNULL(', [' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')', ', [' + c.name + ']') + CASE WHEN b.name = 'varbinary' THEN ' = CAST('''' AS XML).value(''xs:base64Binary(sql:column("'+ c.name +'"))'',''VARCHAR(MAX)'')' ELSE '' END FROM sys.columns c WITH (NOLOCK) JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id] AND b.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar') WHERE t.[object_id] = c.[object_id] AND c.is_computed = 0 ORDER BY c.column_id FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)') , 1, 2, '') FROM ( SELECT o.[object_id] , o.[schema_id] , o.name FROM sys.objects o WITH (NOLOCK) WHERE o.[type] = 'U' AND EXISTS( SELECT 1 FROM sys.partitions p WITH (NOLOCK) WHERE p.[object_id] = o.[object_id] AND p.[rows] > 0 ) AND NOT EXISTS( SELECT 1 FROM sys.columns c WITH (NOLOCK) JOIN sys.types t WITH (NOLOCK) ON c.system_type_id = t.system_type_id WHERE (c.user_type_id != c.system_type_id OR t.name = 'xml') AND c.[object_id] = o.[object_id] ) ) t JOIN sys.schemas s WITH (NOLOCK) ON t.[schema_id] = s.[schema_id] ) data OPEN [tables] FETCH NEXT FROM [tables] INTO @ObjectName, @Data IF (@@FETCH_STATUS = -1) RAISERROR('Have nothing to export...', 16, 1) WHILE @@FETCH_STATUS = 0 BEGIN EXEC sys.sp_executesql @Data FETCH NEXT FROM [tables] INTO @ObjectName, @Data END CLOSE [tables] DEALLOCATE [tables]
Where, the following query will be generated for each table:
INSERT INTO tempdb.dbo.MergeTemp ([object_name], data) SELECT <TABLE name>, ( SELECT <columns list> FROM <TABLE name> t FOR XML AUTO, ROOT('<table name>') )
After such query is executed, an XML with table data will be generated and written into the MergeTemp table.
3) Disabling relations and triggers between the tables of the master database for the time of synchronization
At the next stage, we will insert data into tables, therefore it’s preferably to turn off foreign keys and triggers. If you do not, various errors may appear at the inserting stage.
Here is a script to turn off/on foreign keys for tables:
DECLARE @FKeysOff NVARCHAR(MAX) , @FKeysOn NVARCHAR(MAX) ;WITH fkeys AS ( SELECT DISTINCT o.[object_name] FROM tempdb.dbo.MergeTemp mt JOIN ( SELECT o.[object_id] , [object_name] = '[' + s.name + '].[' + o.name + ']' FROM sys.objects o JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] JOIN sys.foreign_keys fk ON o.[object_id] = fk.parent_object_id WHERE o.[type] = 'U ' ) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT ) SELECT @FKeysOff = ( SELECT 'ALTER TABLE ' + fk.[object_name] + ' NOCHECK CONSTRAINT ALL;' + CHAR(13) FROM fkeys fk FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)') , @FKeysOn = ( SELECT 'ALTER TABLE ' + fk.[object_name] + ' CHECK CONSTRAINT ALL;' + CHAR(13) FROM fkeys fk FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)') EXEC sys.sp_executesql @FKeysOff EXEC sys.sp_executesql @FKeysOn
This query seems cumbrous and it doesn’t take into account more complicated relations between tables. In case when the database contains relatively few tables, the following query (turning off all table triggers and foreign keys in the database) may be used:
EXEC sys.sp_msforeachtable ' ALTER TABLE ? DISABLE TRIGGER ALL ALTER TABLE ? NOCHECK CONSTRAINT ALL '
4) Data synchronization between tables
We have already access to data from the master database stored in XML.
With the following query we can find the unique identifier of the database object we are going to synchronize:
SELECT o.[object_id] , mt.[object_name] FROM tempdb.dbo.MergeTemp mt JOIN ( SELECT o.[object_id] , [object_name] = '[' + s.name + '].[' + o.name + ']' FROM sys.objects o JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U ' ) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT
It’s important to note that the object identifier (object_id) is unique in the database and may be changed when the schema is modified (e.g., adding a new column). That’s why, initially, we were wrote the name of the object instead of its identifier. To synchronize tables, we need to parse data from XML.
As an option, for this, you can use the OPENXML construction:
DECLARE @IDoc INT EXEC sys.sp_xml_preparedocument @IDoc OUTPUT, @XML SELECT [<column name>] FROM OPENXML(@IDoc, ' <table name>/t') WITH ([<column name>] [<column datetype>] EXEC sys.sp_xml_removedocument @IDoc
However, this approach is less effective when parsing significantly large XML structures. Therefore, it was decided to use the construction described below:
Here is an example of a query, that will generate dynamic SQL by XML parsing and synchronization with the target table:
DECLARE @ObjectID INT , @ObjName NVARCHAR(300) , @SQL NVARCHAR(MAX) DECLARE package CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT o.[object_id] , mt.[object_name] FROM tempdb.dbo.MergeTemp mt JOIN ( SELECT o.[object_id] , [object_name] = '[' + s.name + '].[' + o.name + ']' FROM sys.objects o JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U ' ) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT OPEN package FETCH NEXT FROM package INTO @ObjectID, @ObjName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQL = ' DECLARE @XML XML SELECT @XML = ( SELECT data FROM tempdb.dbo.MergeTemp WHERE [object_name] = ''' + @ObjName + ''' ) IF EXISTS( SELECT 1 FROM sys.columns c WITH (NOLOCK) WHERE c.[object_id] = ' + CAST(@ObjectID AS VARCHAR(10)) + ' AND c.is_identity = 1 ) SET IDENTITY_INSERT ' + @ObjName + ' ON; DELETE FROM ' + @ObjName + ' INSERT INTO ' + @ObjName + '(' + STUFF(( SELECT ', [' + c.name + ']' FROM sys.columns c WHERE c.[object_id] = @ObjectID ORDER BY c.column_id FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ') SELECT ' + STUFF(( SELECT ', [' + c.name + '] = ' + CASE WHEN c2.name IS NOT NULL THEN 'ISNULL(' ELSE '' END + 'r.c.value(''@' + c.name + ''', ''' + b.name + CASE WHEN b.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'varbinary') AND c.max_length = -1 THEN '(max)' WHEN b.name IN ('char', 'varchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')' WHEN b.name IN ('nchar', 'nvarchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length / 2 AS VARCHAR(10)) + ')' WHEN b.name = 'decimal' THEN '(' + CAST(c.[precision] AS NVARCHAR(10)) + ',' + CAST(c.[scale] AS VARCHAR(10)) + ')' ELSE '' END + ''')' + CASE WHEN c2.name IS NOT NULL THEN ', ' + c2.[definition] + ')' ELSE '' END FROM sys.columns c JOIN sys.types b ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id AND b.name!= 'xml' LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id] AND b.name IN ('int', 'bit', 'char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar') AND c.is_nullable = 0 WHERE c.[object_id] = @ObjectID AND c.is_computed = 0 ORDER BY c.column_id FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)') , 1, 2, '') + ' FROM @XML.nodes(''' + s.name + '.' + o.name + '/t'') r(c) ' FROM sys.objects o JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] WHERE o.[object_id] = @ObjectID EXEC sys.sp_executesql @SQL FETCH NEXT FROM package INTO @ObjectID, @ObjName END CLOSE package DEALLOCATE package
The following script will build such query for each synchronized table:
DECLARE @XML XML SELECT @XML = ( SELECT data FROM tempdb.dbo.MergeTemp WHERE [object_name] = ' <table name>' ) IF EXISTS( SELECT 1 FROM sys.columns c WHERE c.[object_id] = <table_id> AND c.is_identity = 1 ) SET IDENTITY_INSERT <TABLE name> ON; DELETE FROM <TABLE name> INSERT INTO <TABLE name> (<columns list>) SELECT [<column name>] = r.c.value('@<column name>', '<column datetype>') FROM @XML.nodes(' <table name>/t') r(c)
Synchronization can be performed in three steps:
- Inserting new data
- Updating existing data by a key field
- Deleting non-existing data in the master database
The provided sequence of actions can be organized through separate operators: INSERT/UPDATE/DELETE; or using a MERGE construction that was implemented in MS SQL Server 2008. However, in this case, SQL code will have quite a complicated structure, since we will have to take into account many factors (e.g., insert data in accordance to the order of the relations between tables). In case if the database has a more simple structure, an easier approach can be used.
If all the tables of the database or a specific isolated schema are synchronized, there is a possibility to directly remove all the records from the tables and insert data from the master database. On one hand, such approach can be the most cost-effective one in terms of implementation, if a database with simple logic is synchronized. On the other hand, this way of synchronization may lead to damages in the integrity and validity of the data in the database.
5) Enabling relations and triggers between the tables of the target database
After inserting data into required tables, it’s necessary to enable foreign keys and triggers in order not to damage the cascade integrity and internal business logic. This can be performed as follows:
EXEC sys.sp_msforeachtable ' ALTER TABLE ? ENABLE TRIGGER ALL ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
When executing this command, the server will check whether the inserted data is correct. Therefore, if an error arises on this step, the problem may initially due to damaging of the cascade integrity.
The resulting version of the script:
USE AdventureWorks2008R2 SET NOCOUNT ON; SET XACT_ABORT ON; RAISERROR('Start...', 0, 1) WITH NOWAIT DECLARE @SQL NVARCHAR(MAX) , @Time DATETIME , @Text NVARCHAR(300) , @SessionUID UNIQUEIDENTIFIER SELECT @SQL = '' , @Time = GETDATE() , @SessionUID = NEWID() SELECT @Text = CONVERT(NVARCHAR(25), @Time, 114) + ' Creating temporary table...' RAISERROR(@Text, 0, 1) WITH NOWAIT IF OBJECT_ID('tempdb.dbo.MergeTemp', 'U') IS NULL BEGIN CREATE TABLE tempdb.dbo.MergeTemp ( id UNIQUEIDENTIFIER NOT NULL , [object_name] NVARCHAR(300) NOT NULL , create_date DATETIME NOT NULL DEFAULT GETDATE() , data XML NULL , CONSTRAINT [PK_MergeTemp] PRIMARY KEY CLUSTERED ( id ASC, [object_name] ASC ) ON [PRIMARY] ) END SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Generate SQL queries...' RAISERROR(@Text, 0, 1) WITH NOWAIT DECLARE @Data NVARCHAR(4000) , @ObjectName NVARCHAR(300) DECLARE [tables] CURSOR READ_ONLY FAST_FORWARD LOCAL FOR SELECT [object_name] , = ' INSERT INTO tempdb.dbo.MergeTemp(id, [object_name], data) SELECT ''' + CAST(@SessionUID AS VARCHAR(36)) + ''', ''' + quota_object_name + ''', (SELECT ' + object_columns + ' FROM ' + quota_object_name + ' t FOR XML AUTO, ROOT(''' + [object_name] + '''))' FROM ( SELECT [object_name] = s.name + '.' + t.name , quota_object_name = '[' + s.name + '].[' + t.name + ']' , object_columns = STUFF(( SELECT ISNULL(', [' + c.name + '] = NULLIF([' + c.name + '], ' + c2.[definition] + ')', ', [' + c.name + ']') + CASE WHEN b.name = 'varbinary' THEN ' = CAST('''' AS XML).value(''xs:base64Binary(sql:column("'+ c.name +'"))'',''VARCHAR(MAX)'')' ELSE '' END FROM sys.columns c WITH (NOLOCK) JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id LEFT JOIN sys.default_constraints c2 ON c.default_object_id = c2.[object_id] AND b.name IN ('char', 'nchar', 'ntext', 'nvarchar', 'text', 'varchar') WHERE t.[object_id] = c.[object_id] AND c.is_computed = 0 ORDER BY c.column_id FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)') , 1, 2, '') FROM ( SELECT o.[object_id] , o.[schema_id] , o.name FROM sys.objects o WITH (NOLOCK) WHERE o.[type] = 'U' AND EXISTS( SELECT 1 FROM sys.partitions p WITH (NOLOCK) WHERE p.[object_id] = o.[object_id] AND p.[rows] &gt; 0 ) AND NOT EXISTS( SELECT 1 FROM sys.columns c WITH (NOLOCK) JOIN sys.types t WITH (NOLOCK) ON c.system_type_id = t.system_type_id WHERE (c.user_type_id!= c.system_type_id OR t.name = 'xml') AND c.[object_id] = o.[object_id] ) ) t JOIN sys.schemas s WITH (NOLOCK) ON t.[schema_id] = s.[schema_id] -- You can specify sync all db tables or specified schema/tables -- WHERE s.name = 'Sales' -- OR o.name IN ('Address', 'AddressType') ) data OPEN [tables] FETCH NEXT FROM [tables] INTO @ObjectName, @Data IF (@@FETCH_STATUS = -1) RAISERROR('Have nothing to export...', 16, 1) WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Generate XML for ' + @ObjectName RAISERROR(@Text, 0, 1) WITH NOWAIT EXEC sys.sp_executesql @Data FETCH NEXT FROM [tables] INTO @ObjectName, @Data END CLOSE [tables] DEALLOCATE [tables] PRINT 'ELAPSED TIME: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS NVARCHAR(15)) + 'ms' RAISERROR('Finish...', 0, 1) WITH NOWAIT PRINT REPLICATE('-', 80) USE AdventureWorks2008R2_Live RAISERROR('Start...', 0, 1) WITH NOWAIT SELECT @Time = GETDATE() SELECT @Text = CONVERT(NVARCHAR(25), @Time, 114) + ' Get similar objects in both databases...' RAISERROR(@Text, 0, 1) WITH NOWAIT DECLARE @MergeTemp TABLE ( [object_name] NVARCHAR(300) , [object_id] INT , data XML ) INSERT INTO @MergeTemp ( [object_name] , [object_id] , daata ) SELECT mt.[object_name] , o.[object_id] , mt.data FROM tempdb.dbo.MergeTemp mt JOIN ( SELECT o.[object_id] , [object_name] = '[' + s.name + '].[' + o.name + ']' FROM sys.objects o WITH (NOLOCK) JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id] WHERE o.[type] = 'U' ) o ON mt.[object_name] COLLATE DATABASE_DEFAULT = o.[object_name] COLLATE DATABASE_DEFAULT WHERE mt.id = @SessionUID IF NOT EXISTS( SELECT 1 FROM @MergeTemp ) RAISERROR('Have nothing to import...', 16, 1) SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Turn off foreign keys and triggers...' RAISERROR(@Text, 0, 1) WITH NOWAIT EXEC sys.sp_msforeachtable ' ALTER TABLE ? DISABLE TRIGGER ALL ALTER TABLE ? NOCHECK CONSTRAINT ALL ' DECLARE @ObjectID INT , @ObjName NVARCHAR(300) DECLARE package CURSOR FAST_FORWARD READ_ONLY LOCAL FOR SELECT mt.[object_id] , mt.[object_name] FROM @MergeTemp mt OPEN package FETCH NEXT FROM package INTO @ObjectID , @ObjName WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Processing ' + @ObjName + '...' RAISERROR(@Text, 0, 1) WITH NOWAIT SELECT @SQL = ' DECLARE @XML XML SELECT @XML = ( SELECT data FROM tempdb.dbo.MergeTemp WHERE [object_name] = ''' + @ObjName + ''' AND id = ''' + CAST(@SessionUID AS VARCHAR(36)) + ''' ) IF EXISTS( SELECT 1 FROM sys.columns c WITH (NOLOCK) WHERE c.[object_id] = ' + CAST(@ObjectID AS VARCHAR(10)) + ' AND c.is_identity = 1 ) SET IDENTITY_INSERT ' + @ObjName + ' ON; DELETE FROM ' + @ObjName + ' INSERT INTO ' + @ObjName + '(' + STUFF(( SELECT ', [' + c.name + ']' FROM sys.columns c WITH (NOLOCK) WHERE c.[object_id] = @ObjectID AND c.is_computed = 0 ORDER BY c.column_id FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)'), 1, 2, '') + ') SELECT ' + STUFF(( SELECT ', [' + c.name + '] = ' + CASE WHEN c2.name IS NOT NULL THEN 'ISNULL(' ELSE '' END + 'r.c.value(''@' + c.name + ''', ''' + b.name + CASE WHEN b.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'varbinary') AND c.max_length = -1 THEN '(max)' WHEN b.name IN ('char', 'varchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length AS VARCHAR(10)) + ')' WHEN b.name IN ('nchar', 'nvarchar') AND c.max_length!= -1 THEN '(' + CAST(c.max_length / 2 AS VARCHAR(10)) + ')' WHEN b.name = 'decimal' THEN '(' + CAST(c.[precision] AS NVARCHAR(10)) + ',' + CAST(c.[scale] AS VARCHAR(10)) + ')' ELSE '' END + ''')' + CASE WHEN c2.name IS NOT NULL THEN ', ' + c2.[definition] + ')' ELSE '' END FROM sys.columns c WITH (NOLOCK) JOIN sys.types b WITH (NOLOCK) ON b.user_type_id = c.system_type_id AND b.user_type_id = b.system_type_id AND b.name!= 'xml' LEFT JOIN sys.default_constraints c2 WITH (NOLOCK) ON c.default_object_id = c2.[object_id] AND b.name IN ('bit', 'char', 'varchar', 'nchar', 'nvarchar') WHERE c.[object_id] = @ObjectID AND c.is_computed = 0 ORDER BY c.column_id FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'NVARCHAR(MAX)') , 1, 2, '') + ' FROM @XML.nodes(''' + s.name + '.' + o.name + '/t'') r(c) ' FROM sys.objects o WITH (NOLOCK) JOIN sys.schemas s WITH (NOLOCK) ON o.[schema_id] = s.[schema_id] WHERE o.[object_id] = @ObjectID EXEC sys.sp_executesql @SQL FETCH NEXT FROM package INTO @ObjectID , @ObjName END CLOSE package DEALLOCATE package SELECT @Text = CONVERT(NVARCHAR(25), GETDATE(), 114) + ' Turn on foreign keys and triggers...' RAISERROR(@Text, 0, 1) WITH NOWAIT EXEC sys.sp_msforeachtable ' ALTER TABLE ? ENABLE TRIGGER ALL ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL ' PRINT 'ELAPSED TIME: ' + CAST(DATEDIFF(ms, @Time, GETDATE()) AS NVARCHAR(15)) + 'ms' RAISERROR('Finish...', 0, 1) WITH NOWAIT
Conclusion:
As we can see, even for databases with a simple structure, the synchronization process is quite a bigtask that includes many different actions.
As you have noticed early in the article, manual data synchronization is not quite easy task. Thus, to save time and efforts it is highly recommended to use special tools for synchronization. For instance, the following screen-shot demonstrates how you can visualize compared data before synchronization by using comparison feature of dbForge Studio for SQL Server.
The upper grid shows the database objects differences and the bottom grid displays data differences. To synchronize source and target databases you can automatically generate the synchronization script. Then you can view this script, save it, or execute it immediately to update the target database. So the process can be handled in an easier way.
Reference: Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
Hi dear ,
Thanks for such nice blog, I am reading your blogs since 2011, When ever I stuck in any SQL issue your blog also helped me .
I have a question hope you will get time to answer it.
I am developing a MVC app which is just like Point of sales.
In this app user have one web App which is live and multiple desktop applications which have there own local database but whenever desktop app will have internet connectivity it will sync with live server and get any updated or new data, like some branch have transferred stock to other branch or main branch have transferred any stock,
Other than this all branches Purchase and Sales or any data also will be updated to live server and sub branches can also see other branches data.
After googling I get solution that I should use Merge Replication for this purpose , so please give me suggestion about it or any other solution better than this.
But at this level I have question that if I use merge replication so as I have declared auto inc primary key in each table will this PK not conflict with other branches or main server data? And how will it distinguish between branch 1 order id =1001 and branch 2 order id = 1001.
I know Its basic things and I have seen ROWGUID etc but I want to be more clear before doing any thing.
Thanks in advance hope you will get some time to answer my childish question.
Take Care
Regards
Zeeshan Ali