1

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.

4
  • What is your intention for the second server? Are you doing this for disaster recovery? High availability? Reporting?
    – Hannah Vernon
    Commented Oct 1, 2015 at 4:20
  • I want the second Server to be as Test Environment, where the data is synced from first server, but here if i do nay changes on the second serve it shouldn't reflect on the first one?? Any help how to do so?
    – Omran
    Commented Oct 1, 2015 at 6:47
  • @Omran, your both Server are in DC site or one is DC site and other one in DR site. Commented Oct 1, 2015 at 10:40
  • They're both in One DC & both are running on VM's
    – Omran
    Commented Oct 1, 2015 at 12:52

2 Answers 2

2

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).

6
  • But I want the second Server to be as Test Environment, where the data is synced from first server, but here if i do any changes on the second serve it shouldn't reflect on the first one?? Any help how to do so? or what's he appropriate way to cater this?
    – Omran
    Commented Oct 1, 2015 at 6:49
  • @Omran see my edit.
    – kayess
    Commented Oct 1, 2015 at 10:00
  • Thanks a lot for the explanation, but how am gonna configure the Replication Agent in DB-1 and let the Data Flow to DB-2? appreciate if you could guide me through steps?
    – Omran
    Commented Oct 3, 2015 at 10:43
  • Please describe exactly which replication type do you want to adopt to your system.
    – kayess
    Commented Oct 3, 2015 at 13:22
  • I'll be using Transaction Replication with 3-hours latency, keeping in mind the data flow will be one way only, for example from DB-1 to DB-2 ONLY & NOT from DB-2 to DB-1. Hoping you have a clear idea now on what am looking for exactly?
    – Omran
    Commented Oct 3, 2015 at 20:53
1

You have three options out of the box:

  1. SQL Server Replication
  2. SQL Server AlwaysOn Availability Groups
  3. Database/log backups via TSQL and SQL Agent jobs.

Replication and AlwaysOn can be configured from TSQL or SSMS, both require administration. Only thing you didn't mention is what the desired latency is between the two, and what the availability of the second server needs to be. If you need real-time, or near real-time, then the above would work. If the second server can be stale for quite some time, then you have other options, simplest would be plain old backup restore via SQL Agent jobs.

4
  • But I want the second Server to be as Test Environment, where the data is synced from first server, but here if i do any changes on the second serve it shouldn't reflect on the first one?? Any help how to do so? or what's he appropriate way to cater this?
    – Omran
    Commented Oct 1, 2015 at 6:49
  • replication supports both options - changes can go one way, or both ways, depending on how you configure it. You never mentioned your latency requirements, so transactional or snapshot replication would work. AlwaysOn supports changes at the primary replica only.
    – Greg
    Commented Oct 1, 2015 at 13:15
  • the latency requirements is whenever any records inserted in DB-1 it show reflect in DB-2 in 6 hours time, i don't want immediate replication, so that i can rollback the changes that's made
    – Omran
    Commented Oct 3, 2015 at 11:26
  • 1
    For a test environment, if all you want to do is refresh every six hours, IMO the simplest and easiest thing to do is database backup/restore via TSQL in SQL Agent jobs. Ensure on server 1, you have regularly scheduled database/log backups being taken, and placed/copied to a folder that test server has access to. On test server, create job to restore the db data or log file(s) every six hours. That will be much simpler than the administration overhead of replication/alwayson.
    – Greg
    Commented Oct 3, 2015 at 13:07

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.