Online Backup of Multiple Databases - Forum - OpenEdge Deployment - Progress Community

Online Backup of Multiple Databases

 Forum

Online Backup of Multiple Databases

  • 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?

    : Follow this for feedback.

  • If your disk I/O can handle it, then it’s not unsafe.
     
    There is an ancient feature called 2PHASE commit, assuming all of your databases are intertwined (or whatever the syntax of this word is), but it’s not compliant with Replication for example. So if you want to have all those backups at the same time for integrity reasons, then yes.
     
    Or you can do proquiet and a mirror split, snap copy etc ….. if your SAN/NAS supports it.
     
    From: PatrickOReilly [mailto:bounce-PatrickOReilly@community.progress.com]
    Sent: Wednesday, October 15, 2014 3:09 PM
    To: TU.OE.Deployment@community.progress.com
    Subject: RE: [Technical Users - OE Deployment] Online Backup of Multiple Databases
     
    Reply by PatrickOReilly

    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.

    Stop receiving emails on this subject.

    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?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • 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.

    --
    Tom Bascom
    tom@wss.com

  • 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?

    Consulting in Model-Based Development, Transformation, and Object-Oriented Best Practice  http://www.cintegrity.com

  • You have to issue two proquiets.  One for each db.  They know nothing about each other and make no attempt to synchronize.

    Sure, they will usually be close.  And if close is good enough that's fine.

    If close really isn't good enough then there is always two-phase commit.  But you better be really sure that's what you really need because it carries a performance penalty.  At least that's what "people" say -- I don't actually know because I've never felt like it was something that I needed and I have never had a customer feel that way either.

    In practice most of the multi-database implementations that I come across have one main database that is the target of most transactions.  The other databases are read-mostly or are some sort of largely independent bolt-on where exact synchronization is not critical.

    You could also always consider restore and roll-forward to a point in time.

    In practice simply restoring the backup isn't adequate.  You cannot just toss out X hours of transactions.  You also have to recover from your after-image logs to some particular moment.  So you could use the point in time recovery as a method to get everything synchronized.


    On 10/30/14, 4:13 PM, Thomas Mercer-Hursh wrote:
    Reply by Thomas Mercer-Hursh

    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?

    Stop receiving emails on this subject.

    Flag this post as spam/abuse.



    -- 
    Tom Bascom
    603 396 4886
    tom@greenfieldtech.com

    --
    Tom Bascom
    tom@wss.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.