10 things I hate about SQL Server 2008

These are some of the things I don't like about SQL Server 2008.

I feel compelled to write a counter to last week's blog on the 10 things I love about SQL Server.  I think anyone with any decent amount of experience in a product will gather peeves or wishlists.  So this is both my peeve list and my wishlist.  It's hard to order these correctly so don't put too much stock into the way they're ordered.  And again, ask me tomorrow and the list may change.

10.  Many problems with SSIS.  It's a nice framework, but there are some big things missing that they haven't bothered changing.  Working with xml isn't easy, neither is having reusable components.  One of the biggest things that's just ridiculous is that you can't reasonably import a simple csv with SSIS.  CSV is the most common form of text export on the planet yet SSIS seems to blog up when it comes across embedded text qualifiers.  DTS handled it just fine, but the SSIS folks have seen fit to remove that troublesome feature from the product.  I don't know if they're trying to teach us all a lesson about how to form text files properly or what, but we can't always control the feeds we receive.  I've recently had to spin up a 2000 instance for the sole purpose of handling an embedded text qualifier because SSIS is too big and enterprisey to deal with it.  There are some other things that are wrong with it too but I can't spill them all here.  I could write an entire essay on what's wrong with SSIS though.

9.  Now this one is just dumb.  You can't switch a DB to multi_user w/o kicking everyone out.  I understand that when I put the DB into restricted mode that everyone has to come out and then it'll let the DBOs back in... but what I don't understand is that when I got to put it back to multi_user, why everyone has to get kicked out again.  It's in the name... I want everyone to be back in the DB, so why can't it just start accepting connections again?  Why do I have to kick the DBOs out just to let other users in?  Tell me now or else.

8.  Compressed data doesn't stay compressed.  Now, what I mean by that is that the compressed data can't stay compressed across the wire.  Let's look at this logically.  You compress data on large stores, typically a DW.  Now, let's say that you need to move a good portion of that table (or even the entire thing) to a data mart or some other downline system.  Wouldn't it be nice that if since you have a huge amount of data that you need to transfer, and it's compressed, that it stays compressed over the wire and just gets laid down on the other disk?  Afterall, what's the real point of having this kind of disk savings if you can't take advantage of it during your transfers?  And yes, I realize some of the problems with that, but you guys at MS are smart, you'll figure it out.

7.  You can't create a user based off of another user as a template.  That's one of the biggest requests we get from our app groups.  The ticket simply says, I need you to give this user the same rights that this other user has.  That's great, but when you look into it there are a lot of specialized perms and no groups setup so you have to mine that info out and even still it takes a few passes sometimes.  And I know MS wants us using groups for that kind of thing, but you know what, sometimes groups aren't set up and it's not practical to set them up right this second.  And users can belong to a lot of groups esp when there are lots of DBs on the server, so you'd still have to search through all of the different DBs and see what groups the 1st user belongs to and try to mirror that with the 2nd user.  So groups still won't solve the problem.  What we need is some kind of template flag so you can just say something like 'Create Login from User1'. 

6.  Agent jobs don't have fine-grained perms.  This one is kind of a big one because it happens quite often that you need to be able to give a small group of people the rights to admin their set of jobs, but nothing else and you can't easily do that today.  Today you can admin any job you own, and that works fine for an individual, but if you've got a small team that needs to monitor these jobs they can't all be the owner.  And groups can't own jobs so that won't work.  Your only native SQL recourse is to add them all to the role in msdb that allows them to admin jobs, but that gives them rights to all the jobs and that's not acceptable.  So we end up putting together some kind of weird solution where we write them a custom SP that they can call to do what they need, or we give them a web frontend to do it.  Either way it's not elegant.  What we need is the same level of perms on jobs that we have on every other object on the server.

5.  As fabulous as the scripted setup is, setup has problems like not uninstalling correctly and not being smart enough to know when a component is already installed... so instead it fails.  The logs are also very difficult to go through.  It doesn't happen all the time, but it happens enough to be a problem.  Especially when SQL2K8 first came out I remember having fits with these servers because when trying to upgrade from SQL2K5, the install would fail for some small reason and then you could almost never get it back on track.  And the logs did very little to help you actually track down the issue.  They're even cryptic to members of the setup team at MS, and if they can't read them then who can?  So the installer really needs to be smarter about failures and give us some answers in plain English so we have even a slight hope of fixing the problem.  And if something is already installed, don't barf on us, just recognize that you don't have to install it and move on... geez.

4.  You can only mirror to 1 server at a time. And you can't read from a mirrored DB.  Yeah, I know all the arguments surrounding this and I'm just saying it would be nice to be able to do some reading from a mirrored DB, that's all.  And it would really really be nice if we could mirror to more than 1 server.

3.  It's absolutely fabulous that they gave us the ability to do minimally-logged inserts in SQL2K8 without having to use a SELECT INTO statement.  However, the fact that you can't force the engine to do a minimally-logged operation can negate that.  You can have all the right things in place and the engine may still choose to do this as a fully logged op.  I can understand where there might be a couple minor exceptions (like inserting 2 small rows or something ridiculous like that), but for any fair amount of data you should be able to force it to minimize the logging.  It's all about speed here right?  When you're loading a DW you need to be able to load the tables as fast as you can and quite often you don't care about the logging.  You're willing to start over on the rare occasions when something goes wrong.

2.  Table partition schemes are limited to a single measure and partitioning type.  As much as I love table partitions it would be great if they went all the way with it and gave us the ability to not only have more than one measure for a partition, but also to partition different ways.  I hate to tout Sybase, but I really love the way they do it.  They give you 4 different ways you can partition a drive.  And while I don't remember them all off the top of my head here's an example.  You can partition with a scheme and tell it which data goes where, and you can tell it to do a round robin and write rows to each partition in that fashion.  So say you've got 4 partitions and 4 rows to insert.  Each row will be saved on the next partition in a round robin fashion.  This is an excellent way to just spread out performance because it's likely that each partition will be on its own disks.  So seeing how the other vendors have such rich partitioning, it would be really nice if MS did it too. 

1.  No OLR.  Companies have been making tons of money off of this for years now.  In 6.5 we had Object-Level Recovery, but they took it away to "protect us from ourselves".  Then they turned around and started using LiteSpeed internally at Microsoft because they wanted the ability to be able to restore individual tables, etc from their backups.  Personally, I think it's pretty embarrassing for MS to delete this feature and then rely on another vendor to come in and show them up like that.  And to this day, MS is still the only major vendor for SQL Server backups that doesn't have OLR capabilities for their backup files.  Quest, Red-Gate, Hyperbac (recently bought by Red-Gate), and even Idera all have OLR.

Join the Network World communities on Facebook and LinkedIn to comment on topics that are top of mind.

Copyright © 2010 IDG Communications, Inc.