Upgrade Toolkit NAV 2013 R2 does not work for many companies

vremeni4

Hi,

After 10 days of trying all possible options to upgrade a 10GB database (NAV 2009 R2 FR Localisation) with 90 companies I have to say that the Upgrade toolkit does not work, if there are many companies in the database.

Everything works fine until the step where customised objects have to be imported; after the database conversion to NAV 2013 R2.
When the import starts after a minute or two I get this error message :

This request operation sent to net.tcp://vmdev.globalmc.com:3945/2013r2_rollup4/ManagementService 
did not receive a reply within the configured timeout (00:01:00).  The time allotted to this operation may have been a portion of a longer timeout.  
This may be because the service is still processing the operation or because the service was unable to send a reply message.  
Please consider increasing the operation timeout (by casting the channel/proxy to IContextChannel and setting the OperationTimeout property) 
and ensure that the service is able to connect to the client.

I do understand the error message but I cannot find any config file where I can change the timeout.
As RTC client connects for the first time to the database it starts SQL Schema synchronisation by running following code

IF (SELECT OBJECTPROPERTY(OBJECT_ID(N'dbo."VIR_00_009$Sales Invoice Line"'), N'IsTable'))=1
DROP TABLE dbo."VIR_00_009$Sales Invoice Line";
CREATE TABLE dbo."VIR_00_009$Sales Invoice Line" ("timestamp" TIMESTAMP NOT NULL,"Document No_"
..... all fields here
,CONSTRAINT "VIR_00_009$Sales Invoice Line$0" PRIMARY KEY CLUSTERED ("Document No_","Line No_"));
CREATE UNIQUE NONCLUSTERED INDEX "$1" ON dbo."VIR_00_009$Sales Invoice Line" (...);
……. All indexes here 

If I stop NAV Server (and kill SQL Schema synchronisation processes on the SQL server) and try to import the FOB with custom objects again I get the same error message.
So I thought I will leave the SQL Schema synchronisation to complete.
If I try that after 4 hours I get this error message in the Event Viewer

Type: System.Data.SqlClient.SqlException
ClientConnectionId: d743bf05-37ef-4325-b3bc-b3de558ef569
Class: 11
LineNumber: 0
Number: -2
Server: SERVM3
State: 0
Source: .Net SqlClient Data Provider
ErrorCode: -2146232060
Message: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace:
     at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
     at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
     at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
     at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
     at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
     at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
     at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
     at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
     at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
     at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
     at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
     at System.Data.SqlClient.SqlInternalTransaction.Commit()
     at System.Data.SqlClient.SqlTransaction.Commit()
     at Microsoft.Dynamics.Nav.Runtime.NavSqlConnection.ExecuteAction(Action action, NavSqlCommand command)
HResult: -2146232060
----------------------------------
Type: System.ComponentModel.Win32Exception
NativeErrorCode: 258
ErrorCode: -2147467259
Message: The wait operation timed out
HResult: -2147467259

I changed the <!--Timeout for Sql command-->
<add key="SqlCommandTimeout" value="09:30:00" /> and the SQL Server was running happily all the time.

If I try to connect with RTC client to the database I get a list of companies and after that this error message:
The following SQL error was unexpected. Invalid column name 'Queue Folder Path'. Invalid column name 'Queue Folder UID'. Invalid column name 'Storage Folder Path'. Invalid column name 'Storage Folder UID'. Invalid column name 'Autodiscovery E-Mail Address'. Invalid column name 'Email Batch Size'. Statement(s) could not be prepared.
Which again makes sense as the SQL Schema synchronisation is not completed yet.
I also have to say that I did compiled all system tables, as I was following the upgrade toolkit step by step.

The only other way to force SQL Schema synchronisation to run is to use Powershell command Sync-NAVTenant
So I would restart NAV Server and run Sync-NAVTenant.
After few hours I would get following error message :

Sync-NAVTenant : The following SQL error was unexpected.
There is insufficient system memory in resource pool 'internal' to run this query.
Caution: Changing any part of an object name could break scripts and stored procedures.
Caution: Changing any part of an object name could break scripts and stored procedures.
At line:1 char:1
+ Sync-NAVTenant -ServerInstance Venus_Chosn2013R2_Rollup4
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (0:Int32) [Sync-NAVTenant], FaultException`1
    + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$Venus_Chosn2013R2_Rollup4,Microsoft.Dynamics.Nav.Management.Cmdlets.Syn
   cNavTenant

or on another SQL Server I would get this error message

Sync-NAVTenant : A connection to SQL server is no longer usable.
This could be caused by one of the following reasons:
* The server has been shut down manually or because of an error.
* The SQL server connection settings are not correct.
* A network failure has occurred.
* A hardware failure has occurred on the server or on your computer.
Try to connect again later or contact your system administrator.
At line:1 char:1
+ Sync-NAVTenant -ServerInstance Venus_Chosn2013R2_Rollup4
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (0:Int32) [Sync-NAVTenant], FaultException`1
    + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$Venus_Chosn2013R2_Rollup4,Microsoft.Dynamics.Nav.Management.Cmdlets.Syn
   cNavTenant

