SQL database synchronisation / live synchronisation

antalp71
antalp71 used Ask the Experts™
on
Hi there

We are looking into getting a new in office recruitment sql database.  The company has told us the best way forward if we open two offices would be for the second office to remote in via terminal services or rww.  However in the past I have worked for companies that have several offices and they sync their databases overnight or use live synchronisation.


We have a normal broadband line, 0.2mb upload / 1mb download which we cannot alter as it is in a serviced office.  I wanted to know if it was possible and what would be involved or needed.  Is it costly?

Many thanks for your help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017

Commented:

Author

Commented:
Thanks for the quick reply.  I actually have to merge two office based databases on a daily basis and from your links I see this is possible via the internet.  Is it possible for this to be programmed to carry out automatically?
Distinguished Expert 2017
Commented:
I think it is part of the configuration, the internet synchronization is only available if your sql server and windows server are enterprise versions.
The problem with performing the merge at the end of the night versus having the merge operations occur on a regular basis, is that you are running a higher probability that two changes to the same record was made and would need to be reconciled when the merge process occurs.
i.e. someone at the branch office made a change to account number 10245465.
Someone at the main office made a change to account number 10245465 as well.
Depending on how you define/setup the conflict resolution logic, the changes made at the branch might be discarded in favor of the changes made at the main office.

http://www.replicationanswers.com/Merge.asp

The other issue is that you would have to transfer a large set of data at one time.
I.e. every 15-30 minutes you may need to transfer 300K of data each way to reflect the active changes.  If you wait till the end of business or at night, you may end up having to transfer 2-3GB of data which would need to be processed.

Author

Commented:
Thank you for your help and the links.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial