question

DSSH-8859 avatar image
0 Votes"
DSSH-8859 asked ClintR-2195 published

Pros and Cons of using SQL Server Upgrade Advisor

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Curious if you had any DTS Packages that still required migration to SSIS in SQL Server 2008R2? We did and it was a challenge to finally get confirmation that the Data Migration Assistant does not support any analysis of DTS Packages (but does with SSIS Packages), like the old Upgrade Advisor did.

0 Votes 0 ·
Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @DSSH-8859,

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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://docs.microsoft.com/en-us/sql/dma/dma-bestpractices?view=sql-server-2017

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.