Adding an AD Security Group to remote Server Administrators with PowerShell

Finally, my team of 1 has become 2. From now on I am not the only point of contact for SQL at my company. I tell you, it is going to be nice to go away without a laptop tucked away in my bags. To bring them on board and get his access working, I had to make sure the various security groups were set up correctly. The Database security group was configured on all SQL instances, so that worked great. There was not however a group for rdp access to the SQL instances, as I was a member of a higher level group, that gave me access to all servers, SQL or not. I created a group in AD (the benefits of being on the Middle-ware team), but I had to get the group deployed to all of my managed instances, and I was not going to add it to 102 servers manually, not when PowerShell exists in the world.

I initially approached my boss, who is an AD guru, and he said that it could be done very simply using a couple of lines of PS. He was right, It took me about 10 minutes of searching to find the right PS objects, and then about 20 minutes of playing with those objects until I found the Right combination to complete my task. To hopefully save you some time, if you are trying to accomplish the same thing, I present to you this code. I use a database for managing all of my instances, but you can easily change the code to use a server list, or array.

cls
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$MonitorInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "MyMonitoringServer"
$MonitorDB = $MonitorInstance.Databases.Item("SQLMonitor")
$domainGroup = [ADSI]("WinNT://DOMAIN/MYDOMAINSECUITYGROUP")
$ds = $MonitorDB.ExecuteWithResults("select servername from server")
foreach ($set in $ds.Tables[0].Rows)
{
$computer = $set.servername
$localGroup = [ADSI]("WinNT://$computer/Administrators")
$localGroup.PSBase.Invoke("Add",$domainGroup.PSBase.Path)
}

It’s that simple. I hope this proves useful to you.

Thanks for reading.

Posted in Powershell | Tagged , , | Leave a comment

Collecting Current Performance Monitor Data on Remote Servers with PowerShell

As an admin of systems, people will come to me on a regular basis an ask me “so and so server is slow, what’s wrong with it??”, as if expecting me to have that information right in front of me. If you are lucky, you may have monitoring software that records your historical data for you, and you can log in, and take a look. If, however, you don’t, or the server in question isn’t deemed important enough to warrant that costly third party license fee, you may need to collect some data right away, to see if thing are looking iffy.

This script will create a function that will allow you to log in to you PowerShell IDE of choice, and pull back some current perfmon data in a couple of minutes (depending on how much you want to collect), and then provide you with an aggregated output of the data points from your server.

Enjoy…

