Database Mirroring is a powerful technology introduced in SQL Server 2005 that provides high availability for databases. It operates at a database level and allows for automatic failover in case the server hosting the database becomes unavailable.
There are four major high availability options in SQL Server 2005: Clustering, Log Shipping, Replication (Transactional), and Mirroring. Database Mirroring can be considered as a combination of Log Shipping with automatic failover per database and Clustering on a database level without a single point of failure or shared disk.
To successfully implement Database Mirroring, you need to have three instances of SQL Server 2005 installed, either Standard or Enterprise Edition, for all instances. Each database participating in the mirroring session must be set to the Full recovery model.
It’s important to note that Database Mirroring works with a single database at a time. If your application uses multiple databases on a single server, you need to consider this when designing your database architecture.
Database Mirroring consists of two mandatory roles: Principal and Mirror, and a third optional role: Witness Server. The Witness Server is used for automatic failure detection and failover. SQL Server 2005 Express and Workgroup Edition are good candidates for the Witness role.
Each participating server keeps metadata about the session and the current state of the databases. You can inspect the session on the Principal or Mirror server by querying the sys.database_mirroring catalog view. The metadata for the Witness server is returned using the sys.database_mirroring_witnesses catalog view.
Before starting a mirroring session, it’s important to initialize each Mirror database to ensure synchronization with the Principal. This involves performing a last known full backup of the Principal database and restoring it to the Mirror Server with the NORECOVERY option. This puts the Mirror database in a recovering state and disallows any connections.
There are several other considerations when implementing Database Mirroring, such as transferring logins, SQL Agent jobs and alerts, support databases, linked server definitions, and more. SQL Server Integration Services provides a Transfer Logins task that can be used to copy logins and passwords from one server to another.
Database Mirroring is configured within SQL Server Management Studio (SSMS) from the database properties, Mirroring page. The connectivity among Principal, Mirror, and Witness Servers uses endpoints, which are created with a payload of DATABASE_MIRRORING. Endpoints can be configured for security reasons, including changing the default TCP ports and encrypting communications between endpoints.
Database Mirroring can be configured in three different operating modes:
- High Availability Operating Mode: Provides durable, synchronous transfer of data between Principal and Mirror, including automatic failure detection and failover.
- High Performance Operating Mode: Does not require a Witness Server and allows for asynchronous data transfer between Principal and Mirror. This mode provides better performance and allows for geographic dispersion between the Principal and the Mirror.
- High Protection Operating Mode: Similar to High Availability Mode, but failover is manual and requires manual promotion of the Mirror to be the Principal. Data transfer is synchronous. This mode is not recommended and should only be used in specific scenarios, such as replacing an existing Witness Server.
Client connections in Database Mirroring can be handled automatically by ADO.NET or the SQL Native Client driver, which can redirect connections when a failover occurs. The connection string should specify the initial Principal server and database, and optionally the failover partner server.
Hardware considerations for configuring servers for Principal and Mirror include ensuring similar CPU and memory configurations, as well as matching SQL Server configurations and logins with their permissions.
In the event of a principal server crash or damage, the entire environment needs to be reinitialized. Removing mirroring can be done through the database properties dialog or by issuing a command on the Principal or Mirror.
Database Mirroring is a valuable technology that provides high availability and high performance solutions for database redundancy. It keeps the Mirror database nearly up to date with the Principal, with no loss of committed data. It is an important option in the array of high availability technologies supported by SQL Server.
Database Mirroring Features in the various SQL Server 2005 Editions:
Database Mirroring Feature | Enterprise Edition | Developer Edition | Standard Edition | Workgroup Edition | SQL Express |
---|---|---|---|---|---|
Partner | ✓ | ✓ | ✓ | ✓ | ✓ |
Witness | ✓ | ✓ | ✓ | ✓ | ✓ |
Safety = FULL | ✓ | ✓ | ✓ | ✓ | ✓ |
Safety = OFF | ✓ | ✓ | ✓ | ✓ | ✓ |
Available during UNDO after failover | ✓ | ✓ | ✓ | ✓ | ✓ |
Parallel redo | ✓ | ✓ | |||
Database Snapshots | ✓ | ✓ |
Database Mirroring is a valuable tool for achieving high availability in SQL Server. By understanding its concepts and implementing it correctly, you can ensure that your databases are always available and protected against server failures.