Mirroring allows you to create hot standby servers. SQL Server 2005 allows you to mirror any database except for master, msdb, temp, and model. You can configure and enable mirroring using the Mirroring page in the Database Properties dialog box. As discussed in Chapter 2 in the subsection titled "Ensuring Availability and Scalability," mirroring requires up to three servers: a principal server, a mirror server, and a witness server.
After you have installed SQL Server 2005 on the servers that will be used in mirroring, you must prepare for mirroring by doing the following:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to act as the principal.
Right-click the database you want to work with and choose Properties. This displays the database’s Properties dialog box. On the Options page, set the Recovery Model to Full. Click OK.
Right-click the server and choose Properties to display the Server Properties dialog box. On the General page, click Configure. This starts SQL Server Configuration Manager with the server selected for configuration.
Select the SQL Server 2005 Services node in the left pane. In the right pane, note the Log On As account for the SQL Server service on the instance that will act as the principal.
Perform a full backup of the principal database.
In Object Explorer view, connect to the server you want to act as the mirror.
Restore a full backup of the principal database on the mirror server instance with NORECOVERY.
Right-click the database you want to work with on the mirror and choose Properties to display the database’s Properties dialog box. On the Options page, set the Recovery Model to Full. Click OK.
Right-click the server and choose Properties to display the Server Properties dialog box. On the General page, click Configure. This starts SQL Server Configuration Manager with the server selected for configuration.
Select the SQL Server 2005 Services node in the left pane. In the right pane, note the Log On As account for the SQL Server service on the instance that will act as the mirror.
In Object Explorer view, connect to the server you want to act as the witness (if any).
Right-click the server and choose Properties to display the Server Properties dialog box. On the General page, click Configure. This starts SQL Server Configuration Manager with the server selected for configuration.
Select the SQL Server 2005 Services node in the left pane. In the right pane, note the Log On As account for the SQL Server service on the instance that will act as the witness (if any).
After you prepare for mirroring, you then must configure the mirror endpoints and mirror security. To do this, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to act as the principal.
Expand the Databases folder. Right-click the database you want to work with and choose Properties to display the database’s Properties dialog box.
On the Mirroring page, click Configure Security. When the wizard starts, click Next on the opening page.
If you are using a witness server to allow synchronous mode with automatic failure, accept the default answer of Yes in the next page of the wizard, and then click Next. Otherwise, click No to specify that you will not be using a witness server.
Typically, you will want to configure security on all the servers that are part of the mirror set, so accept the default (which has the appropriate server types selected) on the next page of the wizard and click Next.
The current server is selected by default as the principal. Set a listener port and endpoint name for this server as part of the mirror set. If the principal, mirror, and witness are instances on the same server, the related endpoints must use different listener ports. Otherwise, the endpoints can use the same listener ports. The default is TCP port 5022.
Next, use the Mirror Server Instance drop-down list to select the server that will act as the mirror. The principal and mirror cannot be the same server instance.
Set a listener port and endpoint name. The default endpoint name is Mirroring. If the principal, mirror, or witness are instances on the same server, the related endpoints must use different listener ports. Otherwise, the endpoints can use the same listener ports. The default is TCP port 5022.
Next, use the Witness Server Instance drop-down list to select the server that will act as the witness. The principal and witness cannot be the same server.
Set a listener port and endpoint name for the witness server. The default endpoint name is Mirroring. If the principal, mirror, or witness are instances on the same server, the related endpoints must use different listener ports. Otherwise, the endpoints can use the same listener ports. The default is TCP port 5022.
If the server instances use different domain accounts for their SQL Server service accounts, enter the account names in the boxes provided. For domain accounts, be sure to enter the account name in DOMAINusername format. If the service accounts are different, the wizard will grant CONNECT permissions on the endpoints for each account.
Click Next, and then click Finish. The wizard then configures security for mirroring.
Set the mirror operating mode you want to use, and then click OK. If you have configured a witness server, you can use Synchronous With Automatic Failover mode to ensure high availability.
To start mirroring, click Start Mirroring on the Options page in the Database Properties dialog box.
Click Yes to allow SQL Server to close all other connections to the database temporarily while configuring mirroring.
You can view the current status of mirroring by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to act as the principal.
Expand the Databases folder. Right-click the database you want to work with and choose Properties to display the database’s Properties dialog box.
Select the Mirroring page. The current mirroring status is displayed at the bottom of the page.
After you have configured and started mirroring, you can manage mirroring on the Mirroring page by using the following options:
Click Pause to temporarily stop mirroring. When prompted to confirm, click Yes. Click Resume to resume mirroring.
Click Stop Mirroring to stop mirroring. When prompted to confirm, click Yes. Click Start Mirroring to start mirroring again.
If you are using a witness, failover occurs automatically. You can also force failover by clicking the Failover button.
Mirroring details can be obtained using the following catalog views:
sys.database_mirroring This catalog view displays the database mirroring metadata for each mirrored database in a server instance.
sys.database_mirroring_endpoints This catalog view displays information about the database mirroring endpoint of the server instance.
sys.database_mirroring_witnesses This catalog view displays the database mirroring metadata for each of the sessions in which a server instance is the witness.
In Performance Monitor, you can use the SQLServer:Database Mirroring object to monitor mirroring performance:
Use the Log Bytes Sent/sec counter to monitor the amount of log data sent per second.
Use the Transaction Delay counter to determine if mirroring is impacting performance on the principal server.
Use Redo Queue and Log Send Queue counters to determine if the mirror database is keeping up with the principal database.