question

Joe 2 avatar image
Joe 2 asked

One way transactional replication accross two datacenters

Hello,

With MSSQL 2005, is this architecture possible?:

One database instance in site A and another instance in site B

Application in site A updates SQL database in site A

Application in site B updates SQL database in site B

One-way transactional replication pushes Site A updates to site B

One-way transactional replication pushes Site B updates to site A

The idea behind this architecture is for HA/DR purposes. BlackBerry Enterprise Server for Exchange is the application utilizing the database instnaces in both locations. I guess the bottom line question is "Can a SQL database instance be a source and reciever of independent sets of database updates via one-way transactional replication?"

sql-server-2005transaction-log
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kristen avatar image
Kristen answered

Yes, I think you can design . build a SQL2005 replication system this way. It's what I would call a two-phase commit. For each site, A & B, each transaction is applied to the local database and, as part of the transaction, it is also applied to the remote database. If applying to the Remote fails then the whole transaction fails (and the local transaction is rolled back).

I seem to remember that the application got an additional record-set back - so you get the 1 record(s) effected message twice, instead of jsut once, which might need some application changed (or possibly just the use of SET NOCOUNT ON)

But I have to say that I'm very hazy on the details as it is years since we fiddled with a system like this.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.