Prerequisites and Recommendations for Database Mirroring
This topic describes the prerequisites and recommendations for setting up database mirroring. For an introduction to database mirroring, see Overview of Database Mirroring.
Note
The SQL Server on-disk storage format is the same in the 64-bit and 32-bit environments. Therefore, a database mirroring session can combine server instances that run in a 32-bit environment and server instances that run in a 64-bit environment.
Restrictions
On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.
Database mirroring is not supported with either cross-database transactions or distributed transactions. For more information, see Database Mirroring and Cross-Database Transactions.
Prerequisites
Before you can set up database mirroring, you must perform the following:
Make sure that the two partners, that is the principal server and mirror server, are running the same edition of Microsoft SQL Server 2005. The partners require either SQL Server 2005 Standard Edition or SQL Server 2005 Enterprise Edition.
If you are using a witness, make sure that SQL Server 2005 is installed on its system. The witness can run on any reliable computer system that supports SQL Server 2005 Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition.
Verify that the mirror server has enough disk space for the mirror database.
Note
For information about how to use database mirroring on a replicated database, see Replication and Database Mirroring.
When you are creating the mirror database on the mirror server, make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY. Also, all log backups that were created after that backup was taken must also be applied, again WITH NORECOVERY.
Important
If database mirroring has been stopped, before you can restart it, any subsequent log backups taken on the principal database must be applied to the mirror database.
Database mirroring works with any supported database compatibility level. For information about the supported compatibility levels, see sp_dbcmptlevel (Transact-SQL).
Recommendations for Configuring Partner Servers
The partners should run on comparable systems that can handle identical workloads.
Note
If you plan to use high-safety mode with automatic failover, the normal load on each of the failover partners should use less than 50 percent of the CPU. If your work load overloads the CPU, a failover partner might be unable to ping the other server instances in the mirroring session, resulting in a unnecessary failover. If you are unable to keep the CPU usage below 50 percent, we recommend using either high-safety mode without automatic failover or high-performance mode.
If possible, the path (including the drive letter) of the mirror database should be identical to the path of the principal database. If the file layouts must differ, for example, if the principal database is on drive 'F:' but the mirror system lacks an F: drive, you must include the MOVE option in the RESTORE statement.
Important
If you move the database files when creating the mirror database, you may be unable to add files to the database later without mirroring being suspended.
All of the server instances in a mirroring session should use the same master code page and collation. Differences can cause a problem during mirroring setup.
Optionally, estimate the time to fail over a database, to make sure that your system configuration will provide the performance you require. For more information, see Estimating the Interruption of Service During Role Switching.
For best performance, use a dedicated network interface card (NIC) for mirroring.
We make no recommendations about whether a wide-area network (WAN) is reliable enough for database mirroring in high-safety mode. If you decide to use high-safety mode over a WAN, be cautious when you add a witness to the session, because unwanted automatic failovers may occur. For more information, see "Recommendations for Deploying Database Mirroring," later in this topic.
Recommendations for Deploying Database Mirroring
Optimal database mirroring performance is obtained using asynchronous operation. A mirroring session that uses synchronous operation might experience slowed performance when its workload generates large amounts of transaction log data.
In test environments, it is appropriate to explore all of the operating modes to evaluate how database mirroring performs. However, before deploying mirroring into a production environment, it is essential to understand how your network functions in the real world so that you can predict how database mirroring will work there.
High-safety mode with automatic failover is designed for a high-service network with either a dedicated connection or a fairly simple network configuration that minimizes the sources of possible network failures. Such a high-quality network environment is essential for high-safety mode with automatic failover and is recommended for a database mirroring sessions. However, high-performance mode and high-safety mode without automatic failover are much less affected by network reliability.
For production environments, therefore, we recommend that you follow these deployment guidelines:
Begin running in asynchronous, high-performance mode. This mode is the least sensitive to the network environment and provides the best configuration for exploring how mirroring works. We recommend running your system asynchronously until you are confident that your bandwidth supports mirroring and you have developed a solid knowledge of mirroring setup and of the performance of asynchronous mode in your environment. For more information, see Asynchronous Database Mirroring (High-Performance Mode).
Important
Throughout testing, we recommend that you monitor your sessions for network errors that cause database mirroring to fail. For more information about potential sources of failure, see Possible Failures During Database Mirroring. For information about monitoring database mirroring, see Monitoring Database Mirroring.
When you are confident that asynchronous operation is meeting your business needs, you might want to try synchronous operation to improve your data protection. When testing how synchronous mirroring works in your environment, we recommend that first you test high-safety mode without automatic failover. The primary purpose of this testing is to see how synchronous operation affects your database performance. For more information, see Synchronous Database Mirroring (High-Safety Mode).
Wait to enable automatic failover until you are confident that high-safety mode without automatic failover is meeting your business needs and that network errors are not causing failures. For more information, see Automatic Failover.
Note
Database mirroring works correctly with the vardecimal storage format. But each Database Engine must be upgraded to at least SQL Server 2005 Service Pack 2 and all related databases must be enabled for vardecimal storage format. For more information about vardecimal storage format, see Storing Decimal Data As Variable Length.
See Also
Concepts
Database Mirroring Sessions
Database Mirroring Transport Security
Database Mirroring and Backup and Restore
Managing Database Mirroring (SQL Server Management Studio)
Overview of Database Mirroring
Troubleshooting Database Mirroring Setup
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|
14 April 2006 |
|