ourSSD 2 - Are My Databases Being Backed Up?

Checking Backups

In ourSSD part one we covered checking that our SQL nodes were up, now we’ll move on to checking that our backups are working. I used to do this with a painful DTS and then SSIS package which would go to each SQL Instance and pull in all the backup information. Initial setup was extremely time consuming and the maintenance of the package with adding and removing SQL Instances got to be a serious pain.
Powershell to the rescue once more. A single command will give us the backup information for all the user databases on a SQL Instance (this does not provide information on the system databases) (using the -force switch we can pull back all databases, including system).

 dir SQLSERVER:\SQL\MYSQLSERVER\SQLINSTANCE\Databases | SELECT Name, LastBackupDate, LastDifferentialBackupDate

Name                              LastBackupDate            LastDifferentialBackupDate
---------------------------    --------------------         ---------------------------------
AdventureWorks               1/31/2010 1:40:12PM   1/1/0001 12:00:00AM
AdventureWorks2008        1/20/2010 10:23:03AM 1/31/2010 1:40:04PM
AdventureWorksDW2008   1/1/0001 12:00:00AM   1/1/0001 12:00:00AM

A couple of things to note here, instead of using a null value when there’s no record, a date of 1/1/0001 12:00:00AM is used. This date value is not usable within SQL so when we bring in the data we need to make it a date that we can actually work with. We’ll do that as a part of the larger Powershell script. If you look at the records returned above you’ll see that the AdventureWorks database was backed up 1/31/2010 1:40:12PM, and has no differential backup. The AdventureWorks2008 database has a full backup from 1/20/2010 and a differential 1/31/2010. The AdventureWorksDW2008 database has never been backed up.

Setting Up The SQL

Now that we have the very basics to pull the information that we want let’s get creative. To start things off we’ll create a table to hold a list of the SQL Instances that we are going to want to monitor. We’ll include a couple of configuration columns that we will be using within our monitoring routines.

CREATE TABLE SQLMonitor.dbo.SQLInstances
    (
      ServerName VARCHAR(20)
    , Instance VARCHAR(30)
    , CheckBackup BIT DEFAULT 1
    , CheckBackupDate DATETIME
    , CheckDBSpace BIT
    , CheckDBSpaceDate DATETIME
    , CheckDiskSpace BIT
    , CheckDiskSpaceDate DATETIME
    )

Insert a couple of rows for SQL Instances that we’ll be checking the backup stats on

INSERT INTO SQLMonitor.dbo.SQLInstances (ServerName, Instance, CheckBackup, CheckDBSpace, CheckDiskSpace)
VALUES ('PROD1', NULL, 1,1,1)
, ('PROD2', 'ODS', 1,1,1)

Finally, for the DB side of things, add a table to hold the results of the database backup checks.
Note: we are going to use a computed column to store the difference between the last backup and the check time as well as store the last full and last differential backup times, with the lastbackupdate being the most recent of those two

CREATE TABLE SQLMonitor.dbo.DatabaseBackups
    (
      ServerName VARCHAR(50)
    , DatabaseName VARCHAR(128)
    , ExtendedProperties NVARCHAR(MAX)
    , LastFullBackupDate DATETIME
    , LastDifferentialBackupDate DATETIME
    , LastBackupDate DATETIME
    , RecordDate DATETIME DEFAULT GETDATE()
    , BackupDelta AS (DATEDIFF(DAY,[LastBackupDate],[RecordDate]))
    )    

Going Through The Powershell

Now we just need the powershell script to go and pull the data in and load the table.
We use Invoke-SqlCmd to go and get our recordset from the SQLInstances table and throw that into the $DSServername dataset.

$DSServernames= Invoke-SqlCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "select ServerName,isnull(Instance, 'NULL') as Instance from SQLInstances where CheckBackup = 1"

