SQL Server 2008 Database Mirroring Deployment Instance Three configuration Witness server _mssql2008

The previous configuration of the mirrored database has been completed and the failover test has been carried out. Next you will deploy the witness server for automatic failover.

First, about the witness server

1. To support automatic failover, you must configure the database mirroring session in high security mode and also have a third server instance (also known as a "witness server"). The witness server is an optional instance of SQL Server that enables the mirror server in the High Security mode session to recognize whether to initiate automatic failover. Unlike these two partners, the witness is not available for the database. The only role of the witness server is to support automatic failover.

2. In order to set up the witness server for the database, the database owner assigns the database engine instance to the role of the witness server. The witness server instance can run on the same computer as the principal server instance or the mirror server instance, but this can significantly reduce the reliability of automatic failover. Therefore, it is recommended that the witness server be on a different computer.

3. In high-performance mode, the witness server has a detrimental effect on availability. If the witness is configured for a database mirroring session, the principal server must be connected to at least one other server instance, the mirror server or the witness server, or connected to both servers. Otherwise, you will not be able to use the database and cannot force service (data may be lost). Therefore, for high-performance models, we strongly recommend that you always set the witness server to OFF.

Ii. about automatic failover

1. A database mirroring session with a witness is supported for automatic failover only if it is running under High security mode ("High security mode with automatic failover feature"). In high security mode with automatic failover, automatic failover occurs after the database is synchronized, if the principal database becomes unavailable. Automatic failover causes the mirror server to take over the role of the principal server and bring a copy of its database online as the principal database. Because each transaction committed in the principal database is also committed in the mirrored database, the database needs to be synchronized to prevent loss of data during the failover process.

2. Conditions required for automatic failover

A, the database mirroring session must be running in high security mode, and the witness server must be processed.

B, the mirrored database must already be synchronized. This will ensure that all logs sent to the mirror server are written to disk.

C, the principal server has interrupted communication with the rest of the database mirroring configuration, while the mirror server and the witness will retain the quorum. However, if all server instances are disconnected, and the witness and mirror servers re-establish communication later, automatic failover does not occur.

D, the mirror server has detected the loss of the principal server

E, the way the mirror server detects the principal server failure depends on whether the fault is a hard fault or a soft fault.

More automatic failover Content reference

Http://msdn.microsoft.com/zh-cn/library/ms189590.aspx

Three, automatic fault transfer principle

1. If the principal server is still running, change the status of the principal database to disconnected and disconnect all clients from the principal database.

2. The witness server and the mirror server register the principal server as unavailable.

3. If there are any waiting logs in the redo queue, the mirror server completes the roll forward mirror database operation

4. The previous mirror database, as the new online principal database, restores all of the transactions by rolling back uncommitted transactions as soon as possible. Locks will isolate these transactions.

5. When a current principal server is again joined to the session, it will assume that its failover partner now has a principal role. The previous principal server takes over the mirror role and takes its database as a mirrored database. The new mirror server synchronizes the new mirror database with the principal database as soon as possible. Once the new mirror server synchronizes the database, the failover can be performed again, but in reverse execution ...

The following figure illustrates an instance of automatic failover.

Four, look at the witness server installed SQL Server 2008

See one of the SQL Server 2008 Database Mirroring Deployment Instances

V. Configuring the witness server

1, the main database server, right click on the database, select Tasks-Mirror

2, in the pop-up Database Properties page, select the Mirror page, click on the right side of the configuration security

3, click Next, select Include witness server

4, click Next, the witness server configuration

5, click two times next, fill in the witness server address or machine name and site name

6, fill in the service account

7, view the summary information, click to complete

Vi. Test automatic failover

1, the current principal server is Hytest01, mirror database is Hytest02

2, the principal server Hytest1 network disconnected to see whether the database automatically transferred to the mirror server Hytest02

Mirroring settings show that the principal server and mirror server roles are also interchangeable.

Some people will say, two database, IP address is not the same, how to write the connection code? Do you want to manually change the code after a failure? In fact, using ado.net or SQL Native client can automatically connect to the failover partner, the connection string is as follows:

connectionstring= "Datasource= A; Failover partner=b;initial catalog=adventureworks;integrated security=true; " Datasource= A;

After this setting, the client can automatically switch the database

The mirrored high-availability configuration instance for SQL Server 2008 is complete.