In SQL Server 2005, Microsoft introduced the Upgrade Advisor. This was a great idea to be able to analyze your current SQL Server in a safe environment and start the planning process of an important upgrade. The key element of any Project Plan is Risk Management and this tool allowed you to prepare for an upgrade by assessing risk proactively, without introducing risk itself. You could then assess each issue that the tool identified on a case-by-case basis and start planning the upgrade project in a controlled strategic manner. The good news is that Microsoft has continued with this tool for SQL Server 2008. I have been testing the new version of the tool and wanted to discuss it here.
What I like about the architecture is that we can install the Upgrade Advisor on a separate desktop computer and connect to the "legacy" SQL Server machine remotely. The Upgrade Advisor will then analyze in a read-only mode without changing anything on the existing server. In this way, you feel comfortable that the SQL Server will continue to operate in the normal way, while you can review the reports generated and start planning for the upgrade. The tool lists issues categorized as errors or warnings. Each issue has a related Help article under a link titled "Tell me more about this issue and how to resolve it". You can then mark each issue as "resolved" as you handle them one-by-one. The next time you run the tool, the resolved issues will not appear, with the idea being that eventually you will run the advisor with a "clean bill of health". Of course, Microsoft threw in a little trick in 2005. No matter what state your server was in, you would always get at least one error on the Database Engine entitled "Other Database Engine upgrade issues". Microsoft did not want anyone to be complacent during an upgrade so it hard-coded this one to force us to read the associated knowledge base article which listed general upgrade issues. We could then mark this issue as resolved and maybe get a clean slate next time. The same tactic is employed for 2008. At least it's only a warning this time.
The knowledge base that backed up the Upgrade Advisor in SQL Server 2005 was extensive and very helpful. Each article would clearly explain the issue and the steps to possibly resolve it. I wish I could say the same for the knowledge base in 2008. For instance, in my tests I received an error because I was hosting a Web Service natively on my SQL Server 2005 instance. This functionality, which was a cool feature introduced in 2005, has already been "deprecated" for a future release. I talked about the deprecation process in a previous blog entry. It identifies features that will not be supported in the future. I can only think that there is a major security issue with this feature since it is now planned to be removed so quickly. But, as I stated in the previous entry, a "future release" could be many years away. So I click the link to review the article hoping to find out the whys and wherefores of this issue but I am very disappointed. Under Description it reads "In SQL Server 2008, SQL Server native SOAP has been deprecated and will be removed in a future SQL Server release" and under Corrective Action it reads "Avoid use of SQL server native SOAP in new development work, and plan to modify applications that currently use it". Upon inspection of other issues, the knowledge base is equally light. The SQL Server help system "Books Online" is the best out there in my opinion and that includes 2008, but unfortunately, the Upgrade Advisor knowledge base for 2008 does not live up to that high standard. Once again, I am afraid this points to the lack of iterations during the Beta (or CTP) program. Another Release Candidate would have provided the window of time to take care of this type of thing.
Maybe the Upgrade Advisor should have an issue entitled "Wait for the next Service Pack before upgrading".
Ok, that may be a bit harsh. What's your opinion? I'd be glad to hear it.
Like this? Check out these other recent posts by me:
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.