Microsoft KB Archive/300164

= INF: How to Set Up an Identity Column on both the Publisher and the Subscriber with Transactional Replication =

Article ID: 300164

Article Last Modified on 8/8/2001

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q300164



SUMMARY
This article describes how you can replicate an IDENTITY column to another server while preserving the IDENTITY values in a table on both the source and the destination servers. In the example, the destination server must be able to update the column and have the information propagated back to the source.



MORE INFORMATION
The recommended method to implement this design is to use Merge Replication. However, this article assumes that due to design constraints you have decided that you want to implement the design by using a Transactional Replication topology.

For immediate-updating subscribers you cannot have an IDENTITY column on the subscriber-side. To have an IDENTITY column on the subscriber-side, requires &quot;Nonpartitioned, Bidirectional, Transactional Replication&quot;, which is described in the &quot;Nonpartitioned, Bidirectional, Transactional Replication&quot; topic in SQL Server Books Online. Note that you need to correct the example listed in SQL Server Books Online as per the following Microsoft Knowledge Base article:

240235 BUG: Implementing Nonpartitioned, Bi-directional, Transactional Replication Sample in Books Online Contains Errors

The Transact-SQL script that follows demonstrates how to replicate a table that has an updateable IDENTITY column on both the Publisher and the Subscriber. This example makes the following assumptions:

 Because neither of the subscriptions are immediate-updating, it is possible to insert the same primary key value on both the Publisher and the Subscriber. To avoid having the same primary key value, the example does not do anything. In the real world you must partition the primary key so that a key violation does not occur.

 The Publisher, Distributor and Subscriber are on the same computer that is running SQL Server on which you run the script. This is for the convenience of the sample code and is not a requirement for the method being used.

 The script creates two databases:

 [test1]

-and-

 [test2]

The two databases should not conflict with any existing databases. Make sure that you do not have any existing databases with any one or both of these names.

 The script assumes that the local server entry is correct and that the @@servername function returns the correct SQL Server name.

</li> The IDENTITY seed value is partitioned arbitrarily for the purposes of the example. On [test1].[dbo].[table1].[intcol] the seed value is 1, and on [test2].[dbo].[test2].[intcol] the seed value is 1,000,000,000. The value is approximately half of the positive maximum for an INTEGER data type.

</li> For the purpose of the example the destination and source table names are not the same; however, this is not a requirement of the method being used.</li></ul>

The Transact-SQL script to implement the example follows: USE MASTER GO

-- Step 1: Create the databases. RAISERROR( 'Creating publication databases...', 0, 1 ) GO

CREATE DATABASE test1 GO CREATE DATABASE test2 GO

-- Step 2: Enable the databases for replication. USE master GO

RAISERROR( 'Enabling databases for publication...', 0, 1 ) exec sp_replicationdboption N'test1', N'publish', true GO

exec sp_replicationdboption N'test2', N'publish', true GO

-- Step 3: Create two tables that have an IDENTITY column with the &quot;Not -- For Replication&quot; option set. USE test1 GO

RAISERROR( 'Creating user table [two_way_test1]...', 0, 1 ) CREATE TABLE two_way_test1 (   pkcol       INTEGER PRIMARY KEY NOT NULL,    intcol      INTEGER IDENTITY(1,1) NOT FOR REPLICATION,    charcol     CHAR(100),    timestampcol    TIMESTAMP )

USE test2 GO

RAISERROR( 'Creating user table [two_way_test2]...', 0, 1 ) CREATE TABLE two_way_test2 (   pkcol       INTEGER PRIMARY KEY NOT NULL,    intcol      INTEGER IDENTITY(1000000000,1) NOT FOR REPLICATION,    charcol     CHAR(100),    timestampcol    TIMESTAMP ) GO

-- Step 4: Create a custom stored procedure to apply changes. -- A. Create custom stored procedures in test1. USE test1 GO

RAISERROR( 'Creating customer stored procedures in database [test1]...', 0, 1 ) GO

-- Insert procedure. The column list is required for IDENTITY columns. -- Normally, you need a Set Identity_Insert two_way_test1 on but this is --  taken care of by the Not For Replication option only for incoming -- replication agents. CREATE PROCEDURE sp_ins_two_way_test1  @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp AS INSERT INTO two_way_test1 (pkcol, intcol, charcol) VALUES (@pkcol, @intcol, @charcol) GO

-- Update procedure: This needs to be completely recoded, because you -- cannot update an IDENTITY column. With &quot;Not For Replication&quot; the agent -- can insert a specific value into the column. Normally, this requires -- the use of Identity_Insert. CREATE PROCEDURE sp_upd_two_way_test1  @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @old_pkcol int AS DECLARE @x INT DECLARE @y INT DECLARE @z CHAR(100) SELECT @x=pkcol, @y=intcol, @z=charcol FROM two_way_test1 WHERE pkcol = @pkcol

DELETE two_way_test1 WHERE pkcol=@pkcol

INSERT INTO two_way_test1 (pkcol, intcol, charcol) VALUES(   CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END,    CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END,    CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END  ) GO

-- Delete procedure: This remains the same. CREATE PROCEDURE sp_del_two_way_test1 @old_pkcol int AS DELETE two_way_test1 WHERE pkcol = @old_pkcol GO

