Database upgrade scripts for D365 Finance and Operations. Upgrade steps overview.

The standard application code has many standard data migration scripts. Their main purpose is to migrate data for standard tables. 

The process of data migration has different steps which include Pre-req, Pre-sync, Database sync, Post-sync, Final database sync, stopping and starting various services and many more.

Below you will find a short description of the key steps.

• Pre-req - At this step, the upgrade framework prepares the source database for an upgrade. This step patches the SQL dictionary, applies SQL sequences instead of system sequences, modifies user info and system variables, synchronizes system tables of the database, and performs an additive synchronization of new tables. 

• Pre-sync - At this step, the upgrade framework executes Pre-synchronize upgrade scripts to make metadata changes before step “synchronize tables”. 

For example, pre-synchronize scripts map the database table or fields on the source Microsoft Dynamics AX system to the database table or fields in the target system. This table-to-table and column-to-column mapping prepares the source-to-target upgrade model for data copy from source to target. Other kinds of pre-synchronize scripts disable unique indexes in preparation for data upgrades and avoid duplicating key errors. This action is undone during Post-synchronize step.

In other words, at this stage, there is the full set of metadata of both versions. The database contains tables from Ax 2012 and their data as well as the full set of new D365 tables. During this step, you can copy data from Ax 2012 tables and fields to D365 if the table or field was renamed in the version. When this step is completed, the database data should be whole from a D365 application perspective. Otherwise, you will see synchronization errors at the next stage.

In case it is needed to write scripts to move data from specific Ax 2012 fields to the appropriate D365 field and tables it can be done via direct SQL command because Ax 2012 fields do not exist in D365 version from a D365 code application perspective.

• Database sync - At this step, the upgrade framework executes the database synchronization with  the current application code. When this step is completed all tables that do not exist in D365  application will be deleted and their data will be lost.

• Post-sync –  At this step, the upgrade framework executes post-synchronization upgrade scripts that contain the bulk of the data upgrade scripts. The database contains the fields and tables from D365 version only. So all data from Ax 2012 was lost at this step. 

Usually, company-specific business data is upgraded in post-synchronization upgrade scripts. Post-synchronization also reverses the metadata changes (disables unique indexes) that were made during the pre-synchronize step. During post-synchronization, upgrade scripts run to update existing data when required and populate newly created tables and columns. Running upgrade scripts and any inter-dependencies are controlled by the upgrade framework. Upgrade scripts are implemented for any major version change.

At this stage, it is possible to write scripts to manipulate migrated data in D365 tables via code or SQL commands.

• FinalDBSync – This step does the final database synchronization that synchronizes all remaining objects in the database.