February 11, 2014 at 8:31 am
I am following a book called "Pro SQL Server 2008 Policy-Based Management".
There is a section that tells you how to evaluate a policy against a central management server, and a particular group using a powershell script. My CMS is bnhdbsqa01, and it contains a group called wvtsqltest. I would like to evaluate policies against this wvtsqltest group. Here is a section of the powershell code it told me to write, and also the section which seems to error.
$CMSGroup = 'SQLSERVER:\SQLRegistration\Central Management Server Group\' + (Encode-SqlName $PolicyInstance) + '\AllServers\'
It's not clear to me if I am supposed to put the name of the group (wvtsqltest) where it says \Central Management Server Group\ or if I'm supposed to put that in the \All Servers\ part. I have tried both however and I get the error below.
Get-ChildItem : Cannot find path 'SQLSERVER:\SQLRegistration\Central Management Server Group\BNHDBSQA01\wvtsqltest\' because it does not exist.
I'm hoping someone has had success and tell me what they did differently. Thanks...
February 11, 2014 at 5:47 pm
I just got in touch with Jorge Segara who wrote the book. He should be by here shortly to get you an answer.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 11, 2014 at 8:39 pm
I'm not at a machine with a configured instance right now but yes, I believe Colin (Stasiuk) means for you to put the group name in there. Try it out and it should work. Let us know how it goes. Also, how are you enjoying the book? Are you finding it useful?
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
February 11, 2014 at 8:58 pm
I did try it out. I got that error below. So far it's been a good book. However the code examples are formatted a little strange. Many times the code would not work as written, I had to move it so it was on the same line as the previous one. I think the book just did that because it could not fit everything horizontally but it did not imply that anywhere. Basically I'm looking for a way to get more out of policies. We have set a few up but the alerts don't tell us what databases violated the policy, I'd like that information in the email somehow, so I'm hoping this book will tell me a way.
February 11, 2014 at 9:21 pm
Glad to hear it's been helpful. By chance, have you looked at Enterprise Policy Management Framework (EPMF) yet? You can get it for free at http://epmframework.codeplex.com . We cover it in the book but basically it handles a lot of the policy evaluation you're trying to do for you. On top of that it has real nice reports that you can drill through and discover which servers/databases are in compliance with your policies.
=============================================================
/* Backups are worthless, Restores are priceless */
Get your learn on at SQL University!
Follow me on Twitter | Connect on LinkedIn
My blog: http://sqlchicken.com
My book: Pro Server 2008 Policy-Based Management
February 12, 2014 at 3:56 am
Another vote for the Enterprise Policy Management Framework. That really made Policy Based Management take off and be useful in our organization.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2014 at 1:33 pm
I have a policy that checks for backups within the last 8 hours, and the EPM solution sounded very cool for this. Well it's not working out so well, the policy seems to ignore a fair amount of databases because no they are not listed in the report. For example the report has results for 14 databases but 19 databases exist on the server. The policy is on the central management server so it should be using that same policy across the board. If I evaluate the policy by right clicking the cms and say evaluate (instead of using job that runs powershell), then the same policy does eval each db.
More of a design flaw on our part, but we have several servers that backup later in the day, or do not backup all db's so no one policy fits them all.
February 17, 2014 at 2:56 pm
Figured something out. The CMS machine's agent runs under service_account_1, but many of the servers that I am evaluating run under service_account_2. Not all the databases being evaluated have have database permissions related to service_account_1. I gave Service_Account_1 db_reader on a couple of databases that do not show on the report and now they do. So that's great, but instead of going to each one and giving it that access, I was thinking about using a proxy. I know of a sysadmin account I could use as a credential, but that does open up a security concern I would think. Who is allowed to use credentials, hopefully only other sysadmins?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply