Thursday, November 14, 2013

Dynamics AX 2012 FAST synchronization

Database synchronization on AX 2012 is usually pretty slow.

This is due to the Ax database becoming bigger and bigger. Actually an Ax 2012 R2 installation has something like 5000+ tables.

Slowliness on the synchronization process can be a problem when it come to update a production environment, since it will take the system unavailable for 20-30 minutes.

Deploying changes via model store using the AXUTIL temporary schema technique is very useful, but then you still have to waste a lot of time synchronizing the database.

However, to speed things up, you can synchronize only tables that are actually changed since the last update.

You really don't need to synchronize all those 5000 tables every time you update the system.

So here is a tool to come to rescue.

Ax keep track of changes you made to the aot on the Model* tables. With the query below you can find the last date on which a table or table field has been changed.

 SysModelElementData modelElementData;  
   SysModelElement   modelElement;  
   SysModelElementData modelElementDataRoot;  
   SysModelElement   modelElementRoot;  
   delete_from tmpFrmVirtual;  

   while select modelElement  
     group by modelElement.RootModelElement, modelElementRoot.Name, modelElementRoot.AxId  
     join maxOf(ModifiedDateTime) from modelElementData  
     where modelElementData.ModelElement == modelElement.RecId  
      && (modelElement.ElementType == UtilElementType::TableField || modelElement.ElementType == UtilElementType::TableIndex || modelElement.ElementType == UtilElementType::Table)  
      //&& modelElementData.Layer == UtilEntryLevel::cus + 1  
      && modelElementData.modifiedDateTime > DateTimeUtil::newDateTime(transDate, 0)  
     join modelElementRoot  
       where modelElementRoot.RecId == modelElement.RootModelElement  

So with that query you can filter down tables modified within the last X days, and synchronize only those tables.

I've built a tool that does exactly this, you got a screenshot below:

In the date field you can filter tables modified since this date. Pressing OK the synchronization of the selected tables will occour.

That way, I can usually synchronize the system in about... 10 seconds?

XPO project download link HERE

P.S.: please note that this is not a complete replacement for the DB synchronization, but a procedure that synchronize only a small subset of the database!
There are cases in wich a modification of a table is not tracked in the SysModel* tables, for example if you change a string extended data type lenght, or completely delete a table layer. So use at your own risk.


Chandra Setiawan said...

Hi I have download your tool but haven't tried it yet.

But sound like great tools..

I like it.

Unknown said...

Hi Ludo,

The tool looks great. I like simple solutions.
What i am not sure about, is whether the tool can
really replace the normal sync process.
For example: Changes in views are not yet included.

Even without replacing, this might be a nice addition.
Especially for thosee 'gadget collecting' developers. (Me)

Thanks, Gijs.