Introduction to Database Mirroring - Part II

This is Part II of the series on Database Mirroring that we will continue in this article. What we missed in Database Mirroring - Part I is the archietecture, topology and factors that we need to keep in mind when working with Database Mirroring. Database mirroring is a highly flexible high-availability technology that does not need specialized hardware and storage technologies. However, before selecting database mirroring as your high-availability technology, you must consider its implications on your environment. You should also plan and document the approaches you will adopt when configuring security, network infrastructure, hardware, and server configurations for database mirroring.

Security: You should consider the security implications of implementing database mirroring as a high-availability technology. In order for database mirroring to function, a trusted connection must be established between all the servers involved in the database mirroring session. In a single Active Directory domain and forest environment, each SQL Server instance login must have rights to connect to the other mirroring server, and to its endpoints. For communication across nontrusted domains, you must use public key infrastructure (PKI) certificates. When choosing to implement database mirroring with servers across nontrusted domains, you should consider the complexity associated with configuring a PKI.

Network: Assess the implications of database mirroring on your network infrastructure. Unlike in a clustering configuration, in mirroring there is no distance limit between mirrored servers. It is possible to distribute a database mirroring configuration and perform database mirroring over a wide area network (WAN). However, you must calculate the network bandwidth required for database mirroring to work and ensure that the network infrastructure meets this requirement. The bandwidth required is determined by the frequency of updates on your specific system and your database size. You also need to consider the additional latency caused by setting the safety level to FULL, especially when operating over a slow network. Operating database mirroring across a firewall is not recommended because additional ports must be opened, which may compromise the security of the network.

Hardware and Storage: Unlike server clustering, database mirroring does not require cluster-certified hardware or specialized storage technologies. You can implement database mirroring on any hardware capable of running SQL Server 2005. This makes database mirroring a cost-effective option for implementing a high-availability solution. However, database mirroring requires twice the storage capacity required by server clustering.

Misc Factors

Use a similar hardware configuration for the mirror and the principal servers to protect your system against performance issues and to ensure an appropriate service level for your application users.

Logins and permissions: The mirror server and the principal server should have consistent login and permission settings. You can use a SQL Server Integration Services Transfer Logins task to copy logins and passwords from one server to another. If the principal and mirror servers reside in different Active Directory domains, you must develop a user account migration strategy, because security identifiers (SIDs) will be different in each domain.

Server Configurations: As far as possible, SQL Server configurations should be kept the same on the principal and the mirror servers. These configuration options include Address Windowing Extensions (AWE), max degree of parallelism, SQL Agent jobs and alerts, SQL Server Integration Services packages, support databases, linked server definitions, backup devices, maintenance plans, SQL Mail or Database Mail settings, and Distributed Transaction Coordinator settings.

External Resources: You may have external resources such as common language runtime (CLR) assemblies, supporting applications, monitors, or other executables that support the database application. You need to configure these resources on the mirror server to ensure that your application functions properly after a failover.

Multiple Databases: Many applications use multiple databases on a single server. One application may communicate with multiple databases, or multiple applications may communicate with several databases. However, database mirroring works with a single database at a time. You need to consider this when designing mirroring in your database architecture.

Testing Database Mirroring Configuration

Before deploying your database mirroring solution, you must test it to ensure that it functions as expected. First, test the core functionality of your database mirroring solution, such as database synchronization and database states, failover, and recovery. This testing will ensure that the mirroring environment configured can provide the required functionality. You must then conduct specialized tests such as security testing, latency and response time testing, database consistency testing, and communications testing. Ensure that the results of these tests are documented and approved before you deploy the solution. For each type of testing, you must ensure that your test plans include details of what part of the system is being tested, how the results will be documented, and how the results will be communicated to key stakeholders. Use your SLA as your benchmark for measuring the test results. The following table lists some of the areas of database mirroring that you should test, and provides guidelines for doing so.

Latency and response time: Before deploying mirroring in a production environment, you must understand how your solution functions under live conditions so that you can predict how database mirroring will perform when deployed. In test environments, you should explore all of the operating modes to evaluate the latency generated by each mode of database mirroring. It is also important to conduct performance testing against both the principal and the mirror databases. Document the response times reported by the tests, and ensure that they are within the performance thresholds described in your SLA.

