Transactional Replication and IDENTITY

Hilary Cotter

2007-04-19 12:23:11 UTC

This is a complex issue.

On SQL 2000 you should do the following:

1) use EM to change the identity property to not for replication on every
table containing identity columns on your publisher. Also do the same for
triggers and constraints.
2) change the identity increment to 2.
3) use dbcc checkident(tablename) to display the current assigned value
4) use dbcc checkident(tablename) reseed to set the current assigned value
to an odd value if the current value is even.
5) kick your users off the database. If you can't do this create a
publication and no-sync subscription but ensure your distribution agent is
disabled.
6) backup your database and restore it on the subscriber.
7) use dbcc checkident(tablename) to display the current assigned value
8) use dbcc checkident(tablename) reseed to set the current assigned value
to an even value if the current value is odd.
9) create a dummy publication with a sync subscriber and use
sp_scriptpublicationcustomprocs to generate the replication procs. Run the
results of this on both sides.
10) enable your distribution agent. You may have to run with the continue on
data consistency profile for a while and you may need to do a validation to
determine which tables are out of sync.
11) Publish the DR site back to the main site.
12) modify your update procs with the sp_scriptdynamicupdproc which will
prevent updates to the indentity column

Post by q***@yahoo.co.uk
Hi,
SQL 2000 ENT; SP4.
Thre setup here is two databases, each with over 600 tables. Each
table has a UID column that is an IDENTITY (1,1) column. Thge
databases are on a SQL Cluster and we want to replicte to a DR Server.
I setup transactional replication and create a publication and
subscription for each database and this works fine. The issue is when
we come to test the databases on the DR server. We stop replication
and try and edit some data however it is messed up as the IDENTITY on
each UID column has not been copied across, nor has the primary key.
I have tried to use NOT FOR REPLICATION however this has not worked.
1. Do I need to run a script before we start editing data on the
subscriber (after we have stopped replication) so that I can reapply
the primary keys and identity?
2. Can transactional replication copy across a column's identity?
3. When I setup a subscription and select the "subscriber already has
schema and data" the replication doesn't work and I get a "cannot find
sp_msins<tablename>" error.
5.2.20 New Schema Option for Replicating Identity Columns in
Transactional Publications
Introduced in SP4
In previous releases, identity columns in transactional publications
were replicated as the
base data type, such as int, without the identity property set. This
approach is
appropriate for applications that do not allow inserts at the
Subscriber. SQL Server 2000
SP4 introduces a new schema option (0x4) for transactional
publications, which is used to
replicate the identity column as an identity column. This is useful in
a number of cases,
including bidirectional replication and using the Subscriber as a warm
standby server. In
these cases, inserts can occur at the Subscriber, and the inserts
result in the identity
column being incremented.
To specify that an identity column should be replicated as an identity
When creating the table at the Publisher, specify the NOT FOR
REPLICATION option for the
identity column. This ensures that only user inserts, not replication
agent inserts,
increment the identity column. For more information, see "CREATE
TABLE" in SQL Server Books
Online.
When adding an article with an identity column, set the option 0x4 for
parameter of sp_addarticle. For more information about this parameter,
see "sp_addarticle"
in SQL Server Books Online.
After initializing the Subscriber, execute DBCC CHECKIDENT for each
table with an identity
column. This allows you to specify a starting value for inserts into
the identity column at
the Subscriber, so that the values inserted will not be the same as
those inserted at the
Publisher. For example, you could specify that inserts at the
Subscriber should start at
USE Northwind
GO
DBCC CHECKIDENT ('Employees', RESEED, 1000000)
GO
For more information, see DBCC CHECKIDENT in SQL Server Books Online.
Given the above steps would I have to create publication via scripts
since I have to specify the option 0x4 in sp_addarticle?
Please help!
Thanks
Scott