Sql-server – SQLServer Upgrade Advisor 2014 hanging

sql serversql server 2014sql-server-2008upgrade

I try to run SQL Server Upgrade Advisor 2014 against a SQL Server 2008 server.

Everything looks fine when connecting to the 2008 server and I can select which database I would like to analyze.

enter image description here

But once launched, it runs forever, without doing anything (staying at step Analyzing Rules: 0/112).

enter image description here

What's happening and how can I fix that ?

Both SQL Server 2008 and 2014 are Express versions and are installed on my laptop. Unfortunately, this is the only way I found to run the Upgrade Advisor (there are too many restrictions to run it on real prod / dev databases).

The SQL 2008 instance is with SP3 as needed.

Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)   Sep 21 2011 22:45:45   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) 

When the Advisor is frozen, we can see 4 sleeping queries (no active ones):

SELECT name,
       cmptlevel
FROM
  (SELECT name,
          dbid,
          cmptlevel,
          DATABASEPROPERTYEX(name, N'UserAccess') AS 'UserAccess',
          DATABASEPROPERTYEX(name, N'Status') AS 'Status',
          DATABASEPROPERTYEX(name, N'IsInStandBy') AS 'IsInStandBy'
   FROM master.dbo.sysdatabases) t
WHERE LOWER(name) NOT IN ('tempdb',
                          'master',
                          'model',
                          'msdb')
  AND HAS_DBACCESS(name) = 1
  AND dbid NOT IN (32767)
  AND UserAccess != 'SINGLE_USER'
  AND Status = 'ONLINE'
  AND IsInStandBy = 0
ORDER BY name;

and

(@dbname nvarchar(256))
SELECT COUNT(*)
FROM master.dbo.sysdatabases
WHERE name=@dbname

and

SELECT 'Edition'=SUBSTRING(@@VERSION,PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2,PATINDEX(N'% on %',@@VERSION)-(PATINDEX(N'%Corporation%',@@VERSION)+DATALENGTH('Corporation')+2))

and

SELECT N'Job.Step',
        j.name + N'.' + js.step_name
FROM msdb.dbo.syssubsystems ss
JOIN msdb.dbo.sysjobsteps js ON ss.subsystem = js.subsystem
JOIN msdb.dbo.sysjobs j ON js.job_id = j.job_id
WHERE ss.description_id=14555
UNION
SELECT N'Proxy',
        p.name
FROM msdb.dbo.sysproxysubsystem ps
JOIN msdb.dbo.sysproxies p ON ps.proxy_id = p.proxy_id
JOIN msdb.dbo.syssubsystems ss ON ss.subsystem_id = ps.subsystem_id
WHERE ss.description_id=14555

Of course, when we launch them manually, they work.

And there's nothing suspicious when I launch a SQL trace on the server…

Best Answer

As far as I know there is issue with SQL Server 2014 upgrade advisor, it sometime hangs when analyzing the workload for migration. What you can do is download SQL Server 2016 upgrade advisor which is now called as Data Migration Assistant V 3.1. As per Microsoft the upgrade advisor 2016 is deprecated and is replaced by Data migration assistant.

I am not quite sure but I believe MS folks knew about this that is why they moved to much better tool data migration assistant.

This does not comes inbuild with SQL Server installation media it is a separate download.