Security: Security testing should be performed in an environment that closely replicates the live environment. For example, if your solution mirrors the database across multiple domains, ensure that the test environment replicates this infrastructure. Security testing will indicate whether your solution functions appropriately with the proposed security settings. Check the security practices you have used against the organization’s security guidelines and best practices. You may want to use the SQL Server Best Practices Analyzer (SQL BPA) tool as part of your security testing. You should also ensure that all security settings are clearly documented.

Database consistency: During database mirroring, it is important to test the database consistency on the mirror server. Ensure that under the simulated projected load, the mirror database is in the SYNCHRONIZED state, because in this state the database is transactionally consistent. To test database consistency, periodically check the database state of the mirror database while applying the projected load to the principal database.

Understanding Database Roles with Database Mirroring

There are three database roles in database mirroring: the principal, the mirror, and an optional witness. The principal and mirror servers communicate and cooperate as partners in a database mirroring session and perform complementary roles. The witness server’s role is to enable automatic failover. Each partner is described as owning its current role. The partner that owns the principal role is known as the principal server, and its copy of the database is the current principal database. The partner that owns the mirror role is known as the mirror server, and its copy of the database is the current mirror database. When database mirroring is deployed in a production environment, the principal database is the production database. The following table explains the three database roles.

Principle Server: The role of the principal server is to serve the production database to clients. Only the principal database is accessible to client connections. When the principal database receives updates requested by clients, the principal server sends every active transaction log record to the mirror server. When the transactional safety level for the database mirroring session is set to FULL, the principal server waits for an acknowledgment from the mirror server before notifying the client connection of a committed transaction. Log records that result from the BULK-LOGGED and SIMPLE recovery models cannot be sent to the mirror database and are therefore not supported. Therefore, the principal database must use the FULL recovery model.

Mirror Server: The mirror server acts as a hot standby server. When the mirror server receives a block of log records sent by the principal server, it first places the log records into the mirror database’s log buffer and then writes them to disk as quickly as possible. As a background process, the mirror server redoes the log on the mirror database record by record, beginning with the oldest log record, as quickly as possible. Each log record is redone only once. As the mirror server redoes the log, the mirror database is continually rolled forward. When the principal server truncates or shrinks the log for the principal database, the mirror server also shrinks the log at the same point in the log stream. Because the mirror database replays the principal’s transaction log records, it duplicates the database changes on the principal database.

Witness Server: In addition to the two partner servers (principal and mirror), a database mirroring session may have an optional third server called the witness. If the principal server fails suddenly and if the mirror server receives confirmation of the failure from the witness server, the mirror server can automatically take on the role of the principal server and make its database available within a few seconds. When the witness server is configured, a database mirroring session requires a quorum to keep a database in service. A quorum is the minimal relationship among all the connected servers required by a synchronous database mirroring session.

Role Switching Options

In a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching. Role switching involves transferring the principal role to the mirror server, which acts as the failover partner for the principal server. When a role switch occurs, the mirror server brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly. The following table describes the three forms of role switching.

Automatic Failover

If the mirror database is synchronized with the principal database, automatic failover will be initiated when the principal database fails. For automatic failover to occur, the mirror database must be updated, and the witness server must be connected to the mirror server. The following steps outline the process of automatic failover:

The witness server verifies that the principal server has failed. The role of the witness server is to verify whether a particular partner server is functioning. If the mirror server loses its connection to the principal server while the witness server remains connected to the principal server, the mirror server does not initiate a failover.
The mirror server receives confirmation from the witness server that the principal server has failed. The mirror server can now automatically take on the role of the principal server and make its database available in less than 10 seconds.

For automatic failover to improve availability, the mirror and principal databases must reside on different computers. Note that manual failover is still possible in high-availability mode.

Manual Failover

A manual failover is possible if the database server is running in high-availability or high-protection mode. You may want to initiate a manual failover in disaster recovery situations and to provide maintenance service during a planned outage such as a hardware upgrade. During manual failover, the partners must be connected to each other, and the mirror database must be updated with all the current changes to the principal database. Manual failover can be initiated either through SQL Server Management Studio or by using Transact-SQL.

Forced Service

In high-performance and high-protection mode, you can force service to the mirror database if the principal server has failed and the mirror server is available. By forcing service to the mirror database, the database owner can make the database available to clients quickly. Forcing service reassigns the principal role to the mirror server, which thereby becomes the principal server and makes the database available. The database runs unmirrored until the other partner reconnects and assumes the mirror role.

Forced service initiates a smooth transition of the principal role to the mirror database. However, it is important to understand that forcing service can cause data loss, because the partners cannot communicate with each other and cannot guarantee that the two databases are synchronized. You should force service only if you must restore service to the database immediately and are willing to risk losing data.

