Automating SQL Backup verification testing with PowerShell

Introduction
Talk to any person responsible for a database, no matter what platform, and ask them what is their most important responsibility, I would hope that all of them would list backups at the top of their list. Managing these backups is a well documented and tested process. There are plenty of tools, native and 3rd party, for managing backups: Keeping track of missed backups, last backups, backup size, backup duration, backup type… the list goes on and on. However, a backup strategy is pretty much useless if it isn’t tested. Simply relying on a ‘backup completed’ check is setting yourself up for disaster.

This process is less popular for vendors and DBAs alike. The requirements for testing, the availability of platforms for testing, the company requirements for testing all differ from platform to platform and company to company.

In this article I will present a series of PowerShell functions that work with SMO objects to automatically manage this process in my environment. With a few small changes, the scripts can be tweaked to fit pretty much any scenario that manages more than 1 or 2 instances.

The Scenario
My requirement was driven by quarterly SOX auditing that required proof that we completed regular verification our of SQL backups and then document that proof. In my current position, I am managing thousands databases spread across multiple instances. The priority of each system is entirely dependent on it’s function in the company, ERP systems take position #1, and the rest scale on down from there.

The source of the proof for the audit was previously coming from a number of different sources:
• ERP Refreshes – As part of regular business operations, the ERP systems were refreshed from dev to Quality , Production to Quality and D, Q or P to sandbox, depending on the requirement.
• Regular system refreshes – Moving databases between both vendor and proprietary in house environments, from prod to dev and back.
• Recovery – Emergency tickets to restore production databases to test environments to recover accidentally deleted data.
• DBA Backup testing – A Manual process to validate a random database or 2 on a regular interval (in my case monthly).

When any of these events came to pass, it was taken as an opportunity to collect the proof of restore and save it into the restore log. The first 3 were usually not predictable, as they relied solely on business requirements. However, as a lone DBA doing a monthly manual restore of random databases, I felt that this was not something that I should be spending any more than a couple of minutes \ month, and my recent love affair with PowerShell prompted me to take the plunge and write the code necessary to automate and (mostly) forget about this job.

The actual process is a fairly simple one:
1. Select an random Instance.
2. Select a random Database.
3. Select a Backup.
4. Attempt a restore.
5. Collect data from restored database as proof.
6. Delete database.
7. Email results.

There were, however, a couple of caveats as to what I could realistically accomplish within our environment.

Although most of our instances sit in our data center, we have numerous instances hosted in sites around the world, but these generally back up to local disk, so on-disk retention was usually only a couple of days, so I was limited to testing backups on instances in our data center. The databases in the DC are backed up using native SQL tools to an EMC Data Domain via a network share. The on disk retention for these was 30 days, so I was going to be unable to test any backups older than 30 days, without requesting a restore from our backup team. This met our SOX requirements though, as all important systems are in the central DC, and as long as the backup was being tested, auditors would be happy.

I have a dedicated vm that I use for my monitoring, so this was where I was going to be restoring to. I only had 50GB of useable space to do my restores to though, so I was only going to be able to concentrate on databases > 50GB.

The Code

Logging
Troubleshooting PowerShell, or any scripting language for that matter, isn’t always straight forward, so for any complex piece of code, I usually throw in a logging function, to output steps processed with and relevant variables:

Function Write-DebugLog
{
Param([String]$Message, [String]$Path)
try
{
$date = Get-Date -Format “yyyy-MM-dd HH:mm:ss”
$outFile = $Path
$msg = $date + ": " + $Message
$msg | Out-File -FilePath $outFile -Append
}
catch
{
write-output "Exception Type: $($_.Exception.GetType().FullName)"
write-output "Exception Message: $($_.Exception.Message)"
}
}

Instance Selection
I manage all of my instances by storing relevant data in a home grown database that contains a list of all servers, instances, databases, data files etc. In my script I am using this database as the selection tool for finding a random instance to test against. Your code can easily be amended to use a txt file that contains all of your servers, or your own inventory database:

