The project was to make Crystal Reports running reports against progress database (UniFi) without hitting performance. Exactly the same approach could take place for Progress client running reports only.

We decided to set up Reporting database and to use after-images for replication from Production. Every hour after-image file from Production database is replicated to Reporting database located on another NT box using the below .bat scripts. Note that we do not start the server on Reporting database otherwise the after-image file could not be applied. Instead we connect to database in read-only mode. We start ODBC broker on NT server for Reporting database. And client running Crystal Reports is connecting to Reporting database through that ODBC broker with ?RO startup parameter.

That Reporting Database could also be used as warm spare db, i.e. it could be copied into production in case of crash of the later. All we need to do is truncate bi on Reporting Database. Progress will report: *** The last session was abnormally terminated (852). That is ok, never mind. Then procopy or probkup Reporting DB into Production and it is good to go. Reporting DB could continue accepting After-image files.

We did not even shut production database down when our replication project went live. The initial copy of Production database to Reporting database was done with progress online backup to disk. Progress database restore was done directly from backup file on source NT without a copy of that file to target NT. The whole setup took about 20 min for 2.5GB database. And in case we need to refresh Reporting Database from production we can do that with online backup/restore without users impact.

The downside of that approach is that ?RO client does not see the changes made to database while it is connected. More accurate to say that ?RO client does not reread the records from the database again. They are buffered. So to see the updates recently made to the database (by appling after-image in our case) the client has to be disconnected and reconnected to database again. That was ok for us because reports will be scheduled to run one at a time and disconnected after each run. New ?rereadnolock startup parameter does not help.

One NT specific problem. Jobs in AT scheduler are running as SYSTEM account, so they do not recognize UNC file names. You will have to do registry changes in order to access drives on one NT from another NT box. For details on that see Progress Knowledge Base 19534.

Script to switch AI files on source NT replic.bat.
Script to apply AI file on target NT apply-ai.bat.
Both scripts are running from AT service. In case of a problem with replication script will send e-mail to administrator using blat.exe.

Reference to documentation:
Progress System Administration Guide. 18.4 Implementing Log-based Site Replication

Dmitri Levin
04/28/2000