Enabling index hints is deprecated in AX 2012 … almost

Sending index hints to the SQL server from AX has been around for a long time and often it has not done any good since the SQL server is pretty good at picking the best way it self.

So when it ended up as a parameter in the AOS server configuration in AX 2009 and then removed from the configuration in AX 2012 we seemed clear from the trouble it could cause. Microsoft stated that it was deprecated with AX 2012 and no longer available …

So it seemed a bit strange that the SQL server apparently received the information on a server I was working on recently.

While going through about all possible stuff to locate why it was acting like the non-existing index hint flag was enabled, I was going through the registration database to compare it against an AOS we knew was working as expected. And there it was … the registration key called “hint”.

I did a bit of research and I was not the only one struggling with this. As it appears there are these values to choose from :

Empty = Index hints are enabled and LTRIM (yes, it is there too) is disabled.

0 = Both index hints and LTRIM are disabled. This has to be the recommended setting.

1 = The same as empty. Does that make sense? No, not really. Anyways …

2 = Index hints are disabled and LTRIM is enabled.

3 = Both index hints AND LTRIM are enabled

 

And just for refreshing the memory: The location of the registration keys are

HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\services\Dynamics Server\6.0

and in here find the right AOS and configuration.

Troubleshooting AX/SSRS

The purpose of this document is to list a troubleshoot path to follow when reports cannot be printed from AX 2012.

The contents are not fully covering all options available but points out a toolbox for handling often seen issues.

Troubleshooting

Is it all reports not printing?

Try printing one of the most simple reports available. I usually use the Departments report found in “Organization administration / Reports / Base data / Departments”. If that one prints successfully it is probably not the SSRS causing the problem.

Is SSRS set up correct within AX?

AX has a very flexible setup regarding SSRS. Each AOS has – and must have – its own relation to an SSRS. All AOS’s can point towards the same SSRS but it is also possible to address the reports from each AOS to its own SSRS instance. One AOS can on top this have multiple relations to different SSRS instances. However, only one relation can be default and active.
So how do we make sure all this works?
1. Make sure that all AOS servers have a relation (with default mark set) to an SSRS instance in the SSRS list (Appendix 1 – Locating the SSRS names)
2. Validate the relations using the “Validate settings” button.
3. Verify that the specified URLs are valid.

Is SSRS set up correct regarding connection to AX?

With AX pointing towards the right SSRS instance we need to make sure that the SSRS instance points towards a valid AOS. With only one SSRS instance on the server it – by default – looks up the default client configuration on the server to access AX. Having multiple SSRS instances on one server requires each instance to have an configuration file with the name Microsoft.Dynamics.AX.ReportConfiguration.axc. Make sure that this configuration file is valid. For instance by opening a client with it if possible. Also make sure that the WCF part of the file is fully updated. To rebuild the WCF part follow the description in the section Rebuild the WCF part of the AX configuration for the SSRS.
Another way to test that multiple instances points towards the correct instances is to create eg. a department called “Development” in a development environment and one called “Test” in the test environment and then verify that these are shown when printing from the relevant AX environments or the SSRS report manger site (see description of how to print from outside the AX client).

Does it print from outside the AX client?

SSRS offers a web frontend allowing us to test – at least some – reports from outside AX pretty easily. To do so you need to find the URL address to the report manager site. This is listed in the Report server list found within AX. To find this list please read the description in Appendix 1 – locating the SSRS names.
From here navigate to the URL specified in the field Report Manager URL.
If this does not open up succesfully go directly to restarting the SSRS service.
aIf it opens up successfully click on the DynamicsAX folder and use the Search field in the upper right corner to locate the report design HcmDepartmentReport.DepartmentDesign

Printing reports from the SSRS Report Manager

Printing reports from the SSRS Report Manager

Click on the report to show it. If a report is shown the report server and the connection works.
If not printed successfully you might be presented with an error description pointing you in a useful direction.
Rebuild the WCF part of the AX configuration for the SSRS
The WCF part of the AX configuration describes the service ports available. It can be usefull to rebuild these in some circumstances eventhough you do not think they have been altered. To update this open the Microsoft Dynamics AX Client Configuration on the server running the SSRS. Click Manage and “Open a configuration file…”. Navigate to C:\Program Files\Microsoft SQL Server\MSRS11.<Name of the relevant instance>\Reporting Services\ReportServer\bin and select the file Microsoft.Dynamics.AX.ReportConfiguration.axc.

Rebuild the WCF part of the configuration file

Rebuild the WCF part of the configuration file

On the Connection tab click the Refresh Configuration button and wait …

Waiting for the WCF to be rebuild.

Waiting for the WCF to be rebuild.

When completed click Apply and “Manage / Save configuration file”.
After saving the file restart the SSRS-service.

