AX DB Restore Scripts – Full Script

Just over a month ago I embarked on a series of posts on SQL scripts that I use when restoring Dynamics AX databases from one environment to another. We have covered topics including server setups, general configurations as well as data cleans to get your new environment up and smoothly running as quickly as possible. We are now at the end of the series and I trust it has been helpful to you in structuring your scripts.

I’d just like to remind you that these posts and scripts are not meant to be used as is but rather to serve as a guide to adapt for your specific scenario and to prompt your own thinking as to what you will need.

That said you can download my templated script here or view it below (download)

View Previous – AX DB Restore Scripts #8 – Disable users

Back to List

AX DB Restore Scripts #8 – Disable users

A few weeks ago I embarked on a series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another. We have covered a variety of topics including various configuration and setting changes to Dynamics AX. This post will be the last in the set of “data cleanup scripts” designed to help scrub your data for use in a development or testing environment.

Disabling users.

Quite often companies or partners will require access to development environments to be restricted to a very select group of users that are aware of what to test and are also careful enough to work methodically in the correct environments (i.e. not trying to to real work in the wrong environment or test work in production). To ensure all of the above one could follow a few paths.

  1. Only provide a shortcut/configuration to users who require it.
  2. Color code your environments so that they immediately recognizable.
  3. Add a startup message to your AX environment to warn users what environment they are logged into.

All of these solutions do not physically stop users from accessing the alternate environment.  So if you need an additional security mechanism, simply disable the users either manually in AX after doing a restore or disable then along with the SQL scripts we have been building up over the past few weeks.

To disable users in AX navigate to “System Administration -> Common -> Users -> Users. Double click on the user in question, click “edit”, uncheck the “enabled” button.DisableUser

The accomplish the same in a SQL script you can do the following. I have parameterised the SQL for easier reuse or adjustment.

Note 1: Parameterising a list can be tricky in TSQL, so the above trick is what i found easiest.
Note 2: You will need to decide whether the list of users provided is inclusive (i.e. provide the list of all the users you WANT to disable) or exclusive (i.e. disable all users except for a small subset). I have provided both options, but would recommend the exclusive approach as it is more likely to give you safely give you what you expect.

I hope this assists you and will be useful in your management of your AX environments. Keep an eye out for new posts in this series!

View Previous – AX DB Restore Scripts #7 – Setting Email Templates and User Email Addresses

Back to List

AX DB Restore Scripts #6 – SMTP Server and DMF Folder

Recently I started creating a list and a series of posts on SQL scripts I use when restoring Dynamics AX databases from one environment to another, typically from production to QA or development.

We have so far discussed a number of configurations that must be changed for the new system to operate effectively and starting yesterday we moved onto exploring more optional configurations.

Today we will cover two of these items namely re-configuring the outgoing SMTP server in AX and configuring the settings for the DMF working folder.

Note: In my experience the SMTP server will most likely not change between a production and development server, but you may need to reconfigure it if moving to a new domain etc. Similarly it will work to use the same DMF shared folder between PROD and DEV, however I would recommend changing this to keep the data 100% separate.

These two configurations are located in the Dynamics AX 2012 client under the following paths.
1. SMTP Server, accessed via System Administration -> Setup -> System -> E-mail parameters.EmailServer

2. Data import, export framework shared working directory. Accessed via “Data import export framework -> Setup -> Data import export framework parameters”
DMF

To update these 2 settings above we need to do the following:

1. Update the SysEmailParameters table to update the SMTP server name (and additional login parameters)
2. Update the DMFParameters table to reflect the new shared folder path.

The following SQL code will accomplish the above. I have parameterised the SQL for easier reuse or adjustment.

I hope this assists you and will be useful in your management of your AX environments.

View next – AX DB Restore Scripts #7 – Setting Email Templates and User Email Addresses

View Previous – AX DB Restore Scripts #5 – Configure Service Accounts and Help Server

Back to List

Synchronize Dynamics AX DB in X++

Challenge/Problem: Synchronise Dynamics AX database from X++

Description: Previously I have written code to manipulate AX AOT objects via code (reflection) and found the need to kick off a database sync automatically from X++ code. I found the code snippets below to be useful for this operation.

The following two options allow you to perform the db syncronisation.

1. To synchronize a specific table: appl.dbSynchronize([tableid]);
2. To synchronize the whole app: appl.dbSynchronize(0);