SQL Server 2005’s High Availability Options

You'll find an embarrassment of riches when it comes to SQL Server 2005's high availability options. Choosing the right option really depends on your organization's needs.

To jumpstart your decision process, let's get an overview of what SQL Server 2005 offers.

4. Windows Server Clustering

Microsoft's primary high availability technology for SQL Server 2005, Windows Server Clustering, provides automatic failover in the event of SQL Server system failure. Clustering protects the entire server, including the OS, system databases, systems configuration, and logins. With clustering, multiple systems are grouped or clustered together. If one system in the cluster fails, then its workload is automatically transferred to another system, or node, in the cluster. Both SQL Server 2005 Standard Edition and Enterprise Edition support Windows Server Clustering: Standard Edition supports two nodes while Enterprise Edition supports up to eight nodes. On the OS side, Windows Server 2003 Enterprise Edition and Datacenter Edition both support clustering.

3. Database Mirroring

Another high availability option is database mirroring, which provides database-level protection, although only the database itself is protected, not system databases. With database mirroring, transactions applied to one database on the principal server are automatically transferred to a backup database on the secondary server. The backup database on the secondary server can't be used and is in a state of constant recovery until it's made primary by either a failover condition or manual configuration. Database mirroring runs in either high availability mode, which utilizes a third witness server to determine when database failover is required, or high protection mode. All versions of Windows Server support database mirroring, as do SQL Server 2005 Standard and Enterprise editions.

2. Log Shipping

2SQL Server's other availability technology is log shipping. Although clustering and database mirroring both provide automatic failover capabilities, log shipping does not, which puts log shipping more in the area of disaster recovery technology. With log shipping, a stored procedure periodically forwards transaction logs from one SQL Server system to one or more backup servers. Log shipping is supported in all versions of the Windows Server OS and is also supported by SQL Server 2005 Standard, Enterprise, and Workgroup Editions.

1. Replication

Although replication can be used to transfer data between multiple SQL Server systems for distributed systems and reporting, it's not well suited for ensuring high availability, though some organizations do use it that way. Microsoft views replication as a distributed data solution that transmits selected data between systems—it has neither built-in failover mechanisms nor any built-in failback capability. To use replication as a high availability technology, you would need to either manually or programmatically add your own failover/failback procedures.