Is it worth upgrading to SQL 2008?

this below article was published byNigel Maneffa, on www.sqlservercentral.com

I found it quite a good overview, while there is some ranting and raving, as to which I agree with, however after using the management studio for SQL 2008 (dev edition) I can see other undocumented and documented enhancements which are in standard edition, find them at the very bottom

I had downloaded and played a little with the SQL2008 CTPs, and the new features were impressing me, although it appeared that SQL2008 was a development of SQL2005, as SQL2000 was a development of SQL7 despite comments to the contrary. New headline features included backup compression, governors, policy framework, data compression, encryption, change data capture etc. But then I read the recently produced Microsoft documentation on version differences and see that none of them are in Standard edition, not one. They are all Enterprise features. SQL server may be moving more towards a data platform from just a database engine, but the platform appears to be somewhat narrow for non Enterprise users!

The official list of differences can be found here: http://download.microsoft.com/download/2/d/f/2df66c0c-fff2-4f2e-b739-bf4581cee533/SQLServer%202008CompareEnterpriseStandard.pdf

This posed a question – is it worth upgrading from SQL2005 Standard edition to SQL 2008 Standard edition?

Maybe we have been spoilt by Microsoft ‘giving away’ too many new features in the upgrade from SQL2000 to SQL2005. The potential performance/scalability differences between a say 32 bit 2 GB RAM SQL2000 Standard on NT4 Server and a 64 bit 32 GB RAM SQL2005 Standard on W2K3 Standard system is very large indeed, despite both the OS and SQL Server version being the Standard edition (a likely OS/SQL combination).

SQL2005 Standard offered such new goodies as clustering, unlimited RAM, mirroring (safety always on unfortunately), and log shipping – the unlimited RAM (to OS max) was a real eye opener when I saw the specification sheet, although the price of RAM made such levels of RAM just a paper exercise for typical Standard edition users. That is no longer the case, as RAM is now much more affordable. I would have capped the RAM on SQL2005 Standard edition to around the 8 GB mark, allowing an increase to say 16 GB in SQL2008. The 4 processor limit was a good selection IMO, although I can see disk IO being far more of a bottleneck in most servers of this specification.

SQL2005 Standard (still) did not support indexed views during optimisation, but I can afford to learn a bit about ‘noexpand’ with the money I had saved and still use the feature when required. As for partitioned data, that was ‘just for people with terabyte databases’ and data warehousing, and I would never see that sort of disk IO anytime soon. One feature that would have been useful was database snapshots, but I could see they would need to differentiate the versions. The parallel indexing I could live without too. They even threw in the (little used) CLR and service broker, and the (doomed) notification services.

One of the subsystems that I frequently use very heavily, merge replication, had so many new features in SQL2005 I could hardly keep count. I could also see on paper improvements for DTS/SSIS and reporting, but had written my own systems many years ago when SQL Server did not have them, so cannot comment on their improvements in practise.

Upgrading from SQL2000 Standard to SQL2005 Standard provided many new features. I remember the discussion at the time, when many thought that SQL2000 was good enough for many applications. I still think that is the case today to be truthful, but have used quite a few of the new SQL2005 features over the last year or two.

However, let’s look at what SQL2008 Standard offers over 2005 Standard.

  • There are no processor or RAM improvements, not that they are required any more. Mirroring looks like it might be improved marginally.
  • I am really peeved to see that backup compression has not made it to the Standard feature list.
  • There are the usual incremental improvements in SSIS and Reporting, again which I do not use.

To cap it all the (to me) business critical merge replication has gained no features, but its growing deprecated item list makes it look more like it’s on the way out than moving ahead. The new sync services look interesting in the longer term, but offer nothing except a likely version 1.0 buglist for those wishing to take the early adopter plunge. With the massive increase in the number of Enterprise features I would have thought it would make great sense to throw in a few of the ‘old’ Enterprise features into the Standard edition – database snapshots would have been nice, or lock pages in memory.

Forgive me if I feel short changed, but that feature list does not look a great reason to upgrade. Or I have I failed to turn over the magic stone with hidden features?

So I am left looking at programming/development enhancements – date/time fields, HierarchyID, spatial, merge SQL statement etc. Nice but not critical. There are 2 features that have gained relatively little attention but might improve application performance dramatically for the applications I see – optimize for ad hoc workloads, and filtered indexes. It appears on the surface that these features apply to all editions.

So I am left with the question of whether to upgrade, or wait till the next version of SQL server. Or in the longer term investigate ‘cheaper’ alternatives such as MySQL, which will become ever more viable as the performance of hardware hides any performance deficiencies (either perceived or real). Bearing in mind I have ‘free’ upgrade rights within our Microsoft licensing agreement, I would be very worried if someone that can upgrade for free cannot see the benefits of using the new version.

A related topic is that the CTP’s and developer versions of SQL Server are in effect Enterprise versions, with all the features always on. I can understand this (particularly with the CTPs), but surely it must be simple to add a switch to tell developer version which edition you wish to emulate, so it is possible to develop against the edition that will be used in production. This problem was annoying in SQL2005, but the feature is critically required now, with the vast difference in feature set. I see that there is now a management view that tells you which Enterprise features need to be dropped to use a database in Standard edition.

It could be that Microsoft has done their homework well, and are really targeting the ‘Enterprise’ customer, and are less concerned about the Standard version than they were a few years ago as SQL Server moves more upmarket. I think that Microsoft should make sure that everyone gets something from upgrading, not just being forced to do so as the support for the older product eventually runs out. If they don’t provide improvements I can see slippage of SQL Server customers at the lower to middle range products.

Of course, if you run SQL 2000 Standard, upgrading to SQL2008 Standard (or maybe workgroup depending on which features you require) would be a great upgrade. Or am I already getting good value for money and should just be pleased that I am running a very reliable database system, and be happy to pay extra if I need any of the new features.

What do you think?

By Nigel Maneffa, 2008/10/21

So I said some undocumented changes, well at least I think they are undocumented, I have not yet come across any reading about them.

1. When you are restoring a file from the a device, and lets say the backup came from a different server, and the SQL data
and Logs resided in a different drive or location, you would always seem to have to go to the ‘options’ tab and change the location to your new location, even with all the Service Packs applied SQL 2005 continued to annoy me with this, while funnily enough SQL 2000 corrected the location automatically. Seems 2008 also does this too…

2. This is documented but worth bringing up powershell integrated with SQL 08, whilst we could powershell with 05, its just made it a little quicker by allowing us to click on the database we want to start powershell in, just another time saver.

3. Also documented but again a nice time saver rather than having to run TSQL statements to find info about the database, maintainance tasks, security, performance and more all we now have to do is right click the database and choose ‘Facets’

there are some other small functions that are helpful, which is across the most SQL 08 range.

I am also VERY disappointed that the backup compression / encryption didn’t make the cut in std edition, this should not be just an enterprise feature!


One Response to “Is it worth upgrading to SQL 2008?”

Leave a Reply