Replication with IDENTITY columns

[David Gilbert]

(Long post)

I have a production database with about half the tables using
IDENTITY columns for PRIMARY KEYS. This system is configured as both
a Publisher and a Distributor. We are using Transactional Replication
without updates. The SQL Server Agent runs every hour to pick up any
changes and replicate them to the Subscriber (another SQL Server
machine configured as our failover server).

Both servers are running SQL Server 7.0 (original, no service packs)
under Windows NT 4.0 (SP4).

The failover server (the replication Subscriber) will only be used if
the primary server goes down. And hopefully, only for a short time
before the primary server comes back online. During the time that the
failover is actually being used, the application will not make any
changes to the database.

The IDENTITY values that are replicated need to stay with their
original values. The replication process CANNOT assign new values to
these columns when there are inserted into the database on the
Publisher (i.e. failover server)

My question is: According to the documention, I can add 'NOT FOR
REPLICATION' to the IDENTITY columns and the values will be
preserved. But a collegue of mine says that resets the IDENTITY
sequence on the subscriber and the 2nd time a row gets inserted on
the Publisher, the values get messed up. On his system, he calls a
stored procedure for the tables with IDENTITY columns, and in the
stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then
INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this
approach solved his issues with IDENTITY columns.

Who is right? Do I have to create a stored procedure for replication
for every table with an IDENTITY column, or can I just add 'NOT FOR
REPLICATION' and SQL Server will handle the rest?

NOTE: Upgrading to SQL Server 2000 is NOT an option right now.
Although, if a Service Pack for 7 fixes this, that might be an option.

Thanks in advance for any help you can shed on this issue.

Dave

[Paul]

Put on Service Packs 1 & 2


------------
David Gilbert at 1/16/01 10:02:41 PM

(Long post)

I have a production database with about half the tables using
IDENTITY columns for PRIMARY KEYS. This system is configured as both
a Publisher and a Distributor. We are using Transactional Replication
without updates. The SQL Server Agent runs every hour to pick up any
changes and replicate them to the Subscriber (another SQL Server
machine configured as our failover server).

Both servers are running SQL Server 7.0 (original, no service packs)
under Windows NT 4.0 (SP4).

The failover server (the replication Subscriber) will only be used if
the primary server goes down. And hopefully, only for a short time
before the primary server comes back online. During the time that the
failover is actually being used, the application will not make any
changes to the database.

The IDENTITY values that are replicated need to stay with their
original values. The replication process CANNOT assign new values to
these columns when there are inserted into the database on the
Publisher (i.e. failover server)

My question is: According to the documention, I can add 'NOT FOR
REPLICATION' to the IDENTITY columns and the values will be
preserved. But a collegue of mine says that resets the IDENTITY
sequence on the subscriber and the 2nd time a row gets inserted on
the Publisher, the values get messed up. On his system, he calls a
stored procedure for the tables with IDENTITY columns, and in the
stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then
INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this
approach solved his issues with IDENTITY columns.

Who is right? Do I have to create a stored procedure for replication
for every table with an IDENTITY column, or can I just add 'NOT FOR
REPLICATION' and SQL Server will handle the rest?

NOTE: Upgrading to SQL Server 2000 is NOT an option right now.
Although, if a Service Pack for 7 fixes this, that might be an option.

Thanks in advance for any help you can shed on this issue.

Dave

[R.Talabis]

Greetings! I am also dealing right now with replication. But my problem seems to be different and much simpler than the one in this post. I can't seem to replicate the Foreign Key constraints or relationships for that matter. I would greatly appreciate your help on this matter. Thanks.. Ryan

------------
David Gilbert at 1/16/01 10:02:41 PM

(Long post)

I have a production database with about half the tables using
IDENTITY columns for PRIMARY KEYS. This system is configured as both
a Publisher and a Distributor. We are using Transactional Replication
without updates. The SQL Server Agent runs every hour to pick up any
changes and replicate them to the Subscriber (another SQL Server
machine configured as our failover server).

Both servers are running SQL Server 7.0 (original, no service packs)
under Windows NT 4.0 (SP4).

The failover server (the replication Subscriber) will only be used if
the primary server goes down. And hopefully, only for a short time
before the primary server comes back online. During the time that the
failover is actually being used, the application will not make any
changes to the database.

The IDENTITY values that are replicated need to stay with their
original values. The replication process CANNOT assign new values to
these columns when there are inserted into the database on the
Publisher (i.e. failover server)

My question is: According to the documention, I can add 'NOT FOR
REPLICATION' to the IDENTITY columns and the values will be
preserved. But a collegue of mine says that resets the IDENTITY
sequence on the subscriber and the 2nd time a row gets inserted on
the Publisher, the values get messed up. On his system, he calls a
stored procedure for the tables with IDENTITY columns, and in the
stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then
INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this
approach solved his issues with IDENTITY columns.

Who is right? Do I have to create a stored procedure for replication
for every table with an IDENTITY column, or can I just add 'NOT FOR
REPLICATION' and SQL Server will handle the rest?

NOTE: Upgrading to SQL Server 2000 is NOT an option right now.
Although, if a Service Pack for 7 fixes this, that might be an option.

Thanks in advance for any help you can shed on this issue.

Dave

[Dave]

Dave,
I am trying to understand your problem..
Lets say you have Server A (Pub & Dist) which is your primary database server accessed by application of data access and modificaiton.

Then you have a server B (Sub), which is basically to have a warm backup of Server A.

Now when Server A is down , your application switches to Server B..however it does not put any new rows(after server A is down)in Server B and just uses Server B for data access.

Again when Server A is up, you start working with the data you previously had in Server A( before it went down).

If this is your requirement you just need to set "Not for replication" for hte Identity column so that Server B does not insert its own value but retain the Identity value in Server A.

If you are doing something else ( other than what I understood) to write back...

Vijay

------------
David Gilbert at 1/16/01 10:02:41 PM

(Long post)

I have a production database with about half the tables using
IDENTITY columns for PRIMARY KEYS. This system is configured as both
a Publisher and a Distributor. We are using Transactional Replication
without updates. The SQL Server Agent runs every hour to pick up any
changes and replicate them to the Subscriber (another SQL Server
machine configured as our failover server).

Both servers are running SQL Server 7.0 (original, no service packs)
under Windows NT 4.0 (SP4).

The failover server (the replication Subscriber) will only be used if
the primary server goes down. And hopefully, only for a short time
before the primary server comes back online. During the time that the
failover is actually being used, the application will not make any
changes to the database.

The IDENTITY values that are replicated need to stay with their
original values. The replication process CANNOT assign new values to
these columns when there are inserted into the database on the
Publisher (i.e. failover server)

My question is: According to the documention, I can add 'NOT FOR
REPLICATION' to the IDENTITY columns and the values will be
preserved. But a collegue of mine says that resets the IDENTITY
sequence on the subscriber and the 2nd time a row gets inserted on
the Publisher, the values get messed up. On his system, he calls a
stored procedure for the tables with IDENTITY columns, and in the
stored procedure, he executes a 'SET IDENTITY_INSERT OFF', then
INSERTS the row, then 'SET IDENTITY_INSERT ON'. He claims that this
approach solved his issues with IDENTITY columns.

Who is right? Do I have to create a stored procedure for replication
for every table with an IDENTITY column, or can I just add 'NOT FOR
REPLICATION' and SQL Server will handle the rest?

NOTE: Upgrading to SQL Server 2000 is NOT an option right now.
Although, if a Service Pack for 7 fixes this, that might be an option.

Thanks in advance for any help you can shed on this issue.

Dave