Enabling Transactional Replication: A bit of help


Over the past few months, I have discussed the feasibility of enabling transaction replication for customer databases on various occasions. Every time I end up writing queries to answer certain questions about the database… the most common one being if the tables that need to be replicated have primary keys.

So I finally decided to write a T-SQL script which will help me answer the most common questions asked about a database while deciding on the feasibility of enabling transaction replication.

The script doesn’t capture information like workload, performance metrics etc. to decide if the replication workload (snapshot and distribution agent) can be supported on the existing hardware and resources available in the environment.

My take on the matter is that this information is required only once we have figured out if transactional replication can be enabled on the database or not. Eg. If the main tables that need to be replicated do not have primary keys, then the question of resource availability and hardware capability is moot point!

The script below checks the following:

1. Existing of primary keys on the tables in the database. Objects (articles) without primary keys cannot be replicated as part of a transactional replication publication.
2. If the database has transparent database encryption enabled. The subscriber database is not automatically enabled for TDE in such a scenario.
3. Constraints, primary keys, triggers and identify columns which have  NOT FOR REPLICATION bit set and which objects do not. You might choose to replicate or not replicate some of these objects. However, you need to be aware of what you are replicating.
4. Tables having ntext, text and image columns as there are special considerations for handling DMLs on such columns.
5. XML schema collections present in the database. Modifications to the XML Schema collection are not replicated.
6. Tables with sparse column sets as they cannot be replicated.
7. Objects created using WITH ENCRYPTION option. Such objects cannot be replicated either.

As always, in case you think that there are additional checks that could be included in the script, then please leave a comment on my blog and I will add the same into the script.


/************************************************************

DISCLAIMER:

The Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. THIS SAMPLE CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE. We grant You a nonexclusive, royalty-free right to use and modify the Sample Code and to reproduce and distribute the object code form of the Sample Code, provided that You agree: (i) to not use Our name, logo, or trademarks to market Your software product in which the Sample Code is embedded (ii) to include a valid copyright notice on Your software product in which the Sample Code is embedded and (iii) to indemnify, hold harmless, and defend Us and Our suppliers from and against any claims or lawsuits, including attorneys’ fees, that arise or result from the use or distribution of the Sample Code.

This script can be executed against the context of a database to identify if the objects in the database can be replicated. Some of the common replication rules are verified using this script.

The script works for the following releases of SQL Server:

SQL Server 2008

SQL Server 2008 R2

SQL Server 2012

Author: Amit Banerjee

Date: February 1st, 2013

For feedback, please leave a comment on my blog: www.troubleshootingsql.com

************************************************************/

SET NOCOUNT ON

GO

PRINT '******* Tables without primary keys *******'

PRINT '******* Tables which do NOT have primary keys cannot be used as an article for Transactional Replication *******'

IF EXISTS (SELECT COUNT(*)

FROM sys.objects a

inner join sys.schemas b

on a.schema_id = b.schema_id

WHERE is_ms_shipped = 0 and type = 'U'

AND object_id NOT IN (SELECT object_id FROM sys.key_constraints where is_ms_shipped = 0))

BEGIN

SELECT quotename(b.name) +'.' + quotename(a.name) as TableName

FROM sys.objects a

inner join sys.schemas b

on a.schema_id = b.schema_id

WHERE is_ms_shipped = 0 and type = 'U'

AND object_id NOT IN (SELECT object_id FROM sys.key_constraints where is_ms_shipped = 0)

END

IF EXISTS (SELECT name

FROM sys.triggers

WHERE parent_class_desc = 'OBJECT_OR_COLUMN' AND is_ms_shipped = 0 AND is_not_for_replication = 0 AND is_disabled = 0)

BEGIN

PRINT '******* Triggers without NOT FOR REPLICATION option not set *******'

SELECT name as TriggerName

FROM sys.triggers

WHERE parent_class_desc = 'OBJECT_OR_COLUMN' AND is_ms_shipped = 0 AND is_not_for_replication = 0 AND is_disabled = 0

