10 things I love about SQL Server 2008

Everyone has their favorite features; here are mine.

Being a SQL Server MVP you might say that I have a pretty good idea of what the product can do.  And if you ask all the experts you'll get some overlap, but their lists will be completely different.  And even my list changes from day to day.  So here's my list (today) of my favorite features of SQL Server 2008.  These aren't necessarily features that are new to this latest version, but they're definitely present. I like anything that saves me time and since I do a lot of admin and I don't have a lot of time, I really like it when I can do something in 1 or 2 steps instead of 7 or 9. 

10. OBJECT_ID() - The new and improved object_ID() function is quite useful and not many people know about it.  It now takes a DB_ID() parameter so you no longer have to do dynamic SQL to find out the name of the object you're looking for. 

9.  Backup Compression - This doesn't deserve to be #9 but at least it made the list.  Companies are always running out of space to house backups so this is a huge step for us.  Good job.

8.  Fully scripted installs - You can fully script your installs so you don't have to remember to do certain things.  In my shop, we install SQL to a non-standard location and we've got a couple different groups that need sa access to all the boxes.  So it's handy to put all that in the config file and just let setup do its thing.  All of our boxes are setup right without me having to check that I remembered everything.

7.  No IIS - IIS is no longer required for SSRS.  It was always a huge pain to have to go back to the server team to have them install IIS because the disk wasn't available and you couldn't do it yourself.  And it's always when you get in the middle of the SQL install that it blows up on you.  So yeah, I think we're all glad that SSRS comes with its own web server now.

6.  Table partitions - Not only can I segregate my I/O by putting different partitions on different disks, but I can also reindex partitions separately.  So I can reindex really large tables in smaller chunks to make it easier on the user processes.  What can I say, it's the bomb.

5.  Online Reindexing - I love being able to keep my DB up while reindexing is going on.  It seems ridiculous these days to kick users out of a DB or lock them out of a table to do routine maintenance.  And I'm fully aware that SQL Server isn't the only DB that has online reindexing.  That doesn't mean I can't like it though.

4.  CDC - Change Data Capture is just awesome.  It was originally built as a way to allow us to write custom replication scenarios but it's so much more.  It uses the replication log reader, but instead of sending the data changes to a distribution DB, it just logs them to a table.  You can then retrieve the changes and send them wherever you like using whatever method you like.  If you've got a small number of tables and you want to be able to send incremental data changes to any number of other boxes, then CDC is the way to go because it doesn't have the overhead of traditional replication and is much easier to manage.  You can use CDC with a small or a large number of tables, but for a lot of tables, replication may be the way to go.  The point is it's your choice and CDC can get you up and running quickly.

3.  Multi-server query - This is one of those features that was long overdue when we finally got it.  So many companies have so many DB servers and it's really hard to manage them all one by one.  And multi-server query is a feature of SSMS that allows you to run a single query against any number of SQL Servers at the same time and get a single result set back.  This can be really handy when you need to run the same query or the same script on many servers.  This could be a query that checks the status of all the backup jobs, or putting a control table of some kind on all the servers, etc.  The more complicated the task, the more clever you have to get about writing it so that it runs against all the servers without any errors, but it can be worth it.  So it's a really good way to group some of your more traditional T-SQL tasks.

2.  SSIS and SSRS - So wait a minute, you're telling me that not only do I get a good DB platform, but I can also have an enterprise-class ETL tool and reporting server for free?  Where do I sign?  All jokes aside though, it's such a cool thing to get the BI stack for free when you buy SQL Server and why more companies don't take advantage of it is beyond me.  I still consistently see companies spending hundreds of thousands of dollars on apps like Business Objects Enterprise when they don't use any of the high-end features.  For some reason though they feel the need to spend all that money for something they could have for free.  Whatever guys, SSIS and SSRS are fabulous tools that do indeed scale to enterprise workloads, so if you insist on paying tons of money for something you've already got in your shop then go right ahead.  In my experience though, it typically ends up biting you in the ass because those products only get more and more expensive and it gets very cost prohibitive to keep up with the licensing. 

1.  Powershell - there's really not much I can say about powershell that I haven't said 100 times.  Powershell allows you to not only run almost anything against any number of servers you like, but also against any number of objects like DBs, accounts, schemas, tables, etc.  Look at it like multi-server query with a PhD.  I've come across nothing that I've needed to do that I couldn't do with powershell, and usually much easier than with its T-SQL counterpart.  Powershell has literally changed the way we do our jobs and the way we think about managing large environments or large servers.  I can make changes to every server I own with just a couple lines of code in many cases and that's pretty powerful.  So when you're counting the ways you like other RDBMSs, ask yourself how you manage those products in masses, and then consider how you manage SQL Server with powershell.  Powershell will win every time.

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

Copyright © 2010 IDG Communications, Inc.