sql and syncronisation

Question

HI all, Long time listener, first time caller as they say :D Right - i know very little about sql and i have been asked some questions related to it, allow me to explain a little

 

The company i work for is using an application that relies on sql to manipulate data - now the company wishes to move to a external hosting solution, with this in mind their thoughts go something like this - their idea is to have the sql server hosted at the hosting center and the offices around the globe to connect up and talk to it - however it looks like this could be a little ott and will cause massive latency from clients perspective as they try and perform sql related operations, so the idea was put out there that maybe there could be satellite type sql servers at each office - each user at their respective office would talk to the local sql server, to which in turn it would then talk to the master held at the hosting center....ok i thought - i haven’t a clue how to even begin to do that, but it does sound feasible....then came the question "If i was at one of the offices and ran a report to tell me how many samples the group as a whole has in progress - would that then communicate with the master sql at the hosting center ?  In which case how could we be sure the information returned was up to date with regards to what information the other sql servers have that haven’t maybe pushed their updated information to the master at the hosting center ?

or does it all depend on how often you set the satellite sql servers to synchronise their data....

Forgive me if this all seems a little garbled - as really my knowledge of sql extends as far as installing it and running backups using the maintenance plan lol

Answer 1

Jed,

It sounds like you want replication setup between branch offices and a central hosting provider (or datacenter). It is very possible to do this with your own software and not replicate but use another installation of sql server to do periodic or near real time updates, but there would be no need to reinvent the wheel. This is an overview for 2008: HERE

There are a few different types of replication of which I would focus on Transactional and Merge. From what it sounds like in your post, I believe you'll want to look more specifically at transactional replication, such as peer-to-peer.

Hope this helps,
-h