Tag Archives: administration

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 (2509 downloads)

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

Updating an AX users SID when they’ve been accidently deleted from Active Directory

If you ever find an AX user’s account has been accidently deleted from Active Directory then is recreated with the same user name but they cannot access AX this is because they now have a new Active Directory SID that doesn’t match the SID against their user in AX.

This can be resolved by updating their SID in AX with the newly recreated user’s SID.

 

The Problem:

“User1” was deleted in Active Directory, it was established that “User1” shouldn’t have been deleted, so “User1” was recreated.  Unfortunately “User1” couldn’t access AX though, they were receiving an Access Denied message.

Resolution:

First of all run the following powershell script (replace “User1” with your user) to find their SID

$AdObj = New-Object System.Security.Principal.NTAccount('User1')
$strSID = $AdObj.Translate([System.Security.Principal.SecurityIdentifier])
$strSID.Value

 

As you can see their SID is returned as S-1-5-21-1214440339-1788223648-682003330-48397

 

Using the SID returned by the Powershell script run the following SQL Script (replacing the  Database Name, SID and ID with yours) to update the newly created login’s SID

UPDATE [MY_DAX09_DATABASE].[dbo].[USERINFO]
SET SID = 'S-1-5-21-1214440339-1788223648-682003330-48397'
WHERE ID = 'User1'

Note: ID is not usually the same as the users AD login name, in this example it happens to be.  Doing a simple select statement on the [UserInfo] table will establish the users ID that you need to use in the above SQL Statement.

“User1” will now be able to access AX again.

Recovering from an AX 2009 AOS Crash

If for whatever reason you have an AOS crash, you can set the AOS service to restart automatically.  This will ensure your AOS is back up and running quickly and does not rely on manual intervention to restart the service which may not always be on hand to resolve at the time of failure.

Within Adminstrative Tools > Services
Right mouse click on your AOS Service and select ‘Properties’
Select the ‘Recovery’  tab
Change the settings to your requirements

The options shown in the example will attempt to restart the service twice in any one day of failure with a 1 minute interval before it attempts to restart the service.  The fail count is reset every 1 day.

 

Future posts will cover how you can be notified instantly by email of AOS Service crashes and of AOS Services not running.

Data cache is not always syncing between clustered AOS’s (Cannot edit a record in LastValue (SysLastValue). User ID: , AdminUserSetup.)

If you experience data mismatches between AOS’s it could be because the table is being cached by the AOS but is not syncing between other AOS’s.

So for example a user updates some data logged onto AOS01, a user then queries the data on AOS02 and it’s not there.  Even the automatic nightly flush of the AOS caches doesn’t update AOS02 the only way to get the caches in sync is to restart both AOS’s.

You might see the following error messages in the AOS Application Event Log:

110 Object Server 01: Dialog issued for client-less session 1: Cannot edit a record
in LastValue (SysLastValue). User ID: , AdminUserSetup.
The SQL database has issued an error.

If you have been experiencing this issue  then you may need to apply Hotfix KB261128 to resolve the issue or apply SP1 RU8 which also contains this hotfix.

AX 2009 Batch Job Created Notifications

Want to know instantly by email when users are creating batch jobs in AX 2009.  This saves you having to constantly go and check the Batch Job form and work out what’s new.

I have created a script which can be downloaded below which will create a SQL DML insert trigger on your database, so when a user submits a process to batch you can receive an email informing you of the submitter and name of job they have submitted.

There’s a couple of pre-requisites that need to be adhered to:

1.  Database Mail must be configured and working in your environment
2.  You need to know the following details:

  • Your Database Server and Instance Name (Instance Name only applicable if you are using named instances)
  • Your Production Database Name
  • Your Database Mail Profile name
  • Email Recipients Address (always best to have a generic distribution group email address)

With these details you can perform a find and replace on the defaults values in the script before running the script against your AX Database, full details are provided in the script itself on how to set it up.

Download  AX Batch Jobs Added Script (241 downloads)

DISCLAIMER: As always please try setting this up first in your test environment to ensure you understand the setup and whether it provides the functionality you require.

Finding which Operators are setup against SQL Agent Job Notifications

As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.

Agent Job Properties

