Reporting Database

[jennid]

Hello All-We are looking to set up a separate reporting database. Ideally we would use Replication, but at this time it is not in our budget to do so. We are currently on Progress v 10.0B05. The reporting database could be either another Progress db or SQL. Are there any tools less expensive than Replication that we could use to keep the reporting database current? I would prefer to not have to create database triggers for every table we want to sync up to the reporting database.

If there aren't any tools I could utilize, I was thinking of just automatically refreshing the reporting database from a backup of the production one every night. That would work, but would mean that the reporting database is down while the restore/refresh is running....would like to avoid that if possible since we have operations that run 24x7.

Thanks,

Jenni

 

[MaximMonin]

OpenEdge Replication from source to target DB works as mirroring. It uses ai files to collect changes and roll forward it to target db. You dont have to write any triggers. It is exact copy of source DB.

 

[jennid]

I understand how Replication works. Per my original post though, we don't have Replication in our budget right now....I am looking for another way to do this that is less costly.

 

[RealHeavyDude]

What Maxim meant was "poor man's replication". You can use OS scripts to switch after image extents manually, copy them to the reporting destination and roll them forward against the reporting database. Unfortunately this approach also means for the target database: Look but please don't touch. That means the continuous roll forward process is broken as soon as you access the database - which does not make sense for a reporting database. Therefore copying the backups every night is - apart from you own replication software based on database triggers or triggers in your business logic - are the only option I can see.

Heavy Regards, RealHeavyDude.

 

[MaximMonin]

Look but please don't touch. That means the continuous roll forward process is broken as soon as you access the database - which does not make sense for a reporting database. Therefore copying the backups every night is - apart from you own replication software based on database triggers or triggers in your business logic - are the only option I can see.

Heavy Regards, RealHeavyDude.

My own expirience - we have wrote own replication software 10-15 years ago. It have worked as RHD said - used file copy of ai extents to target server. But right now it doesnt work under new enviroment and new progress versions. And it costly to support this "custom" replication.

 

[jennid]

Thanks RealHeavyDude. We use poor man's replication for our DR environment, so I'm familiar with what you are referring to. That was my understanding too that using replication with the continuous roll forward process would not allow for accessing the database for reporting.

We'll probably proceed with creating a reporting database via copying backups. I just wanted to check here and see if anyone else had any tools or tricks I wasn't aware of.

 

[jennid]

But right now it doesnt work under new enviroment and new progress versions. And it costly to support this "custom" replication.

Curious about your comment about replication via the ai extents. We are currently on version 10.0B05 and using "poor man's replication". Within the next few months we are planning on upgrading to version 10.1.? (not sure on level). What version of progress are you on that this type of replication doesn't work? Just wondering if I can expect to run into problems with using this type of replication once we upgrade. Thanks.

 

[MaximMonin]

It was written in 1997 for 7 progress and sco.
Now it is linux and windows. Copy command between servers has specifics (scp - ssh copy for linux is not the same to rpc for sco) etc....
Openedge replication uses tcp sockets to transfer data.
error managment changed too.
We understood that it is cheaper to buy replication software than rewrite old replication software.

 

[RealHeavyDude]

For the record: "Poor man's replication" based on scripts which handle AI to get a lukewarm standby database is still a valid option and I'll take it that it will work with what ever product updates Progress will surprise us in the future.

It all depends on your disaster recovery requirements, mainly, in the worst case, how much downtime is acceptable and how much data can you afford to lose. Plus, the standby database may only be accessed by the roll forward process, otherwise your approach is broken. That is because there is a time stamp when the database has last been updated which must match between database and after image when you attempt to roll it forward against the database. That way Progress ensures that the database is consistent. In any other case the database would be corrupted, maybe not from the physical point of view, but from the application's point of view.

Heavy Regards, RealHeavyDude.