Microsoft fully supports database mirroring with SQL Server 2005 SP1 onwards. For the RTM release (prior to SP1), Microsoft support services will not support databases or applications that use database mirroring; the database mirroring feature should not be used in production environments. Prior to SP1, database mirroring is disabled by default, but can be enabled for evaluation purposes by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL) in the SQL Server Books Online.)
Yes, you can mirror a database from one virtual server to another virtual server in a different cluster. You can even mirror from one virtual server to another virtual server in the same cluster. However, Microsoft does not recommend mirroring to another virtual server in the same cluster.
When you use both mirroring and clustering, understand that mirroring and clustering work independently of each other. Mirroring knows nothing about clusters, and clusters know nothing about mirroring. If a node fails, mirroring will probably fail over before the clusters do. Therefore, when the other node in the cluster comes online, this node will be in the mirror role.
If you are currently running well with clusters in production, using mirroring in addition to clusters should be fine.
In the Microsoft SQL Server 2005, you cannot guarantee that mirroring will fail over after a cluster failover. (Cluster failover is where the cluster fails over first. When the last node in the cluster fails, database mirroring fails over.)
Yes, the witness can be on a cluster. However, having the witness on a cluster is not required. Your experience with clustering should determine whether to use clustering.
Note that the witness is not the most important member of the database mirroring session. The witness just answers the question: “Who do you see?” When the partner servers cannot see each other, the partner servers contact the witness to see whether the witness can contact the other partner and verify that a failure has occurred.
When you use database mirroring in high-availability mode, the process of determining a failover is based on the network connection. If there is a problem with the network, mirroring will fail over or deny access to the database because of the quorum requirement. Although mirroring has acted correctly, not having access may surprise customers, because with an ordinary stand-alone computer, the database would still be served. To understand how the system works without the option of automatically failing over, try using database mirroring without a witness for a time.
There are no specific restrictions on the network for mirroring, but the network connection between the servers is critical. The network should generally be dedicated, and be of high quality and high bandwidth. As a rough guideline, the network bandwidth should be three times the maximum log generation rate.
Use multiple network adapters in the server. For the network adapter that you want to dedicate to mirroring, associate a specific IP address to that adapter. Then, associate a particular name to that IP address. Use that particular name when you set up database mirroring.
For all systems, including stand-alone systems, if a client issues a COMMIT statement, there can be one of three different outcomes:
Acknowledgement—the transaction is guaranteed to be committed.
Rollback—the transaction is guaranteed not to be committed.
Connection Closed—there is no way to tell if the transaction was committed or not. The client must go back and query the database after reconnecting to the server.
For database mirroring, the only other implication is that the database is brought online quickly on another server. If the commit log record is not on the mirror server before failover occurs, the client was never informed that a COMMIT statement had occurred, and so whether the log record hit the disk on the original principal is not important.
In the past, backup and restore to another server or failover for log shipping was generally a difficult process. Some customers could not correctly move a database to another server, or the move would take a long time to do. Therefore, moving a database to another server was rarely performed. When moving a database to another server, the following features did not work “smoothly:”
Replication
Distributed Transaction Coordinator (DTC)
Change notification
Logins
Jobs
Information stored in other databases, such as the msdb or master database:
TRUSTWORTHY database property
Jobs
Logins
Unique stored procedures in system databases
Using three-part names to access databases on the same server
Multiple databases
Log shipping
Performance
Now with database mirroring, moving a database to another server is much easier.
No, the witness just answers the question: “Who do you see?”
The witness is not a single point of failure.
If one partner server thinks that there has been a problem with the other partner, the partner servers contact the witness and ask the witness what it sees.
If the safety is set to FULL, but the mirror is not SYNCHRONIZED, transactions do not wait on the COMMIT statement. The mirror is either SUSPENDED, down, or catching up (SYNCHRONIZING). When the mirroring is SYNCHRONIZING, mirroring is chasing a moving target because the principal is generating more log records. To catch up, the following actions occur:
1. The principal takes the current end of log and sends that to the mirror.
2. After sending the current end of log, the principal determines how much more log to send.
3. If the remaining amount of log to be sent is less than 1 megabyte (MB), the status is set to SYNCHRONIZED and all COMMIT statements will wait to hear back from the mirror before returning. The system is caught up.
4. If the remaining amount of log to be sent is more than 1 MB, the principal adds a delay of several milliseconds (ms) to all transactions when those transactions commit.
5. The principal then goes back to step 1 and repeats the process.
Every time that the principal enters step 4, the delay is increased until the delay reaches a maximum of 100 ms. As the delay added in step 4 increases, the principal is throttled more and more until the mirror catches up in step 2.
No. However, if you spent some time working with an external tool that sent SUSPEND and RESUME statements to the mirroring session at the right times, a delayed mirror might be possible. The recommendation for a delayed secondary is to use log shipping.
The location of the witness (indeed, the location and configuration of all the servers) depends on what problems are anticipated and what errors must be reduced. Ideally, all three servers should be in well-maintained locations with high-quality services and personnel.
If there two and only two data centers, where should the witness go? With the mirror or principal? The answer depends on what the weakest link in your infrastructure. If the power is likely to fail in the principal site, the witness should probably accompany the mirror to avoid losing two different servers, and therefore quorum, if the power system fails. Generally, the witness would accompany the principal.
The database cannot be changed to use the bulk-logged or simple recovery model. You can import the data as before; however, the data will be fully logged and sent to the mirror.
You have the following solutions:
Turn off mirroring, change the recovery model to bulk logged, import the data, change the recovery model back to full, do a log backup, copy the backup over and restore the backup, and turn on mirroring again.
Take the performance hit.
Use a different technology (for example, SQL Server 2005 Replication). Any solution that works at the physical level—log shipping, mirroring, stretch clustering—will have to move the physical contents of the files to the other side.
The send queue is the part of the transaction log that has been hardened on the principal, but not sent to the mirror. The redo queue is the part of the transaction log that has been hardened on the mirror, but not redone.