Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ASE Replicator materialization using dump/load?

4 posts in replicator Last posting was on 2007-06-19 17:04:01.0Z
Michael C. Matthews Posted on 2007-06-18 22:35:19.0Z
From: "Michael C. Matthews" <michael.matthews@chron.com>
User-Agent: Thunderbird 1.5.0.12 (X11/20070604)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.replicator
Subject: ASE Replicator materialization using dump/load?
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 130.80.28.26
X-Original-NNTP-Posting-Host: 130.80.28.26
Message-ID: <467708a7$1@forums-1-dub>
Date: 18 Jun 2007 15:35:19 -0700
X-Trace: forums-1-dub 1182206119 130.80.28.26 (18 Jun 2007 15:35:19 -0700)
X-Original-Trace: 18 Jun 2007 15:35:19 -0700, 130.80.28.26
Lines: 108
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.replicator:176
Article PK: 103382

I'm trying to set up ASE Replicator for a fairly large database (~20
GB), so I'd like to materialize it using dump and load rather than
sp_materialize (if I use sp_materialize on empty tables on the replicate
database, the transaction log fills up quickly as it tries to insert a
couple of million records.)

The problem I'm having is that I'd like to avoid having to take the
primary database out of service while materializing, since the dump/load
process itself takes about an hour. Unfortunately, I haven't been able
to figure out the "correct" way to do this to ensure that no
transactions are lost during the materialization process.

Here's the procedure I've been trying:

Primary server: PS (ASE 12.5.3)
Replicate server: RS (ASE 15.0.1)
Database to be replicated: DB (same name on both servers)
Publication and subscription names also the same as the database name.
Maintenance user/login: rpl

1) Disable any existing replication for "PS.DB" -> "RS.DB"
sp_suspendreplicateconn "RS.DB"
sp_suspendprimaryconn "PS.DB"
sp_dropreplicateart DB
sp_dropsub DB
sp_dropprimaryart DB
sp_droppub DB
sp_dropreplicateconn "RS.DB"
sp_dropprimaryconn "PS.DB"

2) Set up rpl user in PS DB
use DB
sp_adduser rpl
grant create procedure to rpl

3) Grant permissions to rpl user in PS DB
use DB
grant select on <each replicated table> to rpl

4) Create primary connection "PS.DB"
sp_addprimaryconn "PS.DB"

5) Create primary publication and articles
sp_addpub DB, "PS.DB"
sp_addprimaryart DB, <each replicated table>

6) Start primary connection
sp_resumeprimaryconn "PS.DB"

7) Dump primary database DB to a file
dump database DB to "/backdir/DB.dmp" with init

8) Load replicate database DB from file at PS_back server
kill all processes on RS that are using DB
load database DB from "/backdir/DB.dmp" at PS_back
dbcc dbrepair(DB, "ltmignore")
online database DB

9) Post-materialization cleanup (truncate/drop some unneeded tables, etc.)

10) Grant permissions on replicate tables
use DB
grant select on syspartitions to rpl
grant create table to rpl
grant all on <each replicated table> to rpl

11) Create replicate connection
sp_addreplicateconn "RS.DB"

12) Create replicate subscription and articles
sp_addsub DB, DB, "RS.DB"
sp_addreplicateart DB, <each replicated table>

13) Start up replication
sp_validatesub DB
sp_resumerep

At this point, replication seems to be working; changes made to the
primary show up in the replicate database within a few minutes.

Unfortunately, it looks like all the transactions that happened on the
primary database during the time the replicate database was being loaded
and validated (steps 8-13, about 45 minutes) disappeared.

I guess I understand this -- sp_validatesub would logically be telling
ASE Replicator that everything up to that point in time has been
replicated, and it should start collecting new transactions at that point.

So what (if anything) can I do to make ASE Replicator catch those
transactions?

I've considered validating before the dump (or perhaps immediately after
the dump but before the load), and then archiving the contents of
rpl.rl_lastcommit to a temp table before doing the load so I can
recreate the replicate articles and restore rl_lastcommit after the
load, but I'm not sure whether I can make that work. Is there a better
(or even a supported) way?

I'd appreciate any guidance...

Incidentally, because some of my largest tables include TEXT columns, I
also had to use a modified version of sp_setreptable in order to create
the primary articles without blocking the primary database for an
unacceptably long time:
http://groups.google.com/group/sybase.public.rep-server/browse_thread/thread/52ef7958a8b8dcff/ea579951137c1c28?lnk=st&q=%22sp_setreptable%22+text&rnum=2&hl=en#ea579951137c1c28
--
Michael Matthews
http://www.chron.com


