The Importance of SQL Documentation

The other day, I was perusing around the SQLPass website and came across something challenges_thumbinteresting: #EntryLevelBlogChallenge.  Which of course is a personal challenge to technical bloggers by Tim Ford (b|t).

The concept is simple, write one entry-level blog post a month over the next year and tag it on twitter with #iwanttoshare


My series of blogs posts will be tagged/filed under the heading of “Back to Basics (B2B)”

My first blog post in this series, entitled “B2B: Documentation” will be out next week!

Dont-miss-the-boatBecause I missed the boat on January, I will make up for it by posting two in February.

Today’s post will discuss the importance of documentation and the best way I know of getting that information.  Next week, I will show you how to automate the collection of all this data.

SQL Documentation

One of the most important tasks you can do as an entry level Database Administrator (DBA), is to write down what you do, how you do it and what you did it to.  Documentation does at least two important things: 1) provides a history and 2) most people remember things if they write them down.

Why is knowing the history of your server important? 

I’m glad you asked!  Data by its very nature is always evolving and growing which produces trends.  These trends if un-checked or undocumented will present problem in the future, I promise.  As data grows, hard drive space is consumed.  As data evolves complex queries are developed to pull data and resources are consumed.  A DBA must be able to monitor these aspects of SQL Server in order to provide optimal performance.

The first step in documentation is to determine what you have and what you are working with. One of the most effective ways of doing this is using Glenn Berry’s (b|t) SQL Diagnostic Information Queries (Jan 2016 version) to obtain all the server/hardware information you will need to document your servers. Glenn has developed his Dynamic Management View (DMV) queries based on SQL Server Version 2005, 2008, 2008 R2, 2012, 2014 and 2016.  Each new version provided different DMV functionality and Glenn has kept up with the times.

The queries are very extensive and provide a wealth of information.  At last count there were 70 queries included in the SQL Server 2016 January 2016 version.  That is a boat load of information.

The queries are numbered and designed to be executed one at a time.  Once you have started SQL Server Management Studio (SSMS) you would open the file in SSMS by either clicking the OPEN folder or selecting FILE >> OPEN >> Open File or simply pressing CTRL + O.

Then beginning at the top with the first query highlight the text of the selected query  and then click Execute or hit F5.HighlightExecute

If you have selected the correct version of the Information query then you should be seeing this message from SQL Server.


From this point, you highlight and execute the next query to see the results. 

With each result you can go to the FILE >> Save Results As…>>> command and actually save the results to a Comma Delimited (.csv) for viewing later.  As effective as this is; this is a very tedious method for collecting the results for all 70 queries.

Next post, I will show you a method of collecting the results for all 70 queries into one single Excel spreadsheet!

Preventing a Disaster: My Methodology Part 4

This is my last post in a series called Preventing a Disaster: My Methodology. In Part 1 I discussed the importance of running DBCC CHECKDB on your databases and provided tips on how to do this in VLDB and very busy systems.  In Part 2: Backups, I discussed the importance of a DBA knowing the RPO/RTO (Recovery Point Objective/Recovery Time Objective) of the business.  It is the RPO/RTO of a company that should determine your backup policies and procedures.  The 3rd installments discussed Off-site Locations and the fact that your backup strategy is not complete until you have a copy of your backups off the physical site.

In this installment I would like to discuss my last point in Preventing a Disaster and that is to ask yourself, “are my backups valid?”  Earlier, in post 2, the concept of “verifying” your databases was introduced and it should be a part of your backup process.  This basically verifies that what was written to the disk is equal to what is in the database.

To properly validate your backups, a DBA must perform a RESTORE of that backup to ensure that 1) it can be done, 2) that the RESTORE process works and 3) to validate the backup process.

This can be done to a development box, your desktop, a VM server that you can blow away later, it really doesn’t matter.  The point is to restore the database to a SQL Server. Typically, after a restore is complete you should run DBCC CHECKDB on the database to validate the integrity of the database.

To properly test your entire backup procedures, a DBA should get a backup file or files from archive, tape or off-site: i.e from the final resting place and restore that backup.  Restoring last nights backups is only half the process.

I once worked in a shop where a SQL server backed up to a network share where the 3rd party file backup solution then archived it tape.  Their policy was to keep 1 month’s worth of tape.  So just for kicks, I asked to get access to a 29 day old backup file to test restores.  Unfortunately, the Backup Administrator did not know how to retrieve a file from tape and place it on a network share. He was competent in getting all the necessary files to write to tape; but was unsure how to retrieve data (in his defense, he was new and was never asked to do a restore from tape).  The “backup procedures” as a whole was broken.

In Summary

In Preventing Disasters: My Methodology, I hope I explained what DBAs should do and why it is important to not skip a step. Be aware of who else is involved in the process and work closely with them to execute and test the process.

Preventing a Disaster: My Methodology–Part 3

