SQL Server Mirroring – Is it Safe to Start on Production Server?

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.

The DBCC SHRINKFILE command will be mirrored from the principal to the mirrored database. Here's some proof.

Create a sample database on the principal:

create database MirroredDb;
go

Create the same database from a backup with NORECOVERY:

restore database MirroredDb
from disk = '\\backupdir\MirroredDb.bak'
with norecovery;
go

Setup your mirroring session however which way you choose.

On the principal database look at the database file sizes:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like this following:

name            size
MirroredDb      392
MirroredDb_log  104

On the mirror database, create a snapshot and look at the same information:

create database MirroredDbss
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb.ss'
)as snapshot of MirroredDb;

use MirroredDbss;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  104

Now grow the transaction log file on the principal database (I brought it to 1 GB):

alter database MirroredDb
modify file
(
    name = MirroredDb_log,
    size = 1GB
);
go

Looking at the principal database's transaction log size, we now see the adjusted size:

use MirroredDb;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Create another snapshot on the mirrored database, and look at the transaction log file size there:

create database MirroredDbss2
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb2.ss'
)as snapshot of MirroredDb;

use MirroredDbss2;
go

select
    name,
    size
from sys.database_files;

My result set looks like the following:

name            size
MirroredDb      392
MirroredDb_log  131072

Now do the DBCC SHRINKFILE on the principal:

use MirroredDb;
go

dbcc shrinkfile('MirroredDb_log', 0);
go

select
    name,
    size
from sys.database_files;

My result set is the following:

name            size
MirroredDb      392
MirroredDb_log  104

Create a third and final snapshot on the mirrored database, and look at the size:

create database MirroredDbss3
on
(
    name = 'MirroredDb',
    filename = 'c:\sqlserver\MirroedDb3.ss'
)as snapshot of MirroredDb;

use MirroredDbss3;
go

select
    name,
    size
from sys.database_files;

And I get the following result set:

name            size
MirroredDb      392
MirroredDb_log  104

So as you can see here, the DBCC SHRINKFILE command is in fact mirrored to the mirror database.