0

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.

  • 1
    Which edition of SQL Server 2012 are you on? Have you considered log shipping? – James Anderson May 26 '16 at 9:26
  • SQL Server 2012 Standard. Some of the servers are SQL Server 2014 Standard. I have not considered log shipping yet. I don't know, if it can also be an option, I will study about it. – Akanksha Singh May 26 '16 at 10:06
  • What changes does each rev of the DB bring? Just data or schema also? If you do get schema changes then taking a compressed backup copy of the database, move to remove and restore is probably your fastest route. If you're on standard edition which (I think) doesn't have backup compression, you can still compress the .bak file and move across the wire, de-comp and restore. That doesn't take your primary DB offline like copying mdf/ldf will. Also, compression is worth the time and effort so long as you don't have lots of binary data and don't have CPU pressure. – SQLmojoe May 26 '16 at 16:26
  • Changes can be of any type, mostly change in store procedures, UDFs, and may be data as well. But change in schema can also be there. Also, I guess currently our IT team does somewhat similar to what you are suggesting. This takes a lot of time in transferring data. And my question is, whether it is even possible to win that time using SQL features or not, instead of physically moving files, be it .bak or .mdf? – Akanksha Singh May 26 '16 at 17:16
1

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.

  • Thanks for the details @Kin. Although, as per your suggestion, I am now working on configuring Log Shipping on two test servers I have on different domains. But some points that are concerning me are: 1. I don't want a scheduled job, I want this to run only on manual request. Is that possible that jobs be there in SQL Agent and I can start them as and when required? 2. How much resources does it utilize on Primary Server? 3. I might not have full control over the primary server, hence cannot ensure, if any adhoc back-up is taken or not. – Akanksha Singh May 30 '16 at 10:05
  • You should let the job on primary run as is. On secondary, the copy job should run as is as well. The only job - restore job should be run as demand (since you want that way) or you can delay log restoration as well. – Kin Shah May 30 '16 at 12:57
  • Kin, I am facing some problem while configuring this for a heavy database. At the time of configuring Log Shipping itself, it starts taking back-up of primary DB and restoring at Secondary server. I first configured this for a small DB. It took few minutes to take back-up and restore at first. then the jobs LSbackup, LSCopy and LSRestore created further transaction back-ups and did restore like charm. But when it comes to Huge DB ,it took almost 8 hours to take back-up and restore (cumulatively) while configuring. – Akanksha Singh May 31 '16 at 12:59
  • And when it comes to job LSback to create transaction back-ups, it FAILS saying 'BACKUP Log cannot be performed because there is no current database back-up'. Any help? – Akanksha Singh May 31 '16 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.