How to solve AX Data Refresh with Powershell?

The number of resources regarding PowerShell with AX, at our disposal, is so small and it is so little we can do with them, this is a topic that I would like to increase awareness starting an open discussion. I will try to bring to the blog scripts that have been helping me to optimize daily tasks.

For my first script, I will talk about Refreshing AX Data from Production to Test. This is complex and time-consuming work, especially when you have to ensure that your test environment is not pointing to production batch servers, POS terminals, web services, TFS and so forth.

In my initial version of this script I was exporting the environment configuration to CSV files. In this approach, I have to create a store procedure to generate inserts from the AX database. The store procedure had its limitations, such as exporting some data fields (timestamp, geography, geometry, hierarchyid, image, binary or varbinary) and slow performance for big tables (1000+ rows).

The script has been improved creating a central database for UAT, DEV or TEST, which gives us the ability to reuse the configuration more easily, empowered for SQL features (compression, backup, bulk inserts).

Let’s look at the script and how to get your test data refreshed.

Prerequisites before you move forward:

1. Windows PowerShell Extensions for Microsoft SQL Server (PowerShellTools.msi)

• SQL 2012 – https://www.microsoft.com/en-us/download/details.aspx?id=29065
• SQL 2014 – https://www.microsoft.com/en-us/download/details.aspx?id=42295
• SQL 2016 – https://www.microsoft.com/en-us/download/details.aspx?id=52676

2. Create Refresh Database (RFR_Database.sql)

3. Update PowerShell Script:
3.1. SQL Server name and Database name (lines 25 and 26) are required values.
3.2. Review $ParamSqlTruncate  (default set to $true), this parameter truncates all destinations before copying data. It prevents duplicate keys in AX Database and data merge in store database.
3.3. Review $ParamSqlCompression (default set to $true), it tries ColumnStore first and then Page compression, if both methods fails, compression is set to None.

4. Define your working tables in $Script:RFRTables and truncate only in $Script:RFRDelTables (please note there are no backup for these tables).  I strongly recommend to you spending some time doing this analysis and creating a well-structured set of tables. The tables bellow are an example, you should change it according to your needs.

Note: The script does not take into consideration table constraints, primary keys and relationship between AX objects. If you export SALESLINE without SALESTABLE, you have data integrity issues.

5. To manage Windows services, you should open PowerShell as Administrator and enable Remote Service Management, WMI, and WinRM on your firewall.

Once everything is configured, press F5 to run the script.
(Do not use this tool in Production. Make sure you have exported all data before deleting any table.)

The script will request you to input an environment configuration before any option. You may use an existing environment configuration or create a fresh store name (environment names have a limit of 30 chars). You can load and release environment configurations using L (Load Environment) and X (Release Environment), at any time. Also, Q to finish the script and R to get back to the previous menu.

Main Menu

• The Main Menu has all the steps to create a backup of your lower environment configuration into the store database, execute most common refresh steps and restore the configuration back to your environment. Also, through this menu, we have access to some additional tools.

Additional AX Tools Menu

• Batch Jobs Maintenance, see description bellow.
• Check SystemSequences Table provides us the possibility to check RecIDs on all working tables and fix them to avoid duplicates. (less likely to happen).
• AX Services Tools, see description bellow.
• Delete Environment Store deletes all backups and historical data for a designated environment.
• Export and Import CSV are the previous methods to save an AX, this process creates a stored procedure in the database (sp_GenerateInsertScript) to create CSV files with inserts for each row that might need to be restored. Exporting to CSV runs slow when working with 1000+ rows.
• Update GUID is not common, but it fixes issues with AX caching.

Batch Jobs Maintenance Menu

• Batch Jobs maintenance assists when moving batch groups out of batch servers or moving batch groups to a specific batch server. Also, it is possible to delete batch history from the database.

AX Service Tools Menu

• It is possible to Start, Stop, Restart and Check AOS Services for a specific environment. If there are no servers in the script database, you can type a server name or multiple servers separated by comma.

In conclusion, this script can make significant improvements by simplifying operational tasks when copying live data to all your lower environments. I hope this script can help you and your customer to realize the full potential of PowerShell as a PowerShell scripting language, in order to automate AX administration tasks. Let’s keep the conversation going and create more interesting scripts for AX using PowerShell.

CodePlex Download: https://dynamicsrefresh.codeplex.com/

Thanks,
Bruno Ferreti

DISCLAIMER
Microsoft Corporation. All rights reserved.
Do not use in Production. Sample scripts in this guide are not supported under any Microsoft standard support program or service. These sample scripts are provided without warranty of any kind expressed or implied. Microsoft disclaims all implied warranties including, without limitation, any implied warranties of merchantability or fitness for a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages. The opinions and views expressed in this script are those of the author and do not necessarily state or reflect those of Microsoft. Do this at your own risk. The author will not be held responsible for any damage you incur when running, modifying or carrying out these scripts.