Log Shipping: two secondary DBs on the same instance - is this possible?

Question

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...

Answer 1

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.

Answer 2

As SqlWorldWide stated in his answer, it's not possible (using out-of-the-box log shipping).

A supported workaround is to install a third instance on the server, instance_3, and put dest_2 on that instance with a different schedule. Your server will need to be of sufficient size, but this will allow you to have two different copies of the database on two different recovery schedules.

If you can't install a third instance, and you're not afraid to code up some custom routines, there is another option available. Basically, log shipping is a process wrapper that will automagically backup a database, copy the backups to a remote location, and then restore those files to a different database. There's really no mystery about what's going on here, but the routines make this process easy to manage.

With sufficient effort, you can customize your own restore routines for the dest_2 database as the formal Log Shipping jobs for dest_1 will already have the backups located in a common area for you to reference. You'll need to remove dest_2 as a log shipping candidate and develop routines that restore the outstanding logs when/how you desire. All backup information will be located in the msdb.dbo.backup* tables on your primary instance, so you may need to query that over a linked server to generate the restore scripts. This isn't going to be an easy approach, but it's an option if you must have the functionality you want and you've got time to customize some restore routines.

Obviously the downside is dest_2 won't show up in your log shipping monitoring reports, but it sounds like you want this location more as report target instead of a failover candidate in the event of a disaster.