Tuesday, April 5, 2011

Database Mirroring

Database Mirroring provides database redundancy by transferring data from the transaction log to another instance of SQL Server. There are several operating modes that can be used such as: high performance, high safety with automatic failover and high safety without automatic failover. Based on the operating mode, we can have complete or almost complete database redundancy. More information can be read from this MSDN library article.
  • This is a good option when there is the need to have automatic failover for a database. It can be near real time failover of a database depending on the options used. Also a good choice if there is a need to provide database connectivity with minimal downtime.
  • Recovery Time Objective (RTO) - Depending on the options used it could be almost immediately for a database, because the mirror copy will become the primary copy. Also, if you use the Failover Partner option in the connection string the application should be able to find the new server without any other configuration changes.
  • Recovery Point Objective (RPO) - If there is no data loss (due to data deletion), there is minimal to no data loss during the failover. Because this is an exact copy of the primary database all transactions will be replicated to the mirror copy as they were done on the primary server.
  • It provides automatic failover. (if used with a witness)
  • Snapshots of the database can be created against the mirrored copy for read only access and off-loading reporting to another server.
  • It provides near real time failover of a database, depending on the options used.
  • No additional cost, except for the need to have another available server for the mirror and possibly a third for the witness.
  • If secondary server is used in a passive mode (only for failover), additional SQL Server licensing is not needed.
  • High safety without automatic failover setting may be network overhead.
  • A third server is required for "automatic failover" (witness - SQL Server Express can be used)
  • Other items need to be handled outside of mirroring such as logins, SQL Agent jobs, etc...
  • Requires additional storage for mirrored copy
  • If Snapshots are used for read only, the snapshot is only as current as when the snapshot was created.

No comments:

Post a Comment