info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



How to prevent schema changes to tables configured for SQL Server Replication
by BF (Principal Consultant; Architecture; Engineering)
2016-06-30









Create a T-SQL Database-Level Trigger:

Create Trigger tr_DDLOnTablesUnderReplication
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS

BEGIN

--Select * from sys.triggers order by name

DECLARE @TABLE_NAME SYSNAME
SELECT @TABLE_NAME = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','SYSNAME')

If Exists (Select 1
FROM
[Distribution].[dbo].[MSarticles] AS A
INNER JOIN [Distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id])
where a.article = @TABLE_NAME
and A.[source_owner] = CONVERT(nvarchar(100), CURRENT_USER)) --DBO
Begin
PRINT @TABLE_NAME + ' - Is a Replicated Table. Please contact your DBA to proceed!'
ROLLBACK;
End
END


Note: This code assumes your Distribution DB is named Distribution and on the same server as your Subscribers. If not the case,
adjust the T-SQL accordingly.


Testing:


Table not configured for Replication:

Alter table dbo.TBL1 add PhotoID int NUll

Output:

Command(s) completed successfully.



Table configured for Replication:

Alter table dbo.TBL2 add PhotoID int NUll

Output:

TBL2 - Is a Replicated Table. Please contact DBA Sysadmin to proceed!
Msg 3609, Level 16, State 2, Line 2
The transaction ended in the trigger. The batch has been aborted.



DBA steps to make schema change:

Step 1:
Disable Trigger [tr_TBL2_upd_Status] on [dbo].[TBL2]
go

Step 2:
Make schema change

Step 3:
Enable Trigger [tr_TBL2_upd_Status] on [dbo].[TBL2]
go