pointers, solutions and scripts for the SQL DBA
not intended to replace common sense

2/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:

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

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

    Nir Ben Ami.

    ReplyDelete

All comments are moderated before publishing. If you find something wrong or disagree, please comment so that this blog can be as accurate and helpful as possible.