By: Kun Lee | 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.
- Books Online: sp_addarticle (Transact-SQL)
- 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
- Replicate the Primary Key with index that is related to the primary key constraint
- Convert User Defined Types (UDTs) to base data types at the Subscriber
- 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.
Schema Option |
Publisher Version |
Distributor Version |
Subscriber Version |
Description |
0x00000000000000A3 | SQL2K | SQL2K5 | SQL2K | No PK with same index type as PK |
SQL2K | SQL2K5 | SQL2K8 | No PK with same index type as PK | |
SQL2K8 | SQL2K8 | SQL2K8 | No PK with same index type as PK | |
0x00000000000080A3 | SQL2K | SQL2K5 | SQL2K | PK with no other index |
SQL2K | SQL2K5 | SQL2K8 | PK with no other index | |
SQL2K8 | SQL2K8 | SQL2K8 | PK with no other index | |
0x00000000000000B3 | SQL2K | SQL2K5 | SQL2K | The same index as PK on publisher, but NO PK constraints |
SQL2K | SQL2K5 | SQL2K8 | The same index as PK on publisher, but NO PK constraints | |
SQL2K | SQL2K8 | SQL2K8 | The same index as PK on publisher, but NO PK constraints | |
SQL2K5 | SQL2K8 | SQL2K8 | PK with no other index with clustered index | |
SQL2K8 | SQL2K8 | SQL2K8 | PK with no other index with clustered index | |
0x00000000000080B3 | SQL2K | SQL2K5 | SQL2K | PK with clustered index even if the index is not part of PK |
SQL2K | SQL2K5 | SQL2K8 | PK with clustered index even if the index is not part of PK | |
SQL2K8 | SQL2K8 | SQL2K8 | PK with clustered index even if the index is not part of PK | |
0x00000000000082A3 | SQL2K | SQL2K5 | SQL2K | PK + FK with clustered index even if the index is not part of PK |
SQL2K | SQL2K5 | SQL2k8 | PK + FK with clustered index even if the index is not part of PK | |
SQL2K8 | SQL2K8 | SQL2K8 | PK + FK with clustered index even if the index is not part of PK |
* PK - Primary Key
* FK - Foreign Key
So, based on the above, I have concluded that our company, "0x00000000000080A3" fits our needs.
Requirement 2
- All of the Requirement 1 plus
- Replicate all Indexes on Publisher
Schema Option | Publisher Version |
Distributor Version |
Subscriber Version |
Description |
0x0000000000001073 | SQL2K | SQL2K5 | SQL2k | No PK with all indexes (no FK) |
SQL2K | SQL2K5 | SQL2k8 | No PK with all indexes (no FK) | |
SQL2K8 | SQL2K8 | SQL2K8 | PK with all indexes (no FK) | |
0x00000000000080F3 | SQL2K | SQL2K5 | SQL2k | PK with all indexes (no FK) |
SQL2K | SQL2K5 | SQL2k8 | PK with all indexes (no FK) | |
SQL2K8 | SQL2K8 | SQL2K8 | PK with all indexes (no FK) | |
0x00000000000082F3 | SQL2K | SQL2K5 | SQL2k | PK+FK with all indexes |
SQL2K | SQL2K5 | SQL2k8 | PK+FK with all indexes | |
SQL2K8 | SQL2K8 | SQL2K8 | PK+FK with all indexes |
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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips