Replication fun: setting NFR on all objects

If you ever tried to set up replication on the large environment with numerous servers, hundreds of databases then you, for sure, spend some time searching/writing scripts to set flag NOT FOR REPLICATION on thousands of identities, constraints and triggers. I want to share here my scripts and I hope it will save some readers hours of headache. Feel free to use them, just add comments below - there is nothing better than feedback.

Here we go.

********************************************************************************
1. Identities. The resutset is a list of identity columns on all tables in all databases where NOT FOR REPLICATION option is not set and fixing script:


DROP TABLE #IdentityNFR
CREATE TABLE #IdentityNFR (ServerName varchar(250), Dbname varchar(250),TableName varchar(250), IdentityColName varchar(250), IsNFR varchar(10),FixingScript varchar(4000))
exec sp_MSforeachdb '
IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') BEGIN
USE ?
INSERT INTO #IdentityNFR
SELECT @@servername,''?'' DBName, OBJECT_NAME(c.object_id) TableName, c.name IdentityColumnName,
CASE ColumnProperty(object_id, name, ''IsIDNotForRepl'')
WHEN 0 THEN ''No''
WHEN 1 THEN ''Yes''
ELSE ''Unknown''
END [Is NFR],
''Alter Table [''+OBJECT_NAME(c.object_id)+''] Alter Column [''+c.name+''] ADD NOT FOR REPLICATION;'' FixingScript
FROM ?.sys.columns c
WHERE is_identity = 1
AND OBJECTPROPERTY(object_id, ''IsUserTable'') = 1
AND ColumnProperty(object_id, name, ''IsIDNotForRepl'') = 0
AND OBJECTPROPERTY(object_id, ''IsMSShipped'') = 0
END
'
select * from #IdentityNFR
********************************************************************************
********************************************************************************

2. Triggers. Same as above, the resutset is a list of all triggers on all tables in all databases where NOT FOR REPLICATION option is not set and fixing script:

DROP TABLE #TriggersNFR
CREATE TABLE #TriggersNFR (ServerName varchar(250), Dbname varchar(250),TableName varchar(250), TriggerName varchar(250), FixingScript varchar(max))
exec sp_MSforeachdb '
IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'') BEGIN
USE ?
insert into #TriggersNFR
SELECT @@servername,''?'' DBName,OBJECT_NAME(parent_object_id) TableName,
OBJECT_NAME(o.object_id) [Trigger Name],
STUFF (REPLACE(definition,''CREATE TRIGGER'',''ALTER TRIGGER''),
PATINDEX(''%AS%'',REPLACE(definition,''CREATE TRIGGER'',''ALTER TRIGGER'')),0,''
NOT FOR REPLICATION '')
+ ''
GO
''
from ?.sys.objects o
join ?.sys.sql_modules m
on o.object_id = m.object_id
where o.type = ''TR''
and OBJECTPROPERTY(o.object_id, ''ExecIsTriggerNotForRepl'') = 0
AND OBJECTPROPERTY(o.object_id, ''IsMSShipped'') = 0
END
'

select * from #TriggersNFR

********************************************************************************
********************************************************************************
3. Foreign keys.

This one is a little more tricky. In SQL 2005 we can use SQLDMO to get foreign key script, which is fantastic feature from my point of view. We do not need to think about how many columns we have in foreign key definition and no need to create complicated case structions. Unfortunately Microsoft decided to stop support for this feature in SQL 2008. So, pasting some simple script that will work with 1 column foreign key only, if you have foreign key on multiple column need do some fixing.
Just keep in mind that setting NOT FOR REPLICATION on Foreign keys ( as well as on check constraints) sets constraint as NOT TRUSTED which mean optimizer cannot trust this constraint any more.

********************************************************************************
SQL 2005:
SET NOCOUNT ON

DECLARE @rc int,@oServer int,@method varchar(300),@TSQL varchar(4000),@ScriptType int
DECLARE @DBname varchar(250),@TableName varchar(250),@FKName varchar(250)

