SQL Database Replication....

I need some advise from the group.

I have developed a piece of software that uses MS SQL Server to store my application database. My software can be used by companies that have multiple offices in different countries, and this is fine as they write back to the SQL database in one of the locations. However I have one specific company who is using my software in both Toronto and Mumbai, the database is located in Mumbai. Normally this would not be a problem apart from the connection from Toronto to Mumbai has a 200ms latency. As you could imagine this causes huge response problems when they run my software from Toronto. What I want to be able to do is do some kind of database replication, therefore there would be an SQL database in both Toronto and Mumbai and you would connect to the database in your location. Doth of these databases would need to have read write access to them. What I then want to happen is any changes to the tables whether deletions, inserts or updates to also be replicated in the other location. I have already thought of a way to control which location has access to certain records. therefore stopping both locations from modifying the same record.

My question is how I can do this? Does MS SQL Server have this functionality already built in, the company is using MS SQL Enterprise? What would I have to change to my currently table structures to enable this to work?

Any help advise would be really appreciated.

Simon

Oooooh boy...
Short answer is, yes, SQL Server can do it... you may or may not have to make some changes to your database. Theoretically, it's not that difficult, you set one up as a Master database, and the other as the subscriber database, then set up a schedule to for the replication.

The realities is that it can be a mess. I've done some replication before, in the past, and I try not to do that any more if I can help it. One of the biggest problems is the the Primary Keys.

I would look into possibly a third-party high-speed data center hosting option. That's what we did for a couple of clients a couple lifetimes ago, eliminating the need for replication and with their T-3 connection (they had the next best thing to a direct pipe into the internet.)


Just google "SQL Server Replication" ... you should get more information than you really want: Types, steps, and even tools to help.

-tg