The Client went live on the 6th. This is the task I told you about in last September.
The database was 567GB in size with just a little less than 400GB used. It had 1.506.747 sales invoices and 30.438.858 value entries. Version 4.0Sp3 with a lot of added fields and customization.
When I first ran the upgrade process with the standard upgrade tool it took about ten days. As you can see there is no way to stop a company for ten days. The process was rewritten and these are the steps I used.
- Backup the live database with compression enabled
- Copy the backup file to the new SQL server
- Restore the backup to the new server
- Drop all statistics that was stopping me from modifying tables
- Run Inventory Adjustment
- Import and execute 4.0 SP3 -> 2009 R2 Step 1
- Import 2009 R2 objects
- Prepare data for Step 2 by importing conversion tables
- Execute 4.0 SP3 -> 2009 R2 Step 2
- Import 2009 R2 -> 2013 R2 upgrade objects and execute Step 1
- Change database compatibility level to 2012
- Execute SQL commands to drop all statistics that was blocking database conversion
- Open the database with NAV 2013 Developement Environment and convert database
- Open the database with NAV 2013 R2 Developement Environment and convert the database
- Compiled the system tables
- Changed the SQL Command Timeout for the service to 12:00:000 and started the service
- Selected “No” in the Developement Environment option “Prevent data loss from table changes”
- Imported all NAV 2013 R2 objects
- From PowerShell, executed Sync-NAVTenant
- Selected “Yes” in the Developement Environment option “Prevent data loss from table changes”
- Import 2009 R2 -> 2013 R2 upgrade objects and execute Step 2
There where a few minor steps in between and after that was needed for this customer but basically this was it.
The good news is; this was completed in just under 23 hours with an unexpected delay of just over an hour. This can be done and the client is happy.
Hi Gunnar,
thanks for sharing. This appears to be vital information when you have to do a real upgrade… Anyway, how is the client faring with the new GUI? That’s one of the main obstacles I can’t get my head around… it feels so much slower, mouse-only, always search for the icon – like.
with best regards
Jens
Next tuesday I´ll do a real upgrade, from 3.70 to 2013 R2 , O_o . DB near 15 Gb, and took me 2 days in test. But I think I can use some of your ideas and save some time! Thank you!!
Hello Gunnar,
actually we are upgrading a database with 450 GB, 4 companies from 4.02 to 2013R2 and therefor facing the
same callenges.
The technical convert from 2009 to 2013 (opening nav 2009 db with 2013 dev Client) takes about 18 hours !!
Could you accelerate this process , or what was your experience ?
With best regards from germany
Andre
Hi Andre
Two things I did.
1. Deleted all indexes from large tables (they need to be rebuilt anyway in NAV 2013 R2)
2. Used SSD drives on the SQL server
p.s. in my case this step ran from 01:50 to 05:47 AM.
Hello Gunnar,
I am facing same issue for one my customer upgrading from NAV40SP3 (200GB) to NAV2013R2. Especially on NAV40SP3 to NAV2009R2 step 2 process. Originally NAV40SP3 to NAV2009R2 step 1 took 50 hours to complete. We built new upgrading PC with all SSD drive and that steps down to 7 hours. For update Sales Invoice, that steps will take 2 to 3 days to complete based on current calculation. I tried your steps on this post. But I cannot get expected hour. I am not sure if this is because our customer has a lot of data in value entry and sales invoice line table or I missed some detail steps. For the steps that “Drop all statistics that was stopping me from modifying tables”, is that from SQL side to drop all statistic or just disable unused key from NAV. I tried use SQL command to drop all statistics, but still got around 50 hours for update sales invoice step.
Here is some information from our customer’s database
Value Entry: 26600718 records
Sales Invoice Header: 781082 records
Sales Invoice Line: 17394930 records
Item Ledger Entry: 17651068 records
Best regarding from Canada
Tom
Hi Tom
There is a step from 4.0 to 2009 where the system is updating item ledger entries and value entries based on invoiced sales- and purchase lines. This process took several days to complete in my first trial.
What I did is. I created a new database (Upgrade Helper) and in that database I stored the new fields for item ledger entries and value entries from my first test upgrade process. I also stored the last invoice number for both sales and purchases. Then on my next upgrade process I began with merging these fields from the upgrade helper database to the live database and then only processed new sales and purchases. This saved a lot of time.
Thanks a lot for sharing this.
Right now I am doing data migration from 2009 to 2013. As per the quick Guide provided by Microsoft, I have completed 1st step & doing the conversion i.e opening the 2009 Database in Navision 2013 version.
My DB size is 250 GB. After 12/13 hours I am getting an error
“The G/L Entry table cannot be changed because it is locked by another user.
Wait until the user is finished and then try again.”
What to do ?? The database is already in single use mode in SQL.
Please help .
Is it the database conversion that fails ?
Yes…Database Conversion from 2009 to 2013. The error is coming
And no backup running ?
The Database Conversion puts the database in single user mode. I suggest that you put the database on SSD drive(s) to gain speed.
We have already upgraded the RAM to 32 GB. SSD drive , may be we can try while going live.
Yes. Off course this will speed up the Process. But do you have any clue why this error is coming ??
In your Post , you have suggested to drop statistics.How can we do that ?
In navision in Ledger tables(Ex:G/L entry, Item Ledger Entry, Value Entry etc), if I disable all secondary keys , then do you think it will speed up the process ? It will affect the data if i do so ??
Please suggest.Thank you so much
By dropping indexes you will gain time as most of them need to be rebuilt in NAV 2013 R2 anyway. The error I got and fixed with drop statistics came just after a few minutes. But perhaps this will help you. Here is the script that I used:
http://sqlblog.com/blogs/jonathan_kehayias/archive/2008/12/15/drop-all-indexes-and-stats-in-one-script.aspx
Hi Gunnar
We are upgrading 400GB+ database from 3.7 to NAV 2013R2. we have used your tricks for Step 1 data transfer from NAV 3.7 to 2009 R2, but for Step 2 data transfer from NAV 3.7 to 2009 R2 it took so much time. Especially Transfer Receipt Header and Transfer Shipment header tables took more than 48 hours. Can you please suggest any solution to make this process fast?
Best Regards,
Jatin
Hi Jatin
It probably is the same step that took forever to complete in my case. I found out that the process was updating a few fields on Item Ledger Value Entry and Value Entry. I created additional database with the primary key + the new fields and a few tables to keep the last processed document.
When I then executed the upgrade I merged the new fields from the additional database into the live database and only processed documents newer then the ones in the additional database.
Hey,
We tried to convert a database froom 2009 r2 to 2013 of 126gb. It took 18 hrs. Its giving us a error The following SQL Server error or errors occurred when accessing the Calendar Entry table:
802,”42000″,[Microsoft][SQL Server Native Client 10.0][SQL Server]There is insufficient memory available in the buffer pool.
SQL:
CREATE VIEW dbo.[Tan Prints Consolidate$Calendar Entry$VSIFT$2] WITH SCHEMABINDING AS SELECT [Work Center Group Code],[Date],[Work Shift Code],COUNT_BIG(*) “$Cnt”,SUM([Capacity (Total)]) [SUM$Capacity (Total)],SUM([Capacity (Effective)]) [SUM$Capacity (Effective)] FROM dbo.[Tan Prints Consolidate$Calendar Entry] GROUP BY [Work Center Group Code],[Date],[Work Shift Code]
What should we do?
Appreciate your help.
Hi,
I would start by dropping all indexes and just leaving primary keys. When you import and sync the 2013 (R2) objects the indexes will be rebuilt.
Thanks for your help. Could you please suggest how much RAM is required to convert such a large database?
Hello Gunnar,
Only one thing is strange for me “Selected ‘Yes’ in the Developement Environment option ‘Prevent data loss from table changes’ “. Isn’t this dangerous?
If you choose this there is possibility to lose some data in customer database, or I’m missing something?
Thank you and regards,
Sasa
This is dangerous, true, and I don’t do this when I am building and testing the upgrade.
Hi Gunnar, Can i use this step in upgrading from Nav 2013 to Nav 2018 Cu5 and where exactly would i make use of the upgrade tool kit. Thanks for the help.
Gbenga
I believe you need to upgrade to 2015 and then from there to 2018. With the new upgrade codeunits and the parallel execution you should be able to do this with C/AL code.
Thanks Gunnar, but the new upgrade tool kits allow upgrade from nav 2013 to nav 2018 so are you saying if i use the new upgrade codeunite i do not need to go thorough the process you mention above and still attain the same speed of execution.
You just need to try it to see the speed
Hello Gunnar,
Amazing blog you have here 🙂
I was wondering if you may be able to help me with a problem. Me and my team members are upgrading a NAV2009 R2 database to NAV2013 (RTM, not R2. With cumulative update installed) and we get the error: ‘The Type ‘UNDEF’ is not defined for the function’. We followed all steps you described above including dropping statistics, but at the end of the conversion step (opening the NAV2009 database by NAV 2013 (not R2) development environment) when the progressbar is at 100% and the dialog disappears the above error pops up. We tried deleting or replacing objects of which we think they may be the cause of all this, but still no effect (only effect: The same error popped up sooner or later). We think it may be due to some operations at the end of the upgrading process, but so far we actually have no clue. Thanks in advance and hopefully you can provide us with some helpful tips.
Greetings,
Floris
Hi there.
I would bet that you have either a variable or a function without a name in your 2009R2 code.
Or the function name or variable name is used internally by the system.
Wow, thanks for the fast reply. We will look right into it!
Update: The conversion succeeded. We could not find any functions or variables that were not defined or had a reserved (internally by the system) name. Therefore, we used a SQL tracer to find out where the conversion stopped, deleted the data in the specific table and deleted the table object in the object designer (this was the solution to our problem). I think somewhere in the data of the specific table something was undefined or not supported (I could not find any functions or variables that were ‘odd’ as described by you).