One of the things that made me smile at my launch presentation on upgrading SQL Server was that when I asked if everyone had heard of the upgrade advisor everyone in the room put their hands up. This is because most of the support issues raised by customers about upgrade use this template…
DBA: My upgrade didn’t work properly [insert detail here]
Support: What did the Upgrade Advisor report when you ran it?
DBA: What the [insert favourite swear word here] is Upgrade Advisor?
This isn’t necessarily the fault of the DBA, it is another illustration of the fact that there is usually a tool or some help on Microsoft.com, but that it can be hard to find. In the case of Upgrade Advisor, the tool isn’t included in the download or media for SQL Server so you have to download it, also it didn’t come out until SQL Server 2005 sp1.
So for those of you considering upgrading SQL Server in any combination of 2000, 2005 and 2008, your first port of call should be Upgrade Advisor. It is essentially a reporting tool that takes the following as input:
- a database
- a text file containing SQL
- a SQL trace
The last two are just as important as not all of the SQL that hits the database is in the database e.g. applications, web services, stored procs called from isql batch files and you need to either find where the code is or ruun profiler to get traces of what is actually running against your database.
The output is a report that tells you how severe the problem is and when it should be fixed. It can be run from any client with .NET 2.0 framework installed and does not affect the targeted databases when it is run. It can take a while to run as it has to check each object in the database so the more there are the longer you have to wait. I mention this because one of the first thing you want to do when you decide to bite the bullet and upgrade is to get rid of all the redundant code and object in your database.