SQLChicken.com

SQL Server DBA Tips & Tricks

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

Remote Server Administration Tools for Windows 7 (RTM)

Earlier I had blogged about the toolkit being available for Windows 7 RC but now Microsoft has officially released the RTM version of the tools. Big thanks to my co-worker Nick Piccone ( Twitter ) for bringing this to my attention. The new tools are available at the Microsoft site at the link below:

https://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7d2f6ad7-656b-4313-a005-4e344e43997d

Overview

Remote Server Administration Tools for Windows 7 enables IT administrators to manage roles and features that are installed on remote computers that are running Windows Server 2008 R2 (and, for some roles and features, Windows Server 2008 or Windows Server 2003) from a remote computer that is running Windows 7. It includes support for remote management of computers that are running either the Server Core or full installation options of Windows Server 2008 R2, and for some roles and features, Windows Server 2008. Some roles and features on Windows Server 2003 can be managed remotely by using Remote Server Administration Tools for Windows 7, although the Server Core installation option is not available with the Windows Server 2003 operating system.

This feature is comparable in functionality to the Windows Server 2003 Administrative Tools Pack and Remote Server Administration Tools for Windows Vista with Service Pack 1 (SP1).

Share

By

Database Mirroring Freak Show – SQL Quiz from Chris Shaw

Chris Shaw ( Blog | Twitter ) started another great web chainpost. He tagged Brent Ozar, Brent tagged Thomas LaRock and Tom tagged me. This particular quiz is a two-part question and here are my responses. Excuse my lack of wit and charm. It’s late, my caffeine supply is running low and my cat is eyeballing me in a most peculiar fashion.

Do you feel that you have a reliable SAN solution? If so, what’s the secret?

It’s hard for me to answer this one given that just a few weeks ago our data center (SAN included) came crashing down hard. Now given that the problem was a faulty generator test and not the SAN itself that was the problem I can’t really place blame there. Honestly I can’t say anything really bad about our SAN. Tons of disk space, tons of cache, it does what its supposed to do. We’re also in the middle of finding a replacement for a SAN administrator so what SAN solutions we have in place now could radically change in the coming months. As far as secrets go I’d say you just need to make sure you have an open line of communication with your SAN administrator. They have no idea what’s going on in your world and you have no idea what’s going in theirs. Clear communication of needs need to be there as a SAN admin worth his salt is going to know what they need to do on their end to make sure you get the best performance on your end (i.e. proper RAID levels for your LUNs depending on needs, I/O throughput, etc.).

Explain Database Mirroring in layman’s terms

Everyone else seems to be giving off-the-wall answers to this so I’ll give it a go as well. Not going to lie, took me a good portion of my drive to Melbourne last week to finally come up with an example.

Think of clustering as a conjoined twin. You’re talking to the same body. Both heads can hear the conversation but ultimately the two are stuck together because they have to share the same base trunk. Now if you were to punch one twin in the face and knock him out you’d still be able to talk to the other head but you’re still lugging around that base. Now, imagine a set of regular twins (non-conjoined). This is your database mirroring in that you have two separate entities. You knock one out but the other is still chugging along just fine. Only difference being that the location of the second one doesn’t really matter because he doesn’t have a shared trunk to deal with. I’m sorry if that explanation sucks, if you’re bored check out the whitepaper written by someone who doesn’t have a fascination for using genetic defects to compare feature sets. Ok time for me to tag a couple of unwilling victims fellow bloggers:

Kendal Van Dyke

Jack Corbett

K. Brian Kelley

Share

By

SQL Saturday #16 – Recap

Last Saturday was the SQL Saturday event at Devry University in Miramar, Florida. First off I have to say it was a great event with over 400 attendees! It was also my very first SQL Saturday speaking event. The speaker evaluations haven’t come back yet but I have a feeling I’ll definitely should have some dings as my presentation started off well but towards the end the presentation Gods reared their ugly heads and it ended on a not-so-great note with my demo not going so well. The good news is that one attendee, Argenis Fernandez ( Twitter ), gave me some good feedback as well as told me that he got some good ideas from the presentation and was excited to go back to work and implement Policy Based Management in his environment. Honestly that kind of response makes the effort all worthwhile.

From my session I went to check out Kendal Van Dyke’s ( Blog | Twitter ) session on Configuring SQL Access for the Web Developer/Admin. Kendal and I interact pretty frequently on Twitter so I was glad to get a chance to finally meet him as well attend his sessions. This was a good intro level talk on how to configure web application authentication methods in both classic ASP and ASP.NET. Some nice tricks come out of this session including how to properly add a user to the IIS_WPG group (hint: the proper way ISN’T to just add them to the group in computer management!). I’m no developer but this was really useful to me, especially as a DBA tasked with implementing Sharepoint in to our environment. I say this because Sharepoint has its share of permission issues especially if you run the app pools or web apps under a different account such as a custom AD account.

My next session was Andy Warren’s ( Blog | Twitter | LinkedIN ) session on Social and Not so Social Networking for the DBA. I’ve attended other sessions with Andy and they’ve all been great. This one was no exception. Andy is an excellent presenter and makes it look so easy. One thing that was great about this, for me, is that this is the first weekend was the first time I’ve gotten a chance to really meet and talk to Andy. It was funny because at the speaker dinner he asked “Hey, aren’t you that SQLChicken guy?” which made me laugh. I guess my self-branding is working! Anyhow, this session was really interesting as it was more of an open discussion with the group rather than a straight forward presentation. Andy brings up important things to think about in terms of networking such as don’t start building your network only when you need something (aka job search). Networking is something that can benefit you far beyond simple job searches and opportunities. In the session some of us shared how simply being connected on social networks like Twitter have actually helped improve ourselves in our current jobs. If you ever get a chance to attend this session I highly recommend you do. In fact, if you’re attending the PASS Summit in Seattle in November, Don Gabor will be holding a Pre-Conference session called Networking to Build Business Contacts. Andy highly recommends anyone attending the summit to check this session out as it will help you not only build your network professionally but help you network in general at events like PASS and SQL Saturday.

At lunch I got to sit down and have lunch with Andy Warren, Kendal Van Dyke and a few other attendees. I only mention this in the blog because I we got a chance to talk with some people who ranged from first-timers to the SQL world as well as others who’ve been doing it for awhile. Also it was interesting to get feedback on little things like how sometimes the session descriptions were a little too vague so it made it difficult to decide if the topic or level was the right one to attend. Based on this I know I’ll be tweaking my abstracts for future events. The other cool thing was getting a chance to sit and pick Andy’s brain a bit about the direction of PASS. Personally I’m excited to see what PASS has in store to continue bringing the community together. So far its been fantastic to be a part of it.

Next up was another session with Kendal with topic being Transactional Replication: Beyond the Basics.  Honestly I’m currently not using transaction replication in my shop but after attending this session I feel like I gleaned enough knowledge to be able to tackle that task if it were asked of me. Great overview of different topologies that were clearly and easily explained, as well as going over some possible pitfalls you might encounter. Very interesting topic and presented very well. Kendal will actually be doing this presentation this year at PASS (first time presenter, congrats to him!) so again if you’ll be at PASS Summit this year I suggest you check this session out.

After that I went over to Jeffrey Garbus’ ( Blog ) session on Indexing for Join Optimization which drew quite the crowd. Packed house with a bunch of people (myself included) taking a seat on the floor along the walls to check this topic out! I’ve attended Jeffrey’s session before at the last SQL Saturday in Tampa and he is a great speaker. This particular talk was actually kind of a part 2 to his earlier talk on Choosing Indexes for Performance . Even if you didn’t catch the first session this one alone is a treasure trove of great information. For instance do you know why join orders matters? Do you know the difference in performance between doing a join using the old ANSI syntax and the new? You’d be surprised. Again I highly recommend you check out any session by Jeffrey if given the opportunity.

Last, but most definitely not least, was Chad Miller’s ( Blog | Twitter ) session on Powershell and SQL Server Administration. Chad not only covers basic Powershell commands and tricks but he covers using a cool project he’s developed called SQL Server PowerShell Extensions. What PSX gives DBAs is a base set of functions that covers most common DBA tasks. One of the coolest demonstrations I saw during this presentation was the use of Powershell as an ETL tool. Chad shows you how you are able to copy data from table to another using only 3 lines of code! This presentation truly made me excited to really start learning Powershell and applying it at work.

