Question Can I use proquiet to do backup

[Jimbro]

Hi All,

Currently we are moving away from a tape based backup solution to a disk based, replication based solution.
This solution only backs up changed blocks so it can replicate small amounts of data offsite.

The way we do backups currently is with probkup online. probkup online creates a new file each time so our backup solution can not do delta backups from these.

GOAL
What I am looking for with progress is something similar to Postgresql or Oracle where you can run a start backup command that enables you to copy the data with OS tools.

I see the the proquiet utility may be used to quiesce the DB long enough for me to do an LVM snapshot of it so I can copy the snapshotted extents possibly?

Do you guys know of a way I can accomplish my goal?

Thanks
James

Edit.

Hi I found this thread
"Will Symantec Backup Exec DB Agent work with Progress?"

Tom Bascom mention there that one can use proquiet to establish a "quiet point" then backup all the bits.

Tom can you please elaborate and give an example?

Thanks
James

 

[RealHeavyDude]

Quit point have been arond in Progess - IIRC - almost forever. Quit points do block updates to the databsae until you disable them. Therefore one can use them to take snaphots of the database with OS tools. Nevertheless this is not the recommended way to backup a Progess database and you need to test your strategy thoroughly. For example you need to give whatever tool enough time so that you are 100% positive everything is flushed back to disk - which may be a matter of seconds or minutes after you've enabled a quit point - depending on your storate setup and which caches are involved.

• You do know that there is an incremental option to the probkup command?
• You do have After Image enabled, dou you?

What strikes me is that most people are resistent to understand the fact that a database ( regardless of the vendor ) is not just a bunch of files. The database broker caches parts of a database in memory and writes updates back to disk in an asynchronous way. The online backup utilities shipped with the various technologies from the different vendors do exactly cope with that so that you get a consistent snapshot at any point in time when you invoke it.

I don't see any benefit in attempting to use and 3rd part tool to generate a delta on the file system level. It might be favored by your system admins for the sake of simplifying their responsibilities and maybe the guys that sell such software and/or storage systems are telling your system admins that. But, never have I seen such a sales guy show up at a disaster site and to be held accountable for his promises. Plus, in the past, when something bad happened to the database and the backup taken with 3rd party utilities coulnd not be restored successfully, they blamed, you guess who.

Heavy Regards, RealHeavyDude.

 

[Jimbro]

Quit point have been arond in Progess - IIRC - almost forever. Quit points do block updates to the databsae until you disable them. Therefore one can use them to take snaphots of the database with OS tools. Nevertheless this is not the recommended way to backup a Progess database and you need to test your strategy thoroughly. For example you need to give whatever tool enough time so that you are 100% positive everything is flushed back to disk - which may be a matter of seconds or minutes after you've enabled a quit point - depending on your storate setup and which caches are involved.

• You do know that there is an incremental option to the probkup command?
• You do have After Image enabled, dou you?

What strikes me is that most people are resistent to understand the fact that a database ( regardless of the vendor ) is not just a bunch of files. The database broker caches parts of a database in memory and writes updates back to disk in an asynchronous way. The online backup utilities shipped with the various technologies from the different vendors do exactly cope with that so that you get a consistent snapshot at any point in time when you invoke it.

I don't see any benefit in attempting to use and 3rd part tool to generate a delta on the file system level. It might be favored by your system admins for the sake of simplifying their responsibilities and maybe the guys that sell such software and/or storage systems are telling your system admins that. But, never have I seen such a sales guy show up at a disaster site and to be held accountable for his promises. Plus, in the past, when something bad happened to the database and the backup taken with 3rd party utilities coulnd not be restored successfully, they blamed, you guess who.



Heavy Regards, RealHeavyDude.

Dude....That's Heavy.. I like the pic, Clint is the man.

Quit point have been arond in Progess - IIRC - almost forever. Quit points do block updates to the databsae until you disable them. Therefore one can use them to take snaphots of the database with OS tools. Nevertheless this is not the recommended way to backup a Progess database and you need to test your strategy thoroughly. For example you need to give whatever tool enough time so that you are 100% positive everything is flushed back to disk - which may be a matter of seconds or minutes after you've enabled a quit point - depending on your storate setup and which caches are involved.

What exactly do you mean I need top give my tool enough enough time to make sure everything is flushed back to disk?? I was thinking proquiet, lvm snapshot, confirm lvm snapshot is created, end proquiet. Can you elaborate on this please?

Of course I would test first Heavy Dude. We have a DBA team and they use ..

probkup online
and then verify with
prorest

Of course it works like a champ..

• You do know that there is an incremental option to the probkup command?
• You do have After Image enabled, dou you?

Quite aware and yes they do use incremental backups.
Yes we have AI enabled.

What you must understand is that this solution is based on replication of only changed blocks, incremental forever technology. The way progress / probkup online does backups is to write a new file each day..known as flat file backups.. Flat file backups cannot be deltized or participate in delta backups. This is true of MS SQL backups to disk, RMAN backups to disk.. Now you are thinking "did you not read my incremental statement!!" Yes I am quite aware of what an incremental is but unless you are willing to rollback 100 incrementals, you are going to have to do a full backup once in a while. Furthermore we are talking about replicating data across a WAN link so running a probkup online of a 100GB database is going to result in too much data to replicate. over a T1 or 2 x T1 link even if it compresses down to 30GB.

So Microsoft SQL offers a SQL VSS writer and Oracle and postgres allow you to run a start backup command so yo can do a hot backup of the DB files. I mean is it too much for me to ask for something similar from Progress?

What strikes me is that most people are resistent to understand the fact that a database ( regardless of the vendor ) is not just a bunch of files. The database broker caches parts of a database in memory and writes updates back to disk in an asynchronous way. The online backup utilities shipped with the various technologies from the different vendors do exactly cope with that so that you get a consistent snapshot at any point in time when you invoke it.

I am not resistant, I understand this and I thought that proquiet does just this for Progress? I was planning on doing a proquiet, then snapshot the Database directories, then end the proquiet. Sounds logical to me that it would work.

I don't see any benefit in attempting to use and 3rd part tool to generate a delta on the file system level. It might be favored by your system admins for the sake of simplifying their responsibilities and maybe the guys that sell such software and/or storage systems are telling your system admins that. But, never have I seen such a sales guy show up at a disaster site and to be held accountable for his promises. Plus, in the past, when something bad happened to the database and the backup taken with 3rd party utilities coulnd not be restored successfully, they blamed, you guess who.

I am the system admin so you got me man, I am just trying to follow orders and those orders are.. no more tape period.. but make sure you can replicate the data...I am able to do this for all of our other applications, MS SQL Oracle, postgres, sharepoint , exchange, the only exception is progress to this point. But I am a responsible person, if something goes wrong I would take responsibility for it, therefore I would test and test before even suggesting implementation. I am simply looking for the how to here. So please help me out with some syntax or examples if you could please be so kind I would greatly appreciate it.

Thanks again for replying, keep it coming please
James

 

[RealHeavyDude]

Actually the quiet points on the database have been implemented to do just that - enabling you to backup a running database with 3rd party tools. But it is in the responsibility of you and the 3rd party tool that you backup all files that belong to the database when it is spread across different file systems ( or disks ) for optimizing performance ( separating the data / index extents and the before image extents ) and recoverability ( separating after image extents on their dedicated file systems or disks ). If you miss just one file you don't have a good backup of the database and you won't be able to successfully restore it - be it deltas or complete snapshots.

Again, that is not the recommended way to backup a Progress database. But, if you use 3rd party tools to do the job and you thoroughly test and carry out your disaster strategy it CAN be as good as any solution based on the Progress backup utilities. You need a thorough understanding of the components involved and what they do and what they don't do - that's all.

It appears to me that your going for a one-size-fits-all solution which is a valid approach as long as one size really fits all. To often, in the past, I have experienced that one size did not fit all although what did not fit seemed to be made fit.

Regarding replication: Since you use After Image you can use After Image replication - either in rolling your own ( scripted ) or utilizing the replication product from Progress.

What I meant is, that, after enabling the quiet point you need to wait until everything is flushed to disk. The flushing to disk is beyond the control of the database. Progress uses system calls to flush buffers to disk. As soon as the system call returns okay, from the database's point of view everything is flushed although there might still be some cache in a file system, on a controller or a disk sub sytem that has not been flushed yet. Depending on how the delta is created that might be a fact you need to cope with - usually in delaying the start of the "delta" process for some period of time after you've enabled the quiet point. How long that period of time must be - again - depends on the parts involved and how the delta is created. Your milage may vary.

Heavy Regards, RealHeavyDude.

 

[Jimbro]

Actually the quiet points on the database have been implemented to do just that - enabling you to backup a running database with 3rd party tools. But it is in the responsibility of you and the 3rd party tool that you backup all files that belong to the database when it is spread across different file systems ( or disks ) for optimizing performance ( separating the data / index extents and the before image extents ) and recoverability ( separating after image extents on their dedicated file systems or disks ). If you miss just one file you don't have a good backup of the database and you won't be able to successfully restore it - be it deltas or complete snapshots.

Again, that is not the recommended way to backup a Progress database. But, if you use 3rd party tools to do the job and you thoroughly test and carry out your disaster strategy it CAN be as good as any solution based on the Progress backup utilities. You need a thorough understanding of the components involved and what they do and what they don't do - that's all.

It appears to me that your going for a one-size-fits-all solution which is a valid approach as long as one size really fits all. To often, in the past, I have experienced that one size did not fit all although what did not fit seemed to be made fit.

Regarding replication: Since you use After Image you can use After Image replication - either in rolling your own ( scripted ) or utilizing the replication product from Progress.

What I meant is, that, after enabling the quiet point you need to wait until everything is flushed to disk. The flushing to disk is beyond the control of the database. Progress uses system calls to flush buffers to disk. As soon as the system call returns okay, from the database's point of view everything is flushed although there might still be some cache in a file system, on a controller or a disk sub sytem that has not been flushed yet. Depending on how the delta is created that might be a fact you need to cope with - usually in delaying the start of the "delta" process for some period of time after you've enabled the quiet point. How long that period of time must be - again - depends on the parts involved and how the delta is created. Your milage may vary.

Heavy Regards, RealHeavyDude.

Hey thanks again man,

In regards to the way we spread the I/O amongst the disks, this is a topic of great debate internally in our company. We had a strict policy when setting up servers going back to the Redhat 4 and AIX 4 days that we use only RAID 10 on 15k disks for the DB extents. In recent years , the past two years we have kind of let that slide. Even to the point where they are virtualizing Progress DB on SATA 7200RPM disk..Obviously we have had a few problems. That being said we are all LVM whether it be AIX or RedHat. We usually do a set of disks dedicated to the DB extents and maybe a single disk or simple volume for the AI and BI stuff respectively. Obviously virtualization complicates this because the disk has to be shared even when you use dedicated Luns. I'd love to hear the experienced Progress guys like yourself take on virtualizing Progress best practice.

I will have to look into progress native replication, I was not aware it was built into the software. I can also do some sort of Rsync pretty easily. I may setup an hourly backup job to backup the AI files to a local backup disk target and then replicate that backup to the remote site. I need to talk to DBA team about what that entails. I appreciate you pointing out that Progress has built in replication I was unaware and we may go that direction if it makes more sense.

As for the flush I wonder if calling a "sync" command would accomplish the flush properly..Something like proquiet, sync, snap, sync, proquiet end..Have to play with that..

Can you tell me how to determine if I have Enterprise License for Progress? I understand that is needed for proquiet.


Thanks again for your help
James

 

[RealHeavyDude]

Easiest way to determine is to execute the showcfg utility located in the bin subdirectory of the installation directory. I recommend you to use the proenv script upfront - which sets the environment and adds the installation directory to the PATH. showcfg will exactly list what is installed.

I have to admit that I am not familiar with the "sync" command as I've always used the Progress backup utility so far.

In order to use OpenEdge Replication ( based on After Imaging ) you need to buy it first. Obviously, the powers that be at Progress' decided that having to buy a separate product for that would be a clever idea - at least short sighted when it comes to their revenue ). If you opt for OpenEdge Replication Plus you can even have the target database(s) ( two instances max, AFAIK ) online in enhanced read-only mode - which means, for example, you could use them to generate reports.

But to roll your own is rather simple. The only downside is that the replication site must not be touched other than by rolling foward the After Images:

• Switch to the next After Image extent ( will automatically happen when you start a backup with the Progress utility )
• Either have the AI archvier up and running or use your script to backup the full extents and mark them empty
• Copy the backed up After Image extents to your replication site
• Continuously roll them forward against the database on the replication site ( which you would have created initially by restoring a backup )
• Under all circumstances the database on the replication site must not be touched or started in order for the roll forward to work - as soon as you change the database ( say for starting a server against it to have a look at some data ) the last update time stamp of the database is set and will not match the one recorded in the After Image you are trying to roll forward anymore.
• You might opt for a restore at a regular basis ( say once a month or week or whatever suits your needs ).

