Backup Options

[johnm]

Hello All,

I appreciate all of the help with my previous posts. It's nice having such a
knowledgeable group to draw upon for help.

I have taken all of your previous suggestions to heart and have upgraded our
DB/2 system to 8.2.

However, I still have a problem with backup times and backup window size.

Our DB/2 8.2 system is hosted at a data center in LA. Our main office is in
Portland.

Our current DB size is 18 GB.

We have been taking a full offline backup of the DB every evening.

Our process looks like this:
1. After the offline backup of the DB is completed, we are FTPing it to
Portland for offsite storage and for use on a standby machine should the
need arise.
2. During the period between offline backups, a complete set of log files
are ftp'd to Portland every two hours.

Therefore, and theoretically, if the DB in LA goes down, we can recover all
data, that has been logged of course, except maybe for the last 2 hours
worth of transactions.

Here is the problem. The day is no longer lengthy enough to ftp the DB
backup and all of the log files to Portland. The available window has become
just too small.

Is there a way we can do this more efficiently thereby increasing the
effective size of our window?

What is the most efficient way of backing up a DB at a remote location and
then transferring the backup files to the "home" office?

We expect the DB to double in size over the coming year.

One last question.

We are also planning on implementing HADR. We figured that would solve this
backup problem because while the primary server would be in LA, we would
have the secondary server in Portland. We figured we could then backup both
the DBs local to the servers. However, we have since read that "Backup
operations are not supported on the standby database". This kind of throws
us a curve. Is there a way to backup the standby server? If so, this might
solve our entire conundrum.

Thanks for all of your help.

John

[Ian]

johnm wrote:

Our process looks like this:
1. After the offline backup of the DB is completed, we are FTPing it to
Portland for offsite storage and for use on a standby machine should the
need arise.
2. During the period between offline backups, a complete set of log files
are ftp'd to Portland every two hours.

Therefore, and theoretically, if the DB in LA goes down, we can recover all
data, that has been logged of course, except maybe for the last 2 hours
worth of transactions.

Here is the problem. The day is no longer lengthy enough to ftp the DB
backup and all of the log files to Portland. The available window has become
just too small.

Is there a way we can do this more efficiently thereby increasing the
effective size of our window?

Have you tried compressing your backup (either with the COMPRESS option
or before transmitting (using gzip or bzip2)?

Also, you shouldn't need to send the full backup every night. Just send
the log files, which should be sufficient to keep your standby server
in sync. You could transfer the full backup once a week.

Ian

[Jurgen Haan]

Try using delta's they save a lot of time and space.

-R-

[Phil Sherman]

You didn't state anything about the FTP link. One obvious option is to
increase the size of the "pipe" between the sites. As mentioned in prior
responses; compression will also help. I have also read about cases
where a supposed high speed connection between two distant offices ended
up with a t1 section somewhere in the middle. When this was discovered;
the carrier rerouted to provide the appropriate bandwidth. Today's ADSL
services usually run t1 speeds for receiving but only 10% or less of
that for sending.

A time-honored and frequently viable technique is to back up to portable
media and overnight a copy to the remote location. The overnight costs
need to be weighed against the cost of the communication link needed to
transmit the data.

Phil Sherman

johnm wrote:

Hello All,

I appreciate all of the help with my previous posts. It's nice having such a
knowledgeable group to draw upon for help.

I have taken all of your previous suggestions to heart and have upgraded our
DB/2 system to 8.2.

However, I still have a problem with backup times and backup window size.

Our DB/2 8.2 system is hosted at a data center in LA. Our main office is in
Portland.

Our current DB size is 18 GB.

We have been taking a full offline backup of the DB every evening.

Our process looks like this:
1. After the offline backup of the DB is completed, we are FTPing it to
Portland for offsite storage and for use on a standby machine should the
need arise.
2. During the period between offline backups, a complete set of log files
are ftp'd to Portland every two hours.

Therefore, and theoretically, if the DB in LA goes down, we can recover all
data, that has been logged of course, except maybe for the last 2 hours
worth of transactions.

Here is the problem. The day is no longer lengthy enough to ftp the DB
backup and all of the log files to Portland. The available window has become
just too small.

Is there a way we can do this more efficiently thereby increasing the
effective size of our window?

What is the most efficient way of backing up a DB at a remote location and
then transferring the backup files to the "home" office?

We expect the DB to double in size over the coming year.

One last question.

We are also planning on implementing HADR. We figured that would solve this
backup problem because while the primary server would be in LA, we would
have the secondary server in Portland. We figured we could then backup both
the DBs local to the servers. However, we have since read that "Backup
operations are not supported on the standby database". This kind of throws
us a curve. Is there a way to backup the standby server? If so, this might
solve our entire conundrum.

Thanks for all of your help.

John

[Steve Pearson (news only)]

Hi, John.

Regarding HADR, if backing up your standby is a requirement for you,
please have your IBM rep formally enter it in the requirement tracking
system. This enhancement is already on the radar, but prioritization
vs. other work can be influenced by the level of customer interest.

Thanks.

Regards,
- Steve P.
-----------------------
Steve Pearson
IBM DB2 UDB Development
Portland, OR, USA

[johnm]

Excellent Suggestions.

We are not as yet using any sort of compression. We'll give that a whirl,
and I would think, it should certainly help.

Also, sending the full backup only once a week would absolutely help.

Thanks!

John
"Ian" <ia*****@mobileaudio.com> wrote in message
news:42**********@newsfeed.slurp.net...

johnm wrote:

Our process looks like this:
1. After the offline backup of the DB is completed, we are FTPing it to
Portland for offsite storage and for use on a standby machine should the
need arise.
2. During the period between offline backups, a complete set of log files are ftp'd to Portland every two hours.

Therefore, and theoretically, if the DB in LA goes down, we can recover all data, that has been logged of course, except maybe for the last 2 hours
worth of transactions.

Here is the problem. The day is no longer lengthy enough to ftp the DB
backup and all of the log files to Portland. The available window has become just too small.

Is there a way we can do this more efficiently thereby increasing the
effective size of our window?

Have you tried compressing your backup (either with the COMPRESS option
or before transmitting (using gzip or bzip2)?

Also, you shouldn't need to send the full backup every night. Just send
the log files, which should be sufficient to keep your standby server
in sync. You could transfer the full backup once a week.

Ian

[johnm]

Please excuse my ignorance but, what is a "delta"?

John

"Jurgen Haan" <ju****@fake.dom> wrote in message
news:42***********************@news.xs4all.nl...

Try using delta's they save a lot of time and space.

-R-

[johnm]

Thanks for the great suggestions!

We have not been using compression, but we will try it as that should
certainly help.

Also, I can see your point about sending the full backup only once a week.
We will try that as well.

Thanks,

John

"Ian" <ia*****@mobileaudio.com> wrote in message
news:42**********@newsfeed.slurp.net...

johnm wrote:

Our process looks like this:
1. After the offline backup of the DB is completed, we are FTPing it to
Portland for offsite storage and for use on a standby machine should the
need arise.
2. During the period between offline backups, a complete set of log files are ftp'd to Portland every two hours.

Therefore, and theoretically, if the DB in LA goes down, we can recover all data, that has been logged of course, except maybe for the last 2 hours
worth of transactions.

Here is the problem. The day is no longer lengthy enough to ftp the DB
backup and all of the log files to Portland. The available window has become just too small.

Is there a way we can do this more efficiently thereby increasing the
effective size of our window?

Have you tried compressing your backup (either with the COMPRESS option
or before transmitting (using gzip or bzip2)?

Also, you shouldn't need to send the full backup every night. Just send
the log files, which should be sufficient to keep your standby server
in sync. You could transfer the full backup once a week.

Ian

[johnm]

Good information!

We have not looked at the "overnight" option. That might be a viable way of
accomplishing our goal. We will certainly investigate that furhter.

Thanks,

John

"Phil Sherman" <ps******@ameritech.net> wrote in message
news:vs*****************@newssvr19.news.prodigy.co m...

You didn't state anything about the FTP link. One obvious option is to
increase the size of the "pipe" between the sites. As mentioned in prior
responses; compression will also help. I have also read about cases
where a supposed high speed connection between two distant offices ended
up with a t1 section somewhere in the middle. When this was discovered;
the carrier rerouted to provide the appropriate bandwidth. Today's ADSL
services usually run t1 speeds for receiving but only 10% or less of
that for sending.

A time-honored and frequently viable technique is to back up to portable
media and overnight a copy to the remote location. The overnight costs
need to be weighed against the cost of the communication link needed to
transmit the data.

Phil Sherman

johnm wrote:

Hello All,

I appreciate all of the help with my previous posts. It's nice having such a knowledgeable group to draw upon for help.

I have taken all of your previous suggestions to heart and have upgraded our DB/2 system to 8.2.

However, I still have a problem with backup times and backup window size.
Our DB/2 8.2 system is hosted at a data center in LA. Our main office is in Portland.

Our current DB size is 18 GB.

We have been taking a full offline backup of the DB every evening.

Our process looks like this:
1. After the offline backup of the DB is completed, we are FTPing it to
Portland for offsite storage and for use on a standby machine should the
need arise.
2. During the period between offline backups, a complete set of log files are ftp'd to Portland every two hours.

Therefore, and theoretically, if the DB in LA goes down, we can recover all data, that has been logged of course, except maybe for the last 2 hours
worth of transactions.

Here is the problem. The day is no longer lengthy enough to ftp the DB
backup and all of the log files to Portland. The available window has become just too small.

Is there a way we can do this more efficiently thereby increasing the
effective size of our window?

What is the most efficient way of backing up a DB at a remote location and then transferring the backup files to the "home" office?

We expect the DB to double in size over the coming year.

One last question.

We are also planning on implementing HADR. We figured that would solve this backup problem because while the primary server would be in LA, we would have the secondary server in Portland. We figured we could then backup both the DBs local to the servers. However, we have since read that "Backup
operations are not supported on the standby database". This kind of throws us a curve. Is there a way to backup the standby server? If so, this might solve our entire conundrum.

Thanks for all of your help.

John

[johnm]

Steve,

Ahh...a fellow Portalnder!

Is it possible to backup the standby server using a third party backup
program like Veritas? Or, is it just not possible to back it up at all?

I am also curious as to what prevents it from being backed up. If a third
party backup program is used, it looks like the actual DB/2 system would be
ignorant of the backup and wouldn't care. Where am I wrong with this logic?

Thanks,

John
"Steve Pearson (news only)" <st*******@my-deja.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi, John.

Regarding HADR, if backing up your standby is a requirement for you,
please have your IBM rep formally enter it in the requirement tracking
system. This enhancement is already on the radar, but prioritization
vs. other work can be influenced by the level of customer interest.

Thanks.

Regards,
- Steve P.
-----------------------
Steve Pearson
IBM DB2 UDB Development
Portland, OR, USA

[Steve Pearson (news only)]

Well, yes and no.

You could make what I'll call for lack of a better term
an OS-style backup from the standby. You could use it
to perform an OS-style restore, which would get you back
the standby database with all the bits on disk just as
they were when you made the OS-style backup.

You could even do this with minimal disruption to the
standby if you use "fancy" disk technology to perform
a flash copy, split a mirror, or such, providing the
necessary steps are taken to avoid any partial write.

Note that for this form of backup to be useful, all the
interesting stuff related to the database must also have
been backed up and restored together with it. This means
especially the stuff under the database directory and
the log files. If these don't match the data (proper) of
the database after the restore, you'll have...issues.

Now, presently we do *not* support making a DB2-style database
backup from the standby. The most significant difference
is that a DB2-style backup should be restoreable at either
the primary or standby and useable as the basis for a
rollforward that would bring the database up to date as
of the end of the available logs or as of a certain point
in time.

The same is not supported for an OS-style backup. When
you restore an OS-style backup of an HADR standby database,
what you get is an HADR standby database. We do not support
issuing a ROLLFORWARD command on an HADR standby database.

What you can do with the restored image is restart it again
as an HADR standby database. If it can then reconnect with
the expected HADR primary database, it can rejoin the pair
and start catching up from where it is (time of the backup)
to where the primary is (somewhat later, presumably).

Or, you could turn the restored database into a primary database.
To do this, you would first restart it as a standby
(via ACTIVATE DATABASE), then you would perform a failover-style
takeover (TAKEOVER HADR .. BY FORCE). This would get you an
HADR primary database, which can be connected to and used by
applications. (If you want the database to lose
its HADR flavor altogether, you could then issue a further
STOP HADR command on it.) But note that here the
database will *not* have been caught up with any operations
performed at the original primary after the time of the backup,
and further, once it is made into a primary, it will not be
eligible for a later rollforward operation to perform that
catchup. This is because (a) we don't allow the ROLLFORWARD
command on the database since it is not marked as rollforward
pending, and (b) even if you get under the hood and mark the
database as rollforward pending by dirty trickery, DB2 will not
like the smell of the original log files. This is because when
the takeover occurred, the restored standby database started
off on its own branch of history, and DB2 will detect that it
is not on the same "log chain" if you try to apply the original
primary's log files to it. For similar reasons, it is likely
that after making the restored standby into a primary (via
Takeover) that you would be unable to re-form the original HADR
pair. An attempt to cause the original primary to start as a
standby would fail unless by rare luck it had never performed
any logged operation past the time the standby was backed up, or
it was also restored to such a condition.

Hope that helps explain things. Speak up of anything isn't clear.

Regards,
- Steve P.
-----------------------
Steve Pearson
IBM DB2 UDB Development
Portland, OR, USA

[Steve Pearson (news only)]

> The same is not supported for an OS-style backup.

Doh! Hold the presses, I overlooked another possible command sequence
which does seem to allow for a rollforward after restoring an OS-style
backup. It turns out that the STOP HADR command by itself (on a
standby) *will* leave the database marked rollforward pending (and
doesn't cause the restored copy of the standby database to be on a new
log chain).

So assuming you have access to any later (after time of the backup) log
files from the original primary, which could be retrieved from archive
and/or copied from the active log path of the primary, you should be
able to rollforward the restored standby through those files using a
procedure like this:

< perform OS-style restore >
db2start
db2 activate db <x>
db2 deactivate db <x>
db2 stop hadr on db <x>
< take any steps needed to make later log files of original...>
<...primary available to this server; including overwriting...>
<...the current log file with a copy of same from the...>
<...original primary >
db2 rollforward db <x> to end of logs [and stop]

Regards,
- Steve P.
-----------------------
Steve Pearson
IBM DB2 UDB Development
Portland, OR, USA

[Phil Sherman]

You didn't state anything bout what kind of data you have in the
database. Recent news stories have highlighted a potential need to
encrypt data being overnighted. This gives you additional protection if
the item is lost in transit.

Hmmmm - I wonder if data transmission between locations should also be
encrypted???

Phil Sherman

johnm wrote:

Good information!

We have not looked at the "overnight" option. That might be a viable way of
accomplishing our goal. We will certainly investigate that furhter.

Thanks,

John

"Phil Sherman" <ps******@ameritech.net> wrote in message
news:vs*****************@newssvr19.news.prodigy.co m...

You didn't state anything about the FTP link. One obvious option is to
increase the size of the "pipe" between the sites. As mentioned in prior
responses; compression will also help. I have also read about cases
where a supposed high speed connection between two distant offices ended
up with a t1 section somewhere in the middle. When this was discovered;
the carrier rerouted to provide the appropriate bandwidth. Today's ADSL
services usually run t1 speeds for receiving but only 10% or less of
that for sending.

A time-honored and frequently viable technique is to back up to portable
media and overnight a copy to the remote location. The overnight costs
need to be weighed against the cost of the communication link needed to
transmit the data.

Phil Sherman

[johnm]

This is excellent news!

I figured there "must be a way".

We will try this out. It could potentially solve our "long distence" backup
problem.

Thanks!

John

"Steve Pearson (news only)" <st*******@my-deja.com> wrote in message
news:11********************@g44g2000cwa.googlegrou ps.com...

The same is not supported for an OS-style backup.

Doh! Hold the presses, I overlooked another possible command sequence
which does seem to allow for a rollforward after restoring an OS-style
backup. It turns out that the STOP HADR command by itself (on a
standby) *will* leave the database marked rollforward pending (and
doesn't cause the restored copy of the standby database to be on a new
log chain).

So assuming you have access to any later (after time of the backup) log
files from the original primary, which could be retrieved from archive
and/or copied from the active log path of the primary, you should be
able to rollforward the restored standby through those files using a
procedure like this:

< perform OS-style restore >
db2start
db2 activate db <x>
db2 deactivate db <x>
db2 stop hadr on db <x>
< take any steps needed to make later log files of original...>
<...primary available to this server; including overwriting...>
<...the current log file with a copy of same from the...>
<...original primary >
db2 rollforward db <x> to end of logs [and stop]

Regards,
- Steve P.
-----------------------
Steve Pearson
IBM DB2 UDB Development
Portland, OR, USA

[johnm]

This is excellent news!

We will give it a try and see how it works.

I just knew there "had to be a way".

It could solve our "long distence" backup problem.

Thanks,

John

"Steve Pearson (news only)" <st*******@my-deja.com> wrote in message
news:11********************@g44g2000cwa.googlegrou ps.com...

The same is not supported for an OS-style backup.

Doh! Hold the presses, I overlooked another possible command sequence
which does seem to allow for a rollforward after restoring an OS-style
backup. It turns out that the STOP HADR command by itself (on a
standby) *will* leave the database marked rollforward pending (and
doesn't cause the restored copy of the standby database to be on a new
log chain).

So assuming you have access to any later (after time of the backup) log
files from the original primary, which could be retrieved from archive
and/or copied from the active log path of the primary, you should be
able to rollforward the restored standby through those files using a
procedure like this:

< perform OS-style restore >
db2start
db2 activate db <x>
db2 deactivate db <x>
db2 stop hadr on db <x>
< take any steps needed to make later log files of original...>
<...primary available to this server; including overwriting...>
<...the current log file with a copy of same from the...>
<...original primary >
db2 rollforward db <x> to end of logs [and stop]

Regards,
- Steve P.
-----------------------
Steve Pearson
IBM DB2 UDB Development
Portland, OR, USA