August 20, 2019 at 2:57 pm
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
August 21, 2019 at 12:30 pm
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
August 22, 2019 at 12:55 pm
Hi Grant,
We haven't set up a VM yet. Just running the assessment test.
Regards
August 22, 2019 at 1:56 pm
I wonder if it could be timing out - failing to extract dacpac.
Worth changing the default 60 seconds perhaps
August 22, 2019 at 1:58 pm
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
August 22, 2019 at 6:09 pm
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
August 22, 2019 at 6:11 pm
Yes that was the intention to test the upgrade from 2008 R2 to 2017 possibly. I understod the DMA replaced the upgrade advisor.
August 23, 2019 at 2:47 pm
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>
August 23, 2019 at 5:57 pm
I understand this is quite relevant:
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
September 2, 2019 at 9:01 am
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.
September 2, 2019 at 11:27 am
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
July 7, 2020 at 10:16 pm
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