Backup and Roll Forward for DR

[Jeff Hartmann]

We have a Progress 9.1e setup (Windows) that currently does a nightly online backup to disk that is then both written to tape and copied to our DR site overnight. The copy takes almost all night to complete and if we lost the main database we could possibly lose the last 24 hours of data. (I've got AI files, but those don't get copied to DR) I'm working on setting up a new server and as part of that I'm fixing this scenario.

I've setup the database with 8 variable AI extents and I'm working on some VBScripts to backup the AI extents by sequence number and then copy them over to the new server. I'm planing on having it switch and copy extents every 15 minutes or so.

The part that I'm not completely understanding is how to handle the full backup. Should I do a full backup every day, once a week, once a month? How often should I transfer the full backup to DR?

It sounds like if I did a full backup every day and archived 60 days worth of AI files, I could restore from any of the full backups in the last 60 days and roll-forward to current with my AI archives. So I could still do full backups to dump to tape every day and only copy the backup to DR once a month or so. Is that correct? If so, is that a good solution, or is there a better recommended approach?

I've also read that there is a point where I'll hit a limit on the sequence number. What's the best way to handle that? Start the AI sequence over again once a year or something?

The other thing I'm wondering about is restoring after a DR situation. Say we did have to go into DR mode and I brought up the DR database. Is it possible to take the AI files at DR and transfer those back to the main database when that server comes back online and roll-forward there?

 

[Casper]

First of all, I am not the real expert on this. I suppose they will jump in later. But there are a few things I would like to say:

We have a Progress 9.1e setup (Windows) that currently does a nightly online backup to disk that is then both written to tape and copied to our DR site overnight. The copy takes almost all night to complete and if we lost the main database we could possibly lose the last 24 hours of data. (I've got AI files, but
those don't get copied to DR) I'm working on setting up a new server and as part of that I'm fixing this scenario.

Why did you choose to copy only the backup to the remote server? I suppose the tapes wont stay in the building. Is that just to make sure that if the tape fails you still have a backup to use? But then u will need the AI files. Otherwise it wont serve any purpose.
How big is the database. If it takes all night to copy you either have a really large database or a very bad line between the production and DR site.

I've setup the database with 8 variable AI extents and I'm working on some VBScripts to backup the AI extents by sequence number and then copy them over to the new server. I'm planing on having it switch and copy extents every 15 minutes or so.

The part that I'm not completely understanding is how to handle the full backup. Should I do a full backup every day, once a week, once a month? How often should I transfer the full backup to DR?

That depends on what you want to do. It sounds like you want to have a warm spare at the DR site. In that case it suffices to copy the backup once and then start rolling forward AI extents. You only need a new backup there if something fails. You need some scripts to monitor this. Locally you can still do an online backup every day to be able to make a fast restore if the switch to the DR site might fail. So you need to archive the ai files both locally (on tape) and remote.
I think there are 2 things at least that you must be aware of:

1. You really must test you DR solution before you are really going to trust it: Does the change to the other server work, how do you handle your clients, can the DR server handle the same workload etc. etc.
2. There is license issue if you want to have a DR site like you describe (I believe it is about 50% of the production license price.

I've also read that there is a point where I'll hit a limit on the sequence number. What's the best way to handle that? Start the AI sequence over again once a year or something?

The maximum sequnce number for your Progress version is 65535.
If you switch every 15 minutes then it takes 682 days to reach that number. So you need to do that before that.

The other thing I'm wondering about is restoring after a DR situation. Say we did have to go into DR mode and I brought up the DR database. Is it possible to take the AI files at DR and transfer those back to the main database when that server comes back online and roll-forward there?

Once that database is started it is out of synch with the original database. So the AI files cannot be roll forwarded on the original database. You need to do the same proces as you did at the DR site (copy backup and roll forward AI, then make the switch to fail back to the original server)

One other thing to consider is upgrading to a newer Progres release. If you dont feel very adventurous thenyou can go to 10.2A (10.2B is the most recent release). Then this maximum sequence number issue is fixed. And on top of that you will have many many new features like typeII storage area's many online features like incrreasing database parameters online, no more real db limits.

Furthermore you can also consider using replication which makes this whole proces faster, more reliable and with no data loss. (With the 15 minute switch you still got a potential 15 minutes of data to loose).

Regards,

Casper.

 

[TomBascom]

For a "non-expert" that was a pretty thorough treatment

One reason to periodically copy backups to the DR site even though you have an active warm spare is to protect against situations where data is corrupted due to programatic or human error. Stuff like "for each customer: delete customer. end.". An automatically replicated db will go ahead and do that sort of thing to both copies. If you also have a backup and all of the ai files you can do a "point in time" roll forward and recover from such a situation. You might, for instance, do a weekly transfer of a full backup and keep 60 days of ai files hanging around. That gives you quite a lot of flexibility.

 

[Jeff Hartmann]

Thanks to both of you. That helps.

Our database is only about 8 gig, and I zip the backup before transfer down to about 4 gig. The DR site is connected by a single T1, so it is a rather slow link.

We don't have a Progress DBA, so I've been learning on the fly. I inherited the database and the recovery solution they had before, which was restore from tape and lose a day, and copying the database to DR every night just speeds that process up. I'm working on fixing that now. We don't have a warm spare now, so I'll have to talk to our Progress rep about licensing too.

Let say I have a full backup and AI files 1 - 11. The main office goes down, so I restore to DR the backup and 1 - 11. Then start the DR database. Am I correct in assuming that the DR database would start with sequence 12 for it's AI files? And then couldn't I restore that same backup at the main site, then 1 - 11, and then 12 from DR and be current again on the production system? I would then only lose the data entered at the main site after sequence 11 and before the database went down.

Any thoughts?

Thanks again!
-Jeff

 

[Casper]

Let say I have a full backup and AI files 1 - 11. The main office goes down, so I restore to DR the backup and 1 - 11. Then start the DR database. Am I correct in assuming that the DR database would start with sequence 12 for it's AI files? And then couldn't I restore that same backup at the main site, then 1 - 11, and then 12 from DR and be current again on the production system? I would then only lose the data entered at the main site after sequence 11 and before the database went down.

No the roll forward of AI will only happen on a restored database with ai disabled. As soon as you enable AI then the the sequence number you start with is 1.

Casper.

 

[Jeff Hartmann]

Thanks! That's clears that up.

-Jeff