NAV 5 Upgrade - Extremely large DB

itmanager00

I have a customer who is upgrading a very large NAV 4 sp3 database to NAV 5 sp1. The database size is approximately 120GB. The upgrade procedure is being run on a separate server with DB and SQL client on the same system. It takes approximately 2 weeks to run!

Has anyone had any luck getting more performance from such a large upgrade? This particular customer is an online retailer with very large sales volumes. Shutting down their NAV system for an upgrade process which takes more than a couple of days is not acceptable to them.

So far our only options are to purge history but the bulk of their data is not more than 12 months old.

Any ideas would greatly appreciated.

Clark Nichols
TRAK Software
Lexington, Kentucky

davmac1

You don't give enough information.
Is this an executable upgrade only? I am guessing not, since they run fairly fast.
SQL Server 2000, 2005, or 2008?
System optimized for SQL Server?
32 bit or 64 bit? 32 bit has massive speed degradation for converting a large number of companies.
How many companies in database?
If you are doing an object upgrade, is there any step in the upgrade that is taking a long time? Sometimes the conversion code is highly inefficient for minor changes to very large tables in a SQL Server environment.

matttrax

Yeah, take a look at the code in the upgrade codeunits. I did an upgrade once where it was taking an average of one second per value entry...too bad there were over 5,000,000 and none of them needed to be changed.

Alternatively you take a snapshot of their data and upgrade it. They go back to work while you upgrade old data. When that's done you take the new data, upgrade it, and merge it in on go-live weekend. We've done that on occasion. Upgrade everything through January 2009 or so, then do February on go-live weekend. Whatever the last closed accounting period is is what I go up to when I have to do this.

i4tost

Try to use native database for upgrade procedure and after restore it on SQL.

bbrown

We did a 4 to 5 upgrade with one of our cleints this past summer. Their database had ~100 GB of data and they had a limited available downtime window. Our approach was to do much of the data conversion directly in SQL. We used the Upgrade Toolkit as a guide, reviewed each step, and determined whether to run it in NAV or SQL. In the end, we ended up doing about 90% of the steps in SQL. The others steps we decided the time involved in developing the SQL code would not be offset by the performance improvement. We also skipped the steps that didn't do anything or limit the dataset on others.

In the end, the upgrade process took ~15 hours from the point of starting the backup to allowing users back on the system. Having a nice server to run this on didn't hurt either.

Server used:

2 quad core 3.0 Ghz processors
64 bit Windows & SQL (Enterprise)
32 GB RAM
*.MDF drive - RAID 1 (2 x 72 GB) SCSI (Also hold core SQL databases)
Temp DB - RAID 10 (4 x 72 GB) SCSI
*.NDF - RAID 10 (30 x 72 GB) SAS
*.LOG - RAID 10 (6 x 72 GB) SAS