Archive for the ‘Top 10 Tips for SQL Server Performance and Resiliency’ Category

Top 10 Tips for SQL Server Performance and Resiliency

This article is part 10 in a series on the top 10 most common mistakes that I have seen impact SQL Server Performance and Resiliency. This post is not all-inclusive.

Most common mistake #10: Storage

For the final post in the top 10 items that influence the performance and resiliency of the databases, we will talk about storage.  Storage is by far the number one cause of performance issues I have experienced, and over the last 20 years of my career, the changes with storage have been huge. When I first started as a professional, we were using internal spinning disks and then quickly changed up to internal RAID arrays. Five or so years later, we moved to external RAID cages.  Eventually the road took us to SAN and NAS storage and most recently SSD.  With all these changes, it is easy to see why we have administrators who focus on nothing but storage.  If you are fortunate enough to have a Storage Administrator, do you as a DBA still need to understand what the storage is doing?

How can you identify if you are having some sort of performance bottleneck?  There are a number of indicators that can provide you with the evidence your database is having a storage issue. A common indicator used for a number of years is the storage latency.  Storage latency information is collected by using the Performance Monitor in Windows.  Add the counters Average Disk/sec Read and Average Disk/sec Write.

The storage latency can be monitored in real time or the data can be recorded by starting a data collection.

According to Microsoft Best Practices, the latency on the disk the log file resides should be less than five milliseconds, and the data file latency should be less than 20 milliseconds.  In my experience, I have seen log file latency climb as high as 10 millisecond and sometimes a little higher during spikes without any end user impact.   In addition take location note of the TempDB database as we talked about in Tip 8. You will want to ensure you are keeping the TempDB on the fastest storage you can.

There are additional tools you may want to consider using to dig deeper into the performance of your storage systems such as SQLIO and SQLIOSIM both from Microsoft.  A couple popular third party tools include IOMeter and CrystalDiskMark.

Please do not miss my other blogs regarding this topic.

Top 10 Tips for SQL Server Performance and Resiliency

  1. Improper Backups
  2. Improper Security
  3. Improper Maintenance
  4. Not having a Baseline
  5. SQL Server Max Memory
  6. Change History
  7. Disaster Recovery Plans
  8. TempDB
  9. AutoShrink


Top 10 Tips for SQL Server Performance and Resiliency

This article is part 9 in a series on the top 10 most common mistakes that I have seen impact SQL Server Performance and Resiliency. This post is not all-inclusive.

Most common mistake #9: Automatically Shrinking Your Database

This is a topic that has been written about frequently, and most often, I try not to re-hash what many people have already blogged about.  However, as often as I see this I would be amiss if I did not add auto shrink to the list.

Often you will see IT professionals approaching their tasks from different angles.  Consider if you were a Systems Admin and you knew you needed some additional storage on a server you might send a request to the storage admin requesting an additional 50 gigs, or whatever amount you need.  As a Database Professional, you would be wise to not only include the size of storage that you need but also the performance specifications that you require.  As a DBA, we need to understand that SQL Server management may not always translate well to other types of systems management.  Now granted this should be no surprise, it is understand we do not approach all things the same way, but where this comes into play is the understanding we all have different backgrounds.  We became DBA’s from different career paths.

If you are new to being a Database Administrator or the Primary focus of your job is not to be a DBA you may see the benefits of shrinking a database automatically.  If the database shrinks by itself, it might be considered self-management; however, there is a problem when doing this.

When you shrink a data file SQL Server goes in and recovers all the unused pages, during the process it is giving that space back to the OS so the space can be used somewhere else.  The downstream effect of this is going to be the fact your indexes are going to become fragmented.  This can be demonstrated in a simple test.

I have a database in my lab based on the Chicago Crime Stats.  I have been doing a lot of testing in the database with an automated indexing script, that has me inserting a deleting a large number of rows at different times.  Over time this database has become rather large for my small lab, it is time to shrink it down to a more manageable size.  The first thing done is to check what the status of my indexes is.

This is a simple query that will return all the indexes in the database with its fragmentation level.

SELECT db_name() as [database],
      Object_Name(ps.object_id) as [table], as Index_Name,
      round(avg_fragmentation_in_percent, 0) as Frag
FROM sys.dm_db_index_physical_stats(db_id(), null, null, NULL, NULL) ps
            Join sys.indexes i on ps.Object_ID = i.object_ID and ps.index_id = i.index_id


The results look like this:



More or less the indexes are looking good; there is not a lot of fragmentation except in the one table (that is a discussion for later topics). What happens if I shrink the whole database, to include not only the log but also the data file as well?


Use the following T-SQL:

DBCC ShrinkDatabase ([ChicagoCrimeStats])

Rerunning the index fragmentation script, I now receive these results:



If I have queries that use the IDX_Crimes_Frag_XCORD_Clustered index, there is a real good chance the performance on that query is going to degrade.

There are times when you may need to shrink a file, some considerations could be after a large delete of records or maybe you archived much of the data out of the database.  These sort of operations remove data leaving your databases with a lot of free space.  This free space can be reclaimed by using the DBCC Shrinkfile or DBCC Shrinkdatabase T-SQL commands, however be aware you should re-index after those statements are run.

It is not a bad thing to shrink a database as long as you do it in a controlled manor with proper maintenance afterwards.

Top 10 Tips for SQL Server Performance and Resiliency

  1. Improper Backups
  2. Improper Security
  3. Improper Maintenance
  4. Not having a Baseline
  5. SQL Server Max Memory
  6. Change History
  7. Disaster Recovery Plans
  8. TempDB

Tip # 6 – Change History

Top 10 Tips for SQL Server Performance and Resiliency

This article is part 6 in a series on the top 10 most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is not all inclusive.

Most common mistake #6: Not Keeping a Change History

Business today moves fast, companies change interfaces to keep content relevant and are continuing to offer new services to their client base. In a perfect world code is tested 10 ways to Sunday and is never released with bugs or design issues, however we don’t live in that world. I mean no disrespect to the developers that spend hour upon hours trying to provide the best changes possible. There are just so many variables that making a release perfect every time is just not a reality.

There is an inherent risk with change; software, hardware or configurations including SQL Server.

When a change is being planned no matter how small of a change, these changes should be documented. Chances are most DBA’s have heard this before; however, simple changes are often overlooked. Documenting the change is a great start; however, keep in mind that it is critical that the documented changes are easily researched. In my experience with working with SQL Server, most issues with SQL Server can be tracked back to a date when an issue started. Because of this, I recommend storing documents so they can be searched by date.

Stability increases when you embrace change history and take change management couple steps further. Consider setting up a change process. Implementing policies such as not allowing changes to be made to the SQL Server unless they are being made via scripts. There are some great benefits to only allowing changes via script. Consider a small table change, a few of the benefits that you will see by using a script over the user interface:

  • When changes are made via script, and the script has been saved it is easy to see exactly what was executed. When changes are made via the user interface you have to assume you know what the exact change was.
  • Changes made via the user interface are susceptible to the “fat finger” otherwise known as a typo. You can review your change with your eyes, but having others review your change is more difficult. If you mistype the name you are now left with having to make a second change to correct your mistake.
  • When using a script, you can share what the change is going to look like before the change is made. You can send the new table layout to other teams in the company and they can see exactly the totality of the change.

When making changes it is also important to have a roll back script. This is most likely the most overlooked part of change management. Having a roll back script is not often needed and when everything is rolling out as planned there is no thoughts to having to roll back these changes. When a changes are made and it isn’t going as well as it was planned someone may make the decision to cancel the change. A restore can often lead to extended hours of downtime and unexpected impacts to customers. These are the times when the extra effort put into a rollback script prove to be priceless.


Top 10 Tips for SQL Server Performance and Resiliency

  1. Improper Backups
  2. Improper Security
  3. Improper Maintenance
  4. Not having a Baseline
  5. SQL Server Max Memory

This article is part 5 in a series on the top 10 most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is not all inclusive.

Most common mistake #5: Not Setting a Maximum Memory

Default configurations are not always the best configuration for your server. Maximum memory allocated to SQL Server is a great example of this. If this setting is left unchanged, there is a chance your SQL Server could be starving the operating system of memory. When SQL Server starves the operating system, the OS cannot provide a stable platform. A struggling OS will have a difficult time retrieving data from disk, swapping processes, and completing standard OS functions to name a few issues.

There are a number of ways to see if the operating system is running into memory issues. One of the fastest ways is to simply look at the task manager and see how much is available. If you review the available memory when the server is not performing well and the amount of available memory is low, there is a good chance the OS is being starved. With tasks like file copies, backups and virus scans, the operating system will look for memory to help complete the process.   If SQL Server has that memory reserved, the OS will not have the memory it needs to work efficiently.

Proper SQL Server memory configuration can take some fine tuning as a number of variables need to be taken into consideration. For example if there are 2 SQL Server instances installed you will need to leave the memory configured for each SQL Server low enough so the Operating System has enough to work with. And don’t forget processes like copying large files to different media, such as a file copy for a backup. Finding the balance can often leave DBA’s going in circles. When you add virtual servers into the mix the variables increase even more and sometimes the memory configurations go against SQL Server best practices (VMWare recommends min and max memory to be set to be the same). There are many formulas for recommend initial max memory settings.

So where do you start? A general rule of thumb is to leave the operating system 20% of the memory. With that being said, I hesitate to leave the operating system anything less than 2 gigs. After making a memory setting adjustment, close attention should be given to monitoring the memory counters in the performance monitor and the DMV’s in SQL Server to ensure the operating system has enough memory to perform well without fighting with SQL Server.

Top 10 Tips for SQL Server Performance and Resiliency

  1. Improper Backups
  2. Improper Security
  3. Improper Maintenance
  4. Not having a Baseline

Tip # 4 – Not having a Baseline

Top 10 Tips for SQL Server Performance and Resiliency

This article is part 4 in a series on the top 10 most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is related to the most common errors I see and is not all inclusive. If you like this post check out the other tips already posted:

  1. Improper Backups
  2. Improper Security
  3. Improper Maintenance

Most common mistake #4: Not having a Baseline

Baselines can come in many different forms, however none of them are overly complex to understand. The core principal of a baseline is having a known set of metrics which can be used to make a comparison. A common and effective trouble shooting technique when something goes wrong is review what has changed. However, if you don’t have a starting point showing you what the system was like before something changed, finding what has changed is much more difficult.

Performance Baseline

I am often reviewing systems after they have started to exhibit performance issues. My goal with these type of engagements is to return the server to normal or better than normal performance as quickly as I can. Without a performance baseline of how the system runs under normal circumstances, leaves whoever is troubleshooting the performance issues without a valuable tool.

At the basic of level, SQL Server performance can be grouped into 3 categories where a performance issue may lie; hardware, operating system and SQL Server. Consider having a performance monitor counter run that includes some basic counter that could be relevant to each section.

Memory\Available Bytes


PhysicalDisk(*)\Avg. Disk sec/Read

PhysicalDisk(*)\Avg. Disk sec/Write

PhysicalDisk(*)\Current Disk Queue Length

PhysicalDisk(*)\Avg. Disk Queue Length

Processor(_Total)\% Processor Time

SQLServer:Access Methods\Full Scans/sec

SQLServer:Access Methods\Index Searches/sec

SQLServer:Buffer Manager\Page life expectancy

SQLServer:General Statistics\User Connections

SQLServer:Latches\Average Latch Wait Time (ms)

SQLServer:Locks(_Total)\Lock Wait Time (ms)

SQLServer:Locks(_Total)\Lock Waits/sec

SQLServer:Locks(_Total)\Average Wait Time (ms)

SQLServer:Memory Manager\Memory Grants Pending

SQLServer:Memory Manager\Target Server Memory (KB)

SQLServer:Memory Manager\Total Server Memory (KB)

SQLServer:Plan Cache(_Total)\Cache Hit Ratio

SQLServer:SQL Statistics\Batch Requests/sec

SQLServer:SQL Statistics\SQL Compilations/sec

SQLServer:SQL Statistics\SQL Re-Compilations/sec

System\Processor Queue Length

System\Context Switches/sec

When you leave tasks to be run manually, such as taking a baseline each month, there is a risk of someone forgetting or the task being put aside for higher priorities. Consider scheduling your performance monitor data collections.

Configuration Baseline

The baseline you have shouldn’t stop at the performance monitor. The configuration of your SQL Server with a document explaining some of the non-standard configurations should be done as well.

Recently I was working on a performance problem for a client who has had their SQL Server online for years. The server had been slowing down over time and the client needed the performance to be better. When we were looking at the database configuration, we noticed the compatibility level on the database was set to SQL Server 2000 even though the SQL Server instance was 2008. Everyone who worked in the IT department when the server was configured had moved on a few years prior. The server and a number of settings configured that were not default, some of the settings were not best practice. The only way we could determine if the setting was correct or if it was just something someone overlooked was to make the change back to the default and complete regression testing to see the impact. This added a lot of time and delay to correcting the overall performance issue. A configuration baseline would have saved a lot of time and money. I recommend doing a health check\baseline on your SQL Servers once a year.

Top 10 Tips for SQL Server Performance and Resiliency

  1. Improper Backups
  2. Improper Security
  3. Improper Maintenance

Top 10 Tips for SQL Server Performance and Resiliency

