SQL Server Database Mirroring

How Database Mirroring Works

Database Mirroring is a feature of SQL Server that maintains two copies (Principal and Mirror) of a single database that must be kept synchronized. A witness server is used to act as an intermediary between them if needed.

To enable Database Mirroring, one first needs to establish a partnership between the two servers. Afterward, a database to be mirrored can be selected, and then the principal database and transaction log are backed up and transferred to the mirror server by using FTP, a network share or tape backup. Once the mirror server has the initial database copy, the principal server sends transaction log backups for onward application to the mirror server.

A transaction log record contains enough information to reapply every change made to a database over a period of time, known as the log sequence number (LSN). The mirror server waits for the principal server to commit a transaction and then records the transaction’s LSN, known as the ‘hardened LSN’, which survives if the principal server crashes or the network connection between them fails. The mirror server then makes sure that the changes are reapplied to the mirror database.

When database mirroring is properly configured, the mirror server automatically replaces the principal server if the principal server experiences a failure.

Setting Up Database Mirroring

Database Mirroring can be set up in either synchronous or asynchronous mode. In synchronous mode, transactions are committed on both the principal and mirror servers before the commit is considered complete. In asynchronous mode, the transaction is considered complete when it is committed on the principal server alone.

There are three operational modes of Database Mirroring: High Safety with Automatic Failover, High Safety without Automatic Failover, and High Performance.

High Safety with Automatic Failover ensures that data loss is avoided and the service availability is maximized which means the principle database is synchronously mirrored in real time. In case of Principal database failure, the mirror database automatically takes over and becomes the principal with minimal data loss or no data loss. The witness server is used to perform automatic failover.

High Availability without Automatic Failover uses synchronous mode without automatic failover. This mode is typically used when automatic failover is not technically possible or not desired for other reasons, i.e reporting etc.

High Performance mode practices asynchronous disk mirroring without automatic failover. This method provides higher throughput, i.e fast write transactions etc.

Database Mirroring can be set up using the SQL Server Management Studio. The following steps need to be followed:

1. Open SQL Server Management Studio and Choose the Database to be mirrored.
2. Establish endpoints for mirroring security
3. Backup and restore the database to a mirror server instance
4. Establish the mirroring session between the two servers.
5. Set up the witness if automatic failover is needed.

The syntax for establishing endpoints for mirroring security is:

CREATE ENDPOINT Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT = 7022)
FOR DATABASE_MIRRORING (
    AUTHENTICATION = WINDOWS KERBEROS ,
    ENCRYPTION = REQUIRED ALGORITHM AES
)
GO

Once the endpoint is established, the server instances will communicate through this endpoint when exchanging information.

Overall, setting up Database Mirroring requires careful planning and execution. The optimal mode for any specific application should be chosen based on the availability and data loss requirements of the application.

Configuring Database Mirroring

There are a number of configuration options for Database Mirroring in SQL Server. These options allow you to control when and how mirroring occurs, and provide you with the flexibility to customize mirroring to suit your specific requirements.

Some of the key configuration options include:

Partnership Settings: This includes endpoints for connectivity, endpoints url, and endpoints security options. Generally, in mirroring, a principal server sends transaction logs to a mirror server, which then applies them to its copy of the database. These endpoints enable servers to communicate with each other for these purposes.

Operating Modes: The three operating mode mentioned earlier—High Safety with Automatic Failover, High Safety without Automatic Failover, and High Performance— can be configured based on the application requirements.

Mirroring Monitor: The Mirroring Monitor displays information about mirroring sessions and servers.

Failover: This specifies which server becomes the principal server in case of the current principal server failure.

Failover Modes: Failover can be automatic or manual. In automatic mode, if the principal server fails, the mirror server is selected to become the new principal. In manual mode, the IT administrator chooses which server becomes the principal server.

Quorum: When multiple servers are used to provide mirroring services, they are often configured with a quorum model to ensure that all servers have current data. Quorum models determine the number of servers that must be operational in order to perform a failover.

To configure Database Mirroring, you need to use the SQL Server Management Studio. In the Properties window of a specific Database, you can select ‘Mirroring’ where you can customize the various options mentioned above.

For instance, to configure Database Mirroring for a specific database, you would select the database in SQL Server Management Studio. Next, select ‘Mirroring’ from the properties, configure the operating mode, failover mode or witness server, firewall port numbers for security, and then enable mirroring.

Overall, Database Mirroring provides a range of configuration options that allow you to tailor mirroring to your specific requirements.

Understanding Automatic Failover

Automatic Failover is a feature of Database Mirroring in SQL Server that provides uninterrupted service to a client application when there is a failover from the principal server to the mirror server.

The failover process is triggered automatically by a monitoring process that runs on the witness server, which checks the status and availability of the principal and mirror servers. If the principal server becomes unavailable, the witness server automatically initiates a failover to the mirror server, which then becomes the new principal server. Once the new principal server is up and running, clients can connect to and use it for ongoing transactions.

Automatic Failover requires a witness server. The witness server can be a stand-alone server or it can be housed within one of the participating servers. It is responsible for monitoring the principal server and, if it goes offline, initiating a failover to the mirror server. If the witness server itself is unavailable, then automatic failover will not occur.

For Automatic Failover to work, the High Safety with Automatic Failover mode must be selected when configuring mirroring. The failover mode must be set to ‘Automatic’ to ensure that automatic failover occurs when the principal server fails.

Overall, Automatic Failover provides seamless service continuity to client applications in case of unplanned outages. It is recommended to use Automatic Failover for applications that require near-constant uptime, and for which even a short period of downtime could result in serious business disruption.

In order to utilize Automatic Failover, the HA configuration of the system needs to be setup in a way that allows automatic failover as described in the subheading.

Summary

Database Mirroring is a powerful feature of SQL Server that can increase the availability of your databases by maintaining copies of your data on multiple servers. In this article, we learned about how Database Mirroring works, how to set it up, how to configure it, and how to use automatic failover to minimize downtime in case of database failures. As a developer, it is important to incorporate Database Mirroring into your systems to ensure that your data is reliably available in case of unexpected failures.