Log shipping

Question

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

Answer 1

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

Answer 2

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.