Search This Blog

What is Database Mirroring

In the proposed database mirroring solution there will be two groups of active-passive windows clusters deployed at each location (PRIMARY SITE and Secondary Site). The Primary Site will be the primary site and the Secondary Site will be the disaster recovery (DR) site.
The different failure scenarios are as follows:
- In case of a failure at the Primary Site, Node-A would automatically fail-over to Node-B on the Primary Site.
-In case of a Node-B failure, Node-C at Disaster Recovery site will need to be manually, brought online.
-In case of a Node-C failure, Node-C will automatically fail-over to Node-D.
Asynchronous database mirroring is to be implemented to replicate the data across these sites. Fail-over and fail-back is to be performed manually from PRIMARY SITE to Secondary Site.
Database Mirroring Overview
Database mirroring is a software technology built into Microsoft SQL Server providing high availability of application databases. Mirroring is implemented on a per database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring.
Database mirroring offers a substantive increase in availability over the level previously possible using Microsoft SQL Server and provides an easy-to-manage alternative or supplement to failover clustering or log shipping. Database mirroring works by maintaining a hot standby server. During a typical mirroring session, after a production server fails, client applications can recover quickly by reconnecting to the standby server.
Pros
- Database mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimize data loss.
-Database mirroring has automatic server failover and client failover capabilities.
-Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).
-Because propagation can be done asynchronously it requires less bandwidth than synchronous method (e.g. host-based replication, clustering). Replication is not limited by geographical distance with current technology.
-Database mirroring supports full-text catalogs.
-Database mirroring does not require special hardware (such as shared storage, heart-beat connection) and cluster hardware, thus potentially has lower infrastructure cost.
Cons
-As the mirroring database is in a constantly restoring state a database snapshot is required to use the database for reporting.
-There is a risk in using automatic fail-over on a SQL Server cluster set-up, as in case of hardware failure on primary node there is a possibility of fail-over occurring to the mirrored database instead of secondary node.
-Additional SQL Server instances may be required for witnessing.

 

1 comment:

Mark Willium said...

Tips for Database Mirroring:

1. You can not create mirror database for system databases like master, model, msdb, & tempdb
2. You can not rename mirrored database during database mirroring session
3. You can not create mirror database for the databases that are using filestream filegroup.

Regards Mark Willium
Blog