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.