April 22, 2009 at 4:46 pm
My co-worker proposed the idea of setting up a SQL 2005 configuration where we have two database servers, connection strings from the application servers will point to a DNS entry configured for round-robin. Writes to the database would go through a server with transaction coordinator to write out to the two servers equally so they are up to date.
Am I mistaken or is she misinterpreting what DTC does? For this kind of configuration, I would think you would need to use transactional or merge replication. I thought DTC only applied to SQL clusters, or that we would have to put in a lot of effort to make DTC do this. If I have this wrong, can someone explain how this would work, or link me to an article with more information? The pages for DTC on msdn don't seem to touch on this kind of setup... although I may not have been digging deep enough.
Any extra information as to how this would work would be greatly appreciated.
April 29, 2009 at 11:10 am
Any thoughts from anyone here?
April 29, 2009 at 12:07 pm
I'm *mostly* certain you're right. I'll check with some co-workers. This isn't my forte.
I'm surprised noone else has responded yet.
---
Dlongnecker
April 29, 2009 at 12:31 pm
icarus (4/22/2009)
My co-worker proposed the idea of setting up a SQL 2005 configuration where we have two database servers, connection strings from the application servers will point to a DNS entry configured for round-robin. Writes to the database would go through a server with transaction coordinator to write out to the two servers equally so they are up to date.Am I mistaken or is she misinterpreting what DTC does? For this kind of configuration, I would think you would need to use transactional or merge replication. I thought DTC only applied to SQL clusters, or that we would have to put in a lot of effort to make DTC do this. If I have this wrong, can someone explain how this would work, or link me to an article with more information? The pages for DTC on msdn don't seem to touch on this kind of setup... although I may not have been digging deep enough.
Any extra information as to how this would work would be greatly appreciated.
DTC will play part in "DISTRIBUTED" transactions.
DTC is a Resource Manager(RM) that can talk to any other RM that understand the two-phase commit protocol.
To run such scenario Read this Example on MSDN
Take a look at how the scope controls commit or rollback on BOTH Servers.
Now this proposition is very expensive because you are forcing two-phase commits always.
Also you could achieve similar results by using TSQL BEGIN DISTRIBUTED TRANSACTION on one SQL Server that performs DML on a Linked Server and it will require simple normal Transaction control on client-side because SQL Server will do the work for you.
Replication comes in handy on these scenarios but there is a latency involved
DBMirroring can also help but the mirrored side can not be queried unless you have DBSnapshots on it.
Hope it helps
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply