Sql-server – Mirroring and log shipping together

disaster recoveryhigh-availabilitylog-shippingmirroringsql server

We are using SQL Mirroring as an HA solution and Log Shipping as a DR solution.

Recently, the mirror database has been configured as a log shipping primary (the mirror/primary database) to allow log shipping to continue after database mirroring fails over, using the same configuration we used for the primary on the principal database.

Now the problem is every so often the restore job fails due to:

Exclusive access could not be obtained because the database is in use.

This is because two restore jobs (one from principal/Primary, and one from the mirror/primary) start at the same time. I have changed the job schedules to be run at different times, however.

For example, the restore job from the principal/Primary is scheduled for every 13 minutes and the restore job from mirror/Primary is scheduled for every 10 minutes. But there are still some times that they start at the same time.

Is there any way to prevent this?

Best Answer

Conditionally restore based on which job is running on the principle

If I understand you correctly, I would think that the primary mirror should be the one doing the restoring. If this is the case I would modify your job(s) like so:

if ((select mirroring_role from sys.database_mirroring where db_name(<database id>) = '<database name>') = 1) 
    Restore Database XYZ from disk = 'C:\XYZ.bak'

Not knowing your full setup, I "would think" both servers could run this job and it wouldn't matter which job got there first or the timing of the jobs.

Note:

A mirroring_role of 1 signifies the principle whereas a mirroring role of 2 signifies the secondary role.

Edit based on your comment:

If you'd rather keep your jobs "as is", even though one of them sounds redundant, just stack them together in one job with two job steps. If the first step fails, execute the second step. But if the first job step succeeds, don't run the second job step.

I still think conditionally restoring is your best bet, but I'd have to see your job code on both servers to know exactly what your doing. Having both jobs compete to restore the same database doesn't sound like the very best of plans to me.

Related Solutions

Sql-server – Getting timeouts and backup errors after mirroring and log shipping is setup

If your server has only 4GB of RAM, AWE isn't going to help you much. AWE is designed for when you have greater than 4GB of RAM you need to use.

Timeouts are harder to define. The timeout period itself is likely defined in your application, so it's not inherently SQL Server that's causing the timeouts. However, what you're running into are queries that take to long to complete. To troubleshoot this, I would first start by reviewing the wait stats to identify specifically what is causing things to drag in your SQL Server. Some waits to look for:

BACKUPTHREAD means your queries are waiting on backup operations to complete. Other backup waits could indicate problems.
Memory pressure could be any number of waits, but look for things like RESOURCE_SEMAPHORE or IO waits. Also, check the trend on your Page Life Expectancy in Perfmon (SQL Server: Buffer Manager -> Page Life Expectancy). If this continually trends low, your pages are cycling out of memory to quickly.

Another thing you can do is check how long your log shipping backups are taking to complete:

SELECT 
    database_name
    ,backup_finish_date
    ,datediff(ss,backup_start_date,backup_finish_date) SecondsToBackup
FROM
    msdb.dbo.backupset
WHERE
    type='l'
ORDER BY 
    backup_finish_date desc;

As for resolution, if your log shipping is happening all at the same time, you might want to try and stagger that out. If it isn't necessary to log ship every 5 minutes, I'd try and go with your 15 minutes and stagger each database off by 5 minutes so that they're running separately.

The other thing is to consider a hardware upgrade. The box your describing sounds fairly out of date, considering there are laptops you can purchase now that have more horse power than what you're describing. If you can move the server to a virtual machine, you'll be able to immediately upgrade to better resources, which could resolve these issues for you.

Sql-server – Does a mirroring configuration requires the database do stop receiving requests

After you take your transaction log backup and restore it on the mirrored pair, you might have taken more transaction log backups. If so, try to catch up on the backups then init the mirror. At that point all the old transactions that are not replayed with be queued up and you will endure a DB_MIRRORING event on your log file which will stop the transaction log from truncating those transactions that have not replayed yet. Do note if your network is really slow those transactions might continue to get queued up, it's critical to have an appropiate IO subsystem and network on the 2nd pair so it can keep up.

I'm not sure if you can in your environment, but if the transaction log backups happen too fast, perhaps then you have a IO subsystem problem you need to review on the 2nd pair or a networking problem. Worst case, you could pause transaction log backups if it isn't going to cause a bad growth issue. Otherwise you could look at using a DIFF backup to fully catch up then do 1 transaction log restore.

Also, consider high performance mode over high safety mode if you can, but if you can't just make sure your network and diskIO are production quality. Also set up monitoring for log delays, it will cause massive growth on the primary if you don't.

Best Answer

Related Solutions

Sql-server – Getting timeouts and backup errors after mirroring and log shipping is setup

Sql-server – Does a mirroring configuration requires the database do stop receiving requests

Related Question