DMA DacPac files are not created

mslee 0 Reputation points
2023-02-01T00:19:32.9533333+00:00

Why dacpac files are not created?

I have 3 new SQL 2019 Enterprise instances on each Windows Server 2019 Standard
and     3 old  SQL 2016 Enterprise instances on each Windows Server 2012 Standard.

Runing DMA from a new machine with DB Engine Accessment mode from SQL 2016 to SQL 2019 was successfull.

But the other two have failed.

I found that dacpac files were not created in the two servers %LocalAppData%\DataMigrationAssistant\DacFxAnalyzer-xxxyyyzzz .

Both old and new servers are on premise.

Tried both Domain Account and SQL Server Account, sa when connecting from new to old server when running DMA. Both account has full access to old and new servers.

No firewalls are set on both old and new servers.

Opened the DMA with "Run as administrator"

Below is the part of DMA Log.

Dma.exe Information: 0 : Extracting Dacpac Started: ActivityId = '9f6ed75d-6147-45a0-9d7c-ce87b2cfcaa0', DatabaseId = '77d8fd5a-e3ca-4058-bb8b-05cf3845abf1', AdvisorType = 'DacFxExport'.
ScenarioCommonTraceSource Verbose: 204 : 9696 : 63 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Validating schema
ScenarioCommonTraceSource Verbose: 204 : 9696 : 63 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Extracting schema from database
ScenarioCommonTraceSource Verbose: 204 : 9696 : 63 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Resolving references in schema model
ScenarioCommonTraceSource Verbose: 204 : 9696 : 62 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Validating schema model for data package
ScenarioCommonTraceSource Verbose: 204 : 9696 : 14 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Pending, Message Extracting schema
ScenarioCommonTraceSource Verbose: 204 : 9696 : 15 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Running, Message Extracting schema
ScenarioCommonTraceSource Verbose: 203 : 9696 : 19 : 01/31/2023 15:47:00 : OnDacMessage(194) : Dac Message Message SQL73097: Extracting schema (Start)
ScenarioCommonTraceSource Verbose: 204 : 9696 : 68 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Running, Message Extracting schema from database
ScenarioCommonTraceSource Verbose: 204 : 9696 : 66 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Faulted, Message Extracting schema from database
ScenarioCommonTraceSource Verbose: 204 : 9696 : 3 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Faulted, Message Resolving references in schema model
ScenarioCommonTraceSource Verbose: 204 : 9696 : 3 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Faulted, Message Extracting schema
ScenarioCommonTraceSource Verbose: 203 : 9696 : 3 : 01/31/2023 15:47:00 : OnDacMessage(194) : Dac Message Message SQL73100: Extracting schema (Failed)
ScenarioCommonTraceSource Verbose: 204 : 9696 : 3 : 01/31/2023 15:47:00 : OnDacProgressChanged(200) : Operation id 1, Status Faulted, Message Validating schema model for data package
Dma.exe Information: 0 : Unhandled exception reported, ErrorId '11733302271495205336', ExceptionName 'Win32Exception', Error '0x80004005', StackFrame '.', WatsonData '0x80090325', OuterExceptionName 'DatabaseAssessmentFailedException', OuterErrorCode '0x86020001', OuterCallSite '.', OuterWatsonData 'NotSpecified'
Dma.exe Information: 0 : 'Exception type: Microsoft.SqlServer.Advisor.Common.Contracts.DatabaseAssessmentFailedException
    Message: 
        The assessment of database 'myDB' on server 'oldSasServerName(NOT FQDN)' failed.
    HResult : 0x86020001
        FacilityCode : 1538 (602)
        ErrorCode : 1 (0001)
    Data: 
        DatabaseName = myDB
        ServerName = oldSasServerName(NOT FQDN)
        DpesErrorReportedId = 11733302271495205336
    Inner exception type: Microsoft.SqlServer.Advisor.Common.Contracts.DacpacExtractionFailedException
        Message: 
            Failed to extract dacpac for databaseId '77d8fd5a-e3ca-4058-bb8b-05cf3845abf1' and Advisor 'DacFxExport' at location 'C:\Users\myUserName\AppData\Local\DataMigrationAssistant\DacFxAnalyzer-332f94801180448fbf-myDB\myDB.dacpac'.
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,667 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
492 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Pio Balistoy 0 Reputation points MVP
    2023-02-01T01:39:54.8333333+00:00

    From the error, it appears it wasn't able to extract the dacpac. There might be some syntax issues due to object names or incompatible commands between the source and destination due to dbcompat level. You can isolate by trying to take a dacpac and exporting manually so you can identify which.

    0 comments
  2. Seeya Xi-MSFT 16,436 Reputation points
    2023-02-01T02:52:45.5433333+00:00

    Hi mslee,

    Dma.exe Information: 0 : Unhandled exception reported, ErrorId '11733302271495205336', ExceptionName 'Win32Exception', Error '0x80004005', StackFrame '.', WatsonData '0x80090325', OuterExceptionName 'DatabaseAssessmentFailedException', OuterErrorCode '0x86020001', OuterCallSite '.', OuterWatsonData 'NotSpecified'

    Please refer to this MS document: Overview of Data Migration Assistant

    You have to be a member of the SQL Server sysadmin role.

    Best regards,

    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    0 comments