A few months ago, I posted a walkthrough on using a Policy and indicated that, although I started the process using a CMS or Central Management Server query, I didn’t see a good way to do exactly what I wanted all the way through with CMS, and I was troubled by that.

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.configurations
ORDER 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):

(‘Apr 15 2012 10:02AM’,’COCO’,’access check cache bucket count’,0,0,0,65536,’Default hash bucket count for the access check result security cache’),
(‘Apr 15 2012 10:02AM’,’COCO’,’access check cache quota’,0,0,0,2147483647,’Default quota for the access check result security cache’),
(‘Apr 15 2012 10:02AM’,’COCO’,’Ad Hoc Distributed Queries’,0,0,0,1,’Enable or disable Ad Hoc Distributed Queries’),
(‘Apr 15 2012 10:02AM’,’COCO’,’affinity I/O mask’,0,0,-2147483648,2147483647,’affinity I/O mask’),
(‘Apr 15 2012 10:02AM’,’COCO’,’affinity mask’,0,0,-2147483648,2147483647,’affinity mask’),
(‘Apr 15 2012 10:02AM’,’COCO’,’affinity64 I/O mask’,0,0,-2147483648,2147483647,’affinity64 I/O mask’),

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: 

insert into dbo.SQLServerValues 
 ([date] 
 ,[server]
 ,[name]
 ,[value]
 ,[value_in_use]
 ,[minimum]
 ,[maximum]
 ,[description]) 
values

Then, I execute the result in the context of my management database on the correct server.

What do you think?

Is there a better way? Please let me know.  Thanks!

And remember: the object is to get good use out of the CMS.  It just seems strange to me that the CMS concept is missing something that I’d consider so obvious.