Heavy Regards, RealHeavyDude.

 

[TomBascom]

Yes, you can use a quiet point to set things up to make an OS utility backup. Or a snapshot using disk utilities. That is what it is for.

As RHD points out -- it is up to you to make sure that you get it right. And it is relatively easy to mess it up. Especially if you are in an organization where the right hand doesn't talk to the left. Consider a case where you initially set everything up and carefully ensure that all of the right filesystems are being snapshot. 6 months later you run out of space on those filesystems and the admin adds a new filesystem... Or a temporary disk shortage leads to one extent being placed on an oddball filesystem... nobody notices until the system crashes and you need to restore...

One non-obvious thing to be careful of when using proquiet... the command returns immediately. But that does not mean that it is safe to start your backup. All it did was to queue a request for a quiet point. You must monitor the .lg file to determine when the quiet point is actually active. 99 times out of 100 it may be active more or less immediately. But it sometimes takes a little while and starting your OS backup prematurely is one of those easy ways to mess up.

You can be certain that the one time that it screws up will also be the time when you need to restore...

 

[TomBascom]

I'd love to hear the experienced Progress guys like yourself take on virtualizing Progress best practice.

There are some threads in the forum already. Or you could open a new one and ask away!

 

[Jimbro]

Ahh now you guys did it, now I am feeling like I can make this proquiet thing happen.

So I ran showcfg, thanks for the tip Really Heavy Dude.

Looks like we are Enterpise licensed on this server right?

showcfg | grep -i product
Product Name: OE Application Svr Ent
Product Name: 4GL Development System
Product Name: OE Enterprise RDBMS
Product Name: Client Networking

There you are Tom, glad you replied.

The .lg file you are talking about the database specific .lg file. For example for our database named "dharry".. would be dharry.lg
I assume I could use a script to kick this off, excuse the oversimplification..I don't yet know what I would look for in the log until I test.

###
proquiet dharry enable
until grep -i "quiet point active"
do
sleep 10
done

lvm snap command ...

check status here

proquiet dharry disable
###

I agree with you 100% that it will need to be watched very carefully. I have a good relationship with the DBA team so we would have to have a meeting around any changes first.


So now I have to ask..

Once I run "proquiet dbname enable" and I see the confirmation in the lg file that the quiet point creation succeeded, if I snapshot the necessary filesystems, these filesystems are now considered a valid source for a progress database backup right?

Just want to make sure I am not missing anything. Any issues with proquiet that you guys have seen like it fails or times out?

Thanks again Tom and Really Heavy Dude.

James

 

[TomBascom]

That is correct. Other than needing to wait for confirmation I've not seen any issues.

 

[Jimbro]

That is correct. Other than needing to wait for confirmation I've not seen any issues.

Thank you Tom

Would you mind confirming from the output that I have an Enterprise license?

Product Name: OE Application Svr Ent
Product Name: 4GL Development System
Product Name: OE Enterprise RDBMS
Product Name: Client Networking


Thanks again
James

 

[TomBascom]

It says "OE Enterprise RDBMS". What more confirmation do you need?

 

[Jimbro]

Thank you Tom

Would you mind confirming from the output that I have an Enterprise license?

Product Name: OE Application Svr Ent
Product Name: 4GL Development System
Product Name: OE Enterprise RDBMS
Product Name: Client Networking


Thanks again
James

It says "OE Enterprise RDBMS". What more confirmation do you need?

Sorry Tom, I wasn't sure but thanks for confirming.

Appreciate it.

James

 

[Jimbro]

Hello Tom and Really Heavy Dude,

I have another question. I understand the proquiet command creates a quiet point to allow the database extents to be copied to disk or tape. What I am unsure of is how do we ensure that the in use AI extent is committed so we can backup the AI files properly? In talking to our DBA team they have communicated that this AI extent commit is performed as part of the probkup onnline command.

Does proquiet do the same or is there a manual way to perform this action?

Thanks
James

 

[TomBascom]

Yes, both probkup and proquiet cause an "rfutil dbname -C aimage new".

 

[TomBascom]

This: http://knowledgebase.progress.com/articles/Article/P112492 might be useful.

And this: http://knowledgebase.progress.com/articles/Article/17553