Database Mirroring planning on SQL server 2005

26 July,2010 by Jack Vamvas

Database mirroring transfers transaction log records from one server to another server
and can quickly failover over to a standby server. There are some very useful features but is not always the best solution.

Beyond the technical aspects , consider also :  management overheads, storage , network

Below are some  of questions in deciding if database mirroring is the best solution . This is not an exhaustiVe list , and consideration should be given
to overall High Availability(HA) and Disaster Recovery (DR) strategy within the organisation

1) Are the databases in FULL RECOVERY mode?
Database mirroring requires the principal database to be in Full Recovery .
If you keep your databases in Simple Recovery than consider the impact of changing to Full Recovery

2) Are you running multi database applications?
In the FULL saftey mode , a single database failure will cause automatic failover.
Consider alternative operating modes. The FULL (HA ) mode is maximised with a fast , reliable network link, and one database.

3) Is "automatic failover" required?
If so, 3 servers are required. Principal,Mirror and Witness. There is an obvious server and storage impact

4) Does data have to be transfered over long distances?
If some potential data loss is acceptable , and transfering data in asynchronous mode is acceptable , consider the High Performance Operating mode.
A forced service failover is required.

5) Are multi-part object names used for querying across multiple databases?
It is possible that only one database fails over to the standby server, therefore consider a separate client connection for every database,otherwise you might discover you're querying on a server where one database is principal and the others are still mirrors

6) Is data loss unaccepatable?
Safety will need to be set  to FULL. This means the prinipal server simultaneously ,hardens its log buffer  and  sends a copy of the log records from the buffer to the mirror.For the transaction to be considered complete , it's own IO and the IO of the mirror have to complete .This is not a distributed transaction or the implementation of a two-phase commit. In effect it's two transactions tightly co-ordianted over two servers.To highlight the difference , a commit failure on the mirror
will not rollback the transaction on the principal

7) Is the  query activity of the many users\ long transactions?
This is the ideal usage pattern for database mirroring - as it disguises the mirroring overhead. Whereas one user with short transactions highlights the the mirroring.
This can be compounded by the safety level setting, as the principal must wait for an acknowledgment from the mirror, before it can issue an acknowledgment to the

8) Do you need full server failover or database level failover?
Serious consideration should be given to optimising one application per database - particuarly if High Availability is required. If an automatic failover occurs,then other databases on original mirror are not needed.
If redundancy is required at the server instance level , consider failover clustering

Read More

SQL Server DBA: High Availability

SQL Server system redundancy and high availability

Author: Jack Vamvas (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Database Mirroring planning on SQL server 2005 | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer