Some time ago, I wrote about the importance of thinking about Deprecated, Discontinued Features and Breaking Changes while Upgrading to SQL Server 2012 in a guest post I wrote on SQLAuthority.com (http://bit.ly/12f8pAQ).

A number of tools and utilities are shipped with (and in addition to) SQL Server. The tools are useful in various stages of the upgrade preparation process. One such utility is the SQL Server Upgrade Advisor, which comes into picture in the first step of the upgrade process – gap identification. The SQL Server Upgrade Advisor analyses a database and installed services on a given SQL Server instance for potential upgrade blocking issues and reports any areas that need user intervention.

Today, I will do a quick walk-through of the SQL Server Upgrade Advisor for SQL Server 2012:

• The SQL Server 2012 Upgrade Advisor can be found at: http://www.microsoft.com/en-us/download/details.aspx?id=29065
  
• SQL Server is part of the SQL Server 2012 Feature Pack
  
• The SQL Server 2012 Upgrade Advisor is dependent upon the Transact-SQL ScriptDom, which is also available as part of the feature pack
  
• The SQL Server 2012 Upgrade Advisor is also available on the SQL Server 2012 Installation Media

Installation

The installation of SQL Server 2012 Upgrade Advisor is simple – it’s a straight matter of clicking “Next” through the installation screens, and you should be all set.

Running the Upgrade Advisor

The SQL Server Upgrade Advisor executes a set of rule-based checks against the selected database and instance. Below is a screen-by-screen guide to running the Upgrade Advisor:

As you can see, there are two sub-utilities of the upgrade advisor: An Analysis Wizard and the other A Report Analyzer. First up, we will launch the Analysis Wizard.

In the feature selection screen, you can choose the “Detect” option to detect the installed features of the given SQL Server instance.

Click on the “Launch Report” button to launch the Report Analyzer.

As you can see, the Report Analyzer allows you to view the report for each individual instance/feature, and also provides you in detail, the reason why a particular warning or error has been reported. It also links up to it’s dedicated SQL Advisor help (from the “tell me more” link) for possible solutions.

Important Points to Note

When running the SQL Server upgrade Advisor, it is important to note the following:

• SQL Server Upgrade Advisor uses static analysis. Hence, it will not identify issues within the T-SQL code or within the application code
  
• The Upgrade Advisor can be executed infinite number of times because it does not make any change to the instance and/or database configuration
  
• The SQL Server Upgrade advisor can only be executed against SQL server versions supported by SQL Server 2012 for the upgrade. Therefore, it cannot be executed against a SQL Server 2000 instance, because that is not supported by SQL 2012 (Deprecated features-Valid compatibility levels–COMPATIBILITY_LEVEL 80 support-Msg 15048)
  
• You may be surprised to know that the Upgrade Advisor has been around since the days of SQL Server 2005!
  
• Finally, passing all checks in the SQL Server Upgrade Advisor does not mean that the database and/or application are following the recommended best practices for the deployment – please use other tools like the SQL Server Best Practices Analyzer for the same

Until we meet next time,

Be courteous. Drive responsibly.