Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

PowerShell and PBM Expand / Collapse
Author
Message
Posted Tuesday, February 11, 2014 8:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:54 AM
Points: 65, Visits: 281
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...
Post #1540239
Posted Tuesday, February 11, 2014 5:47 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 14,802, Visits: 27,278
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1540512
Posted Tuesday, February 11, 2014 8:39 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:30 PM
Points: 136, Visits: 641
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
Post #1540540
Posted Tuesday, February 11, 2014 8:58 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:54 AM
Points: 65, Visits: 281
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.
Post #1540542
Posted Tuesday, February 11, 2014 9:21 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 12:30 PM
Points: 136, Visits: 641
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
Post #1540548
Posted Wednesday, February 12, 2014 3:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:48 AM
Points: 14,802, Visits: 27,278
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1540613
Posted Monday, February 17, 2014 1:33 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:54 AM
Points: 65, Visits: 281
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.
Post #1542268
Posted Monday, February 17, 2014 2:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:54 AM
Points: 65, Visits: 281
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?
Post #1542294
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse