Pros and Cons of using SQL Server Upgrade Advisor

DSSH 61 Reputation points
2021-02-09T16:01:58.893+00:00

To all,

My organization is considering using the upgrade advisor tool for sql server inplace upgrades. Can anyone direct to information that documents the pros and cons of using the tool? Our main concerns is if the application will change anything on the OS or files. We will be upgrading from 2008 to 2016.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,149 questions
{count} votes

Accepted answer
  1. CathyJi-MSFT 22,326 Reputation points Microsoft Vendor
    2021-02-10T06:19:32.58+00:00

    Hi @DSSH ,

    > Our main concerns is if the application will change anything on the OS or files

    No, the upgrade advisor only gives you the report about the upgrade issue, such as the option of seeing the affected objects or of accessing documentation that discusses what the error is and how to resolve it. You need to resolve it manually.

    By the way, as Tom mentioned you can use Data Migration Assistant Tool.

    Quote from an old thread, What is the difference between the Upgrade Advisor (UA) and the Data Migration Assistant (DMA)?

    All versions of UA are officially deprecated and have been replaced by DMA for your migration needs. The core differences between the tools are as follows:

    • UA performs only assessments, DMA enable you to perform assessments and as well as migrations. DMA provides E2E migration experience.
    • UA only provides assessments migrating to on-premises SQL targets, but DMA extends to Azure Database platforms as well.
    • You need to have a specific UA tool, per each target SQL Server version, but DMA is a unique tool and supports all target SQL versions, starting SQL Server 2012.
    • UA enables assess the workload traces, but DMA only assess the schema and static objects.
    • All versions of UA is officially deprecated and replaced by DMA.

    > We will be upgrading from 2008 to 2016.

    You need to apply SP4 for SQL server 2008, then upgrade SQL 2008 SP4 to SQL 2016.

    According to MS document, SQL Server 2016 supports upgrade from the following versions of SQL Server:

    SQL Server 2008 SP4 or later
    SQL Server 2008 R2 SP3 or later
    SQL Server 2012 (11.x) SP2 or later
    SQL Server 2014 (12.x) or later


    If the response is helpful, please click "Accept Answer" and upvote it, thank you.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Tom Phillips 17,741 Reputation points
    2021-02-09T18:31:19.743+00:00

    You should use the new "Data Migration Assistant'. This is tool does NOT install on the SQL Server. You install this on a workstation and connect to the SQL Server database engine to evaluate it.

    Please see:
    https://learn.microsoft.com/en-us/sql/dma/dma-bestpractices?view=sql-server-2017

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.