Monday, February 14, 2011

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
     




2 comments:

David Alexander said...

Gonna have to check this out, cool, thx William.

Anonymous said...

Thank you. It is exactly what i needed.
Your explnations is very helpful.
Keep on doing.

Nir Ben Ami.