Properly configured database mirroring will provide high availability, however there are many factors you must consider before deploying a database mirroring solution. These factors range from the service pack and edition of SQL Server 2005, to environmental factors, like your network bandwidth, the type of workload and whether you should use a witness or not.
In some cases another high availability technology may be a better fit than database mirroring. The purpose of this article is to discuss these considerations.
Database mirroring ships in the Standard and Enterprise editions of SQL Server 2005. It's near real-time log shipping -- transactions that originate on the source server (called the principal) are copied to a single destination server (called the mirror). The principal and mirror are partners in a database mirroring session. Under some configurations, database mirroring will perform automatic failover and redirection of clients; and a third server called a witness may be required under some configurations.
For more information on database mirroring, refer to the Q&A SQL Server 2005 database mirroring primer.
Service pack
By default in the RTM (released to manufacturing) version of SQL Server 2005, database mirroring is not enabled, nor is it supported. To enable it to run on the RTM version of SQL Server 2005, you need to enable trace flag 1400. Database mirroring is supported and enabled by default starting in SP1.
Database requirements
While you can mirror from one database to another on different instances, you cannot mirror to:
The same instance
System databases
A database with a different name (for example, you cannot mirror from the pubs database to the NorthWind database)
Databases that are not in the full-recovery model...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
A database that is not a restored copy of the source database (called the principal database), and this database must be restored using the no recovery parameter
Database mirroring is not a good choice for mirroring large numbers of databases on a single instance or server. Even though Microsoft has done functional testing on hundreds of mirrored databases per instance, it recommends around 10 databases be mirrored on a single mirrored database instance. Each database instance consumes a couple of worker threads on both the principal and the mirror, and with large numbers of mirrored database instances, the SQL Server system can quickly run out of worker threads to perform work on the server.
Server requirements
Here are considerations for server software, SQL Server versions, and hardware configuration and layout.
Ideally, the principal and mirror server will consist of the same hardware configuration and OS, SQL Server version and service pack level.
The disk layout should be the same. If you do an alter database statement to add a file to a filegroup and this file path refers to a non-existent path of the mirror, the mirror will fail.
While you can mirror from a 32-bit version of SQL Server to a 64-bit version of SQL Server and vice versa, you cannot mirror between different editions of SQL Server (for example, from Enterprise Edition to Standard Edition). The exception is that you can mirror from the Enterprise to the Developer Edition because both have the same engine (the Enterprise Engine). However, the Developer Edition is not licensed for production use.
Database mirroring will apply committed transactions that occur on the principal on the mirror. This is termed the "redo process." In the SQL Server Enterprise Edition, the redo process is multi-threaded, meaning the mirror will failover faster on the Enterprise Edition than if the mirror was running on the Standard Edition.
With SQL Server 2005 Enterprise and Developer editions, you can create a database snapshot from a mirror and access this mirror for reporting purposes, for example. In all other cases, the mirror will be inaccessible.
Database mirroring supports two modes – synchronous and asynchronous. Using asynchronous mode, a transaction will be committed on the principal before it will be committed on the mirror. Using synchronous mode, the transaction will be committed on the mirror before it is committed on the principal -- with some very important caveats. Before we look at what those are, we need to discuss which versions support which modes and when to use each mode.
Asynchronous mode/high performance
Asynchronous mode is only supported on the Enterprise and Developer editions of SQL Server 2005. Asynchronous mode is where transactions originate on the principal and are applied asynchronously on the mirror as soon as possible. This is also known as high-performance mode. Should the mirror go offline, users will not detect any changes in the state of the principal and, hence, continue to work as before the mirror went offline. Commands to be mirrored will be stored in the transaction log. Note: The witness does not play a role in asynchronous mode.
Synchronous mode
There are two forms of synchronous mode -- high availability and high protection.
High availability
High availability requires a witness server in addition to a principal and a mirror server. It will provide for automatic failover. Should the principal be unable to achieve a quorum with the witness and the mirror (in other words, it cannot connect to the mirror and the witness), then the database will shut down and:
- All users will be disconnected from the database.
- No transaction will be processed in the database.
- No new users will be allowed in the database.
As soon as it achieves a quorum again – when it sees the mirror or principal – it will start serving the database again. However, if the principal goes offline, the mirror may switch roles and now be the principal. You will have to fail back to get the original principal serving the database again, if this is what you require.
High protection
High protection does not require a witness server. If you are using high-protection mode and the principal is unable to achieve a quorum with the mirror – i.e., it cannot connect to the mirror – then it continues to serve the database and the loss of the mirror is transparent to the users connected to the principal. Users will not be disconnected, and they will be able to do work. New users can connect and transactions to be mirrored are stored in the transaction log.
Network requirements
While it is possible to mirror over the Internet, it is not a good practice for these reasons:
Under any appreciable load, database mirroring latencies will increase and your exposure to data loss will increase.
Should you be running high safety mode, your database mirroring session will never reach the synchronized state and you will be unable to failover and fail back.
Should you be running high safety mode and your principal is unable to connect to the witness or mirror, database mirroring will be disconnected and the principal database will enter a read-only state.
In high-performance mode, the transactions that are not mirrored to the mirror will remain in the log and the transaction log can get very large if the mirror is offline for an appreciable length of time. You'll be unable to dump the log.
You will need to use certificates to encrypt authentication.
As database mirroring latency is very sensitive to network speed, make sure your network bandwidth – reliability and resolution – are as optimal as possible. It is a good practice to copy files back and forth between your principal and mirror to test network reliability, speed and throughput before you implement a database mirroring topology. Microsoft recommends that the network bandwidth is three times the maximum log generation rate.
For low-bandwidth connections, you can set the partner timeout and partner redo_queue to larger values. Consult this Alter Database Statement for more information on these settings.
Workload considerations
While running in high-availability and high-protection modes, there will be added latency to each transaction originating on the principal, because it has to be mirrored over to the mirror before it is committed. The latency of short-running transactions will increase dramatically due to this split write. For example, transactions taking 10 ms, will now take 20 ms, i.e., twice as long (these numbers are purely for illustrative purposes, your results may vary).
On the other hand, the proportionate increase of long-running transactions will be considerably less. A one-second transaction, will now take 1.01 seconds. Long-running transactions will perform better with the high-protection/high-availability settings than short running-transactions do. Microsoft recommends that you open up more connections running in high-protection/high-availability modes to minimize the impact of the increased latency as well.
Witness requirements
There are no special requirements for the witness server. It should be connected via a well-connected network link. It does not have to be clustered because mirroring topologies requiring a witness can form a quorum with the witness being offline for brief periods. For more information on mirroring and clusters consult Tom Moreau's article, Top Tips for SQL Server Clustering.
ABOUT THE AUTHOR:
Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a Web and database consultant, and has worked with SQL Server for 11 years. Cotter is Director of Text Mining at RelevantNoise, dedicated to indexing blogs for business intelligence. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and studied economics at the University of Calgary and computer science at UC Berkeley. He is author of the book A Guide to SQL Server 2000 Transactional and Snapshot Replication and is currently working on books devoted to merge replication and Microsoft search technologies. Hilary Cotter can be contacted at hilary.cotter@gmail.com.
Copyright 2007 TechTarget
