How To Synchronise Multiple Database Objects Across Two SQL Server Databases or Instances

In two of my previous posts (HERE and HERE) I explored the concept of dynamic data synchronisation between tables in two different databases. Lately, I have been working with a client who required not only data to be replicated between individual tables but also the objects themselves. Data synchronisation can be easily achieved through embedded SSIS functionality or if dynamic configuration is required through THIS solution implementation, however, in this particular instance, the caveat laid with not just data but also with schema acquisition dynamisms – the client had no way of knowing what objects (names, types, definitions, data etc.) were added to the source database that required to be recreated on the target. Simply put it – all tables and their data as well as stored procedures, functions and views had to be recreated on a daily/nightly basis in the destination database without going through the exercise of selecting or nominating them individually, which SSIS is more than capable of performing through Transfer SQL Server Objects Task.

In order to demonstrate how this functionality is resolved, let’s first create a sandbox environment with two databases and a bunch of dummy objects. Source_DB is the database where stored procedure, function, view and a few tables will be placed on, whereas Target_DB, as the name implies, will become our destination database, not containing any user defined objects just yet.

USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Source_DB')
BEGIN
-- Close connections to the DW_Sample database
ALTER DATABASE [Source_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Source_DB]
END
GO
CREATE DATABASE [Source_DB]

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Target_DB')
BEGIN
-- Close connections to the DW_Sample database
ALTER DATABASE [Target_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [Target_DB]
END
GO
CREATE DATABASE [Target_DB]

USE Source_DB
CREATE TABLE Tbl1 (
ID int NOT NULL,
Sample_Data_Col1 varchar (50) NOT NULL,
Sample_Data_Col2 varchar (50) NOT NULL,
Sample_Data_Col3 varchar (50) NOT NULL)
GO

USE Source_DB
DECLARE @rowcount int = 0
WHILE @rowcount < 10000
	BEGIN
		SET NOCOUNT ON
		INSERT INTO Tbl1
		(ID, Sample_Data_Col1, Sample_Data_Col2, Sample_Data_Col3)
		SELECT
		@rowcount,
		'Sample_Data' + CAST(@rowcount as varchar(10)),
		'Sample_Data' + CAST(@rowcount as varchar(10)),
		'Sample_Data' + CAST(@rowcount as varchar(10))
		SET @rowcount = @rowcount + 1
	END
GO

SELECT * INTO Tbl2 FROM Tbl1
SELECT * INTO Tbl3 FROM Tbl1
SELECT * INTO Tbl4 FROM Tbl1
SELECT * INTO Tbl5 FROM Tbl1
GO

CREATE PROCEDURE sp_SampleProcedure1
AS
BEGIN
	SET NOCOUNT ON;
	SELECT GetDate()
END
GO

CREATE FUNCTION dbo.ufn_SampleFunction1 (@ID int)
RETURNS TABLE
AS
RETURN
(SELECT * FROM Tbl1 WHERE ID = @ID)
GO

CREATE VIEW vw_SampleTop10
AS
SELECT TOP (10) * FROM dbo.Tbl1

Once the code executed, we are ready to create our objects synchronisation stored procedure which should account for all user defined objects and recreate them on the destination database – these will include tables with their corresponding data, functions, views and additional stored procedures and can be modified to encompass other types as per additional requirements. The code simply queries a collection of system objects to fetch their names and definitions, builds a temporary table to store this metadata and finally using cursors replicates those on a target database.

USE [target_DB]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SyncDBObjects]')
AND type IN (N'P',N'PC'))
DROP PROCEDURE [dbo].[usp_SyncDBObjects]
GO
 
