Monthly Archives: April 2014

DBA Morning Checklist with PBM (Part 3)

A few months ago, I blogged about how I did my morning checklist by using Policy Based Management together with a Central Management Server. This method works really well for me and I’ve implemented it at all my customers for the moment. When the months passed by, I’ve added extra checks to my list and created the necessary policies for it. This blog post gives a short description how my full morning DBA checklist looks like.

All jobs succeeded

Are there any jobs that have failed the last 7 days? I’m using 7 days here because I visit my customers mostly on a weekly bases. You can of course change that period in the condition that is checked in the policy.

Check SQL Server services are running

This policy will check if all SQL Server services are still running. Sounds ridiculous but it happened to me a few times that the SQL Server Agent was stopped without anybody noticing. This check only works from SQL Server 2008 R2 SP1 onwards because I’m using the DMV sys.dm_server_services, which is available since that version.

Data and Log files on separate volumes

For best performance, your data and log files should be stored on separate volumes. In the condition, I’m just checking the @DataAndLogFilesOnSeparateLogicalVolumes parameter in the Database Performance facet.

Data File Growth in MB

The autogrowth setting of your data file should be set MB instead of %. I’m also verifying if the growth setting is at least 128 MB…

Database Auto Close

You should never set the auto close parameter to True on your database. This option will cause performance degradation because of the overhead of opening and closing the database after each connection. The policy will check if there is any database on the server that has auto close set to True.

Database Auto Create Statistics

This policy will check if the Auto Create Statistics database setting is set to True. In some cases, like on some SharePoint databases, you have to ignore this check because the statistics are handled by SharePoint.

Database Auto Shrink

This rule checks whether the auto shrink database option is set to False. This setting should never be set to True because it will lead to physical fragmentation.

Database Auto Update Statistics

This policy will check if the Auto Update Statistics database setting is set to True. In some cases, like on some SharePoint databases, you have to ignore this check because the statistics are handled by SharePoint.

Database Collation

Is the database collation the same as the collation of my master database? The condition that is checked is the @CollationMatchesModelOrMaster parameter in de Database Performance facet.

Database Compatibility Level

Database compatibility level should be equal to the server level. The condition is going to check the @CompatibilityLevel database parameter against the collation of the master database that I retrieve by querying the sys.databases.

Database DBCC CHECKDB last 7 days

Is a DBCC CHECKDB performed the last week on the databases? I’m choosing 7 days here because this is how I schedule the DBCC command at my customers. The condition is checked based on a T-SQL query that is using the DBCC DBINFO() information.

Database is in Normal State

The status of the database should be in a normal state. This policy will indicate a failure on offline, suspect, … status of the database

Database owner

This policy will check if the owner of my database is set to sa.

Database page verify

Database option Page Verify should be set to CHECKSUM.

Free disk space

This policy is going to check if there is any LUN on the server that has less than 10% free disk space. The check itself is done by a powershell command or if powershell is not installed on the server by the fsutil command. The script also supports mount points.

Full or Differential Backup

Is there a full backup taken of my database during the last 24 hours? Or… Is there a differential backup taken of my database during the last 24 hours and a full backup during the last 7 days. Some of the database are too big to take a full backup every day. This check is going to verify is there is a differential backup. I’ve planned a full backup every week, next to the differential backups. You can change the condition of course, according to you backup strategy.

Jobs owned by sa

Are my jobs owned by the sa user? In the condition, I’ve filtered on the category Jobs from MSX, Database Maintenance, Full-Text and Uncategorized. There is no server policy created for this, so I’m using a T-sql script based on the sysjobs table.

Log File Growth in MB

The autogrowth setting of your log file should be set MB instead of %. I’m also verifying if the growth setting is at least 128 MB and not 1 MB in some cases…

Recovery Model = FULL for Production Databases

The policy is going to check if the recovery model of production databases is set to full. By default, I’m always using a full recovery model for production databases unless the application owner tells me that simple should be fine. I had to find a way to specify if a database exists on a production server or not? I’ve used a simple trick by specifying an Extended Property “Environment” on the master database.

In the policy, I’ve specified a server restriction based on the extended property.

Severity Alerts

Were there any severity Alerts in the SQL Server Log with a value larger than 16 during the past 7 days? When your SQL Server Log file is really big, it might happen that this policy returns a timeout.

Transaction Log Backup Taken

Is there a transaction log backup taken of my database during the last 2 hours? Again, you can change this time period depending on the backup strategy that you’re using.

All these checks give a me nice overview how healthy my SQL environment is, in no time!

You can download all my policies here

In case you should have any issues or you want to share some extra policies, just let me know!

Pieter

Remove SQL Backup Files from your Azure Storage

Microsoft introduced a lot of new Azure features in SQL Server 2014, which has been released this week. It has become really easy to store your backup files on Windows Azure (or should I say Microsoft Azure J?).
You can check my previous blog post on how you can take a backup to Windows Azure. This all works really great and yesterday I also introduced this new feature to one of my customers and they wanted to go ahead with it! While I was driving home, I was thinking how I could implement this in a good way. My customer didn’t want to use Smart Backup, because they wanted to be in control of the backup process. Scheduling the backups with the SQL Server Agent looks like the appropriate solution here, just like you take backups to disk, but you choose to URL.

When you take backups to disk, you also have to schedule a cleanup job for the old backup files. The same counts for the Azure storage, unless you want to pay a lot of money for your storage… J
But how can I clean up the backups from my Azure Storage container? I decided to use PowerShell to clean up the backup files.

First of all you have to install the Azure PowerShell cmdlets. More information can be found here. Once the cmdlets are installed, don’t forget to connect to your subscription as mentioned. Execute the Get-AzureSubscription Command in the Windows PowerShell ISE.

Login to your Azure portal and copy the access key of your storage account because you will need it in the script where it says “Paste your storage access key here“.

In my script, I’m using the Get-AzureStorageBlob to get the blobs out of my backup container. I’m going to check the LastModified date for each blob with extension *.bak and *.log. If it’s less than my cleanup time (3 days for full backups and 1 day for transaction log backups) I’m removing the backup file with the Remove-AzureStorageBlob cmdlet.

$CleanupTime = [DateTime]::UtcNow.AddHours(-72)
$context = New-AzureStorageContext -StorageAccountName koherastorage -StorageAccountKey {Paste your storage access key here}
Get-AzureStorageBlob -Container "sqlbackup" -Context $context | 
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.bak"} |
Remove-AzureStorageBlob
$CleanupTime = [DateTime]::UtcNow.AddHours(-24)
Get-AzureStorageBlob -Container "sqlbackup" -Context $context | 
Where-Object { $_.LastModified.UtcDateTime -lt $CleanupTime -and $_.BlobType -eq "PageBlob" -and $_.Name -like "*.log"} |
Remove-AzureStorageBlob

 You can easily schedule it this with a SQL Server Agent Job. Just choose Powershell in the job step.

 I know this script can be fine-tuned and any suggestion is appreciated.

 Pieter