How many readonly mirrors should be configured?

By: Steven Shaughnessy

Abstract: faq: How many readonly mirrors should be configured?

If you need incremental backup or manual failover support, at least one readonly mirror is needed. More than one readonly mirror can be configured for additional safety. Note that the location of the readonly mirrors may be a bigger concern than the number of readonly mirrors.

If you need automatic failover support, then at least 1 primary and 2 readonly mirrors must be configured with the AUTO_FAIL_OVER property set to true. Note that automatic fail over will only be attempted if a majority of mirrors can be connected to by the mirror that is being failed over to. So if a total of 3 mirrors are configured, at least 2 need to be on line (a total of 2 readonly mirrors). The majority rule ensures that 2 primaries can never exist at the same time after an automatic fail over operation is completed. Note that manual fail over does not have this restriction. With manual fail over it is assumed that the user knows for sure that the prior primary mirror is not accessible.

If you are using readonly mirrors for increased scalability, you can use several mirrors to off load read only queries from the primary mirror. It is best to configure a directory mirror to achieve this goal. Connections made to the directory mirror with the readonly property set to true (readOnlyTx=true), will be redirected to one of the readonly mirrors. The directory uses a round robin algorithm to load balance readonly connection requests across all of the available readonly mirrors. The more readonly mirrors you have the more computers you have to service readonly query requests. However, there is also a cost to keeping all of these readonly mirrors up to date. The log files from the primary mirror must be periodically synchronized with all of the readonly mirrors. Configuring a hierarchical synchronization scheme can significantly reduce this overhead. In such a scheme the primary mirror synchronizes with a small number of first tier readonly mirrors. Additional second tier readonly mirrors can be configured to synchronize with one of the first tier readonly mirrors. In this way the primary only has to synchronize the first tier readonly mirrors. The first tier readonly mirrors can then synchronize the second tier readonly mirrors.

Server Response from: ETNASC04