Upgrade from AX 2012 to D365 Finance and Operations. Data upgrade in self-service environments.

Overview

When a successful upgrade test has been completed in a Tier-1 environment with customer data and developed data upgrade scripts you can start the data migration process in a Tier-2 machine. 

It has almost been 2 years since the old process via "backpack" files is no longer available. The only way to upgrade data is by using "Data Migration Toolkit for Dynamics 365" which uses SQL replication process. 

Note: An old name of the "Data Migration Toolkit for Dynamics 365" is "AX2012 Database Upgrade Toolkit for Dynamics365 Version".

The data upgrade process is described in Microsoft standard documentation. There is also a tech-talk session about the migration process. I would like to share my personal experience of the data upgrade in self-service Tier-2 environments below.

Key points before you start

• You should have free disc space in Ax 2012 database SQL server for distribution and snapshot folders. The disc space should be about 2 times more than Ax 2012 business database size.

• "Data Migration Toolkit for Dynamics 365" uses native SQL logins only. I would recommend to create a new SQL server login for this purpose.

• A new SQL login should have DB_Owner privilege in the source Ax 2012 database and access to the master database in the source SQL Server instance.

• Make sure that the replication feature is installed and enabled in the source Ax 2012 SQL Server instance. If the replication components aren't installed, follow the steps in Install SQL Server replication to install them.

• Don’t forget to enable and start SQL Server Agent on the source Ax 2012 database server.

• Download and install .NET Framework version 4.7.1 if it isn't already installed.

• You have to know the external IP address of the SQL Server machine. Use can use this website to help you. You should use IPv4 address field value.

• It is required to enable support for TLS 1.2 in your Ax 2012 SQL Server machine for Azure AD. To find some information on how to do it use this link. 

If you don’t enable TLS 1.2 support in advance, LCS authentication window might not work properly or not appear at all and then you see errors in the logs:

2023-06-25 06:47:27.202 -04:00 [Information] User Login started.

2023-06-25 06:49:38.538 -04:00 [Information] User Login failed.

2023-06-25 06:49:38.541 -04:00 [Error] AADSTS1002016: You are using TLS version 1.0, 1.1 and/or 3DES cipher which are deprecated to improve the security posture of Azure AD. 

Your TenantID is: g9370196-3d9a-9d85-a5e9-3604ec7ffbdd. 

Please refer to https://go.microsoft.com/fwlink/?linkid=2161187 and conduct needed actions to remediate the issue. For further questions, please contact your administrator.

Trace ID: g02f1232-caf4-479c-8c0e-0442059e5e01

Correlation ID: 52f25db8-e61a-4023-9e2a-c487f87e6c5d

Timestamp: 2023-06-25 10:49:37Z 2023-06-25 06:49:38.545 -04:00 

[Error] User login failed / not authorized.

• You need to make sure that TLS 1.2 is enabled by default and that the previous versions are disabled:

Tips to improve the process performance

• Since the "Data Migration Toolkit for Dynamics 365" is based on the SQL Server replication feature it moves data as it is from the source database. If you are short on time for data migration and would like to reduce the time span it is a good idea to perform cleanup operations in the source database.

• It is recommended that you start the replication during off hours when the system resources are at minimum usage (during off-peak time). 

• Also, you can use this article to improve replication performance on the Ax 2012 SQL server.

Customization Analysis Report (CAR) in D365 Finance and Operations

The Customization Analysis Report (CAR) is a tool that analyzes your customization and extension models and runs a predefined set of best practice rules. The report is one of the requirements of the solution certification process. 

Let’s imagine we have a model. It’s called the MYDEV model.

Normally we would use the following command to generate the Customization Analysis Report (CAR) in a development environment:

K:\AOSService\PackagesLocalDirectory\Bin\xppbp -metadata=K:\AosService\PackagesLocalDirectory -all -model:MYDEV -module:MYDEV 

-car=K:\temp\CAReport.xlsx

If you would like to verify the particular object you can include additional parameters such as Class, Form, or another object type, for example:

K:\AOSService\PackagesLocalDirectory\Bin\xppbp -metadata=K:\AosService\PackagesLocalDirectory -model:MYDEV -module:MYDEV 

-car=K:\temp\CAReport.xlsx Class:WHSDocumentRoutingFormMY_Extension

If your MYDEV model references a binary model (it can be a case when you use third-party or ISV models), then it is required to include the -PackagesRoot parameter, for example:

K:\AOSService\PackagesLocalDirectory\Bin\xppbp 

-packagesroot=K:\AosService\PackagesLocalDirectory 

-metadata=K:\AosService\PackagesLocalDirectory 

-all -model:MYDEV -module:MYDEV -car=K:\temp\CAReport.xlsx

Database upgrade scripts for D365 Finance and Operations. Development of the data migration script via temporary table.

During the data migration from Ax 2012 to D365, the table might be renamed and there might be system setups e.g. electronic signatures for the table fields. (For example: In Ax 2012 the table is called “AATable”, in D365 the same table is called “BBTable”)

