After months of deadlines, late night writing sessions, getting my ass handed to me by ADD and editors, and a loving wife pushing me to write some more the book is now done! A HUGE thank you to Ken Simmons for inviting Colin Stasiuk and myself to be a part of this project. Also huge thanks to all the folks at Apress for doing a great job pushing this along as well as Tom LaRock, whose technical editing on this book was a great asset. According to Amazon, the book should be available April 27th so make sure you buy plenty of copies for you and your loved ones. After all, nothing says “I love you” like the gift of policies that stop developers from horrendous naming conventions Policy-Based Management. You can also get the book directly from Apress by going to http://www.policybasedmanagement.com.
Upcoming Presentation for NJ SQL User Group
Just wanted to send out a quick note that I’ll be presenting via LiveMeeting for the New Jersey SQL User Group next Tuesday, February 16th at 6:40 PM EST. I’ll be doing my Policy-Based Management talk. I will post the link to the presentation here when I get it.
NJ SQL User Group meeting page
LiveMeeting link: https://www323.livemeeting.com/cc/usergroups/join?id=B6MF59&role=attend&pw=
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.
OPASS User Group Meeting: Review
Well last night I presented my Policy Based Management presentation for the Orlando SQL PASS User Group aka OPASS. The meeting was held at the End-to-End Training (now called SQLShare.com) offices which is a nice facility ran by Andy Warren (Blog | LinkedIn). The meeting started off with a short bit of networking where Andy has everyone introduce themselves to their neighbors and get some discussion going. I thought this was a nice little touch and lets people work on their networking skills.
First up for the night was a mini presentation on Backup Basics with Todd Holmes (LinkedIn), a DBA for Channel Intelligence in Celebration. The mini presentation is a 15-minute presentation slot that Andy came up with to encourage new speakers to cut their teeth on public speaking and technical presentations. Todd did a great job with such a broad topic and even went the extra mile in showing examples via T-SQL code. Todd will also be doing this mini presentation at the upcoming SQL Saturday #21 in Orlando.
After Todd’s presentation there was a short dinner break and I got setup for my PBM presentation. Andy said he was curious to see an hour-long presentation went on Policy Based Management since he thought it was a topic that could be covered rather quickly. Funnily enough my presentation ran just a tad over an hour and I could have kept going! There were some hiccups here and there with my VM taking a little longer than I would have liked to open certain things but demos didn’t blow up like they did at the last SQL Saturday. I also got a chance to show the audience EPMF in action (sort of). I showed the script run that used PowerShell to apply existing policies and dump results into a database repository. The example failed because I tried to open Reporting Services page on VM which had the hardened IE settings enabled that didn’t allow scripts to run so I wound up just showing a screenshot of the dashboard view. Hopefully this demo helps people take SQL 2008 and PBM back to their jobs and look like rock stars for virtually no money (except for cost of SQL 2008 Standard license after they see how awesome this is).
After the meeting I stuck around and talked shop with Andy, Jack Corbett (Blog | Twitter) and Kendal Van Dyke (Blog | Twitter) which was pretty awesome as we talked about all things SQL. Always a good time when you get quality geek time in. Overall it was a great time and a big thank you to the group for having me out there. If you’re in the Orlando area make sure to check out the group!
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.
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).
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…’
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.
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’.
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:
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…’
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.
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.
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.
For this particular policy you should see something similar to this be output to SSMS:
Declare @condition_id int EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'System and User', @description=N'Allows you to select both user databases as well as all system databases.', @facet=N'Database', @expression=N'<Operator> <TypeClass>Bool</TypeClass> <OpType>OR</OpType> <Count>2</Count> <Operator> <TypeClass>Bool</TypeClass> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <TypeClass>Bool</TypeClass> <Name>IsSystemObject</Name> </Attribute> <Function> <TypeClass>Bool</TypeClass> <FunctionType>True</FunctionType> <ReturnType>Bool</ReturnType> <Count>0</Count> </Function> </Operator> <Operator> <TypeClass>Bool</TypeClass> <OpType>EQ</OpType> <Count>2</Count> <Attribute> <TypeClass>Bool</TypeClass> <Name>IsSystemObject</Name> </Attribute> <Function> <TypeClass>Bool</TypeClass> <FunctionType>False</FunctionType> <ReturnType>Bool</ReturnType> <Count>0</Count> </Function> </Operator> </Operator>', @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT Select @condition_id GO
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.