Redheadatabase

About me: My name is Maria Zakourdaev. I have more than 10 years experience with SQL Server (starting from SQL Server 6.5). The last five years have been spent mostly on benchmarking different SQL Server features and flows, like data import, indexes impact on DML flows, star transformations in RDBMS, Hierarchic queries and custom OLAP-like aggregations. I was a speaker in the Microsoft Teched (Israel) on the SQL Server track. I am also an active member of the Israel SQL Server Group.

Wednesday, August 18, 2010

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

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

Labels: , , , , , , , , , ,

2 Comments:

Anonymous Berrie Roeofs said...

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

March 19, 2013 at 5:53 AM  
Blogger Oky said...

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.

April 18, 2013 at 4:12 AM  

Post a Comment

Subscribe to Post Comments [Atom]

Links to this post:

Create a Link

<< Home