Here's what I mean

Today I'm going to describe my problem, and how I currently solve it, in more detail, because it might help you perform a common task -- or, you might help me figure out how to do this common task better.

How do you save all your servers' settings in one place?

As you probably know, you can use the query select * FROM sys.configurations to get the server-global settings in SQL Server. As you probably realize, you can use this query, plus @@SERVERNAME, to get this information for all the servers being managed as a group by a CMS.

What I wanted to do seemed pretty simple and useful, and probably every DBA has a way of do it: keep a history of these settings over time, for all my servers, in one table of a management database I use for stuff like this.

Seems obvious, right? But I couldn't figure out a way to do this in one step using a CMS. Each iteration of the query would be issued in the context of each individual server, not the context of my management server.

To be clear, I'm sure you can do this if you create a stored procedure on each managed server and also a linked server pointing to the CMS database in which you want to store the data -- but, #1, I absolutely don't want to create those links on each of my servers and #2, I thought that the point of a CMS was to avoid having to disperse a bunch of code among all the servers you manage.

How I do it now, faute de mieux

I've got it down to a couple of klutzy steps.

1. Script what I want to put in the management table as a set of VALUES

The actual query I issue against the CMS server group looks like this:

SELECT '('+quotename(getdate(),'''')+','+ quotename(@@ServerName,'''')+','+quotename(name,'''')+','+ cast(value as nvarchar(max))+','+ cast(value_in_use as nvarchar(max))+','+ cast(minimum as nvarchar(max))+','+ cast(maximum as nvarchar(max))+','+ quotename([description],'''')+'), 'FROM sys.configurationsORDER BY name -- the ORDER BY just lets me view the results a little more clearly

2. Receive a bunch of almost-executable text back

When I run the above statement, I get a single column of results that looks like this (assuming you run the results to a grid, I guess it makes just as much sense to run to text in this case):

In what you see above, "COCO" is the name of my server; following COCO's values, there is another set for each of the servers in the CMS server group.

3. Adjust the almost-executable text to make it executable

I take the stuff I get from the CMS query and copy it to a new sql script query. Since each set of values ends with a comma, I remove the comma from the final row of values and replace it with a semi-colon.

Then I preface all the sets of values with the following INSERT statement, matching the values, and appropriate to the table (somewhat de-normalized, yes, I know) that I have chosen to use for this purpose: