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.
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.