-- B. Create custom stored procedures in test2. USE test2 GO

RAISERROR( 'Creating customer stored procedures in database [test2]...', 0, 1 ) GO -- Insert procedure. CREATE PROCEDURE sp_ins_two_way_test2  @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp AS INSERT INTO two_way_test2 (pkcol, intcol, charcol) VALUES (@pkcol, @intcol, @charcol) GO

-- Update procedure. CREATE PROCEDURE sp_upd_two_way_test2  @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @old_pkcol int AS DECLARE @x INT DECLARE @y INT DECLARE @z CHAR(100) SELECT @x=pkcol, @y=intcol, @z=charcol FROM two_way_test2 WHERE pkcol=@pkcol DELETE two_way_test2 WHERE pkcol=@pkcol INSERT INTO two_way_test2 (pkcol, intcol, charcol) VALUES(   CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END,    CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END,    CASE ISNULL(@charcol,'N') WHEN 'N' then @z ELSE @charcol END  ) GO

-- Delete procedure. CREATE PROCEDURE sp_del_two_way_test2 @old_pkcol int AS DELETE two_way_test2 WHERE pkcol = @old_pkcol GO

-- Step 5: Create publications for both directions. -- A. Add the transactional publication and article in test1. USE test1 GO

RAISERROR( 'Adding the transactional publication two_way_pub_test1 for database [test1]...', 0, 1 ) GO

-- Adding the transactional publication. sp_addpublication @publication = N'two_way_pub_test1', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication of database test1.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'true', @autogen_sync_procs = N'true', @retention = 72 GO

sp_addpublication_snapshot @publication = N'two_way_pub_test1',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 233000, @active_end_time_of_day = 0 GO

RAISERROR( 'Adding publication article [two_way_test1]', 0, 1 ) GO

-- Adding the transactional articles. sp_addarticle @publication = N'two_way_pub_test1', @article = N'two_way_test1', @source_owner = N'dbo', @source_object = N'two_way_test1', @destination_table = N'two_way_test1', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL sp_ins_two_way_test2', @del_cmd = N'CALL sp_del_two_way_test2', @upd_cmd = N'CALL sp_upd_two_way_test2', @filter = null, @sync_object = null GO

-- B. Add the transactional publication and article in test2. USE test2 GO

RAISERROR( 'Adding the transactional publication two_way_pub_test2 for database [test2]...', 0, 1 ) GO

-- Adding the transactional publication. sp_addpublication @publication = N'two_way_pub_test2', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication of database test2', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'true', @autogen_sync_procs = N'true', @retention = 72 GO

sp_addpublication_snapshot @publication = N'two_way_pub_test2',@frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 0, @frequency_recurrence_factor = 1, @frequency_subday = 1, @frequency_subday_interval = 0, @active_start_date = 0, @active_end_date = 0, @active_start_time_of_day = 233000, @active_end_time_of_day = 0 GO

RAISERROR( 'Adding publication article [two_way_test2]', 0, 1 ) GO

-- Adding the transactional articles. sp_addarticle @publication = N'two_way_pub_test2', @article = N'two_way_test2', @source_owner = N'dbo', @source_object = N'two_way_test2', @destination_table = N'two_way_test2', @type = N'logbased', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x00000000000000F1, @status = 16, @vertical_partition = N'false', @ins_cmd = N'CALL sp_ins_two_way_test1', @del_cmd = N'CALL sp_del_two_way_test1', @upd_cmd = N'CALL sp_upd_two_way_test1', @filter = null, @sync_object = null GO

-- Step 6: Create subscriptions with Cycle Detection Enabled and Sync Tran update mode. -- A. Add the transactional subscription in test1. Use test1 go

RAISERROR( 'Adding subscription to publication two_way_pub_test1 for database [test2]', 0, 1 ) GO

sp_addsubscription @publication = N'two_way_pub_test1', @article = N'all', @subscriber = @@servername, @destination_db = N'test2', @sync_type = N'none', @status = N'active', @update_mode = N'sync tran', @loopback_detection = 'true' GO

-- B. Add the transactional subscription in test2. Use test2 go

RAISERROR( 'Adding subscription to publication two_way_pub_test2 for database [test1]', 0, 1 ) GO

sp_addsubscription @publication = N'two_way_pub_test2', @article = N'all', @subscriber = @@servername, @destination_db = N'test1', @sync_type = N'none', @status = N'active', @update_mode = N'sync tran', @loopback_detection = 'true' GO

-- Step 6: Test the Application. RAISERROR( 'Script completed successfully', 0, 1 )

Using Query Analyzer you can &quot;break&quot; the preceding example. You can break the example by exploiting the fact that the primary key is not partitioned. The following code example demonstrates this point, which is mentioned earlier in the article:

-- Assuming that you have not already used 500 as a value for pkcol the -- following should break the distribution agents for both publications. insert into test1.dbo.two_way_test1 (pkcol) values(500) insert into test2.dbo.two_way_test2 (pkcol) values(500) Here is the error message that is generated by the Distribution Agent in that case:

Number: 2627 Message: Violation of PRIMARY KEY constraint 'PK__two_way_test1__08EA5793'. Cannot insert duplicate key in object 'two_way_test1'. Repl Agent Status: 3