We are going to upgrade to 30Gb databases that are running on 10.2b and are using Pro2 5.5. Everyting is running on vmWare, and the new server is a windows 2016 20Gb memory, intel xeon es-2690, 2.6Ghz (2 core).
I have testet diff scenarios and it seems like it will take about 30 hours to load everything. I got 4 tables in each of the databases that has 30-40 million records.
The backup, move of backups, restore and converting from v10 to v11 takes approx. 30 min., but the bulkload is where I need to see if it can be tuned.
The test environment is much faster than the production environment. After going throught the load reports I can see that the production environment is up to 3-4 times slower then test environment. When confronting the admin people I was told that I could not compare the two environment, since the production environment was heavy used. I am not sure in what way, but they are using a kind of SQL server that mirror itself so that there is a failover solution. I am not sure if that is something that could make it go that much slower.
1. Would it be possible to take a backup of production i.e. two days before, and run a full convertion and bulkload, and at the upgrade day it will only read the stuff that is new? Is it so that Pro2 is using ROWID when checking for new records? if so, when doing a full restore, will rowid change?
2. Should I start one bulkload thread for each of the large tables, and use one thread for the rest? or should I just start loading with 5 threads, and let the Pro2 take care of the rest?
Is there other consideration I should take?
I don't know how many DBs you have or what features you're using in PRO2... But we're currently doing a HPUX to Linux migration of our OpenEdge DBs 11.7.x DBs.
We have a couple over 650GB; for those DBs we trialed PRO2 as a replication tool from HPUX to Linux since OpenEdge Replication (is it still called fathom?) won't work between those 2 OS. The feature we liked about PRO2 was it's buildload + record catchup; 650GB took about 46 hours in our environment but at the end we could just shutdown both down DBs and make the Linux the new production with very little downtime. We are yet to do this in production, but the trail worked well.
Why can't PRO2 do all this upgrade work for you? setup new DB, on current Production using PRO2 point it at the new DB and kick off the bulkload + record catchup all in PRO2 app?
On a bulk load you can avoid the build of indexes during the load procedure.
After the load you need to rebuild the indexes on the database.
This works faster.
>>Is it so that Pro2 is using ROWID when checking for new records?
>>if so, when doing a full restore, will rowid change?
The rowid is changed only after Dump&Load on the source database.
>> Should I start one bulkload thread for each of the large tables, and use one thread for the rest?
We use one thread for each small table (30-40 million records is a small table for us)
But for really large tables (~15 billion records), we wrote our own primary synchronization template and process for multithreaded primary synchronization of such a table.The number of threads can be adjusted. We usually use 25-30 threads per table. The main thing here is to have enough server power. We usually synchronize such tables in 3-5 days.
>>Are you saying that Pro2 could bulkload without indexing on SQL ? and then do a rebuild of index on SQL ?
Yes, it's possible. But to do this, you need to remove unnecessary code from the primary synchronization template.
We use Pro2Oracle and in our templates we removed the check for records in Oracle before copying (FIND FIRST bfrTgt< < TGT_TABLE25>>). This check significantly reduces performance. However, should always remember that no one work with the target table at this time.
At the same time, we still had two main indexes active by PRROWID and PROGRESS_RECID fields. This allows us to avoid wasting time after the initial synchronization is completed and start ETL processes immediately (replbatch.sh). All other indexes that are needed by a third party system are rebuilt online in oracle.
Thanks Valeriy, I will try to do a restore with the databases 10 days back, do a full Pro2 bulkload, then restore yesterdays backup, do start a bulkload without do a reset. Do you think that will do what I want?
I'm afraid I didn't understand the process you described.
Why do you need to do two restores?
If I understand your task correctly, then you just need to update the database to 11.7.5 with migration to another server with the same OS?
All you need to do is stop the source database, make a backup, copy and restore it to another server, and get started. If the repl database also needs to be converted and moved, then do it.
And then change the connection parameters in Pro2 if the hostname of the source database has changed. After that, everything will start and the replication process will continue without problems. You do not need to re-do the primary synchronization (bulkload).