Log Shipping - database expansion

[Richard]

Log shipping was discussed in this post from 2008 but I couldn't find
a reply link. Hmm. Maybe it's too old. No matter, my question is
specific to replicating database size increases.

http://groups.google.com/group/sybase.public.ase.administration/browse_thread/thread/0a5dd928f6744d28/4db23ea66134da7d?pli=1

I'm wondering what folks, with an existing log shipping solution, do
to replicate increases in a database size from the primary to
secondary. Commands like "alter database ABC on DEV4 = 10" aren't
captured in ABC's log (likely in master's log).

I had incorrectly assumed that I could continue to apply logs on the
secondary database (even after the primary database has been
expanded), as long as the secondary database has space for the data
changes. That doesn't appear to be the case.

Here are the steps I performed (ASE 15.0.2 on AIX),

1 - created a database with one table
- 50Mb data, 10 Mb log
- table with one 'int' column
2 - full backup
3 - inserted one row
4 - tran backup 1
5 - inserted another row
6 - tran backup 2
7 - extended database data segment by 10 Mb
8 - inserted another row
9 - tran backup 3

Did a restore and roll forward with these results:

1 - full restore
2 - tran 1 restore
- success
3 - tran 2 restore
- success
4 - tran 3 restore
- failed - "Data on dump will not fit into current
database. Need 70 Mbyte database."
5 - extended database by 10 Mb
6 - tran 3 restore
- success
7 - online database

We plan to have a process on the primary shipping the logs and a
process on the secondary loading them (one process per database on
each side).

How does your log shipping solution deal with this issue.

Thanks so much. :-)

[Antony]

We just make it standard practice to alter the database at the same time. So when you alter Prod, you alter DR at the same time. It ususally works :)
Altering databases isn't the sort of things you should be doing daily anyway - nor even weekly. We normally add 2-3 months worth of space at a time.

[Richard]

Thanks for the reply. :-)

I guess the practice should be to extend the secondary, then the
primary. That will ensure that a tranlog dump can't make it across
and fail because of a database sizing issue.

We also only add space every few months, so it shouldn't be a big
issue. I could create a script that periodically extracts the segment
layout for each database on both sites, and then compares the two.

Does your log shipping software do lots of monitoring? I can envision
a process on the primary side that makes sure that log dumps are being
done regularly (in our case, every five minutes). Same sort of thing
on the secondary side that checks that logs are arriving every five
minutes and that they're successfully loaded. We plan to have the
monitor at each site also ensure that the monitor at the other site is
running, as well as a backup monitor at each site ensuring that the
local monitor is running. Sounds like overkill but we've shooting for
a 15 minute failover window. Can't risk finding that a tran log
process died two hours ago.

I also plan to have a bcp script that grabs copies of syslogins,
sysloginroles, syssrvroles, sysservers, and sysremotelogins every 15
minutes as well as copying the config files at the same time (for
loading in the secondary in a disaster). Also bcp a copy of
sysdevcies, sysusages, sysdatabases for reference if things aren't
working right. Do you do anything like that?

Anything else you have in place?

[Antony]

We have custom processes that we wrote do the dumps (among many other things) ... the process dumps the tran (or db) and uses XP server to scp the dump files across to DR as soon as the dump is complete. Any errors send us an email and sms alert.
On the DR side, we have a simpler script which polls a directory for new tran dump files, loads them, and then renames them. Any error results in an email alert(only). That way, at least we know the tran logs are on the DR host - even if the load fails.

For system tables in master, we take text copies of all that you mention plus a few others (some of dubious value!)

For devices, databases etc. we also reverse engineer the sql to create devices and databases - this is more useful, since we can fire it at a dev/test server to create and load a prod copy quickly.

HTH.

[Richard]

Thanks again for the reply.

Anyone else have input? :-)

[RJha]

On Feb 8, 11:35 pm, Richard <rmcgor...@gmail.com> wrote:
> Thanks again for the reply.
>
> Anyone else have input? :-)

Another ways to maintain data consistency at DR sides.

1. You can use replication server (able to maintain data copy at
multiple sites)
2. You can also do the data copy at SAN level using SAN technology
like SRDF in EMC world.
3. Automatic in-house build process similar to above discussions
require extra DBA support and coding expertise in shell/perl/etc..

[Richard]

On Feb 14, 11:00 pm, RJha <resheersh....@gmail.com> wrote:
> Another ways to maintain data consistency at DR sides.
>
> 1. You can use replication server  (able to maintain data copy at
> multiple sites)

Thanks for the reply.

We looked at Rep-Server. In fact, we trialed the product. We had
three issues:
- cost (priced based on cores on box, not CPU's allocated to
lpar. An lpar
running with one CPU on hardware with eight cores would be
priced based
on the 8 cores, not the one CPU).
- it's a complex product. High level of understanding required,
particularly if
something goes wrong. Seemed like overkill for the challenge we
were
trying to address.
- we break the transaction chain in a number of databases during
our nightly
processing. Re-establishing syncronization after that was not a
simple
process, that we felt would require non-trivial shell scripting
work

> 2. You can also do the data copy at SAN level using SAN technology
> like SRDF in EMC world.

Our sites are 400 miles apart. We were told network latency would
impact
app performance.

> 3. Automatic in-house build process similar to above discussions
> require extra DBA support and coding expertise in shell/perl/etc..

Can't argue with that. :-) Always good to become more competent
at something like shell scripting. :-)

[Antony]

On Friday, 17 February 2012 09:37:34 UTC+13, Richard wrote:
> On Feb 14, 11:00 pm, RJha <resheersh....@gmail.com> wrote:
> Thanks for the reply.
>
> We looked at Rep-Server. In fact, we trialed the product. We had
> three issues:
> - cost (priced based on cores on box, not CPU's allocated to lpar. An lpar running with one CPU on hardware with eight cores would be
> priced based on the 8 cores, not the one CPU).

As of RS 15.5 Sybase now support sub-capacity licensing - so a 1 core VM on an 8 core host will only cost 1 core license. Logical partitions are also supported I think (e.g. Solaris containers).

> - it's a complex product. High level of understanding required,
> particularly if something goes wrong. Seemed like overkill for the challenge we were trying to address.

Warm standby setup is very simple. It's as complex as you want to make it really - though you do need to get used to the product, it's nothing like ASE :)

> - we break the transaction chain in a number of databases during
> our nightly processing. Re-establishing syncronization after that was not a
> simple process, that we felt would require non-trivial shell scripting work

Do you know why this happens? Not everything is 100% compatible with replication (whether viw RepServer or other), but most things should work. If it's just Warm Standby you're using, you can sync with dump/load right? Not that hard, but it is time consuming if the db is large.

>
> > 2. You can also do the data copy at SAN level using SAN technology
> > like SRDF in EMC world.
>
> Our sites are 400 miles apart. We were told network latency would
> impact app performance.
>

Indeed. You would need to use SRDF/S (synchronous) which hammers your IO throughput. Sybase do not support SRDF/A (asynchronous) as the database may be corrupted if the disk pairs are not in sync when split.