{"id":51,"date":"2012-04-15T04:37:00","date_gmt":"2012-04-15T04:37:00","guid":{"rendered":"\/lisa\/post\/2012\/04\/15\/CMS-example-for-saving-server-setting-info-is-there-a-better-way.aspx"},"modified":"2012-04-15T04:37:00","modified_gmt":"2012-04-15T04:37:00","slug":"cms-example-for-saving-server-setting-info-is-there-a-better-way","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2012\/04\/15\/cms-example-for-saving-server-setting-info-is-there-a-better-way\/","title":{"rendered":"CMS example for saving server setting info: is there a better way?"},"content":{"rendered":"

A few months ago, I posted a walkthrough on using a Policy<\/a> and indicated that, although I started the process using a CMS or Central Management Server<\/a> 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.<\/p>\n

Here’s what I mean<\/h2>\n

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.<\/p>\n

How do you save all your servers’ settings in one place?<\/h2>\n

As you probably know, you can use the query select <\/span>* <\/span>FROM <\/span>sys<\/span>.<\/span>configurations<\/span> 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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

How I do it now, faute de mieux<\/em><\/h2>\n

I’ve got it down to a couple of klutzy steps.<\/p>\n

1.  Script what I want to put in the management table as a set of VALUES<\/h3>\n

The actual query I issue against the CMS server group looks like this:<\/p>\n

SELECT  ‘(‘<\/span>+<\/span>quotename<\/span>(<\/span>getdate<\/span>(),<\/span>””<\/span>)+<\/span>‘,’<\/span>+ 
           <\/span>quotename(<\/span>@@ServerName<\/span>,<\/span>””<\/span>)+<\/span>‘,’<\/span>+<\/span>quotename(<\/span>name,<\/span>””<\/span>)+<\/span>‘,’<\/span>+
<\/span>           cast(<\/span>value as <\/span>nvarchar<\/span>(<\/span>max<\/span>))+<\/span>‘,’<\/span>+
<\/span><\/span><\/span>           cast(<\/span>value_in_use as <\/span>nvarchar<\/span>(<\/span>max<\/span>))+<\/span>‘,’<\/span>+
<\/span><\/span><\/span>           cast(<\/span>minimum as <\/span>nvarchar<\/span>(<\/span>max<\/span>))+<\/span>‘,’<\/span>+
<\/span><\/span><\/span>           cast(<\/span>maximum as <\/span>nvarchar<\/span>(<\/span>max<\/span>))+<\/span>‘,’<\/span>+
<\/span><\/span><\/span>           quotename(<\/span>[description],<\/span>””<\/span>)<\/span>+<\/span>‘), ‘
FROM sys<\/span>.<\/span>configurations
<\/span>ORDER BY<\/span> name
— the ORDER BY just lets me view the results a little more clearly<\/span><\/p>\n

2. Receive a bunch of almost-executable text back<\/h3>\n

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):<\/p>\n

(‘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’), <\/span>
(‘Apr 15 2012 10:02AM’,’COCO’,’access check cache quota’,0,0,0,2147483647,’Default quota for the access check result security cache’), <\/span>
(‘Apr 15 2012 10:02AM’,’COCO’,’Ad Hoc Distributed Queries’,0,0,0,1,’Enable or disable Ad Hoc Distributed Queries’), <\/span>
(‘Apr 15 2012 10:02AM’,’COCO’,’affinity I\/O mask’,0,0,-2147483648,2147483647,’affinity I\/O mask’), <\/span>
(‘Apr 15 2012 10:02AM’,’COCO’,’affinity mask’,0,0,-2147483648,2147483647,’affinity mask’), <\/span>
(‘Apr 15 2012 10:02AM’,’COCO’,’affinity64 I\/O mask’,0,0,-2147483648,2147483647,’affinity64 I\/O mask’),<\/span><\/p>\n

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.<\/p>\n

3. Adjust the almost-executable text to make it executable<\/h3>\n

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.<\/p>\n

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: <\/p>\n

insert<\/span> into<\/span> dbo.<\/span>SQLServerValues 
<\/span> <\/span><\/span>(<\/span>[date] 
<\/span> <\/span>,<\/span>[server]
<\/span> <\/span>,<\/span>[name]
<\/span> <\/span>,<\/span>[value]
<\/span> <\/span>,<\/span>[value_in_use]
<\/span> <\/span>,<\/span>[minimum]
<\/span> <\/span>,<\/span>[maximum]
<\/span> <\/span>,<\/span>[description]) 
<\/span><\/span>values<\/span><\/p>\n

Then, I execute the result in the context of my management database on the correct server.<\/p>\n

What do you think?<\/h2>\n

Is there a better way? Please let me know.  Thanks!<\/p>\n

And remember: the object is to get good use out of the CMS.<\/em>  It just seems strange to me that the CMS concept is missing something that I’d consider so obvious.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 TodayRead more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-51","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/51","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=51"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/51\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=51"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=51"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=51"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}