Microsoft Dynamics AX2012 Import-AXModelStore

The suggested method for deploying Dynamics AX2012 code into production environment (Eg. Go live) is to import the model store.

Read more about the difference between model and model store at this link: http://technet.microsoft.com/en-us/library/hh335184.aspx

Compare AX4, AX2009 and AX2012 deployment:
- AX4, AX2009: Copy layer and label files, replace the destination files (in application folder)
- AX2012: Export model store and import model store

There’re two ways to import model store:
1. Using PowerShell
2. Using AXUtil.exe

The rest of the text below will use PowerShell as the AXUtil will be obsolete in future version of Dynamics AX.

Start Dynamics AX PowerShell from here: All Programs > Administrative Tools > Microsoft Dynamics AX Management Shell

The steps:

1.             Export model store from your source (Eg. UAT environment)

(Similar to AX2009 copy layer & label files)

- Export model store

  

2.             Import model store to destination (Eg. Production environment)

(Similar to AX2009 replace layer & label files)

- Create a temporary schema
- Import model store to temporary schema

- Apply the model store

Export model store

Export model store is quite straight forward.
Syntax: Export-AXModelStore -File <filename> -Details
Eg. Export-AXModelStore -File “UAT.axmodelstore” –Details

What happen behind the scene?
The Export-AXModelStore connect to SQL server and access the AX database, then select and save the data into file (with the file name you specified in the command).

It gets the database name from AX server (AOS) configuration.
  

Export-AXModelStore with file name "STD_ModelStore"

Import model store

Create a temporary schema
Create a temporary schema in AX database to be used on next step during import model store.

Steps:
1. Open SQL Server Management Studio
2. Connect to the SQL Server instance where AX database resides
3. Expand to Databases > [AxDatabaseName] > Security > Schemas
4. Right click > New schema
5. Type in the temporary schema name (Eg. TempSchema) and select the schema owner and click "OK"
6. Initialize the temporary schema (Eg. Initialize-AXModelStore -AOSAccount "Domain\AccountName" -SchemaName <NewSchema> -Server <ServerName> -Database <DatabaseName>)

Import model store to temporary schema
Syntax: Import-AXModelStore -File <filename> -SchemaName 
Eg. Import-AXModelStore -File UAT.axmodelstore -SchemaName TempSchema

What happen behind the scene?
The Import-AXModelStore read the *.axmodelstore file and do bulk insert.
It uses .NET SqlBulkCopy class and through TDS (Tabular Data Stream) pushing the data into AX model store tables.

You’ll see the command in SQL server like this: insert bulk [TempSchema].[ModelElementData] ([LayerId] Int, [ParentHandle] Int, [ElementHandle] Int, [LegacyId] Int, [Properties] VarBinary(max), [FileHeader] VarBinary(max), [BASEVERSION] Int, [VERSION] Int, [SAVECOUNT] Int, [CONSISTENCYDATA] Int, [MODIFIEDDATETIME] DateTime, [MODIFIEDBY] NVarChar(5) COLLATE SQL_Latin1_General_CP1_CI_AS, [CREATEDDATETIME] DateTime, [CREATEDBY] NVarChar(5) COLLATE SQL_Latin1_General_CP1_CI_AS, [ElementVersion] Int, [ModelId] Int) with (TABLOCK)

This "INSERT BULK" is different than the "BULK INSERT", it can’t be used in SQL server directly, it has to go through API.

Import-AXModelStore with file name "UAT.axmodelstore" and schema "TempSchema"

Apply the model store
Syntax: Import-AXModelStore -Apply:TemporarySchema
Eg: Import-AXModelStore -Apply:TempSchema

What happen behind the scene?
It execute several stored procedures to transfer the temporary schema to default.
Eg.
- XU_IsSchemaEmpty
- XU_CreateSchema
- XU_TransferObjectsToSchema
and etc

Apply model store (Import-AXModelStore -Apply) with the temporary schema imported earlier

You doesn't need to manually remove the temporary schema after apply it as they won't remain in the AX database after the APPLY process.

But if you decided not to apply the model store, you can remove it with the following syntax.

Syntax: Initialize-AXModelStore –Drop 
Eg: Initialize-AXModelStore –Drop TempSchema

Alternative
If you prefer to directly import the model store into default schema and replace it, do remember when it hit ID conflict, the import will stop.

For more details about working on model store, visit this link:
http://technet.microsoft.com/en-us/library/hh433530.aspx
http://technet.microsoft.com/en-us/library/hh433540.aspx

Misc. screenshot during my test

Create AX model store (Initialize-AXModelStore)

Import-AXModelstore error due to database referred in AOS settings does not exist

Initialize-AXModelStore error on pre-release AX2012 database due to stored procedure - "XU_DropSchema" does not exist

Import-AXModelStore error when schema does not exist



Import-AXModelStore error when Id conflict detected

  

Apply AX model store (Import-AXModelStore -Apply) error when applying the RTM model store to pre-release AX database due to XU_CreateSchema does not exist in pre-release database

Apply AX model store (Import-AXModelStore -Apply) error when applying the RTM model store to pre-release AX database due to pre-release database stored proc is outdated, has different number of parameters

 

Some of the tables created using Initialize-AXModelStore (to get the full list, you can query the list of objects attached to the schema you created through the Initialize-AXModelStore)