Microsoft® SQL Server® 2012 Bible

708

Part V: Enterprise Data Management

Monitoring Using SQL Server Profi ler

To capture the time taken for database mirroring failover, launch the SQL Server Profi ler

tool as follows:

1. Select Start ➪ Programs ➪ Microsoft SQL Server 2012 ➪ Performance Tools ➪ SQL
   Server Profi ler.


2. From the File menu, select New Trace.


3. This brings up the Connect to Server dialog box. Enter the principal server informa-
   tion, and click Connect.


4. On the Trace Properties dialog box, enter the trace name in the General tab.


5. On the Trace Properties dialog box, click the Events Selection tab. Select the Show
   All Events check box. Expand the Database event, and select the Database Mirroring
   State Change check box. Select the columns TextData (gives a description of the
   database mirroring state change) and StartTime (tells you the time at which the
   event started).


6. Click Run to start the capture.

Pausing or Removing Database Mirroring

After confi guring database mirroring, you can pause, resume, or remove database mirror-

ing. If database mirroring impacts your applications performance, you may want to pause

a database mirroring session and improve the performance. Pausing a database mirroring

session causes the mirroring state to change to suspended. During this time the principal

does not send any transactions to the mirror, and its principal database’s transaction log

keep growing, even if you schedule transaction log backups. The transaction log is not be

truncated because it has to send the transactions to the mirror after the database mirror-

ing session resumes.

To pause 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 Pause button.


3. You will be prompted for confi rmation. Click Yes to confi rm. This pauses the data-
   base mirroring session and changes the Pause button to Resume.


4. Click the Resume button to resume the database mirroring session.
   To pause the database mirroring session for the AdventureWorks database in code, connect
   to either the principal or mirror server and execute the following:

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

http://www.it-ebooks.info