May 09, 2008 | Comments: (0)
Microsoft's backup compression
I still have a hard time getting jazzed about Katmai's backup compression. Again, it's probably because I've been using LiteSpeed for so long, and it's just not news anymore. Not to mention it still has licensing problems.
I believe that I talked to someone at MS about this a while back and they said that it was their belief that it was typically enterprise customers who had backups large enough to need compression. That's just not so.
There are plenty of dev systems and lesser systems that need compressed backups. Look at it this way. It's all relative, right? Because if you have a 30GB DB, which isn't every big, but you've only got a 100GB disk to do your backups, then you'll run out of space pretty fast. And what if you've only got 50GB? You may not have enough space to house your full backups and all the logs. So it's not just huge systems that need compressed backups. It's any system that is space constrained.
And with companies moving DBs around like they do, I just feel that it's going to cause problems where you least expect it. What if I have an enterprise system with compressed backups and I need to restore it to a system that isn't enterprise? Say my dev box is standard, what then?
And I still say that it's best to manage all your backups the same way. If you're going to compress your enterprise backups with the native backup routine, and use something like LiteSpeed for the others, then you're only inviting trouble... or complication at the very least.
So while I suppose I'm glad that MS is at least thinking in that direction, I just don't think that making it available to only enterprise customers is the best thing. And it certainly isn't going to hurt the 3rd party vendors' business any. But if they were to open it up to enterprise and standard, then they could do some real damage.
Posted by Sean McCown on May 9, 2008 07:25 AM
April 17, 2008 | Comments: (0)
First look at Red-Gate's data generator
OK, I told you all that I'd do a Camtasia of Red-Gate's data generator, so here it is. Hope you like it.
It's not showing up very well because of the right margin, but you can download the file below.
Download file
Posted by Sean McCown on April 17, 2008 09:05 AM
April 01, 2008 | Comments: (0)
Every year April 1st sneaks up on me and Steve Jones gets me with his fake SQL Server newsletter article. This year is no different. As I read his article a lot of things went through my mind. First and foremost, how the hell did he trump me on such big news? Then it was, wow, that's cool... I'm glad they finally listened. Because a lot of the stuff he talked about, I've been talking about recently both with MS and with other DBAs as well as in online articles. So this was on my mind anyway.
Anyway, kudos to you again Steve. Consider me gotten.
Posted by Sean McCown on April 1, 2008 06:38 AM
March 27, 2008 | Comments: (0)
OK, at long last, the MS guys have sent me a photo that they doctored. You can see the original in this post. And since I told them that I'd post it, here it is. This is the 2nd time that I've seen Oracle park taxis with their logo outside a SQL launch event, so what do they expect MS to do? And if MS ever does the same thing outside an Oracle launch event I'd be happy to post any pics they want me to post as well.
So anyway, let's have some fun.
Posted by Sean McCown on March 27, 2008 06:40 AM
March 07, 2008 | Comments: (0)
I remember a conversation I had at the Redmond campus with the program manager (at that time it was Paul Randall... now I think he goes by Paul Tripp, but all the same he's not a MS anymore.). Anyway though, he was asking me about my favorite Katmai features and I listed 2 or 3, and he was astounded that my favorite feature wasn't backup compression. They were particularly proud of that one. It turns out the SQL team at MS had never even conceived that someone wouldn't just jump out of their skin for the chance to have backup compression in the product.
My reasoning is sound. And it's not that the feature itself isn't cool, or even long overdue. It's that the implementation itself isn't practical to real DBAs. Let's look at what we have today and how the native Katmai backup compression will stack up.
Today, if you want backup compression you go with one of the big vendors... Quest LiteSpeed, Red-Gate SQL Backup, Hyperbac, etc. And these products bring you features like object-level restore, resource management, and central reporting. And those are just the top 3, there are more features that come with products like these that make them attractive. One of the most important to any DBA is centralized reporting because it's far easier than writing routines to cycle through all your servers to pull all the backup stats into a single report. Those routines will usually consist of an SSIS package that does the cursoring or putting a job on each box to ship the job info up to a central server repo. Either way it's manual work with moving parts and queries that can be difficult to work out properly.
So what does Katmai's backup compression offer? Well, it offers you the ability to compress your backups. Period. That's it. There's no centralized reporting, there's no object-level recovery, there's no resource management. Resource management is another one that's important. Sometimes, I want to open up my compression to all CPUs, or maybe 1/4 of the CPUs, etc. And you just don't have that kind of ability with native SQL backups. Another aspect of resource management is the ability to choose your compression algorithm. Native SQL compression offers you only one. You either compress the backup or you don't. The other tools offer you several algorithms. The advantage of this is clear. On a prod system when you need good compression, but you also need the speed, you can set a lower compression rate. But on dev systems where the backups can take longer, but need to be much smaller, you can use a higher compression rate. Typically dev systems will hold several backups of the same DB so you can restore back to several points in time. This is why you typically want higher compression on these systems.
So now that I've talked about the actual features, what about the implementation? How has MS's specific implementation affected it's usefulness in the field?
Well, simply, it's too limited. Even if you could put up with the missing features, backup compression in Katmai is limited to the enterprise edition. Personally, that's unacceptable as a backup solution because I don't want to have several backup processes to manage.
Let's look at an example. You've got 30 SQL2K boxes, 50 SQL2K5 boxes, and let's say you're really keeping up with the times and you've got 100 Katmai boxes. And of those Katmai boxes you've got say 3-5 with enterprise installed. Just to be generous I'll say 5. So you've got 5 boxes with compressed backups. What about the rest? You still need something for those too, right? Just because they're not enterprise doesn't mean they're not big or important. OK, so let's say you go with LiteSpeed... or it could Red-Gate, it doesn't matter in this discussion. You're getting centralized reporting for all of your boxes except your 5 native SQL backup boxes. So now you have to find a way to merge those boxes' reporting info into the LiteSpeed repo so you can report from one place with one method. And the time and effort it may take to do that and support it may be more trouble than it's worth. And you can't just leave it out because now you're supporting 2 methods for backups and that's just not good practice. So you're better off installing LiteSpeed (or Red-Gate) on your 5 Katmai enterprise boxes and not only getting the extra functionality, but also having a single method to support. It's really a no-brainer. Why would you limit yourself like that? Even if you had 20 servers and they were all Katmai enterprise, the other features still make the 3rd party tools more attractive.
So while it's nice that MS has given us compression, I think that limiting it to enterprise makes it an extremely incomplete solution. And I think that because you can't use it with anything lower than Katmai also limits you tremendously.
I have nothing bad to say about the compression itself though. In fact, just the other day someone told me that he knew someone who tested Katmai's backup compression and he was getting better compression than LiteSpeed. Now, that's very subjective because what LiteSpeed compression level did he use, and what did the data set look like, etc? I've talked a lot on this topic in the past and the algorithm being used matters depending on what the data looks like. But it sounds like the Katmai team got it right on some level anyway. I'll be putting backup compression to a whole battery of tests here pretty soon so I'll know the full story in pretty short order.
So that's why backup compression isn't my favorite feature in Katmai. I've heard good rumors about its abilities, but it's a definite case of coding in a vacuum. It has practically no real-world application compared to what we've already got, and implementing it in any large shop will only complicate the backup solution as a whole. And it's pretty clear to me that MS isn't going to be putting any of these other guys out of business anytime soon. Maybe eventually it'll become worth it to drop the other solutions in favor of native SQL compression though. Afterall, the other vendors have been doing it for years and MS just started. I remember when LiteSpeed started. They didn't have all the features they have now either. Neither did Red-Gate. So MS is allowed to get into the game the same way the others did. But that doesn't mean they're currently ready to compete. Who knows, maybe we get a couple releases into the future and they'll have a richer feature set. But for now, it's just not feasible.
Sorry guys, that's just the way it is.
Posted by Sean McCown on March 7, 2008 06:57 AM
March 05, 2008 | Comments: (0)
L.A. SQL Server 2008 launch pics
Well, as usual I'm a few days behind getting my launch pics posted. There weren't any really interesting things to snap this time, but as usual, Oracle parked taxis outside with their logo on them. Personally, I think it's funny. So here's me at the cabs with a couple of the Microsoft guys. They said they were going to Photoshop the pics and I said if they did that I'd post them. So let's hope they actually do it. When they do (if they do) I'll put them up right away.
Posted by Sean McCown on March 5, 2008 11:38 AM
March 04, 2008 | Comments: (0)
I've come across some new functionality in SSMS that you should be aware of. By default, you can't perform any action, like inserting columns, that forces the table to be recreated. But there's a flag for that.
I've put together a short Camtasia just to demonstrate how it works.
Hope this helps someone.
Posted by Sean McCown on March 4, 2008 12:10 PM
February 25, 2008 | Comments: (0)
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)
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)
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
January 29, 2008 | Comments: (0)
As I sit here typing I find I really don't know what to say. Ken Henderson, the author of the Guru's Guide to SQL Server series of books (and others), died Sunday. Ken was a good friend of mine and I just talked to him not long ago.
I have no words to express my deep sadness of this event. I've been reading other blogs on this and everyone is crediting him with how much he's taught them and how much he'll be missed in the community. And while that's true it saddens me even more that nobody seems to be expressing any personal loss. Ken was a friend of mine and while he taught me a lot about SQL, and while I do owe him a lot in that area as well, my heart just breaks at the thought of never being able to see him again.
He was like a hippie turned computer nerd. He was the coolest guy I think I've ever met and I know his family is devastated. I'm going to miss sitting in his office at MS talking about just anything and everything. It was always so comfortable there with him. He was a very private person and I consider myself lucky to have been brought into his circle. From the day we met we just hit it off right away.
Ken, I don't know what to say brother... the community will miss your brilliant writing... but screw them. Those of us closer to you will miss YOU.
I just don't know what else to say.
Posted by Sean McCown on January 29, 2008 07:45 AM
November 26, 2007 | Comments: (0)
I downloaded the new Katmai CTP today and ran an upgrade on my workstation. Unfortunately it didn't go quite as planned. In fact, I'd have to say I'm actually a little disappointed.
First, many parts of the upgrade failed. I can take most of them, but the engine upgrade failed and for such a simple scenario, it really shouldn't have. I haven't looked at the log yet so I don't know why it failed, but the point is it just did.
I was installing it mainly for the tools anyway. However, the new SSMS also has its problems. Even though I upgraded, it kept the old Yukon version in tact, which is just fine with me. The problem came though when I wanted to import my servers. See, I have to import them because it's not smart enough to do it on its own. So I exported my server list from SSMS Yukon, and imported them into Katmai. Of course, it failed. I tried a couple different times with a couple different configurations, and it still keeps failing.
Now, it does an excellent job with the multi-server querying, which is why I wanted to install it to begin with. But I'm not looking forward to having to create my dozens of servers by hand in the new tool. Isn't this something that should be worked out by now?
Anyway, more to come the more I play with it.
Posted by Sean McCown on November 26, 2007 04:34 PM
November 01, 2007 | Comments: (0)
Parturient montes, nascetur ridiculus mus
Ok, let's get into it again. I'm just trying to do something simple and I'm hitting roadblocks at every turn.
The task is simple: Script out these databases on server1 and create the empty schemas on server2.
I also want to save the scripts so I can do it again and again during my dev cycle. I want to have an identical schema so I can just test my change scripts to make sure they'll run before I put them against my cert environment. Anyway...
OK, so the obvious choice for this task is VSTE for DBAs. It has a schema compare doodad and since I already dumped all the objects in the target DBs, it'll be easy because all the actions will be create.
So I ran the compare and things went just as planned. Then I tried to send it to the query window. Guess what... the script is too big and VS refuses to display it. so it tells me to save it to a file. Ok, I save it to a file and import it into SSMS, which loads it with no problem. However, the script is too big and SSMS refuses to run it. Nice. Now the fun begins.
I then get the idea to split up the script into object scripts... Tables/views, SPs, etc. So I get my first script split up into sections and run it. And of course it fails because even though I have 'include dependencies' checked in VSTE, it's not scripting my views in order. I've got views calling views that haven't been created yet. Now I'm back to SSMS which is crawling. I've got 5500 views to script, and it's been going to an hour and it's only now hitting 1,000.
It would be nice if the MS tools could keep up with the times. Does nobody have big projects in the MS world? I guess they're telling us that if you have a small to mid-size app you're good to go, but otherwise you have to just do the best you can.
Needless to say I'm very discouraged. Once again, my MS tools have taken a relatively simple chore and turned it into an ordeal.
And btw:
Parturient montes, nascetur ridiculus mus.
Translation: The mountains will be in labor, and a ridiculous mouse will be brought forth.
- Horace (Ars Poetica, or The Epistle to the Pisones (c. 18 BC))
Posted by Sean McCown on November 1, 2007 08:34 AM
October 05, 2007 | Comments: (0)
OK, here we are quite a bit of time after Yukon SP2 and we still don't have an ordered list if SSIS packages. Seriously guys, how long does it take to put an order by clause on the end of that query?
Currently, SSIS packages are sorted by import date. I don't know who thought that was a good idea, but I have a hard time believing that I'm the only one complaining about this. MS has a huge IT dept and they use their own tools. So why is it that these things go unnoticed for so long? Or at least unfixed. Maybe it isn't unfixed though... maybe they've fixed it internally and their DBAs aren't complaining anymore so there's no rush to get the fix to us. But there are a lot of things in the tools we all had high hopes for, and I've personally given them a big break because Yukon is basically a v.1 product and you can expect some quirks. But these things they missed should have been taken care of by now.
Dare we hang our hopes on the next release? From what I'm seeing so far in Katmai there are a lot of things that don't look like they'll be addressed. There are a lot of complaints with SSIS alone that don't look promising. There's a good reason for some of this, but I don't think I'm allowed to say what it is so I'll just leave you with the haunting thought that I know something you don't know.
I'm really just in a complaining mood today. I get like that when I have to take several minutes to pick through the 300 packages on my server to find the one I'm looking for. Guys, give us a little patch or something and let us sort these things.
Posted by Sean McCown on October 5, 2007 07:48 AM
October 01, 2007 | Comments: (0)
OK, again with the backup wars... for those of you who have followed me for a long time you'll remember a few yrs ago I did a piece on Idera vs. Imceda. And this was for backup products, right... so SQLSafe vs. LiteSpeed. And IF you remember back that far you remember how heated the outcome was and the extremely poor rating I gave Idera in that roundup. I stood by it then, and I stand by it now. I just don't think the product is enterprise-ready. It still has some serious problems that keep it from making the grade. But I'm really not here to bash on Idera. I do have a couple things to say about the new version of their Diagnostic Manager though, so maybe I'll put something together sometime this week. Anyway though... getting off topic.
Well, for once, I'm not the only one saying things about SQLSafe in public. And frankly I'm a little tired of being the only one sticking my neck out, but that's another post.
Here's a link to a guy who is an avid supporter of Idera and actually chose their product and uses it every day for years now. Well, he just downloaded their new version, and I think you should see what he has to say before you take the leap yourself. Here's his blog post.
There are lots of thing I could say about this situation, but there's really no need to. He says it far better than I ever could. Besides, I just semi-made-up with Janette at PASS and even though she isn't returning my emails, I know she'll eventually come around and realize she can't live without me.
One thing that hits me square in the face though, is how careful companies need to be these days. Perhaps this is the topic for a full post later, but I'll touch on it here just because it's relevant. Gone are the days when you can piss a customer off and just leave it at that. Ordinary people like me have full access to blogs now and we make full use of it. Think about that for a sec. This guy felt so wronged by the new product that he felt it necessary to blog about it to warn others against it. It sounds to me like you need to take all of your customers very seriously. Even if he's a nobody and his blog is unpopular... he still made it to my blog roll so the word will get out somehow. And even if I didn't pick this up, somebody else might have. And they may still. Someone may look at this blog and point to both of our blogs and now the story's out there for everyone to see forever.
So there really are no small customers anymore... just small testing depts.
Posted by Sean McCown on October 1, 2007 08:21 PM
September 24, 2007 | Comments: (0)
PASS last week was just a little disappointing. I heard somewhere through the grapevine that attendance was down by around 15%, but as it turns out, that was the lucky part. The Denvery convention center turned out to be inadequate for our crowd anyway because many of the good sessions were standing room only, and they wouldn't let anyone stand during the sessions. More on that in a minute.
That wasn't the disappointing part though. What I found disappointing was the inconsistent quality of the content. PASS is making more of an effort to expand its speaker base, so they're getting some people who either aren't experienced speakers, or who have other agendas (like promoting themselves). Anyway, I took a 400-level session that spent most of the time explaining the basics to us. Things like, here's what tempdb is, and don't forget to split data and log files. And I found that to be the norm with the sessions that I didn't like. They advertised themselves at a much higher level than they turned out to be. Needless to say next year I really hope they have someone checking content so they set these sessions at the right level. I don't mind them teaching that stuff because someone will always need it, but at least be honest about your audience.
OK, now about standing in the rooms. Nobody ever gave me a reason, but it appears to be something that the convention center folks insist on. I'm sure if they were pressed, they would say it was a fire regulation to not have people sitting in the back. You couldn't even sit on the floor or on a table. So I'm having a hard time here not talking about how pointless and moronic that is, but I will refrain. However... if I were to talk about it, I would point out that that would be the stupidest fire code I've ever seen. If you say that you can't have anyone there because of a fire during a presentation, then you have to account for fires before the presentation while everyone is still standing around and the room is crowded. At that point everyone is blocking the door. So what you're telling me is that it's ok to block all the exits before and after sessions, but not during. So evidently, it's the fact that someone is talking in the front of the room that determines the fire laws. That kind of rule was clearly made by idiots. Seriously, if you don't stop to consider the actual logic of your rules, then you end up with crap like this. And what would the other reason be then? The convention center people just think it's disrespectful to not be in an actual chair while someone is talking? That's even dumber than the other one. So I don't know exactly what's up with that, but I would hope that PASS would stop that kind of mindless, moronic garbage next time.
Anyway, that's what I would say IF I were going to talk about it.
Posted by Sean McCown on September 24, 2007 11:08 AM
September 14, 2007 | Comments: (0)
This is going to be a quick post. I just wanted to say to all of you who are going to PASS in Denver next week, don't forget to go over the coference survival guide. Instead of reprinting it, I'll just link to it.
Sean's Conference Survival Guide
And you guys feel free to come up and say hi if you catch me next week. I like meeting the little people (j/k).
Posted by Sean McCown on September 14, 2007 09:54 AM
September 12, 2007 | Comments: (0)
The Multi-Tier Storage Solution
For many DBAs performance is a huge problem. I know I've currently got systems that have their bottlenecks. One of the bottlenecks in a lot of SQL Server systems is in tempdb. There were a lot of things in SQL2K that relied on tempdb and Yukon increased that dependency by several fold (or made it worse depending on how you want to look at it). And while I don't really know yet if Katmai is actually going to increase it even more, it certainly isn't going to decrease it.
So in a way, Microsoft is actually working to create a bottleneck by not giving us multiple tempdb areas that we can use to segregate our users into. This really is one of those areas where Oracle beats SQL hands down because anyone who's ever tried to manage tons of users or tried to manage a large job in the middle of normal processing knows the advantage of having a separate tempdb area.
Enter tiered storage... I think that these new tempdb requirements are going to drive more and more DBAs to consider tiered storage for their servers. The simple truth is that tempdb needs to be as fast as possible, and it doesn't get backed up, so you've got some wiggle room when it comes to configuring your tempdb area. And to that end, you should start considering SSDs (solid state disks) for your tempdb area. SSDs are magnitudes faster than spindles so performance should go through the roof. And since tempdb services every DB on the server, then it needs to be as fast as possible. And with SSDs you can pretty much guarantee that the bottleneck won't be tempdb.
Don't get me wrong though. MS isn't the only one driving this need. Pretty much any DB could really take advantage of SSDs. The problem with SSDs though is that they're very Very VERY expensive. But look at the difference in raw hardware cost vs maintenance and power consumption. SSDs use considerably less power than hard disks so you'll be saving money on that side. It also takes far fewer SSDs to get good performance than it does hard disks. I can't count the number of times we had to build an array of several dozen spindles just to get the performance of tempdb to where it could actually support the load on the system. That's the perfect application for SSDs. Instead of buying a couple hundred SCSIs, you might be able to get away with just a few SSDs. Of course that's only if you're doing it for performance.
Yukon and Katmai aren't the only ones that can benefit from SSDs either. SQL2K still uses tempdb and while it doesn't use it as heavily as the newer versions do, it's still quite often the bottleneck. So don't be afraid to pop some of those expensive boys in your SAN.
This isn't anything all that new. We're already tiering storage configuration. We don't use the same RAID configuration for every DB or for every file type. You don't use RAID5 for tempdb, and you don't use RAID0 for your logs. So now all we're gonna have to start doing is mixing up the types of disks we use for different purposes. Use your SCSI for your main DB app. Use SSDs for your tempdb and some of your lesser log files. Use SATAs for your backups before they get pushed to tape. You know, start tiering your storage to fit the requirement of the intended application.
So anyway... this is just food for thought. The next time you've got a tempdb bottleneck just consider all of your options before buying 100 more spindles to get the performance you need. And I think as SSD technology gets better, and as the price comes down a little, you'll start to see this being the norm. Of course, I don't think it's the solution for every aspect of your DB server. I still have serious reservations against putting my main data files on SSDs, but I'm sure that too in time will pass.
Posted by Sean McCown on September 12, 2007 09:48 AM
June 26, 2007 | Comments: (0)
I wrote this code the other day that disables all user logins in SQL Server 2000. If you have the need to be the only one on the server in SQL for doing maint, troubleshooting, etc., then you'll find this useful. I've given you both the adhoc version and the SP version. Hope someone can use it... because even sa can be kept out of the server this way.
When you use this, don't forget to put yourself in the exclude list in that NOT IN clause. Otherwise you'll lock everyone out and you'll need to restore master to get everything back.
And since this hits the system table directly, I'm not responsible for any damage you do to your system with this code. Use it at your own risk.
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH override
GO
--Disable users.
UPDATE sysxlogins
SET xstatus = 1|xstatus
WHERE name NOT IN ('user1', 'user2')
--Enable users.
UPDATE sysxlogins
SET xstatus = 1^xstatus
WHERE name NOT IN ('user1', 'user2')
sp_configure 'allow updates', 0
GO
RECONFIGURE WITH override
GO
sp_helptext syslogins
------------------------Turn into SP-----------------------------------
--
--Create Procedure dbo.spDisableAllUsers
--
--@Users varchar(3)
--
--as
--
--/*
--Disables all user accts on the server.
--Author: Sean McCown
--Date: 6/25/2007
--
--*/
--
--DECLARE @sql nvarchar(1000)
--
--SET @sql = 'sp_configure ''allow updates'', 1; reconfigure with override;'
--
--EXEC (@sql)
--
--
--IF @Users = 'OFF'
-- BEGIN
--
-- update master..sysxlogins
-- set xstatus = 1|xstatus
-- where name NOT in ('user1', 'user2')
--
-- END
--
--IF @Users = 'ON'
-- BEGIN
-- update master..sysxlogins
-- set xstatus = 1^xstatus
-- where name NOT in ('user1', 'user2')
--
--END
--
--SET @sql = 'sp_configure ''allow updates'', 0; reconfigure with override;'
--
--EXEC (@sql)
OK, so it's worth mentioning that this code doesn't work in Yukon. Not only can you disable accts in Yukon with 'alter login', you'd have to be able to touch the system tables directly, which is just too much trouble. The one thing that you can't do in Yukon, that you can do with the code above is disable windows groups. BOL says that's by design, and I suppose I can see that, but it would be really handy to be able to throw your server into a mode where only certain admins can connect, etc. Now, to get around this problem, all you have to do is make sure that no matter what you do, you don't give anyone dbo in any of your DBs. Then you can just throw all of your DBs into dbo only mode before you start your script. Another method would be to take your DBs offline if you don't need them. So if you're performing maint on several DBs one at a time, then disable all DBs except the one you're working on, and then disable it when you're done, and enable the next one in dbo only mode. This way, you can't control users connecting to the box, but they won't be able to do anything.
Now, all this has come about because MS decided to not allow us to disable windows groups. Now, I'm not saying that's a good thing or a bad thing... I'm just saying it's the reason we have to do it this way.
Posted by Sean McCown on June 26, 2007 08:02 AM
June 21, 2007 | Comments: (0)
In case you missed it, MS just acquired the Dundas products for use in future versions of SSRS. Those would be the maps, guage, charts, etc that made Dundas famous. And while Dundas is still currently selling those products, there's no telling what may happen in the future. However, I want to make it perfectly clear that MS did not buy Dundas... just their products.
Posted by Sean McCown on June 21, 2007 07:24 AM
June 18, 2007 | Comments: (0)
OK, from time to time this topic comes up so I thought I'd publish this info in the hopes that someone will find it useful.
The problem is with getting non-admins the ability to admin jobs in SQL2K. It's widely known that only SAs can do this, but there's a special DB role in msdb that lets you do this w/o having to be an admin. Rather than give you a big explanation, here's the code so you can test it yourself. This will let anyone in this group admin SQL jobs just like they were SA.
use msdb
exec master..sp_addlogin 'testme', 'testme', 'msdb'
exec msdb..sp_adduser 'testme', 'testme', 'TargetServersRole'
grant execute on sp_add_job to TargetServersRole
grant execute on sp_add_jobstep to TargetServersRole
grant execute on sp_update_job to TargetServersRole
grant execute on sp_add_jobserver to TargetServersRole
grant execute on sp_add_jobschedule to TargetServersRole
grant execute on sp_delete_jobschedule to TargetServersRole
grant execute on sp_delete_job to TargetServersRole
grant execute on sp_start_job to TargetServersRole
grant execute on sp_stop_job to TargetServersRole
I hope this helps somebody. If you want to give the ability to admin only specific jobs though, you'll still have to use one of the intermediate steps you did before, or make the user the owner of the job. Too bad groups can't own jobs.
Posted by Sean McCown on June 18, 2007 12:17 PM
April 09, 2007 | Comments: (0)
Last week I watched a really good episode of SSWUG TV. Stephen interviewed Paul Randal and Kim Tripp in the hallway of the hotel. First, let me say that I've never seen either of them look happier. They were laughing and giddy the whole time, which really shows how good they are for each other. That aside though, this interview was exactly what you would expect when SQL experts of their caliber get together in one place. They touched a lot on recovery and the things you can do to bring your DB back up if you have a problem. I'm sure a lot of these things are unknown to a lot of DBAs simply because dispite what our Oracle counterparts say, SQL Server doesn't go down all that often, so recovery skills are hard to develop and keep unless your on the PSS team.
So I seriously advise all of you guys to go watch this episide because it'll expose you to some things you've never heard of, and at least you'll know they exist. Then if you want more specifics, you can write your local MVP or anyone else really, and get some more info. Or you can just wait for Paul's whitepaper that he PROMISED ON FILM. I think he said he's trying to have it ready for this summer sometime. Don't worry though. I'll setup a SQL job to email him every day until it's ready.
But it's a fun interview, and I highly suggest that everyone go check it out. It's here.
I'm serious though... these two were just having a blast. They really are the new dynamic duo. I kept expecting them to say "Wonder Twin powers: Activate". I'm happy for you guys. You really agree with each other. And Kim, I have no doubt that Paul beats you at that game all the time.
Posted by Sean McCown on April 9, 2007 08:43 AM
March 01, 2007 | Comments: (0)
Red-Gate has answered the blog and all the hype it created earlier this week. A comment was posted to the original blog, and I'm going to just copy it here, partially to make sure that everyone sees it, and partially because I'm really bad about going in to approve comments.
Here's the message:
The clause in the license agreement referred to in these posts is designed to prevent the use of a single license on multiple machines by continually transferring the license between machines. We can see, however, that the wording of the license can be interpreted in a different, more sinister way. It was never intended to stop people from transferring licenses in the case of upgrades, replacement of old equipment, reorganization, or any other legitimate reason, and we apologize if customers or prospective customers interpreted it this way.
I believe that any customer who does business with Red Gate Software knows that we operate in an atmosphere of trust and respect. We are working now on changing our licensing terms to make our policies clear. In the meantime, if anybody is concerned about this issue, please contact me directly at nick.warren@red-gate.com.
Nick Warren
Head of Customer Support, Red Gate Software
I'm glad to see them step up to the plate and try to straighten this out right away, and frankly I'm not surprised. I've always known Red-Gate to go out of their way for their customers. That's really why I was so shocked to see that in their license.
Now above, Nick said that he said that he can see how that wording could be used for evil, and while I agree, I just don't see how it could be taken any other way. There's just not much wiggle room in there for interpreting it any other way.
However, guys, when you get it changed, let me know and I'll post it here so everyone knows it. And make sure you put some language in there stating that the changes are retroactive so that those who already have licenses are covered.
Posted by Sean McCown on March 1, 2007 11:18 AM
February 13, 2007 | Comments: (0)
You know, there are some things I just don't get. These network admin backup solutions that everyone keeps trying to push off on is are starting to really get to me. I'm getting on this topic now because I was looking at Microsoft's DPM datasheet. One of the things it stated in there was that "Database administrators have asked for the ability to restore data themselves." Funny, I thought we already had a way to restore our data. It's called native SQL backup.
This is actually a conversation I've had a lot with high-level techs, and even with some at InfoWorld. The question that always comes up is what difference does it make? Why is everyone like Quest(LiteSpeed) and Red-Gate(SQL Backup) fighting over the SQL Server backup arena? Well, I suppose that's a fair question if you don't live in the database world, so here, I'll go ahead and answer it publicly.
The big deal is this... those network-level backups like Microsoft DPM, BackupExec, and ArcServe don't allow DBAs to do what they need to do how they need to do it. Let's say that I have a server that I've backed up and I need to restore it to a dev box. Do I already have the agent deployed to that box? Will it cost me another license? Can I schedule an automatic refresh of that environment every day/week/month? If there's an error, can catch it and try to do some automatic fixing before I get involved? Can I add it to say an SSIS package or other mechanism to be part of a whole recovery workflow? What about sending it to another business unit? Can I restore across their firewall? Do I need another license for that? What's their change control process like for putting another service on that box? Is the data compressed? Can I do mirrored backups for both maximum protection and to help keep dev/QA/test boxes in synch?
What about Yukon? Can I still do page-level restores, or do I give that up in lieu of this other solution? Can I still do filegroup restores? How about object-level recovery? Can I restore an individual table, SP, trigger, etc?
Anyway, you get the point. There are a lot of things to consider when choosing a SQL Server backup solution and these vendors try to make it seem like it's a simple choice. Frankly, I enjoy the freedom that the REAL database backup tools give me.
That holds especially true with LiteSpeed. I just don't know why anybody would choose anything else. Sure, some of the others are cheaper, but the object-level recovery alone is worth its weight in gold. I've had my butt saved more times than I can count by the fact that I could pull a specific object out of my LiteSpeed backup file. And I'll let you guys in on a little secret... LiteSpeed is the ONLY backup solution in the world with object-level recovery for SQL Server.
Even MS, who has spoken out against this functionality in the past, has changed their stance on it and is not in favor of object-level recovery. Look, I'm not putting the other tools down. I've recently taken a look at Red-Gate's tool and it's a solid engine. It does a very good job of compressing your backups quickly and giving you a fast restore. But when it comes to functionality, they just don't compare to LiteSpeed. This also isn't a commercial for LiteSpeed. I'm just telling you like it is. From a logical standpoint, there's no reason to cut yourself off from that functionality. You WILL need it one day.
So why is everyone trying to re-invent my wheel? I dont know, but it kinda pisses me off. Do any of these guys have DBAs on staff? Have they tested these things against the native functionality of SQL backup? I'll tell you who these products are for. They're for NT and network admins who find themselves needing to make sure that the database gets backed up. They need a mechanism that lets them manage backups the way the manage their NT backups. The trouble is that it's just not the right tool for the job. Databases have completely different backup/restore requirements than file servers. I've never seen a multi-terabyte excel file that someone needed to restore just a macro from, or just a couple rows that someone deleted or corrupted.
So stop trying to put square DBAs into your round NT holes (that actually came across a lot dirtier than I intended). And let us do the backing up for our databases.
Posted by Sean McCown on February 13, 2007 08:32 AM
February 05, 2007 | Comments: (0)
OK, here's a tidbit of news that not many of you will hear anywhere else. My friend and secret informant at MS, Donald Farmer, has just left the SSIS team in favor of the data mining team. Now, I talked to Donald about this last week, and he doesn't think he's blog-worthy, but that's where he's wrong and I'll tell you why. After talking to Donald about the reasons for his move, and what he hopes to accomplish, it was clear to me that this is something we all need. Data mining is the future, and as of yet, it's still far too complicated for the ordinary IT guy to grasp. Donald's hope is to bring a much simpler process to data mining, and make it so that any DBA or even educated business user can do it. Why do I think we all need Donald to make this leap? Well, because put simply… Donald actually cares about it. There's a fire in his voice that you don't hear often in this industry anymore, and anyone with that kind of fire can make things happen. It's like being a chef. Someone can teach you how to cook, but you'll never be truly great unless you're a real foodie. That's what Donald is I guess… a data mining foodie! I have another friend at MS right now who's trying to learn to code C++. The only problem is his heart's not in it and I can tell he'll learn just enough to get by. He'll never be great.
So for this reason, I think that Donald is very blog-worthy, and I'm personally very excited at the prospect of having someone with that level of excitement and commitment to data mining looking at the problems I face every day… ok, well, every other day anyway. It's funny isn't it... when somebody's excited about something it's contagious. They get you excited about it too, and I'm psyched about data mining right now.
But hey guys, don't take my word for it. Here are Donald's exact words to me about his move.
I can't see myself as blog-worthy, but certainly I can tell you what I'm up to and why.
My new role will be Principal Program Manager for the SQL Server Data Mining team. It's not really an up or down move. There's just so much fun to be had with customers and partners where the rubber hits the road. I asked for the move, and it's giving me a thrill to do this.
My motivation really goes back to why I am in the BI world at all. BI enables people to build and test their hypotheses against data of a volume and complexity that otherwise they would find difficult to grasp. Data integration is needed to do that, and so it was a great thing to work at that end of the process. But 6 years ago I actually joined MS to work on Analysis Services, because it was the analytic power that appealed to me.
I sometimes compare my passion for analysis with the amazement I felt when I was a kid, when I would help my dad in the garage, pumping the hydraulic jack. A 10-year old kid could lift a car off the ground with one hand! It was awesome. I feel the same about data analysis – imagine being able to make sense of every transaction of every customer for every year. That's a real buzz for me. Hypotheses can be formed, tested, rejected, applied, reviewed, shared ... all hopefully with relative ease.
Predictive analytics takes this a little further. Every single business uses predictive analytics - they just don't know it. When the village shopkeeper orders 2 boxes of valentine's cards, they are performing predictive analysis. In their case it is perhaps informed by knowledge of their customers, some analysis of past sales, and some gut feelings. Predictive analytics with data mining brings gut feeling to BI, captures it, models it and so on. I want to bring that to every user. I want small and medium businesses to have the capabilities of making data-driven decisions without needing to understand the complexities of algorithms or even how to model their data. That's going to be tough. And I want to get predictive analysis into situations where we’re not using it. I want to see DBAs receive predictive warnings about query performance degrading, or which databases are most likely to have issues under different loads – before it happens.
BTW, you'll see talking me more often about predictive analytics than about data mining. It's a fine distinction, but for me data mining is more about the techniques, while predictive analytics is more about the business cases and scenarios. I'm not going to be going all deep and academic on the algorithms – there is an awesome team of smart folks at MS who can do that. They will be marvelous to work with, I'm sure. For myself, I'll be concentrating on how we can change the landscape for BI users.
Finally, I just know I'm going to love working with the customers on these issues. Working with SSIS customers, we directly impacted their technical capabilities, and indirectly saw great benefits to their business. Working with predictive analytics customers, we'll get to see direct business benefits at first hand. That will be very satisfying.
OK Donald... all eyes are on you now brother... let's see whatchu got.
Posted by Sean McCown on February 5, 2007 12:16 PM
December 27, 2006 | Comments: (0)
OK, if you guys remember, I told you that I'd be talking to MS about the tools they provide for DBAs, since they don't seem to be working out very well. Well, I think it was friday I finally got on a call with them, and here's basically what came of it.
Their research has made them choose a certain path, and they do recognize that they didn't get it exactly right. However, they made a sincere argument that they want to get it right. They swore to me that they're dedicated to making tools DBAs can actually use. We got into a couple specifics that get on my nerves, and a couple things that have actually crippled me; they were very receptive.
You have to understand though that whenever you compile a feature list, you have to balance effort vs return. It's simple math really. Should we spend 3mos and 10 devs writing this piece of code that 5 people are asking for, or that same amount of time writing something that 500 people are asking for? Do you write a really cool feature into the product, or do you fix what's already wrong with it? These are decisions every software vendor has to make for every release cycle. And it's a fine line to walk. From what I can tell, MS at least tries to do things right. They pull in customers and run them through the product and give them a set of tasks to perform. They then write the GUI based off of these results (and others I'm sure).
My big question now is since the GUI isn't living up to what it should be, is the test flawed, or is the general DBA population filled with idiots? I don't think they're pulling in MVPs for these studies. Maybe they are, I don't know, but it's always been my view that if you write for the real professionals, everyone else will fall in line. Don't write for the LCD, write for the ones who really know what they're doing. If you think about it, it's the smarter way of doing things. Which do you think is a better way to feed an industry... to write to the masses who don't know what they're doing, and force the real pros to work like them, or to write to the pros and show the masses how they should be working? I would think that writing to the pros would be better for everyone. Sure, the masses won't get it at first, but as they get better, they'll see that things are better this way. There's always the risk you run of scaring away your user base though. A certain amount of the population will run to the vendor tools that dumb everything down for them. That's fine, let them go. If they ever pick up a book and learn something they'll be back.
All this has me thinking though. What are the qualities of a good tool? This isn't necessarily DBA specific either; it goes for any kind of tool. I would say that the really good tools make you feel comfortable no matter what your skill level. They grow with you. If you're a novice, you can get your job done. As you grow in your career, there are things to discover in the tool that will make things easier for you. Yeah, that's it. A tool has to grow with its users. It has to have a good mix of GUI, menu, wizard, command-line, scripting, reporting, extensibility, etc. It all has to be there. Early in my career I found an MVP and asked what tool he used to admin SQL. He said he used Query Analyzer. I then had him give me a few of the things he felt that it gave him over anything else, and he gave me a list of about 10 things, most of which I'd never heard before. So I started using Query Analyzer and even though I was a little young in SQL to really get the appeal, I slowly started to see what he meant. In short, whatever the real experts are using is what everybody should be using. And if the those experts aren't endorsing your tools, then you don't have good tools. I talked to many of the MVPs at PASS, and none of them are very happy with the DBA tools for SQL Server.
Here's what we ultimately decided. I'm going to have a monthly call with MS to discuss issues with the tools. So, if you guys have any feature requests, feel free to send them to me and I'll champion them if I think they're worth anything.
So send them to me at sean_mccown@infoworld.com. We'll have other topics of discussion as well I'm sure, because we did discuss a couple other problems in other areas.
Ok, so that's the report.
Posted by Sean McCown on December 27, 2006 07:12 AM
December 12, 2006 | Comments: (0)
With all of the talk about MS not caring about DBAs lately (and I'm still getting emails about it), it also seems as though MS has taken notice. They've contacted me and want to discuss our different views on how well they like DBAs. We're actually not able to hook up until next week sometime, but when we finally do, I'll be pretty anxious to report to you how it goes. What I'm expecting from the conversation is some detail on where we've been with DBA tools, where we are now, and where they plan to take us. I know a lot of you are looking forward to hearing their response as well, and I'll let you know when I have something.
Here are the blogs I've written on the topic:
Why Doesn't MS Care about DBAs?
I Guess It's True Then
Posted by Sean McCown on December 12, 2006 02:33 PM
December 08, 2006 | Comments: (0)
The other day I wrote how MS doesn't care about DBAs. I really thought I was just being my usual negative self, but as it turns out, I'm not the only one. I've received so much email from DBAs saying the same thing, and that they're disgusted by MS's lack of thought they put into DBA tools. Some have even gone so far as to say MS is trying to do away with DBAs completely and make everyone a developer. That would certainly seem to be the case with all the advancements they're making to developer tools, and hardly anything for DBAs. I keep thinking that surely MS can't be so blind as to think that a developer could do a DBA's job. Then it hits me... they've been big on ease of use, and they're certainly easier than a lot of the other DBs, so maybe their ultimate goal is to engineer us out of a job. Is the goal to make SQL so easy to use that you just don't need DBAs? It's possible I suppose, but if that is their true goal, it's a naive proposition. Developers simply don't know the things DBAs do, and who will fix all of their bad code if they're the ones watching the system, or if the system is watching itself. True, SQL Server may be so easy even a caveman can use it, but that doesn't mean problems don't arise. And when they do, who are you going to turn to... the Windows guy??? The developer who thinks SQL can only be written in cursors??? HAH!
From everything I'm seeing though, it certainly seems like they're trying to do away with DBAs. Our tools are becoming less and less rich, while plenty of effort is being put into developers. But I'm telling you right here and now... the day you get rid of DBAs is the day you get rid of your database. There just has to be somebody who knows how systems work, and how to make all of the DB code play together all nice-like.
Like I said... I thought it was just me, but the feedback I've gotten is overwhelming in favor of MS just not doing much for DBAs. And I realize that we're like the illegal aliens of IT, but you'd better start taking care of us, or you'll find out the hard way exactly what it is we do.
Posted by Sean McCown on December 8, 2006 12:34 PM
December 07, 2006 | Comments: (0)
A New Product with a Sexy Voice
At PASS I had the pleasure of finally meeting the man behind the new DBLaunch for SQL Server. I had talked with Sean before, and it sounded interesting so I was glad to get a chance to sit down with him.
DBLaunch is basically an install and patch management system for SQL Server. You install the server, and you can push out preconfigured SQL installs, as well as schedule mass installs of patches, and configuration changes.
I have managed to get a hold of a copy now and I'll be running it through its paces to see if it's really all that. However, from what I saw at PASS it looks really promising.
I should mention too that Sean McLean's got one of those sexy Scottish brogues that drives me wild. Every time I talk to him I fall a little deeper. OK, just kidding (I think), but you girls will love his accent.
If you don't believe me, then go visit his new Camtasia videos demonstrating the product. You'll find them here.
Posted by Sean McCown on December 7, 2006 06:59 AM
November 28, 2006 | Comments: (0)
Why Doesn't Microsoft Care about DBAs?
I got a chance to talk extensively with some of the MS dev team at PASS. Specifically, the new VSTS for DBAs team, or part of it anyway (Gert Drapers and Cameron Skinner). In our discussions about MS's current vision for the product, it became very clear to me that MS really doesn't hold DBAs in much regard. However, apparently, developers are the bee's knees.
The main problem that I outline in VSTS is that you have to go into VS.Net to use it instead of being able to access it from SSMS, which is where DBAs spend all of their time. Not only has the DBA been left completely out of this equation, but the more popular dual-role guys are hardshipped as well because they now have to keep 2 tools open. The presumption that DBAs don't need an easy way to get at code is absurd. I get the idea behind it though... offline editing. Everything you do to the code is done offline and kept away from production. That still doesn't mean that you have to keep everything separate though. They said they wanted to help close the gap between devs and prods, but everything they're doing is keeping us apart. It's now very apparent that anytime you want to develop something, that you have to go through VS. Look at this this way... for a DBA to have to go to VS to code is a huge pain. For starters, none of their connections are there. They have to define all new connections, and if they change, he's got to keep them up to date as well. Even the help files are kept separate. Have you ever saved a help topic in favorites and then tried to retrieve it from VS help? It doesn't work.
I'm a DBA, and when I write code, I write it in SSMS, not VS. So for me to put something into the code vault I have to now cut and paste it into VS. So, VS becomes nothing but a way for me to source my code and that's it. There's also nothing in the way of a decent auto-complete feature in SSMS. There are some really thoughtful features... in VS. SSMS doesn't really have anything all that earth shattering in the way of features, and most of the ones it does boast tend to be so impossible to work with they're not even worth using. The mgmt reports are a good example. They're so slow to bring up, they're almost not worth even having. And have you ever tried to right-click on a table and bring up a 'select to' menu? It's like getting blood out of a turnip. Most of the time it's just so slow, it just drags on and on and on and on and on and on and on... well, you get the idea. Whereas in QA (Query analyzer), these things were very fast and I've only ever had very minor problems with it. QA was written by Gert Drapers, btw. However, SSMS falls short and I haven't talked to anybody who's happy with it. It's got some good ideas, but none of them have materialized to anything useful yet.
I was really hoping they'd fix some of these problems in SP2, but that so didn't happen. Why, you may ask? Well, the answer's simple. MS doesn't care about production DBAs. The message is clear... if you have any serious development to do, do it in VS, not SSMS. Apparently no self-respecting developer would be caught dead in SSMS, and no DBA could possibly be serious about development if they insist on coding in a front-end tool.
I would like to see a feature pack come out very soon that contains just the client tools. They could address all of the issues with SSMS and really give DBAs something to work with. Every single dev I talked to said that he knew about the problems in SSMS. And why wouldn't they... it's been a year. So why weren't they fixed in SP2? That's right... you guessed it... because MS doesn't care about DBAs. If the same problems existed in VS I can guarantee you they would have been fixed by SP1 if not sooner.
Now, one more thing. While it's true that DBAs have to go to VS to develop if they want to us VSTS, it could be argued that MS is using this model to conform with the separation of duties control in most audits. Making DBAs keep their functions separated forces those dual-role guys to be more aware of what they're doing and of the fact that they're actually switching roles. I doubt MS really had that in mind, and I'm sure now that I've said it they'll be like... yeah, that's why we did it... I'm glad you finally figured it out, now get off our backs.
That's fine guys. I don't mind. The idea does have some merit though. I'm sure I don't have to flesh out the whole discussion for you, but the separation of duties angle could be the way to sell this to DBAs who just complain and complain about little stuff like this. I'm just glad I'm not one of them.
Posted by Sean McCown on November 28, 2006 10:55 AM
November 27, 2006 | Comments: (0)
I was sitting with Adam Mechanic at PASS last week and we were talking about the kind of stuff we do and don't like about Yukon. Jean-Rene Roy happened to be there as well, and asked if we'd be willing to have the conversation on film... so we did.
Here's the link. Best and Worst of SQL Server 2005
It's nothing earth shattering, but I thought some of you guys may like to see it.
Posted by Sean McCown on November 27, 2006 07:43 AM
November 15, 2006 | Comments: (0)
I just the PASS keynote where they were demonstrating mirroring. They had 2 clients hitting the DB and setup mirroring. Then they did a manual failover to show how your apps will start working on the new live instance. Well, somebody should have told the apps that because not only did neither of them failover, they shot up one of those nasty SSPI errors. He said that they were having problems with multiple clients for the demo so he tried a single client. He never did get an app to failover. That's gotta be just murder. Anyway, since I've got my treo this year, I'll blog some interesting things as they come up.
Posted by Sean McCown on November 15, 2006 09:53 AM
November 06, 2006 | Comments: (0)
SQL Server 2005 SP2 Nov CTP is HERE!!!
Well, SQL Server SP2 Nov CTP is being released today so I hope you’re all ready for it.
The CTP will officially open up today at 3pm PST. You can get it here: http://www.microsoft.com/sql/ctp.mspx
I’m really hoping this will add up to a significant release for MS because while Yukon was an excellent release, the promise is that this SP will pick up a lot of the things that got left out. That’s kinda the problem you have when you rewrite something basically from scratch. It’s hard to make sure that everything gets in there. Though I do find it hard to believe sometimes that some of these things didn’t get noticed before, the point is they’re getting noticed now.
I don’t have a complete list of the enhancements, but I’m working on it. As well, I’ll be making a series out of this and as I find things out, I’ll do my best to upload videos demonstrating some of the new features so you can see firsthand what’s going on. However, some of the more important fixes revolve around the GUI. SSMS has had tons of features added that may actually make it a complete environment. I can’t say yet how complete the feature set is going to be, but with features like finally being able to add our own SSRS reports to SSMS, the future looks bright. They’ve also broken up the disk usage report into smaller reports. Personally, I that’s one that I welcome the most because I have some DBs with some pretty big tables (the biggest being 10bill rows), and that disk usage report just never returns. So I can’t wait to sample that one.
There are other significant changes that I’ll be talking about more as the CTP cycle goes on, and I’ll of course be monitoring the progress of bug reports and such. There is one more feature I’ve really got my eye on, and that’s the common criteria compliance enabled option. I’m going to dedicate some significant time to this in the future and I’ll be explaining it in pretty good detail, so stay tuned.
OK… an extremely abridged fix list can be found here: http://go.microsoft.com/fwlink/?LinkId=71711
However, while I would ordinarily print it for you here, I’ve got another special surprise. I’ve got a pretty decent fix list for SSIS that’s already included in the SP. I don’t think you’ll find this anywhere else so enjoy.
SSIS Fix List
433365 - Need to change SQLCE references to SQL Server Everywhere
SQLCE has been re-branded as "SQL Server Everywhere". All UI elements have been updated to reflect this change.
431678, 443197 - Improved SQLDumper functionality
SSIS now always uses the native dumper instead of the managed implementation. In some circumstances dumps were not being submitted to Watson, this has been remedied. Furthermore it is now possible to specify the type of dump required using registry settings. Previously, SQLDumper was invoked for SSIS executables with only the SQLDUMPER_SEND_TO_WATSON flags specified. PSS needed the ability to configure SQLDumper for SSIS dumps to capture things like a full dump for debugging purposes.
449646 - Supportability -- Include Error Constant along with HRESULT in logging
Including the error constant in the log details (instead of just the HResult) for the most common errors helps both PSS and customers track down issues more efficiently. The following constants are affected: DTS_E_OLEDBERROR, DTS_E_THREADFAILED, DTS_E_PRIMEOUTPUTFAILED, DTS_E_THREADCANCELLED, DTS_W_MAXIMUMERRORCOUNTREACHED, DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER, DTS_E_INDUCEDTRANSFORMFAILUREONERROR, DTS_E_PROCESSINPUTFAILED, DTS_E_PRODUCTLEVELTOLOW
450793 - Import Wizard in SQL 2005 cannot import from text file that was exported from SQL 2000
The current UI enforces a maximum of 1000 sampled rows so there is no way for a customer to scan more data in order to effect a larger statistical sample.
428373, 478562 – Package fails to load after changing user locale and/or date/time locale settings
In some instances, packages created on a machine with a specific date locale may fail to load if the package is opened on a machine with a different locale or date/time formats.
433051 - SQL Server Integration Services service failed to start after installing SP1
The service startup delay occurs because .NET verifies signature of the files and it needs to consult certificate revocation list (CRL) to check whether the certificate was revoked. If access to these URLs timeouts, service may timeout and fail to start. Please see this KB article which describes how you can configure the system to avoid this issue:
http://support.microsoft.com/kb/918644
433396, 443017 - 64-bit runtime and pipeline interop assemblies don't match 32-bit assemblies
Certain interop assemblies were built as platform independent which meant that alignment on 64bit and 32bit platforms was different. The fix was to include platform-dependant interop assemblies.
433397 - Soft memory leak in service if continuously executing very short packages
If packages are executed very fast (empty package in a loop), the objects that represent packages inside the service cannot be finalized quickly enough, which creates a memory leak until the pressure is removed.
433539 – Invalid sort order in legacy package list
When attempting to edit a legacy (SQL 2000) DTS package with multiple versions from MSSMS, the versions listed in the SELECT PACKAGE dialog box are not sort chronologically (and appear to be sorted alphabetically). Caution must be used when opening the latest version because the latest version may not be at the top or bottom of the list as expected.
434497 - Certain keys not working when in DTS 2000 Package Designer
Certain keys such as Home, End, Backspace and Enter do not work in SSMS after opening a legacy (DTS2000) package from SSMS.
434567 - The parameter is incorrect. (Microsoft OLE DB Provider for Visual FoxPro) after SP1
Regression: After installing SP1, connections to Visual FoxPro databases may not work correctly. This used to function as expected in RTM code.
434686 - Expression evaluator REPLACE function may lead to data corruption if source string is empty
Under certain conditions when using the REPLACE function with specific input (all spaces) can result in a column in a row receiving a value from a previous row.
434878 - File System Task to create directory fails if directory is not empty
If the Create Directory operation is selected and the UseDirectoryIfExists is set to True the task will fail with an error if the directory has a file in it.
435542 - Not all configurations have their paths updated when being deployed depending on their name in the deployment manifest
In certain circumstances, not all configurations have their paths updated when being deployed depending on their name in the deployment manifest.
436221 - ExecuteSQL task has BypassPrepare set to true by default
Many customers are hitting this problem when they have named parameters (denoted by "?") in their queries. This causes the prepare() call to fail. Setting the bypassprepare property to true by default should take care of this problem without any functional effects on customers.
436793 - ForEach enumeration of ADO recordset can cause infinite loop when using checkpoints
When using the SSIS ForEach loop to enumerate an ADO recordset, and checkpoints are enabled, the loop may iterate infinitely.
436921 - Combo box for variable selection in transform UIs
A combo box is required for entering the VariableName property on various transformations such as the Recordset Destination adapter and the RowCount transformation. Currently it is free text.
436925 - Bad error for OLE DB Command and UPDATE
Running an update statement through the OLE-DB Command component, such as is generated by the SCD transform, gives a poor and misleading error message when the UPDATE violates column nullability.
436926 - I/E wizard: Better error messages when "Optimize for many tables" option is used
The wizard does not capture all the errors passed by the internal provider mechanisms, resulting in difficult to identity errors.
436942 - UnPivot: Remove a pass-thru column from upstream and fix unPivot afterwards saw a crash
After a column is removed from an upstream component, opening the UnPivot transform will bring up the Fixup metadata window. Choosing to delete the invalid column can crash the environment.
437169 - Strings are not trimmed when they flow into a recordset destination
If a recordset is populated with strings using the Recordset destination - those strings are padded with whitespace all the way to the full length of the data-flow column.
437339 - Potential caching of identical rows in Lookup reference buffer
The code to cache rows for fully cached case may cache identical rows multiple times (performance problem) and fail to issue duplicate lookup row warning.
438001 - Cannot run a SSIS Package which is under Source Control but not checked out
It's not possible to run a package which is under source control without checking it out. With multiple developers working on these scripts having to check them out to run them is very disruptive as they are locked regularly.
438057 - Property description for package properties "VersionMajor" and "VersionMinor" are wrong
The property description for the package property VersionMajor contains a typo. Moreover, the descriptions for the two properties are swapped.
438329 - Script Transform Wrapper ignores IsNull setting
Trying to assign a False value to the _IsNull property of any column in the ScriptTransform will fail silently, and the value True will be assigned instead. Note that this is buy design since the user should assign a value to the actual property instead. The fix is to raise an error when this usage pattern is detected.
439132 - SSIS SmtpServer configuration property not set correctly
Using a configuration file to change the SmtpServer property of the Smtp Connection Manager does not work.
440035, 485112 - Add Parameter Size Property to Execute SQL Task
Execute SQL Task fails when using ADO.Net connection to execute a stored procedure with string output parameter.
440415 – Improved error messages related to offline operations in BIDS
The old error message was ambiguous. The new error is "An error occurred due to no connection. A connection is required when requesting metadata. If you are working offline, uncheck Work Offline on the SSIS menu to enable the connection."
440849 - ExecuteSQLTask: Data corrupted when using XML ResultSet
Data can be corrupted when using ExecuteSQL task to save xml content to a variable.
441588 - Lookup component does not emit final cache row count
The info messages output by the Lookup component do not show a final total of the number of rows cached (in full-cache mode). The fix should be a simple extra logging entry that displays the final count. Note that this is simple an informational change – the component does in fact cache all the rows correctly.
442309 - Data flow lines not displaying correctly.
In some circumstances the lines between components hosted in containers may not be drawn correctly.
442740 - Derived Column: potential for data loss when overriding input columns
In certain scenarios, intermediate results of calculations are not cached which can cause the final result to be erroneous.
442981 - Change in one path downstream from a multicast is seen in the other path
In some circumstances a multicast followed by derived column on one path will show the same change being effected on the other path(s).
445853 - SSIS Packages fail with "Package
When