"Mark A. Parsons" <iron_horse Posted on 2007-06-19 00:07:37.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.replicator
Subject: Re: ASE Replicator materialization using dump/load?
References: <467708a7$1@forums-1-dub>
In-Reply-To: <467708a7$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ool-4357fce9.dyn.optonline.net
X-Original-NNTP-Posting-Host: ool-4357fce9.dyn.optonline.net
Message-ID: <46771e49@forums-1-dub>
Date: 18 Jun 2007 17:07:37 -0700
X-Trace: forums-1-dub 1182211657 67.87.252.233 (18 Jun 2007 17:07:37 -0700)
X-Original-Trace: 18 Jun 2007 17:07:37 -0700, ool-4357fce9.dyn.optonline.net
Lines: 121
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.replicator:177
Article PK: 103381

Other than some quick tests I really haven't used Replicator much but fwiw ...

After you've loaded the database dump, but before bringing the replicate database online, consider performing a
transaction log dump of the primary and then loading this into the replicate database. Continue this dump/load of
transactions until you get down to a small window to complete the dump-n-load.

Then when you're ready to do the replication part you would bring the replicate db online. ("Duh, Mark!" ?)

I would imagine that some combination of sp_validatesub/sp_suspendrep/sp_resumerep used in conjunction with the
dump-n-load of the transaction log should get you to the point where you have zero data loss. Obviously (?) performing
these last steps would be easier if/when there's little activity in the primary database.

Michael C. Matthews wrote:
> I'm trying to set up ASE Replicator for a fairly large database (~20
> GB), so I'd like to materialize it using dump and load rather than
> sp_materialize (if I use sp_materialize on empty tables on the replicate
> database, the transaction log fills up quickly as it tries to insert a
> couple of million records.)
>
> The problem I'm having is that I'd like to avoid having to take the
> primary database out of service while materializing, since the dump/load
> process itself takes about an hour. Unfortunately, I haven't been able
> to figure out the "correct" way to do this to ensure that no
> transactions are lost during the materialization process.
>
> Here's the procedure I've been trying:
>
> Primary server: PS (ASE 12.5.3)
> Replicate server: RS (ASE 15.0.1)
> Database to be replicated: DB (same name on both servers)
> Publication and subscription names also the same as the database name.
> Maintenance user/login: rpl
>
> 1) Disable any existing replication for "PS.DB" -> "RS.DB"
> sp_suspendreplicateconn "RS.DB"
> sp_suspendprimaryconn "PS.DB"
> sp_dropreplicateart DB
> sp_dropsub DB
> sp_dropprimaryart DB
> sp_droppub DB
> sp_dropreplicateconn "RS.DB"
> sp_dropprimaryconn "PS.DB"
>
> 2) Set up rpl user in PS DB
> use DB
> sp_adduser rpl
> grant create procedure to rpl
>
> 3) Grant permissions to rpl user in PS DB
> use DB
> grant select on <each replicated table> to rpl
>
> 4) Create primary connection "PS.DB"
> sp_addprimaryconn "PS.DB"
>
> 5) Create primary publication and articles
> sp_addpub DB, "PS.DB"
> sp_addprimaryart DB, <each replicated table>
>
> 6) Start primary connection
> sp_resumeprimaryconn "PS.DB"
>
> 7) Dump primary database DB to a file
> dump database DB to "/backdir/DB.dmp" with init
>
> 8) Load replicate database DB from file at PS_back server
> kill all processes on RS that are using DB
> load database DB from "/backdir/DB.dmp" at PS_back
> dbcc dbrepair(DB, "ltmignore")
> online database DB
>
> 9) Post-materialization cleanup (truncate/drop some unneeded tables, etc.)
>
> 10) Grant permissions on replicate tables
> use DB
> grant select on syspartitions to rpl
> grant create table to rpl
> grant all on <each replicated table> to rpl
>
> 11) Create replicate connection
> sp_addreplicateconn "RS.DB"
>
> 12) Create replicate subscription and articles
> sp_addsub DB, DB, "RS.DB"
> sp_addreplicateart DB, <each replicated table>
>
> 13) Start up replication
> sp_validatesub DB
> sp_resumerep
>
> At this point, replication seems to be working; changes made to the
> primary show up in the replicate database within a few minutes.
>
> Unfortunately, it looks like all the transactions that happened on the
> primary database during the time the replicate database was being loaded
> and validated (steps 8-13, about 45 minutes) disappeared.
>
> I guess I understand this -- sp_validatesub would logically be telling
> ASE Replicator that everything up to that point in time has been
> replicated, and it should start collecting new transactions at that point.
>
> So what (if anything) can I do to make ASE Replicator catch those
> transactions?
>
> I've considered validating before the dump (or perhaps immediately after
> the dump but before the load), and then archiving the contents of
> rpl.rl_lastcommit to a temp table before doing the load so I can
> recreate the replicate articles and restore rl_lastcommit after the
> load, but I'm not sure whether I can make that work. Is there a better
> (or even a supported) way?
>
> I'd appreciate any guidance...
>
> Incidentally, because some of my largest tables include TEXT columns, I
> also had to use a modified version of sp_setreptable in order to create
> the primary articles without blocking the primary database for an
> unacceptably long time:
> http://groups.google.com/group/sybase.public.rep-server/browse_thread/thread/52ef7958a8b8dcff/ea579951137c1c28?lnk=st&q=%22sp_setreptable%22+text&rnum=2&hl=en#ea579951137c1c28
>