END

IF EXISTS (SELECT name

FROM sys.triggers

WHERE parent_class_desc = 'OBJECT_OR_COLUMN' AND is_ms_shipped = 0 AND is_not_for_replication = 1 AND is_disabled = 0)

BEGIN

PRINT '******* Triggers with NOT FOR REPLICATION option set *******'

SELECT name as TriggerName

FROM sys.triggers

WHERE parent_class_desc = 'OBJECT_OR_COLUMN' AND is_ms_shipped = 0 AND is_not_for_replication = 1 AND is_disabled = 0

END

IF EXISTS (SELECT name

FROM sys.check_constraints

WHERE is_ms_shipped = 0 AND is_not_for_replication = 0 AND is_disabled = 0)

BEGIN

PRINT '******* Constraints without NOT FOR REPLICATION option not set *******'

SELECT SCHEMA_NAME(schema_id) SchemaName, name as ConstraintName

FROM sys.check_constraints

WHERE is_ms_shipped = 0 AND is_not_for_replication = 0 AND is_disabled = 0

END

IF EXISTS (SELECT name

FROM sys.check_constraints

WHERE is_ms_shipped = 0 AND is_not_for_replication = 1 AND is_disabled = 0)

BEGIN

PRINT '******* Constraints with NOT FOR REPLICATION option set *******'

SELECT SCHEMA_NAME(schema_id) SchemaName, name as ConstraintName

FROM sys.check_constraints

WHERE is_ms_shipped = 0 AND is_not_for_replication = 1 AND is_disabled = 0

END

IF EXISTS (SELECT name

FROM sys.triggers

WHERE is_ms_shipped = 0 AND is_not_for_replication = 0 AND is_disabled = 0)

BEGIN

PRINT '******* Triggers with NOT FOR REPLICATION option set *******'

SELECT name as TriggerName

FROM sys.triggers

WHERE is_ms_shipped = 0 AND is_not_for_replication = 0 AND is_disabled = 0

END

IF EXISTS (SELECT name

FROM sys.triggers

WHERE is_ms_shipped = 0 AND is_not_for_replication = 1 AND is_disabled = 0)

BEGIN

PRINT '******* Triggers with NOT FOR REPLICATION option set *******'

SELECT name as TriggerName

FROM sys.triggers

WHERE is_ms_shipped = 0 AND is_not_for_replication = 1 AND is_disabled = 0

END

IF EXISTS (SELECT name

FROM sys.identity_columns

WHERE is_not_for_replication = 0 AND object_id IN (SELECT object_id FROM sys.objects WHERE is_ms_shipped = 0))

BEGIN

PRINT '******* Identity columns without NOT FOR REPLICATION option set *******'

SELECT OBJECT_NAME(object_id) as TableName,name as ColumnName

FROM sys.identity_columns

WHERE is_not_for_replication = 0 AND object_id IN (SELECT object_id FROM sys.objects WHERE is_ms_shipped = 0)

END

IF EXISTS (SELECT name

FROM sys.identity_columns

WHERE is_not_for_replication = 1 AND object_id IN (SELECT object_id FROM sys.objects WHERE is_ms_shipped = 0))

BEGIN

PRINT '******* Identity columns with NOT FOR REPLICATION option set *******'

SELECT OBJECT_NAME(object_id) as TableName,name as ColumnName

FROM sys.identity_columns

WHERE is_not_for_replication = 1 AND object_id IN (SELECT object_id FROM sys.objects WHERE is_ms_shipped = 0)

END

IF EXISTS (SELECT a.name

FROM sys.columns a

INNER JOIN sys.objects b

ON a.object_id = b.object_id

WHERE b.is_ms_shipped = 0

AND a.system_type_id IN (34, 35, 99))

BEGIN

PRINT '******* Tables with text, ntext and image columns *******'

