It is time for a more technical Dynamics AX posting here. I want to describe what was needed to be done to upgrade a very complex solution, that consist of several ISV solutions and also have local customizations on top. Overall, the task can be visualized like this.

    

The upgrading was related to new Rollup package from Microsoft, new releases of the ISV solutions; RCM from Microsoft, WM&D from To-Increase and Inventory II from FSB development. My biggest concerns was:

1. The ISV solutions needed to be merged into common layers. This results in different TableID and FieldID.
2. Several fields and tables had been renamed in the ISV solutions.
3. The risk of loosing data in the process is high, unless I get the mapping right.
4. The database is approx. 1 Tb large, so table changes etc is very heavy operations.

There is a central table in Dynamics AX 2009, called SQL dictionary, that translates AOT ID’s to actual SQL names.

And you can see this in the application like this:

When going from old solution to new solution I needed to make some changes in the SQL dictionary to make sure that I don’t have any dataloss. Since Dynamics AX 2009 are quite good to handle changed table names and field names, I could concentrate on making the new mapping correctly in the SQLDictionary.

Here is what I did to make this happen. I installed all NEW layers and customizations on a separate installation, making sure that there where no compile errors etc.

The next was to create a conversion tool to manipulate SQL dictionary, that handles both changed ID’s and changed Names. I therefore created a table to handle the SQLdictionary from old format to new format. I created a small table, that looks like the SQLDictionary, and then created New and Old tableID, FieldID, name and SQL name.

The first process was to read this out of the OLD database, with the following script:

So now I have a table that is a “copy” of the Old SQL dictionary. My next step is to correct up all ID’s. To do this, is a painstaking process, because it has to be done manually. So I created 2 methods for manipulating the SPA_SQLDictionary. The UpdateTableId() and UpdateFieldID()

Then I called these two methods to set the new ID’s:

In my case, there where more than 500 tabels that needed to be handled this way. Manually defining from ID. Luckily I could use the Tablename2Id and fieldname2id methods to give me the new ID’s.

The last step in the prosess was to copy the new ID’s into the SQLDictionary:

After this has been done, the system is ready to by synchronized, and all tables and fields have the correct mapping.

With this approach I did not have any dataloss, I did not have to export/import a 1 Tb database J. I also want to point out, that AX 2009 is the last version where we will have this problem. (I hope). In AX 2012 we will have installation specific ID’s, and you can read more about that here.