Database Maintenance
-
Rebuilding statistics
-
Rebuilding indexes
-
DBCC jobs
-
Backup jobs
Routine database maintenance often slows down SQL Server, even locking users out of tables. All of this can add up to reduced availability.
Database Jobs
-
DTS
-
BCP
-
Building cubes
Scheduled jobs, whatever they are, have the potential of reducing SQL Server’s performance. It is important to design and schedule jobs with the least impact on users’ need for availability.
Third-Party Software
-
Anti-virus
-
Defraggers
-
Monitoring software
-
IIS
-
Unneeded services
Here, I am referring to software running on the same server as SQL Server. Each of these have the potential for bringing down the server, with SQL Server along with it. They can also reduce SQL Server’s performance.
Poor Documentation
-
Lack of a Run (DBA procedure) Book
-
Lack of a disaster recovery plan
-
Lack of installation documentation
You are not going to like reading this. And I don’t like writing it. But if you don’t have proper documentation, and when you have problem, you risk increasing the amount of time SQL Server is unavailable.
Now that we have a good understanding of what can go wrong, causing SQL Server to be unavailable, let’s take a look of how we can reduce the odds of the above problems from occurring, all without spending more than we need to.
What to Do to Help Ensure High Availability
In this section, we will take a look at a large number of ways to help ensure high SQL Server availability. While the emphasis here is on low cost options for providing high availability, I am still going to include a few higher cost options, just so that you are familiar with all of the available options. In addition, what is high cost for one company may be low cost for another company. Because of this, I am trying to be inclusive.
As I describe each option below, I will also categorize each in regards to importance and difficulty of implementation.
For example, the importance of each option will be rated as:
-
Critical
-
Important
-
Low Priority
The difficulty of each option will also be rated as:
-
Hard
-
Intermediate
-
Easy
And more importantly, for each option discussed below, I will describe what it does to help ensure high availability.
I have done this to help you decide how to prioritize the options available to you. While we might like to be able to implement everything here at once, that is not possible. Because of resource limitations, we have to choose what to implement first, second, and so on. Generally, I would recommend implementing the critical/easy to implement options first, and so on. This way, you will get the great effect for the work you put into this effort. So here goes.
Failover Clustering
Importance: Important
Difficulty: Hard
Used to:
-
Increase server hardware HA
-
Increase OS HA
-
Increase application software HA
-
Increase SQL Server HA
-
Increase upgrade/patch HA
-
Increase 3rd-party HA
-
NOT used for data HA
Yikes, I started with an expensive option. But again, if your company generates US $1 million dollars an hour over a web site, then clustering is not really that expensive of an option. Cost is relative from company to company and situation to situation.
There seems to be a myth about failover clustering, and that it is the ideal (or only) way to ensure SQL Server high availability. While it is an important aspect of helping ensure SQL Server high availability, it is not the most important. For example, failover clustering does not protect your data, which is the most important part of your SQL Server. While failover clustering can be an important step to helping ensure high availability, it is only one part of the solution. The rest of this article describes the total solution, as you will see.
Continues…