SQL Server Upgrade Advisor is used by DBAs to Analyze and Evaluate the problems that can arise due to upgrading SQL Server to the specified version. Upgrade Advisor for the specified SQL Server version to which we want to upgrade needs to be downloaded and installed on the SQL Server machine where the SQL Server instance resides(We can install upgrade advisor or other servers and connect remotely to other SQL instances, but best way would be to install on same server or on Test server which is a similar replica of production server).
Mostly Upgrade Advisor runs fine without any problems, but at times you may face some errors. One of the issue we faced with running upgrade advisor on a SQL Server instance is as mentioned below.
Requested registry access is not allowed. (mscorlib)
at System.ThrowHelper.ThrowSecurityException(ExceptionResource resource)
at Microsoft.Win32.RegistryKey.OpenSubKey(String name, Boolean writable)
at Microsoft.Win32.RegistryKey.OpenSubKey(String name)
at Microsoft.SqlServer.UpgradeAdvisor.AnalyzerEngine.GetAnalyzer(AnalyzerType analyzerType)
at Microsoft.SqlServer.UpgradeAdvisor.AnalyzerEngine.RunAnalyzer(AnalyzerType analyzerType, String& reportFile)
The Zone of the assembly that failed was: MyComputer
– The above error can occur if the account which is used to run the Upgrade Advisor is not an Administrator account.
– RDP to the server using a local administrator account and this account needs to be part of SysAdmin server role in SQL Server.
– Launch Upgrade Advisor by right clicking on it and choose “Run as Administrator” option on Windows Server 2008 or higher.
– Make sure you use the Upgrade Advisor of same platform. For SQL Server x64 instance, use x64 Upgrade Advisor.
Purpose of SQL Server Upgrade Advisor
SQL Server Upgrade Advisor helps us in preparing for upgrades from lower version to higher versions of SQL Server. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues and the objects affected to be fixed either before or after you upgrade.
How to run Upgrade Advisor
– Install SQL Server Upgrade Advisor for SQL Server version to which you are planning to upgrade.
– Click Start, point to All Programs, point to Microsoft SQL Server (version) , and then click SQL Server (version) Upgrade Advisor.
– Provide with Server Name and select the required components.
– Click on Next, Choose SQL Server Instance Name and the authentication mode.
– Verify or Provide Login Credentials provided is asked.
– Select the Databases to analyze and Click Next.
– Click Run which will start the analysis and once completed will be provided with Report.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.