Overall I thought it was a great event, as any SQL Saturday I’ve attended, and I had an absolute blast presenting and meeting a bunch of folks I’ve interacted with on Twitter. If you get a chance to make it to a SQL Saturday event I couldn’t recommend  it enough. You get top-notch education, great networking opportunities, awesome swag and all for free! If you don’t have a SQL Saturday event in a city near you, and you won’t be making it to the PASS Summit this year then don’t fret! The good folks at PASS have put together an exciting free training event called 24 Hours of SQL PASS. For more details on this great event check my previous post on it.

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

By

Spacecoast SQL Server User Group Meeting Tonight!

I just wanted to send out a quick note to say that I’ll be presenting tonight at the Spacecoast SQL Server User Group. I’ll be doing my presentation on Policy Based Management. After SQL Saturday last weekend I’ve learned a few things and added a small demo of EPMF to the end of it so hopefully this goes well. If you’re in the Melbourne area tonight come check it out!

Share

By

24 Hours of SQL PASS

Call Chloe and the rest of the CTU gang, grab a giant pot of coffee and your 9mm and get ready for 24 hours of pure, free SQL hotness: 24 Hours of PASS! The good folks at PASS have put together another fabulous free training opportunity for us in 24 hours of webcasts from the world’s leading SQL Server experts!

Using the latest in advanced hacking technology I’ve managed to access the PASS mainframe and get you the session schedule. Uploading to your screen now…

Session 01 (Dev) – Start time: 00:00 GMT
10 Big Ideas in Database Design
Presenters: Louis Davidson and Paul Nielsen

Session 02 (DBA) – Start time: 01:00 GMT
Using Powershell to Get the Most Out of SQL Server
Presenter: Allen White

Session 03 (PD) – Start time: 02:00 GMT
Team Management Fundamentals
Presenter: Kevin Kline

Session 04 (BI) – Start time 03:00 GMT
Delivering Good Performance Consistently with SSIS
Presenter: John Welch

Session 05 (DBA) – Start time 04:00 GMT
Using SQLdiag to Troubleshoot SQL Server Problems
Presenter: Brad McGehee

Session 06 (DBA) – Start time 05:00 GMT
The SQL Server 2008 Vanishing Act
Presenter: Peter Ward

Session 07 (BI) – Start time 06:00 GMT
Text Mining
Presenter: Dejan Sarka

Session 08 (Dev) – Start time 07:00 GMT
Tips and Tricks for Writing SET Based Queries
Presenter: Jacob Sebastian

Session 09 (DBA) – Start time 08:00 GMT
Mirrored Databases
Presenter: Thomas Grohser

Session 10 (Dev) – Start time 09:00 GMT
Working with Spatial Data in SQL Server 2008
Presenter: Greg Low

Session 11 (DBA) – Start time 10:00 GMT
Effective Indexing
Presenter: Gail Shaw

Session 12 (BI) – Start time 11:00 GMT
Reporting Services inside out the things you should know
Presenter: Simon Sabin

Session 13 (DBA) – Start time 12:00 GMT
Query Performance Tuning 101
Presenter: Grant Fritchey

Session 14 (BI) – Start time 13:00 GMT
Reporting from Analysis Services Cubes Using Excel 2007
Presenter: Peter Myers

Session 15 (BI) – Start time 14:00 GMT
Data Warehousing: Laying the Foundation to Success
Presenter: Erik Veerman

Session 16 (DBA) – Start time 15:00 GMT
Database Compatibility Settings: What They Really Do… and Don’t Do
Presenter: Don Vilen

Session 17 (PD) – Start time 16:00 GMT
Building a Better Blog
Presenter: Steve Jones

Session 18 (Dev) – Start time 17:00 GMT
Building Flexible Data Services for the Web Using the ADO .NET Data Services Framework
Presenter: Artemakis Artemiou

Session 19 (Dev) – Start time 18:00 GMT
SQLCLR or T-SQL? A Brief Survey of Performance Options
Presenter: Adam Machanic