Database States and Availability

During a database mirroring session, a database is always in a specific state. The state of a database during a session describes the communication status, data flow, and the difference in data between the partner servers. A database mirroring session takes on the same state as the principal database. With the exception of the PENDING_FAILOVER state, the principal and mirror databases are always in the same state.

Throughout a mirroring session, the server instances monitor each other. The partners use the database state to monitor each other. If a witness state is set for the session, each partner monitors the witness using its connection state, which is either CONNECTED or DISCONNECTED.

The mirroring state has a direct impact on the availability of a database. You must monitor the mirroring situation closely and take appropriate action if the principal or the mirror databases become exposed or isolated, or if either server is unable to serve its database. Generally, the following three situations pose a risk to database availability and should be dealt with immediately:

The principal server cannot serve the database. In some cases, the principal server does not allow any user connections to the database or any transactions to be processed. This could be caused by the failure of either the principal server or the principal database. When the database server is operating in high-availability mode and the mirror server is able to form a quorum with the witness server, automatic failover will occur. When operating in high-protection mode, you must initiate manual failover. When operating in high-performance mode, you can also initiate forced service. However, it is possible that some committed transactions will be lost.
The data on the principal server is exposed. In some cases, the principal database processes transactions, but no log data is sent to the mirror server. This could be caused by the failure of the mirror server, a breakdown in network communication between the principal and mirror servers, or configuration issues.
A server is isolated. In some cases, a server may be operational, but the communication lines between it and both the other servers in the database mirroring session may be broken. When a witness server has been configured for the mirroring session and the principal server cannot contact either the witness server or the mirror server, the principal server will stop serving the database. The principal server will record its state as isolated when there is no server in the session with which it can form a quorum.

High-Availability Technologies That Complement Database Mirroring

Database mirroring is a cost-effective high-availability technology that provides fast automatic failover and ensures full server and storage redundancy. However, database mirroring might not fulfill all your high-availability requirements. For example, you might want to provide redundancy for system databases or have more than one standby server. Combining database mirroring with other high-availability technologies provides advantages over using database mirroring alone, thereby increasing system availability. The following table describes high-availability technologies that complement database mirroring -

Failover Clustering - You can combine database mirroring with failover clustering to ensure maximum availability. The scope of protection offered by failover clustering is the complete server, thereby protecting the entire SQL Server installation. This configuration will protect the system databases and any external resources running on the server. When combining database mirroring and failover clustering, you can use the following configurations, based on the requirements of your high-availability solution:

Cluster the principal server, the mirror server, the witness server, or a combination of these roles, depending on your requirements.
Configure the principal server and mirror server to reside on clusters, with the principal server running on the virtual server of one cluster and the mirror server running on the virtual server of a different cluster.
Establish a mirroring session in which one partner resides on the virtual server of a cluster and the other partner resides on a separate, unclustered computer. This involves the following mirroring options:

Mirror a database in high-availability mode. A two-cluster configuration is recommended for the partners because it provides maximum availability. The witness can reside either on a third cluster or on an unclustered computer.
Mirror a database in high-protection mode. Another node in the cluster will act as the principal server if the node running the current principal server fails.
Mirror a database in high-performance mode. Consider placing the principal server on the virtual server of a cluster, and placing the mirror server on an unclustered server in a remote location. If the cluster fails over to a different node, the virtual server will continue as the principal server in the mirroring session. If the entire cluster has problems, you can force service onto the mirror server.

Log Shipping - If you require more than one destination database as well as automatic failover with zero work loss, you can use log shipping together with database mirroring. You can also use this configuration if you need to delay restoring the log on the destination database. The principal database in a mirroring session can also act as the primary database in a log shipping configuration. Log shipping can supplement availability in specific database mirroring scenarios. For example, you can have a high-availability database mirroring configuration in-house and log ship the principal server to a remote site for disaster recovery purposes.

The advantage of this configuration is that if the entire site is lost, the data is available on the secondary site. However, in the event of a database mirroring failover, you will need to manually reinitialize log shipping from the master server to the remote standby server. Another scenario for using log shipping to complement database mirroring is as a local standby server for the principal server if the database mirroring session is being used for disaster recovery. In this case, the mirroring session is in high-performance mode, with the mirror server on a remote site as the remote standby server.

