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:
- 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!
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?
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.
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.
OK, all the rules have been met
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.
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!