Google Search » Database Underground | Sean McCown » February 2008

February 25, 2008 | Comments: (0)

Katmai's Cool DBA Feature

TAGS: SQL Server

OK, I said I would be looking for a DBA-specific feature, and it looks like I found one. I knew about this before, it just didn't come to mind the other day when I was writing the other blog.

Katmai has this cool feature where you can run code against a group of servers. And I don't know if it's specifically for DBAs, but it's certainly well suited for us. I'm constantly having to run code on a number of servers like giving a new user rights to multiple boxes, or deploying an SP to a lot of boxes. This is a fabulous feature and it looks like MS got a couple things right here.
1. When one box is unavailable it continues to connect to the other boxes.
2. If one box has an error, it doesn't affect the other boxes.
3. You can register a single box in multiple server groups so you can group them logically by application.

Things that didn't get done as well as I'd like:

1. It would be nice to have the same thing at a DB level on an individual box. So instead of having to create a cursor or use the built-in MSforeachDB cursor, I could group the DBs on my server to admin them all at once.

2. I wish you could just copy an instance of a registered server that just logically belonged to different groups. The way it's currently done, you can register a server in multiple server groups, but they're all separate registrations. If I need to change my password or even auth method in that registered server, and I've got it in 25 groups, I have to change it 25 times.

Those aren't really complaints, just things that would have made the feature perfect. Still though, it's a really nice feature, so kudos MS.
I've put together a short Camtasia just to demonstrate how it works.

Posted by Sean McCown on February 25, 2008 09:06 AM

February 21, 2008 | Comments: (0)

Talking to Myself

TAGS: SQL Server

Sometimes it's like I'm just talking to myself. I just got a chance to poke around in the Katmai SSMS and I really hope it's not feature complete. I've talked before about crossing the finish line and how it's the little things that count. Well, here are a couple of the little things that are getting overlooked.

The job monitor doesn't remember your refresh settings. I would say that most of the time, and by most I mean well over 95%... but most of the time when I'm in the job monitor it's to monitor jobs. And to have to reset my refresh settings every time is pointless. Why can't the GUI remember my refresh setting? That actually seems more intuitive than making someone reset it every time. Here's an idea... create a small file on the HD somewhere that holds settings like that, and read it when the time is right. It doesn't have to be fancy. But you could store all these things in there... refresh settings, filter settings, etc. Think about it for me will you?

Then next thing oddly enough has to do with jobs too. When you bring up the list of jobs, it still doesn't show you which ones failed. It's really sad that this was really easy to do in SQL2K, but since Yukon, we have to go through another step to get this info. Even worse, if we open up the job monitor to see failed jobs, again, we have to reset that filter every time. So it's really more than one step to see the list of failed jobs. We can see the disabled jobs in the list, so why not the failed jobs?

You know, like I said above... it's the little things that really count. Like being able to save filters in the SP menu, or in the job monitor. These are things that would be really helpful.

It just goes to show once again that MS really doesn't give DBAs much consideration. Everything is always about developers. Sure, they have the new intellisense, but all those new language features are all centered around development tasks and not DBA tasks. Not a single DBCC, Backup, Login, etc command made it into the intellisense. Nothing for DBAs. Now, this really only counts for GUI features because there are some engine features for DBAs. The DMF and new auditing features come to mind. But when it comes to actually making a DBAs daily life easier and making their day to day tasks quicker and more efficient, all you hear are crickets.

I've personally been asking for some very specific features for a long time now and I haven't seen one of them show up. I've even talked to the tools guys in Redmond and they all shook their heads and agreed with me and said y, wow, that would be great... but it still hasn't happened.

Among these are:
1. sharable snippets with nicknames. You can setup a code snippet that defines a block of code for say getting your top query offenders. Then to activate it you just type something like "topqueries" and it replaces that with the snippet you defined. For those of you who remember the old SQL IDE from Imceda you know exactly what I'm talking about. But this feature would have huge benefits for DBAs who need to troubleshoot things on the fly and don't have time to check code out of VSS or TFS.

