SQL Server 2005/2008 International Real-Time Availability

Question

My company recently opened an office in Singapore. I work on the web application we use internally to manage the company, using a SQL Server 2005 back-end. Because of slow access over the net from Singapore, we're shipping them a server that will host a local copy of the web app and database and we want to keep their local version of the database synchronized with ours. Both the US app and the Singapore app can read/write data.

Is merge replication the right solution for this problem? This is the path I've been investigating and it seems the closest fit for what we want. Is there something better in SQL Server 2008? What type of database availability solutions have you implemented for international collaboration?

Thanks for your thoughts!

Answer 1

Merge replication is way to go if some people in both locations have to update any data. But...

I could recommend using of peer-to-peer replication in the case if:

• Office1 (US?) needs to update/delete only records inserted by Office1 apps.

• Office2 (Singapore) needs to update/delete only records inserted by Office2 apps.

Both offices can query all data.

Quote: The most basic concept you need to understand in peer-to-peer replication is that every server contains all of the data but each server is responsible for updating only its own subset of data. Thus, every server carries the same schema and each server is a subscriber to all the changes that happen on the other servers while being a publisher of its own changed data. When data changes on one server, those changes go out to all the subscribers in the peer-to-peer network. Each server contains and updates data specific to its geographic location and also sees all the data from the other locations. A key part of peer-to-peer replication is that each server is responsible for changing its own data set and no other location can change any of the data in that data set. If this rule is violated, the data could be changed in two places, and because there's no data locking between sites, when the data is replicated, you can end up with inconsistent results.

More info you can find here: http://www.sqlmag.com/Article/ArticleID/49241/sql_server_49241.html

http://technet.microsoft.com/en-us/magazine/2006.07.insidemsft.aspx

http://www.sql-server-performance.com/articles/dba/peer-to-peer_replication_p1.aspx

How it can be implemented.

Tables which must be updated from both locations you have to duplicate, for example:

TableOrders1 (OrderID int IDENTITY(1,2) NOT NULL, Col1 int, Col1 nvarchar(), etc.) 
TableOrders2 (OrderID int IDENTITY(2,2) NOT NULL, Col1 int, Col1 nvarchar(), etc.)

Then you send data from TableOrders1 to Office2, data from TableOrders2 to Office1 using peer-to-peer replication and so on…

To query data you can create a view.