SQL Server Upgrade Advisor
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.
Technorati Tags: SQL Server 2000,Upgrade
Comments
Anonymous
April 02, 2008
One user asked me from where they can download SQL 2008 Upgrade Advisor tool, its hard to finding from Download center!Anonymous
April 03, 2008
Satya It's actually part of the download of CTP 6 (February). You just need to install itAnonymous
April 06, 2008
SQL Server 2008 ではPolicy Management が Best Practices Analyzerを置き換える(Upgrade Advisorは2000から対象)Anonymous
December 03, 2008
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 michaeltrig@si.edu phone: (202) 633-0614 direct fax: (202) 312-2865 front office fax: (202) 633-2953 email: trigonom@si.eduAnonymous
February 25, 2009
Check out the following for running the upgrade advisor on a cluster: http://blogs.msdn.com/jpapiez/archive/2005/09/28/474930.aspxAnonymous
April 01, 2009
How to run this Upgrade advisor on an Virtual serve or Clusterd server where in whihch 2005 OLAP services are installed...Anonymous
April 02, 2009
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 (afryer@microsoft.com) if you want to go into more detailAnonymous
January 14, 2010
The comment has been removedAnonymous
January 18, 2010
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.Anonymous
July 15, 2010
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, Server1InstanceA
- Enter server name (without any instance name) - Server1
- Do not hit the detect button, instead, click on 'Next'
- In the second step, you'll have an option to enter the instance name - i.e, InstanceA (per the above example).
- Provide User credentials to be used to connect to the SQL Server and then click 'Next'
- 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!
- Anonymous
April 16, 2014
We have database in 100 of GB's. How much time it wil take to run upgrade advisor? - Anonymous
April 17, 2014
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
Andrew - Anonymous
November 06, 2014
Over the last few months I have done quite a few sessions on Windows Server 2003 and this has invariably ended in some interesting conversations which I thought I would write up now I have a few days in front of a desk. The objections to upgrading generally