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:
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.
One user asked me from where they can download SQL 2008 Upgrade Advisor tool, its hard to finding from Download center!
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!
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 (firstname.lastname@example.org) if you want to go into more detail
Am trying to connect to named SQL instances with a '\' character in the name - eg. SERVER\INSTANCE
Upgrade Advisor cant connect.
Can you help?
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 helpAndrew