Wednesday, July 8, 2009

R2 – Upgrading from SQL 2005 to SQL 2008, part I

With OpsMgr R2 support for SQL 2008 (SP1) has been added. So besides the regular upgrade path from OpsMgr SP1 to R2 another upgrade is possible as well: upgrading the underlying SQL-server instance to SQL 2008.

image 
In an upcoming blogposting I will go into the technical details about this upgrade process like the needed preparation, the order in which to perform the upgrade and so on (I found one of mine OpsMgr test environments to be running on SQL 2005 so this is a good candidate for this upgrade).

But before that I want to have answered, or better, try to answer this question – which is very important –

Why upgrade to SQL 2008?

Actually there are multiple reasons, some are business related:

  1. Operating costs
    New functions like encryption, Resource Governor and Auditing are natively available so no additional costs have to be made for this kind of functionality.

  2. Mainstream support on SQL 2005
    How long will Microsoft offer Mainstream support SQL 2005? Check here for more information on this topic.

  3. Return On Investment
    With SQL 2008 server hardware can be used for an extended amount of time. It supports hot pluggable cpu's. With Resource Governor one can size it to the company needs.

  4. Third party application support
    Many vendors are working with Microsoft ensuring their products supports and uses the new features of SQL 20008 as well.

Other reasons are technology related. SQL 2008 has not only enhancements to features already present in SQL 2005, but has also a whole lot of new features like these ones:

  • Reporting Services
    No dependency of IIS, improved charting and graphing. My personal experience with this component is that is more robust than it was with SQL 2005. For OpsMgr Reporting certainly a good argument.

  • Performance enhancements
    SQL 2008 is faster, so OpsMgr will benefit from it as well.

Besides reasons to upgrade to SQL 2008 there might be valid reasons not to do so.Like third party MPs with related databases and it’s vendor doesn’t support SQL 2008 (yet). Another valid reason might be the SQL server itself: it is hosting databases for other applications as well which don’t support SQL 2008.

As with every other upgrade, preparation is the keyword. And testing it – after a good solid upgrade plan has been written – is also very important.

Nowadays with virtualization a good testlab is easily built: P2V the SQL server, P2V a DC (needed for authorization), P2V the RMS and put these three virtualized servers in their own VLAN, completely separated from the rest of the IT environment. Snapshot them and off you go. If you wreck anything, find out why, rewrite that part of the upgrade plan and test it again until all is OK.

Some helpful links:

  1. SQL Server 2008 Upgrade Technical Reference Guide
  2. SQL Support Blog
  3. To upgrade from SQL Server 2005 to SQL Server 2008

No comments: