We are doing log backups on the principal database, and my question is what are the gotchas with doing a log backup on a mirror?
You can't do a log backup on the mirror database.
At least one full database backup from the mirror has to be completed before doing a log backup, in that case are there special options that need to be used because it is a mirror?
You can't do a full database backup on the mirror database either.
Take this for instance: I have Server1 which houses the principal database AdventureWorks2012, and I have Server2 which contains the mirror. Here's what happens when I try to run backups on the mirror database (on Server2):
use master;
go
backup database AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.bak';
go
Msg 954, Level 14, State 1, Line 2
The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
backup log AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.trn';
go
Msg 954, Level 14, State 1, Line 1
The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Take a look at this Database Mirroring FAQ by Robert Davis. I will quote him regarding this operation and the mirror database transaction log maintenance:
When you backup the log on the principal, the virtual log files (individual units within the log file) are marked as re-writable. The same VLF’s are marked as re-writable in the mirror log file as well. The VLF status is mirrored on the database.
So there you have it. If you have transaction log backups on the principal then similar log reuse behavior will be mirrored to its partner database.
One way to prevent the mirror failover is:
- Pause Mirroring with
ALTER DATABASE XYZ SET PARTNER SUSPEND
- Move the SQL instance
- Resume mirroring with
ALTER DATABASE XYZ SET PARTNER RESUME
The instance is failing over to the mirror because both the witness and the secondary can no longer see the primary instance.
It sounds like you are attempting to recreate SQL Server 2012 Availability Groups by combining mirroring and clustering.
Database mirroring only times-out on so-called "soft" errors. Hard errors, like a cluster failing over are reported to the mirroring session immediately causing the immediate failover. Read more at http://msdn.microsoft.com/en-us/library/ms190913.aspx
Possible causes of hard errors include (but are not limited to) the following conditions:
A broken connection or wire
A bad network card
A router change
Changes in the firewall
Endpoint reconfiguration
Loss of the drive where the transaction log resides
Operating system or process failure
Conditions that might cause mirroring time-outs include (but are not limited to) the following:
Network errors such as TCP link time-outs, dropped or corrupted packets,
or packets that are in an incorrect order.
A hanging operating system, server, or database state.
A Windows server timing out.
Insufficient computing resources, such as a CPU or disk overload, the transaction
log filling up, or the system is running out of memory or threads. In these
cases, you must increase the time-out period, reduce the workload, or change
the hardware to handle the workload.
For more information about mirroring and potential issues, you might want to see my question What can cause a mirroring session to timeout then failover? SQL Server 2005
Best Answer
Based on your comments, your best option would be Database Mirroring or Log shipping.
You have to evaluate your RPO and RTO. Since you are using VPN to your actual machine - meaning it is point-to-point connection :
If you go with Database Mirroring :
You have to use Asynchronous mode. Also, if you are not running in the same Active Directory, you’ll also need to factor in the time to re-create and tie out the accounts when calculating the RTO value.
RPO: As of last good synchronization
RTO: (Time of failure + Time of client redirect to New System ) - Time of last good synchronization
If you go with Log Shipping (min will be 1 min) :
Because the log file is copied to a Windows share, this solution requires both networking access and an Active Directory integration.
RPO: As of last good log backup application to the secondary system.
RTO: (Time of failure + Time of client redirect to New System ) - Time of last good synchronization
Refer to this excellent article by Buck Woody : Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server