Chapter 13 Administering and Managing Database Mirroring
This chapter takes a systematic look at database mirroring, one of the four high-availability alternatives offered with SQL Server 2008. Database mirroring was probably one of the most anticipated features of SQL Server 2005. Unfortunately, it wasn’t ready for prime time and was actually pulled and not officially supported when Microsoft shipped SQL Server 2005, creating slight disappointment. It wasn’t until the release of SQL Server 2005 Service Pack 1 that database mirroring was officially supported in production environments.
Database mirroring offers increased database availability and database protection by providing and maintaining a hot standby database on another instance of SQL Server 2008. A key point to note is that the mirror database is an exact copy of the principal database. With database mirroring, continuous support is given to organizations, bolstering operations by decreasing downtime and reducing data loss.
To ensure that organizations can reap the full benefits of database mirroring, the topics in this chapter are geared toward giving you the knowledge necessary to understand the full potential of database mirroring and how it meets different business scenarios, as well as how to implement and maintain it successfully. Specifically, the chapter focuses on an overview of database mirroring, terminology, and ways to use database mirroring. The middle sections of the chapter focus on database mirroring configuration and administration. The final sections of the chapter discuss how to manage and monitor database mirroring.
Microsoft realized that the introduction of database mirroring in SQL Server 2005 was widely accepted by many customers. It enabled organizations to implement high availability in a datacenter without the requirement of shared storage. Moreover, because shared storage was not required, the principal and mirrored servers could be placed in separate geographical locations for both high availability and disaster recovery. Unfortunately, in SQL Server 2005 there were some limitations.
With SQL Server 2005, the amount of network bandwidth required was a limitation when trying to mirror large amounts of data over the WAN. It was common for database mirroring to fail as the WAN would get saturated and transactions would either no longer make it or trickle across the wire. In addition, during these times of WAN saturation, performance degradation on the principal database would occur, especially if you were using the High Safety with Automatic Failover mode.
SQL Server 2008 addresses these issues by introducing a new feature called Database Mirroring Log Stream Compression. Application performance and throughput is enhanced over limited WAN connections as transaction log data in transit from the principal server to the mirrored server is extremely compressed. By compressing transaction log data by default, more data can be sent from the principal server to the mirrored server, thus increasing performance, and more log records can be shipped in a given time. Log Stream Compression rates of at least 13 percent or more have been achieved in specific lower-bandwidth networks.
Caution
The only caveat when using Database Mirroring Log Stream Compression is that there is increased overhead on the processor, as the processor must compress and decompress the logs, which translates to higher CPU usage. Depending on the workload, CPU usage could double compare to not using log compression. If WAN utilization and saturation is not an issue, then Log Stream Compression can be disabled with Trace Flag 1463. The behavior returns to the same functionality as in SQL Server 2005.
Automatic page repair and recovery from corrupted pages is another new feature for database mirroring when using SQL Server 2008. From a high level, the SQL Server hosting the mirrored database will try to resolve specific types of errors and corruption that prevent a data page from being read. If an error is detected, the server will attempt to obtain a fresh copy of the data and replace the corrupted data page, thus increasing data consistency among the principal and mirror databases and minimizing a database outage. It is typical to see an 823 or 824 error in the SQL Server logs indicating that an error took place causing an automatic page repair attempt. These errors are subsequently followed by an event indicating that SQL Server 2008 successfully repaired the physical page by obtaining a copy from the partner. Error 823 represents a cyclic redundancy check due to failed data and error 824 indicates logical errors.
Note
Automatic recovery from corrupted pages can only try to successfully resolve a failed data page when one or more of the following errors occur: page has been marked as restore pending, logical errors, or a cyclic redundancy check (CRC). These errors are also referred to by number: 823, 824, and 829.
Additional enhancements associated with database mirroring consist of the following:
The mirror server writes received log records to disk asynchronously.
Twenty-one new performance counters have been added in order to better troubleshoot and understand database mirroring workloads.
If the principal database is configured for enhanced auditing, then the database mirrored server will also automatically include the SQL Server Audit Specification.
Faster recovery on failover can now be achieved with SQL Server 2008.
A new Dynamic Management View (DMV) has been implemented, sys.dm_db_mirroring_auto_page_repair, which allows tracking of 100 corrupt pages previously found in all mirroring sessions.
As mentioned earlier, database mirroring offers increased database availability and database protection by providing and maintaining a hot standby database on another instance of SQL Server 2008. Its usefulness is best witnessed when a failure takes place on a primary database. In this situation, the standby database becomes active and clients are redirected without the organization experiencing data loss or downtime.
Database mirroring is also commonly used to meet disaster recovery requirements and, therefore, should not be recognized only as an availability mechanism for a local site. When database mirroring becomes an integral part of an organization’s disaster recovery plan, a hot or warm standby database is typically placed in a physical location other than the primary active database.
Note
The primary database is commonly referred to as the principal database, and the hot or warm standby is referred to as the mirror database.
The principal database handles client activity, whereas the mirror database receives continuous transaction log changes through a dedicated and secure TCP endpoint. This process keeps the mirror database up to date and ready to take on client operations in the event of a failure. Depending on the configuration/operating mode, database mirroring can be configured for either synchronous or asynchronous operations.
Figure 13.1 depicts the internals of a database mirroring session.
Many database administrators find similarities between database mirroring and log shipping. They often refer to database mirroring as real-time log shipping or log shipping on steroids. However, in database mirroring, unlike log shipping, the primary server does not ship the logs to the standby server based on a time increment. Database mirroring transactions are continuously sent and committed between the principal and mirror; therefore, the databases are kept up to date.
Note
It is not possible to configure a database mirroring session on the SQL Server system databases, that is, the master, msdb, tempdb, or model databases. Failover clustering would be the alternative to provide high availability on the system databases.
Although you may be eager to install SQL Server 2008 database mirroring, you should take the time to fully understand all the new terminology and components that make up this new high-availability solution. By doing this, you and your organization can avoid running into roadblocks and will have an easier time with the installation. To prepare yourself for the installation, review the following terms regarding database mirroring:
Principal database— The principal database is the primary server in a database mirroring solution. This server maintains a live copy of the database that is accessed by clients and applications.
Note
The principal database must reside on a separate instance of SQL Server than the mirror database.
Mirror database— The mirror database is the target database, which reflects all the changes of the principal database through a secure dedicated channel. The mirror database is a hot or warm standby and is continuously updated by transferring transaction logs from the principal database in chunks.
Note
The Mirror SQL Server instance does not require a SQL Server license if the server is strictly used as a standby.
Witness server— The witness server is an optional component in a database mirroring session. Typically, this component resides on a dedicated SQL Server instance independent of both the principal database and the mirroring database servers. The witness facilitates the quorum role and monitors the database mirroring session. It initiates a failover from the principal to the mirror database in the event of a disaster. You may view the witness server as a requirement to support automatic failovers between the principal and mirror database instances. The Express edition of SQL Server 2008 can host the Witness Server role.
Quorum— The quorum monitors the state of a mirroring session. It controls the failover process based on communication among the principal, mirror, and witness databases. The principal server maintains the primary role of owning the database by maintaining quorum with either the mirror or witness. At least two servers are required to form a quorum; if the principal loses quorum with the mirror and/or the witness, a failover is initiated.
Synchronous/asynchronous— A database mirroring session can transfer data between the principal database and mirror database by either a synchronous or an asynchronous operation. When you use the synchronous transfer mechanism, a transaction is successfully completed when it is committed on the principal and the principal receives an acknowledgment from the mirror that the same transaction has been written and committed. This process guarantees transactional consistency between the principal and mirror; however, transaction commits and client performance may be hindered based on the network speed, mirror location, and available bandwidth between the principal and mirror server instances.
The asynchronous transfer mechanism commits transactions to the principal database much faster because it does not require an acknowledgment from the mirror. This process does not guarantee transactional consistency between the principal and mirror.
Automatic and manual failover— Database mirroring supports both an automatic and manual failover process between the principal and mirrored databases. The type of failover is dictated by the type of database mirroring configuration mode selected, whether a witness server is present, and the type of client used.
Transparent client redirect— In the event of a failure, clients and applications automatically redirect from the principal database to the mirror database, resulting in minimal downtime. Be aware that automatic failover requires the latest SQL client based on the .NET and SQL Server Native Client (SNAC) providers/libraries.
Database mirroring endpoint— SQL Server 2008 uses endpoints to achieve secure server-to-server communication and authentication over the network. When you’re configuring database mirroring, a dedicated endpoint is required exclusively for mirroring communications between both the principal and mirror database servers.
Following are the database mirroring configuration and operating modes:
High Availability— This database mirroring operating mode is also known as High Safety with Automatic Failover (synchronous with a witness). It provides maximum availability for a database mirroring session by using a synchronous form of mirroring. This operating mode requires a witness and supports automatic detection and automatic failover in the event the principal database is unavailable. Client performance is affected by the network speed and distance between the principal and mirror servers.
High Protection— High protection is also referred to as High Safety Without Automatic Failover (synchronous without a witness). Like the high-availability operating mode, high protection uses a synchronous form of mirroring but does not require a witness. It does not require a witness SQL Server instance because failover is manual. With this mode, just as in the high-availability operating mode, principal performance is affected between the principal and mirror server based on network speed and distance.
High Performance— High performance is the final operating mode and is also referred to as High Performance Asynchronous. High performance uses an asynchronous form of mirroring. In this situation, the principal server does not wait for confirmation that the transactions have been successfully committed to the mirror database instance. This increases performance because the network speed and distance are not factors. This solution does not require a witness. Therefore, there is no automatic detection or automatic failover as in high-availability mode.
Table 13.1 provides an easy-to-read summary of the database mirroring configuration modes, detection levels, and failover process; it also indicates whether a witness server is required.
Note
Asynchronous Database Mirroring (High-Performance Mode) is supported only by SQL Server 2008 Enterprise Edition.
When you use high-availability and high-protection modes, principal performance is affected by network speed, distance, and available bandwidth. Performance is not adversely affected when you use high performance. The mode you select ultimately determines how your organization wants to handle synchronization and failover processing.
Configuring database mirroring is not as simple as clicking through a few pages of a SQL Server installation wizard. A number of prerequisite steps must be fulfilled before a database mirroring session can be configured and implemented. Following are the prerequisites:
Register the principal, mirror, and witness SQL Server 2008 instances to ensure connectivity is present. The principal and mirror servers should be running the same edition of SQL Server 2008. Based on the features required, the Standard or Enterprise Edition can be used.
The recovery model on the principal database must be set to Full.
A full backup of the principal database is required.
The mirror database needs to be initialized prior to implementing database mirroring by conducting a restore using the NORECOVERY option. All transaction logs taken after the full backup must also be restored.
The database names for both the principal and mirror database must be identical.
The server hosting the mirrored database requires adequate disk space.
Some of the key driving factors for implementing database mirroring with SQL Server 2008 are as follows:
There is a need to provide high-availability support for a specific database but not for an entire instance of SQL Server.
A seamless failover that does not affect client applications and end users is required.
An automatic failover that does not require intervention from a database administrator is favorable.
High availability for a database in another physical location other than the principal is required. Note that there is no distance limitation with mirroring.
There is a need for high availability, and the organization does not have identical servers and shared storage, which is an expensive requirement for failover clustering.
There is a need to fulfill the business continuity and disaster recovery requirements by placing and maintaining a redundant, up-to-date database in a different physical location than the principal.
There is a need to remove the single point of failure intrinsic in failover clusters. The single point of failure is typically the shared storage as it only maintains one copy of the production data.
Database mirroring can be used if there is a need to conduct a rolling upgrade of a SQL Server participating in a mirroring session without impacting database operations.
There are many other reasons organizations may turn to database mirroring. The first step your organization should take is to identify the gaps between the current and desired states of your business and then determine whether data mirroring fulfills your high-availability business goals.
When an organization decides on using a witness server for high availability and automatic failure, it is often challenged with where to place the server. If the database mirroring session is configured over a wide area network (WAN), the witness can be placed either in the site with the principal or the site with the mirror. It is recommended to place the witness server in the same site as the mirror server. The reason is that if a site failure occurs where the principal resides, the witness server will still be operational and can initiate the failover with ease. On the other hand, some organizations place the witness server in the same site as the principal server because the network may not be reliable between the two sites. For these organizations, placing the principal and witness together minimizes unnecessary failovers due to network glitches.
Finally, if your organization is using database mirroring as a high-availability alternative, the witness server should be configured on a dedicated server that is not the principal or mirror. Placement on a dedicated server in this situation protects against hardware failure. It is important to mention that even if the witness is placed on a separate SQL Server instance, but the instance resides on the same server as the principal or mirror, you run into problems. If the physical hardware crashes, both instances fail and the witness cannot conduct an automatic failover, resulting in a longer downtime. Finally, the witness server can be placed on the SQL Server 2008 Express edition to reduce licensing and management costs.
It is worth noting that a witness server can run on any reliable system that supports SQL Server 2008. However, it is recommend that the instance used to host the witness corresponds to the minimum configuration that is required for SQL Server 2008 Standard Edition.
Other SQL Server high-availability alternatives and technologies can be combined with database mirroring for maximum availability, reporting, business continuity, and disaster recovery. The following sections explain how database mirroring interacts with other SQL Server 2008 technologies.
Database mirroring has its advantages and disadvantages, and it does not solve every high-availability requirement. This is why database mirroring can be combined with other SQL Server high-availability alternatives such as failover clustering, log shipping, and replication.
In many cases, database mirroring can be configured as a disaster recovery solution to a local SQL Server failover cluster instance by placing the principal database on the cluster and the hot standby mirror database in another physical location. If this combination is used, it is a best practice to use the high-protection or high-performance configuration mode because a cluster failover takes longer than the mirroring failover threshold. Therefore, if the high-availability configuration mode is being used, an automatic mirror failover takes place every time a cluster failover takes place between the two cluster nodes, making the cluster instance a mirrored database.
It is worth noting that the default threshold setting for controlling automatic failover with database mirroring is set to ten seconds. When combined with failover clustering, a mirroring failover might take place when a node failover occurs within the cluster. Most likely this behavior is unwanted and occurs because a failover within a cluster takes more than ten seconds. In order to address this concern, it is best practice to increase the partner time-out value. The following example illustrates changing the mirroring failover threshold to two minutes:
ALTER DATABASE AdventureWorks20082008
SET PARTNER TIMEOUT 120
One of the limitations of database mirroring compared to log shipping is that database mirroring can have only one mirrored server associated with each principal, whereas log shipping can have multiple standby servers. The two technologies can be combined if there is a need to ship the principal database logs to a remote location other than the place where the mirror resides. In addition, log shipping databases can be used for reporting, whereas mirror databases cannot unless a snapshot is used.
Finally, database mirroring can be used in conjunction with replication. The main focus is to provide availability for the publication database because the distribution and subscription databases are not supported with database mirroring. Because of the requirements and considerations, it is not a recommended practice to combine these two technologies; however, Microsoft includes a list of prerequisite tasks in SQL Server 2008 Books Online.
Many organizations need to run reports against a production database for business purposes. To mitigate performance degradation and unnecessary locking due to sequential read and writes, it is a best practice to have a dedicated reporting server and not have reports run from the production database. Database mirroring offers this capability by allowing the mirror database to be used for reporting purposes. Unfortunately, the mirror database is in a constant recovering state, so it cannot be accessed directly. You can create a point-in-time database snapshot from the mirror database, which can be used for reporting.
Reporting against a mirrored database could be a great technology if offered by Microsoft because organizations would be able to create real-time reports from the mirror database without the need to constantly create snapshots. Perhaps this could be a new feature if enough DBAs requested Microsoft to create it.
Note
For more information on creating database snapshots, see Chapter 7, “Backing Up and Restoring the SQL Server 2008 Database Engine.”
To administer a database mirroring session, first follow the steps to configure database mirroring. The following example simulates a database mirroring implementation that uses the high-availability configuration mode, including a witness for CompanyABC’s AdventureWorks2008 production database located in San Francisco. The mirroring and witness partner is located in Toronto. The server names are shown in Table 13.2.
You must conduct the following tasks to configure database mirroring on the AdventureWorks2008 database from San Francisco to Toronto:
1. From the principal server (SFC-SQL01Instance01), conduct a full backup of the AdventureWorks2008 database by using Transact-SQL (TSQL) code or SQL Server Management Studio (SSMS).
Caution
Use independent files when creating the database and transaction log backups. Do not append both of these backups to the same file; otherwise, an erroneous error such as Error 1418 may occur when setting up the database mirroring session. Error 1418 typically represents network connectivity or issues when resolving server names in a mirroring session.
2. Conduct a transaction log backup of the AdventureWorks2008 database by using TSQL code or SSMS.
3. Copy the backup files from the principal server (SFC-SQL01Instance01) to the mirror server (TOR-SQL01Instance01).
Tip
You need to create the AdventureWorks2008 database on the mirror server if it does not already exist. To simplify the backup and restore process, it is a best practice to maintain the same file path for the database and transaction log files that the principal database is using. Otherwise, the MOVE command is required when you’re conducting the restore.
4. From the Mirror Server SQL Server instance (TOR-SQL01Instance01), conduct a restore of the AdventureWorks2008 database file and then the transaction log. Use the recovery state option RESTORE WITH NORECOVERY for both restores. Therefore, the database is not in an operational state - for end users and applications - and ready to accept database mirroring transactions.
Note
For more information on backing up and restoring SQL Server with either SSMS or TSQL, see Chapter 7.
5. In SSMS, on the principal server, register the principal, mirror, and witness SQL Server instances to ensure successful connectivity and authentication.
Now that you’ve configured the prerequisites, follow these steps to configure database mirroring with high safety and automatic failover:
1. From the principal server (SFC-SQL01Instance01), choose Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio.
2. In Object Explorer, first connect to the Database Engine, expand the desired server (SFC-SQL01Instance01), and then expand the Database folder.
3. Right-click the AdventureWorks2008 database, select Tasks, and then choose Mirror.
4. On the Database Properties page, select the Configure Security button located on the Mirroring page.
Note
Because database mirroring requires the transaction logs for synchronization, you receive a warning message if the database recovery level is not set to Full. If this occurs, switch the recovery model to Full and restart the Database Mirroring Wizard.
5. On the Configure Database Mirroring Security Wizard Starting page, select Next.
6. Specify whether to include a witness server in the configuration by selecting the option Yes on the Include Witness Server page. For this example, you use a witness server instance (TOR-SQL01Instance02) to operate database mirroring in synchronous mode with automatic failure.
7. In the Choose Servers to Configure page, select the principal, mirror, and witness server instances, as illustrated in Figure 13.2. Click Next.
8. On the Principal Server Instance page, specify the endpoint properties for the principal server instance, as shown in Figure 13.3. Ensure that the option Encrypt Data Sent Through This Endpoint is selected, and then click Next to continue.
Note
Typically, the default listener port for each endpoint is 5022. However, if the principal, mirror, or witness is configured on the same SQL Server instance, its endpoints must use different ports.
9. On the Mirror Server Instance page, specify the mirror server instance and the endpoint properties for the mirrored server instance, as shown in Figure 13.4. Click Next to continue.
Note
Before specifying options for this page, you may have to click the Connect button in order to first pass credentials for the desired mirror server instance.
10. On the Witness Server Instance page, specify the witness server instance and the endpoint properties for the witness server instance, as shown in Figure 13.5. Click Next to continue.
11. On the Service Accounts page, enter the service account information for each instance partaking in the database mirroring session. If the service accounts are the same for each instance, as in this example, leave the text boxes blank, as illustrated in Figure 13.6, and click Next to continue.
Note
If the service accounts entered are different and the accounts do not already exist in the specific SQL Server instance, the wizard automatically creates the accounts, grants appropriate permissions, and associates the account credentials to the endpoints.
12. On the Complete the Wizard page, verify the configuration settings for each database mirroring instance, as shown in Figure 13.7, and then click Finish.
13. On the Configuring Endpoints page, verify the status of each endpoint to ensure it was successfully created and click Close.
14. When this Endpoint Security Wizard is closed, you are prompted to either start the database mirroring session now by selecting Start Mirroring or start it later by selecting Start Mirroring on the Mirroring page of the Database Properties dialog box, as shown in Figure 13.8. For this example, click Start Mirroring.
Note
The mirrored database must be present on the mirrored server; otherwise, an error occurs, stating that the mirrored database does not exist and must be created via a backup and restore prior to initializing the database mirroring session.
15. Verify that the initial synchronization was successful by viewing the Status section located in the Database Properties page, as shown in Figure 13.9, and then click OK.
When databases are configured in a database mirroring session, a status message appears next to the database and includes the server role. For example, on the SFC-SQL01Instance01 server, the principal AdventureWorks20082008 database status message indicates (Principal, Synchronized), and TOR-SQL01Instance01 database status message indicates (Mirror, Synchronized/Restoring...).
The Mirroring page accessible from the Database Properties page allows you to manage a database mirroring session. With this tool, you can pause, remove, or fail over a database mirroring session. In addition, it is possible to change the database mirroring operation mode—for example, from high performance (asynchronous) to high safety (synchronous). Finally, you can use this page to initiate manual failovers and status verification. Alternatively, any database mirroring tasks that can be conducted from the management console can also be scripted with Transact-SQL (TSQL).
Occasionally, you need to either pause or resume a database mirroring session for administrative purposes. You can pause and resume a mirroring session by using SSMS or TSQL.
Follow these steps to either pause or resume a database mirroring session with SSMS:
1. From the principal server (SFC-SQL01), choose Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio.
2. In Object Explorer, first connect to the Database Engine, expand the desired server (SFC-SQL01Instance01), and then expand the Database folder.
3. Right-click the AdventureWorks2008 database, select Tasks, and then choose Mirror.
4. Click the Pause button located in the Server Network Address section on the Mirroring tab of the Database Properties page (refer to the previous Figure 13.9).
5. Click the Resume button to restart the database mirroring session. The Resume button is not displayed in Figure 13.9 because it only appears on the Mirroring tab after the Pause button has been clicked.
Alternatively, you can use the following sample TSQL syntax to pause and resume a database mirroring session:
Pausing database mirroring:
Use Master
ALTER DATABASE <database_name> SET PARTNER SUSPEND
GO
Use Master
ALTER DATABASE <database_name> SET PARTNER RESUME
Go
Follow these steps to swap the principal and mirror roles by manually failing over the database session from the principal server to the mirrored server:
1. From the principal server (SFC-SQL01), choose Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio.
2. In Object Explorer, first connect to the Database Engine, expand the desired server (SFC-SQL01Instance01), and then expand the Database folder.
3. Right-click the AdventureWorks2008 database, select Tasks, and then choose Mirror.
4. Click the Failover button located in the Server Network Addresses section on the Mirroring tab of the Database Properties page.
5. Read the warning message and click Yes to finalize the role swap.
In SSMS, notice how the status messages have changed based on the role swap. On the SFC-SQL01Instance01 server, the AdventureWorks2008 database status message indicates (Mirror, Synchronized/In Recovery), whereas the TOR-SQL01Instance01 database instance status message indicates (Principal, Synchronized).
The following sample TSQL syntax should be used to fail over a database mirroring session:
Use Master
ALTER DATABASE database_name SET PARTNER FAILOVER
Go
In some situations, either you or your organization decides to change the operating mode. Reasons for changing the operating mode may include performance issues, the absence of a witness server, or even a need to manually control a failover as opposed to having SQL automatically conduct the role swap.
Follow these steps to change the database mirroring operating mode with SSMS:
1. From the principal server (SFC-SQL01), choose Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio.
2. In Object Explorer, first connect to the Database Engine, expand the desired server (SFC-SQL01Instance01), and then expand the Database folder.
3. Right-click the AdventureWorks2008 database, select Tasks, and then choose Mirror.
4. In the Operating Mode section, change the Operating Mode option to either High Performance, High Safety, or High Safety with Automatic Failover and click OK.
You can use the following basic TSQL syntax to change the database mirroring operating mode:
Enable Transaction Safety:
Use Master
ALTER DATABASE <database> SET PARTNER SAFETY FULL
GO
Disable Transaction Safety:
Use Master
ALTER DATABASE <database> SET PARTNER SAFETY OFF
GO
Similar to the management steps you used previously, you can remove database mirroring sessions with either TSQL or SSMS.
Follow these steps to swap roles by manually failing over the database session from the principal server to the mirrored server:
1. From the principal server (SFC-SQL01), choose Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio.
2. In Object Explorer, first connect to the Database Engine, expand the desired server (SFC-SQL01Instance01), and then expand the Database folder.
3. Right-click the AdventureWorks2008 database, select Tasks, and then choose Mirror.
4. Click the Remove Mirroring button located in the Server Network Addresses section on the Mirroring tab of the Database Properties page.
5. Read the warning message and click Yes to remove mirroring from the AdventureWorks2008 database.
6. In the Database Properties page, click OK to finalize the procedures.
The following TSQL syntax can also be used to remove a database mirroring session:
Use Master
ALTER DATABASE <database_name> SET PARTNER OFF
Go
In the event of a principal database failure, the principal database fails over to the mirror either manually or automatically. Therefore, all client connections need to be redirected from the principal server instance to the new mirror database instance. The latest ADO.NET or SQL Server clients have built-in redirect technologies that allow an application to automatically redirect its connection in the event of a database failure. Either you, as database administrator, or an application developer must specify the principal and failover SQL Server instance in the connection string to make this happen.
Follow these steps to configure automatic client redirect by using the native SQL Server client:
1. Choose Start, All Programs, Administrative Tools, Data Sources (ODBC).
2. On the ODBC Data Source Administrator page, select System DSN.
3. Click Add to create a new System DSN connection to the principal and mirror SQL Server instance.
4. In the Create New Data Source page, select SQL Native Client 10.0 and then click Finish.
5. In the Create a New Data Source to SQL Server page, enter the name, description, and the principal database server instance, as illustrated in Figure 13.10. For this example, use the principal SQL Server instance SFC-SQL01Instance01. Click Next.
6. Select the SQL Server authentication mode for the SQL Server connection and click Next.
7. Select the default database to connect to and enter the name of the mirror server, as shown in Figure 13.11. For this example, select AdventureWorks2008 database and TOR-SQL01Instance01 for the mirror server instance. Click Next.
8. Click Finish and then click Test Data Source to finalize the connection settings.
The new connection can be leveraged with a front-end SQL Server client such as Access, Visual Studio .NET, or Reporting Services. Use the newly created connection and display data from the AdventureWorks2008 database such as the Employee table. When a connection is established and the data is presented, fail over the database mirroring session. The application should still be able to display the Employee table because it automatically redirects to the AdventureWorks2008 database residing on the mirror instance of SQL Server.
After you have configured database mirroring, you should turn your attention to understanding the following tools available for monitoring and managing the mirroring session:
Database Mirroring Monitoring tool
System Performance
System Catalogs
Operations Manager 2007
The Database Mirroring Monitoring tool is included with SSMS and should be used to monitor databases configured in a mirroring session. The tool can be launched by right-clicking the database partaking in a database mirroring session and then selecting Tasks, Launch Database Mirroring Monitor. You can use the tool to identify the status of the database mirroring session, identify the role of each partner, determine whether the mirroring session is behind schedule, and estimate the time it will take to catch up.
Use the following procedure to monitor the state of the database mirroring session configured in the earlier examples:
1. From the principal server (SFC-SQL01), choose Start, All Programs, Microsoft SQL Server 2008, SQL Server Management Studio.
2. In Object Explorer, first connect to the Database Engine, expand the desired server (SFC-SQL01Instance01), and then expand the Database folder.
3. Right-click the AdventureWorks2008 database, select Tasks, and then choose Launch Database Mirroring Monitor.
4. To register a mirrored database, either click the Register Mirrored Database hyperlink in the right pane or select Action, Register Mirrored Database from the Tools menu.
5. In the Register Mirrored Database page, select the server instance by clicking the Connect button.
6. In the Connect to Server dialog box, select the Mirrored Database SQL Server Instance (TOR-SQL01Instance01) and click OK.
7. In the Register Mirrored Database page, click the Register check box next to the database to register the mirrored database instance and then click OK.
8. The Database Mirroring Monitoring tool automatically connects to both the principal and mirror instances partaking in the database mirroring session. In the Manage Server Instance Connections, edit the credentials if necessary or click OK.
The Status tab includes a plethora of database mirroring status information for both the principal and mirror databases. The status information located on the Status tab is broken into four sections: Status, Principal Log, Mirror Log, and General Information.
The Status section indicates the server instance, current role, and mirrored state, and it validates that the witness is operational. The final command in the status window provides a history log file, as shown in Figure 13.12.
The Principal Log section includes metrics on the following:
Unsent Log Information in KB
Oldest Unsent Transaction
Time to Send Log (Estimated)
Current Send Rate in KB per Second
Current Rate of New Transactions
The General section located at the bottom of the Status tab page includes additional status for troubleshooting and diagnostics:
Mirror Commit Overhead in Milliseconds
Time Estimates to Send and Restore All Current Logs
Witness Address
Operation Mode
The Warnings tab allows you to set database mirroring warning thresholds for the principal and mirror SQL Server instances. The four warnings included with this tool are
Warn If the Unsent Log Exceeds the Threshold
Warn If the Un-Restored Log Exceeds the Threshold
Warn If the Age of the Oldest Unsent Transaction Exceeds the Threshold
Warn If the Mirror Commit Overhead Exceeds the Threshold
The Set Warning Thresholds page should be used to enable/disable warning per instance and set thresholds.
The Database Mirroring Monitoring tool is a great starting point for managing and analyzing a database mirroring session. When additional metrics are needed for analysis, or when troubleshooting or creating a performance baseline, you can use the Reliability and Performance Monitor tool included with Windows Server 2008. To launch the tool, choose Start, All Programs, Administrative Tools, and Reliability Performance Monitor.
Following are the specific counters included with the SQL Server Database Mirroring Performance Object:
Bytes Received/sec
Bytes Sent/sec
Log Bytes Received/sec
Log Bytes Redone from Cache/sec
Log Bytes Sent from Cache/sec
Log Bytes Sent/sec
Log Compressed Bytes Sent/sec
Log Harden Time (ms)
Log Remaining for Undo KB
Log Scanned for Undo KB
Log Send Flow Control Time (ms)
Mirrored Write Transactions/sec
Log Send Queue KB
Pages Sent/sec
Receives/sec
Redo Bytes/sec
Redo Queue KB
Send/Receive Ack Time
Sends/sec
Transaction Delay
Collecting and analyzing the preceding metrics assists organizations with planning their database mirroring solution. Before database mirroring is implemented in production, it is a best practice to simulate mirroring in a prototype test lab and analyze the metrics collected. If possible, a bandwidth simulator tool should also be used to mimic the production network speed, especially if Log Stream Compression will be used. This allows an organization to fully understand the database mirroring and bandwidth requirements when setting up database mirroring in production over a private network. When analyzing bandwidth requirements, your organization should also assess the current bandwidth utilization. Therefore, if the link is already fully saturated, more bandwidth may be necessary to support the mirroring solution. Alternatively, many organizations purchase dedicated network lines tailored specifically for database mirroring replication.
The catalog view included with SQL Server is another great source of information when monitoring status and performance.
The following catalog views should be used:
Sys.database_mirroring
Sys.database_mirroring_witness
Sys.database_mirroring_endpoints
Sys.tcp_endpoints
Sys.Server_principals
Sys.dm_db_mirroring_auto_page_repair
The catalog view provides database mirroring metadata for a session, including witness, endpoint, principal, and recovery status.
Another great tool to proactively monitor database mirroring, including the health of the principal, mirror, and witness SQL Server instances, is Microsoft Operations Manager 2007. Operations Manager 2007 includes a dedicated Microsoft Management Pack tailored toward SQL Server. It includes a subcomponent that focuses on database mirroring.
Note
For more information on proactively monitoring a database mirroring session with Operations Manager 2007, refer to Chapter 18, “ Proactively Monitoring SQL Server 2008 with System Center Operations Manager 2007”.
Database mirroring is a SQL Server 2008 high-availability alternative that can be used for maintaining a redundant copy of the principal database on a standby server for increased availability and disaster recovery purposes. The new features and functionality introduced in SQL Server 2008 allow organizations to more easily implement database mirroring across physical sites with limited bandwidth, and automatic page repair protects the mirrored copy from corruption.
How well database mirroring performs is closely associated with the type of application, transaction safety level, and network performance between the principal and mirror servers. Understanding the application behavior in terms of the log generation rate, number of concurrent connections, and size of transactions is important in achieving the best performance.
In addition, the network plays a very important role in a database mirroring environment. When used with a high-bandwidth and low-latency network, database mirroring can provide a reliable high-availability solution against planned and unplanned downtime. With data centers in different geographical locations, database mirroring can provide the foundation for a solid, inexpensive disaster recovery solution.
The following are the best practices for this chapter:
Database mirroring using the high-availability configuration mode is a practical alternative when the principal and mirror server reside in the same physical location. The reason is that most organizations’ production environments are running fast networks without network latency.
Database mirroring using the high-performance configuration mode is a practical alternative when the principal and mirror server reside in different physical locations. The reason is that production performance is typically of higher importance than automatic failover and availability in these situations.
Leverage database mirroring to reduce planned downtime, increase availability for mission-critical databases, and satisfy disaster recovery requirements.
To increase performance, implement and leverage a dedicated high-bandwidth network for synchronization communications between the principal and mirror database servers when possible.
Leverage Database Mirroring Log Compression in environments where there isn’t enough available bandwidth between the principal database instance and the mirrored database instance.
In the event of a failure, the mirror server needs to maintain the same workload as the principal. Both servers should be of similar class and have the same number of processors and the same amount of memory and storage. Unlike in failover clustering, the hardware does not have to be an exact match, but the mirror needs to support the same load as the principal.
Use failover clustering over database mirroring if there is a need to provide high availability on the whole SQL Server instance. This includes the master, model, msdb, and tempdb databases because these system databases cannot partake in a database mirroring session.
To reduce the number of unforeseen issues with database mirroring, use the same edition of Windows and SQL Server for both the principal and mirror server. In addition, the service packs, hotfixes, drive letter layout, collation settings, and SQL Server configuration settings should be identical. Although this is not a requirement, it is a best practice.
To reduce complications and troubleshooting, use a single mirror SQL Server instance if a principal instance is composed of multiple databases belonging to one application that needs to be mirrored.
When using database mirroring, create items such as logins, scheduled jobs, and extended stored procedures that are identical on the mirrored database and instance.
When configuring database mirroring, do not forget to initialize the mirror database by restoring the full backup and the last transaction log with the NORECOVERY option.
If you configured the database mirroring session to use high-availability or high-protection mode and delays are experienced with client applications, switch to high-performance mode.