Tag Archives: nonproduction

Exporting common AX 2009 Environment Configuration Settings

Wouldn’t it be great not having to re-configure certain configuration settings in your non-production environment after you’ve done an application/data refresh?

Well you can by exporting your common config settings using the Microsoft Database Publishing Wizard.

What you end up with is a SQL script file to run against your AX database once you’ve completed your database restore, the only pre-requisites are you need the Database Publishing Wizard tool and also you need to export your settings before you overwrite your data (the last one is pretty obvious!!!).
So the procedure would be as follows:

  •  take a copy of the config settings in your non-production environment whose data is to be overwritten
  • refresh your non-production environment with data from another environment
  • execute the SQL script containing the config settings exported prior to the refresh into your non-production environment to replace them

Download the guide here:
Exporting-common-AX 2009-Environment-Configuration-Settings.pdf (2932 downloads)

You can add to the config/tables being exported for any other config settings you require keeping in your environment.

Get notified when Admin or All user group permissions are granted to users in AX 2009

Want to know instantly when a user has been granted ‘Admin’ or ‘All’  user rights in your Production or Non-Production environments?

Of course you do!  Implementing the script available to download below gives you control over your AX Environment with regards to knowing when permissions could have been granted incorrectly.

The script will create a trigger on the [USERGROUPLIST] table, if the user group ‘Admin’ or ‘All’ is added to a users profile then an email notification will be sent to your AX Administrators.

Follow the instructions within the SQL Script on what needs to be changed to configure it in your environment.  As always please setup and test in your Dev/Test Environment to ensure you understand the setup and that it works as intended in your environment.

I also always advise sending the email notifications to a distribution email group as opposed to individually named email addresses as it means you only need to change the distribution email group for Administrator leavers/starters as opposed to having to change numerous pieces of code/jobs/notifications.

Download Admin User Group Check Setup (653 downloads)

Modifying Batch Job Statuses in a Non-Production Environment

There’s a few things you can do when undertaking your data refresh into a non-production environment one of them is modifying your batch jobs to ‘Withhold’ status ensuring they won’t start to run when you after you start up your AOS’s and then configure your batch server settings. After the script has been ran you can set the status of jobs you want to run back to ‘Waiting’ status at a later point during the data refresh process, happy in the knowledge that you know no batch jobs are going to start emailing customers or update/talk to other external systems.

SQL Script:

-- Change to correct DB
 USE <Your Ax Database>
-- Set all Batch Jobs to Withhold status
 UPDATE BatchJob
 SET status = 0

You could also have a scenario where only certain batch jobs should be set to Withhold status, you could do this by adding the batch jobs you want to continue to run into a table and then only update the ones not in that table to Withhold. An example script to do this can be found below:

Pre-Requisites: Create a table with one data field called ‘Caption’, populate table with batch job names that you want to continue to run in your non-production environment (e.g. not be at withhold status)

SQL Script:

-- Change to correct DB
USE <Your Ax Database>

UPDATE BatchJob
SET status = 0
WHERE
Caption NOT IN (SELECT Caption FROM <the table name created in pre-requisites>)

Disclaimer: Ensure any batch jobs you add to this table don’t have other parameters that need to be changed prior to them running again in the non-production environment.

Removing Email Addresses from Non-Production Environments

A few simple scripts to run when building a 2009 non-production environment from your 2009 production database to remove email addresses from the ‘CustTable’, ‘VendTable’ and ‘Address’ table this will ensure no emails can be sent in error to your customers or vendors from your non-production environment.

-- Change to correct DB
USE <Your Ax Database>

UPDATE Address
SET EMAIL = ''
WHERE
EMAIL != ''

UPDATE VendTable
SET EMAIL = ''
WHERE
EMAIL != ''

UPDATE CustTable
SET EMAIL = ''
WHERE
EMAIL != ''

DISCLAIMER: You may need to add additional tables/fields for any customizations you may be using.

Removing User Login Data from Non-Production Environments

As part of updating your AX 2009 non-production environments I recommend running a script to remove user login data so you can see who is using your environment and it doesn’t cloud things with it having LIVE login data in there.

This script will delete user log data prior to the database creation date (which should be the restored date of the database if a RESTORE DATABASE WITH REPLACE was used)

The user log data can be viewed in AX2009 via ‘Administration > Inquiries > User Log’

Initially run this query to determine the AX DB creation date:

SELECT create_date
FROM
sys.databases
WHERE
name =  '<Your AX Database Name>'

Once you’ve verified the date is okay run the below script:

/* Delete User Login Data based upon Database Creation Date */
DECLARE @dbcreatedate DATETIME

/* Get Database Creation Date */
SELECT @dbcreatedate = create_date
from
sys.databases
WHERE name = '<Your AX Database Name>'

SELECT @dbcreatedate AS Database_Creation_Date

/* Delete User Login Data */
DELETE FROM SysUserLog
WHERE
CREATEDDATETIME < @dbcreatedate

Download  Delete-User-Login-Data-using-Database-Creation-Date (853 downloads)