Monday, April 27, 2020
Upgrading to Microsoft Dynamics 365 for Finance and Supply Chain Management may be a bumpy road for some when running into different obstacles along the way. But do not fear, we have documented some solutions to help you overcome the most common challenges!
While working on various upgrade projects and helping Dynamics Partners with database migrations, we have encountered a whole host of issues. However, with our extensive knowledge and experience with Microsoft Dynamics 365 Data Upgrades, we can help our Dynamics Partners save precious time and project resources by getting those problems solved quickly.
When performing a data upgrade from Microsoft Dynamics AX 2012 to Dynamics 365, one of the frequent stumbling blocks that customers run into is a database collation change issue. In this article, we will provide context around this collation issue and detail procedures that will help you to avoid it, ultimately saving you time on your Dynamics 365 data upgrades.
Background context for collation issues
You may be aware that Microsoft Dynamics 365 for Finance and Supply Chain Management apps only officially support a single database collation that is SQL_Latin1_General_CP1_CI_AS. While this is not causing any issues for new Dynamics 365 implementations, it does become a hindrance in Dynamics AX upgrade projects (both Dynamics AX 2012 and earlier versions).
Microsoft Dynamics AX installations are usually hosted on a customer’s premises and their Dynamics AX databases are typically on their own servers. With Dynamics AX versions, customers were not restricted by database collation choice, so they typically chose a database collation for Dynamics AX database that best matched their region.
On a very high level, the data upgrade process consists of:
- Taking the existing Dynamics AX 2012 database
- Uploading it to Azure SQ
- Running a set of scripts to transform the data structure
Then, you’re basically done!
This means, that if we take the existing Dynamics AX 2012 database for the data upgrade that has a different collation than is officially supported, we will end up with Dynamics 365 database that is on unsupported collation.
However, is this an issue and will you get any errors when running the Dynamics 365 database with unsupported collation? The answer is: you will be able to successfully run the database upgrade and you will have a functional Dynamics 365 instance with an upgraded database but only up until you try to apply the next service update.
Here is what really happens:
The collation conflict issue occurs while performing full database synchronization, when the database is set differently to the recommended SQL_Latin1_General_CP1_CI_AS. This problem is noticeable in the existing Dynamics 365 for Finance and Supply Chain Management environment when applying a platform update. Specifically, an error occurs while synchronizing the SECURITYROLEDUTYPRIVILEGEEXPLODEDGRAPH_POPULATE stored procedure. The cause of this error is a collation mismatch between two columns in a comparison clause.
Normally, this issue should not occur, as all text columns in the database should be collated with default database collation. However, in this case, there are several columns in the database that have their collation hardcoded to SQL_Latin1_General_CP1_CI_AS . Below are the tables where columns with hardcoded database collation can be observed:
- SECURITYROLE
- SECURITYDUTY
- SECURITYPRIVILEGE
These columns are Dynamics 365 specific and are not coming from Dynamics AX 2012. The potential reason why they have different collation is that when creating the tables, collation is hardcoded in the database synchronization code Dynamics 365 core.
Possible solutions for solving collation issues
The only possible and recommended solution for this problem is to change database collation for your Dynamics AX 2012 database before the database copy is uploaded to Dynamics 365. Any attempts to change the collation on the tables listed above, from hardcoded collation to a non-supported database collation, could end up creating more collation conflicts and uncover a rabbit hole of collation conflicts, the end of which may not be reachable.
There are two possible scenarios when the database collation could be changed during your Dynamics AX 2012 data upgrade process:
- The database collation is changed before migration in the existing Dynamics AX 2012 environment
OR
- The collation is changed during every iteration of data upgrade, including go-live migration.
Option |
|
|
|
|
|
|
|
|
|
|
Database collation change procedure
Change of the database collation is only possible by remodeling the database. This particular procedure would be quite difficult but the AZURE SQL tool - SQLPackage.exe, is here to help! This procedure is partially official and is used by Microsoft engineers.
1. SQL Database exportation to * .bacpac format. The commands are described here.
Example: SqlPackage.exe /a:export /ssn:localhost /sdn:Ax2012 /tf:J:\bacpac\AX2012.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false
2. Database collation change in xml file:
a) Open the received *.bacpac file using archiving software (i.e. 7Zip) and copy model.xml file from the archive.
b) Locate the database collation in xml file and change it to the required one, in our case SQL_Latin1_General_CP1_CI_AS: 
c) Import the bacpac file back into the database server by pointing to the modified xml file. The same SQLPackage tool is used for an import.
A sample command is provided below:
SqlPackage.exe /a:import /sf:"J:\Bacpac\AX2012.bacpac" /tsn:localhost /tdn:AX2012_Fixed /p:CommandTimeout=1200 /ModelFilePath:"C:\Users\Admin9b3668c608\Downloads\model.xml"
We hope that this blog has provided solutions on how you can avoid the collation issue and helps to ensure your migration to Microsoft Dynamics 365 is even more successful and efficient!
If you still have AX2012 or older version and are considering how to migrate to the latest Dynamics 365 F&SCM version, 1ClickFactory offers a Best Path Assessment - Dynamics AX Solution upgrade analysis service which provides an upgrade proposal with a fixed price and clear technical scope. Click here find out more about the Best Path Assessment service or contact us at service@1clickfactory.com.