Mirroring SQLServer database

Halfway between replication and server clustering, mirroring of databases is a good solution for high availability. The principle is simple: transactions on the primary instance are also sent to the mirror instance. This way in case of crash of principal it is possible to switch (automatically or not) on the mirror with a loss of data at best and nonexistent at worst minimal.

Unlike replication or cluster, the mirror database is not accessible at the same time as the main base and will in fact used only when the main problem. This is not a way to spread the load across multiple servers but a hot backup, that is to say a current backup of the database available immediately.

In the following article I will detail the steps mirroring a database with SQLServer 2005.

Requirements and Basic Principles

First make sure that the version of SQLServer can be used for mirrored. Developer and Enterprise versions allow all operations related to the mirrors while the Standard version does not allow the high performance mode. Express versions and Working Group do than take the role of witness.

Now see a little more detail the meaning of these words:

- Partner: This is simply an instance involved in a mirror. Note also that mirroring can be done on a single server with two instances of SQLServer.

- Witness: this is an optional body that will monitor the availability of both partners of the mirror. If the principal does not respond, the witness will interpret this as a server crash and, as configured, will automatically activate the mirror database. This behavior is equivalent to the quorum for clusters. This raises an important point: if the use of the principal is very important it is possible that the witness can not be contacted within the time limit (10 seconds by default). This could lead to a problem of unwanted automatic failover to the mirror. It is recommended to keep the CPU utilization below 50%.

- High security (High Safety): In this mode mirroring, transactions are sent to the principal and the mirror. Once the mirror indicates that the transaction was effected, the principal makes his COMMIT. This mode is synchronous and can be configured such that in case of crash of the primary mirror database is automatically activated.

- High performance (High Performance): in this mode, transactions are conducted on the primary and once made ​​the COMMIT is transmitted to the mirror. This mode is asynchronous and can not slow down the principal. There is no need to witness in this mode.

Before Service Pack 2 for SQLServer 2005, mirroring status was still experimental and could be activated directly at least start the proceedings with a switch 1400 (NET START MSSQLSERVER / T1400).

It is recommended to install SP2 to use mirroring.

Mirroring a database in High Performance mode

Initially it is advisable to do some tests in the mode that has the least likely to impact the main proceeding, that is to say, the High Performance mode. Indeed, the High Security modes may slow down the one hand the main while waiting for confirmation of the mirror and the other accidental cause an automatic failover if the primary problem.

- Perform a full backup of the database to be mirrored and restore it on the mirror instance with NORECOVERY (Second bubble Options tab in the interface of restoration). If you get an error 1418 is probably due to the neglect of NORECOVERY.

The base is then visible in Restoring state ...

- Make a backup of transaction log database to be mirrored and restore it on the mirror instance with NORECOVERY (Second bubble Options tab in the interface of restoration). If you get an error 1416 is probably due to the neglect of NORECOVERY.

The base is still in a state Restoring ...

- On the main, right click on the base to mirror and select Task> Mirror. The wizard mirroring starts and then just click on the Configure Security. Then click Next, then click No and Next. Select the main forum, click Next, select the mirror instance and connect to and click Next. Need to learn the necessary accounts and click Next and Finish.

Once setup is complete you can start mirroring itself and the first synchronization, which would normally be very quick. Once you sync the basic state is found in Mirror, Synchronized / Restoring ...).

State bases mirror

You can track the status of the mirror by right-clicking on one of the main bases and to Tasks> Launch Database Mirroring Monitor.

Mirroring a database in High Security mode

The establishment of a High Security mirror is essentially identical to High Performance mode except that a third instance will be designated as a control to check availability of the two partner instances.

Assistant in the mirror needs to be answered Yes to the first question. A further step will appear after the appointment of two bodies partners asked to indicate the instance Witness (Witness).

It will further determine if you want an automatic failover to the mirror in the main event of a crash or not, it will obviously depend on business needs as well as tests that have been made.