{"id":1835,"date":"2013-09-18T00:29:11","date_gmt":"2013-09-18T00:29:11","guid":{"rendered":"http:\/\/bicortex.com\/?p=1835"},"modified":"2013-09-18T01:13:49","modified_gmt":"2013-09-18T01:13:49","slug":"how-to-synchronise-data-across-two-sql-server-databases-part-2-sql-code-ssis-package-and-application-for-multiple-objects-processing","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/how-to-synchronise-data-across-two-sql-server-databases-part-2-sql-code-ssis-package-and-application-for-multiple-objects-processing\/","title":{"rendered":"How To Synchronise Data Across Two SQL Server Databases – Part 2. SQL Code, SSIS Package And Application For Multiple Objects Processing"},"content":{"rendered":"

In the FIRST POST<\/b><\/a> to this series I outlined how to synchronised data across two different databases using dynamic MERGE SQL statement. The idea was that the code built MERGE SQL statement on the fly based on database objects\u2019 metadata and as long the table had a primary key constraint present, it automatically handled INSERT and UPDATE based on its content. In this post I would like to expand on this approach and show you how to provide a looping functionality by means of using another stored procedure or an SSIS package to pick up all relevant object and execute it as many times as there is tables to merge together without listing object names individually. All the code and solution files for this series can be downloaded from HERE<\/b><\/a>.<\/p>\n

Using SQL Stored Procedure With Cursor<\/b><\/h3>\n

The simplest way to loop through a collection of tables which qualify for synchronisation is to create a simple stored procedure with a cursor. Before we get to the nuts and bolts of this solution, however, let\u2019s first create sample databases, objects and dummy data for this demonstration. The below SQL code creates two databases, each containing three tables. Each table located in Source_DB database has 1000 records in it. We can also notice that our destination database has seemingly similar structure, however, from the data point of view, there is only 500 records in each table. Also, attributes with IDs numbered from 1 to 10 are different in source database to IDs in target database. This creates a good foundation for inserting and updating source data based on those discrepancies using MERGE SQL statement. Let\u2019s go ahead and create all necessary databases, objects and dummy data.<\/p>\n

\r\nUSE [master]\r\nGO\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'Source_DB')\r\nBEGIN\r\n-- Close connections to the DW_Sample database\r\nALTER DATABASE [Source_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nDROP DATABASE [Source_DB]\r\nEND\r\nGO\r\nCREATE DATABASE [Source_DB] \r\n\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'Target_DB')\r\nBEGIN\r\n-- Close connections to the DW_Sample database\r\nALTER DATABASE [Target_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nDROP DATABASE [Target_DB]\r\nEND\r\nGO\r\nCREATE DATABASE [Target_DB]\r\n\r\nUSE Source_DB\r\nCREATE TABLE Tbl1 (\r\nID int NOT NULL,\r\nSample_Data_Col1 varchar (50) NOT NULL,\r\nSample_Data_Col2 varchar (50) NOT NULL,\r\nSample_Data_Col3 varchar (50) NOT NULL)\r\nGO\r\n\r\nUSE Target_DB\r\nCREATE TABLE Tbl1 (\r\nID int NOT NULL,\r\nSample_Data_Col1 varchar (50) NOT NULL,\r\nSample_Data_Col2 varchar (50) NOT NULL,\r\nSample_Data_Col3 varchar (50) NOT NULL)\r\nGO\r\n\r\nUSE Source_DB\r\nDECLARE @rowcount int = 0\r\nWHILE @rowcount < 1000\r\n\tBEGIN\r\n\t\tSET NOCOUNT ON\r\n\t\tINSERT INTO Tbl1\r\n\t\t(ID, Sample_Data_Col1, Sample_Data_Col2, Sample_Data_Col3)\r\n\t\tSELECT \r\n\t\t@rowcount, \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)), \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)), \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10))\r\n\t\tSET @rowcount = @rowcount + 1\r\n\tEND\r\nGO\r\n\r\nSELECT * INTO Tbl2 FROM Tbl1\r\nSELECT * INTO Tbl3 FROM Tbl1\r\n\r\nUSE Target_DB\r\nDECLARE @rowcount int = 0\r\nWHILE @rowcount < 1000\r\n\tBEGIN\r\n\t\tSET NOCOUNT ON\r\n\t\tINSERT INTO Tbl1\r\n\t\t(ID, Sample_Data_Col1, Sample_Data_Col2, Sample_Data_Col3)\r\n\t\tSELECT \r\n\t\t@rowcount, \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)), \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)), \r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10))\r\n\t\tSET @rowcount = @rowcount + 1\r\n\tEND\r\nGO\r\n\r\nDELETE FROM Target_DB.dbo.Tbl1\r\nWHERE ID >= 500\r\n\r\nUPDATE Source_DB.dbo.Tbl1\r\nSET Sample_Data_Col1 = 'Changed_Data'\r\nWHERE ID < 10\r\nUPDATE Source_DB.dbo.Tbl1\r\nSET Sample_Data_Col2 = 'Changed_Data'\r\nWHERE ID < 10\r\nUPDATE Source_DB.dbo.Tbl1\r\nSET Sample_Data_Col3 = 'Changed_Data'\r\nWHERE ID < 10\r\n\r\nSELECT * INTO Tbl2 FROM Tbl1\r\nSELECT * INTO Tbl3 FROM Tbl1\r\n\r\nCREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Source_DB.dbo.Tbl1\r\n([ID] ASC)\r\nGO\r\nCREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Source_DB.dbo.Tbl2\r\n([ID] ASC)\r\nGO\r\nCREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Source_DB.dbo.Tbl3\r\n([ID] ASC)\r\nGO\r\nCREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Target_DB.dbo.Tbl1\r\n([ID] ASC)\r\nGO\r\nCREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Target_DB.dbo.Tbl2\r\n([ID] ASC)\r\nGO\r\nCREATE UNIQUE CLUSTERED INDEX [Clustered_Idx_Id] ON Target_DB.dbo.Tbl3\r\n([ID] ASC)\r\nGO\r\n<\/pre>\n
Next, let\u2019s recreate the usp_DBSync stored procedure from the PREVIOUS POST<\/b><\/a>. The SQL code can be found either going back to the start of this series \u2013 POST 1<\/b><\/a> \u2013 or alternatively downloaded from HERE<\/b><\/a>. Without usp_DBSync stored procedure on the server the rest of the solution will not work so make sure that you re-create it first. Now that we have all necessary objects, we are ready to create the construct which will provide our looping functionality based on metadata and allow for multiple objects processing without the need to specify their names. In order to do this, let\u2019s create a ‘wrapper’ stored procedure around usp_DBSync procedure executing the following code.<\/p>\n
\r\nUSE [Source_DB]\r\nIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SyncMultipleTables]')\r\nAND type IN (N'P',N'PC'))\r\nDROP PROCEDURE [dbo].[usp_SyncMultipleTables]\r\nGO\r\n\r\nCREATE PROCEDURE [usp_SyncMultipleTables]\r\n@SourceDBName varchar (256),\r\n@SourceSchemaName varchar (50),\r\n@TargetDBName varchar (256),\r\n@TargetSchemaName varchar (50)\r\n\r\nAS\r\nBEGIN\r\n\tSET NOCOUNT ON\r\n\tDECLARE @Err_Msg varchar (max)\r\n\tDECLARE @IsDebugMode bit = 1\r\n\tDECLARE @SQLSource nvarchar (max) =\r\n\t'INSERT INTO #TempTbl\r\n\t(ObjectName, SchemaName, DBName, Source_vs_Target)\r\n\tSELECT DISTINCT\r\n\to.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''', ''S''\r\n\tFROM   '+@SourceDBName+'.sys.tables t\r\n\tJOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id\r\n\tJOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id\r\n\tWHERE S.name = '''+@SourceSchemaName+''' and o.type = ''U'''\r\n\r\n\tDECLARE @SQLTarget nvarchar (max) =\r\n\t'INSERT INTO #TempTbl\r\n\t(ObjectName, SchemaName, DBName, Source_vs_Target)\r\n\tSELECT DISTINCT\r\n\to.name, '''+@TargetSchemaName+''', '''+@TargetDBName+''', ''T''\r\n\tFROM   '+@TargetDBName+'.sys.tables t\r\n\tJOIN '+@TargetDBName+'.sys.schemas s ON t.schema_id = s.schema_id\r\n\tJOIN '+@TargetDBName+'.sys.objects o ON t.schema_id = o.schema_id\r\n\tWHERE S.name = '''+@TargetSchemaName+''' and o.type = ''U'''\r\n\r\n\tCREATE TABLE #TempTbl\r\n\t(ObjectName varchar (256),\r\n\tSchemaName varchar (50),\r\n\tDBName varchar (50),\r\n\tSource_vs_Target char(1))\r\n\r\n\tEXEC sp_executesql @SQLSource\r\n\tEXEC sp_executesql @SQLTarget\r\n\r\n\tIF @IsDebugMode = 1\r\n\tSELECT * FROM #TempTbl\r\n\r\n\tCREATE TABLE #TempFinalTbl\r\n\t(ID int IDENTITY (1,1),\r\n\tObjectName varchar (256))\r\n\r\n\tINSERT INTO #TempFinalTbl\r\n\t(ObjectName)\r\n\tSELECT ObjectName\r\n\tFROM #TempTbl a\r\n\tWHERE Source_vs_Target = 'S'\r\n\tINTERSECT\r\n\tSELECT ObjectName\r\n\tFROM #TempTbl a\r\n\tWHERE Source_vs_Target = 'T'\r\n\r\n\tIF @IsDebugMode = 1\r\n\t\tSELECT * FROM #TempFinalTbl\r\n\tIF @IsDebugMode = 1\r\n\t\tPRINT 'The following tables will be merged between the source and target databases...'\r\n\t\tDECLARE @ID int\r\n\t\tDECLARE @TblName varchar (256)\r\n\t\tDECLARE cur CURSOR FOR\r\n\t\t\tSELECT ID, ObjectName FROM #TempFinalTbl\r\n\t\t\tOPEN cur\r\n\t\t\tFETCH NEXT FROM cur INTO @ID, @TblName\r\n\t\t\tWHILE @@FETCH_STATUS = 0\r\n\t\t\tBEGIN\r\n\t\t\t\tPRINT '' + CAST(@ID as varchar (20))+'. '+ @TblName +''\r\n\t\t\t\tFETCH NEXT FROM cur INTO @ID, @TblName\r\n\t\t\tEND\r\n\t\tCLOSE cur\r\n\t\tDEALLOCATE cur\r\n\r\n\tDECLARE @ObjectName varchar (256)\r\n\tDECLARE db_cursor CURSOR\r\n\t\tFOR\r\n\t\t\tSELECT ObjectName\r\n\t\t\tFROM #TempFinalTbl\r\n\t\t\tOPEN db_cursor\r\n\t\t\tFETCH NEXT\r\n\t\t\tFROM db_cursor INTO @ObjectName\r\n\t\t\t\tWHILE @@FETCH_STATUS = 0\r\n\t\t\t\t\tBEGIN\r\n\t\t\t\t\t\tPRINT char(10)\r\n\t\t\t\t\t\tPRINT 'Starting merging process...'\r\n\t\t\t\t\t\tPRINT 'Merging ' + @SourceDBName + '.' + @SourceSchemaName + '.' + @ObjectName + ' with '+ @TargetDBName + '.' + @TargetSchemaName + '.' + @ObjectName + ''\r\n\t\t\t\t\t\tEXEC [dbo].[usp_DBSync] @SourceDBName, @TargetDBName, @SourceSchemaName, @TargetSchemaName, @ObjectName, @ObjectName\r\n\t\t\t\t\t\tFETCH NEXT FROM db_cursor INTO @ObjectName\r\n\t\t\t\t\tEND\r\n\t\t\tCLOSE db_cursor\r\n\t\t\tDEALLOCATE db_cursor\r\nEND\r\n<\/pre>\n
This code simply reiterates through tables which share same names between two different databases, providing necessary metadata for our code MERGE SQL stored procedure (usp_DBSync). Given we have our environment setup correctly i.e. we executed the first code snippet to prep our databases and objects and we also have usp_DBSync stored procedure sitting on our Source_DB database we can run the usp_SyncMultipleTables procedure to see if it correctly accounted for the database objects and the data they hold as well as whether data has been synchronised successfully. Let\u2019s execute our stored procedure and observe the output using the following SQL.<\/p>\n
\r\nUSE [Source_DB]\r\nGO\r\nDECLARE\t@return_value int\r\nEXEC\t@return_value = [dbo].[usp_SyncMultipleTables]\r\n\t\t@SourceDBName = N'Source_DB',\r\n\t\t@SourceSchemaName = N'dbo',\r\n\t\t@TargetDBName = N'Target_DB',\r\n\t\t@TargetSchemaName = N'dbo'\r\nSELECT\t'Return Value' = @return_value\r\nGO\r\n<\/pre>\n
\"DBMergeSync_Sync_Multiple_Tbls_Exec_Results\"
\n<\/a><\/p>\n
Finally, when comparing the data between the two databases, we should note that all tables i.e. Tbl1, Tbl2 and Tbl3 have been synchronised and contain the same data. Please note that only tables with same names will be synchronised. If you think of merging data from tables with different names, you need to provide additional functionality to account for source versus target objects mapping.<\/p>\n
Using SQL Stored Procedure and SQL Server Integration Services<\/b><\/h3>\n
If you are familiar with SQL Server Integration Services, we can achieve the same result building a simple solution in BIDS or SQL Server Data Tools. Let\u2019s re-create the environment again running the first SQL code snippet again to start with a clean slate. We also have to recreate usp_DBSync stored procedure as per PREVIOUS POST<\/b><\/a> SQL code – without usp_DBSync stored procedure re-created the rest of the solution will not work. Next, we will create a simple stored procedure which will be used by our SSIS package to pass through the object names before we can initiate the looping functionality.<\/p>\n
\r\nUSE [Source_DB]\r\nIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ReturnObjectsMetadata]')\r\nAND type IN (N'P',N'PC'))\r\nDROP PROCEDURE [dbo].[usp_ReturnObjectsMetadata]\r\nGO\r\n\r\nCREATE PROCEDURE usp_ReturnObjectsMetadata\r\n(@SourceSchemaName varchar (50),\r\n@SourceDBName varchar (256),\r\n@TargetSchemaName varchar (50),\r\n@TargetDBName varchar (256))\r\nAS\r\nBEGIN\r\n\tSET NOCOUNT ON\r\n\tDECLARE @SQLSource nvarchar (max) =\r\n\t'INSERT INTO #TempTbl\r\n\t(ObjectName, SchemaName, DBName, Source_vs_Target)\r\n\tSELECT DISTINCT\r\n\to.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''', ''S''\r\n\tFROM   '+@SourceDBName+'.sys.tables t\r\n\tJOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id\r\n\tJOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id\r\n\tWHERE S.name = '''+@SourceSchemaName+''' and o.type = ''U'''\r\n\r\n\tDECLARE @SQLTarget nvarchar (max) =\r\n\t'INSERT INTO #TempTbl\r\n\t(ObjectName, SchemaName, DBName, Source_vs_Target)\r\n\tSELECT DISTINCT\r\n\to.name, '''+@TargetSchemaName+''', '''+@TargetDBName+''', ''T''\r\n\tFROM   '+@TargetDBName+'.sys.tables t\r\n\tJOIN '+@TargetDBName+'.sys.schemas s ON t.schema_id = s.schema_id\r\n\tJOIN '+@TargetDBName+'.sys.objects o ON t.schema_id = o.schema_id\r\n\tWHERE S.name = '''+@TargetSchemaName+''' and o.type = ''U'''\r\n\r\n\tCREATE TABLE #TempTbl\r\n\t(ObjectName varchar (256),\r\n\tSchemaName varchar (50),\r\n\tDBName varchar (50),\r\n\tSource_vs_Target char(1))\r\n\r\n\tEXEC sp_executesql @SQLSource\r\n\tEXEC sp_executesql @SQLTarget\r\n\r\n\tCREATE TABLE #TempFinalTbl\r\n\t(ID int IDENTITY (1,1),\r\n\tObjectName varchar (256))\r\n\r\n\tINSERT INTO #TempFinalTbl\r\n\t(ObjectName)\r\n\tSELECT DISTINCT ObjectName\r\n\tFROM #TempTbl a\r\n\tWHERE Source_vs_Target = 'S'\r\n\tINTERSECT\r\n\tSELECT DISTINCT ObjectName\r\n\tFROM #TempTbl a\r\n\tWHERE Source_vs_Target = 'T'\r\n\r\n\tSELECT DISTINCT ObjectName FROM #TempFinalTbl\r\nEND\r\n<\/pre>\n
Finally, we are ready to build a simple SSIS package which will handle iterating through object names as merging occurs (all files for this package can be downloaded from HERE<\/b><\/a>). Let\u2019s create a simple SSIS solution starting with a setting up a database connection (the name will be different as per the environment which your’re developing on) and the following list of variables.<\/p>\n
\"DBMergeSync_Con_and_Variables_SSIS_SetUp\"<\/a><\/p>\n
Continuing on, let\u2019s place Execute SQL Task component on the Control Flow pane and adjust its properties under General settings to the following SQL statement and Result Set option.<\/p>\n
\"DBMergeSync_General_ExecSQL1_SSIS_Property\"<\/a><\/p>\n
Next, let\u2019s map the parameters names to our variables and adjust Result Set properties as per the images below.<\/p>\n
\"DBMergeSync_ParamMapping_ExecSQL1_SSIS_Property\"<\/a><\/p>\n
\"DBMergeSync_ResultSet_ExecSQL1_SSIS_Property\"<\/a><\/p>\n
This part of package is responsible for populating our TableNames variable with the names of the objects we will be looping through. In order to reiterate through table names we will place For Each Loop container from Toolbar on the development pane, join it to the first Execute SQL Task transformation with default constraint option and place another Execute SQL Task container inside the For Each Loop one. Next, let\u2019s adjust the second Execute SQL Task container\u2019s properties as per below.<\/p>\n
\"DBMergeSync_General_ExecSQL2_SSIS_Property\"<\/a><\/p>\n
\"DBMergeSync_ParamMapping_ExecSQL2_SSIS_Property\"<\/a><\/p>\n
Lastly, let\u2019s go through similar exercise with the For Each Loop transformation making sure that the Enumerator in the Collection property pane is set to Foreach ADO Enumerator, ADO Object Source Variable is set to User::TableNames variable and that Variable Mapping property is adjusted to match our User::TableNames variable as per images below.<\/p>\n
\"DBMergeSync_ForEachLoop_Collection_SSIS_Property\"<\/a><\/p>\n
\"DBMergeSync_ForEachLoop_VarMapping_SSIS_Property\"<\/a><\/p>\n
That should be just enough development to provide us with some basic, rudimentary functionality for the package to serve the intended purpose. Let\u2019s test it out to so hopefully when you run the package it will synchronise all the database objects (can be confirmed with a simple SELECT * FROM <table_name> SQL statement) and the development pane output will be as per image below.<\/p>\n
\"DBMergeSync_SSIS_Exec_Complete\"<\/a><\/p>\n
This concludes this mini-series. If you happen to stumble upon this blog and find it somewhat useful, please don\u2019t hesitate to leave me a comment \u2013 any feedback is appreciate, good or bad! Again, the first post to this series can be viewed HERE<\/b><\/a> and all the SQL code as well as the solution files can be downloaded from HERE<\/b><\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
In the FIRST POST to this series I outlined how to synchronised data across two different databases using dynamic MERGE SQL statement. The idea was that the code built MERGE SQL statement on the fly based on database objects\u2019 metadata and as long the table had a primary key constraint present, it automatically handled INSERT […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,5],"tags":[49],"class_list":["post-1835","post","type-post","status-publish","format-standard","hentry","category-how-tos","category-sql","tag-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1835","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=1835"}],"version-history":[{"count":14,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1835\/revisions"}],"predecessor-version":[{"id":1861,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1835\/revisions\/1861"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=1835"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=1835"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=1835"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}