none
DC-DR Replication RRS feed

  • Question

  • Hello Experts,

    I am new to SQL Database and I need help to understand DC-DR replication in SQL.

    So here is the task.

    In our production environment, we have Clustered SQL servers in DC and 1 stand alone sql server in DR.
    There is not replication of DB between  DC & DR. All services are running from DC itself. Now, I want to configure DR site so that in case of failure we can get services up from  DR site.

    How can I configure SQL so that DB from DC SQL Server replicates to DR SQL Server?

    Always on Availability group and HA are not options here.

    I would appreciate your advice.

    Thanks    

       

    Thursday, October 4, 2018 5:10 PM

Answers

  • AlwaysOn AG came to my mind first but I see it's out of the question. The other technologies are as follows:

    1. Log Shipping: This is nothing but keeping the DR database in sync with the DC by applying the T-log backup at regular intervals. The database on the DR side would need to be initialized with a full backup of the DC database and subsequently, the T-log backups will keep it up to update. There's still a chance of potential data loss. Read more:

    https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-2017

    2. Database Mirroring: First, this has been deprecated with SQL 2012 and may be removed in future versions. There may not have been any updates to this feature since 2012. MSFT might not (I want to say won't) release any new bug fixes etc. for it. Basically, it's syncing of DR with DC by sending the transaction log records from the primary (DC) i.e. data synchronization happens via the network. Read more:

    https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-2017

    3. Transactional Replication: Transactional replication which replicates every transaction that occurs on the publisher (DC) to the subscriber (DR) and keeps it synchronized with the DC. 

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-2017

    Note: Unlike log shipping and DM, replication makes you select the database objects you want to be replicated to the DR so, in a way, it's not a full database replica, but you can choose to replicate all tables, Sprocs, views etc. It enforces conditions such as the tables need to have PK etc. Read more from the above link.

    I don't know why AG is not an option in your case but that would be ideal for your need. Even if yours is SQL standard 2016, you can still go with Basic AG. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    • Edited by Mohsin_A_Khan Thursday, October 4, 2018 9:23 PM
    • Marked as answer by Kz' Friday, October 5, 2018 3:45 PM
    Thursday, October 4, 2018 9:13 PM

All replies

  • AlwaysOn AG came to my mind first but I see it's out of the question. The other technologies are as follows:

    1. Log Shipping: This is nothing but keeping the DR database in sync with the DC by applying the T-log backup at regular intervals. The database on the DR side would need to be initialized with a full backup of the DC database and subsequently, the T-log backups will keep it up to update. There's still a chance of potential data loss. Read more:

    https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-2017

    2. Database Mirroring: First, this has been deprecated with SQL 2012 and may be removed in future versions. There may not have been any updates to this feature since 2012. MSFT might not (I want to say won't) release any new bug fixes etc. for it. Basically, it's syncing of DR with DC by sending the transaction log records from the primary (DC) i.e. data synchronization happens via the network. Read more:

    https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server?view=sql-server-2017

    3. Transactional Replication: Transactional replication which replicates every transaction that occurs on the publisher (DC) to the subscriber (DR) and keeps it synchronized with the DC. 

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/transactional-replication?view=sql-server-2017

    Note: Unlike log shipping and DM, replication makes you select the database objects you want to be replicated to the DR so, in a way, it's not a full database replica, but you can choose to replicate all tables, Sprocs, views etc. It enforces conditions such as the tables need to have PK etc. Read more from the above link.

    I don't know why AG is not an option in your case but that would be ideal for your need. Even if yours is SQL standard 2016, you can still go with Basic AG. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    • Edited by Mohsin_A_Khan Thursday, October 4, 2018 9:23 PM
    • Marked as answer by Kz' Friday, October 5, 2018 3:45 PM
    Thursday, October 4, 2018 9:13 PM
  • Hello Experts,

    I am new to SQL Database and I need help to understand DC-DR replication in SQL.

    There is NO such "Microsoft define term" what this means is moving data from DC which is primary to DR which is your disaster recovery site. FYI replication is database level technology in SQL Server so don't confuse it with widely used other terms which call replication as moving data from one place to other.

    How can I configure SQL so that DB from DC SQL Server replicates to DR SQL Server?

    Always on Availability group and HA are not options here.   

    What is version and edition of SQL Server. You have Log shipping, mirroring and replication to move data at database level to DR site. If you can be specific I can provide more information


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, October 5, 2018 6:30 AM
    Moderator
  • Hi,

    We have SQL 2014 standard edition. Always On AG is not supported in this edition. I think Log Shipping would be helpful.As we are going to depend on DR for 6-7 days during DC-DR drill and after that I need to be ready for failback. 

    I would appreciate your help if you tell me more about these methods.

    Thanks 


    Adarsh

    Friday, October 5, 2018 3:50 PM

  • I would appreciate your help if you tell me more about these methods.


    What kind of help you need, what more information you need ?

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, October 5, 2018 4:08 PM
    Moderator


  • Suppose I configure transnational replication or log shipping between DC-DR SQL Server. During DC-DR drill activity DC will go off and services will be running from DR. So it is obvious  that DR SQL DB will be getting updated. 

    As DC SQL server will not be insync with DR server for some days. After DR drill and for failback to DC, What changes I have to make in DR/DC sql server so that SQL server in DC gets updated DB from DR server. And I can make services running from DC where DR left them?

    Which option would be better in this scenario  ?
    Log Shipping or Transnational  Replication.  

    Thanks

     

    Adarsh

    Saturday, October 6, 2018 1:31 PM
  • Neither transactional replication nor Log Shipping is straightforward when it comes to failover. They're not as simple as SQL FCI or AlwaysOn AG. 

    In Log Shipping DR drill, you'd have to first stop whatever is writing to the primary DB and perform the final T-log backup, copy and run the restore on secondary DR database amd rec over it so they both are in Sync. Route your application to DR and when you are ready to fail back, stop the app again and reconfigure Log Shipping from a fresh full backup of DR (since it's the latest). You can also look up ways to do reverse log shipping but in short, it's not easy as one click of a button.

    In transactional replication, again, you'd need to reconfigure the whole thing all over unless it's merge replication which can be configured bidirectional but replication is complex and you may end up configuring it from scratch. 

    DR drills are easy if it's SQL FCI, AG or Mirroring.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Saturday, October 6, 2018 2:00 PM

  • Suppose I configure transnational replication or log shipping between DC-DR SQL Server. During DC-DR drill activity DC will go off and services will be running from DR. So it is obvious  that DR SQL DB will be getting updated.

    That will not happen automatically you have to point application to SQL Server at DR or you have to apply some login in application code to achieve this.

    As DC SQL server will not be insync with DR server for some days. After DR drill and for failback to DC, What changes I have to make in DR/DC sql server so that SQL server in DC gets updated DB from DR server. And I can make services running from DC where DR left them?

    Which option would be better in this scenario  ?
    Log Shipping or Transnational  Replication. 

    Both has its advantages while replication is *mostly* used for object level replication Logshipping

    is complete database replication. I would go with LS and there are steps to configure LS again when DC comes online. You have to do what is called as reverse log shipping or simply take full backup of DR and restore it on DC and reconfigure log shipping


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, October 6, 2018 5:54 PM
    Moderator
  • Being naive in DB Administration, I am finding Database mirroring helpful. 

    failover seems easy in mirroring. 

    Please tell me if there are disadvantages of this methods in my DC-DR scenario.

    Thanks for your kind help.

     

    Adarsh

    Sunday, October 7, 2018 4:24 PM

  • failover seems easy in mirroring. 

    Please tell me if there are disadvantages of this methods in my DC-DR scenario.

    Yes, failover is easy when compared to Log-Shipping and replication, but remember, Database Mirroring was deprecated starting with SQL 2012 and Microsoft may remove it anytime. Sure, you can set it up on your SQL 2014 instance but know that MSFT won't support it given that it's deprecated. 

    I understand there may be reasons preventing the server from being upgraded to a higher SQL version/edition but I would rather work on making the upgrade possible than relying on a deprecated feature. 

    That said, if you still want to go ahead with the configuration then you can. Since you have the standard edition, you can only go with "Full Safety" i.e. Synchronous transfer. Now, synchronous is nothing but the transaction would be hardened/committed on DR (Mirror), then DC (principal) and then the acknowledgement would be sent to the client. This may introduce latency given the synchronous nature. You can perform a planned manual failover when it's drill time and when done, failback to the original primary. More information is in the link in my first reply.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Sunday, October 7, 2018 9:24 PM
  • Being naive in DB Administration, I am finding Database mirroring helpful. 

    failover seems easy in mirroring. 

    Please tell me if there are disadvantages of this methods in my DC-DR scenario.

    Not always, mirroring is more complex as compared to transaction log shipping. I am sure you have not worked with LS it is just automation of backup, copy and restore while with mirroring lot of things go under the hood. Troubleshooting failed mirroring requires a bit more knowledge of SQL Server as compared to Log shipping and biggest disadvantage is it is deprecated. If you have good knowledge of SQL Server and mirroring by all means go with it but if you are novice LS would help you more, personal thought :).

    You need to to have continuous strong bandwidth for mirroring to work and if link fails for some time you would have tough time. While with LS it link fails and copy fails you can just start the copy job again when link comes online.

    I would suggest to read both carefully we can only guide your form here we cannot point you to choose technology because we have limited idea about your infrastructure

     

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Monday, October 8, 2018 6:34 AM
    Moderator