Using a DDL trigger push changes over a linked server

Here's a script I put together to automatically propagate schema changes to keep two SQL Server databases in sync using a DDL trigger.  This could just as easily be applied to two databases connected by linked server connections.

This isn't the only solution for schema-only change propagation, I'll have a blog post on that later and update this one when I do.  

The source database is w2, w3 is the destination.

/* Create DDL Change Scripting table. The CommandText contains the actual SQL Statement run that made a DDL change.

A TrackDDLChanges table should be created on each remote database.  */

use w3

go

CREATE TABLE dbo.TrackDDLChanges

(

      ID int IDENTITY(1,1) PRIMARY KEY

,     CommandText nvarchar(max) NOT NULL

,     EventType   nvarchar(100) NULL

,     PostTime    datetime NULL    

,     LoginName   nvarchar(100) NULL

,     Program           varchar(130) NULL

,     HostName    varchar(130)  NULL

,     CreateDate  datetime NOT NULL DEFAULT(getdate())

)

/* Create testing table */

CREATE TABLE dbo.TestDDLChanges (ID int IDENTITY(1,1) PRIMARY KEY)

go

/* Create trigger to apply changes on remote db. */

CREATE TRIGGER TR_I_TrackDDLChanges  ON dbo.TrackDDLChanges AFTER INSERT

      AS

     

      SET NOCOUNT ON

      SET XACT_ABORT ON

     

      DECLARE @CommandText nvarchar(max)

     

      SELECT @CommandText = i.CommandText from INSERTED i

     

      EXEC sp_executesql @CommandText

GO

use w2

go

/* Create testing table */

CREATE TABLE dbo.TestDDLChanges

(ID int IDENTITY(1,1) PRIMARY KEY

)

go

/* Create Trigger to capture DDL changes on the entire Database.

   Write the SQL command for the DDL change to the TrackDDLChanges table.

*/

CREATE TRIGGER TR_DDL_SchemaChanges ON DATABASE

      FOR DDL_DATABASE_LEVEL_EVENTS

      AS   

     

      SET NOCOUNT ON

      SET XACT_ABORT ON

     

      --http://msdn.microsoft.com/en-us/library/bb510452.aspx

      BEGIN TRY

                       

            DECLARE     @EventData XML = EVENTDATA()

            DECLARE @CommandText nvarchar(4000) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(4000)')

                       

            --Repeat this insert for each remote database target.

            INSERT INTO [remoteservername].w3.dbo.TrackDDLChanges (EventType, Commandtext, PostTime, LoginName, Program, HostName)

            SELECT

                  EventType   = @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)')

            ,     Commandtext = @CommandText

            ,     PostTime    = @EventData.value('(/EVENT_INSTANCE/PostTime)[1]',   'datetime')

            ,     LoginName   = @EventData.value('(/EVENT_INSTANCE/LoginName)[1]',   'NVARCHAR(100)')

            ,     Program           = PROGRAM_NAME()

            ,     HostName    = HOST_NAME()    

                 

      END TRY

      BEGIN CATCH

           

            print 'DDL propagation to remote site database failed! '

            print 'SQL command that failed: ' + @CommandText

            print '  ErrorNumber: ' + str(ERROR_NUMBER())

            print ' ErrorSeverity: ' + str(ERROR_SEVERITY())

            print ' ErrorState: ' + str(ERROR_STATE())

            print ' ErrorProcedure: ' + isnull(ERROR_PROCEDURE(), '')

            print ' ErrorLine: ' + str(ERROR_LINE())

            print ' ErrorMessage from the remote server: ' + ERROR_MESSAGE()

           --Note the lack of ROLLBACK.

      END CATCH

     

GO

That's all you need to get it going.  Now lets test it out.

/* Show both source and remote tables before DDL changes are made 

   This is just to test the proof of concept.

*/

select * from w2.dbo.TestDDLChanges

select * from w3.dbo.TestDDLChanges

go

/* Make a bunch of DDL changes on source database only 

   This is just to test the proof of concept.

*/

USE w2

go

IF EXISTS (select * from information_schema.tables where table_name = 'Tmp_TestDDLChanges')

      DROP TABLE dbo.Tmp_TestDDLChanges

CREATE TABLE dbo.Tmp_TestDDLChanges

      (

      ID int NOT NULL IDENTITY (1, 1) NOT FOR REPLICATION,

      testcolumn1 nchar(10) COLLATE Latin1_General_CI_AI NULL

      )  ON [PRIMARY]

GO

ALTER TABLE dbo.Tmp_TestDDLChanges SET (LOCK_ESCALATION = TABLE)

GO

EXECUTE sp_addextendedproperty N'MS_Description', N'ID Column', N'SCHEMA', N'dbo', N'TABLE', N'Tmp_TestDDLChanges', N'COLUMN', N'ID'

GO

ALTER TABLE dbo.Tmp_TestDDLChanges ADD CONSTRAINT

      DF_TestDDLChanges_testcolumn1 DEFAULT suser_name() FOR testcolumn1

GO

SET IDENTITY_INSERT dbo.Tmp_TestDDLChanges ON

GO

IF EXISTS(SELECT * FROM dbo.TestDDLChanges)

       EXEC('INSERT INTO dbo.Tmp_TestDDLChanges (ID)

            SELECT ID FROM dbo.TestDDLChanges WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT dbo.Tmp_TestDDLChanges OFF

GO

DROP TABLE dbo.TestDDLChanges

GO

EXECUTE sp_rename N'dbo.Tmp_TestDDLChanges', N'TestDDLChanges', 'OBJECT'

GO

ALTER TABLE dbo.TestDDLChanges ADD CONSTRAINT

      PK_TestDDLC PRIMARY KEY CLUSTERED

      (

      ID

      ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

ALTER TABLE TestDDLChanges ADD text1 nvarchar(4000);

go

--The second insert will fail of the DDL propagation didn't occur.

INSERT INTO w2.dbo.TestDDLChanges (testcolumn1,text1) VALUES ('source','Testing source')

INSERT INTO w3.dbo.TestDDLChanges (testcolumn1,text1) VALUES ('remote','Testing destination')

go

/* Review the changes.  The first table should contain a statement for each of the above DDL changes. 

   This is just to test the proof of concept.

*/

select * from w3.dbo.TrackDDLChanges

select * from w2.dbo.TestDDLChanges

select * from w3.dbo.TestDDLChanges

GO