SQLChicken.com

SQL Server DBA Tips & Tricks

By

Policy Based Management: A Brief Walk-through

Over the last few months I have been doing the rounds at various user groups and SQL Saturday events presenting on Policy Based Management. In the spirit of my on-going SQL University project as well as the upcoming book I’m co-authoring with Ken Simmons (Blog | Twitter) and Colin Stasiuk (Blog | Twitter), I’ve thrown together this brief video walk-through on Policy Based Management.

Share

By

Upcoming Talks:Orlando SSUG and SQL Saturday #21

It’s going to be a fun month! Next week I’ll be speaking at the Orlando SQL Server User Group on Policy Based Management 101. I also got confirmation that not only was my PBM presentation accepted for SQL Saturday #21 on October 17th but I’ll also be doing a MINI session on SQL & Twitter:A Perfect UNION !

I’m REALLY psyched about SQL Saturday as there are going to be some awesome presenters there such as Andy Leonard (Blog | Twitter), Ken Simmons (Blog | Twitter), Joe Webb (Blog | Twitter), Brian Knight (Blog | Twitter), Kevin Kline (Blog | Twitter), Andy Warren (Blog | Twitter), Buck Woody (Blog | Twitter), Kendal Van Dyke (Blog | Twitter), Chad Miller (Blog | Twitter) and many more! The speaker lineup looks like a veritable PASS Summit East Coast edition. If you can’t make it to the Summit this year (and I highly encourage you to do so if you can) then definitely try to make it to SQL Saturday #21!

And as if the lineup of talks at the SQL Saturday event wasn’t enough to blow your mind, the week leading up to it there is a week of day-long training seminars! Again, this looks like a rock star line up featuring Andy Warren, Brian Knight, Kevin Kline, Andy Leonard and Buck Woody. Each session is $150 and WELL worth the investment.

Share

By

Policy Based Management: Against All Databases Condition

Recently after one of my presentations a Twitter-buddy of mine, Argenis Fernandez ( Blog | Twitter ), showed me something interesting. By default when you create a new policy the against targets default says ‘Every Database’ (see screenshot below).

all-databases1

Well this is a tad misleading as it apparently means “every” in the sense that it will apply this policy against every USER database. This means if you want to ensure autoshrink is not enabled on your system database (master, msdb, model, tempdb), for example, applying this policy with this default target setting won’t work. So how do we create a new condition/target that allows us to run a policy against both user AND system databases? We’re going to create a new custom condition that allows us to look at both types of targets.

First click on the down arrow next to Every and go down to the bottom of the list and select ‘New condition…’

new_condition1

First you want to give this condition a new name. For this demo we’ll call it ‘Every database – User and System’. The next step is choosing the Facet as well as the property we want to look at. For this particular condition we want to use the ‘Database’ facet (which is the default facet selected). Next click on the box under Field and click the down arrow to view all of the properties available under that facet.

new_condition3

The field we want to select for this is the @IsSystemObject property. Once you select it, make sure your Operator value is set to ‘=’, then click on the field under Value. Once again, when you click on the field and click on the arrow you are given pre-set options. For this value select ‘True’.

new_condition4

Now that you’ve selected your first clause that states to apply to system object, we will create a second clause that applies to user objects as well. To start click on the next line where it says ‘Click here to add a clause’. When you click on it you’ll notice you get a new row to be able to create a new expression. For the first column (AndOr) we want to select ‘OR’. This is important because if you select AND in this instance, you’ll never return a valid entry since an object is either a system object OR a user object, never both. Next select the @IsSystemObject again from Field value and finally set the value to FALSE. Your Expression should now look like this:

new_condition5

Click OK and you should now see your new ‘Every database – User and System’ in the Against Targets box. Click OK to close your policy. To test it apply your policy against your database via your Central Management Server (CMS). To do that drill down to your server, right-click the database click ‘Evaluate Policies…’

new_condition6

From the Evaluate Policies box click on the ellipses box to select your Source. Your source is where your policies are located. With PBM you can either select individual policies (XML file) or you can select a SQL 2008 Server that has the policy you want. In my case I am selecting a server that contains the policies so all of the policies on that server will appear in my list. Select the policy we modified with the new target.

new_condition7

To verify we’re using the right target click on the policy name (in this case ‘Database Auto Shrink’). This will open the policy itself and you can verify that the correct target is in place. You should see your ‘Every database – User and System’ in the Targets window. Click Close to close the policy. With your policy checked click on the Evaluate button to proceed. The policy will then run and show you your results. In the target details box you should see your policy has run against all your databases, both user and system.

user-and-system-databases

There is also a way to create this through a script. I created this particular via the SSMS gui but if you want to export it you can let SSMS create the T-SQL Script for you and share the code. To do this right-click on the condition in SSMS, Script Condition as, CREATE TO. To have it go directly to creating the .SQL script file for you select ‘File…’ otherwise lets select the ‘New Query Editor Window’ so we can see our result.

export-condition

For this particular policy you should see something similar to this be output to SSMS:

And that’s it! You now have your T-SQL Code to create your new condition on other SQL 2008 Servers. Note of caution, however, be careful about how you use this as messing with system databases can be disasterous if you’re not careful.

Share

By

Spacecoast SQL User Group Meeting: Review

After writing up my review for my presentation at OPASS I realized I hadn’t done one for my visit to Melbourne for the Spacecoast SQL User Group! A thousand apologies Bonnie and crew!

I arrived early in Melbourne so I spent some quality time at Starbucks for a bit before the meeting which gave me the opportunity to test and retest some of the policy demos so I didn’t repeat my blunders from the previous weekend at SQL Saturday in South Florida. When the time came around I got over to the Spacecoast Federal Credit Union headquarters building where the meeting was held (which is a beautiful building btw). The group is small but very enthusiastic crowd in that they ask a LOT of good questions. The PBM presentation went without a hitch which was nice and the presentation actually took a positive unexpected turn when we started discussing virtualization. I wound up giving a mini presentation on VMware and virtualization and how it all worked which was pretty cool.

Afterwards a few of us went over to the questionably-named sports bar called The Rendezvous in the Holiday Inn to socialize and talk shop a bit. It was interesting to hear about how Bonnie got started in the SQL world and how she came about establishing the User Group. Want to hear the story yourself? Then get over to one of their meetings! They meet on the second Thursday of each month so if you’re in the Melbourne/Cocoa Beach/Titusville area go check them out!

Share