2. Server groups with different backgrounds defined. So you could define all of your prod boxes in the GUI and assign anything marked as prod a background of say light pink or yellow... whatever... that way, you get to visually see when you're working on prod and it's harder to make a mistake because you thought you were in dev. It's these visual queues that make things easier.

3. Undockable windows. A lot of us have dual monitors these days and when we're working on a prod issue it's really helpful to be able to see 2 panes at once. Say we're query tuning and we want to undock the execution plan window so we don't have to keep switching back to it. Or we're trying different things in a deadlock or blocking scenario and we want to see the sp_who window (or these days, sys.sysprocesses) to see when it clears up. Having undockable windows is nothing but helpful.

4. Along with the one above how about auto-refresh query windows. We should be able to define that a query in a window refreshes at an interval. The sp_who scenario is perfect. I shouldn't have to switch to a new window and run the query again just to see if the block has cleared up. I'd like to be able to type my command in a window, undock it, and have the window run that command say every 1sec or every 5secs so I can just see what it's doing. Why do I have to keep doing it by hand?

See, these are all DBA functions that we have to deal with every day. But what GUI functions do we get? None. It actually takes us more work today to get a list of failed jobs than it did back in 2000. It's actually harder for us (in Yukon anyway, I'll check on Katmai soon) to troubleshoot SSIS packages because we not only have to export the packages and open them up in VS, but if we want to do anything with them, they have to actually be part of a project. What the hell kinda crap is that? Do you have any idea how many miscellaneous projects I have on my workstation because I had to troubleshoot a single SSIS package once? I finally created a troubleshooting shell project and I just trade the packages out, but it's ridiculous to have to do something like that for a simple production problem.

You can chalk this up to a form of sticker shock. Every time a new build comes out I always hope that some things have been made easier for DBAs in the GUI. And I'm always disappointed. The problem is that you've got developers writing code for DBAs. These guys don't know what it is to work in a real prod shop with 100 things to do at once.

Anyway, I'm going to keep looking for DBA enhancements in the GUI and I'll report if I find anything that's actually easier than it was 10yrs ago. But there's still no way to easily see and change server-side traces, and we've been commenting on that one for years.

You guys shoot me things if you've discovered something I haven't. But I'm using the Katmai GUI for my prod work now so if there's anything in there, I'll find it. I am glad that I'm finally able to use it though. This is the first CTP of the cycle that allows us to import our existing server list from Yukon.

Posted by Sean McCown on February 21, 2008 07:13 AM

February 20, 2008 | Comments: (0)

New Katmai Install Notes

TAGS: SQL Server

OK, I downloaded and installed the new Katmai CTP today. And for those of you who don't follow Microsoft that much, CTP is just a fancy way of saying beta. Actually, it's not really all that fancy is it? Oh well...

Anyway, here are my install notes so you'll know what to expect. Now, I did this on my XP box and I only installed the tools so far, so take this with a grain of salt.

I had to uninstall the previous CTP by hand, but it uninstalled completely and ready for the new code to be installed. I've gotten on to them in the past for sloppy uninstalls, so good work guys! The uninstall does require a reboot, so be prepared for that.

The first thing it did was install the .net framework 3.5 which caused one of those annoying reboots. Then when I came back up, it installed the setup support tools and powershell. No reboot required.

Then the Installation Center finally appeared. The installation center is basically your menu of install choices. It has links for several things having to do with setup. Here's a screenshot.

So I'm actually installing as I write this blog, and I just got a wonderful surprise. Even though it didn't tell me I needed to reboot after it installed the setup support tools and powershell, it just ran a check to give me my features selection and it's now saying I need another reboot. We have roaming profiles here, so it takes me 15mins to reboot my box. So I'll be back in a few mins. Guess I'd better not forget to save my work here.

~15mins later...

OK, I'm back now and it passed the inspection this time... YAY!

OK, so I chose my features, and I decided to go ahead and install a 2nd instance, so I'm doing a full install minus BI.
Now I came to the section where it's asking for user accts for the services. There's no dropdown list for the local accounts and when I tried to use the local system acct, it thought forever and then kicked it back to me. So I pasted in the NT AUTHORITY\SYSTEM from one of the other services. This is a step backward from before when we could just set everything to local system and then work them out later, which is what I prefer to do.
And of course, I did lookup the proper name of the local system acct and it's NT AUTHORITY\LocalService. However, setup won't let me run the Agent under that acct... whatever.

So getting past that...

You can configure specific users to have admin rights to the DB during setup now. That's nice. Don't forget to look everywhere though. These are tabbed screens so it's easy to miss something.
Here, I've blanked out my user acct, but the rest is ok.

So at the point in this pic, it's already gone through all of the features and listed them as 'In Progress'. Then it listed them as 'Pending'. So looking at the underlined section there, it looks like nothing is going to be marked as complete until the entire install is done. It's a little annoying cause it would be nice to see the actual progress.

OK, the install is finished now. And it requires ANOTHER reboot. The SQL program menu didn't show up on mine until I rebooted.

So the bottom line is this... the install isn't bad, but it's not exactly what I'd call smooth. All the reboots are ridiculous, and it takes quite a while to install.
Frankly, Oracle 11g is still beating the pants off of MS in terms of an easy install. We should be able to point to a default install with very little intervention. I didn't see a place to record an unattended install so I'll be looking at install in detail on my actual servers over the next few days. So while the install isn't bad, SQL2K was actually easier to install.

One thing I wanted to make sure I tied into install was the user account list. Remember in that pic above where I was able to assign a windows user account to SQL? I was hoping that meant that the builtin\admins group was gone by default inside SQL, but it's not. It would've been very nice for the install to add my service accts as logins, and whatever user accts I added manually on that screen, and left builtin\admins out. That would have made for a nice, secure default install. Oh well... maybe next time.

Ok, I'm going to go play with install some more and let you guys know what I come up with.

Posted by Sean McCown on February 20, 2008 09:29 AM

February 19, 2008 | Comments: (0)

Search and Replace

TAGS: General Database

In writing some code this morning I found it necessary to make some major changes that involved search and replace in SQL Mgmt Studio. I was a little disappointed in the ability of both the SQL tools and Visual Studio to perform rich replace routines.

Here's a simple example of what I was trying to do:

Create View myView
as
Select * from myTable where col1 = 63

I had a couple hundred of these statements in the script and they needed to be changed to:

Create View myView
as
Select * from myTable where col1 < 50
GO

Replacing '= 63' with '< 50' is the easy part.
But try putting that 'GO' statement on the next line between all of them. If there's a way to do it in the tool it's not apparent.

This is why I always keep a version of PrimalScript handy. It has a search/replace util that I haven't seen matched anywhere else. And I wasn't even sure if it would do what I wanted, but it did.
What can I say. I still love this tool. Even though I don't use it for much of my scripting anymore (I just don't do that kind of scripting at my current job), I always have it loaded on my box for emergencies like this.
There are some things I'd like to see PrimalScript's search/replace do, and maybe I'll shoot them a note about some of them. And no, they don't pay me for stuff like this. I actually just love this tool.

Here's a small Camtasia I put together to show you how it works.
SearchReplace.wmv

Posted by Sean McCown on February 19, 2008 10:44 AM

February 07, 2008 | Comments: (0)

Still Just Disappointed

TAGS: Microsoft

OK, I downloaded Visual Studio 2008 a couple weeks ago. I've got a couple AJAX sites that I wanted to start working with in the new environment. I also wanted to get more involved in LINQ.

However, I'm still just disappointed in VS08. It's got some decent features as far as deployment goes, but man, this thing is still just dog slow on vista. I don't know if the problem is vista by itself, or the combo, but it's quite often just a beating to do anything.

A good example of this is sometimes I will make a small change to a page. Maybe the text on a label has a couple transposed letters. So I'll go in and switch the letters and hit save. Now I'm waiting for no less than 2mins while the GUI freezes up and gives me the hourglass because it's trying to workout all that heaving coding I just did. Typically I code at night and something similar to this happens several times in a single coding session. I had the same problems in the last version so it's not like it's anything new, but I was really hoping for more this time.

Well, and like I said, this may have more to do with vista than anything else. I haven't tried it on XP yet. But I did (and still do) have very similar problems in VS05 on my XP box, so my gut tells me it's not all vista.
However, I created a new folder yesterday on my vista box. It's a very simple operation. I typed in the new folder name and hit enter. Then I waited for what I timed 2.5mins for it to unfreeze the window before I could do anything at all. Vista's not exactly a speed demon but it shouldn't be doing stuff like that either. I've got a lot of the cool stuff turned off because they brought down my box too much. And vista still performs worse than Win95 ever did. That's sad isn't it? Win95 actually performed faster than vista. Vista's got so many cool features, but most of the time it pisses me off so bad I just wanna throw it out the window. And my vista box isn't a piecer. It's a very beefy box with tons of RAM. So it should be able to handle vista w/o too many issues.

What brought this up was the launch later this month. I'll be going to the big launch in L.A. and I doubt any of this will come up. Maybe I'll be lucky enough to run across someone on the VS or vista teams that I can complain to. Because frankly, I'm just disappointed in both of these products and if Longhorn has the vista core, what are we in store for with our mission critical apps if we put SQL or Exchange on it? This is a valid concern since everyone I talk to almost has vista performance problems.

Posted by Sean McCown on February 7, 2008 06:30 PM

February 06, 2008 | Comments: (0)

Database Insurance

TAGS: General Database

It's difficult having an IT shop these days. You have to manage different types of people along with different priorities from your wide-ranging customer bases. And as an IT director, you want to get the biggest bang for your buck. So you have devs and it's best to have them actively writing code as much as possible. That's how you get your money out of them. And if you have some really expensive sr.-level devs, you want them working on sr.-level dev stuff at all times. You don't want to pay $100K+ for a dev to have him write basic HTML pages.

We had the same thing in kitchens back when I was a chef. If you've got a worldclass chef that you're paying a lot of money, you want him standing there doing what only he can do as much as possible. That is afterall, what makes it worth having him there. So you don't want him running to the walk-in for his own supplies or whipping his own cream. You have apprentices for that.

The situation is different with DBAs though. DBAs are more of an insurance policy. Sure, they're there to guide your data efforts, but they're mainly there in case of a serious disaster. A lot of IT directors get upset when their DBAs aren't completely busy at all times. Afterall, good DBAs are expensive so you want them sitting there doing whatever it is that they do as much as possible. However, that just isn't so. You don't want your DBAs so busy that they don't have time to fully monitor your systems. And you don't want them so busy that 10 projects fall behind because there's a disaster and they have to be pulled onto that instead.

DBAs are just different from devs. A dev will never be called into action with a downed server. Nor will he get a call in the middle of the night that something's running too slowly. So you can work your devs 100% of the time while they're at the office. But DBAs actually have more downtime than you'd expect. Sure, they're busy, even busy as hell sometimes, but not 100% of every day.

Again, DBAs are an insurance policy. You definitely want them around when something happens to your data. However, be mindful of the type of DBA you have in your shop. If you want someone to work on all the projects and do lots of coding, etc, then you want a database dev, not a DBA. DBAs are systems folk. They make sure things run smoothly. So ideally, they shouldn't be attached to any projects, or at least very few.

Posted by Sean McCown on February 6, 2008 12:09 PM