E10 Upgrade Progress to SQL Ideas?

Epicor ERP 10
,
1

Hi Guys,
I just did an upgrade that took over 160+ hours to complete. The Progress DB was only 30GB but their data is distributed such a way that most of those 30GB come from the PartTran table.(on a Dump and Load the Part Tran .d file was over 11GB)
The final DB in SQL is 113GB but with a migration time of over 160 hours Go Live is not really feasible.

Has anyone faced similar issues? Any ideas what may help?
I started to do a migration from Progress to SQL (Progress Schema) in 9.05 that process has been now running for 4 days also, my hope was that converting Progress to SQL ahead of time would make the migration faster, but it appears we are paying for that time regardless.

2

No Way!
Every post I recall seeing on the migration to 10 claims DB size is reduced!

3

Based on my experience PG E905 to E10.x always results in a larger database. I know people have very strong opinions about OpenEdge, but, boh, do they know how to cram data into their db.

SQL/SQLU E905 to E10 generally results in a smaller database but it depends on where the data was in E905 (we’ve added some indexes to some traditionally large tables) and if user defined fields were heavily used.

2 Likes
4

We are doing a 9.05.701 Progress 64 to 10.1.500.8. Our DB in E9 is a little under 50GB our E10 DB ends up being 150GB. Our conversion times on a HP 380P Gen8, SATA SSD are as follows;

  1. Copy of the backup progress file to conversion machine: 20 min
  2. Restore backup progress db on conversion machine: 36 min
  3. Update to 9.05.702a: 3.5 hours
  4. Run 9.05.702a data scrubbers: 2 hours
  5. 9.05.702a to 10.1.500.8 DB conversion 30.6 hours
  6. Post conversion first time scripts: 4.5 hours

After a short talk with Ben we decided to get a Fusion IO card and use that instead of the SATA SSD. I beleive that cut our step 5 down to 19 hours, and little improvements on step 2 to 5min. I’m not 100% sure on the rest of the times as I don’t see the updated times for our last run in our internal conversion process document. I will post them when I find them.

Long story short, check your disk IO and throughput.

1 Like
5

HI Jason,
I think we are already using Fusio IO

For the DB and such… I just think its something specific to this DB. I’ll keep plugging at it though

6

I would track the actual throughput on the drives just to be sure its being used properly. Ours hits close to 800Mbit/s on the 9to10 part. I will check the tables sizes next go around to see what is maybe different between the two.

Pasted image859×265 7.5 KB

7

What are you using to measure the throughput?

8

We use PRTG Discover the 3 Paessler PRTG monitoring solutions , there is a free trial or free version you can download and setup. You will want to run it on a separate server. It’s been very helpful helping us track down performance problems, especially on the E10 upgrade and SAN performance testing.

9

I would be looking at your SQL configuration. Consider setting the database into simple recovery mode during the process.

I would also be looking at things like the location of the log and database files and if they are on the same disks and you can keep them on separate spindles do so (although with Fusion cards it may not make much difference).

I would also be looking at the location of the tempdb,

Finally I would also set the initial size of the destination database to a size that is practical, this will prevent auto database growth during the process, having an auto grow job will stop any processing until it is complete as you can imagine this can kill things pretty fast.

There are a lot of performance counters that you can monitor for SQL to see where things are going wrong, but in my experience with E9 on SQL 2008 R2 there are a ridiculous amount of indexes on tables and that can impact the performance.

One thing that you might be able to do with regards to indexes would be to disable the indexes and constraints on the destination database, but I would be checking with Epicor on that one first though. You could enable the indexes later and perform a reindex (this might take a while also)

An average disk queue length greater than 2 is telling you that you have an I/O problem.

Redgate have some handy free tools that help you analyze instances of SQL,

I have also used the PAL tool which is very useful for analyzing Windows Performance counter logs, and has some great reports.

I have also had another thought, but it is just a thought, and not sure if it is feasible, but what about performing the upgrade in the cloud, there you could through as many resources as you wanted at it.at the end of the day you are just after the resultant database, and I bet there will be a fair bit of index fragmentation and free space in that database.

I’s be interested to know what the resultant size of the compressed backup is.

I hope the information helps.

2 Likes
10

Whoo Hoo, My first like. Thanks.

11

The queue length is interesting. We are seeing pretty high spikes during the conversion, this server is dedicated for this process and is running a Fusion IO card. We must have something holding us back here not sure what it would be.

Pasted image865×311 14.4 KB

I’m pretty sure we are setting the initial size, but I will double check that.

12

I bet those two humps are from log file or database file growth.

You should be able to do this within SSMS:(kindly provided by google)
Right click on a database name in Object Explorer.
Select Reports.
Then Standard Reports.
Then Disk Usage. Voila, the Disk Usage report appears!
To see recent autogrowth or autoshrink events, click the little plus sign under the pie charts.

Here is a post from Brent Ozar.

The default trace needs to be enabled for the autogrowth to show. I believe it is enabled by default anyway.

13

We had similar issues with the upgrade and had to plan to do it when we had a 3 day weekend and could boot everybody off early on the Friday. The issue is the progress conversions are only single threaded and take a dog age to work. When you get into e10 it tends to move a lot quicker. The only thing we managed to do to get them to speed up was in the dell R620 BIOS there was a setting that changed how the cpu behaved and it improved single threaded performance but decreased multi threading.

The e10 technical team are putting together an upgrade process that is multithread. May be worth giving them a shout to see if you can get on the beta?

If you are going from progress to SQL it will expand dramatically as progress is much more efficient on space. SQL to SQL will also increase as the schema is generally bigger than Vantage 8. Ours from 8->10 increase by about 20gb.

1 Like