IF OBJECT_ID('tempdb..#FKConstrNFR') IS NOT NULL DROP TABLE #FKConstrNFR
CREATE TABLE #FKConstrNFR ( Dbname varchar(250),TableName varchar(250), FKName varchar(250),Changed int)
exec sp_MSforeachdb '
IF ''?'' NOT IN (''tempdb'',''master'',''model'',''msdb'',''ReportServer'') BEGIN
USE ?
insert into #FKConstrNFR
SELECT ''?'' DBName,sh.name + ''.''+t.name TableName,
fk.name FKName,0
from ?.sys.foreign_keys fk
join ?.sys.tables t
on fk.parent_object_id = t.object_id
join ?.sys.schemas sh
on t.schema_id = sh.schema_id
WHERE is_not_for_replication = 0 and is_disabled = 0
AND OBJECTPROPERTY(fk.object_id, ''IsMSShipped'') = 0
END
'
EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'
EXEC sp_OAMethod @oServer , 'Connect', NULL, @@servername, null , null
SET @ScriptType =1432262144

while exists ( select 1 from #FKConstrNFR
where Changed = 0 ) BEGIN

SELECT top 1 @DBname = Dbname,@TableName = TableName, @FKname = FKName
FROM #FKConstrNFR
WHERE Changed = 0

UPDATE #FKConstrNFR
SET Changed = 1
WHERE Dbname=@DBname and TableName=@TableName and FKName=@FKname

PRINT 'USE ' + @DBname
PRINT 'GO'

SET @TSQL = ''

SET @method = 'Databases("'+@DBname+'").Tables("'+@TableName+'").Keys("'+@FKname+'").Script' +
'(' + CAST (@ScriptType AS CHAR) + ')'

EXEC @rc=sp_OAMethod @oServer, @method , @TSQL OUTPUT

IF PATINDEX('%CASCADE%',@TSQL) > 0 BEGIN
SET @TSQL = STUFF(@TSQL,CHARINDEX('CASCADE',@TSQL,PATINDEX('%CASCADE%',@TSQL)+1)+7,0,' NOT FOR REPLICATION')
END
ELSE BEGIN
SET @TSQL = stuff(@TSQL,charindex(')',@TSQL,patindex('%REFERENCES%',@TSQL)),1,') NOT FOR REPLICATION')
END

PRINT @TSQL
--EXEC sp_OAGetErrorInfo @oServer

END

EXEC sp_OADestroy @oServer


********************************************************************************
SQL 2008:
set nocount on;

select 'BEGIN TRAN
ALTER TABLE '+ s.name + '.'+t.name+ ' DROP CONSTRAINT '+k.name+';
ALTER TABLE '+ s.name + '.'+ t.name+ ' WITH CHECK ADD CONSTRAINT '+k.name +
' FOREIGN KEY('+COL_NAME(c.parent_object_id,parent_column_id)+') REFERENCES '+
s2.name + '.' + t2.name+'('+COL_NAME(c.referenced_object_id,c.referenced_column_id)+') ' +

case when k.delete_referential_action = 1 then ' ON DELETE CASCADE ' ELSE '' END +
case when k.update_referential_action = 1 then ' ON UPDATE CASCADE ' ELSE '' END +
' NOT FOR REPLICATION
IF @@ROWCOUNT = 0 COMMIT TRAN ELSE ROLLBACK'
from sys.foreign_keys k
join sys.foreign_key_columns c
on k.object_id = c.constraint_object_id
join sys.tables t
on k.parent_object_id = t.object_id
join sys.schemas s
on t.schema_id = s.schema_id
join sys.tables t2
on c.referenced_object_id = t2.object_id
join sys.schemas s2
on t2.schema_id = s2.schema_id

where is_not_for_replication = 0

********************************************************************************
********************************************************************************

