Sql-server – Sync two databases on different servers

Architecturedata synchronizationsql serversql-server-2012

I can't pick the right solution for my below requirement. Can you people please advise?

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.

Best Answer

For simplicity if this was for a limited time frame, i would go with backup and restores.

Unless you are required to respond within minutes you could probably get by with daily backups. You could even set up a job to take a backup and FTP it on demand, after they have reported a defect.

I wouldn't even bother restoring daily unless the issue was specific to newly created data. In my experience UAT is more about functionality. So you test with existing data and create new data. Having a db a few days behind theirs means you should be able repeat the test and reproduce the defect.

This approach would get slower, more difficult and less feasible the bigger the db is.

If you will be providing ongoing support, the db is huge, and you have SLA saying you have to respond within minutes, replication/synchronization of some sort might be worth the effort to set up.

The best solution for you may be dictated by client security or technical policies. Work with them to ensure what you are proposing is acceptable. If not keep in mind there are lots of variations on this theme. But it all boils down to: 1. you need something to take a back up, and 2. you need something to FTP the files either on a schedule or on demand.

If I was doing this personally I would begin with Ola Hallengren's backup scripts. After installing that I'd create a SQL Agent job which invokes the backup.

I would then edit the agent job to add a step to FTP the files. I just had a quick search and found this example of how to use SSIS to FTP a file and I found this example of an FTP script for SQL Server.

My recommendation is that you set this job up to run on a schedule, and only run it on demand if truly required. If you don't have remote access and permission to execute tasks, you can request someone on the client site execute the task for you.

Related Solutions

Sql-server – Syncing two databases in SQL Server

Well I might not get it, but I try to answer it.

You said you need a high performance solution which runs often (minimum all 2 minutes) and you need a good approach which should be fast without locking. But you don't want a blackbox system.

Instead of a blackbox system, which is used on millions of installations with good results, you try to invent the wheel again and build your own solution? Hm, sounds a bit weird.

In fact these are my suggestions.

Replication even if you said you won't use it. It's quite the easiest and best solution you can use for this. The replication is easy to setup, replicate fast and you don't have to invent the wheel again. If you just weird about locking, you may try to set the ISOLATION LEVEL to READ_COMMITTED_SNAPSHOT. You can read more about it here. This will use up a part of your tempdb, but your table is always read- and writeable and the replication can work in the background.

See the example below:

ALTER DATABASE yourDatabase SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE yourDatabase SET READ_COMMITTED_SNAPSHOT ON

CDC (Change Data Capture) can also be a solution. But this way you need to build nearly everything on your own. And I've made the experience that CDC can be a fragile thing in some circumstances. CDC will capture all data on a watched table (you need to specify each watched table manually). Afterwards you'll get the value before and the value after an INSERT, UPDATE or DELETE. CDC will hold back those information for a period of time (you can specify it on your own). The approach could be to use CDC on certain tables you need to watch and manually replicate those changes to the other database. By the way, CDC uses the SQL Server Replication under the hood too. ;-) You can read more about it here.

Warning: CDC will not be aware of DDL-changes. This means, if you change a table and add a new column, CDC will watch the table but ignore all changes to the new column. In fact it only records NULL as value before and value after. You need to reinitialize it after DDL-Changes to a watched table.

The way you described above is something like capturing a workload using SQL Server Profiler and run it again on another database for some benchmarks. Well it could work. But the fact that there are too many side effects is a bit too heavy for me. What do you do if you capture a procedure call on your client. Afterwards running the same command at your principle database as it is out of sync? The procedure may run through, but it may delete/update/insert rows which were not present in your client. Or how do you handle multiple clients with one principle. I think this is too tricky. In the worst case, you probably destroy your integrity.
Another idea could be application based or using a trigger. Depending on how many tables you want to be synced. You can write all changes to a separate staging table and run an SQL Server Agent Job all x Minutes to sync those rows in the staging table with your master. But this may be a bit to heavy if you try to sync (e.g.) 150 tables. You would have a big overhead.

Well these are my 2 cents. Hopefully you have a good overview and maybe you found one solution which works for you.

Sql-server – Doubt in Log Shipping

This article covers the topic: https://msdn.microsoft.com/en-us/library/ms178117.aspx

Basically, you have to configure log shipping from the former secondary (now primary) server to the former primary (now secondary) server.

Best Answer

Related Solutions

Sql-server – Syncing two databases in SQL Server

Sql-server – Doubt in Log Shipping

Related Question