Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server Upgrade Advisor

SQL Server Upgrade Advisor

  • Comments 13
  • Likes

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, 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.

Technorati Tags: ,
  • One user asked me from where they can download SQL 2008 Upgrade Advisor tool, its hard to finding from Download center!

  • Satya

    It's actually part of the download of CTP 6 (February). You just need to install it

  • SQL Server 2008 ではPolicy Management が Best Practices Analyzerを置き換える(Upgrade Advisorは2000から対象)

  • I installed the SQL Server 2005 Upgrade Advisor so I can analyze our SQL Server 2000 instance and DTS packages.

    Even though SQL Server 2000 is up and running, it does not recognize our instance when I click the Detect button in the wizard, nor does it recognize it when I key in the instance name.

    We are running on a Windows Cluster.

    Is the cluster environment the reason it cannot detect the instance?

    Is there a trick or a different set of procedures to follow when installing and running the tool on a cluster?

    If you can provide any information or guidance, I would greatly appreciate it.

    Thanks in advance!

    Mike Trigonoplos

    Smithsonian Institution

    Office of the Chief Information Officer

    phone:            (202) 633-0614

    direct fax:        (202) 312-2865

    front office fax: (202) 633-2953


  • Check out the following for running the upgrade advisor on a cluster:

  • How to run this Upgrade advisor on an Virtual serve or Clusterd server where in whihch 2005 OLAP services are installed...

  • Install the upgrade advisor on your local machine and when you run it, it asks you which instance/server you want to analyse (in just the same way as you connect to sql server in management studio, or enterpsie manager).  So for clusters you enter the name of the cluster and instance.  

    You also mention olap services, that was in SQL Server 7 so are you upgrading from that? If you are you have to upgrade to sql server 2005 as interim step and actuall my advice would be redsign the whole cube setup again in sql server 2005/8 from scratch as an upgrade cube will be far from optimal, because it will not use all the new stuff in the later version properly, please email me ( if you want to go into more detail

  • Hi there

    Am trying to connect to named SQL instances with a '\' character in the name - eg. SERVER\INSTANCE

    Upgrade Advisor cant connect.

    Can you help?

  • Ewan

    I am surpirsed that you can creat an instance with a special character in like this, but no I don't know how to get aorund trhat I'm afraid, but I would chnag it if you can after upgrade.

  • Ewan, I'm assuming that your question is still unanswered. If that's the case, given below are the steps to be followed to run upgrade advisor for a named SQL Server instance - say, Server1\InstanceA

    1) Enter server name (without any instance name) - Server1

    2) Do not hit the detect button, instead, click on 'Next'

    3) In the second step, you'll have an option to enter the instance name - i.e, InstanceA (per the above example).

    4) Provide User credentials to be used to connect to the SQL Server and then click 'Next'

    5) All the databases on that SQL Server should now be visible, for you to choose the ones you want to run Upgrade Advisor on.

    In short, there's no need to hit the 'detect' button. The SQL Server will automatically be detected by the next steps in the wizard.

    Hope this helps!

  • We have database in 100 of GB's. How much time it wil take to run upgrade advisor?

  • SQL DBA whoever you are, It take hours, but what's it's looking at is the views and schema etc. not the data itself. Also if you run it close to the server rather than on a slow network that'll help

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment