Data Migration Assistant errors - dacpac and openquery failures

  • Hi all,

    I am attempting to use DMA to assess an upgrade from 2008 R2 to Asure SQL on VM.

    Our database has linked servers and many views that use openquery.

    I'm receiving a lot of errors running DMA.

    First is 'Failed to extract dacpac at location 'C''.

    Second is a load of errors like:

    'The column definition from this OPENQUERY function cannot be verified, and the operations involved with it might not be understood correctly.'

     

    I have tried another DB of ours without a linked server or openqueries and the assessment works fine.

    Is this a common issue and can anyone help me in resolving this to run the DMA?

     

    Thanks

     

     

  • You may need to set up a linked server on the Azure VM. Use synonyms to dummy out the behavior.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Hi Grant,

     

    We haven't set up a VM yet. Just running the assessment test.

     

    Regards

  • I wonder if it could be timing out - failing to extract dacpac.

    Worth changing the default 60 seconds perhaps

  • Ah, I missed this the first time. Sorry.

    The Data Migration Assistant is for moving to Azure SQL Database, not to Azure VMs. An Azure VM is just a VM. Moving to it is not really any different than moving between VMs locally. The DMA is for moving to a platform as a service offering, Azure SQL Database, which doesn't have the same operational footprint as a full blow instance of SQL Server. For example, there are no linked servers. Therefore, the DMA, which thinks you're moving to Azure SQL Database (not a VM), is letting you know that some of your objects, linked servers, won't migrate successfully.

    In short, this tool isn't doing what you need done. Here's documentation on moving from local instances to Azure Virtual Machines.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Thanks for the info Grant.

    Sorry maybe I am missing something butt it has options to test for SQL Server on Azure VM.

    Worked fine for one of our databases (small and no objects using a linked server)

    • This reply was modified 5 years, 2 months ago by  yonny24.
  • Interesting. I've only ever tested it going to Azure SQL Database. I'm not sure why you'd need it for a VM migration unless it's somehow testing the upgrade from some older version of SQL Server to whatever you're running in a VM (presumably 2017 or 2019). I'm still inclined to think the error is because of the Azure SQL DB checks.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Yes that was the intention to test the upgrade from 2008 R2 to 2017 possibly. I understod the DMA replaced the upgrade advisor.

  • I'm getting 2 error codes.

    Trying to add them to IgnoreerrorCodes in the DMA config file.

    After starting and running again the error codes that are in the config are stiill not being ignored.

    xml looks all fine.

    Scratching my head over this one. Still receive: Error SQL71005: Error validating element

    <workflowSettings>

    <!-- Configuration for the updater:

    - updateCheckTimeout: time (in milliseconds) to wait when checking for updates before cancelling check. (Default=3000)-->

    <updater updateCheckTimeout="3000" />

    <!-- Configuration for the assessment workflows:

    - parallelDatabases: number of databases that can be assessed in parallel

    - ignoreErrorCodes: Specifies the list of error code(shown in rule title) that the users want to ignore. The format is like "1" or "1;2;3" (Default="") -->

    <assessment parallelDatabases="8" ignoreErrorCodes="70558;71005;71501" />

    <!-- Configuration for the migration workflows:

    - parallelDatabases: number of databases that can be migrated in parallel (Range: 1 - 99)

    - fileExistenceAttempts: number of attempts to check for the existence of a file-->

    <migration parallelDatabases="8" />

    <!-- <migration fileExistenceAttempts="0" />-->

    </workflowSettings>

    • This reply was modified 5 years, 2 months ago by  yonny24.
    • This reply was modified 5 years, 2 months ago by  yonny24.
  • I understand this is quite relevant:

     

    https://techcommunity.microsoft.com/t5/Microsoft-Data-Migration/Release-Data-Migration-Assistant-DMA-v4-3/ba-p/583595

    Jim Toland

    Jim Toland

    Microsoft

    ?08-09-2019 03:50 PM

    @Sanjay-9_30, DMA v4.3 does support SQL Agent Jobs -

    Agent job assessments for Azure SQL Database managed instance as a target.

    Assessment of T-SQL queries in job activity steps and providing appropriate recommendations

    Support for Linked Servers is on our radar but currently in the backlog. Hope this helps!

    Jim Toland

     

  • Hi,

    I've tried to do an assessment for the migration of SQL2008R2 database to SQL2017 and run into the same errors. Have tried also to change the setting "ignoreErrorCodes" but this didn't help. Perhaps the only solution is to drop all the objects involved in the errors, run the DMA assessment and then recreate the objects.

  • Glad I'm not the only one encountering the same issues. I thought I was at fault.

    I have so many objects dependant on linked servers dropping them is not an option.

     

    Let me know if you find another solution.

     

    Thanks

  • The is not DMA issue. Actually it is dacFx issue, the framework that DMA uses. You can verify it by extracting dacpac from SSMS. I believe you will see the same error.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply