Log shipping

mukki mehra 1 Reputation point
2020-06-29T05:40:19.84+00:00

Dear experts

I have two node SQL server standard failover cluster.

I need to setup single node DR using log Shipping method.

Want to ask after switch over to DR how we can setup, plan so that application can connect automatically to DR database server without any changes on application connection string.

Thanks

SQL Server on Azure Virtual Machines
Azure Backup
Azure Backup
An Azure backup service that provides built-in management at scale.
1,124 questions
Azure SQL Database
Azure Migrate
Azure Migrate
A central hub of Azure cloud migration services and tools to discover, assess, and migrate workloads to the cloud.
717 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,525 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ronen Ariely 15,096 Reputation points
    2020-06-29T11:32:37.03+00:00

    Hi,

    Log shipping does not fit best for automatic move to the secondary since the primary and secondary databases are not synchronized probably. The clients continue to change the data in the primary after its latest backup job. You might need to synchronizing all of the secondary databases with the primary database.

    There are many architecture which you can use, especially since you will need to do some work manually. The simplest one is probably use load balance. But if you really need automatic failover, then you should probably use a solution which fit it like SQL Server Always On Availability Groups automatic failover

    For more information, check the following document

    https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-and-failover-modes-always-on-availability-groups?view=sql-server-ver15

  2. Ronen Ariely 15,096 Reputation points
    2020-06-29T12:55:40.187+00:00

    Hi,

    The issue is not that you will need to made change in the application, but that you will need to do some manual work on the database side to sync the data.

    If you have limitation to use solutions for DR with no time down then you will not have the option to get DR with no down time. You cannot ask for 1+1 and say that your requirement is to get 3. If you need to do 1+1 then you will need to provide the solution 2 (if I succeed to clarify the point)

    As I said, you can use load balance so no changed is made in the app side.

    In this case the app connects to the server through the load balance (using the load balance address) and therefore, if the server changed it is up to the load balance to direct the communication to the new server. You will need to get a load balance service if you do not know how to configure one yourself (it is not simple). If you do pay for external service then they will guide you on how to use their service as it is different from one to other.

    Note: load balance can be used with AlwaysOn Availability Groups or without

    https://www.red-gate.com/simple-talk/cloud/cloud-data/azure-load-balancers-sql-server/

    Another option which is very simple to implement for developers is to manage it in your app. You can try to connect to server A and if it fail then connect to server B. Therefore no change will be made in the app if server A is down.

    0 comments