Data Migration Assistant: Known Issues (v1.0)


Execution Timeout Expired

While running assessment you might encounter an execution timeout with an error message similar to the one below:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.
The wait operation timed out

Mitigation: Perform the following actions:

  1. Set 'commandTimeout' to a higher value in the configuration file.
    • Open %programfiles%\Microsoft Data Migration Assistant\SqlAdvisor.exe.config.
    • Uncomment this line <!-- <dacFx commandTimeout="60" databaseLockTimeout="5000" maxDataReaderDegreeOfParallelism="8"/> -->
    • Set commandTimeout to a higher value.
    • This sets the IDbCommand.CommandTimeout property in seconds.
    • Restart Data Migration Assistant.
  2. Follow Data Migration Assistant: Best Practices

Lock request time out period exceeded

While running assessment you might encounter lock request timeout with an error message similar to the one below:

Could not extract package from specified database.
An error occurred while attempting to reverse engineer elements of type...
Batch Command during reverse engineer failed with Error Code: -2146232060 Line Number: 1 Error Message: Lock request time out period exceeded.
Unable to reconnect to database: Lock request time out period exceeded.

Mitigation: Perform the following actions:

  1. Set 'databaseLockTimeout' to a higher value in the configuration file.
    • Open %programfiles%\Microsoft Data Migration Assistant\SqlAdvisor.exe.config.
    • Uncomment this line <!-- <dacFx commandTimeout="60" databaseLockTimeout="5000" maxDataReaderDegreeOfParallelism="8"/> -->
    • Set databaseLockTimeout to a higher value.
    • This is equivalent to SET LOCK_TIMEOUT timeout_period in milliseconds.
    • Restart Data Migration Assistant.
  2. Follow Data Migration Assistant: Best Practices

Error validating element

While running assessment you might encounter error in extracting dacpac with an error message similar to the one below:

Could not extract package from specified database. Error SQL70589: Error validating element [dbo].[<SomeObject>]: NO_LOG and TRUNCATE_ONLY options were removed in SQL Server 2008.

Mitigation: Perform the following actions:

  1. Modify the database object specified in the error message, remove discontinued feature such as NO_LOG, and re-run assessment.
  2. For more information, refer to following links:
