Pros and Cons of using SQL Server Upgrade Advisor

Question

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.

Answer 1

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.

Answer 2

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