SQL Server 2005 – Mirror Server

In this tutorial you will learn about Mirror Server in SQL Server 2005 – Preparing the Principal and Mirror Server, Establishing a Mirroring Session, Establishing a Witness Server, Executing Transactions, Simulating Principal Server Failure, Restarting the Failed Server, Terminating the Mirror Session and Configuring Database Mirroring.

Preparing the Principal and Mirror Server

Database mirroring is easy to set up and can be made self monitoring for automatic failover in the event of the principal server being unavailable. The first step is to configure the relationship between the principal server and the mirror server. This can be a synchronous mirroring with a witness server that provides the highest availability of the database. A drawback in this type of configuration is the need to log transactions on the mirror before such transactions being committed to the principal server may retard performance. Asynchronous mirroring with a witness server provides high availability and good performance. Transactions are committed to the principal server immediately. This configuration is useful when there is latency or distance between the principal server and the mirror. The third type of mirroring configuration is the Synchronous mirroring without the witness server. This guarantees that data on both servers is always concurrent and data integrity is of a very high order. However, automatic failover cannot occur as there are not enough servers to form a quorum decision on which server is to take the role of the principal server and which should be the mirror server.

Establishing a Mirroring Session

Database mirroring is done within a mirror session. A mirror session maintains information about the state of the databases, the mirroring partners and the witness server. The mirror server identifies the most recent transaction log record that has been applied to the mirror database and requests for subsequent transaction log records from the principal server. This phase is called the synchronizing phase.

Once synchronization is complete the principal server will transmit the transaction logs to the mirror server even as changes are made. The mirror database is continually rolled forward to match the principal database. The operating mode of the mirror database(synchronous or asynchronous) will determine whether the transaction log records are applied to the mirror database immediately or after the transactions have been recorded in the principal server.

The mirror session maintains information about the state of any witness servers. It ensures that the witness server is visible both to the principal and the mirror servers.

A mirroring session is terminated by a number of causes. There may be a communication or server failure. The principal server may fail and the mirror may become the principal server. This can happen automatically or manually depending on the operating mode. The session may also be terminated by the manual intervention of the Database Administrator using the TRANSACT-SQL ALTER DATABASE command. Mirroring may be terminated or suspended in the process

Establishing a Witness Server

A witness server is a must where the DBA wants to implement automatic failover and the configuration must be in the synchronous operating mode. The witness server is usually on a different computer from the principal and the mirror servers. However, one server can act as a witness for multiple mirror partnerships.

The ALTER Database command with the SET WITNESS clause is used on the principal server to create a witness server. The Witness server address is specified and the endpoint port is defined to act as the witness for the server_network_address parameter.

A witness server can be disabled. However, the mirroring session will continue even when the witness server is disabled. Automatic failover will no longer be possible.

Information about the witness server can be viewed in sys.database_mirroring_witnesses catalog view.

Executing Transactions

The ALTER DATABASE command has to be run on the mirror server specifying the principal server endpoint address and then the same has to be done on the principal server so that synchronization can commence. The operating mode has to then be selected. By default the operating mode is synchronous. This can be changed by running the ALTER DATABASE command with SET PARTNER SAFETY clause on either partner server. The saftety_mode parameter can be either OFF or FULL. The mirror partnership information can be viewed by running a query on sys.databases catalog view.

If the transaction safety is set to full, the principal and mirror servers operate on synchronous transfer mode. The transaction logs are hardened in the principal server and transmitted to the mirror and then the principal waits for the mirror to harden its logs and send its response. When the safety is OFF the principal does not wait for the acknowledgement of the mirror. In this instance the principal and the mirror may not be synchronized at all times.

Synchronous transfer guarantees that the mirror is a faithful image of the principal database transaction log

Simulating Principal Server Failure

A principal server failure can be simulated in test scenarios to ensure that failover is smooth. Failover implies that the mirror server takes over as the principal server and the mirror database will have to act as the principal database. The failover can be manual, automatic or forced.

Automatic failover occurs when the high availability operating mode is synchronous and the safety is FULL and a witness is part of the session. Manual occurs in high availability and high protection operating modes. Safety has to be full and the partner databases are synchronized. Forced service is used primarily in the High Performance mode with safety off.

Simulating Principal Server failure can be done by manual intervention of the DBA in an orderly way. The safety will have to be first set to FULL and the principal and the mirror databases synchronized. Manual failover can be invoked by invoking the ALTER DATABASE command on the principal server or by clicking the failover button in the Database Properties/Mirroring dialog in the Management Studio. A manual failover causes current users to be disconnected and all unfinished transactions to roll back. These transactions will then be recovered from the redo queue. The mirror assumes the role of the principal server and the two servers will negotiate a new starting point for mirroring based on their mirroring failover LNS.

If the principal server is no longer operating, and safety is OFF, forced service can be resorted to. This service causes some data loss.

Restarting the Failed Server

A failed server can be restarted and can be synchronized with the principal server or the mirror server as the case may be. Any suspending of transactions causes the log on the principal server to grow with the transactions being logged and stored. Once the mirror session is resumed, the principal transaction log is synchronized and written on to the mirror database log.

Terminating the Mirror Session

A mirror session can be manually terminated and the relationship between the servers can be ended. When a session is ended, all information about the session is removed from all servers and leaves both the principal server and the independent server with an independent copy of the database. The mirror server database will remain in the restoring state until it is manually recovered or deleted.

Configuring Database Mirroring

Configuring a mirror server includes configuring the mirror server and the database.

The server designated as the mirror must be accessible and trusted by the principal database server. Ideally both servers should belong to the same domain. The mirror server should also have sufficient memory and processing power to act as the principal server in the event of failover. It should be able to support users and applications without noticeable difference in the quality of service.

The mirror database must be created manually. The file structure must match the principal database file structure. Both databases must implement full recovery model. Once the mirror database is created, the latest full database backup of the principal database must be applied to the mirror using the RESTORE DATABASE command with the WITH NONRECOVERY clause.

The next step is to enable the communication mechanism through which the mirroring will take place. This implies creation of endpoints on both servers. The endpoint controls the Transmission Control Protocol(TCP) port on which the server listens for database mirroring messages. The endpoint also defines the role that it must perform. A server needs to have only one configured endpoint regardless of the number of mirroring sessions it participates in. However, each instance requires a unique port on which to listen.

The next step is to establish a mirroring session. The process of establishing a mirroring session has been discussed above. It involves creating a mirroring session using the ALTER DATABASE command on the mirror server first and then on the principal server. The server_network_address parameter will have to be specified. Then a partnership will have to be created on the mirror server, the operating mode will have to be changed and so on.

In this lesson we have examined the new enhanced high availability features of SQL Server 2005. We have also examined how database mirroring works and how a database can be configured for mirroring using different configuration options. In the lesson that follows we shall examine the replication features of this version of SQL Server.