Comments (22)

  1. This seems like an obvious requirement in hindsight but if you don’t have sysadmin then you’ll get a connection failure. It took me a few minutes to figure out because there was no obvious error other than “connection failed”

    1. Hi Kenneth,

      With DMA 2.0 we have fixed this.
      For assessment the credentials used to connect to source SQL Server instance must have CONNECT SQL, VIEW SERVER STATE and VIEW ANY DEFINIION permissions.
      For migration
      a) Credentials used to connect to source SQL Server instance must have CONTROL SERVER permission
      b) Credentials used to connect to target SQL Server instance must be a member of the sysadmin server role.

      All the required permissions are now listed in the tool itself, when you try to connect to a server.
      You can download latest version of DMA from here

  2. Ravi Joshi says:

    Hi,
    Are there any performance benchmarks for this tool. Specifically, how much time would it take to analyze and migrate a DB having 6-8 TB size

    1. Hello,
      The assessment duration is not so much dependent on the size of the database, but more with the total number of objects that the database consists of. In our benchmark testing, a database with 150,000 objects took around 60 minutes to finish the assessment.
      Please feel free to reach us at dmafeedback@microsoft.com with more specifics so that we can help you in estimating the assessment durations in your environment.
      Thanks
      Raj

  3. I am getting the following error on one Database and a server… The other 9 databases evaluation okay…

    The error from the CSV output file is:

    “We have experienced one or more failures: Object reference not set to an instance of an object., Object reference not set to an instance of an object.”

    On Screen error ”
    Error Details
    The assessment of database ‘{databasename}’ on server ‘{ServerName}’ failed.
    Object reference not set to an instance of an object.

  4. Brett G says:

    I think the tool works okay for checking a small number of databases. But if I select a larger number of databases, they all have timeouts, as if the tool is trying to connect to all of them simultaneously. 🙁

    I’m aware of the best-practice advice, “Try to limit assessments to 10 databases or less per SQL Server instance”, but that does not seem practical for someone who manages a large number of databases. Surely there must be some way to automate checking a larger number of databases over several hours.

    I am seeing this error: “Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=15145; handshake=0;”

    I am looking for a configuration setting to limit the number of simultaneous connections. I tried to enable maxDataReaderDegreeOfParallelism=”4″ and it didn’t help, but the error messages changed somewhat.

    Now there are mostly errors like this: “Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was – [Pre-Login] initialization=2; handshake=15004; ”

    And a few of these: “A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 – The wait operation timed out.)”

    No database was analyzed successfully when I selected all. But when I selected 4 databases at random, the process completed fine within a minute or two.

    By the way I didn’t enable encryption, so I’m not sure why I’m seeing “SSL Provider” in some of the errors.

    Thanks!

    1. Hello,
      On your question regarding, “SSL Provider” in the connection string exception, can you please check if “Force Encryption” is set to true, on your SQL Server instance?
      You can check this in SQL server configuration manager, select the protocols for that specific instance, right click and properties.
      You will see this property in Flags tab.
      Did you also try connecting using SQL authentication instead?
      Thanks
      Raj

      1. Brett G says:

        “Force Encryption” is False. I tried both kinds of authentication. Thanks!

    2. Hi Brett,

      With DMA v2.0 we have exposed flag that controls the maximum number of databases that DMA assesses in parallel. Default value is 8, however it could be changed.

      <advisorGroup>
      <workflowSettings>
      <assessment parallelDatabases=”8″ />
      </workflowSettings>
      </advisorGroup>

      Refer to Configuration settings for details.

      I hope this will let you assess large number of databases over several hours.

  5. Sailesh Mehta says:

    I have the data migration assistant running now for 6 days on a SQL Server 2008R2 DB actual size 2.3 TB , but the assistant is showing the DB size to be 4.76 TB
    How long should it take to complete the assessment ?

    1. Hi Sailesh,

      Please install Data Migration Assistant v2.0 which has fixed the issue that showed the wrong size in v1.0.
      Assessment of a database depends on the size of the database schema such as umber and complexity of objects, and not on the size of data. It extracts a Data Application Tier dacpac file form the database where it spends bulk of its time.

      Regards,
      Akash

  6. Dan Lampkin says:

    can we no longer run the advisor on trace files?

    1. At this point, DMA doesn’t support assessment against the SQL server trace files, enabling this feature is in our backlog, I do not have any ETA at this point.
      Thanks
      Raj

  7. Suresh Kanchi says:

    Hi Team,
    This is great tool for migrating SQL Server from lower version to latest one. I have some queries and looking for your expertise.

    1. Are you taken care of TDE enabled databases also, while taking backup & restoring in the upgrade process.
    2. This tool compares compatibility of all objects in SQL Server but If I have used some LINQ queries in .Net application then that also actually needs to be verified then only my application shall work as it is. If you have any solution or work around for this then it would be Nice!
    3. Actually we are looking a tool to certify our product while upgrading from lower version to latest version of SQL Server. What is your recommendation for that. Is this tool fulfill to certify application?

    1. hello Suresh,
      #1 In order to migrate the TDE enabled databases, first you need to create the certificate in the destination instance from the backup of the certificate created at the source instance. DMA will not perform this step, you need to perform manually prior to the migration using DMA tool.
      The article, https://msdn.microsoft.com/en-us/library/ff773063.aspx provides the steps to move TDE enabled databases.
      #2 DMA currently has no ability to access the adhoc queries directly executed from applications. You can use the “Database Experimentation Assistant (DEA) tool https://blogs.msdn.microsoft.com/datamigration/2016/10/24/database-experimentation-assistant-v1-0-preview/ to replay the SQL trace collected at the source SQL Server instance and look for exceptions retuned at the target SQL Server instance.
      #3 DMA is officially supported by Microsoft, and can be part of your certification compliance.
      Thanks
      Raj

  8. Is there a better place than these comments to report bugs?

    Here is one – “Syntax issue on the source server” false alarm (or better Recommendation message to better understand why this is bad).

    Steps to reproduce:

    USE master;
    GO

    CREATE DATABASE Test;
    GO

    USE Test;
    GO

    ALTER PROC dbo.A
    AS
    BEGIN
    select ‘test’ AS A
    END
    GO

    ALTER PROC dbo.B
    AS
    BEGIN
    CREATE TABLE #tmp (testData char(5) null)

    INSERT #tmp
    exec dbo.A WITH RECOMPILE;

    SELECT * FROM #tmp
    END
    GO

    — This confirms it actually works
    exec dbo.B
    GO

    1. To clarify, the bug is that you can use WITH RECOMPILE or INSERT exec, but cannot combine the two at the same time in one statement, or the DMA tool will raise it as a false alarm.

      USE master;
      GO

      CREATE DATABASE Test;
      GO

      USE Test;
      GO

      CREATE PROC dbo.A
      AS
      BEGIN
      select ‘test’ AS A
      END
      GO

      CREATE PROC dbo.B
      AS
      BEGIN
      CREATE TABLE #tmp (testData char(5) null)

      INSERT #tmp
      exec dbo.A WITH RECOMPILE;

      SELECT * FROM #tmp
      END
      GO

      CREATE PROC dbo.C
      AS
      BEGIN
      CREATE TABLE #tmp (testData char(5) null)

      INSERT #tmp
      exec dbo.A;

      SELECT * FROM #tmp
      END
      GO

      — This confirms it actually works
      exec dbo.B
      GO

      exec dbo.C
      GO

      1. Harini Gupta says:

        Please email me (harinid@microsoft.com) the impacted objects that you think should not be detected by DMA.

        1. Sent. Please confirm you got it. Thank you!

    2. Harini Gupta says:

      Hi, can you send me (harinid@microsoft.com) with the false positives and if possible, send me the impacted object as well.

  9. leifp says:

    Greetings,
    I’m at a client assessing several 2008r2 instances for 2016 upgrade. One instance has the same error for several databases in the following format (I have unwrapped the text)….

    Generic SQL Exception.
    Node=DBName
    UniqueName=Database_SQLInstance_ServerName_DBName
    SqlError=-2
    Type=Microsoft.Sirona.Collection.Collection Exception

    …and then a long server stack trace.

    Any suggestions?
    TIA for answering these questions,
    Leif

  10. CPRow says:

    DMA does not seem to be reporting all occurrences of an issue. For example, the compatibility error “SET ROWCOUNT used in the context of DML statements such as INSERT, UPDATE, or DELETE” is reported only three times, but I can see many other stored procedures in the same database have this issue.

Skip to main content