Comments

  1. Hi,
    Thank you for thes NFR scrips.
    I've changed the FK script, so that it includes all columns. Also I added a script for check constraints, because they con contain NFR as well. Both scripts are for version 2008 and 2012.

    /*
    --3. Foreign keys.
    --Just keep in mind that setting NOT FOR REPLICATION on Foreign keys (as well as on check constraints) sets constraint as
    --NOT TRUSTED which mean optimizer cannot trust this constraint any more.
    */
    SET NOCOUNT ON;
    WITH CTE ( [Constraint], ParentSchema, ParentObject, ParentFKcolumns, ReferencedSchema, ReferencedObject, ReferencedFKcolumns
    , delete_referential_action, update_referential_action)
    AS (
    SELECT [Constraint] = FK.name
    , ParentSchema = object_schema_name(FK.parent_object_id)
    , ParentObject = object_name(FK.parent_object_id)
    , ParentFKcolumns = REPLACE((SELECT QUOTENAME(COL_NAME(FKC.parent_object_id, FKC.parent_column_id)) + ', '
    FROM sys.foreign_key_columns FKC
    WHERE object_name(FKC.constraint_object_id) = FK.name AND FKC.parent_object_id = FK.parent_object_id
    ORDER BY FKC.referenced_column_id
    FOR XML PATH('')) + '~', ', ~', '')
    , ReferencedSchema = object_schema_name(FK.referenced_object_id)
    , ReferencedObject = object_name(FK.referenced_object_id)
    , ReferencedFKcolumns = REPLACE((SELECT QUOTENAME(COL_NAME(FKC.referenced_object_id, FKC.referenced_column_id)) + ', '
    FROM sys.foreign_key_columns FKC
    WHERE object_name(FKC.constraint_object_id) = FK.name
    ORDER BY FKC.referenced_column_id
    FOR XML PATH('')) + '~', ', ~', '')
    , delete_referential_action
    , update_referential_action
    FROM sys.foreign_keys FK
    WHERE is_not_for_replication = 0
    )
    SELECT 'ALTER TABLE [' + ParentSchema + '].[' + ParentObject + '] DROP CONSTRAINT [' + [Constraint] +']; ' + CHAR(10) +
    'ALTER TABLE [' + ParentSchema + '].[' + ParentObject + '] WITH CHECK ADD CONSTRAINT [' + [Constraint] + ']' + CHAR(10) +
    'FOREIGN KEY (' + ParentFKcolumns + ')' + CHAR(10) +
    'REFERENCES [' + ReferencedSchema + '].[' + ReferencedObject + '] (' + ReferencedFKcolumns + ')' + CHAR(10) +
    CASE WHEN delete_referential_action = 1 THEN 'ON DELETE CASCADE ' + CHAR(10) ELSE '' END +
    CASE WHEN update_referential_action = 1 THEN 'ON UPDATE CASCADE ' + CHAR(10) ELSE '' END +
    'NOT FOR REPLICATION ' +
    + CHAR(10) + 'GO' + CHAR(10)
    FROM CTE
    ORDER BY 1;

    /*
    --4. Check constraints.
    */
    SET NOCOUNT ON;
    SELECT 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + '];' + CHAR(10) +
    'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] ADD CONSTRAINT [' + name + ']' + CHAR(10) +
    'CHECK NOT FOR REPLICATION (' + [definition] + ')' + CHAR(10) +
    'GO' + CHAR(10)
    FROM sys.check_constraints
    WHERE is_not_for_replication = 0;
    GO

    ReplyDelete
  2. Beware,
    the trigger script will insert the 'not for replicatiion' in the wrong place, if the letters 'as' apear in the trigger name or a comment before it. I add char(13)+ before the 'as'.
    Check all scripts! they also get truncated.

    ReplyDelete
  3. I don’t know who you are but certainly you are going to a famous blogger if you aren’t already ?? Cheers!

    ReplyDelete
  4. I appreciate your skills and style in elaborating on the topic. It bound the reader for long. I have liked everything that I have read in the article so far. Nice content.

    ReplyDelete
  5. There may be noticeably a bundle to find out about this. I assume you made certain good factors in features also.

    ReplyDelete
  6. Thanks for sharing such an insightful post. This post really contains amazing stuff that I never read. I appreciate you for writing such an informational post. Well Done keep posting!

    ReplyDelete
  7. This comment has been removed by a blog administrator.

    ReplyDelete
  8. Great blog! The content is well-written and engaging, keeping me hooked from start to finish. I appreciate the insightful perspectives and valuable information shared here. The layout and design of the website are visually appealing and user-friendly, making navigation a breeze. I look forward to reading more thought-provoking articles and discovering new topics on this fantastic blog. Keep up the excellent work!

    Bathurst 1000 Schedule

    ReplyDelete

Post a Comment

Popular posts from this blog

SQL Awesomeness: Finding a QUALIFY query clause in the depths of the database ocean

The Greatest Reasons to use or not to use a Centralized Data Access Architecture

Look back and realize how far you came