function Get-PerformanceCounters
{
param([String]$Instance, [Int]$samples, [Int]$Interval )

#our output array for the raw data
$outputArr = @()

#set the required format for counter data SQL metrics
if ($Instance.Contains("\"))
{
$srvr = $Instance.Split("\")
$server = $srvr[0]
$instanceName = $srvr[1]
$sqlinstance="MSSQL$"+"$instanceName"
}
else
{
$server = $Instance
$sqlinstance = "SQLServer"
}

#build an array of the required counters
$counterCollection = @(
"\Physicaldisk(*)\Avg. Disk Queue Length",
"\Physicaldisk(*)\Avg. Disk sec/Read",
"\Physicaldisk(*)\Avg. Disk sec/Write",
"\Physicaldisk(*)\Avg. Disk Sec/Transfer",
"\Physicaldisk(*)\Disk Reads/sec",
"\Physicaldisk(*)\Disk Writes/sec",
"\Physicaldisk(*)\Disk Bytes/sec",
"\Memory\Pages/sec",
"\Memory\Page Reads/sec",
"\Memory\Available MBytes",
"\Paging File(_Total)\% Usage",
"\Processor(_Total)\% Processor Time",
"\System\Processor Queue Length",
"\$($sqlinstance):General Statistics\User Connections",
"\$($sqlinstance):Access Methods\Page Splits/sec",
"\$($sqlinstance):Access Methods\Full Scans/sec",
"\$($sqlinstance):Buffer Manager\Page life expectancy",
"\$($sqlinstance):Buffer Manager\Buffer cache hit ratio",
"\$($sqlinstance):Memory Manager\Memory Grants Pending",
"\$($sqlinstance):Memory Manager\Target Server Memory (KB)",
"\$($sqlinstance):Memory Manager\Total Server Memory (KB)",
"\$($sqlinstance):SQL Statistics\Batch Requests/sec",
"\$($sqlinstance):SQL Statistics\SQL Compilations/sec",
"\$($sqlinstance):SQL Statistics\SQL Re-Compilations/sec",
"\$($sqlinstance):Locks(_Total)\Lock Requests/sec",
"\$($sqlinstance):Locks(_Total)\Number of Deadlocks/sec",
"\$($sqlinstance):Databases(*)\Transactions/sec",
"\$($sqlinstance):Wait Statistics(Waits in progress)\Page IO latch waits",
"\$($sqlinstance):Wait Statistics(Average wait time (ms))\Page IO latch waits",
"\$($sqlinstance):Wait Statistics(Waits in progress)\Page latch waits",
"\$($sqlinstance):Wait Statistics(Average wait time (ms))\Page latch waits",
"\$($sqlinstance):Wait Statistics(Waits in progress)\Network IO waits",
"\$($sqlinstance):Wait Statistics(Average wait time (ms))\Network IO waits"
)

#pull the raw data using default PS function and assign to an object
$counters = Get-Counter -ComputerName $server -Counter $counterCollection -SampleInterval $Interval -MaxSamples $samples
foreach($counter in $Counters.CounterSamples)
{
#bind the counter details to a variable
$counterPath = $counter.Path;
$counterInstance = $counter.InstanceName;
$counterValue = "{0:N4}" -f ($counter.CookedValue);

#create an object for all values
$properties = @{ `
'counterPath'=$counterPath; `
'counterInstance'=$counterInstance; `
'counterValue'=$counterValue;}

$readingObject = New-Object -TypeName PSObject -Prop $properties

#add the current counter to our main data set
$outputArr += $readingObject
}

#aggregate and output our values.
$outputArr | Group-Object CounterPath | %{
New-Object psobject -Property @{
CounterPath = $_.Name
Sum = "{0:N2}" -f(($_.Group | Measure-Object CounterValue -Sum).Sum)
Min = "{0:N2}" -f(($_.Group | Measure-Object CounterValue -Min).Minimum)
Max = "{0:N2}" -f(($_.Group | Measure-Object CounterValue -Maximum).Maximum)
Average = "{0:N2}" -f(($_.Group | Measure-Object CounterValue -Average).Average)
}
} | select CounterPath, Min, Max, Average | ft -AutoSize
}

Thanks for reading.

Posted in Powershell, SQL Server | Tagged , , | 1 Comment

Beware the unmanaged VM snapshots

So, it happened. After 3 years and 4 months in my current position, we have corruption! Every DBAs nightmare, right? Now, before I go any further, good planning won the day, and no data was harmed during the making of this blog. Thanks to a good backup routine, and page level restores (how I love page level restores), each time the corruption re-occurred (and it did) I was able to recover the database back to it’s full state with zero data loss.

The first time this instance corrupted, a full investigation was launched. A premier support ticket was opened with MS, while internal investigation by the SAN, VM and SQL teams took place. Microsoft came back reporting all clear. All they could find was some outdated VMWare drivers. So, we opened a ticket with VMWare to investigate, again, the all clear came back reporting that, yes, the drivers were old, but in support for our current ESX version (5.1). EMC were called to evaluate the logs of the array, to make sure there were no problems there. Again, the all clear came through. Heads were scratched, but we all decided it was one of those unknown events, and we would put it behind us, while keeping a close eye on the server, and increasing the frequency of Integrity checks (from daily to every 3 hours).

A few weeks passed peacefully and then !boom!, I got my alerts in the middle of the night “Database Integrity Check Failed on Server”! crap! same server! crap!. After another 12 hours outage, and much yawning on my part, the system was backup and recovered. Again, we launched our internal dialog to look at all possible possibilities, was it the SAP install, was it the SQL install (installed by a contractor I might add), was the OS at fault, were the disks bad. All involved came back with a resounding “it’s all good”, let’s keep monitoring and hope for the best. Christmas passed, and then suddenly bedlam. The database began corrupting almost constantly, and I began a 6 day cycle of repair \ corrupt \ repair \ corrupt. During this cycle and before we tried all of the following, to no avail:

  • Migrate VM to multiple new hosts.
  • Run chdsk on all SQL data and log drives.
  • Run a SQL repair.
  • Delete the database completely and restore from a clean backup.
  • De-provision and re-provision all disks (some had been badly provisioned with Lazy0 rather than Eager0).

And then, one of our more competent VM admins arose from his winter hibernation, and pointed out that we had a 5 month old snapshot attached to the VM!!

After several minutes banging my head on my desk, and recriminations saved for later, I asked how long it would take to remove the snapshot (hours as it happens), and why no one had pointed this out before (much shuffling of feet ensued). Once the snapshot was removed, I did a final recovery and brought the database back on-line. We are now 7 days and counting, with no re-occurrence.

When a snapshot is attached to a VM, it creates vmdk and vmdk-delta disks of all of the current virtual disks connected to the VM. Any changes made to the VM are written to these delta drives, and over time these files grow. The amount of growth is directly proportional to the IO performed on the VM, which as you can imagine on a SQL server is high, so during the 5 month life time of this snapshot, the growth steadily increased, and with it, the risk to the data. The team had already been advised to never use snapshots as a backup method of any of the SQL instances in the environment, and all old snapshots were supposed to have been removed, but sometimes mistakes are made.

There is no moral to this story, no diagnostic tool to detect the cause, just a rant and a share so hopefully someone can diagnose the same problems early, to avoid the pain I went through.

Thanks for reading.

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

Keep up to date with CMS and registered servers, using PowerShell

Introduction

Working for a large company with multiple SQL instances can be a lot of fun, a challenge, overwhelming, a headache, a great motivator for automation. You choose.

For me automation is the best answer here, working with hundreds of instances and 1000s of databases is impossible, unless you can work centrally and collect everything you need into one easily accessible location. There are many great ways to manage this automation, PowerShell, VBScripts, SSMS, SSRS etc. etc. Microsoft have been improving the lives of DBAs since day 1 and every version released introduces new ways of working.

One of the best tools they introduced was way back in SQL 2008, when they introduced us to SQL Central Management Servers (CMS). This allowed us to run single commands against multiple databases, and also have quick access to all of our managed instances.

When you are working with a small number of instances, manual updating is easily manageable. When you are managing hundreds of instances, not so much. I keep a track of all of my live ‘managed’ (we have many unmanaged remote site databases where offers of help are flatly refused) in a homemade inventory database, and occasionally I need to update my CMS with the correct list of live databases. The script below was written for just this task. I keep my list arranged by version, but it can easily be broken down into whatever category you require (prod \ non-prod, site etc.). When it comes to updating my CMS, I can simply open up PowerShell and run this script, et voila.

The Code
#Using SQLPS? make it so, number 1!
Import-Module sqlps -DisableNameChecking #load the SQLPS functionality for getting the registered servers

#set a top level location for selecting instance list.
#If done at lower levels errors are generated (e.g. in SQLRegistration).
Set-Location SQLServer:\

#get all of the servers from inventory database
$str = "SELECT
instance_name,
CASE
WHEN CHARINDEX('8.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2000'
WHEN CHARINDEX('9.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2005'
WHEN CHARINDEX('10.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2008'
WHEN CHARINDEX('10.5', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2008 R2'
WHEN CHARINDEX('11.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2012'
WHEN CHARINDEX('12.0', SUBSTRING(s.instance_version,1,4)) > 0 THEN 'SQL 2014'
END as instance_version
FROM
SQLMonitor.dbo.instance s"

$results = Invoke-Sqlcmd -query $str -ServerInstance "SERVER1"

#make sure we are on the right server and in the right place
Set-Location 'SQLSERVER:\SqlRegistration\central management server group\SERVER1';

#clean up all existing entries
dir -Recurse | Remove-Item -force;

#go through each new entry
foreach($result in $results)
{
#get our values in vars
$regserver = $result.instance_name;
$reggroup = $result.instance_version;

#check that the group exists or not
if(-Not (Test-Path -LiteralPath "SQLSERVER:\SqlRegistration\central management server group\SERVER1\$($reggroup)"))
{
#couldn't find the path, let's add the top level directory
Set-Location -LiteralPath "SQLSERVER:\SqlRegistration\central management server group\SERVER1"
New-Item -Path $reggroup -ItemType "Directory";
}
#add the new registration
New-Item -Name $(encode-sqlname $regserver) -path "SQLSERVER:\SqlRegistration\central management server group\SERVER1\$($reggroup)" -ItemType registration -Value ("Server=$regserver ; integrated security=true");
}

Thanks for reading, hope this is useful.

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

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 , , , , | 2 Comments

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