SQL Server 2012 – How to Replicate Data Between Two Servers

replicationsql serversql-server-2012

I have two SQL Server 2012 databases running on two different Windows Server instances, I want the Server A (DB1) to be in full sync with the new Server B (DB2).

How can I make this connection between them?

Is there any way to do this through SQL management Studio??

Note: whatever I do on Server B shouldn't replicate to Server A.

Best Answer

Lets take a quick look at the official Replication features page:

Transactional replication. For more information, see Transactional Replication.

Merge replication. For more information, see Merge Replication.

Snapshot replication. For more information, see Snapshot Replication.

In short:

  • Transaction replication: incremental changes between the databases.
  • Merge replication: like transactional but with the subsequent changes.
  • Snapshot replication: means a full copy/overwrite of the given database. Basically this is used for creating the initial replicated instance of your database.

You will also have to overview and setup the SQL server agents for replication.

For a basic technological understanding the SQL Server replication mechanism utilizes the Publisher-subscriber pattern.

EDIT: i was intentionally mentioning the used pattern for replication, let me elaborate on it a bit more.

Architectural overview of the basic transactional replication:

Transactional replication architectural graph

Summarizing simplified the flow of operations:

  1. Publisher (in your case DB-1, the source database server) makes the data ready for replication.
  2. Distributor (the chosen replication agent) is the intermediary between the publisher and subscriber (note the pattern mentioned above).
  3. Subscriber (in your case DB-2, the destination database server) which receives the published data.

This way you replicate your data from DB-1 to DB-2, and not backwards (though you can configure it to do that too, if you may want).

Related Question