Microsoft® SQL Server® 2012 Bible

710

Part V: Enterprise Data Management

The default timeout for communication between the principal, mirror, and witness servers is 10 seconds. If the

principal does not respond within the timeout period, it is considered to be down. If you use high-safety mode, you

can change the timeout period using the ALTER DATABASE SET PARTNER TIMEOUT command. The default

timeout of 10 seconds works well for most environments. If you do want to change the timeout period, do not set it

below 10 seconds because this may cause false failures.

4. The mirror server recovers the mirror database.


5. The mirror server forms a quorum with the witness server.


6. The mirror server becomes the new principal server and brings the mirror database
   online as the new principal database.


7. The old principal server, when it is back online, takes the mirror role, and the old
   principal database becomes the new mirror database and starts synchronizing with
   the new principal database.
   Manual Failover is available only in synchronous mode with and without failover. As the
   name suggests, you decide to switch the roles of the servers and manually failover the
   database. Manual failover is used for planned downtime (for example, during hardware or
   software upgrades). Manual failover is allowed only when the partners connect and the mir-
   roring state is SYNCHRONIZED. During a manual failover, the clients are disconnected from
   the principal database and the roles of the partners are switched.

To perform a manual failover using SQL Server Management Studio, follow these steps:

1. In the Object Explorer on the principal server in SQL Server Management Studio,
   right-click the principal database, and select Properties.


2. On the Mirroring page, as shown in Figure 27-8, click the Failover button.


3. You are prompted for confi rmation. Click Yes to confi rm. This performs a manual
   failover.
   To perform a manual failover for the AdventureWorks database in code, connect to the prin-
   cipal server and execute the following:

ALTER DATABASE AdventureWorks SET PARTNER FAILOVER;

Forced Failover also referred to as forced service (with possible data loss) is available only in

synchronous mode without failover and asynchronous mode. If the principal server is lost,

the principal database is unavailable to the clients. You can make the database available by

manually forcing service on the mirror server by executing the following command on the

mirror server:

ALTER DATABASE AdventureWorks SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;

This brings the database online on the mirror server, which becomes the new principal

server. When the old principal server becomes available, it automatically assumes the

c27.indd 710c27.indd 710 7/31/2012 9:50:30 AM7/31/2012 9:50:30 AM

http://www.it-ebooks.info