Resolving Mirror Errors

A host of network, operating system, or SQL Server issues can cause a database mirroring session to fail or not even get started. No matter at what stage your mirroring session, you need to be constantly vigilant for errors so that you can react in time to prevent a simple error from resulting in a complete stoppage of the mirror session.

There are two classes of error to consider in a mirroring session. The first, a “hard” error, occurs when a component anywhere in the system fails and renders database mirroring unable to continue. It will then report that a error has occurred. These components will report errors independent of mirroring, but mirroring will detect them and react accordingly. “Soft” errors occur when the mirroring subsystem detects that an error has occurred. Depending on how you set up to fail over, manually or automatically, if the database session is running in a high-safety mode with automatic fail-over and it is deemed synchronized, an automatic fail-over occurs.

Here are hard error causes that can interrupt the mirroring session:

• Bad network cable

• Bad network card

• Changes or failure in network topology (such as routers)

• Network services, such as DNS and Active Directory

• Firewalls devices and their configuration

• Endpoint configuration

• Network security

• Hard disk or server failure

• Operating system failure

SQL Server 2005 and its mirroring subsystems have no way of monitoring any of the preceding components or others. On a complex and mission-critical implementation you should not hesitate to configure a Microsoft Operations Server package for the SQL Server mirroring session.

Mirror Time-Out

So-called soft errors are not directly detectable by SQL Server, and thus if a soft error occurs, it could potentially cause the mirror session to wait indefinitely for a response. Thus database mirroring will time out a session if session “pings” are not responded to during fixed intervals.

As long as pings are sent and received according to the time-out settings, the mirror session will be deemed to be open and occurring. If a ping is not responded to, then the server will wait until the time-out before suspending the session. Once a ping is successfully received, the algorithm on the server instance resets its time-out counter on that connection.

Be aware that even if the target server is alive and replicating, it may still time out because of a bottleneck or an intermediate problem on the network. In this case the time-out value for a session may be too short for the normal responsiveness of either partner. This would produce a false failure. You thus need to factor this into your topology, especially over WAN communications.

Under the asynchronous nonmonitored session, the default time-out value is 10 seconds and cannot be changed. If you use synchronous or high safety with automatic fail-over, you can change the time-out period to suit the environment. You can change the time-out by using the ALTER DATABASE <database> SET PARTNER TIMEOUT <integer> statement (see Chapter 10).