migration Archives - DAXRunBase

Active record count in ValidTimeState tables

In the previous blog post we have explored how to tell how many records do we have per company account using a T-SQL Query. But AX does have a concept which SQL cannot cope with, and makes it a bit harder to tell the active record count in ValidTimeState tables.

SQL record count by company

The following job can pull back this value for us, in order to validate if data migration row counts are matching between AX 2012 and D365FO.

static void WIK_findValidTimeStateKey_Tables(Args _args)
{
    DictTable           dictTable;
    Dictionary          dict = new Dictionary();
    TableId             tableId;
    Common              common;
    date                currentDate = systemDateGet();
 
    setPrefix('Record count for ValidTimeStateKey tables');
    tableId = dict.tableNext(0);
 
    while (tableId)
    {
        dictTable = new DictTable(tableId);
        if (!dictTable.isTmp() && !dictTable.isTempDb() && !dictTable.isView()
            && (dictTable.configurationKeyId() ? isConfigurationKeyEnabled(dictTable.configurationKeyId()) : true)
            && dictTable.isValidTimeStateTable())
        {
            common = dictTable.makeRecord();
            select validTimeState(currentDate) count(RecId) from common;
 
            if (common.RecId)
            {
                info(strFmt('%1\t%2', dictTable.name(), common.RecId));
            }
        }
 
        tableId = dict.tableNext(tableId);
    }
}

By DAXRunBase|2022-03-31T19:42:32+02:00March 31st, 2022|Categories: AX 2012, Dynamics 365 for Finance and Operations, MSDyn365FO|Tags: #MSDyn365FO, AX 2012, migration, SQL, tables, X++|0 Comments

Identify tables to migrate

When we have decided to use a Data migration instead of a Data upgrade to D365FO we had to come up with a way to identify tables to migrate.

The AX developer client already has a form under Tools > Number of records, which can tell you how many rows does a table have. This form can be easily expanded to include additional information. The most useful are the table type, and the application layer.

You would typically want to migrate all tables which belong to the following Table groups:

Parameters
Group
Main

This is how you can add the table type display method and control to the SysTableRowCount form to identify tables to migrate:

//BP deviation documented
display TableGroup getTableGroup(UtilIdElements _utilIdElements)
{
    SysDictTable sysDictTable = SysDictTable::newName(_utilIdElements.name);

    if (sysDictTable)
    {
        return sysDictTable.tableGroup();
    }

    throw error(strfmt("@SYS19384", classstr(SysDictTable)));
}

By DAXRunBase|2022-03-31T19:06:37+02:00March 31st, 2022|Categories: AX 2012, Dynamics 365 for Finance and Operations, MSDyn365FO|Tags: AOT, AX 2012, Dynamics 365, metadata, migration, tables, X++|0 Comments

SQL data compression for Dynamics AX 2012 (Part 2)

It has been a while since I have touched up on SQL data compression in part 1 on my blog. It is time to re-visit the topic.

SQL data compression for Dynamics AX 2012 (Part 1)

Since that post Microsoft has published an excellent series of articles on how to apply compression within AX, or directly on SQL Server. It is a must-read blog post for everyone, so start there:

https://blogs.msdn.microsoft.com/axinthefield/sql-server-data-compression-for-dynamics-ax-0-blog-series/

Since JJ Food Service has a very large database, we have started applying compression to Production AX. Our SalesLine and CustInvoiceTrans has been between 250-350 GB size each! Our approach was to apply compression before a maintenance window using the Enterprise Edition license of SQL Server in ONLINE mode and using TempDB sorting, so it was running during business hours only adding a slight overhead to the system (mostly as disk I/O). It took 4 and 6 hours respectively, and space saving was more than 75% which is excellent.

Once the compression has finished and we were about to begin our regular Friday night maintenance, once the AX AOS, reporting and web services were all down, we could run the post-compression SQL script provided by Microsoft to populate the SQLStorage table. This is required for the Data dictionary synchronization step to recognize compressed indexes, so it would not drop and recreate them uncompressed.

Eventually we will consider compressing our whole database for Page level. The reason being is that Dynamics 365 for Finance and Operations, Enterprise edition in the background uses full page compression for the whole AX database. If you would like to use the upgrade scripts from 2012, even the documentation mentions that compression is a pre-requisite step:

https://docs.microsoft.com/en-us/dynamics365/unified-operations/dev-itpro/migration-upgrade/prepare-data-upgrade

As a result we have seen great performance boost for the tables involved and much lower numbers in the slow performing SQL statements, since more data could be kept in the SQL memory buffer.

By DAXRunBase|2018-02-27T09:45:41+01:00February 27th, 2018|Categories: AX 2012, Dynamics 365 for Finance and Operations|Tags: AX 7, compression, Dynamics 365, migration, performance, SQL|0 Comments

Table Delete actions in code compare window

I am sure all fellow developers have faced the frustrating problem of not being able to insert certain objects when importing an XPO or comparing different models, layers. Today I will show you how can you insert the Table Delete actions in code compare window for AX 2012 R3.

Modify \Classes\SysTreeNode\mergeInsertSubnode method in the AOT as per below:

(more…)

By DAXRunBase|2016-03-30T15:24:06+02:00March 30th, 2016|Categories: AX 2012|Tags: AOT, AX 2012, compare, delete actions, migration, X++, XPO|3 Comments

Find missing objects behind menu items

As part of our code cleanup exercise we have discovered that there were menu items pointing to objects which no longer exists in our environments, ie. legacy reports which were upgraded to SSRS but their menu items were never removed.

The following job could be used to identify such missing objects for any of your menu items. I would also recommend to double-check the xRef references to find connected objects, like security privileges/roles, to completely remove whatever you do not need.

(more…)

By DAXRunBase|2015-11-23T17:42:08+01:00October 27th, 2015|Categories: AX 2012|Tags: AOT, AX 2012, menu items, migration, X++, xRef|0 Comments