Sybase point in time recovery over multiple subject area databases

[Gerald Whyte]

We have a large application with the data split over multiple subject area
databases on the same ASE 15 server.



Analysts perform updates using



BEGIN TRAN

Update Table A in Dabase01

Update Table B in Database02

COMMIT TRAN



If we have a crash and wish to roll forward to a specific point in time e.g.
14:00 how does Sybase ensure that consistency is maintained between the two
databases and their respective logs given that a gap of several milliseconds
could exist between the log records being written for DatabaseA and
DatabaseB so that one set of log records is just before 14:00 and one set
just a few milliseconds after 14:00.



Is this kind of recovery coordinated through the systransactions table?





Thanks for any response

[Rob Verschoor]

Assuming we're talking about boot-time recovery, each database is recovered
one by one for its entire transaction log. You cannot specify a cutoff point
for a certain time (except if you'd be prepared to go through some really
heavy, and possibly dangerous, trickery -- that's a different way of saying
this is not feasible).
When all databases have been recovered you have a situation whereby the
latest committed transaction before the crash is indeed committed and all
uncommitted modifications after the last commit are undone.

HTH,

Rob V.
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:***@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

Post by Gerald Whyte
We have a large application with the data split over multiple subject area
databases on the same ASE 15 server.
Analysts perform updates using
BEGIN TRAN
Update Table A in Dabase01
Update Table B in Database02
COMMIT TRAN
If we have a crash and wish to roll forward to a specific point in time
e.g. 14:00 how does Sybase ensure that consistency is maintained between
the two databases and their respective logs given that a gap of several
milliseconds could exist between the log records being written for
DatabaseA and DatabaseB so that one set of log records is just before
14:00 and one set just a few milliseconds after 14:00.
Is this kind of recovery coordinated through the systransactions table?
Thanks for any response

[Mark A. Parsons]

I got the impression the OP was referring to a recovery from dumps (eg, the system crashed so let's rebuild from
database and transaction log files).

If this is what the OP is referring to then each database could limit transaction loads to a specific point-in-time.

While it would be *possible* to get all databases recovered to the same point in time I'm not aware of any way to
*guarantee* this type of multi-database recovery, ie, Sybase does not maintain any sort of interdatabase transactional
consistency during the loading of database and transaction loads.

Post by Rob Verschoor
Assuming we're talking about boot-time recovery, each database is recovered
one by one for its entire transaction log. You cannot specify a cutoff point
for a certain time (except if you'd be prepared to go through some really
heavy, and possibly dangerous, trickery -- that's a different way of saying
this is not feasible).
When all databases have been recovered you have a situation whereby the
latest committed transaction before the crash is indeed committed and all
uncommitted modifications after the last commit are undone.
HTH,
Rob V.
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

Post by Gerald Whyte
We have a large application with the data split over multiple subject area
databases on the same ASE 15 server.
Analysts perform updates using
BEGIN TRAN
Update Table A in Dabase01
Update Table B in Database02
COMMIT TRAN
If we have a crash and wish to roll forward to a specific point in time
e.g. 14:00 how does Sybase ensure that consistency is maintained between
the two databases and their respective logs given that a gap of several
milliseconds could exist between the log records being written for
DatabaseA and DatabaseB so that one set of log records is just before
14:00 and one set just a few milliseconds after 14:00.
Is this kind of recovery coordinated through the systransactions table?
Thanks for any response

[Rob Verschoor]

Indeed, if we're talking about reloading dumps, then the answer is that ASE
does not guarantee anything at all here. You can load transaction log dumps
with the until_time= --or better in this case: the until_rid= option-- but
you'll still need to sort out yourself up to what point you need to recover.
The background is that recovery is performed only on an individual database
when loading dumps, and relation to othr databases are not taken into
account.

Rob V.

