Category Archives: Dynamics AX 2009

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

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

Checking AX2009 ‘EntireTable’ Caching Setup

We are going to look at how you can review your ‘EntireTable’ Cache settings on tables within the AOT and why performance can be improved if your settings are incorrect.

When a table’s Cache Lookup in the AOT is set to ‘EntireTable’ it means a maximum of 2,000 records are loaded into the AOS servers cache for that table which saves having to go back to the SQL Server Database each time a query on that table is performed.

Each time a record is Inserted / Updated / Deleted the cache on the AOS has to be dropped and reloaded for that particular table, if you have a large table that is set to ‘EntireTable’ type caching and the table is being changed often this can cause detrimental effects on performance for your users as the table is having to be dropped from the cache and reloaded often and the AOS may have to go to back to the database anyway if the record isn’t in the cache.

We can check quite easily to see what tables are set in the AOT to ‘EntireTable’ caching and their associated row count in the database using the scripts below, I’ve provided two scripts one for real-time analysis and one which can be used with DynamicsPerf if you have this setup and configured for your environment:

Real-Time Analysis:

/* AX EntireTable Caching Row Counts (Real Time).sql
-- D.Coupland 24/03/13
-- Find rows counts of tables with cache lookup set to 'EntireCache' 
-- Investigate any row counts over 2,000+ to ensure they are not affecting performance
*/

USE <MyDax09Database>

SELECT  
AOT.TABLENAME
,APPLICATION_LAYER = CASE
                        WHEN AOT.TABID BETWEEN 1 AND 7999 THEN 'SYS'
                        WHEN AOT.TABID BETWEEN 8001 AND 15999 THEN 'GLS'
                        WHEN AOT.TABID BETWEEN 16001 AND 17999 THEN 'DIS'
                        WHEN AOT.TABID BETWEEN 18001 AND 19999 THEN 'LOS'
                        WHEN AOT.TABID BETWEEN 20001 AND 29999 THEN 'BUS'
                        WHEN AOT.TABID BETWEEN 30001 AND 39999 THEN 'VAR'
                        WHEN AOT.TABID BETWEEN 40001 AND 49999 THEN 'CUS'
                        WHEN AOT.TABID BETWEEN 50001 AND 59999 THEN 'USR'
                        WHEN AOT.TABID >= 65000 THEN 'System Table'
                        ELSE Ltrim(Str(AOT.TABID))
                     END
,ST.ROW_COUNT
FROM 
AOTTABLEPROPERTIES AOT
	INNER JOIN 
SYS.DM_DB_PARTITION_STATS ST
	ON OBJECT_NAME(ST.OBJECT_ID) = AOT.TableName
WHERE
ST.INDEX_ID < 2
AND AOT.CACHELOOKUP = 4 -- EntireTable Cache Enabled
AND ST.ROW_COUNT > 0 -- Ignore Tables with no rows
ORDER BY ST.ROW_COUNT DESC

Using DynamicsPerf DB

/* AX EntireTable Caching Row Counts (Using DynamicsPerf DB).sql
-- D.Coupland 24/03/13
-- Find rows counts of tables with cache lookup set to 'EntireCache' 
-- Investigate any row counts over 2,000+ to ensure they are not affecting performance
*/

USE <MyDynamicsPerfDB>

SELECT DISTINCT 
 TD.TABLE_NAME
,TD.APPLICATION_LAYER
,TD.CACHE_LOOKUP
,ISC.ROW_COUNT
FROM 
AX_TABLE_DETAIL_CURR_VW TD 
	INNER JOIN 
INDEX_STATS_CURR_VW ISC 
	ON TD.TABLE_NAME = ISC.TABLE_NAME
WHERE
ISC.ROW_COUNT > 0  AND -- Ignore Tables with no rows
CACHE_LOOKUP = 'EntireTable' 
ORDER BY ISC.ROW_COUNT DESC

Download the scripts here: AX EntireTable Caching Row Counts (1030 downloads)

I recommend you review any tables over 2,000+ rows in your ‘EntireTable’ caching strategy regularly ensuring you test any changes prior to making the changes in your Production Environment.

For further reading on AX 2009 Caching please take a look at the following articles:

http://msdn.microsoft.com/en-us/library/bb314693(v=ax.50).aspx

http://dynamicsuser.net/forums/p/30043/157813.aspx

AX 2009 Report Deployment Session Log On Failed

Issue:

