Sql-server – Log Shipping: two secondary DBs on the same instance – is this possible

load balancinglog-shippingsql server

This is a bit insane, I'll explain intention later:

Scenario:
+ instance_1: one database (LS_source) configured as primary for log shipping
+ instance_2: two databases (dest_1, dest_2) as secondaries for LS_source (yes, this works!)

Problem:
There is one restore job on the secondary instance. Therefore both secondaries will be restored at the same point in time. We'd like to setup two restore jobs to have different schedules for dest_1 and dest_2.

Closer look: Restore executes sqllogship.exe -Restore secondary_id

Core of the problem: Both secondary databases have the same secondary_id, so sqllogship.exe will always restore both…

Any ideas how to get two restore jobs? Can the secondary_id be changed?

Thank you for your help!

Now the idea behind this strange scenario:
We'd like to use instance_2 for load balancing read only access (secondary is standby). While trn are restored, user cannot access it for ~90 seconds. This is not acceptable, 5 seconds would be ok.
If we got two secondaries restored at different times we could switch from one to another after restore completed. Both databases must be on the same instance, so we can have one database snapshot pointing to the active secondary => no need to change connection strings for all clients.
Transactional replication is no choice, impact is to high when replication stalls. AlwaysOn is not available on Standard Edition…

Best Answer

It is not possible.

Ref:

About Log Shipping

SQL Server Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances.

Provides a disaster-recovery solution for a single primary database and one or more secondary databases, each on a separate instance of SQL Server.

The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

Related Question