Database Professional

I have been working with a client recently who purchased a nice top of the line de-duplication backup device in order to synchronize backups between two data centers. This device is very nice in how it will de-duplicate data to reduce storage needs and also compress the data. It will then synchronize the data to a secondary device hundreds of miles away.

These types of devices are not cheap and work very efficiently. My client got their SQL Server environment migrated to the new backup appliance and found a nice vulnerability within the device. Their previous backup solution maintained the retention policies whereas this new device did not. After using the new solution for a few months they notice performance decreasing almost to the point of moving back to their old solution.

After a couple of days of research into the issue it was ruled that the root cause was having too many files in the backup directory. Since several of the servers contained databases with low RPO’s, they were making lots of transaction log backups. Several of the tlog backup folders contained 10′s of thousands of backup files all with similar names.

Once the tlog folders were purged the system returned to a normal operational level. It was explained to the client by the vendor that they shouldn’t have more than 1000 files per folder and no more than 1000 subfolders.

The first lesson is that anytime you fundamentally change a solution of a critical process, you need to have a process in place to account for every aspect of the previous solution. Had this client built a proper purge process, they would not have brought a top of the line backup device to its knees.

The second lesson is that if you are making frequent transaction log backups for a system with multiple databases on it, you should consider writing the transaction logs to individual sub folders. The best practice would be to have a tlog folder with a sub folder for each database.  Having this in place will limit the number of files per directory.

I recieved an email today from my publisher Joes 2 Pros that my book was now available on Barnes and Noble’s website in addition to Amazon in print and Kindle. This is very exciting.

In just three months SQL Backup and Recovery has sold nearly 500 copies. I am still in awe that my name is on the cover of a book and I really hope this is the beginning of many more to come.

It is such an educational process to write a book.

Many of us know the importance of scripting out our users on a regular bases in order to be able to before DR restores more efficiently.  If you are responsible for performing backup and restores or disaster recovery solutions and you do not have a routine to regularly script out your users and permissions, then you should implement one soon.

A common problem when DBA’s have to restore production user databases to another server is that none of their users exist on the new server. Depending on the level of disaster, you may not have access to a working master database on the primary server. You would then find yourself having to manually create the users or add AD users to the new server, or have to restore the master database from the old server and script the users out.

You don’t want to be in that type of situation. Microsoft provides us a nice sp_help_revlogin script that will easily script out your users. The issue with this script is that it does not include the users permissions. Luckily this is not a brand new issue and my friend Kendal Van Dyke (blog)has published a great article with the scripts on how to get your users permissions and roles. Check that out here.

A common problem that you may face if you are testing out the user create scripts is that if you try to run the script to create users on a server that does not support the complexity requirements or has a more strict password requirement, the script may fail.  You may get an error message similar to “Invalid value given for parameter PASSWORD”.

A good practice would be to implement a process to run both of the scripts above to output the values to a file that you backup each night with your databases. There are plenty of documentation available on the web to help you get the output into text files. I use sqlcmd and bcp to get the output into the format that I need. I have seen others that just create a two step job and use the “Output file:” option to save the results to a file. I got more complex so that the files I create are ready to execute as part of my DR policy without requiring any modification to the results.

I hope you found this helpful and that you have a solid recovery plan that you rehearse on a regular basis. Something you should try is to get someone completely unfamiliar with SQL Server and have them execute your recovery plan. If they can not follow it completely and recover your system, update your recovery plan. Who is to say that when disaster strikes, that you are available with your institutional knowledge to bring the system up. Who is to say that your priority will be your company if disaster strikes. With the recent events in New York and New Jersey with hurricane Sandy, where would your priority reside? Trying to fail over your company to another datacenter or finding food and shelter for your wife/husband and kids? If you spent the time and have a fool proof recovery plan that has been executed and tested multiple times, you could be rest assured that the support staff in that remote datacenter could execute your recovery plan to bring your company back online. Do yourself the favor, plan for the failure and test the plan.

I was very fortunate to have the opportunity to write a book for Joes2Pros on a topic that is very important to me. As database professionals, few things are more important than being able to properly backup and recover our data.

I had put together a presentation for user groups and SQL Saturday’s titled “Know Backups, Know Recovery”. It was during my presentation in New York City at SQL Saturday 158 that Rick Morelan heard me speak and told me he thought my session would make a great book. Over the next few weeks we shared a few emails and on September 14th we officially began the work. Several weeks later on October 31st, my book “SQL Backup and Recovery” went live on Amazon for the low price of $9.99.

I am very excited to have finally written a book, but more importantly to be able to share my experiences and help others through their journey to becoming a database professional. I wouldn’t be where I am today without the support of my family, that includes my SQL family as well. You all have been amazing to me and I love being able to pay it forward. So continue your support and go out and buy my book.

I will be having a book signing Wednesday night at the Idera Happy Hour at Fox Sports Grill and another book signing on Friday at the Joes2Pros booth during the second hour of lunch. If you are going to be at the PASS Summit, come see me.