When attempting to deploy a report library using the “Microsoft Dynamics AX 2009 Reports Deployment” Tool you may receive the following error:

“Error: Session log on for Microsoft Dynamics failed”

Report Deployment Session Logon Error

If you check the application event log you will have two source event messages called “Microsoft.Dynamics.Framework.BusinessConnector”

The first error message will show:

Dynamics Adapter Logon failed.

Connection with the Application Object Server could not be established.

Microsoft.Dynamics.BusinessConnectorNet.LogonFailedException
at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(BC_PROXY_ACCOUNT_INFO* pBCProxyAccountInfo, String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsAdapter.Logon(String company, String language, String objectServer, String configuration)

The seconds error message will show:

Session log on for Microsoft Dynamics failed.

Connection with the Application Object Server could not be established.System.InvalidOperationException
at Microsoft.Dynamics.Framework.Design.Reports.DynamicsReportsDesignTimeSession.OnHandleException(String message, Exception exception)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsSession.HandleException(String message, Exception exception, HandleExceptionCallback callback)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsAdapter.Logon(String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsSession.OnLogon(IAxaptaAdapter axaptaAdapter)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsSession.Init(ICacheContext context)

Connection with the Application Object Server could not be established.

Microsoft.Dynamics.BusinessConnectorNet.LogonFailedException
at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(BC_PROXY_ACCOUNT_INFO* pBCProxyAccountInfo, String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.BusinessConnectorNet.Axapta.Logon(String company, String language, String objectServer, String configuration)
at Microsoft.Dynamics.Framework.BusinessConnector.Session.DynamicsAdapter.Logon(String company, String language, String objectServer, String configuration)

 

Cause:

There could be two possible causes

1. The AOS running the application containing the report libraries is not started.

2. The Microsoft Dynamics AX 2009 Configuration tool is not pointing to the correct AOS, you must apply a configuration that points to the application containing the report libraries you want to deploy to SSRS.

Resolution:

1. Check the AOS is running.

2. Check the “Microsoft Dynamics AX 2009 Configuration” (AX Client Config Tool) is pointing to the correct AOS on the server your attempting to deploy the reports on.

How to Change the Business Connector Account after Enterprise Portal is Already Installed

Find out the steps and actions needed to change the Business Connector Proxy account in Microsoft Dynamics AX after you have EP installed, and SSRS and SSAS configured for EP by reading this post on the AX Support Team’s blog.

http://blogs.msdn.com/b/axsupport/archive/2012/09/13/how-to-change-the-business-connector-account-after-enterprise-portal-is-already-installed.aspx

 

Dynamics AX 2009 Tracing on Application Object Servers the easy way

Troubleshooting AX 2009 Performance Issues can be sometimes frustrating when you’ve got a lot of users using the system and you want to trace one particular users session.

One way that can ease this is to log the user onto an AOS where the service account it runs under is different to your other AOS’s and where they are the only user on the AOS. This means you can trace only their session using the “Microsoft Dynamics AX 2009 Server Configuration” application along with SQL Server Profiler (In Profiler you can specify the service account to be traced).

In order to set this up there are two different options, option 1 is the preferred option.

Option 1: Install a new AOS on an existing server that has the capacity or a new server – you can allocate specific CPU’s for each AOS Service on your server within the “Microsoft Dynamics AX 2009 Server Configuration” application, I will cover this in another article at a later date.

Option 2: Use an existing AOS Service

 

Option 1: New AOS Service

1.  Create a new Active Directory Login

e.g. CONTOSO\PerfMonAosSvc

 

2.  Add user to ‘Performance Log Users’ group

Performance Log Users GroupThis is required so AOS Tracing can be started via the Tracing tab found within the
“Microsoft Dynamics AX 2009 Server Configuration” application.

3.  Install a new AOS specifying the new Service Account login details when prompted,     specify not to start service after the installation is completed, this ensures you can install Service Packs and Hotfixes on the new AOS service.

4.  Ensure you install any Service Packs or Hotfixes to your current environments level.  During each install specify not to start service after the installation is complete and ensure you only select to update the AOS.

5.  You may need to add ‘EXECUTE‘ permissions on any additional custom Stored Procedures that you may have in your AX Database for your new account.

6.  Start the new AOS Service and check the application event log to ensure no warnings/errors are reported.

 

Option 2: Changing an Existing AOS Service

If you already have an existing AOS Service you want to configure for Performance Monitoring there are different steps to complete:

1.  Create a new Active Directory Login

      e.g. CONTOSO\PerfMonAosSvc

 

2.  Create a SQL Server Login

USE [master]
CREATE LOGIN [CONTOSO\PerfMonAosSvc] 
FROM WINDOWS 
WITH DEFAULT_DATABASE=[MY_DAX09_DB]

 

3.  Create the Database Login

USE [MY_DAX09_DB]
CREATE USER [CONTOSO\PerfMonAosSvc] 
FOR LOGIN [CONTOSO\PerfMonAosSvc]

 

4.  Grant Database Role Permissions

USE [MY_DAX09_DB]
EXEC sp_addrolemember N'db_ddladmin', N'CONTOSO\PerfMonAosSvc'
EXEC sp_addrolemember N'db_datawriter', N'CONTOSO\PerfMonAosSvc'
EXEC sp_addrolemember N'db_datareader', N'CONTOSO\PerfMonAosSvc'

 

5.  Grant Stored Procedure Permissions

USE [MY_DAX09_DB]
GRANT EXECUTE ON [dbo].[CREATESERVERSESSIONS] 
TO [CONTOSO\PerfMonAosSvc]
GRANT EXECUTE ON [dbo].[CREATEUSERSESSIONS] 
TO [CONTOSO\PerfMonAosSvc]

Note:  You may need to add ‘EXECUTE’ permissions on any additional custom Stored Procedures that you may have in your AX Database.

6.  Add user to ‘Performance Log Users’ group
Performance Log Users Group

This is required so AOS Tracing can be started via the Tracing tab found within the “Microsoft Dynamics AX 2009 Server Configuration” application.

 

7.  Ensure the Service Account has Modify, Read & Execute, List folder contents, Read, Write permissions on the Application Folder

e.g. C:\Program Files\Microsoft Dynamics AX\50\Application

 

8.  Ensure the Service Account has Read, Write permissions on the AOS Log folder

      e.g. C:\Program Files\Microsoft Dynamics AX\50\Server\MY_NEW_AOS_02\Log

9.  Change the AOS service account user in Services to the newly created service account

10. Restart the AOS Service.

 

Please as always try this setup in your Test / Dev environment prior to implementing it in your Production environment to ensure you understand the setup required and that it fits your requirements.

 

Additional Reading:

A great 3 part series on AX Tracing from the ‘Premier Field Engineering’ Team
http://blogs.msdn.com/b/axinthefield/archive/2010/12/28/dynamics-ax-tracing-part-1.aspx
http://blogs.msdn.com/b/axinthefield/archive/2011/03/25/dynamics-ax-tracing-part-2.aspx
http://blogs.msdn.com/b/axinthefield/archive/2011/06/25/dynamics-ax-tracing-part-3.aspx

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.

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)

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 (570 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.

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

Run a Dynamics AX SQL statement as an API Cursor in SSMS

We all know that AX2009 uses cursors extensively – simply executing a SQL statement in SSMS doesn’t always result in the same query plan being used that an AX generated SQL statement would use.

Want to run a query in SQL Management Studio exactly how Dynamics AX 2009 runs the query e.g. as a API Cursor so you can ensure you are looking at the same query plan AX has used then run the query below.

Simply replace the red text below with all the query variables and replace the blue text with your parameters and execute the full statement and voila you can view the exact query plan AX uses to execute the statement.

TIP: If your unsure of the variables to declare in the cursor you can find them by doing a SQL Profiler Trace.

———————————————-
— Normal FFO Cursor
———————————————-

— Script taken from AX Dynamics Performance Analyzer scripts by Rod Hansen (http://code.msdn.com/dynamicsperf)

declare @p1 int
set @p1=NULL
declare @p2 int
set @p2=0
declare @p5 int

— Fast Forward(16)+Parameterized(4096)+AutoFetch(8192)+AutoClose(16384)

set @p5=16+4096+8192+16384
declare @p6 int
set @p6=8193
declare @p7 int

— Number of Rows for AutoFetch.
— This is calculated by Maximum Buffer Size (24K default) / Row Length

set @p7=4
exec sp_cursorprepexec @p1 output,@p2 output,N’@P1 nvarchar(5),@P2 nvarchar(21)‘,N’SELECT A.SALESID,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND (SALESID>@P2))’,@p5 output,@p6 output,@p7 output,N’ceu’,N’SO-100004′
— @p2 contains cursor handle for fetch call
exec sp_cursorfetch @p2,2,1,@p7