The main problem is that every time when I start NAV Server the SQL Schema synchronisation starts too and during this time the database is locked.
The process fails after some time and again RTC client cannot connect to the database. (Invalid column name 'Queue Folder Path'.)

I also tried compiling all objects, exporting and importing all objects, Build Server Application Objects.
The same issue exist for NAV 2013 R2, NAV 2013 R2 Rollup3 and Rollup4. I tried only these three I did not try rollup1 or Rollup2 )
And yes when I was using Rollup3 or 4 I was using Rollup executables, for NAV Server, RTC client, Development environment and also the FOB was exported from a NAV 2013 R2, Rollup 3or 4 database respectively.

Interesting thing If I take the same database and I leave only 15 companies inside everything works fine.

So I have a strong suspicion that Microsoft never tested the Upgrade tool-kit for 90 or more companies.

Any help is appreciated.

ara3n

Hello. I did an upgrade this weekend as well to 2013. It took 3 times to get it finished. It's 170 gig database.


We used rollup 3 as well and it didn't work.

after using hotfix bellow did we get the upgrade finished with loading the objects.

https://mbs2.microsoft.com/Knowledgebas ... US;2934571


The way we finished the synchronization is instead of connecting to rtc we ran Syncrhonize object command from AdminShell.

As far as the sqlcommandtime out setting, it is located in Customsettings.config file on the service tier.

So download the hotfix and try to redo the upgrade.

gerdhuebner

May be this is helpfull, too:
http://blogs.msdn.com/b/nav/archive/2014/03/27/table-synchronization-paradigm-in-microsoft-dynamics-nav-2013-r2.aspx

As it is stated in this link, it is recommended to do the sync-process via cmdlet sync-NavTenant and not automatically by opening RTC-client.
In case the sync-NavTenant terminates because of timeout, it must be restarted after increasing the time out parameter SqlCommandTimeout in the server configuration file from its default value of 30 minutes to some higher value.
I monitored the sync process with SQL Profiler (see link above) after Step 1 of the upgrade (import of all objects): At first the tables for each company are rebuild on sql server. At last the indexes are rebuild.

The hotfix mentioned by ara3n should be included in Cumulative Update 6 (Build 36366) - by the way, the build no. of the hotfix (36281) is smaller than the minimum recommended build no. (36310) in the link above!?
With Updates Rollup 4 and 5 came changed upgrade objects, which should also be included in Cum. Upd. 6

The problem with "...Invalid column name 'Queue Folder Path'. Invalid column name 'Queue Folder UID'...":
These fields belong to table 5079 (Marketing Setup). Their names have been changed from NAV 2009 to NAV 2013 R2. May be these fields have their old names on sql server table ...Marketing_Setup, while in NAV they already have their new names. This situation may occur, if you (temporalily) changed the option "Prevent data loss from table changes" to 'No'. In order to fix this, you should try the following:
Start NST, do not start any RTC-client, start the development environment, change this option back to <Yes> (never change this option to 'No' in a one tenant environment), recompile table 5079, start the sync-process via cmdlet from Administration Shell.

VectorK

Try with the last rollup. It works better

http://blogs.msdn.com/b/nav/archive/201 ... eased.aspx

vremeni4

Hi,

Just to give everyone an update on this issue.
Almost all of these issues have been resolved with cumulative update (CU) 6 and CU 7.
For an Upgrade I would recommend at elast CU.
The only error that still exist, is this one,
when you run Sync-NAVTenant from powershell

Sync-NAVTenant : A connection to SQL server is no longer usable.
This could be caused by one of the following reasons:
* The server has been shut down manually or because of an error.
* The SQL server connection settings are not correct.
* A network failure has occurred.
* A hardware failure has occurred on the server or on your computer.
Try to connect again later or contact your system administrator.
At line:1 char:1
+ Sync-NAVTenant -ServerInstance Venus_Chosn2013R2_Rollup4
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (0:Int32) [Sync-NAVTenant], FaultException`1
    + FullyQualifiedErrorId : MicrosoftDynamicsNavServer$Venus_Chosn2013R2_Rollup4,Microsoft.Dynamics.Nav.Management.Cmdlets.Syn
   cNavTenant

The problem is caused by the size of the transaction on the SQL Server.
If Synch takes more then 6 hours this error will appear.
The only way to resolve it was to increase the RAM on the SOL Server. In that case the Synch does not take longer then 6 hours to complete so everything works fine.

I hope this will help someone with the same issue.