In this scenario, it would be good to store the table name and id, field names, and ids of the Ax 2012 table (“AATable”) in order to apply setups to the "BBTable" in D365 during the data migration. Using a temporary SQL table is a good choice to do so. 

You can find a code that can be used as an example in order to give you an idea, below. You can copy the queries in SQL Server Management Studio to see how it works.

First of all, it is needed to create a temporary SQL table via the SQL command at the "PreSync" stage:

private str getSQLCreateTmpSQLTable()

{

    str     tmpSQLTable = “TmpSQLTable”;

    str     sqlStatement;

 

    if (! this.isTablePresent(tmpSQLTable))

    {

        sqlStatement = strFmt(@"

                CREATE TABLE [dbo].%1(

                    [KEYFIELD] [nvarchar](10) NOT NULL,

                    [FIELD1] [nvarchar](60) NOT NULL,

                    [FIELD2] [int] NOT NULL,

                    [DATAAREAID] [nvarchar](4) NOT NULL,

                    [PARTITION] [bigint] NOT NULL,

                    CONSTRAINT [I_TMPSQLTABLEDATAAREA] PRIMARY KEY CLUSTERED

                    (

                        [DATAAREAID] ASC,

                        [PARTITION] ASC,

                        [KEYFIELD] ASC

                    )

                    WITH (PAD_INDEX = OFF,

                          STATISTICS_NORECOMPUTE = OFF,

                          IGNORE_DUP_KEY = OFF,

                          ALLOW_ROW_LOCKS = ON,

                          ALLOW_PAGE_LOCKS = ON)

                    ON [PRIMARY])

                ON [PRIMARY]",

                tmpSQLTable);

    }

 

    return sqlStatement;

}

/// <summary>

/// Defines, whether the table exists in the database.

/// </summary>

/// <param name = "_tableName">

/// The name of the table.

/// </param>

/// <returns>

/// True if the table exists, otherwise false.

/// </returns>

public boolean isTablePresent(TableName _tableName)

{

   str sqlQuery = strFmt(@"IF OBJECT_ID ('[dbo].[%1]', 'U') IS NULL

                         SELECT 0

                            ELSE

                         SELECT 1",

                         _tableName);

 

   str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);

 

   return str2Int(result);

}

NOTE: Enum fields are presented as number fields in Ax2012/D365. If you need to save enum field values you need to create an integer field.

Then populate the table with the data at the "PreSync" stage:

private str getSQLPopulateTmpSQLTable()

{

   TableName    tmpSQLTable = “TmpSQLTable”;

 

   str sqlStatement = strFmt(@"

            INSERT INTO [dbo].%1

                   (KEYFIELD,

                    FIELD1,

                    FIELD2,

                    DATAAREAID,

                    PARTITION)

            SELECT  [dbo].[INVENTITEMSAMPLING].[INVENTITEMSAMPLINGID],

                    [dbo].[INVENTITEMSAMPLING].[DESCRIPTION],

                    [dbo].[INVENTITEMSAMPLING].[TESTQTYSPECIFICATION],

                    [dbo].[INVENTITEMSAMPLING].[DATAAREAID],

                    [dbo].[INVENTITEMSAMPLING].[PARTITION]

            FROM [dbo].[INVENTITEMSAMPLING]

                    where [dbo].[INVENTITEMSAMPLING].[COMPLETEBLOCKING] = 1",

                            tmpSQLTable);

 

    return sqlStatement;

}

Now we can use the methods in the data upgrade method:

/// <summary>

/// Saving data to the temporary table.

/// </summary>

[UpgradeScriptDescription("@SYS113629"),

 UpgradeScriptStage(ReleaseUpdateScriptStage::PreSync),

 UpgradeScriptType(ReleaseUpdateScriptType::SharedScript),

 UpgradeScriptTable(tableStr(InventItemSampling), false, true, false, false)]

public void preSyncAQMInventItemSampling()

{

    str     sqlStatement;

       

    // Create the temporary upgrade table.

    sqlStatement = this.getSQLCreateTmpSQLTable();

    if (sqlStatement)

    {

        ReleaseUpdateDB::statementExeUpdate(sqlStatement);

    }

   

    // Populate the temporary table.

    sqlStatement = this. getSQLPopulateTmpSQLTable();

    if (sqlStatement)

    {

        ReleaseUpdateDB::statementExeUpdate(this.getSQLPopulateTmpSQLTable());

    }

}

The final step is to use the temporary table in order to manipulate data at the “PostSync” stage:

/// <summary>

/// Update the <c>InventItemSampling</c> table with the saved values.

/// </summary>

[UpgradeScriptDescription("@SYS113629"),

 UpgradeScriptStage(ReleaseUpdateScriptStage::PostSync),

 UpgradeScriptType(ReleaseUpdateScriptType::SharedScript),

 UpgradeScriptTable(tableStr(InventItemSampling), false, true, true, false)]

public void postSyncInventItemSampling()

{   

   str         sqlStatement;

   TableName   tmpSQLTable = “TmpSQLTable”;

       

   if (this.isTablePresent(tmpSQLTable))

   {

      sqlStatement = strFmt(@'UPDATE [dbo].[INVENTITEMSAMPLING]

                            SET [dbo].[INVENTITEMSAMPLING].[DESCRIPTION] = ''

                               from [dbo].[INVENTITEMSAMPLING] as iis

                            join [dbo].[%1] as upgTmp

                               ON  iis.INVENTITEMSAMPLINGID = upgTmp.KEYFIELD

                               and iis.DATAAREAID          = upgTmp.DATAAREAID

                               and iis.PARTITION           = upgTmp.PARTITION',

                             tmpSQLTable);

 

      ReleaseUpdateDB::statementExeUpdate(sqlStatement);

 

      sqlStatement = strFmt(@"DROP TABLE %1", tmpSQLTable);

 

      ReleaseUpdateDB::statementExeUpdate(sqlStatement);

   }   

}

I’ll be glad if you find this helpful.

Data migration to D365 Finance and Operation. Alignment of record identifiers(RecId) after the data migration.

During the data migration from Ax 2012 to D365, the table might be renamed and there might be record relations by RecId to another table. (For example: In Ax 2012 the table is called “AATable”, in D365 the same table is called “BBTable”)

In this scenario, it is necessary to develop a script in order to move Ax 2012 data as it is (including the "RecId" numbers) in order to save the data consistency. Otherwise, the migrated data can be inconsistent since record id references can point to not existing records. The script should be executed on the “PreSync” stage when both tables (“AATable” and “BBTable”) exist in the database.

When the data upgrade process is completed and you create a new record in the "BBTable" you can see the error: "Cannot create a record in "BBTable." The record already exists." But you see that there are no violations from a primary key perspective. Most probably there is a RecId sequence issue.

In order to prevent this error, it is needed to align record id numbers (RecId) after executing the data upgrade script. I would recommend performing this operation on the "PostSync" stage or via job when data migration is finished.

First of all, it is good to know the “RecId” number sequence name. In D365 Microsoft has switched from using a dedicated database table to using the standard SQL sequences functionality. So, we need to identify the SQL “RecId” sequence for the table.  The following method can be used:

/// <summary>

/// Defines the SQL sequence name of the given table.

/// </summary>

/// <param name = "_tableName">

/// The name of the table.

/// </param>

/// <returns>

/// Returns the SQL sequence name of the given table.

/// </returns>

public str getSequenceProcedureName(TableName   _tableName)

{

    str         result;

    str         ret;

    TableId     tableId;

 

    if (this.isTablePresent(_tableName))

    {

       str sqlQuery = strFmt(@"

                SELECT ID from TABLEIDTABLE

                JOIN sys.sequences 

                ON sys.sequences.name = CONCAT('SEQ_', TABLEIDTABLE.ID)

                    WHERE TABLEIDTABLE.NAME = '%1'",

                            _tableName);

 

       result  =  ReleaseUpdateDB::statementExeQuery(sqlQuery);

       if (result)

       {

           tableId = str2Int(result);

 

           if (tableId)

           {

               ret = strFmt('SEQ_%1',tableId);

           }

       }

    }

 

    return ret;

}

/// <summary>

/// Defines, whether the table exists in the database.

/// </summary>

/// <param name = "_tableName">

/// The name of the table.

/// </param>

/// <returns>

/// True if the table exists, otherwise false.

/// </returns>

public boolean isTablePresent(TableName _tableName)

{

   str sqlQuery = strFmt(@"

            IF OBJECT_ID ('[dbo].[%1]', 'U') IS NULL

                SELECT 0

            ELSE

                SELECT 1",

            _tableName);

   str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);

 

   return str2Int(result);

}

When we know the sequence name, we can align the RecId number sequence with the method:

/// <summary>

/// Updates the appropriate RecId number sequence for the given table.

/// </summary>

/// <param name = "_tableName">

/// The name of the table.

/// </param>

/// <returns>

/// True if the sequence updated, otherwise false.

/// </returns>

public boolean alignNumberSequence(TableName    _tableName)

{

    boolean     ret;

    str         triggerName;

    RecId       curMaxRecId;

    RecId       newMaxRecId;

 

    if (_tableName)

    {

        triggerName = this.getSequenceProcedureName(_tableName);

        curMaxRecId = this.getMaxRecId(_tableName);

 

        if (triggerName && curMaxRecId)

        {

           newMaxRecId = curMaxRecId + 1;

           str sqlQuery = strFmt(@"ALTER SEQUENCE [dbo].[%1] 

                                    MINVALUE %2 RESTART WITH %2",

                                 triggerName,

                                 newMaxRecId);

 

           ReleaseUpdateDB::statementExeUpdate(sqlQuery);

              

           sqlQuery = strFmt(@"SELECT minimum_value 

                                from sys.sequences

                                    WHERE sys.sequences.name = '%1'",

                             triggerName);

 

           str result = ReleaseUpdateDB::statementExeQuery(sqlQuery);

           if (result)

           {

               curMaxRecId = str2Int64(result);

               if (curMaxRecId == newMaxRecId)

               {

                   ret = true;

               }

           }

        }

    }

 

    return ret;

}

I’ll be glad if you find this helpful.