One way transactional replication accross two datacenters

Question

question

Joe 2 asked Dec 10, '09

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-2005 transaction-log

10 |1200

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

Answer 1 · 2009-12-10T16:12:29Z

Kristen answered Dec 10, '09

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

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

question