Technology, training, learning and development blogs

Getting some bang for your buck

What are the mid-term benefits of upgrading to SQL 2008

In my last blog, I mentioned some of the out-of-the-box benefits of SQL 2008; the ‘free’ benefits, if you’re prepared to buy Enterprise edition.  But what about the other benefits if you’re prepared to put a little work in?

 

1.       Policy Based Management

Think Active Directory  Group Policies for SQL, but without Active Directory.   I can configure my servers into groups, and apply policies to the group.  I can then either get a report of all those servers and databases that don’t comply, or enforce compliance.  Have you seen all those best practice documents for SQL Server?  Well, the good news is that Microsoft has ported them into SQL 2008 policies.

With the average SQL administrator responsible for about one hundred instances, this is going to make the admin’s job so much easier, he’ll have to find other things to do to make himself look busy.

2.       Performance Data Collection

Another feature to make the multi-instance administrator’s life easier.  Your SQL Servers pass performance data to a central database server.  Standard reports are available on each target server, though the data is held centrally.  You can also create your own custom reports.  Now with one job you can back up your performance data from one central place.

3.       Enhanced Date and Time support

In SQL 2005 you have two choices for time data.  DateTime, and SmallDateTime.  Most people just throw in a DateTime, even if all they want is the date, forgetting that the 8-byte datatype allows for 3.3 microseconds, probably more than they wanted.  But what about those that just want a date, or those with, say, scientific data that needs more accuracy?  SQL 2008 introduced some more DateTime datatypes.  The imaginatively entitled DateTime2 allows time to an accuracy of 0.9999999 seconds, which should keep some people happy.  The Date datatype allows …erm …the date.  That is to say, there’s no time element, which makes for smaller fields, and thus quicker read/writes.

4.       Transact SQL enhancements

Lots of nifty little enhancements, such as multiple row inserts from a single INSERT statement.  The new MERGE statement is known colloquially as UPSERT.  It will insert if the row is not there, or update it if it is.  Table-valued parameters allows you to pass a rowset into a stored procedure in one call, and grouping sets allows more flexibility with GROUP BY statements.

5.       Sparse Columns

Supposing you have a table with , oh, let’s say two hundred columns, but typically only ten contain non-null values.  In 2005 you still had to reserve space for each column, even if it was a varchar.  Now you can specify that the columns are SPARSE in the table design, and null values take up no space.  Nada.  Zip.  Nothing.  Again, less space, better performance

6.       Service Broker enhancements

You can now set the priority for the processing of message-based conversations.  You can use a new diagnostic utility to analyze the configurations between services for troubleshooting.

 

So there you have them, various new features that you need to tweak and tune your applications and databases to use, but may well be worth the effort in a relatively short time.  And you don’t need Enterprise edition to use them.