T-SQL Tuesday #62 – Invitation to HealthySQL

The T-SQL Tuesday question this month is from Robert Pearl and is a topic that is near and dear to me. The topic overall is keeping your SQL Server Healthy, and this is the primary function of my current job. The team I am on watch a number of servers for over 100 clients. This is so near and dear to me that by chance I just finished up a blog post that is going to be a 10 part series about the common pitfalls I see on SQL Servers. The first one, by far is what I consider the most important. Backups.

Here is the complete blog post…

Tip Number 1 – Check your Backups

Top 10 Tips for SQL Server Performance and Resiliency

I have been a consultant for a number of years and have lost track of how many different servers I have worked on a long time ago. However today as sit down and think, it occurred to me that so many of the countless hours I have spent working on correcting issues could have been solved by not making some of the common mistakes I see. My goal in this series of blogs is to highlight each of the common mistakes that I have seen and some of the resolutions associated with them.

Unlike other top 10 lists we are going to start at the number one rather than looking at number 10 and then counting down in dramatic fashion. The number biggest mistake that I see on a pretty regular basis that will impact SQL Servers is how the databases are being backed up.

Not Backing up Servers

I am not sure if companies don’t realize that SQL Server should be backed up independently of the operating system or if they just assume that the servers don’t need to be backed up at all. Granted most of the servers that I see that have no backup plan at all are smaller companies, and I am seeing the less and less each day. What I do see most often is backups that don’t match the company’s data protection needs.

When looking at how to backup your SQL Server you need to start with the question of how much data can you lose. Once that question has been answered it can be used to drive your backup strategy. There are other questions you will need to address such as where to store the backups, how long do you keep the backups around and how fast do you need your system back up and running if you are having to restore the file.

Not Testing the Restores

As crazy as it sounds, getting a backup of your server is not the last step in this backup task. There is only one way to make sure that your backups are going to work for you the way that you want it to, and the time to find out that the strategy is flawed is not when you need to restore your database. There are many aspects to restoring your database that are often forgotten.

Consider this, if your backup is stored at an offsite facility, how long will it take to get that backup back on site where a DBA can start restoring the database. What are the steps required to retrieve this backup from the offsite location? Then when that backup file is back on site with your server, who needs to be there to move a back from one medium to another. This doesn’t only impact organizations that have tape media, if you are storing your backups in the cloud how long will it take to get that backup from the cloud and bring it back to your servers, and what happens if the event that has you searching for backups has also impacted your internet connection?

Before you need to restore your database, test it. With the technology that we have access to today it is simple to turn up a VM so you can test restore your backups. To take it one step further this should be part of your monthly maintenance. I work with some companies that know how important those backups are and they restore every backup they take to make sure it is a solid backup.

Not taking backups offsite

I hate to make a point by using a scare tactic, but the reality of the situation is that disasters happen. There are floods, fires, and earthquakes to just name a few. I live in Colorado and over the course of a couple summers we saw 2 major fires that destroyed many homes and businesses. We saw the loss of a restaurant that has been a spotlight of our community for over 40 years, we saw major shipping companies that shut down, but because of solid disaster recovery plans they were back up and delivering packages with 8 hours and they didn’t even have their trucks.

There are so many technologies that can assist you with your backup strategies, you can store your backup in the cloud, you can send it over the internet to a data center in another state. No matter the solution, if your data is critical to your operations your backups should be stored in a location separated from your SQL Servers.

Every layer of your backup strategy needs to consider the protection of your backup. This includes not storing your backups on the same storage where your data file is located. As solid as SAN technology is, there is always the potential of losing your storage. Backups that are stored next to the data and can be a costly mistake.

Securing your backups

If you have been watching the news lately you may have noticed a number of organizations that have had their data compromised. The larger the organization the more difficult the public relations become after such a compromise. The last thing any DBA wants to be part of is a letter to customers letting them know the data they are responsible for has been compromised.

Protect your database, encrypt them with a password and manage the permissions on your backup locations like they contain your personal social security number and your debt card PIN code.

Not backing up everything

SQL Server today can have many dependencies outside the databases. CLR code allows the database to use code that is not stored in the database, and certificates are used to encrypt the data. If those are not backup with the system, restores can become a serious problem.

Make sure everything is backed up that your server would need to continue operation. This is another good reason for a test restore.

Maybe the primary question you need to answer is; how important is your backup? What is the impact of not being able to restore your database, can the data be recovered from other means? What is the impact on your backup strategy to your business? Once you have a good solid answer to how important your backups are, look at your existing strategy and determine if your backup strategy matches your backup plan.