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
TroubleshootingSQL 