복제::스키마 옵션

출처: http://www.mssqltips.com/sqlservertip/2128/replication-schema-option-across-different-versions-of-sql-server/

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.

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

1. All of the Requirement 1 plus
2. 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.