Synchronization enhancements in Dynamics Ax 2009
This post covers the Synchronization functionality in Dynamics Ax 2009 and some of the enhancements that were made from the previous release.
Check Synchronize feature in Dynamics AX 2009
You may have noticed the changes when Synchronizing during Upgrade or maybe you have seen a Synchronize database form when making a change to an AOT table that would permanently lose data (making the size of a field smaller for example). The fact is that the Synchronize feature has received an interesting face lift for Ax 2009.
If you are familiar with Ax Synchronization you know that under the covers it is just looking for differences between the AOT and the Database and then updating the Database to make it look like the AOT. First it will look for tables that need to be dropped from the Database, then tables that need to be created, altered or modified and finally synchronize will fix the views.
There are two important features that we wanted to add to the Check\Synchronize feature in the Ax 2009 release, we wanted to make sure that you where notified when a change in the AOT resulted in irretrievable database changes and we wanted to give you enough information about the changes that you could bypass the synchronization engine in Dynamics Ax 2009 completely. Fortunately, we were able to satisfy both requirements with a very simple design decision.
In Dynamics Ax 2009 when you run Check Synchronize we are actually doing two full Synchronization passes. During the first pass we do not make any changes to the database, but we do generate a complete record of the Sql statements that we would have used if it had been the final pass. After the first full pass we have added a Synchronize Database form that gives you a complete report of the changes. Once you select Continue on the Synchronize database form we will do a second full pass and this time we will update the database. Note that we do not use the information from the first pass in the second, they are independent.
Synchronize Database form:
Between the two full passes we are now showing a Synchronize database form with all of the information on the changes that may impact the database.
Administrator->Periodic->SQL administration->Table actions->Check/Synchronize form
There is another version of this form that pops up when you are making a change to the AOT that results in permanent data loss in the database. For example if you modify the size of a Table String field from 10 characters to 2 you will get the following form pop up when you save.
As you can see this is really the same form but with the Export DDL button removed. In both cases looking at the Warnings you will see a message indicating what the Synchronization problem will be if you continue, in this case it reads: “The 'Field1' field string length decreased from 10 to 2 which could result in data truncation.”
Export DDL.
The export DDL button allows you to save to a file the complete set of SQL statements that Synchronize would have used to update the database to make it look like the AOT. As you can imagine this is a great help for debugging problems but it also allows you to bypass the Dynamics AX synchronization engine. Please note the warning that you get when clicking this button:
The file that is generated uses comment tags to separate the different steps, this makes it a little more readable. In this particular example the interesting part of the file looks like this:
/* table drops */
/* table create/alter/modify */
/* block */
CREATE TABLE "DBO".X6188X (FIELD1 NVARCHAR(2) NOT NULL DEFAULT '' ,DATAAREAID NVARCHAR(4) NOT NULL DEFAULT 'dat' ,RECVERSION INT NOT NULL DEFAULT 1 ,RECID BIGINT NOT NULL CHECK (RECID <> 0) )
INSERT INTO X6188X SELECT {fn SUBSTRING(FIELD1,1,2)},DATAAREAID,RECVERSION,RECID FROM TABLE1
DROP TABLE TABLE1
EXECUTE SP_RENAME X6188X, TABLE1
We have changed the size of TABLE1 Field1 from 10 to 2 in the AOT, the synchronization engine is going to update the Database in the following manner:
· create a dummy table DBO.X6188X that has the same schema as TABLE1 _except_ with a field size of NVARCHAR(2) instead of NVARCHAR(10)
· Copy all of the data from TABLE1 to X6188X, this will use the Database truncation algorithm to move all of the data from TABLE1 Field1 size 10 to X6188X Field1 size 2.
· Delete Table1
· Rename X6188X to Table1
· (not shown) update the SQLDICTIONARY table by dropping all references to TABLE1 and recreating all data on the new TABLE1
Bypassing the AOS Synchronization engine
The above scheme allows partly bypassing some of the synchronization steps and this could be a very useful scenario during Upgrade where we use Check Synchronize and the Synchronization time is critical.
There is nothing stopping you from executing the statements in the exported file directly (maybe using multiple client machines in parallel where possible?) or to modify the synchronization steps to improve performance (move the inserts to the SQLDICTIONARY table to BCP for example). As long as all of the statements generated are correctly executed you will have synchronized the database without relying on the AOT. Because the second full pass is independent of the first pass, when you click Continue the Synchronization engine will realize it no longer has to do any database updates and complete very fast.
Turning off the Synchronize Database form
It is possible that the Synchronize Database form is now showing up unexpectedly during some of your scripts, if so you can programmatically set it on or off using the SysSqlSync ShowSysSqlSync global cache setting, here is a job that will turn off the form and automatically synchronize:
static void ShowSysSqlSync(Args _args)
{
SysGlobalCache gc;
str owner = 'SysSqlSync';
str key = 'ShowSysSqlSync';
boolean showEnabled = false; //set to true to enable.
;
gc = appl.globalCache();
gc.set(owner, key, showEnabled);
info(strfmt('Show SqlSync: %1', gc.get(owner, key)));
}
Hope this was helpful, and let us know if you would like us to cover more in this feature area.
Angel Saenz-Badillos