Microsoft KB Archive/910716

= Requirements for SQL Server 2005 and SQL Server 2000 to support remote mirroring of user databases =

Article ID: 910716

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Compact Edition
 * Microsoft SQL Server 2000 Desktop Engine (Windows)
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Developer Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Enterprise Edition 64-bit
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 Personal Edition
 * Microsoft SQL Server 2000 Workgroup Edition

-





INTRODUCTION
This article discusses the requirements for Microsoft SQL Server 2005 and SQL Server 2000 to support remote mirroring of user databases.



MORE INFORMATION
Remote mirroring is a solution that is offered by third-party vendors. This solution lets you maintain a real-time mirror of your primary storage device at a remote site. In a remote mirror, redundant server hardware and a redundant storage system are maintained at the remote site. Microsoft SQL Server is installed on both the local server and the remote mirror server.

For more information about remote mirroring, visit the following Microsoft Web site:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog05.mspx

The following is a list of requirements for SQL Server to be supported on a remote mirroring solution.

Note This article does not endorse any specific vendor's solution.

Data integrity
Work with your hardware vendor and your storage vendor to make sure that the remote mirroring solution that you want to use supports the I/O requirements of SQL Server.

For information about documented vendor solutions, visit the following Microsoft Web site:

http://www.microsoft.com/sql/alwayson.mspx

For information about I/O requirements, visit the following Microsoft TechNet Web site:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx

The following is a list of some important requirements to consider:
 * Your solution must maintain Dependent Write Consistency.
 * Your solution must avoid torn pages.
 * Your solution must produce an I/O consistent copy of your data at the secondary remote location.

If your solution does not meet these requirements, database corruption may occur at the remote site.

Write-Ahead Logging (WAL) protocol
SQL Server deploys a transaction log and an associated recovery logic to make sure that the system maintains transactional database consistency if a system failure or an unmanaged shutdown occurs. This is known as the Write-Ahead Logging (WAL) protocol.

This recovery protocol must be able to write directly through to the disk media. When an operating system I/O write request returns to the database manager, the recovery system must be able to guarantee that the write has actually completed or that the write will be completed. If a hardware or software component of the system does not support this protocol, you may experience partial or total data loss, or the data may become corrupted, if a system failure occurs.

Your solution must meet all requirements for data write ordering and for write-through guarantees. Microsoft will not support any solution that does not meet the WAL requirements. For more information about the WAL requirements, click the following article number to view the article in the Microsoft Knowledge Base:

230785 SQL Server 7.0, SQL Server 2000, and SQL Server 2005 logging and data storage algorithms extend data reliability

Microsoft Windows-based servers and other storage solutions that are Windows Hardware Quality Lab (WHQL) certified should meet the required guarantees. Therefore, Windows-based servers should be supported for use as SQL Server storage devices. Verify the following with your hardware vendor:
 * Any storage device that you plan to deploy has been designed for database use.
 * You are deploying and configuring those devices for transactional database use.

Remote mirroring support
For more information about remote mirroring and stretch clustering, visit the following Microsoft TechNet Web site:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog05.mspx

Performance
Some remote mirroring solutions may have an adverse effect on overall database performance. This is true especially for solutions that rely on synchronous updates to the remote site. You should carefully consider this point and factor this behavior into the solution that you deploy.

Support
If your solution meets the requirements that are discussed in this article, Microsoft Customer Support Services will provide support for issues that are not related to storage devices.

Important To obtain support from Microsoft Customer Support Services for issues that involve transactional throughput, you must be able to reproduce the issue in a mirror-free environment.

To obtain support for issues that involve a storage device, see your storage vendor.

To obtain support for issues that involve third-party replication technology, see your technology vendor.

Keywords: kbinfo kbtshoot kbsql2005engine KB910716

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.