6

Disclaimer: I have no idea what I'm talking about here, or if what I'm doing is the correct thing to do.

Long story short, my superior has asked me if it's possible to replicate our internal database on another SQL Server instance in another geographical site and keep them both in sync.

For context, they've recently expanded the business and have opened a new building (we'll call this Site B) about 15 miles away from the main building (Site A). Both sites are connected by a VPN.

At Site A, we have a MSSQL 2012 Standard install which has our internal database.

When users from site B are connecting to the database over the VPN, they're reporting that it takes nearly 5 minutes to establish a connection and open the client application. I'll be checking this tomorrow since I've never heard this problem reported before.

My superior seems to think the best idea would be to somehow replicate/mirror (I'm not sure of the terminology here) the database on the server in Site B and somehow keep them both in sync.

Is this what replication is for, and if so, where can I start learning how to replicate the database.

Otherwise, if this is a very bad idea, what other options do I have?

tl;dr - Can I have two copies of a database, kept in sync, in two different buildings?

Improve this question
3
  • Yes, they both would need to be queried.
    – Jake
    Commented Dec 21, 2015 at 20:27
  • @Jake Are you running inserts/updates/deletes on the replicated site or are you just running selects? You also want to know how much log is produced by your sql server to know if your VPN connection is sufficient to move it in a timely fashion.
    – Nicolas de Fontenay
    Commented Dec 21, 2015 at 20:32
  • @ndefontenay They're running inserts/deletes/updates as well, you're probably looking at maybe 500-1000 operations per typical day. Most of those operations are triggers though.
    – Jake
    Commented Dec 21, 2015 at 20:41

1 Answer 1

Reset to default
5

Based on the answers in the comment section, you are looking at a bidirectional transactional replication.

Microsoft document its implementation at this location: https://support.microsoft.com/en-us/kb/820675

Using the name "Bidirectional Transactional Replication" in your research should help you identify how-tos and step by step tutorials.

Since it's bidirectional replication, you will experience conflicts (a row updated here and there at the same time).

The document explains all of that very well. It's a good start.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.