Policy Based Management (PBM)

I merely ready about policy based management (PBM) through short snippets here and there, but never really got that in-depth with it as I normally do with other subjects. I think the major factor was due to the name Policy. Mistakenly I automatically associated it with Active Directory. PBM was introduced with SQL Server 2008 and offers great benefits with administering SQL Server. I was conducting some research on the subject and found a post by Ashish Kumar Mehta titled: “Identify Databases Not in Full Recovery Model Using Policy Based Management” which steps you through the process of creating a simple policy and runs you through the evaluation process. Ashish covers the steps rather well so be sure to visit the post for more details. If you want to get deeper into the terminology, scheduling and such then visit a post by Ray Barley titled: “Using Policy-Based Management in SQL Server 2008

After walking through Ashish’s post I wanted to make one similar but I wanted to return all Windows Accounts and such, though his use for the Recovery Model is more practical. So fire up your SSMS 2008 and let’s get started. This is just a quick run down of how to create one. This does not get into the deep details that others like have. More of a pre-primer primer if you will.

Open SQL Server Management Studio and double-click or expand Management.

Right click on Policies

Select New Policy…

Give your policy a name

Click the Check Condition and Select New Condition…

Give your Condition a name… I know it requires a lot of naming

Select the Facet drop down and Select Login

In the Expression section click on the Field column and select @LoginType

Select the Operator (=)

Select WindowsUser for the Value and hit Enter

Click Ok to create the Condition and the Policy

Policies should be expanded now and you should see your newly created policy. Expand Condition and you should see the condition you just finalized.

Now let’s test the Policy. Right click on the newly created Policy and select Evaluate.

And this brings us to the end results

The green marks are the Windows Accounts and the Red marked rows are not.

In the event you are working on a server that may or may not have policies in place Ken Simmons provided me with the following select statement that will return information about all the existing policies.

SELECT * FROM msdb.dbo.syspolicy_policies

In the event you incorrectly spelled a policy here is a method that will allow you to rename it. Again thanks to Ken Simmons with the #sqlhelp tweet.

SELECT * FROM msdb.dbo.sp_syspolicy_rename_policy @name = 'OldName', @new_name = 'NewName

Ken Simmons (website | @KenSimmons) is also a published author on this very subject so check out his book titled: (Pro SQL Server 2008 Policy-Based Management) with was co-authored by Jorge Segarra (website | @SQLChicken) and Colin Stasiuk (website | @benchmarkIT)


Personal Investments

I am a little late in the certification arena but I have a good excuse… my dog ate my ambition! Actually I have been plugging away at my Computer Information Systems (CIS) degree with a concentration of Database Management and it boils down to a few remaining classes. Seems like I have been working towards this degree for a lifetime. I would have been finished long ago, but I had to decide if I would: 1) either run full steam ahead with my education while working full-time in a travelling required position and let my grades suffer or 2) attend part-time and maintain a 3.5 or higher GPA. I choose the latter. Even though some have told me that a (D) will still get you a degree, that is just not my character. I believe if you are going to commit, then you need to commit 100%. Especially when the investment comes with a hefty price tag. Not to mention “half-ass-ism” can be habit-forming and that quite frankly is something I am not looking to catch.

During these next few classes I have decided to pursue DBA specific certifications. I mean I am certified but the certifications that I currently hold are desktop support centric and since my role has changed it is only natural that I change my focus. To be honest when I first started in the IT field I really wanted nothing to do with databases. I was set on being a Server or Network Admin and thought databases were boring. Looking back I realize I was foolish because now I am consumed in it and wish to do nothing more but DBA work. I literally started working with SQL Server 2000 by an initiative that was put into action by my previous employer. The overall goal was to eliminate the use of paperwork for field staff. So the consultants were brought in and shortly after careful observation a project was born. Instead of equipping the field staff with paperwork to shuffle they were issued laptops with a custom developed application and a local copy of sql server (MSDE) back then. The fact we implemented Merge Replication somewhat enticed me to refer to the field staff as subscribers rather than field staff because I was trying to immerse myself with SQL Server terminology as much as I could. Not to mention I felt cool just speaking it out loud during meetings since this was a new concept to our organization.

I have heard and read about the pros and cons about certifications, but I see it as another item to prove that I can take my own initiatives along with my willingness to continue and further my knowledge and skill-set. Another available avenue for continual education. I have also heard certifications combined with a degree tends to hold more weight over those with just a certification but that is debatable. In the end I suppose it is all up to you and how you feel about it. Some say it boosts personal self-confidence and I am all for that. Why not?

The first exam I might tackle is (70-431: Microsoft SQL Server 2005 Implementation and Maintenance). I know it is a SQL Server 2005 exam, but I am more familiar with 2005 at the present time over 2008. I picked up the Microsoft Press Self-Paced book and will use that as my primary study material. I have heard and read many good things about this title. I also thought about (70-445: Business Intelligence Development and Maintenance). I cannot decide actually because I have a love for both. I see a need for Administration and Business Intelligence development. Either way I figured I would set study time to one hour a day at minimum with weekends being optional. If I can or cannot cover multiple chapters in one hour then so be it. I need to be strict with a schedule in order to stick with it. Luckily I have a license for VMware Workstation 7 which allows me to build a VM study system that I can literally destroy and revert when needed. This will provide me an adequate study platform during my progress. One thing I need to do is schedule myself for the actual test as it will make the process more official since I will be actually setting a realistic and attainable goal. Not to mention it will keep me focused since the added penalty of wasting money will add fire to my priorities and will align my studies.

I am interested in knowing what you would pursue first? SQL Server Administration or Business Intelligence?