Welcome back to part 3 of my 4 part series on Preventing a Disaster. In Part 1, I discussed the importance of Database Integrity checks and possibly ways to run them against VLDB (very large databases).  The second article was an explanation on Backups in general.

In this entry, I would like to discuss the concept of “Off-Site Locations”.

It is my belief that to truly say you have a effective disaster recovery plan, your plan needs to include how to get the SQL backups off-site.

There are a couple of options that I prefer and then there are less desirable options that are still technically effective.

Option 1 – The Cloudcloud

Several Cloud vendors proved storage containers, i.e. hard drive space.  Of course MS SQL works best with MS Azure.  SQL Server 2012 SP1-CU2 and SQL Server 2014 provided the ability to backup directly to an Azure storage container.  This pretty much combines steps 2 and 3 into one efficient step.  In April of 2014 Microsoft provided a secondary tool that allows previous versions of SQL to backup directly to Azure as well.

However, there are some downsides in my opinion.  Your server obviously needs an internet connection to the outside world and you have to have purchased an Azure account with the appropriate storage size. And as that storage blob grow, so does your monthly bill.

The benefits of using Azure storage include: compression, encryption and seamless integration into SQL Server.

Option 2 – SAN Replication

One option that I have seen to be successful is what I am calling SAN Replication. If your company has a backup datacenter in a different location, then chances are you have a SAN storage array their. 

In this configuration, you would use native SQL compressed backups to a local SAN, other than your data SAN of course!  Then that SAN is replicated to your secondary Data Center SAN, either using SAN snapshot or true block-by-block replication.

This method can be very effective in getting your data off-side.  This method may take a little longer however it usually is effective. The major downside to this is cost.  The cost of running a 2nd datacenter and a 2nd comparable SAN is enormous.  That is one of the reasons why cloud storage is becoming a more viable option as time goes by.filecluster

Option 3 – Personal Relocation

This is personally my favorite! (just kidding)  In this method you would use native SQL backup with compression targeted to an external drive.  And then at the end of the day personally take that external drive off-site.  External drive data recovery

Now, I am sure some of you are laughing at this method but with the cost of consumer hard drive rapidly decreasing this is a very viable option for some smaller companies.  I actually knew a company that did this every Friday and the IT manager relocated the USB external drive to a bank vault.  The company purchased 5 drives large enough to hold a weeks worth of backup files and rotated them out weekly.  This method allowed them to keep 30 days worth of backups at all times off-site.

This method is probably the cheapest; however not necessarily the safest. 


I am sure there are many other scenarios that are effective to getting SQL backups off-site, these are the ones that I have seen work in the real world successfully.

The important thing to remember and what to take away from this post, is to get your backups off-site.  In the event of your primary datacenter crashing, you need to be able to get your data restored ASAP. And if your most recent backup is on a server/SAN in that datacenter, your recovery time has just been exponentially increased.

Your backup plan is not complete until your backups are off-site!

Indexes and Execution Plans (my presentation)

Last night I had the opportunity to once again speak at our local Baton Rouge SQL User Group meeting.  And as usual, it was a blast. 

The topic of choice was “Indexes and Execution Plans: Using them together for the better!” This was somewhat of a 101 class designed to show how you can read and use Execution Plans to build your indexing strategies.

An introduction into basic terms with minimal slides and then I was on to demos. My demos were all based on a Phone Book database that I created with a White Pages table and a Yellow Pages table.  I populated the data using this nifty website Mokcaroo.  One bit of new knowledge I learned during the process.  The tSQL phrase of INSERT INTO ….VALUES… has a 1000 row limit.  Who knew?

I believe meaningful discussion was had by all and without hesitation the “peanut gallery” were in top form!  Enjoyable to say the least!

My last point of the discussion was “how to determine if the indexes were useful”. I demonstrated the code I use to list all indexes and their usefulness.  My code to do this can be found in my previous blog post in the 8 Weeks of Indexes series: “Determining what you have now.”

During my presentation, I mentioned one of my favorite books on Execution Plans by Grant Fritchey (b|t) called SQL Server Execution Plans, Second Edition and ITS FREE so there is no excuse not to get it and read it!

Since my slide deck was very minimal because the focus was on the indexing demos, I really don’t see the point of posting it; but I will include the demo scripts just in case.


Swimming through the Sea of SQL

SQL Padre

Just another WordPress site

SQL Studies

 Live, Learn, Share

Aunt Kathi's SQL Server Home

Learn SQL Server from your favorite aunt!

Jen's Blog: We Owls

...stuff. Also, things.

Journey to SQL Authority with Pinal Dave

SQL, SQL Server, MySQL, Big Data and NoSQL

Voice of the DBA

Writings from Steve Jones, the Voice of the DBA

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance


Get every new post delivered to your Inbox.

Join 140 other followers