Michael C. Matthews Posted on 2007-06-19 15:38:51.0Z
Message-ID: <4677F88A.6020501@chron.com>
From: "Michael C. Matthews" <michael.matthews@chron.com>
User-Agent: Thunderbird 1.5.0.12 (X11/20070604)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.replicator
To: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
Subject: Re: ASE Replicator materialization using dump/load?
References: <467708a7$1@forums-1-dub> <46771e49@forums-1-dub>
In-Reply-To: <46771e49@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 130.80.28.26
X-Original-NNTP-Posting-Host: 130.80.28.26
Date: 19 Jun 2007 08:38:51 -0700
X-Trace: forums-1-dub 1182267531 130.80.28.26 (19 Jun 2007 08:38:51 -0700)
X-Original-Trace: 19 Jun 2007 08:38:51 -0700, 130.80.28.26
Lines: 57
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.replicator:178
Article PK: 103384

That's a really good idea -- unfortunately, I haven't had any success in
loading transaction log dumps from my 12.5.3 server onto my 15.0.1
server. I get this:

1> load transaction DB from '/backdir/DB_trans.dmp' at PS_back
Msg 968, Level 16, State 1:
Server 'RS', Line 1:
Database 'database 'DB' (35)' is not upgraded and hence is not available
for access. Please retry your query after database has been upgraded.

Mark A. Parsons wrote:
> Other than some quick tests I really haven't used Replicator much but
> fwiw ...
>
> After you've loaded the database dump, but before bringing the replicate
> database online, consider performing a transaction log dump of the
> primary and then loading this into the replicate database. Continue
> this dump/load of transactions until you get down to a small window to
> complete the dump-n-load.
>
> Then when you're ready to do the replication part you would bring the
> replicate db online. ("Duh, Mark!" ?)
>
> I would imagine that some combination of
> sp_validatesub/sp_suspendrep/sp_resumerep used in conjunction with the
> dump-n-load of the transaction log should get you to the point where you
> have zero data loss. Obviously (?) performing these last steps would be
> easier if/when there's little activity in the primary database.
>
>
>
> Michael C. Matthews wrote:
>> I'm trying to set up ASE Replicator for a fairly large database (~20
>> GB), so I'd like to materialize it using dump and load rather than
>> sp_materialize (if I use sp_materialize on empty tables on the
>> replicate database, the transaction log fills up quickly as it tries
>> to insert a couple of million records.)
>>
>> The problem I'm having is that I'd like to avoid having to take the
>> primary database out of service while materializing, since the
>> dump/load process itself takes about an hour. Unfortunately, I
>> haven't been able to figure out the "correct" way to do this to ensure
>> that no transactions are lost during the materialization process.
>>
>> Here's the procedure I've been trying:
>>
>> Primary server: PS (ASE 12.5.3)
>> Replicate server: RS (ASE 15.0.1)
>> Database to be replicated: DB (same name on both servers)
>> Publication and subscription names also the same as the database name.
>> Maintenance user/login: rpl
>>

--
Michael Matthews
http://www.chron.com


Michael C. Matthews Posted on 2007-06-19 17:04:01.0Z
Message-ID: <46780C80.6000907@chron.com>
From: "Michael C. Matthews" <michael.matthews@chron.com>
User-Agent: Thunderbird 1.5.0.12 (X11/20070604)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.replicator
To: "Michael C. Matthews" <michael.matthews@chron.com>
Subject: Re: ASE Replicator materialization using dump/load?
References: <467708a7$1@forums-1-dub> <46771e49@forums-1-dub> <4677F88A.6020501@chron.com>
In-Reply-To: <4677F88A.6020501@chron.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 130.80.28.26
X-Original-NNTP-Posting-Host: 130.80.28.26
Date: 19 Jun 2007 10:04:01 -0700
X-Trace: forums-1-dub 1182272641 130.80.28.26 (19 Jun 2007 10:04:01 -0700)
X-Original-Trace: 19 Jun 2007 10:04:01 -0700, 130.80.28.26
Lines: 20
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.replicator:179
Article PK: 103383

Oops -- I've solved this part of the problem, at least. It turns out
that I was using a login without OPER role to do this transaction load;
I was confused by the error message. These loads work when using an
appropriately authorized login, so I'm going to try Mark's suggestion.

Michael C. Matthews wrote:
> That's a really good idea -- unfortunately, I haven't had any success in
> loading transaction log dumps from my 12.5.3 server onto my 15.0.1
> server. I get this:
>
> 1> load transaction DB from '/backdir/DB_trans.dmp' at PS_back
> Msg 968, Level 16, State 1:
> Server 'RS', Line 1:
> Database 'database 'DB' (35)' is not upgraded and hence is not available
> for access. Please retry your query after database has been upgraded.
>
>

--
Michael Matthews
http://www.chron.com