Sql-server – Would SQL Replication prove to be a FASTER option than downloading compressed DB files every time from servers at different geographic locations

replicationsql-server-2012

My requirement of synchronising two DB servers in different geographic locations is as follows

  1. synchronise some DBs on 2 servers in regular intervals (manual). One in US is an intermediate release server and we need those updated DBs on our local server every time a new build is deployed.
  2. DBs are huge (not very huge though, more or less 30 GB)
  3. Minimum possible action/disturbance on Source server while synchronising

Currently IT team here downloads the compressed database files from server at US to local server to get the latest updated DBs, which takes huge time for SOME of the DBs.

My question is: Would SQL Replication prove to be a FASTER option in this case?

DBs that take much time have only master look up tables but with huge data, which are updated less frequently. But we need to include these DBs as well, so as to keep the process clean and synchronised.

It seems, I cannot use Transactional Replication as not all tables have primary key here. Initially I thought,once replication is set-up, transactional replication anyways runs snapshot first (which would be a one time activity, so its okay even if it takes time) and replicating only the transactions later would not take much time. Hence, it would save heavy downloads every time.

But, if Snapshot replication is the only option left, I am wondering would it even save time over downloading MDFs,as what I understood till now is that snapshot replication will create a complete snapshot of DB everytime and apply them in batches.

I am not a DBA but a developer, and I was thinking by any chance if I can do something to speed-up current process. First thing I could think of, was replication. I have set-up a snapshot replication for a 30 GB DB which has replicated only 1.5 Gb of data in 2 hours. Please suggest.

Best Answer

There are couple of options --

I would not suggest to go with replication as you are making schema changes and it will require to reinitialize the whole replication with a snapshot.

  • Logshipping with standby option:

    • Cheap and effective. Works great and less overhead of maintenance.
    • Things to look out :
      • If an adhoc log backup is taken, it will break the log chain.
      • If you take differential backup then make sure you take full backups with COPY_ONLY option.
      • Your secondary will be readonly - use the with standby option, so you can read from your secondary. See my answer here for more details.

  • SSIS with incremental load

If you are using Enterprise edition then I would suggest you to look into AlwaysON Availability groups.

Related Question