At first glance, I was a bit critical of the latest version of the Upgrade Advisor. Well, after going through that painful process, I proceeded onto my upgrade from SQL Server 2005 to 2008 using the "in-place" method. I was pleasantly surprised how smooth the transition was. And everything seemed to work after the upgrade. Awesome! I knew Katmai would deliver...
There are a couple choices when upgrading: Migration vs In-place Upgrade. The migration path allows you to install a fresh instance of the latest release and then to copy databases over from the legacy instances to the new instance. The Copy Database Wizard is the easiest method to use. However, Detach/Attach and Backup/Restore work just as well. The Wizard, however, wins my vote because it prompts you to also copy Server level objects such as Logins which would have to be done manually using the other methods. Migration is attractive if you want to start with brand new hardware and a brand new configuration. The downside is that the clients will need to be updated to point to the new server after the transition. If things go wrong you can always revert to the old installation. The in-place upgrade may be attractive where the same hardware will be used and the upgrade is required on all databases at the same time. If the upgrade is judged to be low risk, then an in-place upgrade may be chosen. In the case of upgrading SQL Server 2005 to 2008, the new release is evolutionary not revolutionary, using the same architecture so an in-place upgrade may be a common choice. Just make sure you have a good backup of the server in case things go wrong! If you are upgrading from SQL Server 2000, you may choose to purchase new hardware and go the migration route because of the significant differences. As always, it depends on your situation.
I chose to perform an in-place upgrade on my server after reviewing the output from the Upgrade Advisor. Not many serious issues except some of the deprecated features which I can take care of later. I ran the upgrade using the install program against my SQL Server 2005 SP2 instance. I had to make sure my Windows Server 2003 was up to SP2 also before proceeding as the install checks for that. It also installs the .NET Framework 3.5 as a prerequisite. The upgrade took around 30 minutes and required a reboot. I tested SQL Server Management Studio against my databases and all looked good. I used Excel 2007 to access one of my upgraded cubes using Analysis Services and that worked too. I launched Reporting Services and my existing reports continued to work as designed. Business Intelligence Development Studio launched Visual Studio 2008 and I was able to open my Report Project successfully (after a minor conversion process - still worth making a backup, just in case). I modified some existing RDL's using the slick new Report Designer (which has been improved immensely over the early CTPs). I even updated one of my reports to include the new Dundas feature of dynamic scalebreaks and it worked like a charm. Very cool.
My confidence has been restored in Microsoft technology! I am now excited again to be teaching SQL Server 2008 to our early adopter clients. What a difference a day makes!
Brian D. Egler, MCITP/MCSE/MCT 2009, is currently an instructor with Global Knowledge, teaching various Microsoft training courses. He is a SQL specialist with a focus on SQL Server, Windows, .Net and XML. Egler has been a technical instructor for over 20 years and has more than 10 years experience with SQL Server, data modeling, database design, application development including IMS, DB2, Sybase. Every year he runs the Boston Marathon for cancer research.