Background
Several months I commissioned an environment audit from an external company. As the sole Dynamics administrator I had been responsible for designing and maintaining our environment for the last 18 months. It was time to have a third party opinion on how our environment was doing.

One of the most critical issues discovered was that the collation differed between our Model and Business databases.

This TechNet article clearly states that the collation between these databases must match.

.

In my case the collation's were as follows:

Model SQL_Latin1_General_CP1_CI_AS
Business Latin1_General_CI_AS
It is worth noting that SQL_Latin1_General_CP1_CI_AS is the default collation for North American SQL installs, which explains where it came from. It is now obvious that in one of our pre-production environments which was used to hydrate production had the SQL collation set incorrectly.

Latin1_General_CI_AS is the generally recommended collation for AX. I cannot find any good Microsoft sources on this but there are many blog posts on the subject. This support article is the closest I can find on a general recommendation to use Latin1_General_CI_AS.

The Fix
In my case since the business database was already in the desired collation there was no need to worry about it.

The first step is to determine your SQL servers default collation. To do this use the T-SQL below:
SELECT CONVERT (varchar, SERVERPROPERTY('collation'));

In my case the default collation was Latin1_General_CI_AS which is good. If this setting was incorrect then all of the system database would also have incorrect collation. In this case you would be forced to change the server collation as detailed here, it would not be pleasant.

Since my default collation was correct all new databases created on the server would be created with the correct collation. With this in mind all I had to do was to delete and replace the modelstore database, no problem.

If you are not already using the Microsoft Dynamics AX PowerShell to do most of your administration task, you should be. One of the cmdlets availible is Initialize-AXModelstore. This command is able to work with the modelstore database, including creating one. You should already be using this cmdlet to deploy your modelstores to your production environment as shown in this blog.

Armed with the above my basic process was:

  1. Shutdown AX environment including Enterprise Portal, Management Reporter, and all integrations
  2. Copy-Only Full Back-up of databases with verify
  3. Export Modelstore from AX PowerShell , example 'Export-AXModelStore -Verbose -File "\\Path\To\BackUp\Location"'
  4. Drop the modelstore database from SQL.
    1. DROP DATABASE [ModelDbNamee]
    2. I would advise against closing existing connection when performing this step. If there are still open connections at this point something has gone wrong.
  5. Use AX PowerShell Initialize-AXModelStore to create a new model database, example 'Initialize-AXModelStore -AOSACCOUNT:TCII\SVC_AXAOS -CreateDB -Database -Server
  6. Use AX Powershell Import-AXModelstore to restore the modelstore that was exported in step 3
  7. Start an AOS
  8. Run a Table Synchronization , this step isn't strictly required but it is a good check. If you get an error(that you don't normally get from your sync's) from table sync at this point something has gone wrong.
  9. Start and verify the environment's functionality

The environment should now be fully functional and using matching collation.


Final Thoughts

In my particular case the collation fix was relatively easy because the server collation was already what was desired. At this point there doesn't appear to be any impact one way or another since fixing this. I am not sure what practical effects this issue has on our environment.

The most important lesson here is to be aware of the collation your various environments are using. It is very easy to miss and difficult to fix.

Finally if your SQL Server default collation is incorrect you will not be able to use Initialize-AXModelStore because it will just create a database with the server's default collation. You can work around this by creating the model database on another SQL server and restoring that into your production SQL. However then you will have a problem with your system databases collation being incorrect.

If your business database has the incorrect collation the fix is much more complicated. You will need to find a good DBA to write a script which will change the collation.