How to change the collation on a Microsoft Dynamics NAV 2013 R2 database.


Some significant changes have been introduced to the internal database structure for Microsoft Dynamics NAV 2013 R2 in order to support the powerful new Multitenancy functionality. These changes impact on how the NAV application manages a collation change whether or not you are using Multitenancy.

At first glance, changing the NAV database collation seems to be the same old process as before. It can be achieved by following the steps below:

1. Shut down the NAV middle tier service and (optionally) take a backup of the database.

2. Open the Dynamics NAV Development environment

3. Go to the “File” menu option and then “Database” -> “Alter”.

4. In the “Options” tab, click on the “Single user” check box in order to change the database into single user mode. If you get the following message you need to remove other users from the database before proceeding:

clip_image001

5. Once the database is in single user mode, return to the “Alter Database” dialog box you were in earlier and select the “Collation” tab and modify the database collation as desired. This will take a few moments and a progress bar will indicate how the process is performing. You will soon see the following message to confirm the change:

clip_image002

6. As indicated in the above message, you need to return to the “Options” tab in the “Alter Database” dialog in order to uncheck the “Single User” check box.

7. At this stage you can (optionally) take a backup of the database and then restart the NAV middle tier service.

After following the above process you will have successfully changed the Dynamics NAV database collation – job done! The information below may be of interest if you want to understand what is happening behind the scenes as the collation change is applied in the SQL Server database but it is not required knowledge and no further manual steps are required in order to complete the collation change operation.

There are some significant changes to the way the collation changes you have configured will be implemented at the SQL Server level compared with earlier versions of Dynamics NAV. If you use SQL Server Management Studio (SSMS) to check the NAV database collation (right click on the database and select “Properties”) you will see the old collation and not the one you have just applied via the NAV development environment. This can be confusing because earlier versions of NAV would update the collation on the database level. No more. With NAV 2013 R2 we alter the collation at the field level for all NAV tables. The Multitenancy design requires that we do *not* change the collation at the database level.

If you check one of the character (nvarchar) fields in a NAV table in SSMS (e.g. right click on the field and select “Properties”), you may notice that the old collation is shown. This can be for several reasons. The most likely reason is because the collation change for that field has not been applied yet. With earlier versions of Dynamics NAV the collation changes were applied synchronously at the time you applied the change in the Dynamics NAV development environment and were therefore visible immediately at the SQL Server level. No more. With NAV 2013 R2 the changes are applied at the SQL database level asynchronously by the Dynamics NAV middle tier in a process referred to as “Schema Synchronization”. This process will normally run automatically in the background as soon as a Windows Client attempts to access the database. However, if you like you can force a Schema Synchronization manually at any time via Powershell. If you have this requirement then start the Dynamics NAV Administration Shell with Administrator rights and execute: Sync-NAVTenant <NAV Instance Name>

Sometimes, even after schema synchronization has run successfully you might still notice that a field property in SSMS is showing the old collation. This can be due to the fact that the schema synchronization can take a few minutes to complete on a large database. If you check again later you will probably see the new collation has been applied. This delay does not affect users however because at the point a user touches a table the middle tier will apply any pending schema synchronization for that table immediately – just in time for the user to access the table. If you want to force the immediate schema synchronization for a specific table then go to the NAV development environment and “Run” the table from Object designer. This will open the table contents in a list page in the Windows client and thereby force the schema change for that specific table.

Even after the schema synchronization has completed successfully and all schema updates are fully applied you might still notice that some fields are using the original collation. This may be due to the fact that Dynamics NAV will never alter the collation for fields which have been defined with the NAV data type set to dateformula.

Gerard Conroy
Dynamics NAV Support EMEA

Comments (8)

  1. Michael Kaluza says:

    Hello Gerard,

    thank you for this Information . I have a quenstion, how does the collation Change works if there are dependencies on the field like indexs or views. NAV 2013 doesn't Change the collation on this field. Is it in NAV 2013 R2 the same behaviour?

    King Regards

    Michael

  2. Gerard Conroy says:

    Hello Michael,

    Thanks for your question. When you change the collation using the Dynamics NAV Development environment it should automatically take care of these kind of dependencies and update the collation as expected.  

    Best regards,

    Gerard.

  3. Miguel Oliveira says:

    Hello Gerard,

    I have a customer that has integrations with Biztalk ans is asking me to find a way to change the Database collation anyway.

    Although I understand that with the collation change at the field level there shoul be no problem, is there a way to change the collation at the database level?

    Best regards,

    Miguel Oliveira

  4. Gerard Conroy says:

    Hello Miguel,

    If you change the Dynamics NAV database collation in SQL Server directly (outside of the NAV Development Environment) there is the potential for unexpected results, e.g. in a multitenant environment with multiple databases or with the materialized views created for SIFT indexes. Therefore, we do not recommend this. However, if you want to try it out in a test environment you may be able to get it to work without any problems for a specific customer scenario. As always, make sure you have a good backup before you start 🙂

    Best regards,

    Gerard.

  5. QQ says:

    Hello,

    in one of our customer databases I've detected that queries generated by NAV against tables include CONVERT_IMPLICIT. I've noticed that database level, and some system table collation does not match the actual field collation for data tables.

    How can I fix this?

  6. The addition of CONVERT_IMPLICIT can be caused by the fact that the database collation is different to the collation for fields used in the relevant query. If this is causing performance problems then you can resolve it by creating a new empty database with the desired collation, use the NAVDATA feature to export the data from the current database and then import the data into the new database. After that the CONVERT_IMPLICIT should no longer happen when using the new database.

    For more details about the NAVDATA import/export feature see: https://blogs.msdn.microsoft.com/nav/2014/06/19/importing-and-exporting-data-in-microsoft-dynamics-nav-2013-r2-cu-8/

    Best regards,

    Gerard.

  7. Sebastian Sidor says:

    Hi! Nice article! Could you achieve the same thing if you make a nav backup (powershell), export all data etc, create a new database with the desired collation and restore the data in to that database (with powershell)?

    1. Hi Sebastian. Unfortunately, using the Poweshell cmdlets to export/import the data (e.g. Export-NAVData and Import-NAVData) will not change the collation at the column/field level so the NAV Development environment is still the only fully support way to change the collation for a NAV database.