Two Way Transactional

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CLM - 29 Nov 2005 18:25 GMT

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.

Reply to this Message

Hilary Cotter - 29 Nov 2005 19:39 GMT

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.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Let's say TableA on a 2000 ServerA.DatabaseA has an identity key and I
> want
[quoted text clipped - 26 lines]
>
> Any tips on how I might do this would be much appreciated.

Reply to this Message

CLM - 29 Nov 2005 20:46 GMT

Thx. I'll try this and see if I can get it to work.

> 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
[quoted text clipped - 42 lines]
> >
> > Any tips on how I might do this would be much appreciated.

Reply to this Message