SQL Server Performance Forum – Threads Archive

Merge Replication – California to Texas

We have a website in ca with that is connected to one SQL Server database. We have a failover site in tx that has a duplication of all the servers in ca. The entire site in tx justs sits there and will only come online if there is a failure in ca. The SQL databases in ca are log shipped to tx every 15 minutes. The company has made a large investment in servers at tx and would like to start using the servers actively rather than having them just sit there waiting for a failure. The original thought was to load balance web transaction through both ca and tx but all the writes to the database would be done in ca only. We would continue to log ship from ca to tx for failover purposes. This would allow the tx web servers to share the load but the tx SQL box would still be for failover only. To go one step further why couldn’t we have transactions come into both sites and if the web transaction came from ca we would write to the ca database but if it came in from tx we could just write to the tx database. For this to work in our environment both databases would have to be in sync. As rows are inserted into the tx database they would also need to be merged into the ca database using merge replication. Since we are new to merge replication we are not sure whether this approach makes sense. Is it possible to keep both database in sync continually? It would work best if the 2 databases were never more than 15 seconds out of sync.

For this scnerio Merge Replication is best option.
It works great except some conflicts which can be minimise with planning and
good database and application design.
-Nilay

…and refer to this MSDN article http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_2ipa.asp for planning and other information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

]]>