Upgrading ISV solution and SQLDictionary

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.

 

 

 

 

 

 

Advertisements

One thought on “Upgrading ISV solution and SQLDictionary

  1. If an ID is changed and the name is the same in a new layer, AOD contains the new ID and SQLDictionary contains the old ID. First synchronization updates SQLDictionary, deletes the old field and creates a new field with new ID.
    Before synchronization is run, you can simply read the new value from AOD (via tableId(), DictField or so) and update SQLDictionary to avoid field dropping (obviously, changed IDs can be found automatically by iterating through the dictionary and comparing IDs in AOD and DB for the same table/field name).
    AX provides useful methods for ID updating in the ReleaseUpdateDB class (e.g. changeFieldId()).

    BTW make sure you don’t need to update any references to old IDs in business data (e.g. documents linked to records via TableId/RecId).
    When I was dealing with this issue, I’ve written a tool detecting such references:
    http://www.axaptapedia.com/DataReferenceSearcher.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s