Upgrading SQL Server 2000 to SQL 2008

Upgrading SQL 2000 to SQL 2008

I’ve got an installation with SQL Server 2000, Reporting Services and several DTS packages I’m looking to move to 2008.   I realise there’s going to be a lot of manual work to do, with the minimum likely to be replacing the DTS packages but I wanted to see how much of the existing system the upgrade could do itself.
I’ve never done the upgrade before so I’ll be following the on-screen prompts for the installation along with this MSDN posting.
First things first – the installation may need to install some pre-requisites.  In my case it was VS.NET 3.5 SP1.  After a reboot I decided to run the upgrade advisor.   Let’s see what the upgrade thinks of the current installation.

Install Upgrade Advisor.

This looks at any SQL 200 or SQL 2005 components installed on your machine / server and identifies issues which need to be sorted as part of the SQL 2008 installation.
You’re given the option of selecting the specific components you want to upgrade from:

Select components to install

  • SQL Server
  • Analysis Services
  • Notification Services
  • Reporting Services
  • Data Transformation Services
  • Integration Services

As you progress it’ll inform you if you’ve made invalid selections (Integration Services for a SQL 2000 database for example).   If you’ve made invalid selections you may have to go back through the wizard to change them.

You’ll then be asked which server you want to run the advisor against and the wizard will then take you through the options for each component you selected earlier.

So if you opted to upgrade SQL server, it’ll ask you which server you want to look at, and which database(s) on the server.
If you opted for reporting services again it’ll ask you which server hosts the reports.
If you opted for DTS packages, it’ll ask you which server holds the packages or where the DTS packages are if they’re held in files.

Finally you get to a screen which confirms your selections and asks you to confirm that you want to run the upgrade advisor.    Hit the run button and off it goes…..and it could take some time!

The upgrade advisor running

OK – it’s finished.   Well if you’re lucky you won’t get any issues.  I did.  To get more information on the issues you have to use the report viewer.
Take each component in turn and look at the feedback from the upgrade advisor.   The report gives specific information on issues which must be tackled before the upgrade, after the upgrade or those which are advisory.

Report Viewer Summaries

Database Server :
I didn’t get any specific issues in this area, just a non-specific advisory about “Other Database Engine upgrade issues”.    This seems to be a catch-all.  Looking at the meaning of the warning you’re given,  information on deprecated transact SQL statements, new data types are provided.   I had a look through but there was nothing that struck a chord with my database.   Maybe it’ll catch me out later?

Reporting Services:
Oooh issues to be addressed prior to upgrade!
Obselete or deprecated extensions were detected on the report server computer.  Upgrade can continue but deprecated funtionality is not supported.
Custom ISAPI filters are configured on the report server website.   Upgrade can continue but some functionality provided by the filters could be misssing.

Data Transformation Services:
There were a couple of important issues raised here, but the main killer for me was “Replace functionality of complex data transformation task encapsulated in Execture DTS 2000 package task after package migration”.  In a nutshell, the upgrade won’t migrate my DTS tasks for me and I’m going to have to sort them out once the upgrade has completed probably by re-creating them as SSIS packages.
If you decide that keeping your DTS packages is essential, SQL2008 has a feature which allows you to keep and run your old DTS packages.  It is worth noting that DTS packages are deprecated though,  you’re going to have to re-create them sometime.   Tools such as ‘DTS Backup 2000’ (freeware) can help you move your jobs to SQL2008 or alternatively you can do it yourselt, see my blog post on copying DTS jobs between servers.

OK I’ve ran the advisor, now it’s time to do the upgrade for real.

Upgrade SQL Server and installed Components

It’s time to do the upgrade.  From the Installation Center menu, select “Installation” and there’s an option on the right – “Upgrade from SQL Server 2000 or SQL Server 2005”.

Initially the upgrade wizard tries to identify any problems when the setup files are installed :


The next step confirms the product key for SQL 2008 or gives you the option of specifying a free edition and then asks you to agree to the license T’s and C’s before continuing with the setup.
Installation Steps :

  • Support rules check the installation setup files again.  Any failures must be corrected before the installation continues.
  • Select the instance of SQL server which needs to be upgraded.
  • Select the features required in the upgrade.
  • Specify details of the SQL instances which are to be updated.
  • Check there’s enough disk space for the upgrade.
  • Select the authentication mode for Reporting Services.
  • Select options for informing Microsoft about any errors and usage.
  • Results of upgrade rules check.  Any problems and the upgrade will be blocked (see below)
  • Ready to upgrade – what the upgrade process is going to do.

Make sure you’re using the correct version of SQL 2008 for the upgrade, the upgrade information is here.
Initially I was trying to upgrade SQL 2000 Developer Edition with Enterprise Edition – it won’t work!  The message I received was :
Rule “SQL Server 2008 Feature Upgrade failed.
If you need to dig around further to find out the problem, often some useful information can be obtained from the SQL Server setup log file too.

SQL 2008 Upgrade Rules
Next up I got an error : Rule “SQL Server 2005 Express tools” failed.
Bit perplexed with this one, I’d already uninstalled the express tools from the machine.   Check through the SQL 2005 options from the start menu to make sure you picked everything up and remove it using “Add / Remove programs”.

Finally Rule : “Previous releases of Microsoft Visual Studio 2008” failed.   This message asks you to install VS 2008 SP1 before installing SQL server.

SQL 2008 Upgrade rules

OK, all the rules have been met

Upgrade rules ok

The next step in the process provides a summary of what’s going to be upgraded, where the server is and the options selected for informing Microsoft of progress etc.
It’s time to hit the “Upgrade” button!!!   As with most upgrades / installations you then get  a progress bar indicating how far along the process is.    Time for a coffee.
And it’s done.  The upgrade needs a reboot to complete the process.

upgrade complete

After the reboot the upgrade wizard displays information about the upgrade and confirms the upgrade was successful.  It also provides a link to a log file providing a summary of the upgrade.  It’s now time to check the database, the reports and see about re-writing those DTS packages!

Upgrade Complete


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s