ACHIEVING HIGH AVAILABILITY IN A MICROSOFT SQL SERVER 2005 ENVIRONMENT
By Brandon Hansen
High data availability needs be the goal of every database administrator and system
engineer responsible for maintaining SQL Server 2005 databases and servers in the health
care environment. Maintaining high availability of data allows the ability to quickly bring
your SQL Server 2005 databases and servers back online with little or no interruptions after
a system failure.
There are several methods and features that can be used to achieve high levels of data
availability. There are several main methods and SQL Server 2005 features that can be used
to ensure that there is less downtime of your SQL servers and little or no data loss from a
system failure. Each of these methods and features are discussed in greater detail in a
whitepaper by this same author that can be found on the Vitalize website
www.getvitalized.com. This article will briefly cover
each topic to help your site make more well-informed decisions on the best approach for
maintaining a high level of database and SQL server availability. These methods and
features are as follows:
- Windows 2003 Server Clustering- When setting up a SQL Server 2005 environment
for high availability, the first method that should be implemented is the clustering of
your SQL servers. The servers used for SQL Server 2005 can be clustered if they are
using a Windows 2003 Enterprise or a Windows 2003 Datacenter operating system.
- RAID Arrays- The next method for maintaining high availability of data in a
SQL Server 2005 environment is the implementation of RAID drives (Redundant Array of
Independent Disks). There are several different RAID levels available for use. Each is
briefly described below:
- RAID 0- Data is striped among several different disks that act as one virtual drive. RAID 0 does not provide fault tolerance. Therefore, if one disk fails, then all the data in the array is lost.
- RAID 1– Also known as mirrored RAID, because all data that is written to one disk in the array is also written to another disk, which halves the amount of available disk space. The benefit of using RAID 1 is that there is complete fault tolerance between the two disks.
- RAID 5– Data is striped across the disks in the array much like RAID 0. However, RAID 5 also provides for fault tolerance through the generation of parity bits. The parity bits in a RAID 5 array are spread among all the disks and can be used to help put the data back together if one of the disks fails.
- Additional RAID levels– Basically, a combination of the previously mentioned RAID levels.
- RAID 0+1– Combines stripe sets with mirroring. In this RAID array, if there are six disks in the array, three disks are used for striping data and the other three disks are used as a mirrored set to provide fault tolerance.
- RAID 1+0 (RAID 10)– This RAID level is the opposite of RAID 0+1. In this level, two or three mirror sets are created first, and then data is striped across these mirror sets.
- Hardware or Software Arrays?
- Software-based RAID arrays are implemented in Microsoft Windows 2000 or later operating systems. In this type of array no additional hardware costs are associated, but you will experience a performance hit.
- Hardware-based RAID arrays are operating system independent and come in most modern servers today. These are much fast and provide the ability to rebuild failed disks without shutting down the server.
- SQL Server 2005 Database Mirroring– This is a new feature introduced with SQL Server 2005. It provides a mechanism for duplicating data across different SQL Server instances. In a database mirror, there are two copies of the database that are stored on two separate servers.
- Transaction Log Shipping– Part of SQL Server 2005, Log Shipping involves three phases that are used to maintain data redundancy. In the first phase, the transaction log is backed up on the primary server. In the second phase, the log is copied to a secondary server. In the final phase, the log is restored to the database on the secondary server. Log shipping does not provide for automatic failover.
- Database Snapshots– Another new feature in SQL Server 2005. Database snapshots provide the ability for point-in-time data recovery and analysis. Snapshots do not provide for a standby database in that the original database must be available as the data set is rebuilt. However, the snapshot can be used to restore the database to the point-in-time when it was originally created.
- Data Replication- Another SQL Server feature that implements data redundancy and provides for a higher level of data availability is replication. Data replication allows data to be available in multiple physical locations or on multiple server instances.
More detail can be found on all the features and methods described in this article via
the detailed whitepaper on the same subject found here:
www.getvitalized.com. The features available with SQL
Server 2005 should be used in conjunction with the clustering of your servers as well as
with the use of RAID arrays. Implementing these features and methods together will allow
you to achieve the highest levels of data redundancy and availability possible and will help
you to succeed in the goal of having zero data loss and one hundred percent uptime of your
production databases and servers.
If you would like more information on this topic and the services that Vitalize Consulting
Solutions, Inc. has to offer, please contact us at our Corporate Offices 610-444-1233 or
vcs@getvitalized.com. We are also always available on
our website www.getvitalized.com.