SELECT QUOTENAME(SCHEMA_NAME(b.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(a.object_id)) as TableName, a.name as ColumnName

FROM sys.columns a

INNER JOIN sys.objects b

ON a.object_id = b.object_id

WHERE b.is_ms_shipped = 0

AND a.system_type_id IN (34, 35, 99)

END

IF EXISTS (SELECT name

FROM sys.xml_schema_collections

WHERE name <> 'sys')

BEGIN

PRINT '******* XML Schema Collections can be replicated but changes are not replicated after the initial snapshot *******'

PRINT '******* List of XML Schema Collections in the database *******'

SELECT QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) as XMLSchemaCollectionName

FROM sys.xml_schema_collections

WHERE name <> 'sys'

END

IF EXISTS (SELECT a.name

FROM sys.columns a

INNER JOIN sys.objects b

ON a.object_id = b.object_id

WHERE b.is_ms_shipped = 0

AND a.is_column_set = 1)

BEGIN

PRINT '******* Tables with sparse column sets. Transactional replication does not support column sets. *******'

SELECT QUOTENAME(SCHEMA_NAME(b.schema_id)) + '.' + QUOTENAME(OBJECT_NAME(a.object_id)) as TableName, a.name as ColumnName

FROM sys.columns a

INNER JOIN sys.objects b

ON a.object_id = b.object_id

WHERE b.is_ms_shipped = 0

AND a.is_column_set = 1

END

IF EXISTS (SELECT name

FROM sys.objects WHERE type in ('P','V','FN','IF','TR')

AND OBJECTPROPERTY(object_id,'IsEncrypted') = 1

AND is_ms_shipped = 0)

BEGIN

PRINT '******* List of objects created WITH ENCRYPTION option. These objects cannot be replicated *******'

SELECT CASE [type]

WHEN 'P' THEN 'Stored Procedure'

WHEN 'V' THEN 'View'

WHEN 'FN' THEN 'Function'

WHEN 'IF' THEN 'Inline Function'

WHEN 'TR' THEN 'Trigger'

END as ObjectType,

QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) as ObjectName

FROM sys.objects WHERE type in ('P','V','FN','IF','TR')

AND OBJECTPROPERTY(object_id,'IsEncrypted') = 1

AND is_ms_shipped = 0

ORDER BY [type]

END

IF EXISTS (SELECT name FROM sys.databases WHERE is_encrypted = 1 AND database_id = DB_ID())

BEGIN

PRINT '******* Transparent database encryption (TDE) is enabled on the database'

PRINT '******* TDE has to be enabled on the subscriber as well if you want a TDE enabled subscriber'

END

SELECT '******* Tables without primary keys: ' + CAST(COUNT(*) AS varchar(5))

FROM sys.objects

WHERE is_ms_shipped = 0 and type = 'U'

AND object_id NOT IN (SELECT object_id FROM sys.key_constraints where is_ms_shipped = 0)

PRINT '****************************************'

PRINT 'Refer Books Online topic "Considerations for Transactional Replication" for more details for considerations for Transaction Replication'

PRINT 'Reference: http://msdn.microsoft.com/en-us/library/ms151254(v=SQL.105).aspx'

PRINT ''

PRINT 'Refer Books Online topic "Updatable Subscriptions for Transactional Replication" for more details for considerations for Updateable Subscriptions'

PRINT 'Reference: http://msdn.microsoft.com/en-us/library/ms151718(v=sql.105).aspx'

PRINT ''

PRINT 'Refer Books Online topic "Publishing Data and Database Objects" for more information'

PRINT 'http://msdn.microsoft.com/en-us/library/ms152559(v=SQL.105).aspx'

PRINT ''

PRINT 'Refer Books Online topic "Frequently Asked Questions for Replication Administrators" for more information'

PRINT 'http://technet.microsoft.com/en-us/library/ms151740(v=sql.105).aspx'

GO

SET NOCOUNT OFF

GO

Advertisements

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s