Microsoft® SQL Server® 2012 Bible

709

Chapter 27: Database Mirroring

27

ALTER DATABASE AdventureWorks SET PARTNER SUSPEND;

To resume the database mirroring session for the AdventureWorks database in code, con-

nect to either the principal or mirror server and execute the following:

ALTER DATABASE AdventureWorks SET PARTNER RESUME;

To remove a database mirroring session 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 Remove Mirroring button.


3. You will be prompted for confi rmation. Click Yes to confi rm. This removes the data-
   base mirroring session. This means that the relationship between the partners and
   witness is removed, and each partner is left with a separate copy of the database.
   The mirroring database will be left in the RESTORING state because the database
   was created using the restore with norecovery command.


4. To resume the database mirroring session after removing it, you need to confi gure a
   new database mirroring session, as explained earlier in this chapter.

To remove the database mirroring session for the AdventureWorks database in code, con-

nect to either the principal or mirror server and execute the following:

ALTER DATABASE AdventureWorks SET PARTNER OFF;

Role Switching

Role switching in database mirroring is a process to change the principal and mirror roles.

Three types of role switching exist based on the database mirroring operating mode: auto-

matic failover, manual failover, and forced failover.

Automatic failover is available only in synchronous mode with failover. In this mode,

if the principal database becomes unavailable due to any failure and the mirror and

witness servers are still connected and the mirroring state is SYNCHRONIZED, automatic

failover occurs. Here is a high-level sequence of events that occur in an automatic failover

scenario:

1. The principal database becomes unavailable due to some failure.


2. If the principal server is still available, the state of the principal database is
   changed to DISCONNECTED and all the clients are disconnected from the principal
   database.


3. The mirror and the witness server detect the failure.

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

http://www.it-ebooks.info