This article is part 3 of 10 in a series on the most common mistakes that I have seen impacting SQL Server Performance and Resiliency. This post is related to the most common errors I see and is not all inclusive.
Update: For even more tips, Fortified Data has written a helpful post with recommendations for creating a SQL Server maintenance plan

#1 Most common mistake – Incomplete Backups

#2 Most common mistake – Check Your Security

Most common mistake #3: Improper Maintenance

When it comes to maintenance, SQL Server is no different than an automobile; both require regular maintenance to keep them running at peak performance. In similar fashion, the greater the load, the more often the maintenance is required. Neglecting maintenance is the third biggest mistake that I frequently see impacting SQL Server’s performance.

Fragmented Indexes

Indexes in SQL Server are very similar to indexes in a book. The primary difference between indexes in a book and in your database is the indexes contained within the database are much easier to rebuild when new information is inserted. Imagine you had a book with 100 pages of text. If the book were indexed, finding specific information wouldn’t be too difficult and shouldn’t take much time. Now add 50 pages of new text in the middle of the book. Finding specific text after the 50 pages has been added would be difficult, all the page numbers would be off and the new text wouldn’t be indexed.

To solve this problem with SQL Server, we have the option to rebuild the index or reorganize an index. This should be completed on a regular basis. This ensures data location accuracy, which eases the work load on SQL server. This allows SQL Server to find the specific data it needs quickly and efficiently.

Stale Statistics

Sometimes, people have difficulty understanding SQL Server Statistics. Think of it this way: If you managed a sports team and it was your job to find new talent, would you want to see how much potential the talent has? You may want to know how strong they are or how fast they can run. Trying to pick up a new quarterback without that information would be difficult. What if the player’s statistics you were looking at were 5 years old? Maybe the statistics were from when they were in the best shape of their career and now they have gone a little past their prime. You may hire a player based on bad information if you didn’t know the statistics were out of date, they may not be that star performer you were looking for.

SQL Server keeps statistics for you as well. This information keeps your SQL Server aware of the information in the database and helps it decide on what the fastest way to retrieve data is. However, there are times when this information needs to be refreshed. Keeping your statistics up to date is going to keep your SQL Server aware of what shape your data is in.

Consistency Check

Isn’t it nice when your car starts to warn you before something really bad is going to happen? Sometimes it can be something as simple as change the oil in the engine or changing the battery.

SQL Server has warning signs as well; DBCC commands or database consistency checks should be executed to catch when something may be amiss in your data. This doesn’t mean that it can catch everything, but this layer of protection can save you some serious pain down the road.


Children that are being watched at a child care center or by parents are a lot less likely to get themselves into trouble. SQL Server is no different. A monitored SQL Server is less likely to have critical non-recoverable errors than servers that are not monitored. For example, pretend you have a database that requires 100 gigs of space for a backup file. If that storage falls below having 100 gigs of space available, your SQL Server backup is going to fail. What would happen if you lost the storage due to a hardware failure and now you need to restore from a backup? You could lose a tremendous amount of data and time, assuming you are able to recover it at all.

Now consider the same situation with a monitored SQL Server. A monitored SQL Server should inform you that you had a backup failure, and if it is scheduled via a Job in SQL Server, you can receive an alert from the SQL Server itself. In addition, there would be entries in the error log that you would see as well. A monitored SQL Server is going to give you an opportunity to correct the backup issue, before you need to restore the database.

It’s getting easier to complete a basic install of SQL Server with each and every version. It takes very little time to have a database up and running. It is so simple that many organizations install their SQL Server and forget about it – at least until there becomes a problem with it. Most of the time when I am asked to perform an emergency fix or a restore of a database, it is being done on a server that isn’t being monitored.

For detailed tips on how to move from reactive to proactive SQL Server monitoring and management, read this post.

Database Mail, Alerts and Operators not configured

As mentioned in the monitoring section of this post SQL Server can be configured to send emails in a number of situations; if a job fails or errors are raised with specific severities. These emails are sent to operators that are configured within SQL Server. So when a backup fails, SQL Server can send you an email, allowing you to fix the issue.

When my son started to learn how to drive a car, we spent a number of hours talking about the maintenance. We reviewed how he should keep sand in his trunk to help get traction on the ice, how his oil needs to be changed and the tires need to be rotated, all to ensure his safety while on the road. A well maintained SQL Server can give your organization a stable and well performing database.

Tip # 2 – Check your Security

Top 10 Tips for SQL Server Performance and Resiliency

