High availability is probably a DBA's highest priority. Nothing gets a DBA involved faster than the database server going down. SQL Server provides several features that you can use to create a highly available server environment. Here I discuss SQL Server's high-availability options and show you what types of failures each solution handles best.
Cluster Service
Microsoft Cluster service provides a high degree of database protection as well as automatic failover by letting you set up two or more servers in a cluster. If one server fails, its workload is automatically transferred to one of the remaining servers in the cluster. SQL Server 2000 Enterprise Edition supports Cluster service, but it can be expensive to implement because it requires multiple servers that must come from the Microsoft Hardware Compatibility List (HCL).
Log Shipping
Log shipping protects against server and database failure by creating a backup of the original database on the primary server, then restoring the backup to the standby server. The standby server is in a state of continuous recovery so that transaction logs captured on the primary server are automatically periodically forwarded and applied to the standby server. If you use TCP/IP as the data transport, you can operate the primary and standby servers in different locations. You have to initiate the failover process manually. Log shipping is included in the SQL Server 2000 Enterprise Edition, but it's a less expensive option than Cluster service because the servers don't have to come from the HCL list and you can manually implement it on any server that runs SQL Server.
Replication
Typically, transactional replication is a feature you use for distributed data but it also functions as a high-availability solution that protects against server and site failure by duplicating data between geographically separated servers. When you use replication, both your primary server and your backup servers can actively provide database services. Switching from the primary server to the backup server containing the replicated data is a manual process. All SQL Server editions support transactional replication.
Database Mirroring
SQL Server 2005 will introduce database mirroring, which uses a primary server, a mirrored server, and a witness server (that monitors the database mirror's status) with Transparent Client Redirection (in Microsoft Data Access Components—MDAC) to provide a database-level high-availability solution. Database mirroring, basically built-in real-time log shipping, begins by restoring a database backup on the mirrored server, then forwarding transaction logs in realtime from the primary server to the mirrored server. The failover process is automatic; if the MDAC layer on the client fails to connect to the primary server, it can automatically connect to the mirrored server.
0 comments
Hide comments
