SQL2005: 3.Servers

Most of the SQL Server 2005 High Availability options suggest having three servers (or instances) for optimum performance where each of the servers will play a certain role in the high availability configuration. However, depending on which option, the terms used to describe the server roles can be quite confusing for starters.

In Log-Shipping, the three server roles are Primary, Secondary and Monitor. The Primary server is where the transaction log is being backed-up and the Secondary server is where the log will be shipped to and restored. The Monitor server acts like a coordinator and tracks all details of log-shipping.

In Replication, the three server roles are Publisher, Subscriber and Distributor. Replication uses the publication industry metaphor where Publisher is the server that supply the tables (Articles) to be replicated and Subcribers are the servers subscribing to the replication. The Distributor is the server that stores replication specific information for one or more publishers. Both distributor and publisher can be configured on one machine.

Finally in Database Mirroring, the three server (or instance) roles are Principal, Mirror and Witness. The Principal database instance is the active database and the Mirror database contains the mirrored copy. A Witness server can be configured to provide automatic fail-over to the mirror database when the principal database is not accessible.

So, still feeling confused?