This article is Part 2 in a series on the Top 10 Most Common Mistakes: SQL Server Performance and Resiliency. Neglecting security is the second biggest mistake I frequently see impacting SQL Server’s performance. In case you have missed the prior posts:

#1 Most common mistake – Incomplete Backups

I can’t tell you how many times I have been told “We have a really good firewall so the internal security we have in place is not as important” or “We like to operate with a level of trust, and Chris, you need to learn how to trust people”. The second quote came from an IT Director from a public company. My response to the trust argument is that security has nothing to do with trust, security is about protecting the data from not only things that people may set out to do intentionally, but also protect the data from things people can do on accident.

I am not ashamed to admit I like to have my SQL Servers secure. I consider it not only my job, but a core responsibility in my career. If you have any doubt of how important security is today, just take a few moments and do a search on cyber-attacks. With recent headlines where major organizations are victims of cyber-attacks, hopefully this post becomes all that more important to you. Below are a few practices and procedures that can help secure your data, as well as accidental mistakes made by people without the proper knowledge of the database practices and procedures:

Not everyone should be a SA

The SA name is short for Systems Administrator. This account has permissions to everything inside SQL Server. When a user requests SA, they may be referring to a couple things. They may be asking for the password of the SA account or they may want you to assign their login to the Systems Administrator role. In my opinion, there are very few people who need this level of access to a SQL Server. Usually, these people have a job title that says, “Database Administrator”. Keep in mind: the more people that have this access level to your SQL Server, the more risk you take when it comes to how many people can make really big mistakes.

To be secure, make sure the SA account has a complex password. I recommend setting the SA password to a complex password and disabling the account to be a bit more secure. The administrator’s role should have their own accounts. Treat the SA account as hidden back door into your system and only use it when nothing else works.

Not everyone should be a DBO

In like fashion to SA access, not everyone needs DBO access to a database. DBO is short for database owner. Accounts with this access have permissions to everything internal on the database. This means they can delete users, tables, stored procedures and even data. Since SQL Server cannot make a determination between DELETE statements done on accident and on purpose, it becomes imperative that someone with DBO or SA access knows what they are doing.

I often see people treat security levels as a negotiation between two parties, almost like what you would experience when purchasing a car. The requestor asks for ‘SA’ level access and the DBA might respond by haggling them down to Data_Reader access, only to have the requestor push for something in the middle, like DBO access. As funny as this sounds, some DBA’s agree to it. The theory being that DBO is more restrictive than ‘SA’, so logically, security is intact. The end result in this hypothetical bargain is ‘DBO access’, which many people don’t need. Check the accounts and find out what is required to complete the work tasks. Don’t give employees any more room to break things than they need.

Securing your backups

The larger the organization, the more difficult public relations become after a data breach. 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 databases, encrypt them with a password and manage the permissions on your backup locations like they contain your personal social security number. The protection of your database backups doesn’t stop there, make sure the offsite locations is secure as well.

Linked Servers

Linked servers can add tremendously to the ease of retrieving data from your SQL Servers. Often, people get pretty excited when they learn how easy it is to use and configure. However, a big mistake that I often see has to do with the security configuration of the link. When the linked server is configured, settings that should be set to protect the data on the server being linked to. I often see configurations that will allow a specific named user that may have Data_Reader on the primary server connect to the linked server using the SA account.


In older versions of SQL Server, if you had an administrator account on the windows machine such as local admin access, you would by default inherently have SA access to the SQL Server. This can be very helpful in small shops where a Windows Admin may also be a SQL Server Admin. The issue is sometimes the Windows Admin shouldn’t have that level of access to the data or configuration of SQL Server.

No Policy Management for SQL Server Accounts

SQL Server authenticated accounts, or accounts that reside only in SQL Server have the option to have the policy enforced by the Windows Policy. Sometimes these are configured this way because people don’t want to change the password for SQL Server, or have to worry about having a strong enough password. This might be related to the theory that if the server is behind a firewall it is protected enough. It is a good idea to have your passwords in SQL Server follow the same policy that your windows accounts follow.

This is just a short list of some of the common security mishaps I have encountered. There are others you should be aware of, like updating your service packs, staying on top of when people leave your company or auditing your group memberships. When you look at your database and you believe you have secured it enough, take another look. Consider creating a table called My Personal Info, and in that table insert your credit card, your debit card (don’t forget the P.I.N), your social security number and your banking information. Once that is all inserted there, how comfortable do you feel? Your data deserves to be secure and people every day are relying on you the data owner to keep it secure.

T-SQLTuesdayThe 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.