Mirror *and* Backup?

Mirror *and* Backup?

Post by Keith Kendal » Fri, 05 Jun 1998 04:00:00



We're going to have a ASE NT server in the field and need to make it as
self-sufficient as possible. One issue is preventing the transaction
logs from filling and, essentially, shutting down the system. We've
identified two options, which are listed below. We'd appreciate any
suggestions for other methods or reasons to consider one over the other.

1. Mirror the entire database to a different physical disk on a separate
controller and set sp_dboption <dbname>, "trunc log on chkpt", true.
(Does this obviate the need for data dumps?)

2. Set a threshold on each transaction log that will initiate a dump of
the offending log.

Thanks
Keith Kendall

 
 
 

Mirror *and* Backup?

Post by Philip Wan » Fri, 05 Jun 1998 04:00:00


Keith,

         I suggest you take a look at the Sybase ASE System Administration
Guide - available at Sybase's website for some suggestions on backup and
recovery.


> We're going to have a ASE NT server in the field and need to make it as
> self-sufficient as possible. One issue is preventing the transaction
> logs from filling and, essentially, shutting down the system. We've
> identified two options, which are listed below. We'd appreciate any
> suggestions for other methods or reasons to consider one over the other.

> 1. Mirror the entire database to a different physical disk on a separate
> controller and set sp_dboption <dbname>, "trunc log on chkpt", true.
> (Does this obviate the need for data dumps?)

Mirroring protects against media failure on either your primary or
secondary device. This is not the same as, nor a replacement for, doing
backups or data dumps. You should schedule regular (nightly? weekly?)
database dumps. You can go with "trunc log on chkpt" combined with
thresholding if you don't care about recoverability between full dumps.

Quote:

> 2. Set a threshold on each transaction log that will initiate a dump of
> the offending log.

This is ok if you don't care about recoverability between full dumps -
see above.

> Thanks
> Keith Kendall


I realize that you want minimal maintenance for this server. Unless you
don't care at all if this server is recoverable upon failure, you should
have some means to rebuild the server.

Good luck!
--

===================
     Phil Wang
===================

 
 
 

1. Enterprise readiness - mirroring / incremental backup solutions?

I'm currently starting to evaluate Open Source RDBMSs for use in a
high-volume, high-availability environment.

My main requirements are:

1. Ability to store approx 200Gb of data, with about 5Gb of data
changing per day.

2. Support for high number of concurrent short transactions under
REPEATABLE READ transaction isolation with row-level locking (or
equivalent optimistic concurrency control).

3. Fast (i.e. < 5 mins) failover time to a constantly mirrored secondary
database server.

4. Ability to perform continous network backups such that in the event
of both the primary database server and mirrored database server
suffering total failure, no more than 1 hour of data is lost.

First impressions are that PostgreSQL (and SAP DB, but definitely not
MySQL) appears to meet requirements 1 & 2, but I'm not sure whether it
(or any Open Source db) can currently meet requirements 3 & 4.

My understanding is that while PostgreSQL offers hot backups "out of the
box", it only offers full backups and does not have built in support for
mirroring. Clearly, backing up 200Gb of data hourly is not feasible.

Are there any third part solutions capable of making PostgreSQL meet
requirements 3 & 4?

I'd imagine it may be possible to satisfy 3. using file system level
mirroring, but I'd appreciate it if someone could confirm this.

My last question is somewhat pie-in-the sky, but assuming that
PostgreSQL cannot currently meet requirements 3 & 4 even with 3rd party
solutions, what are people's gut reactions to whether a small team (e.g.
5-6) of experienced, full-time paid developers could add mirroring and
incremental backup support to PostgreSQL within 18 months?

Cheers,
Kieran Elby

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

2. bcp precision in the OS2 environment.

3. Guaranteeing DB consistency with Split Mirror Online backups

4. system DSNs

5. mirroring-backup-replication

6. Emulating Linked server functionality with 6.5

7. Split-Mirror Backup

8. isolation level ( dirty read ) on OPS node

9. replication-backup-mirroring

10. backup & mirroring

11. halting SQL server disks for split-mirror backup