G’day,

Well, it’s the first TSQL-Tuesday of 2014 and one of my New Years resolutions for 2014 is to contribute to each TSQL-Tuesday of the year – lets see how we go!

Well, the subject of this months TSQL-Tuesday is Automation – which is quite obviously something that any good DBA strives for.

There’s a lot of checks to be done every day  – some are pretty mundane but they are all extremly important – after all being proactive is a massive part of our job and if something is about to go pear shaped we want to know about it way before one of our users tells. And so for most DBA’s one of the most imporatnt parts of being proactive are the daily checks and having these automated is a huge bonus.

The daily checks scripts mostly just collect metrics and alert at a certain threashold. For example a disk space check may collect metrics on the size of the disk and the amount of free space on it. The script would alert if say there was less than 10% free or maybe just a certain amount of space – after all, if we’ve got a terrabyte disk we might not want alerting when we only have 10% of that free – which is 100GB.

Another may be collecting errors from the application log or security log. Others may do things like check on the uptime of the OS and the SQL instance, check for logon failiures, check for jobs that failed – the list can go on and on.

There are also many way to run these scripts – TSQL, SSIS, Powershell as well as a few ways to automatically schedule then.

My tools of choice are Powershell scripts run from the SQL Server Agent. So every day at about 4am the SQL agent spins and runs a whole set of scripts for me on all of the servers I look after.

That’s great, but I remember when I first set this up, I needed a list of all the SQL instances that I needed to monitor and have confidence that the list whould be updated regularly to take acount of any new instances joining the environment or old ones leaving.

I could have just used a straight text  file, or even a list stored in a table – but we already have a list of all of SQL instances, nicely arranged by environment ( DEV / TEST / UAT / PROD ) on our Central Management Server – so why not take that list directly from there?

As we know Powershell loves arrays, we can pass arrays to most functions. So I built a few Powershell scripts of my own to pull off a list of SQL instances from each folder of the Central Management server and return then as an array that could be passed to another function or iterated through.

The next question became – what is the best way to get this list as we can use either TSQL or SMO to get it. Did you know that the list of instances held in your Central Management Server was stored in a few tables from MSDB on the Central Management server itself?

Here’s some TSQL that will get the top level list

SELECT * FROM [msdb].[dbo].[sysmanagement_shared_server_groups_internal] [Groups]
WHERE parent_id =
(
SELECT server_group_id
FROM msdb.dbo.sysmanagement_shared_server_groups_internal
WHERE name = 'DatabaseEngineServerGroup'
);

However I decided that the best way for me to obtain a list of the instance for each environment was via SMO.

Here’s a functions that I wrote to obtained a list from a certain node of the Central Management Server – obviously you should change the name of the Central Management Server and node to match your own environment.

[sourcecode language="Powershell"]

function Get-SNZ_DBA_AllSQLInstances

{

#name of the central management server

$CentralManagementServerInstance = “ChangeThis”

#Load SMO assemplies

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’) | out-null

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Management.RegisteredServers’) | out-null

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SqlWmiManagement”) | out-null

[System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Management.Common’) | out-null

$connectionString = “Data Source=$CentralManagementServerInstance;Initial Catalog=master;Integrated Security=SSPI;”

$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)

#$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)

#$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

$CMSStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($sqlConnection)

#Define the group and sub group that we want to loop over

$CMSDBStore = $CMSStore.ServerGroups["DatabaseEngineServerGroup"];

$Servers = @();

foreach ($desc in $CMSDBStore.GetDescendantRegisteredServers())

{

#$Servers += $desc.Name.Trim();

$Servers += $desc.ServerName.Trim();

};

#delete duplicate names and return.

return $Servers | select -Unique;

};

[/sourcecode]

Another great automation tool I’ve come to love is Policy Based Management – but I’ll save that for another day.

Happy automating.

Have a nice day.

Cheers

Martin.