Function Get-RestoreSourceServer
{
param([object]$instance, [String]$LogFile);
try
{
$sourceInstance = $null
#select random server
while(!($sourceInstance))
{
$sourceInstanceName = $instance.ConnectionContext.ExecuteScalar("select top 1 instance_name from MONITOR.dbo.instance where `
instance_ip like '%1.1.%' order by newid()"); #the IP identifies all of data center servers that I am concerned about
#if this is a named instance, strip all chars post \
if ($sourceInstanceName.Contains("\"))
{
$computername = $sourceInstanceName.Replace($sourceInstanceName.SubString($sourceInstanceName.IndexOf("\")),"");
}
else
{
$computername = $sourceInstanceName
}
Write-DebugLog -Message "Attempting to connect to Instance on server ($computername)" -Path $LogFile
#check its availability, if available, lets try and get a database, with a valid backup
if(Test-Connection -ComputerName $computername -Count 1 -Quiet)
{
#connect to our instance
$sourceInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $sourceInstanceName
Write-DebugLog -Message "Connected successfully to Instance ($sourceInstanceName)" -Path $LogFile
}
else
{
$sourceInstance = $null
Write-DebugLog -Message "Unable to Connect to Instance on ($computername)" -Path $LogFile
}
}

#our 2 return values
$true
$sourceInstance
}
catch #output an exception, and return a false flag, and an empty object
{
Write-DebugLog -Message "Exception Message: $($_.Exception.Message) Exception Type: $($_.Exception.GetType().FullName)" -Path $LogFile
$false
$null
}
};

Database Selection
Once I have a valid Instance to work from, I will then select a database from the server, that is less than 50GB in size, by querying the sys.databases system table:

Function Get-RestoreSourceDatabase
{
param([object]$instance, [String]$LogFile);
try
{
Write-DebugLog -Message "Checking for Database on $instance" -Path $LogFile
#we have a valid instance, lets find a valid database that is 50GB in size, or less and not a system db
$database = $instance.Databases | Where-Object{($_.ID -gt 4) -and ($_.size -lt 50000)} | get-random -Count 1
Write-DebugLog -Message "$database found on Instance $instance" -Path $LogFile
#ouput our boolean value and the database
$true
$database
}
catch #output an exception, and return a false flag, and an empty object
{
Write-DebugLog -Message "Exception Message: $($_.Exception.Message) Exception Type: $($_.Exception.GetType().FullName)" -Path $LogFile
$false
$null
}
};

Backup Selection
After a database has successfully been chosen then we can check for a valid backup to restore from. The process here is to find a backup that is less than 30 days old, and is still on disk, and available to restore from. SMO let me down here, as I was hoping to be able to enumerate the backup sets for the database, and then create an SMO object for that backup to pull file data from, but was unable to find an instantiate a valid object, so I went to the msdb backup tables to find data (if anyone knows a way to get an SMO object that represents a backup, with file location, please let me know, and I will update the scripts.):

Function Get-RestoreBackupLocation
{
param([object]$Database, [String]$LogFile);
try
{
Write-DebugLog -Message "Checking for Valid Backup for $Database" -Path $LogFile
$backupDate = Get-Date;
$backupDate = $backupDate.AddDays(-30); #current retention period
#Find all full backups for the database less than 30 days old (current retention period for backups on disk)
$backupset = $Database | %{$_.EnumBackupSets()} | Where-Object{($_.BackupSetType -eq 1) -and ($_.BackupStartDate -gt $backupDate)} | get-random -Count 1
$backupsetID = $backupset.ID
$MediaSetId = $backupset.MediaSetId
Write-DebugLog -Message "Validating Backup on BackupSetID($backupsetID) and MediaSetId($MediaSetId)" -Path $LogFile
$backup_dataset = $Database.ExecuteWithResults("SELECT backup_set_id, bs.media_set_id, bmf.physical_device_name `
from msdb.dbo.backupset bs LEFT JOIN msdb.dbo.backupmediafamily bmf `
ON bs.media_set_id = bmf.media_set_id `
where backup_set_id = $backupsetID and bs.media_set_id = $MediaSetId");

foreach ($t in $backup_dataset.Tables[0].Rows)#only ever one row, 2 values returned
{
$backup_loc = $t.physical_device_name
}
Write-DebugLog -Message "Testing Backup Physical Path ($backup_loc)" -Path $LogFile
#if this path is valid, then we can retrun this as a valid object
if(Test-Path -Path $backup_loc)
{
Write-DebugLog -Message "Backup Physical Path ($backup_loc) is valid" -Path $LogFile
#create a new object from the variables
$properties = @{ `
'backupsetID'=$backupsetID; `
'backup_loc'=$backup_loc; `
'valid'=$true}

$object = New-Object -TypeName PSObject -Property $properties;
return $object
}
else #return a false flag, and an empty object
{
Write-DebugLog -Message "Backup Physical Path ($backup_loc) is NOT valid" -Path $LogFile
$properties = @{ `
'backupsetID'=0; `
'backup_loc'=""; `
'valid'=$false}

$object = New-Object -TypeName PSObject -Property $properties;
return $object
}
}
catch #output an exception, and return a false flag, and an empty object
{
Write-DebugLog -Message "Exception Message: $($_.Exception.Message) Exception Type: $($_.Exception.GetType().FullName)" -Path $LogFile
$properties = @{ `
'backupsetID'=0; `
'backup_loc'=""; `
'valid'=$false}

$object = New-Object -TypeName PSObject -Property $properties;
return $object
}
};

Restore the Database to our target instance
As stated previously, once we have found a valid backup still on disk, we will restore the database to the monitoring instance. I add a _restored onto the name as it is restored, to make sure that there are no accidents, and we end up overwriting an existing database.

Function Run-RestoreBackupVerification
{
Param([object]$Database,[object]$target,[object]$Backup, [String]$LogFile);
try
{
#get the relevant backup data
$backup_location = $Backup.backup_loc;
$backupsetID = $Backup.backupsetID;
Write-DebugLog -Message "Attempting to restore backup($backup_location) of Database($Database)" -Path $LogFile
$smo = "Microsoft.SqlServer.Management.Smo";
$restore_name = "_restored"; #my choice to idenitfy the database on the target instance
$restore_path = "S:\SQLDATA"; # S id dedicated to testing these restores on my monitoring instance.

#set a new name, just in case!
$restore_name = $Database.Name + $restore_name;

#build our restore object
$BackupDevice = New-Object ("$smo.BackupDeviceItem") ($backup_location, 'File');
$Restore = New-Object ("$smo.Restore");
$Restore.Checksum = $True;
$Restore.Devices.Add($BackupDevice);
$Restore.Database = $restore_name;
$Restore.ReplaceDatabase = $False; # make sure we aren't breaking anything!

Write-DebugLog -Message "Attempting to Get Data Files for backup ('$backup_location')." -Path $LogFile
# this is how we find all the files for the database in the selected backup set
$backupfiles = $Database.EnumBackupSetFiles($backupsetID);
foreach ($file in $backupfiles)
{
$FileName = $restore_path + "\" + [System.IO.Path]::GetFileName($file.PhysicalName)
$NewFileLoc = New-Object ("$smo.RelocateFile") ($file.LogicalName, $FileName)
$Restore.RelocateFiles.Add($NewFileLoc)
}
#run our restore
$Restore.SqlRestore($target);
Write-DebugLog -Message "Completed Restore of backup($Backup.backup_loc) to Database($restore_name)" -Path $LogFile
$true
$restore_name
}
catch #output an exception, and return a false flag, and an empty object
{
Write-DebugLog -Message "Exception Message: $($_.Exception.Message) Exception Type: $($_.Exception.GetType().FullName)" -Path $LogFile
$false
$null
}
};

Verify the backup and email results
Once the database is restored, I take a row count of all user tables and output this to a file.

Function Get-DatabaseRowCounts
{
Param([string]$DatabaseName,[string]$sourceInstance,[object]$targetInstance, [String]$LogFile);

try
{
Write-DebugLog -Message "Attempting to collect row data of database($DatabaseName)" -Path $LogFile
#set the database object
$Database = $targetInstance.Databases.Item("$DatabaseName")

#set our file name and path
$date = Get-Date
$date = $date.ToShortDateString().Replace("/","")
$file = $date + "_" + $sourceInstance.Replace("\","$") + "_" + $Database.Name + "_TableCount.txt"
$path = "C:\temp\"
$fullPath = $path + $file
[String]$header = "TableName, RowCount"

Write-DebugLog -Message "Creating output file $fullPath" -Path $LogFile
#create the file
New-Item -Path $path -ItemType "file" -Name $file -Value $tableList
#add the header row
Add-Content -Path $fullPath -Value $header

Write-DebugLog -Message "Adding data to output file $fullPath" -Path $LogFile

#get the data to build our file contents
$ds = $Database.ExecuteWithResults("SELECT OBJECT_NAME(OBJECT_ID) as TableName, `
st.row_count FROM sys.dm_db_partition_stats st `
WHERE index_id < 2 ORDER BY st.row_count DESC")

foreach ($row in $ds.Tables[0].Rows)
{
$newRow = "`n" + $row.TableName + "," + $row.row_count;

Add-Content -Path $fullPath -Value $newRow;

}
Write-DebugLog -Message "Output file complete. Dropping database ($Database)" -Path $LogFile
#we are through, set our true flag and output the database.
$Database.Drop() | out-null;
$true
$fullPath
}
catch #output an exception, and return a false flag, and an empty object
{
Write-DebugLog -Message "Exception Message: $($_.Exception.Message) Exception Type: $($_.Exception.GetType().FullName)" -Path $LogFile
$false
$null
}
};

Put it all Together
There could by any stages above where the code could fail out, it could try and execute against a server that is having maintenance on it, the backup file could be one that isn’t on disk any more, the restore could fail… All of these items will be recorded in the log, so we can investigate why the selected values did not work, but we really want to validate that a proper backup was selected and restored ok, so we assign a Boolean value to all of the above functions, and only a true value from every function will close out the process and send the email to the DBA group:


$targetInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") SERVER1 #This is where we will restore our database, and the source of the database lists
$processComplete = $false;
$instance = $false;
$database = $false;
$backup = $false;
$restored = $null;
$output = $false;
$attachment = $null;

#output items - this is the log file that will store steps taken, and any errors.
$Log = "C:\temp\BackupVerification_" + [DateTime]::Now.ToString("yyyyMMdd_HHmmss") + ".txt";

#mail items
$smtp = "smtp.domain.com";
$from = "dba@domain.com";
$to = "dba@domain.com";
$subject = "DBA: SQL Server backup verification results";
$body = "Find attached the latest reults from SQL Agent job: DBA - Backup verification Process.";
$attachment = @();# 2 attachments \ message, 1 log and 1 table row output
#mail items
while($processComplete -eq $false)
{
#reset everything
$instance = $false;
$database = $false;
$backup = $false;
$restored = $null;
$output = $false;
Write-DebugLog -Message "--------------------------------------" -Path $Log
#step 1 - get a source instance for our testing
$instance = Get-RestoreSourceServer -instance $targetInstance -LogFile $Log
if($instance[0] -eq $true)
{
#step 2 - get a source database for our testing
$database = Get-RestoreSourceDatabase -instance $instance[1] -LogFile $Log
if($database[0] -eq $true)
{
#step 3 - get the backup file
$backup = Get-RestoreBackupLocation -Database $database[1] -LogFile $Log
if($backup.valid -eq $true)
{
#step 4 - restore the database
$restored = Run-RestoreBackupVerification -Database $database[1] -target $targetInstance -Backup $backup -LogFile $Log
if($restored[2] -eq $true)
{
#step 5 - output our results
$output = Get-DatabaseRowCounts -DatabaseName $restored[3] -sourceInstance $instance[1].Name -targetInstance $targetInstance -LogFile $Log
if($output[1] -eq $true)
{
#we are through, send the email, set the flag, and we are done.
$attachment += $Log
$attachment += $output[2]
Send-MailMessage -SmtpServer $smtp -From $from -To $to -Subject $subject -Body $body -Attachments $attachment | out-null
$processComplete = $true;
}
else
{
Write-DebugLog -Message "ERROR: Could not output File" -Path $Log
continue
}
}
else
{
Write-DebugLog -Message "ERROR: Could not restore Database" -Path $Log
continue
}
}
else
{
Write-DebugLog -Message "ERROR: Could not get valid Backup" -Path $Log
continue
}

}
else
{
Write-DebugLog -Message "ERROR: Could not get Valid Database" -Path $Log
continue
}
}
else
{
Write-DebugLog -Message "ERROR: Could not get a valid Instance" -Path $Log
continue
}
};

Conclusion
I have one script that holds all of the above code, and I run it as a job in SQL agent on a weekly basis. It can be called as an Operating System Command in the Agent:

Agent Details

It will then send me an email as below:

EmailDetails

This does not get round all of the steps required for the audit process. I still have to upload the results to our hosted SharePoint site, and record the date of the run, but I can now run this on a daily, weekly, monthly or ad-hoc basis and keep our SOX team happy that we are running random tests on a selection of our databases, and along with the other refreshes, described above, we are covering most scenarios.

A full copy of the code can be downloaded here.

Thanks for reading

Posted in Powershell, SQL Server | Tagged , , , , , , | Leave a comment

Analyzing SQL Agent job activity using PowerShell

From time to time, it is up to DBAs and sysadmin’s to prove themselves innocent of charges that ‘they’ are killing the entire [insert core infrastructure system here] with “their” system activity.

Recently, we have had some serious problems on our relatively new Enterprise class SAN system (vendor names will be withheld to protect the innocent). On a daily occurrence, the SAN was experiencing 100% Write Pending cache for an hour or 2, then dropping off. During these spikes, LUNs attached to mail stores went offline, ERP systems slowed to a crawl, and all manner of other “catastrophic” events took place. Eyes were almost lost with the speed it took for the fingers to fly up and start pointing at each other. But those of us in the Enterprise IT group quickly began rational analysis of the various processes that were running during the outages.

As the only Enterprise DBA, it was up to me to check for any SQL based activity / jobs that may be hitting the SAN during these time frames, or at the very least, provide some evidence that these systems were performing at usual levels. Normally I have a much narrower target when starting these investigations “Our SharePoint server is very slow, can you have a look”, so digging into metrics and looking at activity, is pretty straight forward. This time however, the target was very wide.

Luckily, my best friend PowerShell is in my life now, and I could quickly begin to analyze data across all my servers in some simple scripts. As this was a very specific timeframe, spike starts at 3.00am ends at 4.00am, my first thought was “is there a job firing during this spike?”.

So I set out to query all of my servers, and output and jobs into xl for analysis,. Below is the end result:


cls
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$MonitorInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "MyMonitoringServer"
$MonitorDB = $MonitorInstance.Databases.Item("SQLMonitor")

$ds = $MonitorDB.ExecuteWithResults("[usp_SEL_sql_name_with_instance_id]")

#create our main job array
$objarray = @()

#set a date object
$current_date = Get-Date
$start_time = Get-Date -Date "2013-09-04 19:00:00"
$end_time = Get-Date -Date "2013-09-04 22:30:00"

#loop through each instance
foreach ($set in $ds.Tables[0].Rows)
{

$instance = $set.instance_name
# Create an SMO connection to the instance
$srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

#get all the jobs on the server
$jobs = $srv.JobServer.Jobs

#go through each job and find data for the job name we want
foreach ($job in $jobs)
{
if($job.LastRunDate -gt $current_date.AddDays(-1)) #get the jobs run in the last 24 hours
{

$jobName = $job.Name;
$jobEnabled = $job.IsEnabled.ToString();
$jobLastRunDate = $job.LastRunDate;
$jobLastRunOutcome = $job.LastRunOutcome.ToString();
$jobNextRunDate = $job.NextRunDate.ToString();
$jobStatus = $job.CurrentRunStatus.ToString()
$duration = 0;

foreach($step in $job.JobSteps)
{
#Set Duration
$duration += $step.LastRunDuration;
}
$jobEndDate = $jobLastRunDate.AddSeconds($duration).ToString()

#set our job start and job end variables
$jobstart = $job.LastRunDate
$jobend = $jobLastRunDate.AddSeconds($duration)

if(($jobstart -gt $start_time -and $jobstart -lt $end_time) `
-or ($jobend -lt $end_time -and $jobend -gt $start_time) `
-or ($jobstart -lt $start_time -and $jobend -gt $end_time))
{
$properties = @{ `
'Instance'=$instance; `
'JobName'=$jobName; `
'Enabled'=$jobEnabled; `
'LastRunDate'=$jobLastRunDate; `
'LastRunOutcome'=$jobLastRunOutcome; `
'NextRunDate'=$jobNextRunDate; `
'Status'=$jobStatus; `
'Duration'=$duration; `
'EndDate'=$jobEndDate;}

$object = New-Object –TypeName PSObject –Prop $properties
$objarray += $object
}
}
}
}

#output the jobs
$objarray | select * | Export-Csv C:\DBCC.csv -NoTypeInformation

I should point out, that I keep a monitoring server that has a home grown database with a list of all my currently managed instances in it, and you can see I query that to pull the data I require to loop through each instance. The above script can be easily changed to pass in a text file with a comma separated list of servers.

The flow of the script is pretty easy to follow:

• Go to my monitoring server and get a list of all active instances.
• Set up an array to store my job objects.
• Specify a time frame to search.
• Create an SMO instance to the server currently in the loop.
• Search for jobs that fall into our time frame.
• Create a new object containing the properties of the job we would like to investigate.
• Add that job object into our array.
• Next Please.

Once it has run, it then finally outputs the data into a csv file, so I can go ahead and filter, sort and manage the output in xl. Below is a screenshot of the data I got out for my late evening time frame, as you can see, I make free use of the jobs provided by the Mighty Ola Hallengren:

JObSchedulecapture

I ran this script a number of times, after a particularly bad spike was experienced, and each time it showed that no SQL jobs matched the spikes. This went a long was to convincing the IT managers that SQL jobs were not the cause of the problems we were experiencing.

The investigation is still ongoing into exactly what was causing the problem, even the SAN Vendor engineers are scratching their head at the lack of a smoking gun, but migrating data to a smaller SAN and reducing workloads (backups, SQL jobs, VM activity) during the time frame, has at least brought it under control, to the point that user activity is unaffected, and the spikes are smaller more controlled.

Thanks for Reading.

Posted in Powershell, SQL Server | Tagged , , , , | Leave a comment

Making life easy with PowerShell Functions

In my last blog, I showed you how to quickly update the Log retention for SQL server, and made it look even easier by using a Profile loaded function that gets me an instance of a SQL server SMO object to manipulate, This was a one line essentially:

gss -ComputerName SERVER1

The gss stands for Get-SQLServer (obviously). Since the last blog, I have amended the function slightly to allow for more exact server location, and replaced the -ComputerName parameter, with an -Instance Parameter.

The full code for the function is as below:

#simplify SQL SMO server retrieval
function Get-SQLServer
{
param([string]$Instance, [String]$IP, [String]$DomainSuffix = ".GLOBAL.COM")

#add the domain to the instance
if ($Instance.Contains("\"))
{
$Instance = $Instance.Split("\")[0] + $DomainSuffix + "\" + $Instance.Split("\")[1]
}
else
{
$Instance = $Instance + $DomainSuffix
}

$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $Instance -ErrorAction Continue
if (($server.versionString -eq $null) -and $IP.Contains("."))
{
#couldnt open using FQ instance name, then try IP
$server = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $IP -ErrorAction Continue
}
if ($server.versionString -eq $null)
{
Write-Host "Unable to connect to SQL Server: $Instance"
}
else
{
return $server
}
}

#add alias for Get-SQLServer
New-Alias gss Get-SQLServer

Once this is in your profile (I won’t cover old ground by telling you how to load functions in your profile, this excellent article by Ed Wilson himself! will get you going) you can simply call the function as below:

--using full function name
$server = Get-SQLServer -Instance SERVER1

--or using alias for a multi domain network
$server = gss -Instance SERVER2 -DomainSuffix .DOMAIN2.COM

I included an -IP parameter as DNS issues on some of our legacy networks only allow connection via IP, but if you have a fully working DNS and network, you can leave this out.

This function has a reliance on the Microsoft.SqlServer.SMO libraries, so if you use it, you have to put in a reference to them, as below:

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’)

Hope this helps in some way.

Thanks for reading.

Posted in SQL Server | Tagged , , , , | Leave a comment

Changing Log file retention using PowerShell

I love SQL Server. No matter how long you play with it, there is always a little setting here and there that I did not know about, or had forgotten over the years. This morning Mr Denny clued me into one of these on his blog (via Twitter). The setting was the number of SQL server error logs to retain. He showed us how to set this in SSMS and via a T-SQL change to the registry.

My first thought as I read this was “how can I do this in PowerShell?”.

Sometimes it is easy, sometimes it is harder than anticipated. In this case, it was way to easy, it took me all of 2 minutes looking at the SMO Server object to find a wonderful little property, NumberOfLogFiles. This led me to write this little script that I am going to add into my standard Post deployment configuration script:

$server = gss -computername SERVER1
$server.NumberOfLogFiles = 20
$server.Alter()
$server.Refresh()

gss is an alias to my own profile loaded function that simply returns an SMO object referencing the server I am looking at. Of course, as Mr Denny states, the number you want to configure will vary from install to install, so make sure you use the appropriate value.

Thanks for reading.

Posted in SQL Server | 2 Comments

Checking Alerts on remote server using PowerShell and SMO

It is common practice to configure a set of alerts across all of your servers. These alerts will cover standard Events (severity 017 – 025) and SQL Server conditions (Log space used etc. ). You can manage these alerts through SSMS and scripting, but PowerShell has made this job a lot easier with some very handy SMO classes (server, database, Agent).

For example, if you wanted to check what alerts are configured on all of your servers you can iterate through a table of your instances, or a text file with all of your servers listed, and pull back a list of all of the alerts configured on each instance. You can even expand this to find out how many times each alert has been fired, and when.

The script below shows you how to easily pull this information from one server, and can easily be adapted to manage all of your servers:

cls
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

$server = "SERVER1"
$Instance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $server

$Alerts = $Instance.JobServer.Alerts

foreach ($Alert in $Alerts)
{
write-host "Name: " $Alert.Name
write-host "Occurrences: " $Alert.OccurrenceCount
write-host "Type: " $Alert.AlertType
write-host "Count Reset Date: " $Alert.CountResetDate
write-host "Last Occurrence: " $Alert.LastOccurrenceDate
Write-Host `
}

Thanks for reading.

Posted in SQL Server | Tagged , , , , | Leave a comment

[sqagtres] CheckServiceAlive: returning TRUE (success) SQL 2008 R2 Cluster

Quick blog for this one, took me a little while to track down exact problem and fix, so hopefully this will speed someones fix time.

I recently installed a new 2008 R2 cluster on Windows 2008. A week or so after the install, and before it went live, I noticed that the Application log was being spammed every 15 seconds with the error messages

  • [sqagtres] CheckServiceAlive: returning TRUE (success)
  • [sqagtres] LooksAlive request.

This is a known bug in SQL 2008 R2 and SQL 2012 that has been addressed by Microsoft in the (current) latest CUs. The link for detailed problem can be found here.

Posted in SQL Server | Tagged , , , | Leave a comment

Getting Disk Sizes using WMI and PowerShell

I love the 3rd party products that are on the market for SQL Server. Idera, Redgate and Quest (the big 3) have some amazing options for helping you manage your environment, but these can come at a pretty steep cost, and sometimes that budget just isn’t available to you. If you don’t have access to any of these tools, it can be a real challenge managing your environment, especially when you are dealing with thousands of databases across multiple instances.

PowerShell to the rescue. Here I go again… I have said it before and I will keep saying it, PowerShell is a very powerful tool, and once you get to know it, a pretty easy one as well.

One of the areas I have always had trouble with, is retrieving disk information. Before I knew PowerShell, I spent an inordinate amount of time writing overly complex T-SQL scripts to try and pull the data, but there were always barricades in the way (OLE not enabled, cmdshell disabled etc.) . Even VB scripts seemed to be a pain. Then I started playing with PS and WMI and realized I had been making my life a lot harder than it needed to be. With a simple one line script I could pull all the information I required:

Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Filter "DriveType=3" -ComputerName $server

I am not going to get into the details of WMI here (this is an excellent starting point, if you are interested), but I never realized quite how powerful it was.

The below script takes a list of servers in a simple text file, and iterates through each one using WMI to pull all of the available disks, their size and free space. There is a whole host of other information available in the WMI classes, but for the sake of the script, let’s keep it simple.

cls
#get the list of servers
$server_list = get-content 'c:\temp\servers.txt' | where-object{$_ -ne ''}
#loop through all of the servers individually
for($i=0;$i -le $server_list.length)
{
$server = $server_list[$i]
$server
$diskCol = Get-WmiObject Win32_LogicalDisk -Namespace "root\CIMV2" -Filter "DriveType=3" -ComputerName $server
foreach($disk in $diskCol)
{
#reset values, make sure nothing is carried across
$disk_name = ''
$disk_size = 0
$free_space = 0

#now get real values and then show them in the console
$disk_name = $disk.Name
$disk_size = [Math]::Round(($disk.Size / 1gb), 2 )
$free_space = [Math]::Round(($disk.FreeSpace / 1gb), 2 )

Write-Host "Drive : " $disk_name
Write-Host "Size : " $disk_size
Write-Host "free space : " $free_space
Write-Host

}
write-host
$i=$i+1
}

And there we have it. I am simply outputting the returned data to the console here, but you can do whatever you need to with the values, by adding a couple of SMO statements, you could feed the data back into a monitoring database, or you could output to an HTML report. Below is an example of the output I got for 2 of my servers:

Output

I hope this helps someone out in some small way, and thanks for reading.

Posted in SQL Server | Tagged , , , , | Leave a comment