We need to work with the dataset a little here to ensure that we get the correct path to the databases. The dir command follows the path SQLSERVER:\SQL\<servername>\<instance>. When you aren’t querying a named instance then you use Default in place of the instance name. For the two records that we added to the table we would need to query paths SQLSERVER:\SQL\PROD1\Default and SQLSERVER:\SQL\PROD2\ODS.

To get the correct path we use a string match and set a variable for the location depending on whether it’s a named or default instance

foreach ($Servername in $DSServernames)
 {
  #for the query we use \Default if not a named instance but want to keep the original SQL instance info for table insertion
  IF($Servername.Instance -eq "NULL")
    {
     $location = $Servername.ServerName+"\Default"
         $srv = $Servername.ServerName
    }
  else
    {
      $location = $Servername.ServerName+"\"+$Servername.Instance
      $srv = $location
    }

The $location variable now contains the our SQL path and we just plug that into the dir command and build ourselves a new dataset from there.

$backupstatus = dir -force SQLSERVER:\SQL\$location\Databases | SELECT Name, ExtendedProperties, LastBackupDate, LastDifferentialBackupDate

Note: I’m also pull in ExtendedProperties here. This is useful for me as in my production environment I don’t backup replicated data, this saves me a great deal of space (all procs/views that use that data are housed in other databases). My backup procedures check for a rep_copy value to know whether to perform that backup or not. I don’t want my report to include a list of databases that I don’t expect to be backed up so I’ll pull in this information and evaluate it in my queries later.

We want to be sure that we are getting the information for all the servers that we query. To do this we update the SQLInstances table with the current datetime once we’ve pulled the dataset. We can check this within our reports to be sure that there were no issues with a datapull and thus potentially miss information.

#if we were able to build a recordset update the timestamp in the config table to show we captured data
if($?)
 {
   Invoke-SQLCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "UPDATE SQLInstances SET CheckBackupDate = getdate() WHERE ServerName = '$updsrv' AND (Instance = '$updinst' OR Instance IS NULL)"

For each record in the dataset we assign a variable

foreach ($BackupRecord in $backupstatus)
    {
     $db = $BackupRecord.Name
     $exp = $BackupRecord.ExtendedProperties
     $lbu = $BackupRecord.LastBackupDate
     $ldbu = $BackupRecord.LastDifferentialBackupDate

And we run a fix on the odd datetime value so that we can get the data into SQL

#In the event that there has never been a backup or differential backup for a database we need to set a real datetime value
#there is a default value of 01/01/0001 00:00:00 which is not a real datetime
IF ($lbu -eq "01/01/0001 00:00:00")    { $lbu = "01/01/1900 00:00:00"}
IF ($ldbu -eq "01/01/0001 00:00:00")    { $ldbu = "01/01/1900 00:00:00"}

Then we assign the $lb variable with the most recent backup datetime, be it full or differential

#if the last full backup is newer than the last differential use that, otherwise use the differential date
IF ($lbu -gt $ldbu)
 { $lb = $lbu }
Else
 { $lb = $ldbu }

Finally we insert the data into the table

#insert it into the table
Invoke-SQLCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "INSERT INTO dbo.DatabaseBackups (ServerName, DatabaseName, ExtendedProperties, LastFullBackupDate, LastDifferentialBackupDate, LastBackupDate) VALUES ('$srv', '$db', '$exp', '$lbu', '$ldbu', '$lb')"

The Full Powershell Script

#CHECKING BACKUPS
$ErrorActionPreference = "SilentlyContinue"
Add-PSSnapin SqlServerCmdletSnapin100
#Clear out the old records
Invoke-SQLCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "TRUNCATE TABLE dbo.DatabaseBackups"
#Get a list of SQL Instances from the table and dump them into a dataset that we can work with
$DSServernames= Invoke-SqlCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "select ServerName,isnull(Instance, 'NULL') as Instance from SQLInstances where CheckBackup = 1"


#Now let's work with each record returned and check things out
foreach ($Servername in $DSServernames)
 {
#for the query we use \Default if not a named instance but want to keep the original SQL instance info for table insertion
 IF($Servername.Instance -eq "NULL")
  {
   $location = $Servername.ServerName+"\Default"
   $srv = $Servername.ServerName
  }
 else
  {
   $location = $Servername.ServerName+"\"+$Servername.Instance
   $srv = $location
  }
$updinst = $Servername.Instance
$updsrv = $Servername.ServerName

#this is where we actually get the data (using the -force switch to also include system databases)
$backupstatus = dir -force SQLSERVER:\SQL\$location\Databases | SELECT Name, ExtendedProperties, LastBackupDate, LastDifferentialBackupDate
        
#if we were able to build a recordset update the timestamp in the config table to show we captured data
 if($?)
  {
   Write-Host "$updsrv $updinst"
   Invoke-SQLCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "UPDATE SQLInstances SET CheckBackupDate = getdate() WHERE ServerName = '$updsrv' AND (Instance = '$updinst' OR Instance IS NULL)"
        
    
  foreach ($BackupRecord in $backupstatus)
   {
    $db = $BackupRecord.Name
    $exp = $BackupRecord.ExtendedProperties
    $lbu = $BackupRecord.LastBackupDate
    $ldbu = $BackupRecord.LastDifferentialBackupDate
                    
#In the event that there has never been a backup or differential backup for a database we need to set a real datetime value
#there is a default value of 01/01/0001 00:00:00 which is not a real datetime
 IF ($lbu -eq "01/01/0001 00:00:00")    { $lbu = "01/01/1900 00:00:00"}
 IF ($ldbu -eq "01/01/0001 00:00:00")    { $ldbu = "01/01/1900 00:00:00"}
                    
#if the last full backup is newer than the last differential use that, otherwise use the differential date
 IF ($lbu -gt $ldbu)
  { $lb = $lbu }
 else
  { $lb = $ldbu }

#uncomment the next line to view info on screen
#Write-Host "$srv, $db, $exp, $lbu, $ldbu, $lb"
                    
#insert it into the table
Invoke-SQLCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "INSERT INTO dbo.DatabaseBackups (ServerName, DatabaseName, ExtendedProperties, LastFullBackupDate, LastDifferentialBackupDate, LastBackupDate) VALUES ('$srv', '$db', '$exp', '$lbu', '$ldbu', '$lb')"
                    
  }
 }
}   

Now if you query the DatabaseBackups table you’ll see a list of SQL Instances, databases, their last full and differential dates along with when that info was last checked, the most recent backup (be it full or differential) and the delta (in days) between that last backup and when we performed the check.
In the SQLInstances table you will see an updated CheckBackupDate for any server marked that was successfully checked.

ourSSD 1 - Is My Node Up?

You know that you are going to hear about it if your SQL Server is down. In fact I'm sure you have all kinds of bells and whistles that will grab your attention the second it happens, pre-empting the user community that will come screaming in your direction like a hoard of banshees being serenaded by bagpipers from hell. What if SQL isn't down though? Let's say that you are running in an active/passive cluster configuration, you think everything is great, you are covered. What happens when your active node takes a dive and it starts to failover like the good little cluster group it is, only to find that the other node is not able to take over control of the cluster because it's not up or not responding correctly? Yeah, you have a problem.          

This scenario is what brought about the first item I'll share for the monitoring with ourSSD. Very simply, are the physical nodes that make up my SQL Server infrastructure up and functional? (and by that I don't mean just pingable)          

How to do this, and do it for free? Not being a developer, knowing no languages I struggled, then, just as the night seemed darkest came a bright shiny Powershell light. This thing is awesome, in fact it was so good that it deserved bold type (it was || close to caps!). The things that you can do with it and the level of simplicity it can bring with the vast number of things that it can do make it an invaluable tool.
    

I'm not going to wax lyrical about Powershell integration with SQL nor give you the full skinny and rundown, there are people and resources way better than I at doing that sort of thing, take these links for example          

• Josh Jones - http://www.sqlservercentral.com/blogs/joshjones/


• Chad Miller - http://www.sqlservercentral.com/articles/powershell/65324/


• Darin Strait - http://blog.ashdar-partners.com/2008/06/powershell-function-invoke-sqlquery-and.html


• Twitter  - #Powershell tag and @ScriptingGuys

Here's what you get from me....          

So what's the deal, is my node up? Pinging will tell us that it's on the network, that can be misleading however. So how about we do something with the server and check the result? This is where WMI and Powershell hold hands and make smiley faces to give us something useful.          

Kick off the SQL Powershell Provider sqlps and let's check to see if you're up. In the Powershell window enter          

Get-WmiObject -class Win32_OperatingSystem -computername .          

Check it out, some data about your local computer. You should get your system directory, organization, Windows build number and a couple more things. If you want a slightly more crazy list use the following          

Get-WmiObject -class Win32_OperatingSystem -computername . | SELECT *           

Ok, that's a crazy amount of stuff. Thing is, we know that the computer is up (well of course we do, that's where we ran the script). What about another machine, well provided you have the requisite permissions then you can change the period for another computer on the network and get the results from it          

Get-WmiObject -class Win32_OperatingSystem -computername YUKON1 | SELECT *          

Hey, what's the deal? I just got an error saying that the RPC Server is unavailable. That's because I don't have a YUKON1 OR because YUKON1 cannot respond to my WMI query. Let's say that YUKON1 does exist, we don't need all that data to know that it's up, just having a result is good enough. Heck, we want to find out the status so let's use that.          

Get-WmiObject -class Win32_OperatingSystem -computername YUKON1 | SELECT Status
Status
-------
OK    

Sweet, it's up and running. This is very simple, very basic and all that we need. Now, how to turn that one little item into something we can use for more than one server at a time.          

One of the most challenging things I found when first starting out was figuring out how to get and work with a recordset. I didn't want to have to type in stuff constantly, nor did I want to read in things from text files. I'm a DBA afer all, the data should be in the database. This is where Invoke-SqlCmd comes in. First, login to a SQL Server, create a database to hold some of the record stuff (I'm using AdminDB and will throughout all the examples and we'll add a table for the check.          

Use SQLMonitor
GO
CREATE TABLE dbo.PhysicalNodeStatus
 (
  NodeName varchar(25),
  NodeStatus varchar(4),
  CheckTime datetime default getdate()
 )
GO
CREATE TABLE dbo.PhysicalNodes
 (
  NodeName varchar(25)
 )
GO    

Insert into the dbo.PhysicalNodes table some data for a couple of machines (include one or two that might be down or not exist for control purposes). Crank up your favourite text editor and let's work with some data.          

#Get a recordset of NodeNames from the PhysicalNodes table so that we can work with them
$DSNodes = Invoke-SqlCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "SELECT NodeName from PhysicalNodes ORDER BY NodeName"
#Run the WMI Query for each of the records returned
foreach ($dbn in $DSNodes)
 {
  $nodename = $dbn.NodeName
  $goodbad = Get-WmiObject -class Win32_OperatingSystem -computername $nodename | SELECT Status
  $good = $goodbad.Status
  Write-Host "$nodename $good"
 }    

If you save and run this you should get a nice returned list of servers along with OK if they were good. Of course, if they are not good, for whatever reason, you get the big red ugly error syntax. We generally don't want to see this and so at the very top of the script add $ErrorActionPreference = "SilentlyContinue"
This is good so far, how about something a little more visually interesting and useful. What you can do is use the value of Status in the $good variable and work with that to return something different depending on whether we got an ok result or not.          

$ErrorActionPreference = "SilentlyContinue"


#Get a recordset of NodeNames from the PhysicalNodes table so that we can work with them
$DSNodes = Invoke-SqlCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "SELECT NodeName from PhysicalNodes ORDER BY NodeName"


#Run the WMI Query for each of the records returned

foreach ($dbn in $DSNodes)
 {
  $nodename = $dbn.NodeName
  $goodbad = Get-WmiObject -class Win32_OperatingSystem -computerName $nodename | SELECT Status
  $good = $goodbad.Status

 

 if ($good -eq "OK")
  {
   Write-Host -BackGroundColor Green -ForeGroundColor Black "$nodename good"
  }
 Else
  {
   Write-Host -BackGroundColor Red "$nodename bad"
  }
 }

Run this and now we have a nice list of the nodes and whether the node is good or bad plus some highlighting. Very useful, and great if you want to run manually. The final step though, let's get that information back into the database where we can work with it and do other things like create reports or send emails. Once more Invoke-SqlCmd comes to the rescue. I struggled for an age with using SqlConnection to do this, and was able to get it to work, this is much easier to work with however. For this we are simply going to perform an insert along with the data returned from the WMI query. We end up with a final script thusly...          

$ErrorActionPreference = "SilentlyContinue"

#Get a recordset of NodeNames from the PhysicalNodes table so that we can work with them
$DSNodes = Invoke-SqlCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "SELECT NodeName from PhysicalNodes ORDER BY NodeName"

#Run the WMI Query for each of the records returned
foreach ($dbn in $DSNodes)
 {
  $nodename = $dbn.NodeName
  $goodbad = Get-WmiObject -class Win32_OperatingSystem -computerName $nodename | SELECT Status
  $good = $goodbad.Status  

 If ($good -eq "OK")
  {
   Write-Host -BackGroundColor Green -ForeGroundColor Black "$nodename good"
   Invoke-SqlCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "INSERT INTO dbo.PhysicalNodeStatus (NodeName, NodeStatus) VALUES ('$nodename', 'up')"
  }
 Else
  {
   Write-Host -BackGroundColor Red "$nodename bad"
   Invoke-SqlCmd -ServerInstance ".\YUKON" -Database "SQLMonitor" -Query "INSERT INTO dbo.PhysicalNodeStatus (NodeName, NodeStatus) VALUES ('$nodename', 'down')"
  }
}  

Run this and check the PhysicalNodeStatus table, you should see the the node, it's status and the time you ran the process. Execute this as many times as you want and check the results are entered correctly for each run.          

Comment out the Write-Host lines, throw it in a Powershell Step within a SQL job, schedule and take a step back, check through the results. Boom. Done.

SQL Monitoring On The Cheap

Monitoring tools are costly, a pain to get running (especially within an enterprise) and they frequently don't provide me with what I want or how I want it. I'm not always looking for real-time troubleshooting and critical notification, sometimes I just want to be able to perform some simple tasks to keep an eye on my SQL Server Instances. Little things like regular checks on physical nodes, disk space, database space, etc... I want to be able to manage this simply and get a regular report that will show me anything out of the ordinary and keep some historical data so that I can go back and look for trends and such.

Born from this wish was, as my good friend and colleague Shawn coined, our SQL Server Dashboard *fanfare*

Is ourSSD (cool name huh) really a dashboard? Showing you would spoil the surprise and take away all of my fun so sorry, no screencaps just yet. I can tell you that ourSSD is fairly straightforward, easily adaptable, scalable and modular. In fact you can throw something new into it or rip something out of it any time that you want. You can set your own thresholds for this that and the other thing...blah blah blah...oh, and it didn't cost anything other than time. ourSSD provides me (and Shawn bless him) great little snapshots of the information that I am interested on a daily basis.

Just as a warning though, ourSSD has no pretty little gauges or flashing lights, there isn't a super exotic code base or anything. Heck! You are not even going to find a nicely formatted SSRS report (although there easily could be). What you will find are a couple of packages, some scripts and a plain boring html email sent out a few times a day that contains everything you want to know and are afraid to ask.

I'll be putting a series of posts out with the things I've got hooked into ourSSD already, how they work, what they use and why I use them. Suggestions for other items would be greatly appreciated, and if you can tell me how to do them, even better .