Prerequisites, Restrictions, and Recommendations for Database Mirroring
This topic describes the prerequisites and recommendations for setting up database mirroring. For an introduction to database mirroring, see Database Mirroring Overview.
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.
Prerequisites
Before you can set up database mirroring, you must perform the following:
For a new mirroring session to be established, the partners and the witness, if any, must be running on the same version of SQL Server.
Make sure that the two partners, that is the principal server and mirror server, are running the same edition of Microsoft SQL Server. Database mirroring partners are supported only by SQL Server 2005 Standard and later versions and by SQL Server 2005 Enterprise Edition and later versions. Asynchronous database mirroring (high-performance mode) is supported only by SQL Server 2005 Enterprise Edition and later versions.
If you are using a witness, make sure that SQL Server 2005 or a later version is installed on its system. The witness can run on any reliable computer system that supports the Standard, Enterprise, Workgroup, or Express editions.
Verify that the mirror server has sufficient 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).
Restrictions
Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
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.
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 failover partner should be 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. This causes a unnecessary failover. If you cannot keep the CPU usage under 50 percent, we recommend that you use 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. You must include the MOVE option in the RESTORE statement if the file layouts must differ. For example, if the principal database is on drive 'F:' but the mirror system lacks an F: drive.
Important
If you move the database files when you create the mirror database, you might 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 the 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 adapter (network interface card) 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 about how you add a witness to the session, because unwanted automatic failovers can 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 by 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 the operating modes to evaluate how database mirroring performs. However, before you deploy mirroring into a production environment, make sure that you understand how the network functions in the real world.
High-safety mode with automatic failover is designed for a high-service network that has 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 necessary for high-safety mode with automatic failover and is recommended for all database mirroring sessions. However, high-performance mode and high-safety mode without automatic failover are much less affected by network reliability.
Therefore, for production environments we recommend that you follow these deployment guidelines.
Start 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 that you run your system asynchronously until you are confident that your bandwidth supports mirroring and you have developed an understanding 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 how to monitor database mirroring, see Monitoring Database Mirroring.
When you are confident that asynchronous operation is meeting the business needs, you might want to try synchronous operation to improve your data protection. When you test 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 the 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 the business needs and that network errors are not causing failures. For more information, see Automatic Failover.
See Also
Concepts
Change History
Updated content |
|---|
Added FILESTREAM to "Restrictions" section. |