Appendix 1

Locating the SSRS names

The name of the server can be found from within AX by looking up the reporting server(s) listed in “System administration / Setup / Business intelligence / Report servers”.
Notice, that the list can contain multiple SSRS instances and not all of them are related to the environment you are trying to fix.

Restart the SSRS-service

This is a fairly quick service when it comes to restarting so you will not be troubling users for that many seconds.
Log on to the server running SSRS. From here locate the service “SQL Server Reporting Services (<Name of the relevant instance>)” and restart it.

Change installation folder for the AOS server

Working for a company that handles development of multiple modules/add-ons to AX we end up having a lot of AOS-services on one server. We do that to separate development and release environments and keeping modules isolated from each other.

The problem that we sometimes end up with is that the C-drive reaches max and we need to install some of the AOS services on a different drive.

Going through the normal setup routine does not offer any options of specifying the installation folder. So we need to do two modifications before it works.

First step is to open the regedit.exe and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Dynamics\6.0\Setup\Components\InstallDir and change the value to where you would like the AOS to be installed.

Second step is to make sure that the Server Setup files is present in the new directory. To do this simply copy the folder C:\Program Files\Microsoft Dynamics AX\60\SetupSupport to the new drive so you have a D:\Program Files\Microsoft Dynamics AX\60\SetupSupport or wherever you want the AOS servers installed.

Run the AX setup and your AOS is now installed on your new drive.

Roaming AUC files – new feature in R3

The quest for increasing performance in AX appears never-ending. With AX 2012 R3 Microsoft has added a new tool allowing us to make the AUC files global/shared.

Basic AUC file info:

AUC files are cache files made by the AX client to improve load time. Forms we have already loaded once could be loaded again from the cache without having to make a trip to the AOS for the bits and bytes. All stored in c:\users\<username>\AppData\local and then by nature user specific.

So the main challenge so far has been that user A opens the sales order form the first time, waits, caches the element and can open the form faster the second time and the same story for user B, C and all the others.

In AX 2012 R3 a new feature have been added to the client configuration with the title “Use roaming user profile to store client-side cache”. That basically says it all. We can now specify a global directory for the cache files allowing users to share the cache so when user A has opened the form user B can utilise the cache.

Still no fun for user A, but user B is in luck …

AOS cannot start after extending string length on EDT

All though AX 2012 is constantly moving the boundaries for what we can do we ended up hitting the roof on field/record sizes the other day.

After heavily increasing the string length on an EDT and a crashed synchronisation the AOS was unable to start up again. The error message received in the Event log was this:

Object Server 01: Unexpected situation
More Information: Total field length cannot exceed 64K. Please re-factor your table <tablename>

The obvious solution is to decrease the length of the EDT to get below the 64K limit.

But – to cut this short –  we now have an AOS that cannot start due to an error we need the AOS running to fix. Well, we knew that we had a backup taken a couple of hours ago giving us a loss of changes that we could deal with if it were. However, it was worth giving it a shot trying to fix this.

The solution was quite simple. We found a similar versioned environment and did the following:

  1. Create a new model
  2. Make a small customisation to the EDT giving us the problems putting it in the new model
  3. Export the model
  4. Import the model in the model database of the AOS that will not start
  5. Start the AOS
  6. Skip the models changed dialog and do a full synchronisation from the AOT

Voila! We are back on track with no loss except for the time spent doing the above thanks to the model concept and AXUtil.

Getting from a SPID to an AX user id and back

Earlier versions of AX offered information about the SPID from within AX and the Online Users form. In AX 2012 it is a bit more tricky and requires a little tweaking of the AOS configuration.

What you need to do is to add a key in the registration database and it goes like this:

1. Run regedit.exe

2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Dynamics Server\6.0\<ServerNumber>\<MyConfigurationName>. If you have not changed the configuration of the AOS it is named “Original (installed configuration)”.

2. Add a key of the type String Value, name it connectioncontext and give it the value of 1

3. Restart your AOS

If you have multiple AOS-instances you need to repeat the above for each AOS server.

Now we can use this statement to get a list of active queries:

SELECT cast(context_info AS VARCHAR(128)) AS [Connection info] , *
FROM sys.dm_exec_sessions
WHERE program_name LIKE '%Dynamics%'
	AND host_name LIKE '<AOSNameMask>'

If your AOS servers are named MyAOS01, MyAOS02, MyAOS03 and so forth put in MyAOS% as the <AOSNameMask>.

Now, the result is in the first column a variety of information including the AX user id and the AX session id. This can be matched directly to the online users overview within AX.

You can go the other way around by adding this

AND context_info like ' <AXUserId>%'

where <AXUserId> is the relevant id of the user in AX. Notice the initial blank in the range value.