How do I check which operators are setup against my Agent Jobs I hear you say!

Well run this query below to find out.  If you have any agent jobs return as ‘No email notification configured’ in the [Notify_level_email] field then an email notification is not setup and you should look into these as a priority (unless you have other methods of monitoring Agent Jobs that is!)

SELECT SJ.NAME, SO.[name],SO.[email_address],
[notify_level_email] =
CASE notify_level_email
     WHEN '1' THEN 'When the job succeeds'
     WHEN '2' THEN 'When the job fails'
     WHEN '3' THEN 'When the job completes'
     ELSE 'No email notification configured'
END
FROM
msdb.dbo.sysjobs SJ LEFT JOIN [msdb].[dbo].[sysoperators] SO 
ON SJ.notify_email_operator_id = SO.ID
ORDER BY SO.ID, SJ.NAME

Download  Finding-which-Operators-are-setup-against-SQL-Agent-Job-Notifications (130 downloads)

The example below shows the operators setup against Agent Jobs and the email address the notification will be sent to, the first row shows that no operator is setup on this job for email notifications, if this job fails no-one may be aware.

Agent Job Notifications

 

There’s a few pre-requisites that your need to ensure are setup first for Agent Email Notifications to work:
1.  Database Mail should be configured and working (see this great article on SQL Server Central if you don’t know how to setup Database Mail)
2.  Agent Operators should be setup with valid email addresses (see my post on recommended operators to setup)  
3.  Agent Jobs should have an email notification configured ideally for ‘When jobs the fails’

SQL Agent Operators Recommended Setup

Database Mail should be configured on your SQL Server, if it isn’t get it done now there are no excuses, this is one of the main methods that can be used for finding out when there are issues with your SQL Server!

It’s a good idea to setup an operator with a distribution email group email address so if a DBA leaves or moves onto another position the distribution group can be easily updated once in Active Directory as opposed to being updated on every SQL Server.

DBA Team Operator

 

You could also create additional distribution email groups and add them as operators this is useful if other administrators needs to be aware of certain Agent Jobs failing such as say BI Jobs.

DBA Team BI Operator

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 (519 downloads)

Dynamics AX 2009 components are installed into an incorrect directory and the install fails

I recently encountered an issue when installing a 2nd AOS on a server that it failed to install correctly.  The components were attempting to install into an incorrect directory, therefore the install failed as it was unable to register the service correctly.

You may see the following error message within the “DynamicsSetupLog.txt” install log.

AOS.1: Regasm C:\Windows\Microsoft.NET\Framework64\v2.0.50727\regasm.exe   "C:\Program Files\Microsoft Dynamics AX\50\Server\Server\LiveAOS02\Bin\Microsoft.Dynamics.IntegrationFramework.dll"
RegAsm : error RA0000 : Unable to locate input assembly 'C:\Program Files\Microsoft Dynamics AX\50\Server\Server\LiveAOS02\Bin\Microsoft.Dynamics.IntegrationFramework.dll' or one of its dependencies.

An error occurred during setup of Application Object Server (AOS).
Reason: Error unregistering  Microsoft.Dynamics.IntegrationFramework.dll. Program run: C:\Windows\Microsoft.NET\Framework64\v2.0.50727\regasm.exe.  Parms: "C:\Program Files\Microsoft Dynamics AX\50\Server\Server\LiveAOS02\Bin\Microsoft.Dynamics.IntegrationFramework.dll" /unregister

As you can see it’s attempting to install the AOS into an incorrect directory namely:

C:\Program Files\Microsoft Dynamics AX\50\Server\Server\

The correct folder is:

C:\Program Files\Microsoft Dynamics AX\50\Server\

A hotfix exists to resolve this issue it can be download from Customer Source here: https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;en-us;959494

View the knowledge base article on the MS Support site here:  http://support.microsoft.com/kb/959494/en-us

SQL Server Administration – SQL Object Naming Convention

It’s useful to use a naming convention on Agent Jobs or Maintenance plans, this ensures they are grouped together and easier to find when you have lots of job or maintenance plans on your SQL Server.

Download SQL-Server-Object-Naming-Conventions-Administration (992 downloads)

Download SQL-Server-Object-Naming-Conventions-Administration (1498 downloads)