CREATE PROCEDURE [usp_SyncDBObjects]
@SourceDBName       varchar (256),
@SourceSchemaName   varchar (50),
@TargetDBName       varchar (256),
@TargetSchemaName   varchar (50)
AS
BEGIN
 
    SET NOCOUNT ON
    DECLARE @Err_Msg varchar (max)
    DECLARE @IsDebugMode bit = 0
    DECLARE @SQLSource nvarchar (max) =
    'INSERT INTO #TempTbl
    (ObjectID, ObjectName, SchemaName, DBName, ObjectType, ObjectDefinition)
    SELECT DISTINCT
    o.object_id,o.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''',''Table'',''N/A''
    FROM   '+@SourceDBName+'.sys.tables t
    JOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id
    JOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id
    WHERE S.name = '''+@SourceSchemaName+''' and o.type = ''U''
    UNION ALL
    SELECT DISTINCT
    o.object_id, o.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''',''View'', m.definition
    FROM   '+@SourceDBName+'.sys.tables t
    JOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id
    JOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id
    LEFT JOIN '+@SourceDBName+'.sys.sql_modules m ON m.object_id = o.object_id
    WHERE S.name = '''+@SourceSchemaName+''' and o.type = ''V''
    UNION ALL
    SELECT DISTINCT
    o.object_id, o.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''',''Stored Procedure'', m.definition
    FROM   '+@SourceDBName+'.sys.tables t
    JOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id
    JOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id
    LEFT JOIN '+@SourceDBName+'.sys.sql_modules m ON m.object_id = o.object_id
    WHERE S.name = '''+@SourceSchemaName+''' and o.type = ''P''
    UNION ALL
    SELECT DISTINCT
    o.object_id, o.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''',''Function'', m.definition
    FROM   '+@SourceDBName+'.sys.tables t
    JOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id
    JOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id
    LEFT JOIN '+@SourceDBName+'.sys.sql_modules m ON m.object_id = o.object_id
    WHERE S.name = '''+@SourceSchemaName+''' and o.type IN (''TF'',''IF'', ''FN'', ''FS'')'
 
    IF OBJECT_ID('tempdb..#TempTbl') IS NOT NULL
    DROP TABLE #TempTbl
 
    CREATE TABLE #TempTbl
    (ID int IDENTITY (1,1),
    ObjectID            int,
    ObjectName          varchar (256),
    SchemaName          varchar (50),
    DBName              varchar (50),
    ObjectType          varchar (20),
    ObjectDefinition    varchar (max))
 
    EXEC sp_executesql @SQLSource
 
    IF NOT EXISTS (SELECT 1 FROM #TempTbl a WHERE a.dbname = @SourceDBName and a.SchemaName = @SourceSchemaName)
    BEGIN
        SET
        @Err_Msg = 'Source database objects cannot be fetched. You nominated ''['+@SourceDBName+']'' database on a ''['+@SourceSchemaName+']'' schema. '
        RAISERROR (
        @Err_Msg  -- Message text.
        ,16 -- Severity.
            ,1 -- State.
                    )
        RETURN
    END
 
    IF @IsDebugMode = 1
    SELECT * FROM #TempTbl
    --IF @IsDebugMode = 1
        PRINT ''
        PRINT 'The following objects based on Source vs Target comparison will be synchronised between ''['+@TargetDBName+']'' and ''['+@SourceDBName+']''...'
        DECLARE @ID int
        DECLARE @TblName varchar (256)
        DECLARE cur CURSOR FOR
            SELECT ID, ObjectName FROM #TempTbl
            OPEN cur
            FETCH NEXT FROM cur INTO @ID, @TblName
            WHILE @@FETCH_STATUS = 0
            BEGIN
                PRINT '' + CAST(@ID as varchar (20))+'. '+ @TblName +''
                FETCH NEXT FROM cur INTO @ID, @TblName
            END
        CLOSE cur
        DEALLOCATE cur
    DECLARE @ObjectName varchar (256)
    DECLARE @sql_select varchar (max)
    DECLARE @sql_drop varchar (max)
    DECLARE @sql_definition varchar (max)
	DECLARE @Err int
    PRINT ''
    PRINT 'Starting objects synchronisation process...'
    PRINT ''
    PRINT 'Recreating tables and data...'

    DECLARE db_cursor CURSOR FORWARD_ONLY
        FOR
            SELECT ObjectName
            FROM #TempTbl t
            WHERE t.ObjectType = 'table'
            OPEN db_cursor
			SELECT @Err = @@Error IF @Err <> 0 BEGIN DEALLOCATE db_cursor RETURN @Err END
            FETCH NEXT
            FROM db_cursor INTO @ObjectName
                WHILE @@FETCH_STATUS = 0
                    BEGIN
						BEGIN TRY
							BEGIN TRANSACTION
                            PRINT 'DROPPING TABLE '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''
                            SET @SQL_drop       =   'IF EXISTS (SELECT * FROM '+@TargetDBName+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
                                                = '''+@ObjectName+''') DROP TABLE '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''
                            EXEC sp_sqlexec @SQL_drop
							COMMIT TRANSACTION
						END TRY
						BEGIN CATCH
							ROLLBACK TRANSACTION
							SELECT
							 ERROR_NUMBER       () AS ErrorNumber
							,ERROR_SEVERITY     () AS ErrorSeverity
							,ERROR_STATE        () AS ErrorState
							,ERROR_PROCEDURE    () AS ErrorProcedure
							,ERROR_LINE         () AS ErrorLine
							,ERROR_MESSAGE      () AS ErrorMessage;
						END CATCH

						BEGIN TRY
							BEGIN TRANSACTION
							PRINT 'SELECTING INTO ' + @SourceDBName + '.' + @SourceSchemaName + '.' + @ObjectName + ' FROM '+ @TargetDBName + '.' + @TargetSchemaName + '.' + @ObjectName + ''
                            SET @SQL_select     =   'SELECT * INTO '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+'
                                                FROM '+@SourceDBName+'.'+@SourceSchemaName+'.'+@ObjectName+''
                            EXEC sp_sqlexec @SQL_select
							COMMIT TRANSACTION
						END TRY
						BEGIN CATCH
							ROLLBACK TRANSACTION
							SELECT
							 ERROR_NUMBER       () AS ErrorNumber
							,ERROR_SEVERITY     () AS ErrorSeverity
							,ERROR_STATE        () AS ErrorState
							,ERROR_PROCEDURE    () AS ErrorProcedure
							,ERROR_LINE         () AS ErrorLine
							,ERROR_MESSAGE      () AS ErrorMessage;
						END CATCH
                    FETCH NEXT FROM db_cursor INTO @ObjectName
                END
            CLOSE db_cursor
            DEALLOCATE db_cursor
    PRINT ''
    PRINT 'Recreating views...'
    DECLARE db_cursor CURSOR FORWARD_ONLY
        FOR
            SELECT ObjectName
            FROM #TempTbl t
            WHERE t.ObjectType = 'view'
            OPEN db_cursor
			SELECT @Err = @@Error IF @Err <> 0 BEGIN DEALLOCATE db_cursor RETURN @Err END
            FETCH NEXT
            FROM db_cursor INTO @ObjectName
                WHILE @@FETCH_STATUS = 0
					BEGIN
						BEGIN TRY
							BEGIN TRANSACTION
                            PRINT 'DROPPING VIEW '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''
                            SET @SQL_drop       =   'IF EXISTS (SELECT * FROM '+@TargetDBName+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME
                                                = '''+@ObjectName+''') DROP VIEW '+@TargetSchemaName+'.'+@ObjectName+'' 
                            EXEC sp_sqlexec @SQL_drop
							COMMIT TRANSACTION
						END TRY
						BEGIN CATCH
							ROLLBACK TRANSACTION
							SELECT
							 ERROR_NUMBER       () AS ErrorNumber
							,ERROR_SEVERITY     () AS ErrorSeverity
							,ERROR_STATE        () AS ErrorState
							,ERROR_PROCEDURE    () AS ErrorProcedure
							,ERROR_LINE         () AS ErrorLine
							,ERROR_MESSAGE      () AS ErrorMessage;
						END CATCH

						BEGIN TRY
							BEGIN TRANSACTION
							PRINT 'CREATING VIEW ' + @ObjectName + ' on '+@TargetDBName+' from '+ @SourceDBName + '.' + @SourceSchemaName + '.' + @ObjectName + ' view'
							SET @SQL_select     =   (SELECT ObjectDefinition FROM #TempTbl WHERE ObjectName = @ObjectName)
                            EXEC sp_sqlexec @SQL_select
							COMMIT TRANSACTION
						END TRY
						BEGIN CATCH
							ROLLBACK TRANSACTION
							SELECT
							 ERROR_NUMBER       () AS ErrorNumber
							,ERROR_SEVERITY     () AS ErrorSeverity
							,ERROR_STATE        () AS ErrorState
							,ERROR_PROCEDURE    () AS ErrorProcedure
							,ERROR_LINE         () AS ErrorLine
							,ERROR_MESSAGE      () AS ErrorMessage;
						END CATCH
                    FETCH NEXT FROM db_cursor INTO @ObjectName
                END
            CLOSE db_cursor
            DEALLOCATE db_cursor
    PRINT ''
    PRINT 'Recreating functions...'
    DECLARE db_cursor CURSOR FORWARD_ONLY
        FOR
            SELECT ObjectName
            FROM #TempTbl t
            WHERE t.ObjectType = 'Function'
            OPEN db_cursor
			SELECT @Err = @@Error IF @Err <> 0 BEGIN DEALLOCATE db_cursor RETURN @Err END
            FETCH NEXT
            FROM db_cursor INTO @ObjectName
                WHILE @@FETCH_STATUS = 0
                    BEGIN
						BEGIN TRY
							BEGIN TRANSACTION
                            PRINT 'DROPPING FUNCTION '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''
                            SET @SQL_drop       =   'IF EXISTS (SELECT * FROM '+@TargetDBName+'.'+@TargetSchemaName+'.sysobjects WHERE id = object_id('''+@ObjectName+''')
                                                    AND xtype IN (''TF'',''IF'', ''FN'', ''FS''))  DROP FUNCTION '+@TargetSchemaName+'.'+@ObjectName+''
                            EXEC sp_sqlexec @SQL_drop
							COMMIT TRANSACTION
						END TRY
						BEGIN CATCH
							ROLLBACK TRANSACTION
							SELECT
							 ERROR_NUMBER       () AS ErrorNumber
							,ERROR_SEVERITY     () AS ErrorSeverity
							,ERROR_STATE        () AS ErrorState
							,ERROR_PROCEDURE    () AS ErrorProcedure
							,ERROR_LINE         () AS ErrorLine
							,ERROR_MESSAGE      () AS ErrorMessage;
						END CATCH 

						BEGIN TRY
							BEGIN TRANSACTION
							PRINT 'CREATING FUNCTION  ' + @ObjectName + ' on '+@TargetDBName+' from '+ @SourceDBName + '.' + @SourceSchemaName + '.' + @ObjectName + ' function'
							SET @SQL_select     =   (SELECT ObjectDefinition FROM #TempTbl WHERE ObjectName = @ObjectName)
                            EXEC sp_sqlexec @SQL_select
							COMMIT TRANSACTION
						END TRY
						BEGIN CATCH
							ROLLBACK TRANSACTION
							SELECT
							 ERROR_NUMBER       () AS ErrorNumber
							,ERROR_SEVERITY     () AS ErrorSeverity
							,ERROR_STATE        () AS ErrorState
							,ERROR_PROCEDURE    () AS ErrorProcedure
							,ERROR_LINE         () AS ErrorLine
							,ERROR_MESSAGE      () AS ErrorMessage;
						END CATCH
                    FETCH NEXT FROM db_cursor INTO @ObjectName
                END
            CLOSE db_cursor
            DEALLOCATE db_cursor
    PRINT ''
    PRINT 'Recreating stored procedures...'
        DECLARE db_cursor CURSOR FORWARD_ONLY
        FOR
            SELECT ObjectName
            FROM #TempTbl t
            WHERE t.ObjectType = 'Stored Procedure'
            OPEN db_cursor
			SELECT @Err = @@Error IF @Err <> 0 BEGIN DEALLOCATE db_cursor RETURN @Err END
            FETCH NEXT
            FROM db_cursor INTO @ObjectName
                WHILE @@FETCH_STATUS = 0
                    BEGIN
						BEGIN TRY
							BEGIN TRANSACTION
                            PRINT 'DROPPING STORED PROCEDURE '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''
                            SET @SQL_drop       =   'IF EXISTS (SELECT * FROM '+@TargetDBName+'.'+@TargetSchemaName+'.sysobjects WHERE id = object_id('''+@ObjectName+'''))
                                                     DROP PROCEDURE '+@TargetSchemaName+'.'+@ObjectName+''
                            EXEC sp_sqlexec @SQL_drop
							COMMIT TRANSACTION
						END TRY
						BEGIN CATCH
							ROLLBACK TRANSACTION
							SELECT
							 ERROR_NUMBER       () AS ErrorNumber
							,ERROR_SEVERITY     () AS ErrorSeverity
							,ERROR_STATE        () AS ErrorState
							,ERROR_PROCEDURE    () AS ErrorProcedure
							,ERROR_LINE         () AS ErrorLine
							,ERROR_MESSAGE      () AS ErrorMessage;
						END CATCH 

						BEGIN TRY
							BEGIN TRANSACTION
							PRINT 'CREATING STORED PROCEDURE ' + @ObjectName + ' on '+@TargetDBName+' from '+ @SourceDBName + '.' + @SourceSchemaName + '.' + @ObjectName + ' procedure'
							SET @SQL_select     =   (SELECT ObjectDefinition FROM #TempTbl WHERE ObjectName = @ObjectName)
                            EXEC sp_sqlexec @SQL_select
							COMMIT TRANSACTION
							END TRY
						BEGIN CATCH
							ROLLBACK TRANSACTION
							SELECT
							 ERROR_NUMBER       () AS ErrorNumber
							,ERROR_SEVERITY     () AS ErrorSeverity
							,ERROR_STATE        () AS ErrorState
							,ERROR_PROCEDURE    () AS ErrorProcedure
							,ERROR_LINE         () AS ErrorLine
							,ERROR_MESSAGE      () AS ErrorMessage;
						END CATCH 
                    FETCH NEXT FROM db_cursor INTO @ObjectName
                END
       CLOSE db_cursor
       DEALLOCATE db_cursor
END

Naturally, this SQL code can be modified/extended to work across two separate instances e.g. via a linked server connection, replicate other types of objects e.g. triggers, constraint, synonyms etc. and integrate with a separate SSIS routine/framework. When cross-instance replication is involved, the only change required to the above code is setting up linked servers connection and using four part, fully qualified identifiers i.e. server.database.schema.object.

When the procedure has finished executing, both Source_DB and Target_DB should contain identical data and objects schema for tables, functions, stored procedures and tables as per execution log and each database sys.objects query image below.

If the schema is to remain static and you only wish to synchronise data, please check out my other two posts (HERE and HERE) where I explored the dynamic functionality of cross-table data replication through on-the-fly MERGE statement creation and UPSERT execution.

Submit Article :- BlinkList + Blogmarks + Digg + Del.icio.us + Ekstreme Socializer + Feedmarker + Furl + Google Bookmarks + ma.gnolia + Netvouz + RawSugar + Reddit + Scuttle + Shadows + Simpy + Spurl + Technorati + Unalog + Wink

http://scuttle.org/bookmarks.php/pass?action=add

Posted in: How To's, SQL

Tags: SQL, SQL Server

This entry was posted on Monday, October 7th, 2013 at 9:49 pm and is filed under How To's, SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

What you are looking at...

My name is Martin and this site is a random collection of recipes and reflections about various topics covering information management, data engineering, machine learning, business intelligence and visualisation plus everything else that I fancy to categorise under the 'analytics' umbrella. I'm a native of Poland but since my university days I have lived in Melbourne, Australia and worked as a DBA, developer, data architect, technical lead and team manager. My main interests lie in both, helping clients in technical aspects of information management e.g. data modelling, systems architecture, cloud deployments as well as business-oriented strategies e.g. enterprise data solutions project management, data governance and stewardship, data security and privacy or data monetisation. On the whole, I am very fond of anything closely or remotely related to data and as long as it can be represented as a string of ones and zeros and then analysed and visualised, you've got my attention!

Outside sporadic updates to this site I typically find myself fiddling with data, spending time with my kids or a good book, the gym or watching a good movie while eating Polish sausage with Zubrowka (best served on rocks with apple juice and a lime twist). Please read on and if you find these posts of any interests, don't hesitate to leave me a comment!

Subscribe via RSS | Comments (RSS)