secondary truncation point questions
M Corey
in my test environment, and one of the things I tried was to simulate a
failure of the primary database, with the restore of the primary from a
backup and a restore of the standby from the same backup. Below are my
steps:
1. From the replication server:
suspend connection to primary_ds.primary_db
suspend connection to standby_ds.standby_db
2. From the primary database server:
sp_stop_rep_agent dbname
3. From the primary database server:
load database dbname from <backup>
online database dbname
4. From the standby database server:
load database dbname from <backup>
online database dbname
sp_dropuser maint_user
sp_addalias dbo,maint_user
5. From the replication server:
resume connection to primay_ds.primary_db
resume connection to standby_ds.standby_db
6. From the primary database server:
sp_start_rep_agent dbname
Now, this scenario worked and everything was find except for one thing.
Later I tried stopping the rep agent on the primary and loading a lot of
data into the database. Well the first problem was that the transaction log
on the primary filled up (even though the sp_thresholdaction was working).
I did a select * from master..syslogshold and found that the longest running
transaction is this:
1> select * from master..syslogshold
2> go
dbid reserved spid page xactid masterxactid
starttime
name
------ ----------- ------ ----------- -------------- --------------
--------------------------
-------------------------------------------------------------------
38 0 0 75761 0x000000000000 0x000000000000
Oct 9 2000 3:20PM
$replication_truncation_point
So, I am assuming that even with replication stop (sp_stop_rep_agent), the
transaction log would not truncate properly, so I had to restart the rep
agent. This then caused the transaction log on the standby to fill!!!
Which has the same problem (the secondary truncation point is listed in the
syslogshold table) Which is where I am at now. At the moment my only
recourse is to increase the transaction log on the standby (good thing this
is a test environment), but I now have several questions.
1. Is the only way to stop replication (let's say for a period of one week
as you move a server from east coast to west coast), is to use the
sp_config_rep_agent disable procedure? What about the secondary truncation
point - can you preserve it during this period, and what happened above not
occur? Or do you have to remove the secondary truncation point altogether?
2. When I did a load of the standby from the primary, did it copy the
secondary truncation point of the primary database over? What steps did I
do wrong or should have added? Should there be a secondary truncation point
in the standby? And why does this not occur for the RSSD? In repserver
class the instructor stated when setting up a warm-standby to configure the
rep agent for the RSSD even if you are not replicating the database, thus
this configuration must place the secondary truncation point into the RSSD,
yet I am not having the same problem (I guess) with the RSSD.
Thanks for any and all help.
Michael Lee (KMB)
To reset the secondary truncation point, you can issue the following command
:
DBCC SETTRUNC('LTM',ignore) to the DB.
This command work with old version of ASE (11.0.3) where Rep Agent not
available yet. I think it should work for newer version ASE.
Hope this can help.
M Corey <mco...@edoc.com> wrote in message
news:Hw61FMk...@forums.sybase.com...
Michael Lee (KMB)
settrunc(ltm,'ignore').
Michael Lee (KMB) <micha...@kmb.com.hk> wrote in message
news:c893$rxMAH...@forums.sybase.com...
M Corey
Thanks, I will try this, but since the secondary truncation point was set
via dump/load did I use the wrong method for restoring the two databases?
Is there another approach I should use? And why is it set in the RSSD but I
do not have the same problem? Thanks.
Michael Lee (KMB) wrote in message ...
Michael Lee (KMB)
For the primrary DB, secondary turuncation point is required (and updated by
RepAgent). For standby DB, it should be removed since RepAgent will not be
running in the Standby DB.
The point is : if no RepAgent Active, the secondary truncation point should
be removed.
M Corey <mco...@edoc.com> wrote in message
news:grnIUL6...@forums.sybase.com...
jmc
The dbcc settrunc continues to work with rep agents as it did with LTM's.
As you found out, the sp_stop_rep_agent command does not mess with the
secondary truncation point. It just starts or stops the rep agent process
from sending data to the rep server.
You may want to look at the command 'set replication ' to use with your bulk
loads.
If I understand your situation with the RSSD, the rep agent is running in
that database and sending the log info to the rep server. Since repserver
has no repdefs for any tables and in fact no tables or procs marked for
replication, the secondary truncation point would just move along with the
regular truncation point. You would therefore be able to dump your log as
if there were no replication involved.
In answer to your question 1 about moving server from the coast to coast.
What I did, in a warm standby situation, was to make the logs and the stable
queues big enough to hold about 3 days worth of info. I then dropped the
connection to the standby and packed up the server and sent it on its way.
About 1 day before the server was to be all set in the new location, I used
rs_init to re-added the standby server using rs_init and indicated that I
would use a dump to initialize the standby and both an enable marker and a
dump marker would be used. Then just took a dump of my primary to tape and
Fedex'd it to the new location, loaded the tape, synced the users, restarted
the connection and let the queued trx catch up. It worked great.
Once you have dropped the standby, the trx info is sent to the Rep Server
and deleted because there is nothing subscribing to it. When you add the
standby back, you will continue to send data to the RS, and it will sit in
the queue until you resume the connection.
Remember, unlike the trx log on the ASE, you can increase AND DECREASE the
size of the Rep Server Stable Queue.
Hope this helps,
John
Michael
Thanks, I learned alot in this email then I did in the entire repserver class.
Two questions:
1. If I was to unmark the tables in a primary database as being replicated,
would the secondary truncation point in the primary act as you describe for the
RSSD?
2. Just to be clear I understand, if I dropped the connection to the standby,
transactions will still be passed from the primary to the replication server,
they are just deleted. The secondary truncation point which remains in the
primary pretty much acts as you stated for the RSSD? When I'm ready to resume
replication (the server gets to the west coast), I can add the standby back in
with rs_init, use a dump marker with my dump of the primary and all queued
transactions will be applied to the standby once it has been loaded and the
logins are in sync?
jmc
To answer your questions as best I can:
1, Yes, the rep agent would look at the log and see that there was nothing
to send to the rep server and the secondary trunc point would move along.
2. If you drop the connection to the standby, the rep_agent would continue
to send the trx to the rep server. Once they are at the repserver, the will
have no where to go (no subscriptions, no warm standby) so they will be
immediately marked for delete.
When you add the standby database and tell rs_init that you are
'initializing standby using dump and load' and using 'dump marker to start
replicating to standby', trx will immediately start queuing in the stable
queue. Make sure that you DO NOT resume the connection to the standby. You
can then dump your primary and load the dump to the standby. At this point
you can resume the connection and the rep server will start applying all trx
after the dump marker to the standby.
If anyone notices anything that I misstated or (God forbid) anything wrong,
please feel free to point it out.
Michael wrote in message <39E73A6A...@edoc.com>...