Bi Directional Transactional Replication

Okay I posted this yesterday I believe:
“Let's say TableA on a 2000 ServerA.DatabaseA has an identity key and I want
to transactionally replicate that TableA to a ServerB.DatabaseA (that is also
2000). But here's the catch: in the case of a significant outage on ServerA
I want to be able to repoint the web to ServerB.DatabaseA and have everything
function successfully.
A previous responder suggested (I think) that I could simultaneously set up
transactional replication from ServerA.DatabaseA to ServerB.DatabaseA and
from ServerB.DatabaseA to ServerA.DatabaseA and have one database have the
identity on serverA with a seed of B using odds and the identity on serverB
using a seed of evens for example.
Here are my questions:
1) Will Sql Server 2000 let you set up this kind of quasi-two-way
transactional replication?
2) If the answer to #1 is 'yes', then how can you have identities set up on
both tables? Won't replication throw up if you're copying from an identity
column on ServerA to its sister table on ServerB that also has an identity
column?
3) Would this setup be much easier to implement than standard merge
replication?
Any tips on how I might do this would be much appreciated.”
And Hilary very kindly posted this:
“1) Yes, but you have to create the objects in advance on both sides and use
the not for replication switch on the identity colummns. You must also set
the identity increments and seeds. Then you must run dbcc
checkident(tablename, reseed, correctvalue) to fix everything.
2) replication doesn't throw up per se. Basically when you create your
publication and get to the specify articles dialog box, click on the browse
button to the right of the table, and in the snapshot section, select keep
existing table unchanged. Run the checkident to ensure that you are getting
odds on the correct sides, and evens on the other.
3) Standard merge is simpler to set up. It have typically 1 to 2 minutes of
latency whereas with bi-directional transactional on a system under load you
are looking at about 10-20s. Merge replication is harder to troubleshoot and
recover from.”
So I tried to do what Hilary said:
I set up Not for Replication, Seed=1 and Increment=2 on the identity field
on ServerA.TableA after setting up transactional replication from
ServerA.TableA to ServerB.TableA. I then manually ran the snapshot and it
sent over the table to ServerB.TableA. It created a non-identity column on
ServerB.TableA, however, so I had to go in and manually change ServerB.TableA
to Not for Replication, Seed=2 and Increment=2.
Now here’s the problem:
When I insert a record into ServerA.TableA, it does not migrate over to
ServerB.TableA.
What am I doing wrong?
This type of setup is possible. Replication performs identity inserts as you
set up the columns with 'Identity (not for replication)'.
Is it easier than merge? Probably I'd say a little more difficult although
neither are really difficult. The issue is that you have to do the setup
manually whereas merge can be done with the wizard. It'll perform much
better than merge, but merge will cope with schema changes better. Anyway,
here's a couple of articles to help you out:
http://support.microsoft.com/default...b;en-us;820675
http://msdn.microsoft.com/library/de...lsamp_3ve6.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)