Post by Mark A. Parsons
I got the impression the OP was referring to a recovery from dumps (eg, the
system crashed so let's rebuild from database and transaction log files).
If this is what the OP is referring to then each database could limit
transaction loads to a specific point-in-time.
While it would be *possible* to get all databases recovered to the same
point in time I'm not aware of any way to *guarantee* this type of
multi-database recovery, ie, Sybase does not maintain any sort of
interdatabase transactional consistency during the loading of database and
transaction loads.

Post by Rob Verschoor
Assuming we're talking about boot-time recovery, each database is recovered
one by one for its entire transaction log. You cannot specify a cutoff point
for a certain time (except if you'd be prepared to go through some really
heavy, and possibly dangerous, trickery -- that's a different way of saying
this is not feasible).
When all databases have been recovered you have a situation whereby the
latest committed transaction before the crash is indeed committed and all
uncommitted modifications after the last commit are undone.
HTH,
Rob V.
-------------------------------------------------------------
Rob Verschoor
Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

Post by Gerald Whyte
We have a large application with the data split over multiple subject area
databases on the same ASE 15 server.
Analysts perform updates using
BEGIN TRAN
Update Table A in Dabase01
Update Table B in Database02
COMMIT TRAN
If we have a crash and wish to roll forward to a specific point in time
e.g. 14:00 how does Sybase ensure that consistency is maintained between
the two databases and their respective logs given that a gap of several
milliseconds could exist between the log records being written for
DatabaseA and DatabaseB so that one set of log records is just before
14:00 and one set just a few milliseconds after 14:00.
Is this kind of recovery coordinated through the systransactions table?
Thanks for any response

[Vojislav Depalov]

Hi,

I can tell You how I avoided that problem:
- there are two databases(primary and secondary)

- we do not directly update data in both databases, but instead of that,
we have triggers in primary database to update(or insert) data in
secondary database. It means that there secondary database is never
directly updated, only through primary database. By using this, we can
be sure that only if primary database is OK, secondary database will we
updated. In case that primary is not OK, secondary won't be updated. In
this way we can be sure that data in primary and secondary database are
aligned. Very big drawback in this solution is that you have to mix RI
parts of triggers with that part which updates(or inserts) data into
secondary database.

- during reloading dumps(or during recovery time) we first reload (or
recover with sp_dbrecovery_order) secondary database, and then primary.
In that way we are sure that every update(insert) which goes to primary
database will be written to secondary database.


I am not sure is this the answer to your question, but I'll be glad if
you find something useful for You.

Vojislav Depalov

Post by Gerald Whyte
We have a large application with the data split over multiple subject area
databases on the same ASE 15 server.
Analysts perform updates using
BEGIN TRAN
Update Table A in Dabase01
Update Table B in Database02
COMMIT TRAN
If we have a crash and wish to roll forward to a specific point in time e.g.
14:00 how does Sybase ensure that consistency is maintained between the two
databases and their respective logs given that a gap of several milliseconds
could exist between the log records being written for DatabaseA and
DatabaseB so that one set of log records is just before 14:00 and one set
just a few milliseconds after 14:00.
Is this kind of recovery coordinated through the systransactions table?
Thanks for any response

[unknown]

I am referring to a recovery after a crash where you restore
from the last full backup then apply the logs.

Under Microsoft SQL Server 2005 there is an option to use
“marked transactions” where it is possible to
synchronize a point in all of the related database logs so
that they all can be rolled forward to the exact same point
on recovery.

Does Sybase offer any similar option?


Thanks again Gerald

[Rob Verschoor]

You can load the database transaction log dumps up to a certain point in
time, using "load transaction...with until_time='<datetime value>' ". You
can also load up to a specific log record with "until_rid=<log record ID>".
However, both are for an individual database, and entirely up to your
discretion.
ASE does not consider anything outside the database being recovered.

HTH,

Rob V.
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5 / TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:***@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

<Gerald Whyte> wrote in message news:***@sybase.com...
I am referring to a recovery after a crash where you restore
from the last full backup then apply the logs.

Under Microsoft SQL Server 2005 there is an option to use
"marked transactions" where it is possible to
synchronize a point in all of the related database logs so
that they all can be rolled forward to the exact same point
on recovery.

Does Sybase offer any similar option?


Thanks again Gerald