Session 20 (BI) – Start time 19:00 GMT
Loading a Data Warehouse in SSIS
Presenter: Brian Knight

Session 21 (DBA) – Start time 20:00 GMT
What’s Simple about Simple Recovery Model
Presenter: Kalen Delaney

Session 22 (Dev) – Start time 21:00 GMT
What’s New, Harder, and Easier in SQL Server 2008 Security
Presenter: Don Kiely

Session 23 (DBA) – Start time 22:00 GMT
Analyzing File and Wait Statistics
Presenter: Andrew Kelly

Session 24 (BI): Start time 23:00 GMT
Embed Reporting Services into your Applications
Presenter: Jessica Moss

*Depending on your location, sessions may also be broadcast on Sept. 1 and Sept. 3, 2009. Please be sure to check our time zone guide for exact times. When you register using our LiveMeeting interface, sessions will be sent to your Outlook calendar for the correct time in your region.

Excellent. So your priority should now to be to head over to the event site and register for this excellent opportunity. I highly suggest you hurry up and do so as Jack Bauer doesn’t take kindly to not taking advantage of such awesome opportunities. Here’s the last guy who didn’t take advantage…

Please, for your sake, don’t be that guy.

Share

By

System Documentation: What’s Your Method?

As a system administrator it’s your job to know the ins and outs of the systems you manage. But are you the only cog keeping that particular wheel turning? If you were to leave your current position, or God forbid, something were to happen to you would your company be able to move forward without you without ill effect? This is where proper documentation becomes a crucial part of your job.

Now some would argue that if you document your responsibilities/job/systems/etc you become dispensable. While yes that could be true I dare to say that you really shouldn’t have to worry about that. In my opinion if you’re doing your job, and doing a good job at it, those who matter will notice and this becomes a non-issue. Properly documenting your system if anything then becomes an aid to you to help you do your job better. Yesterday Jonathan Kehayias (Blog | Twitter) wrote a great article on the importance of good documentation. If you get a chance I highly suggest you give that a read since it brings up some great points that include turnover, management, and compliance auditing.

Currently I’m trying to get a handle on the documentation of my systems at work. At the moment my current thought is to create a Word document template that simply has fill-in-the-blank type fields and for every new system that comes online I simply fill out a new form. These forms will be kept in our Sharepoint site and that way those who need access to them (i.e. on-call personnel, Help Desk staff, other system admins, etc.) know where to find them. Of course one could argue the problem with this method is that people who don’t necessarily NEED to see everything in that documentation have access to it. To that I say this, why not? I may take a little flack for saying this but here goes nothing. I believe the world of IT has drastically changed in the last few years (duh). Granted I’m still young but from what I’ve witnessed we are coming from shops where mainframes were king and jobs and knowledge were extremely siloed. Now before you start blasting me on silos, I know they still exist but go with me on this.

In today’s IT world there are simply so many things flying at us at all times. With the rate of new technologies coming out and with organizations trying to do more with less in this economy I think its more important than ever to make knowledge transparent across the enterprise. If someone wants to know what I do as a DBA why not let them know? That person could be interested in jumping in the administration world themselves and just need the basic knowledge and understanding to do so. Another example would be explaining to a project manager the technical pieces of the project they’re managing. How many times have you seen a project dumped in your lap and the necessary components are things that you either A) Don’t support B) Have no in-house knowledge of the necessary technologies.

From my experience knowledge dissemination becomes all the more vital as systems become more tightly integrated. Look at the Microsoft suite of products. Sharepoint is a collaborative portal designed to let people across teams and enterprises share and disseminate (damn I love this word) knowledge with ease. Well in order to stand this product up you’ll need someone who understands databases and their management/administration, someone who understands architectures and how best to implement them, maybe a trainer to let end users understand how this new technology works, desktop personnel to understand the technology and how to troubleshoot it, etc, etc.

As I’m writing I’m realizing I’m really digressing from my original point which is about documentation methodologies. I’ve laid out my general plan for documentation of systems but my original intention of this post (besides my slight soapbox rant up there) was to see how the rest of you guys/gals handle your documentation. Strict methodology and templates? Random documents thrown out on a shared drive or somewhere on your drive? None? Let me hear from you in the comments.

Share