Monday, January 23, 2012

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:

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:

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.

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.