patternsqlMinor
Sync two databases on different servers
Viewed 0 times
databasesserversdifferenttwosync
Problem
I can't pick the right solution for my below requirement. Can you people please advise?
We have two SQL Server 2012 database servers:
While testing our application, the client will find issues/bugs in UAT environment, so we need to replicate to our development environment for further analysis, and to develop fixes for the bugs.
We are planning to sync two databases present in two different places. I am looking to synchronize the database in single direction. It should support write operations.
I have found a few potential solutions:
-
Transaction Log Shipping
In Transaction Log Shipping the secondary database is always in read only/restoring mode, so we can not perform any write operations. I need to do write operations to provide fixes for the issues/bugs. So I think log shipping will not be useful.
-
Database Mirroring and Always On High Availability
As a junior DBA, I don't have much knowledge of these. Please advise if these would fit for me.
-
Scheduling a daily job
Taking a backup of the UAT server and restoring it to our local development server, copying the database backup via FTP.
Can you please advise which solution will be the best fit for this?
Edit:-
I would like to move data changes happening in Database 1(Server 1) to Database 2(Server 2). After moving changes to database 2(Server 2) it should be writable mode. And i don't want to move changes happening in database 2(Server 2) to database 1 (Server 1). So it's a single direction sync.
Below image will gives better understanding.
We have two SQL Server 2012 database servers:
- First one is in Texas (USA). Our client will use this for UAT.
- Second one is in Chennai (India). This one we will use for development/local testing.
While testing our application, the client will find issues/bugs in UAT environment, so we need to replicate to our development environment for further analysis, and to develop fixes for the bugs.
We are planning to sync two databases present in two different places. I am looking to synchronize the database in single direction. It should support write operations.
I have found a few potential solutions:
-
Transaction Log Shipping
In Transaction Log Shipping the secondary database is always in read only/restoring mode, so we can not perform any write operations. I need to do write operations to provide fixes for the issues/bugs. So I think log shipping will not be useful.
-
Database Mirroring and Always On High Availability
As a junior DBA, I don't have much knowledge of these. Please advise if these would fit for me.
-
Scheduling a daily job
Taking a backup of the UAT server and restoring it to our local development server, copying the database backup via FTP.
Can you please advise which solution will be the best fit for this?
Edit:-
I would like to move data changes happening in Database 1(Server 1) to Database 2(Server 2). After moving changes to database 2(Server 2) it should be writable mode. And i don't want to move changes happening in database 2(Server 2) to database 1 (Server 1). So it's a single direction sync.
Below image will gives better understanding.
Solution
User acceptance testing (UAT) usually involves clicking around in an application, which usually means making changes and testing them.
This is especially true if you think you're going to "replicate those issues/bugs to our development environment" - it sounds like you're thinking about synchronizing in both directions.
That requires a writable database, and unfortunately, that rules out transaction log shipping, database mirroring, and Always On Availability Groups. All of those scenarios are designed for read-only secondary databases.
If both sides are going to be changing the data, then in theory, you could use SQL Server's replication techniques (merge, peer-to-peer, bidirectional transactional replication) to push data in both directions. In practice, your developers will be making table schema changes to the development database, and that's likely going to break replication.
This is especially true if you think you're going to "replicate those issues/bugs to our development environment" - it sounds like you're thinking about synchronizing in both directions.
That requires a writable database, and unfortunately, that rules out transaction log shipping, database mirroring, and Always On Availability Groups. All of those scenarios are designed for read-only secondary databases.
If both sides are going to be changing the data, then in theory, you could use SQL Server's replication techniques (merge, peer-to-peer, bidirectional transactional replication) to push data in both directions. In practice, your developers will be making table schema changes to the development database, and that's likely going to break replication.
Context
StackExchange Database Administrators Q#161449, answer score: 3
Revisions (0)
No revisions yet.