Transactional Replication with AX 2009

Recently I was asked some questions about Transactional Replication with Dynamics AX and SQL Server. For information the following is a list of the supported replication options in AX 2009:

(source: http://technet.microsoft.com/en-us/library/dd362068.aspx)

The links above take you to the MSDN site with additional information on the SQL replication options.

However, for this blog, I’m concentrating on transactional replication. In order for transactional replication to work you need to have a primary key on each table. By default, AX 2009 does not have a primary key set on each table; therefore you would need to create these before you can configure SQL Transactional replication. In order to make this simple, I’ve created the a class method to do this.

This class goes through all the tables in the AOT. It checks to see if there is a primary key, then if there is not one, it will set the table to have a RecId Index (if not already set). The final step then makes the RecId index the primary key index.

In order to use this, create a new class in the AOT and create a new method. Copy and paste in the code below and then run the class.

PLEASE NOTE: This process updates table definitions in the AOT and the physical table structures on the database server; so please make sure that you have backups of the application folder and database before running this process.

static void CreatePKforTablesWithoutPK(Args _args)
{
    SqlDictionary   sqlDict;
    SysDictTable    dictTable;
    TreeNode        node, childNode;
    str             properties;
    #AOT
    ;


    while select tabId from sqlDict group by sqlDict.tabId where sqlDict.fieldId==0
    {
        dictTable = new SysDictTable(sqlDict.tabid);
        if(dictTable)
        {
            //Check to see if the table as a primary key index
            if(dictTable.primaryIndex()==0)
            {
                //No Primary Index
                node = infolog.findNode(#TablesPath);
                childNode = node.AOTfindChild(dictTable.name());
                if(childNode)
                {
                    //Set the table to have a RecId index, if it already has property stays as yes.
                    properties = setProperty(childNode.AOTgetProperties(), "CreateRecIdIndex", "Yes");
                    childNode.AOTsetProperties(properties);
                    childNode.AOTsave();
                    childNode.AOTrefresh();
                    //Set the primary index to be the RecId index.
                    properties = setProperty(childNode.AOTgetProperties(), "PrimaryIndex", "RecId");
                    childNode.AOTsetProperties(properties);
                    childNode.AOTsave();
                    childNode.AOTrefresh();
                    Info(dictTable.name() + ' ("' + dictTable.label() + '") has RecId Index added and is set as the PrimaryIndex');
                }
            }
        }
    }
}

Note: The code is provided as is with no warranties, and confers no rights, and is not supported by Microsoft Support.