Technology, training, learning and development blogs

Benefits of SQL 2008 upgrade

What are the immediate benefits of an upgrade

SQL 2008 – is it worth it?

 

Whenever customers are faced with the dilemma: to upgrade or not to upgrade, they usually have to weigh the cost of the upgrade with the business benefits.  Sometimes this can be pretty obvious, if for example the benefits can be realised immediately with little or no work.  Sometimes it’s tougher, when the benefits are long-term, and the road is long and hard getting there.  Of course, at some point we all have to upgrade; there are precious few people out there on Windows 3.1, but the benefits versus the effort and cost can affect when we upgrade.

 

So is SQL 2008 worth upgrading to for immediate short-term benefits?  The answer, as it almost always is with databases, depends.

 Let’s look at some of the new features that can give you business benefits for minimal effort.

1.       Data and backup compression

With a simple ALTER DATABASE command you will be able to compress your database.  This will be transparent to the end user, but the effect is that you will be able to pack more information into a row, and more rows into a page.  This is of benefit because, though disk prices are falling, the demand for the amount of data we are storing is outstripping the price fall.

Also, with the addition of an extra word in your backup command, you can compress your backup, reducing your backup times, and more crucially your restore times, dramatically.  The result, say Microsoft, is comparable to products like LiteSpeed.

2.       Transparent data encryption

With a click of the mouse, or the ALTER DATABASE command, you can encrypt an entire database.  Again, this is invisible to the end application.  Remember the stories of data disks lost in the post?  This will make them safer.  Even when backed up, the backup is automatically encrypted.  Indeed, as the encryption takes place when the page is written to disk, it’s impossible to take an unencrypted backup of an encrypted database.

3.       Resource governor

When there is contention for the CPU or memory between queries, you can now dictate how much of the pie each group of processes take.  Want to limit your maintenance tasks to 20% of the processor?  Make it so, Number One.

4.       Query optimizer

Various changes to the query optimizer and the storage engine make SQL more scalable and perform many operations faster.

5.       SQL audit

A common question I often get asked by financial customers is, can I audit select statements?  Not easily, is the 2005 answer.  Even insert, update and deletes would involve triggers, which slow the operations down considerably.

Now, with 2008, there is a very efficient, low impact auditing system that allows you to log all your activity.  Yes, even select statements.

6.       Reporting and Analysis Server scalability

On the BI track, there have been a number of enhancements that improve even more the scalability of these products out of the box.

7.       Filtered indexes

This is quite a cool tool for the administrator.  Let’s suppose you have a large sales table, but 95% of it is static data, orders that occurred during the year, but don’t change now.  5% is current data, changing all the time as orders are taken, fulfilled, invoiced and paid.  You can filter your indexes on, say, the last month. So maintenance tasks like index defragging take a fraction of the time, because the index only covers some of the rows.  Cool, eh?

 

Now comes the painful bit.  Do you like those features?  Are you champing at the bit to upgrade?  Well, dig deep, my friend.  Everything above, apart from the filtered indexes, are only available in the Enterprise Edition of SQL Server.  Those in the know at Microsoft say that this time round the Marketing guys convinced the developers that Microsoft have to start driving the sales of Enterprise.  There are a bucketful of cool new features in SQL 2008 that are available to Standard users, but they need moderate to significant changes to application or database design.  Nearly all the out-of-the-box benefits will have to be paid for.