Skip to main content
Dynamics 365
·
17 min read

Best Practices Tips and Tricks for Upgrading to Dynamics NAV 2013 R2 or Dynamics NAV 2015 Part 2

In an earlier blog post, we started to mention some tips and tricks to help you plan your upgrade to a newer version of Microsoft Dynamics NAV . In this post, we would like to share with you some of the known issues we stumbled over in the past couple of months or years, and what can you do in case you get into any of these known situations.

Today, it’s mainly not about the solutions we give; sometimes it works at first try, sometimes you need to take another approach. We want to highlight how you should think about the different situations you get into, and what you should  check for. So, based on our experience, we list here some of the main points that you need to understand carefully while facing some issues during your upgrade.

Synchronization Issues

As you know, with Microsoft Dynamics NAV 2013 R2 , we introduced a number of changes to the product comapared to Dynamics NAV 2013 with the new support for multitenant deployments. You can read something about that here:

Based on this new architecture, compiling and synchronizing objects to SQL Server becomes more sophisticated than before. A well-known issue is that when you upgrade to Dynamics NAV 2013 R2 / NAV 2015 and open the Windows client for the first time, you would not be able to start the client and you would get the following error:
‘Invalid Column name’ or ‘column xxx already exist’ or ‘Column xxx does not exist’.

To explain this in a better way, we published an explanation of the new design and how to work on it to avoid having this error:

http://blogs.msdn.com/b/nav/archive/2014/03/27/table-synchronization-paradigm-in-microsoft-dynamics-nav-2013-r2.aspx

Read that blog post before you start on step 11 in the upgrade from Dynamics NAV 2009 SP1/R2 to Dynamics NAV 2013 R2 , or step 7 in the upgrade from Dynamics NAV 2009 SP1/R2 to Dynamics NAV 2015. At that point, you are getting ready to import an .FOB file with all your objects. Based on the size of the database and the amount of customizations, you might want to export the objects to more to than one .FOB file so that you import the objects in a couple of separate rounds to simplify the first synchronization process. Here are the steps to follow:

  1. Start with a clean database in case you already had sync errors, restored the backup before you tried to start the Windows client.
  2. Always work on the latest build possible because we regularly solve lots of issues related to sync on later builds. Proceeding with an older build of Dynamics NAV is not recommended. See following links to identify the latest build for your version of Dynamics NAV:
  3. When you know the KB number (i.e. cumulative update ID number from the above sites) for the build you want, you can use the following link to request the build from Microsoft (just replace the KB number kbnum in the URL):

    https://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=3035983&kbln=en-us&sd=mbs

  4. Go to the configuration of the service and change ‘SQLCommandTimeout’ to a big value such as 10 hours. The default is 30 minutes, which is too small a window.

    Also do not forget to check if your remote query timeout (s) parameter in your SQL Server (In case it was changed before to any very low value).

  5. Restart the service.
  6. Import the .FOB with all your objects.

    If the .FOB file contains too many objects, you can create one .FOB file with table objects and another .FOB file with the rest of the objects. You would then import first the tables .FOB and synchronize the database schema, and then you import the second .FOB and sync. Alternatively, simply do not import all objects at once.

  7. Make sure all objects are compiled on whatever build number you are using.
  8. Do no start the Windows client (RTC) as this could cause a synchronization problem. Also, in Dynamics NAV 2015, do not start the sync inside the development client (although it is available). We recommend running the first sync ever using the PowerShell command Sync-NAVTenant:
    1. Go to start menu, search for Start ‘Power Shell ISE ‘as this is preferable to the Dynamics NAV Administration Shell for this purpose. Always run the ISE as administrator.
    2. Run the below command to avoid restrictions:

      Set-ExecutionPolicy Unrestricted

      A message will show up, confirm by pressing Yes.

    3. Run the following command to import the Dynamics NAV administration tools (amend path if necessary):

      Import-Module “C:\Program Files\Microsoft Dynamics NAV\80\Service\NavAdminTool.ps1”

    4. Run the Sync command ‘Sync-NAVTenant’ for the relevant instance. For Dynamics NAV 2015, see following resources for more parameter you can use for the sync: https://msdn.microsoft.com/en-us/library/dn466418(v=nav.80).aspx

      .. or run following command in PowerShell: get-help Sync-NAVTenant -online

    5. Let it run as long as it takes and do not try to interrupt it even if it takes hours long, till either PowerShell is ready for another command again, which means the sync did run properly, which means you can start your Windows client without issues, then carry on following the upgrade blog, or the upgrade document for Dynamics NAV 2015.

      In case the upgrade is to Dynamics NAV 2015, it is much easier, because it includes a status bar to show you the progress of the sync.

    6. If any errors occur, it will be written in PowerShell, check please the event viewer of the service tier again, for Dynamics NAV 2015 you can always run the command:

      Get-NAVDataUpgrade InstanceName -ErrorOnly

Having all the above information in mind should make you fit in case of having sync. Issues while upgrading to avoid any unpleasant surprises.

Time estimated for the sync process, is always related to the amount of objects synchronized at once vs the amount of resources you do have on your SQL Server, note that you can be having lots of resources on the SQL Server machine, but you might have not configured it properly to get full use of these resources.

Collation Issues

Before we start keep in mind that starting from Dynamics NAV 2013, the supported collations are only Windows collations, and this is for better support to the Unicode compatibility, so if you had a database on older version using SQL Collation, it will be changed automatically while upgrading. This can have an Impact on the Upgrade and can cause some known errors:

  • Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object.
  • It might be not really duplicate, but because the windows collation cannot interpret all characters that the SQL Collation could interpret, it might seem to be the same.
  • Here you have to make sure that all your primary keys are not depending on the used SQL Collation in older version.

Another type of error, while trying to start step 2 to convert data is:

  • SQL error during migration, usually in the following form:

    The following SQL error is not expected

    The data type nvarchar cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable.

    Statement(s) could not be prepared.

    This type of errors can occur if depends on whether you are using case sensitive or case insensitive collation, and if you have objects having different collations than other objects.

    Usually it occurs in similar statements like the one below:

    declare @p1 intset @p1=-1exec sp_prepexec @p1 output,N’@0 nvarchar(1),@1 nvarchar(1),@2 nvarchar(1),@3 nvarchar(1),@4 nvarchar(1),@5 nvarchar(1),@6 nvarchar(1),@7 nvarchar(1),@8 nvarchar(1),@9 nvarchar(30),@10 int’,N’SELECT TOP 1 * FROM (    SELECT     CAST (0 AS timestamp) AS [Timestamp],        [Company Name],        [ID] AS [Table No_],        [Name] AS [Table Name],        CAST (CASE WHEN [Row Count] > 2147483647 THEN -1 ELSE [Row Count] END AS int) AS [No_ of Records],        CASE [Row Count] WHEN 0 THEN 0.0 ELSE [Size] * 1024.0 / [Row Count] END AS [Record Size],        CAST (CASE WHEN [Size] > 2147483647 THEN -1 ELSE [Size] END AS int) AS [Size (KB)] FROM (  SELECT            [Company Name],            [ID],            [Name],            SUM(row_count) AS [Row Count],            SUM(reserved_page_count) * 8 AS [Size]  FROM (   SELECT    C.[Name] AS [Company Name],    O.[Object ID] AS [ID],    O.[Name],    C.[Name] + ”$” + O.[Name] AS [Company Qualified Name]   FROM [Sprügel].[dbo].[Object Metadata Snapshot] AS O   CROSS JOIN [Sprügel].[dbo].[Company] AS C   WHERE O.[Data Per Company] = 1 AND O.[Object Type] = 1   UNION   SELECT    ”” AS [Company Name],    O.[Object ID] AS [ID],    O.[Name],    O.[Name] AS [Company Qualified Name]   FROM [Sprügel].[dbo].[Object Metadata Snapshot] AS O   WHERE O.[Data Per Company] = 0 AND O.[Object Type] = 1        ) AS [O]        INNER LOOP JOIN [Sprügel].[sys].[dm_db_partition_stats]                ON object_id = OBJECT_ID(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([Company Qualified Name], @1, @0), @2, @0), @3, @0), @4, @0), @5, @0), @6, @0), @7, @0), @8, @0)) AND (index_id < 2 OR index_id = 255)  GROUP BY [Company Name], [ID], [Name]    ) AS [Core Query]) AS [Projected Query] WHERE (“Company Name”=@9 AND “Table No_”=@10) ORDER BY “Company Name” ASC,”Table No_” ASC’,@0=N’_’,@1=N’.’,@2=N'”‘,@3=N’\’,@4=N’/’,@5=N””,@6=N’%’,@7=N’]’,@8=N'[‘,@9=N”,@10=0select @p1

    This Union here is causing the issue based on the used collation because it is not matching what SQL server expects.

  • Usually changing the collation solves the situation, as mentioned here: http://www.mibuso.com/forum/viewtopic.php?f=32&t=59489&start=0

    But you have to be careful while doing it manually.

It would be also good to understand what does the collation change exactly do, especially if you are changing the collation of a huge database:

  • The collation change process is simply running through every character based NAV field in the DB and issuing the following statement:

    ALTER TABLE “<table name>” ALTER COLUMN “<column name>” VARCHAR(xx) NOT NULL

  • And this involves every character field in every table, so especially for huge database, you need to plan the time for this properly, in the section related to Performance we will be back to this in more details.
  • In case of flow fields, in the conversion process to Unicode, The conversion is going through all these records and set the BLOB fields to NULL value.

    (Loop cursor type of data upgrade), and after it finishes, the data would be returned, as you would be saved temporarily.

  • So the more FlowFields you have, the more performance issues you might experience.

In huge databases, it is always recommended to do the change collation as a separate step in Dynamics NAV 2009 Classic Client before you even start the upgrade.

  • This will give you more control on the collation change yourself.
  • You can see if it works well, or if you might need to change the collation of some objects manually on SQL Server.
  • You can see if the database has performance issues or not, and if it is related to the collation change, or rather to the Unicode conversion.

If you change the collation for a Dynamics NAV database using the NAV Development environment on versions 2013 R2 or later you might notice some subtle differences compared to earlier versions. See the following blog for more details: http://blogs.msdn.com/b/nav/archive/2014/12/04/how-to-change-the-collation-on-a-dynamics-nav-2013-r2-database.aspx

Data Conversion Issues

Data conversion is a very sophisticated process, and takes into consideration lots of are factors like the design of the tables and the relations of these tables so as the integrity of the indexes and the collation before working on converting the data itself.

If you have customizations that do not take care of all these dependencies you might get an issue while converting the data.

Let us look at an example:

  • It can happen that when the conversion starts conversion of object metadata the following error is received: The following ODBC error occurred: Error: [Microsoft][SQL Server Native Client 10.0] Unspecified error occurred on SQL Server. Connection may have been terminated by the server. State ID: HY000.
  • The main reason here might be some tables / any other objects in the database that do not compile because of some functions that are missing in the tables, or that do not maintain the design or the structure of other objects. For example when changes have been made to the tables directly in SQL.
  • Here again we mention that the database on the original version before the upgrade has to be checked to avoid these issues.
  • For a possible workaround here make a NAV backup of the NAV 2009 database and restore the backup into a new database and you will be able to convert the database.

Another example is a typically known error:

  • The supplied Field name ‘’ does not exist in the ‘’ table.
  • Some fields like field number 1300 in table 112 are automatically added while running step number 11 in the upgrade ‘see the blog mentioned above’.
  • In some cases if an Issue causes this field, or any other fields not to be created, the data conversion will not run and will look for these fields and return errors.
  • Carry on the step again and make sure that the fields are added and synchronized to SQL properly as explained in the previous points.

Error Code 85132273 in Dynamics NAV 2013 R2

While upgrading and in some steps while compiling either system tables or customized tables, you can get an error such as this one:

  • ERROR CODE : 85132273
    • This error code is a PRIMARY KEY violation. This occurs due to different reasons, it can be that on the older versions there were some special characters in the primary keys. In this case, looking for the key causing the issue and correcting it might be the best way.
    • Starting from Update Rollup 9 this error code has been handled to show friendlier message to point to where the error is. It can be that the names of some fields in table are depending on some other ones that are not synchronized yet. To work against this, we wrote another blog post with more info that can help against this:
    • http://blogs.msdn.com/b/nav/archive/2014/07/04/synchronize-metadata-please.aspx
    • Sometimes the same error can be because of not having the system tables compiled before imported the new fob. 

However, sometimes you get another error, even though you have synchronized all objects :

Table Index Issues

As we mentioned before the upgrade procedure takes many factors into consideration. One of these factors is the indexes for the tables.

  • It is often the case that while working on the upgrade you cannot go further because of the following:
      • Wrong or missing indexes.
      • Indexes that show up as null indexes.
      • Indexes that are amended manually.
    • The best way to figure this out the database check tool.
    • In KB 2963997 you find the data base check tool where you can check the database for any internal metadata inconsistencies in the Dynamics NAV database.  We recommend running the command line tool and routing the output to a text file. You can then search the text file for any occurrences of “Error:” as this is the prefix used for any error messages. 
  • In some other cases the Null indexes are coming from older bugs in older versions.
    An example of this is as following:
    • After upgrade from Dynamics NAV 2013 R2 Rollup 2 to Rollup 6 you get this error – which was coming with Rollup 5 and should be solved with Rollup 6!
    • “The server [server name] was unable to process the request. Close the application and start it again” error message when you start the Windows client after converting the database to build 36281.
    • System.Data.SqlTypes.SqlNullValueException

      Message: the value of the SQL Server index cannot be null.

    • A good workaround for this would be by restoring a .fbk backup on another newly created database.
  • In some other example the statistics of some tables can have no index.
  • The tool mentioned above will help you finding it out.
  • Deleting this statistics would resolved this, because you are not allowed to have a statistic with a missing index.

Upgrade Performance

The performance of the upgrade might vary based on the size of the database and the size of the customizations,  During the upgrade, there are 3 procedures that might be the bottleneck especially while upgrading huge databases:

  • Changing the collation to windows collation while opening the database the first time with the NAV 2013 Windows client.
  • The Unicode conversion step.
  • While Importing FOB the first time in NAV 2013 R2 / NAV 2015.
  • While Synchronize the Imported FOB.
  • While transferring the Data in NAV 2013 R2 / NAV 2015. 

Of course depending on the situation of the database, make sure before you start upgrading that you had maintenance plans running on the older database, and that all indexes and statistics are updated gradually  so that the database does not have a general performance issue

  • Check if the database is not having any issue, simply by running dbcc checkdb against the database in SQL server.
  • Fob Importing might take a lot of times for huge FOBs, here we have issued more than one hot fix to help against this, be sure you are on latest build (see point #2 on page 14 of this document for details about how to access the latest build for your version of Dynamics NAV
  • In case the number of customizations is big and the database is huge, do not import one fob with all Objects, divide the FOB into more than one package as mentioned previously in this document.
  • Take care you are running the upgrade on a adequately resourced SQL Machine that is properly configured to get the use of all possible resources (there is more than one sql configuration document for Dynamics NAV on Partner source).
  • Be aware that if the SQL Machine is too weak, and the process of the first sync would take more than 6 hours, it is likely that SQL server will reject the process with a timeout.
  • In case any sync process did not run till the end, be sure that a rollback will be running, do not re-try to run the sync or worse to restart the service before it finishes.
  • You can monitor such a rollback with the following command:
    • select percent_complete, * from sys.dm_exec_requests  
  • The time of the import and the sync is highly depending on the number of companies in the database, so if you have any unused companies, or demo companies inside the database, we highly advise to remove it before starting the upgrade.
  • Some partner do run the upgrade only on one company, and then later on create the other companies and use some own customized SQL Query from other upgraded databases containing the data ‘a database for each company for upgrade sake’, it might sound complicated, but it runs much faster than having it all in one box.
  • Remove any unneeded companies from the database to avoid any unnecessary overhead.
  • Remove any unneeded flow fields, as we said before while changing the collation NAV works on every flow field on its own.
  • Make sure there is plenty of SQL Server log space available as the upgrade process can be very demanding on log space. You can reduce the SQL log size for the NAV database after the upgrade process is complete.
  • For huge databases, we suggest running the change collation as a separate step in 2009.
  • In that case, I would even try to change the collation of the top 5 or top 10 tables manually on SQL Server .‘The tables having most records, especially if we are talking about more than 10 million records per table’.
  • Get rid of all not needed indexes, scan especially the huge tables, and remove any unneeded ‘maintainsqlIndex’.
  • Make sure you are running on a well configured SQL Server with enough resources, if you are not sure, communicate with Microsoft support to ask for their opinion about your configuration.

System Tables with Non-English Names

In the oldest versions of Dynamics NAV, you could translate the columns in system tables to a language other than English. Starting with version 3.0, we adviced heavily against this, and versions later than Dynamics NAV 2013 R2 require that all columns in all system tables are in English. As a result, if you try to open a database with non-English system tables in Dynamics NAV 2013 R2  or Dynamics NAV 2015, you will see an error, saying that one or more columns do not exist. 

if you are upgrading a database where the system tables are translated, do the following:

  • Make sure you use the right version of the .ETX and .STX files.
  • Make sure that all objects where compiled in a development environment with the right .ETX and .STX files.

You can verify that you are running in the correct environment with English (US) as the base language by opening the ndo$dbproperty table in SQL Server Management Studio and verifying that, in the Identifiers column, the word Object is written exactly as that and not in any other way.

The example above is only one of the possibilities you might see in case if using other languages than English in naming system columns.

Company Names in Dynamics NAV 2015

In more than one situation, you might encounter errors related to the naming conversion of your companies in Dynamics NAV.This is usually not an issue in Dynamics NAV 2015 or any older version; rather it is related to the upgrade routine itself.

If a company name includes a special character, or it starts with a number, you might get an error: ‘Incorrect Syntax near…’

  • The issue with having this error because of having a digit at the beginning of the company name was resolved in Cumulative Update 3.
  • But still if you are having any other special character ’ in the company name, you might get the same error. In this context, special characters include the following: [ ~ @ # $ % & * ( ) . ! % – + / = ? ]
  • An easy solution for this is to rename the company before starting the upgrade. After the upgrade is successfully finished, you can re-rename the company again.

We warmly recommend that you maintain a good naming convention, and try to avoid any special characters in company names.

Upgrade Codeunits and Data Upgrade in Dynamics NAV 2015

Dynamics NAV 2015 introduces the concept of upgrade codeunits. We worked on the way schema synchronization and data upgrade works in Dynamics NAV 2015, because some of you experienced problems with the way database synchronization worked when we first launched Dynamics NAV 2013 R2.

Essentially, when you introduce changes to the database schema in Dynamics NAV 2015, Dynamics NAV will be always checking if these changes are destructive or not, and if there is a fear that these change might lead to some data being deleted (such as if you are dropping a table column so that the contents of that column will be deleted). If such as destructive change is detected, you will be prompted to handle the situation using upgrade codeunits.

This was explained on the community blogs more than once, but we suggest that you watch this video to understand what is really going on: 

How Do I: Synchronize Database Schema Using Upgrade Codeunits in Microsoft Dynamics NAV 2015

On the other hand, if you think that these changes are not going to cause issues, you can take a backup of the database, and try to re-run the Sync-Nav Tenant command with the force Option manually again, but we rather advice you to be cautious when it comes to this option.

It is always better to run Sync-NAVTenant with check mode only to see what objects might be a source of destructive changes.

 

As mentioned before, we intended in this blog to summarize some of the tips and the tricks we learned while working on upgrade, some of these information might be basic for some partners, and some might be invaluable, at the end, I just wanted to share the information in a way that might make your life easier when you upgrade.

Abdelrahman Erlebach

Microsoft Dynamics NAV Support EMEA

navteam

Related posts