In high-performance mode, there is a potential for data loss if the principal server fails and the mirror database is recovered using a forced-service recovery. If you are log shipping the old principal database and if its transaction log file is undamaged, you can make a tail-log backup of the principal database to get the last set of log records from the transaction log. If the standby log shipping database has had every other transaction log backup applied to it, you can apply the tail-log backup to the standby server so that you do not lose any of the old principal’s data. You can then compare the data in the log shipping standby server with the data in the remote database and copy missing data to the remote server.

Replication - By combining database mirroring and transactional replication, you can keep a hot standby of a publisher database, thereby providing availability for publication databases. In other methods of protecting a publisher database, such as log shipping, you cannot keep a standby server for the publisher ahead of the publisher's own subscribers. Therefore, transactional replication can deliver transactions to its subscribers much faster than a transaction log backup scheme. Because database mirroring is quicker than log shipping, it is more suitable for keeping a hot standby of a publisher database. If the publisher fails, you need to manually re-establish the recovered standby database as the publisher and reconnect it to the distribution server. However, replication failover to a mirror is supported only for publication databases; it is not supported for the distribution database or subscription databases.

Network Load Balancing - Network load balancing allows clients to connect to a virtual IP address and be automatically redirected to one of the many servers configured in a network load balancing cluster. You can combine network load balancing with database mirroring to manually redirect connections from one server to another during failover, while the client simply connects to a virtual server name. The advantage of this configuration is that all redirection logic can be managed in a central place and can be abstracted away from the client. This is especially useful if client applications are legacy applications that do not support ADO.NET automatic redirect capabilities.

Managing Environmental differences

You should keep the configuration of the mirror server as similar as possible to that of the principal server in a database mirroring solution. This includes hardware configuration, SQL Server login definitions, and server settings. You may also have external resources such as CLR assemblies, files, and applications that must also be configured on the mirror server. After a failover occurs, the client connections will be redirected to the mirror server. If the configuration of the mirror server differs from that of the principal server, the mirror server will be unable to serve the client connections and perform the required tasks. Significant differences in server configuration between the principal and mirror servers could make the mirroring system unusable for high-availability purposes. Use the following guidelines when managing environmental differences between the principal and mirror servers.

Same System Collation - Differences in system collation settings will cause the mirrored database to have a default collation that is different from that of the tempdb database on the mirror server. All temporary stored procedures or temporary tables are created and stored in tempdb. Objects such as implicit columns in temporary tables, coercible-default constants, variables, and parameters in temporary stored procedures accept the collation of the tempdb database. If there are differences between collations on objects in tempdb and collations on comparable objects created in permanent tables and stored procedures on the mirrored database, it could lead to logical errors. This issue could lead to a mismatch in collations between user-defined databases and system database objects, potentially causing application errors which may make the system unstable.

Login Definitions - If login definitions on the principal server differ from login definitions on the mirror server, the application will not be able to use the mirror database in the event of a failover. Differences in login definitions between local Windows groups on the mirror server might result in users having different permissions on that server, which might make the application unstable or pose a security threat.

External Software - The database application might depend on external software components, such as extended stored procedures or external services, installed on the principal server and the mirror server. If these components are not present, the application will not be able to perform the required tasks and might therefore become unstable or unavailable.

SQL Server Software Version - Database mirroring can only work between two servers running SQL Server 2005. However, differences in SQL Server editions or versions might make the system unstable or unavailable to users.

GAC - If CLR integration is used within an application, you must ensure that all CLR assemblies deployed on the principal server are also deployed on the mirror server. If these assemblies are not present or differ in version, the database will not be able to function properly after a failover.

SQL Agent Jobs - If an application requires SQL Server Agent jobs and alerts, they must be present on the mirror server in order for the application to function correctly after failover. When SQL Server Agent jobs are transferred to the standby server, most of them must remain disabled. In the event of a failover, you will need to enable those jobs to continue normal service.

Match Certificates and Keys - Any mismatch between certificates and keys on the principal and mirror servers will prevent the mirror server from accessing resources after a failover. Any mismatch in the keys used for encryption could make the database data unreadable and might even lead to permanent database corruption.

Conclusion

If I were to think about what Database Mirroring does, the horizons are infinite and very interesting. One needs to be really cautious of a ton of things before using Database Mirroring, this article just brings to light some of the considerations from topology, side-by-side tango with other HA features etc. We havent yet discussed the internals of Database Mirroring which makes this technology really interesting with SQL Server 2008. Features like compressed log shipping and automatic page recovery from the mirror server are awesome features and bring immense benifits to DBA as soon as they upgrade into SQL Server 2008.