·
3 min read

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