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

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 "Nonpartitioned, Bidirectional, Transactional Replication", which is described in the "Nonpartitioned, Bidirectional, Transactional Replication" 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:

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.
  
  
• 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.
  
  
• 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.

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 "Not 
--  For Replication" 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 "Not For Replication" 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 "break" 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:

REFERENCES

For more information, see the "Using IDENTITY Values with Replication" and "article Properties, Identity Range Tab" topics in SQL Server Books Online.