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?
Best Answer
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.