Transactional replication for failover - Microsoft describe this at
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog03.mspx
and include discussion of how to configure identity columns, with NOT FOR
REPLICATION on the subscriber.

However, http://www.replicationanswers.com/Transactional.asp suggests this
won't work when you failover, as with "Identity - Yes (not for replication)"
the identity value isn't incremented by the replication process, and DBCC
CHECKIDENT wont reseed these columns. So when you failover, the next identity
you get will be 1, rather than (say) 2045, so causing a primary key violation
- is that right ?

Has anyone implemented transactional replication for failover, in the way
Microsoft describe ? Does it work ? If not, what is the suggested approach ?

Thanks,

Ive always talked employers out of Transaction Replication for any sort of
DR plan. Aside from the Identity problem, there's foreign keys(I dont copy
them in replication), triggers, etc. Also, sometimes people add columns to
tables without having them replicated. Plus replicating Stored Procedures is
a joke. Log Shipping is the way to go if your on a tight budget IMO. You
dont even need Enterprise Ed. if your willing to code your own:

http://www.windowsitpro.com/Article/ArticleID/15804/15804.html




[quoted text, click to view]

Andy,
in straightforward transactional replication, the identity attribute isn't
transferred at all to the subscriber. If you have it there through eg a
nosync initialization, DBCC CHECKIDENT can indeed be used to reseed - BOL
has an error in this regard, which I've logged. However this is a manual
process. The easiest way to have it all work simply is to use queued
updating subscribers and drop the replication settings on failover.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Yes - I had a PSS guy do some research to confirm that BOL is indeed wrong
on this count. However, plain transactional replication doesn't transfer the
Identity at all so you have to either use nosync or queues to get it set up.
I prefer queues, because your (nice!) script doesn't need to be run, and
failback is a possibility by running the queue reader.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Strange!

Setting up plain transactional replication on Northwind, and after failover,
sure enough the next identity value is wrong (still 1), causing a primary key
violation.

However, running DBCC CHECKIDENT ('Categories',RESEED) does indeed do a
re-seed, even with Not For Replication set, contrary to what BOL says. This
then allows inserts into the table, and gets the next identity value.

To do this on all tables:

--Generate a query that can be used to reseed all tables
SELECT 'DBCC CHECKIDENT ('''+ O.name +''',RESEED)'
from sysobjects O
INNER JOIN syscolumns C on O.id = C.id
WHERE o.type='U' AND objectproperty (o.id, 'TABLEHASIDENTITY') = 1
AND columnproperty (o.id, c.name, 'IsIdentity') = 1
ORDER BY O.name

Result:
DBCC CHECKIDENT ('Categories',RESEED)
DBCC CHECKIDENT ('dtproperties',RESEED)
DBCC CHECKIDENT ('Employees',RESEED)
....etc...

Copy & paste the result into query analyser and execute, to re-seed all
tables.


Regards,
Andy Mackie.

[quoted text, click to view]