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.

20 thoughts on “The upgrade story – continued

  1. Jens Glathe says:

    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

  2. Niell says:

    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!!

  3. andre franken says:

    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

    1. 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

    2. p.s. in my case this step ran from 01:50 to 05:47 AM.

  4. Tom says:

    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

    1. 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.

  5. Smita says:

    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 .

    1. Is it the database conversion that fails ?

      1. Smita says:

        Yes…Database Conversion from 2009 to 2013. The error is coming

        1. 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.

          1. Smita says:

            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

          2. 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

  6. Jatin Patel says:

    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

    1. 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.

  7. Anu says:

    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.

    1. 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.

  8. Anu says:

    Thanks for your help. Could you please suggest how much RAM is required to convert such a large database?

  9. Sasa Prodanovic says:

    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

    1. This is dangerous, true, and I don’t do this when I am building and testing the upgrade.

Leave a Reply

%d bloggers like this: