Brief description of SQL Server 2005 database mirroring related knowledge _mssql2005

In SQL Server databases, database mirroring is the primary software solution for improving database availability. Database mirroring is implemented on a per-database basis and applies only to databases that use the full recovery model. The simple recovery model and the bulk-logged recovery model do not support database mirroring, and database mirroring cannot mirror master, msdb, tempdb, or the model database. This article we mainly introduce the relevant knowledge of database mirroring, then let us come to understand together!

Database mirroring maintains two copies of a database that must reside on different instances of the SQL Server database engine (the server instance). Typically, these server instances reside on computers in different locations. One of the server instances makes the database service to the client (the "principal server"), while the other server instance acts as a hot standby or standby server (the "mirror server"), depending on the configuration and status of the mirroring session. When synchronizing a database mirroring session, database mirroring provides a hot standby server that enables fast failover without data loss for committed transactions. When a session is not synchronized, the mirror server is typically used as a standby server (which may cause data loss).

Advantages of Database Mirroring

Database mirroring is a simple strategy that has the following advantages:

Enhance data protection capabilities.

Database mirroring provides complete or near-complete data redundancy, depending on whether the running mode is a high-security or high-performance mode. For more information, see "Running Mode" later in this topic.

Increase the availability of your database.

In the event of a disaster, automatic failover can quickly make an alternate copy of the database available online (without losing data) in a high-security mode with automatic failover. In other running modes, the database administrator can choose to force services, which may lose data, in place of an alternate copy of the database. For more information, see "Role switching" later in this topic.

Increase the availability of the production database during the upgrade.

To minimize the downtime of a mirrored database, you can upgrade the instances of SQL Server that you want to participate in a database mirroring session sequentially, thereby making downtime a single failover time. This form of upgrade is called "rolling upgrades."

How Database Mirroring Works

In database mirroring sessions, the principal server and the mirror server communicate and collaborate as "partners." Two partners play a complementary role in the session: Principal role and mirror role. At any given time, a partner plays a principal role and another partner plays the role of mirroring. Each partner has its current role. The partner who owns the principal role is called the principal server, and its copy of the database is the current principal database. The partner who owns the mirror role is called the mirror server, and its copy of the database is the current mirrored database. If database mirroring is deployed in a production environment, the principal database is the production database.

Database mirroring involves resetting each insert, update, and delete operation that is performed on the principal database to the mirrored database as soon as possible. Redo is done by sending each active transaction log to the mirror server, which applies the log records sequentially to the mirror database as soon as possible. Unlike replication performed at the logical level, database mirroring is performed at the physical logging level.

Run mode

A database mirroring session runs with either synchronous or asynchronous operations. Under asynchronous operations, transactions do not need to wait for the mirror server to write logs to disk to commit, which maximizes performance. Under synchronization, committed transactions are committed on both partners, but the transaction latency is prolonged.

There are two modes of mirroring operation. One is "High security mode", which supports synchronization operations. In high security mode, when the session starts, the mirror server synchronizes the mirror database with the principal database as soon as possible. After the database is synchronized, committed transactions are committed on both partners, but the transaction latency is prolonged.

The second mode of operation, "High-performance mode," runs asynchronously. The mirror server attempts to synchronize with the log records sent by the principal server. Although the mirrored database may lag slightly behind the principal database, the time interval between these two databases is usually small. However, the time interval increases if the principal server's workload is too high or the mirror server system is overloaded.

In high-performance mode, when the principal server sends a log record to the mirror server, it immediately sends a confirmation message to the client without having to wait for the mirror server's confirmation. This means that transactions do not need to wait for the mirror server to write the log to disk to commit. This asynchronous operation allows the principal server to run with minimal transaction latency, but some data may be lost.

All database mirroring sessions support only one principal server and one mirror server. The following figure illustrates this configuration.

High-security mode with automatic failover requires a third server instance, called a witness. Unlike these two partners, the witness is not available for the database. The witness server only supports automatic failover by verifying that the principal server is enabled and running. The mirror server initiates automatic failover only if the mirror server and the witness remain connected to each other after disconnecting from the principal server.

The following illustration shows the configuration that contains the witness server.

Transaction security and Operation mode

Whether the run mode is asynchronous or synchronous depends on the transaction security settings. If you specifically use SQL Server Management Studio To configure database mirroring, the transaction security settings are automatically configured when you select Run mode.

If you are using Transact-SQL metabase mirroring, you need to know how to set up transaction security. Transaction security is controlled by the SAFETY property of the ALTER DATABASE statement. In the database being mirrored, SAFETY is full or off.

If you set the SAFETY option to full, the database mirroring operation executes synchronously after the initial synchronization phase. If you set up a witness in high security mode, the session supports automatic failover.

If the SAFETY option is set to OFF, the database mirroring operation executes asynchronously. The session runs in High-performance mode, and the WITNESS option should also be set to OFF.

Role switching

In the context of a database mirroring session, you can often use a procedure called role switching to interchange the principal and mirror roles. Role switching involves the conversion of the principal role to the mirror server. In role switching, the mirror server acts as a failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and makes a copy of its database online as the new principal database. The previous principal server (if any) would act as a mirroring role and its database would become a new mirrored database. These roles can be repeatedly switched back and forth.

There are three types of role transitions:

Automatic failover

This requires high security mode with a mirrored server and a witness server. The database must be synchronized, and the witness must be connected to the mirror server.

The role of the witness server is to verify that the given partner server is up and running. If the mirror server is disconnected from the principal server, but the witness server remains connected to the principal server, the mirror server cannot initiate failover.

Manual failover

This requires the use of high security mode. The partners must be connected to each other, and the database must be synchronized.

Force service (may cause data loss)

In high-performance mode and in high-security mode without automatic failover, if the principal server fails and the mirror server is available, you can force the service to run.

Important NOTE:

High-performance mode is used to run without a witness server. However, if a witness is present, the Force service will require the witness server to connect to the mirror server.

In either role switching, once the new principal database is online, the client application recovers quickly by reconnecting to the database.

Database Mirroring Support

The database mirroring partners and witness servers are supported by SQL Server Standard Edition SP1 and later, and are supported by SQL Server version Enterprise Edition SP1 and later versions. The premise is that each partner must use the same version, and only SQL Server Enterprise Edition SP1 and later support asynchronous database mirroring (high-performance mode). The witness server is also supported by SQL Server Workgroup Edition SP1 and later, as well as SQL Server Express Edition SP1 and later versions.

About SQL Server 2005 database mirroring is introduced here, hope to bring you harvest!