Hello geeks,
Welcome to the new series of seven wonders of SQL Server. These are not the wonders which make you say Awww. These are the ones which make you really wonder why. I will be covering the seven of such jaw dropping situations where you wonder what is happening and few bizarre reasons why it is the way it is.
You get a bunch of servers (a new client with 200+ servers) to manage. The first request even before you got the whole list of servers is to restore a database to the test server. You check the backup history and run the script I blogged about to get backup history with shortest restore sequence. Interestingly, you do not see the history of one of the databases requested to restore. As a DBA on the first day, you do not want to raise the concern. You have observed the location is same for all backups. You browse to the folders and found the required backups to restore from last weekend. You have restored and end of request.
The film is not yet over my friend. So to understand why it was not shown in history, you started to check how the backups are taken. You find the backups and other maintenance tasks are setup using maintenance plans.
The backups are setup by selecting all databases in checkbox. So any new database added to the server will not be part of the backup plan by default. The first shock.
The cleanup task cleans up the backup files in 7 days. Also the backup is set to expire in 7 days. These drives are backed up to windows backups. Unfortunately, there is no point as they are set to expire after 7 days. The second shock.
The backup history cleanup task is set to cleanup the backup history older than 4 days. No wonder why my script did not pickup the database. The third shock.
To add to all the above, there is one additional task to run database integrity check (DBCC), index rebuild, database shrink and update statistics with no filters. Even though this is set to run once a week on this server, it was identified that this step was set to run every night in few servers. The fourth shock.
All these maintenance plans are manually setup on all servers. You wish you were a hourly billed consultant for this kind of job.
So the first task at hand, change all maintenance plans to follow best practices or scrap all maintenance plans and come up with a more efficient and easily manageable solution. So, for obvious reasons, let’s start with the second one.
First of all, build a central server for managing all instances if not already setup. Build an inventory with all server details. You can use Microsoft Assessment and Mapping Toolkit for SQL Server to build the inventory. This will make it easier to build your meta data for building job scripts. I prefer using Ola Hellengren’s maintenance scripts to do my job. I built a table to hold all parameters for each server for backup and index maintenance. Using the server list, I populate the parameters and generate the job scripts for all servers. I will also make sure it has a servername check like following before each job script.
IF (@@SERVERNAME = '<servername>') BEGIN <job script> END
This will ensure single job script file that can be run on all servers but only the jobs related to that particular server will be deployed on the respective servers. Along with this you also need to deploy few other components for these jobs to run. For this mass deployment, I built a simple .NET application which accepts a bunch of parameters and either runs a script file or gets result of a query from multiple servers. More information on Ola’s maintenance scripts can be found here.
It is your choice to go with this solution or any other automated centralized solution to deal with such situations. The first and foremost task at hand is always to identify the WONDER which caused the problem.
All the best with identifying such wonders. Please leave a comment if you had/have similar experience. I will come back with the second wonder in the next blog.
Happy Learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
