High-Availability Database Solutions

Last month I delivered a training about Designing High-Availability Database Solutions Using Microsoft® SQL Server™ 2005 for three days. It made me a lil bit stressed coz the subject was new for me and my student was a Senior DBA with plus-minus 12 years of experience. Can you imagine? :D

Well, let's start with an overview.

The availability of a database refers to the overall reliability of the system. A database that’s 100% available is one that never goes down. For some databases, being down for an hour is not a problem; for others, 30 seconds of downtime is catastrophe. It’s all depends on organization’s requirements.

Microsoft® SQL Server™ 2005 has several solutions for making database systems highly available. There are failover clustering, log shipping, mirroring, and replication to protect users from hardware, software, and process failures.

Clustering

Generally, the goal of a cluster is to enable the sharing of a computing load over several systems without users knowing that there is more than one system involved. If any hardware or software components in the system fail, the users may see degraded performance but will not lose availability to the database solution.

In failover clustering, what you’re doing is making copies of your hardware. For example, you’re gonna have identical hardware on identical servers. You’ll have the same username of logins, you’ll have all the same applications, you’ll have SQL Server instance installed on the servers, so you have a copy of one or more nodes. However, the actual storage is not copied. You only have one copy of your database. So, if you’re using clustering, you need to make sure that you have some kind of high-availability solutions for the data itself.

Clustering is based on Microsoft Clustering Service (MSCS) so it requires hardware that is listed on the Hardware Compatibility List (HCL). This solution is considered as the most expensive one and the most robust one.

Mirroring

Another solution available to us is database mirroring. Mirroring is less expensive than clustering and easier to set up. With mirroring you can use more flexible hardware, any hardware that is supported by Windows 2003; whereas in clustering, your choices are more limited because clustering requires hardware that’s listed on HCL.

Database mirroring does protect against disk failure because the mirror is actually a copy of our database; whereas in clustering, two or more nodes share a disk so that if the disk get corrupted then you’re in trouble!

Log shipping

The basic concept behind log shipping is to take advantage of the backup and restore operations in SQL Server. Log shipping allows us to automatically send transaction log backups from the primary database to a secondary database on potentially multiple servers. These transaction log backups are restored to the secondary database; this keeps it synchronized with the primary database.

Replication

Replication allows us to make data redundancy across servers. It’s pretty easy using replication if you got a database that has several tables or views, functions or procedures, and you wanna have those copied over other databases. If for any reason your original database gets corrupted, at least you have copies of that data on some other databases.

Each solution for a high-availability has unique advantages and drawbacks. There is no one solution that’s considered as the best solution because it will depend on the situation of organization. So, a good DBA must be able to determine the most appropriate solution for his/her business needs and deploy the solution in a manner that takes maximum advantage of its benefits while minimizing its drawbacks. In additional, remember that the technology alone cannot provide a high-availability solution. You must also have the right people and processes in place to support the technology.

Good luck !! :)