In an earlier blog entry, I showed you how you could build and populate your CMS groups to use with SSMS to run multi-server queries. Okay, so now you are probably thinking of lots of configuration type info which you’d like to collect periodically from your instances. Obviously, you don’t want to have to do this manually in an SSMS Query Editor window all the time, you want to automate it, right? You can probably guess where I’m headed next… Yep – PowerShell!
OK, so that is interesting, you have the SQL Server instance version results for all the instances registered in the defined CMS Group in a file on your hard drive. But what you really want to do is collect several different properties about your server instances and store that data such that you can query (report) it later.
For this case, let’s assume that you have a database called DBAdmin on the same instance as your CMS – this is also your “repository instance”. In the DBAdmin database create the following table:
You do need to be aware in multi-versioned environments that some properties are not available in all versions. Thus you’ll need to be able to handle null values returned by the SMO properties. For these properties, you will want to collect in a variable prior to building the insert statement as shown by the $WebXPs variable in the sample code below.
Now, loop through the CMS group as before and collect the instance level information using SMO and store the results in the repository.
Hopefully, you see the possibilities for extending beyond the properties shown here.
So now all that remains is to save your code into a PowerShell script and schedule it to run from your repository instance’s SQLAgent to collect your SQL Server configuration data on a regular basis. Don’t forget to decide how much historical data you want to keep and set up a step in your job to purge the repository of old data. And finally, in case you didn’t think of this already, you’ll need to schedule the job steps running the PowerShell script to execute as an account which has the necessary access to all of the registered servers in the CMS Group.
So, why use CMS Groups for this task? I think this gives you more flexibility in setting the scope of your collection scripts without requiring you to change any code within those scripts once you’ve finalized them. The only thing you need to manage is the CMS Groups, which can be manual or automated or both, as discussed in my blog entry Managing the Enterprise with CMS. It allows you to use multiple repositories which run the same scripts but against different CMS groups (for example regionally or by customer).
Finally, this is sample code to demonstrate what is possible. It should work, but use at your own risk and test, test, test in your own environment!