When two or more Progress databases are connected (to Progress sessions), how can an on-line backup be performed which would keep the backup copies of the databases in sync (at that moment in time)?
Any ideas on this would be appreciated.
Hello World? :)
I have the very same question. No answers here? Hee-ee-eelp please.
Since each DB is "stalled" when the online backup begins, is it safe to simply start all the backups at the same time? Or is there something more sophisticated we should do?
[mention:5f66f4a387054f83b1c03705136c40b1:e9ed411860ed4f2ba0265705b8793d05] : Follow this for feedback.
Hello World? :)
I have the very same question. No answers here? Hee-ee-eelp please.
Since each DB is "stalled" when the online backup begins, is it safe to simply start all the backups at teh same time? Or is there something more sophisticated we should do?
ahmed_seedat : Follow this for feedback.
Flag this post as spam/abuse.
Thanks Libor, we will proceed on the basis of simply starting the backups at the "same time".
I guess there remains a small risk if the start times are not perfectly in sync. But if they are within fractions of a second I guess our risk is very low. And since the DBs are used by common code, the transactions should naturally be in sync, so the state of all transactions should be consistent at the time of the backup.
Am I correct in assuming that during on-line backup, any data affected by in-flight transactions will be backed up in the pre-transaction state?
Without the proquiet, you are rolling the dice. What do you mean by "stalled"? Are any users connected?
Like Thomas says... you really have to use proquiet to get a consistent backup of two or more databases at once.
Neither DB knows about the other DB so there is no transaction sync between the two. The quiet point forces both DBs to stop writing so you can get a good backup.
If you don't use proquiet on both DBs....
You risk having one part of a logical transaction committed in one DB and the other part of that logical transaction in flight in the other DB. When you restore both databases and crash recovery happens the in flight transaction will be rolled back and you are out of sync.
EDIT: None of this matters if the databases are independent of each other... like multiple versions of the same application.
Well the backup itself does proquiet while backing up the bi file. So if the backups start at the same time, it should have a same effect as proquiet. So rolling back would be restoring the databases to the same time the backup started/has been taken. I don't see any difference (in risk) if proquiet would be used as one would need to restore all the databases to the same time. neither probkup nor proquiet will be executed at the exactly same milisecond by the OS .... And people can work during an online backup, they can't work during proquiet.
The issue is that without proquiet the transactions between databases will never be at the exact same point of time with no pending transactions. Even a few ms of difference can be enough to split the transactions.
Issue proquiet on DB 1 (make sure it takes) and then a proquiet on DB 2 (make sure it takes) and then start the probkups .
Or live with the risk - which depends on how active your databases are when you are doing the backups.
proquiet won't fix that. there is no guarantee that they will all be
coordinated.
If you have a user active with a transaction that spans the two databases ... or, simpler, perhaps, two users, each with a transaction active each in separate databases, what happens when one issues the proquiet?
Reply by Thomas Mercer-HurshIf you have a user active with a transaction that spans the two databases ... or, simpler, perhaps, two users, each with a transaction active each in separate databases, what happens when one issues the proquiet?
Stop receiving emails on this subject.Flag this post as spam/abuse.
-- Tom Bascom 603 396 4886 tom@greenfieldtech.com
Isn't part of the point of proquiet to stop future writes to the DB after the current writes are flushed? Can you get a full quiet point with pending transactions?
Also I can confirm that 2 phase commit has a massive performance penalty... having seen it in action before. If I recall correctly it was something like 10 times slower for transactions.
Hi, maybe just a simple solution. Merge the 2 or more databases into 1. Make some logical storage areas. This solves your backup problem. No 2phase commit needed. And less maintenance and monitoring on your databases.
Unfortunately, whilst this is a good idea in theory, Tkerk, in practise it may not be so easy with legacy applications. In our case, we have 5 databases for legacy reasons, of which only really one is written to in practise. It would make sense to get rid of the others as you say, but the codebase is littered with DBNAME.TABLE.FIELD phrases, each of which would need visiting in order to fix them. It's a massive overhead that businesses don't want to swallow, even if you explain the risks to them.
"but the codebase is littered with DBNAME.TABLE.FIELD phrases, each of which would need visiting in order to fix them."
There's a quick & dirty fix for that too:
Set up aliases using CREATE ALIAS statements in your startup procedure, to redirect the obsoleted database names to the single remaining connection.
Be sure to test though: Since everything goes over a single connection instead of multiple ones, there may be corner cases where concurrency, conguency and transaction flow can change.
The bigger headaches will show if the same table names appear in multiple databases, because then you may need to rename tables before you can merge into 1 database.
"The bigger headaches will show if the same table names appear in multiple databases, because then you may need to rename tables before you can merge into 1 database."
This is of course the other, bigger issue we have... How could I forget that?!
@james: if you are writing to only one of the databases, why do you have to synchronise the backups?
and: how many times has not synchronising them caused a problem in the past for this legacy system?
are you fretting about a problem you don't actually have?
Hi [mention:9617a07f61934bc98f6fccb3b7fabfae:e9ed411860ed4f2ba0265705b8793d05] - we don't synch backups. The OP does though.