SQL Agent job to query multiple SQL Server Instances

SSMS provides a nice way to query many SQL Server instances at one time using the Central Management Server (CMS) functionality.  You can right-click on a server registration group and query all of the servers in that that group.  Very handy.  Unfortunately, there isn't anything that simple built in to the SQL Agent engine, so we have to use something else.  Here we'll use powershell to run a series of queries against a CMS group of servers to collect a list of databases and save that list in a central location.


The Script
For those of you who are anxious simply to try it out, here's the PowerShell script in its entirety right now:

# Get the list of Instances

$instanceNameList = invoke-Sqlcmd -query "SELECT server_name as Name FROM msdb.dbo.sysmanagement_shared_registered_servers_internal RS
join msdb.dbo.sysmanagement_shared_server_groups_internal SG on
RS.server_group_id = SG.server_group_id where sg.name in ('SQL Server 2008','SQL Server 2005')" -serverinstance cmsserver1
# We're looking for CMS groups with specific names ('SQL Server 2008', etc), so you'll need to
# make sure these match what you have in your environment.

$results = @()   #Initialze the Array
# Populate the array with instance and DB information

foreach($instanceName in $instanceNameList)
{$sqlversion=invoke-sqlcmd -query "exec sp_server_info 2" -ServerInstance $instanceName.Name
$SQLVersion="{0}" -f $sqlversion.attribute_value
$results += Invoke-Sqlcmd -query "select @@servername as instancename, name as dbname,'$sqlversion' as sqlversion from sys.databases  where name not in ('master','model','tempdb','msdb','pubs','northwind')" -ServerInstance $instanceName.Name
}

# Since we're only interested in the latest information, truncate the DBList table
invoke-sqlcmd -query "use dba; truncate table DBList;" -serverinstance cmsserver1

# Write the results of the above query to the DBList table
foreach($db in $results) {
# Since SQL Agent doesn't handle PowerShell variables entirely well, we work around it
# by assigning our variables a little backwards
$instancename="{0}" -f $db.instancename 
$dbname="{0}" -f $db.dbname
$SQLVersion="{0}" -f $db.sqlversion
$querytext="insert into dba.dbo.DBList (instancename,sqlversion,dbname) values ( '$instancename', '$sqlversion','$dbname');"
invoke-sqlcmd -query $querytext -serverinstance cmsserver1 }

Save this as a PowerShell SQL Agent job, and you'll be part-way there.  You also will need to make sure that the SQL Agent service account has access to the servers you want to query.  Finally, you'll need a table somewhere to store this information

The table DBLIST looks like this referenced in the above query looks like this:

CREATE TABLE DBLIST (
 instancename VARCHAR(50)
 ,dbname VARCHAR(200)
 ,sqlversion VARCHAR(50)
 )

Of course, you can modify this to meet your needs.


 Now we'll dissect this a bit, so you can make changes to fit your environment.

Get a list of the SQL Server instances
First we'll query the Central Management Server to get a list of the registered SQL Servers:

$instanceNameList = invoke-Sqlcmd -query "SELECT server_name as Name
 FROM msdb.dbo.sysmanagement_shared_registered_servers_internal RS join msdb.dbo.sysmanagement_shared_server_groups_internal SG
  on RS.server_group_id = SG.server_group_id
    where sg.name in ('SQL Server 2008','SQL Server 2005')" -serverinstance cmsserver1 

Here we create an array named instanceNameList that simply contains the names of all of the servers registered in the CMS.  Note that these are stored in the MSDB database.

You'll need to change the -serverinstance switch to point to your CMS server.

We're using the invoke-sqlcmd cmdlet to run our queries; our later queries use syntax that isn't available on SQL Server 2000.  Because of this, we're limiting our queries to server groups we've defined named "SQL Server 2008" and "SQL Server 2005".  You'll need to change these values, as well, to match your environment and needs.  Depending on what your environment looks like, you might not even need that where clause at all.

As an aside, note that this is how you can get at the data in the CMS registration:  query msdb.dbo.sysmanagement_shared_server_groups_internal.

Query the instances one at a time

Now we'll initialize our results array ($results) and iterate through all of the instance names in our $InstanceNameList array.

Because we want the SQL Server version text (SQL Server 2008, etc.) instead of the version number, we have to do a little more work, utilizing the sp_server_info stored procedure.  We first assign that output text to the $sqlversion variable.  The sp_server_info SP outputs more columns than we want, however, so in the next line we re-assign the $SQLVersion variable to contain only the data in the "attribute_value" column.
Finally, we run our main query, including the $sqlversion variable as a static selection so that it is a third column in each of the rows.

$results = @()
foreach($instanceName in $instanceNameList)
{$sqlversion=invoke-sqlcmd -query "exec sp_server_info 2" -ServerInstance $instanceName.Name
$SQLVersion="{0}" -f $sqlversion.attribute_value
$results += Invoke-Sqlcmd -query "select @@servername as instancename, name as dbname,'$sqlversion' as sqlversion from sys.databases  where name not in ('master','model','tempdb','msdb','pubs','northwind')" -ServerInstance $instanceName.Name
} 

When this is complete, you'll have something like this:

instancename	dbname	SQLversion
SQLSRV1	ProdDB1	Microsoft SQL Server 2008 - 10.0.4064
SQLSRV1	ProdDB2	Microsoft SQL Server 2008 - 10.0.4064
SQLSRV1	ProdDB3	Microsoft SQL Server 2008 - 10.0.4064
SQLSRV2	DBA	Microsoft SQL Server 2008 R2 - 10.50.2500.0
SQLSRV2	TestDB1	Microsoft SQL Server 2008 R2 - 10.50.2500.0
SQLSRV2	TestDB2	Microsoft SQL Server 2008 R2 - 10.50.2500.0
SQLSRV3	CalendarDB	Microsoft SQL Server Yukon - 9.00.5000
SQLSRV4	WebDB1	Microsoft SQL Server Yukon - 9.00.5000
SQLSRV4	WebDB2	Microsoft SQL Server Yukon - 9.00.5000
SQLSRV4	WebDB3	Microsoft SQL Server Yukon - 9.00.5000

Note that sp_server_info returns "Microsoft SQL Server Yukon" for SQL Server 2005 instances. Curious choice, that.

Write the results to the reporting table
Now we'll iterate through all of the rows in the $results array.  Normally we'd do something like this to generate our query text:

$querytext="insert into dba.db.dblist (instancename, sqlversion,dbname) 
values ('$($db.instancename)','$($db.sqlversion)','$($db.dbname)');"

Unfortunately, the SQL Agent engine, for whatever reason, sees this syntax as an error, and it won't run, even though it runs fine from a SQLPS command prompt.
Instead, we assign the relevant values to new variables , and it all works OK.  Those "{0}" are formatting codes, BTW.  You can see a discussion of how that works here.

foreach($db in $results) {
$instancename="{0}" -f $db.instancename
$dbname="{0}" -f $db.dbname
$SQLVersion="{0}" -f $db.sqlversion
$querytext="insert into dba.dbo.DBList (instancename,sqlversion,dbname) values ( '$instancename', '$sqlversion','$dbname');"
invoke-sqlcmd -query $querytext -serverinstance cmsserver1 }