Replication Schema Option Across Different Versions of SQL Server

By: Kun Lee   |   Updated: 2010-10-13   |   Comments   |   Related: 1 | 2 | 3 | 4 | > Replication

Problem

My company uses replication quite extensively across SQL 2000, SQL 2005 and SQL 2008 servers. The problem with using different versions of SQL Server is that the replication options do not always behave the same way. Because of these differences we have stopped using SQL Server Management Studio and Enterprise Manager to setup replication. Also we have taken the time to look at the schema options for replication to make sure the end result is the same for all versions of SQL Server.

Solution

We basically have two requirements for replication which are outlined below. I have listed the different schema options that the GUI generates to show how things work differently in different versions of SQL Server as well as the options we selected for our environment.

Background Information

Here are two articles related to this tip that you should understand.

1. Books Online: sp_addarticle (Transact-SQL)
2. How to: Specify Schema Options (Replication Transact-SQL Programming)

If you look at the schema option on sp_addarticle (Transact-SQL), you will see there are over 40 combinations of schema options and it can be hard to know which one to choose. The article, How to: Specify Schema Options (Replication Transact-SQL Programming) shows how to figure out and specify schema options.

Replication Requirements

My company has two different requirements for replication as outlined below. When using the GUI with different versions of SQL Server several different schema options are generated, but they do not always work the same across all versions of SQL Server.

Requirement 1

1. Replicate the Primary Key with index that is related to the primary key constraint
2. Convert User Defined Types (UDTs) to base data types at the Subscriber
3. Use SQL Stored Procedures to replicate (auto generate store procedures on subscribers for Insert, Update and Delete)

Based on the above, I have tested the below combinations and as you can see different version have different results for the 0xB3 option.

* PK - Primary Key
* FK - Foreign Key

So, based on the above, I have concluded that our company, "0x00000000000080A3" fits our needs.

Requirement 2

1. All of the Requirement 1 plus
2. Replicate all Indexes on Publisher

Based on the testing, we have decide to use "0x00000000000080F3".

Confirmation

Now, here is simple query that I use to confirm the options.

If you run this for 0x00000000000080A3:

declare @pubid int
declare @optionid bigint
declare @schema_option varbinary(2000)
set @optionid = 1
set @schema_option = 0x00000000000080A3
WHILE (@optionid <= 2147483648)
BEGIN
if (select @schema_option & @optionid) > 0 
PRINT cast(@optionid as varbinary)
SET @optionid = @optionid * 2
END

You will see this result:

0x0000000000000001
0x0000000000000002
0x0000000000000020
0x0000000000000080
0x0000000000008000

You can check Books Online: sp_addarticle (Transact-SQL) and you will see the below options. Note, in Books Online they shorten the notation, so 0x0000000000000001 = 0x01.

• 0x0000000000000001 - Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
• 0x0000000000000002 - Generates the stored procedures that propagate changes for the article, if defined.
• 0x0000000000000020 - Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.
• 0x0000000000000080 - Replicates primary key constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enable
• 0x0000000000008000 - This option is not valid for SQL Server 2005 Publishers.

If we do the same for 0x00000000000080F3, we get this output:

• 0x0000000000000001 - Generates the object creation script (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
• 0x0000000000000002 - Generates the stored procedures that propagate changes for the article, if defined.
• 0x0000000000000010 - Generates a corresponding clustered index. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.
• 0x0000000000000020 - Converts user-defined data types (UDT) to base data types at the Subscriber. This option cannot be used when there is a CHECK or DEFAULT constraint on a UDT column, if a UDT column is part of the primary key, or if a computed column references a UDT column. Not supported for Oracle Publishers.
• 0x0000000000000040 - Generates corresponding nonclustered indexes. Even if this option is not set, indexes related to primary keys and unique constraints are generated if they are already defined on a published table.
• 0x0000000000000080 - Replicates primary key constraints. Any indexes related to the constraint are also replicated, even if options 0x10 and 0x40 are not enable
• 0x0000000000008000 - This option is not valid for SQL Server 2005 Publishers.

Next Steps

• The reason we do not replicate indexes for Requirement 1 is that sometimes we have different indexes on the Subscriber than on the Publisher, so this gives us flexibility in creating these Subscriber indexes independently.
• When uses replication across multiple versions be aware that some options may not function the same way.
• Read these additional tips about replication.

About the author

Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips