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.
