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