Sybase Rep Server - Warm Standy Problem
We use RS in Warm Standby configuration one of the databases had a problem today and there was a large number of rows generated that we not needed. As a result I truncated the table on the primary side. the problem is that the data still seems to be flowing through the repserver to the database on the warm standby side. The queue for this Db has been increasing in size all day and is now alomst 10 GB.
So I wanted to find out if there any way to delete the data ?
I attached a file with the output of the admin who, sqm & admin who,sqt commands.
repserver-102108.txt
So I wanted to find out if there any way to delete the data ?
I attached a file with the output of the admin who, sqm & admin who,sqt commands.
repserver-102108.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
There were about 10 million rows generated as a result of an update and then a trigger firing.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Joe,
I'm new at this company and as you can see of limited experience with Rep Server in the Warm Standby configuration. We are doing database level replication for all objects in this and three other databases.
It's my understanding that the Replication system was setup using rs_init so the parameters that you mention have not been modified. I looked in the config file for the Rep Server and none of them are mentioned.
I don't have a problem going ahead and Restoring the standby db but I can't find the directions you mention, could you include a link ? Also I am concerned that since the queues appear to be full none of the other db's are getting replicate either ?
Back to the error message I'm getting "Executor does not allow the entered command in the current mode" indicates that rep server was resatrted in Recovery mode that wasn't something I did, at least intentionally, in any case I rebooted rep and I'm still seeing the same thing, any ideas for how I can get out of this mess ?
I'm new at this company and as you can see of limited experience with Rep Server in the Warm Standby configuration. We are doing database level replication for all objects in this and three other databases.
It's my understanding that the Replication system was setup using rs_init so the parameters that you mention have not been modified. I looked in the config file for the Rep Server and none of them are mentioned.
I don't have a problem going ahead and Restoring the standby db but I can't find the directions you mention, could you include a link ? Also I am concerned that since the queues appear to be full none of the other db's are getting replicate either ?
Back to the error message I'm getting "Executor does not allow the entered command in the current mode" indicates that rep server was resatrted in Recovery mode that wasn't something I did, at least intentionally, in any case I rebooted rep and I'm still seeing the same thing, any ideas for how I can get out of this mess ?
ASKER
Thanks
Most RepServer parameters don't live in the config file (it's not like ASE), or even in RepServer itself - they live in the associated RSSD (Replication Server System Database), which is either a database in an ASE, or an embedded RSSD (ERSSD). Assuming it's an RSSD in an ASE, you can see and set RS parameters with:
use [RSSD database name]
go
rs_configure
go
Some other parameters are not RS-wide and apply only to specific connections/DSIs.
Re. database-level replication - this is a very simple and convenient way to do things, and unfortunately also the slowest. And guess what, the worst performance hit is to updates!
This is a long and complicated topic, but very briefly without a *table*-level replication definition (repdef), suppose you have a table with 26 columns and you only update one of them in the primary database:
update [table]
set C=0
where A=19999 -- let's say A is the PK
Without a repdef, RS translates this in the replicate database as:
update [table
set A=19999
, B=[old value of B]
, C=0
[...]
, Z=[old value of Z]
where A = 19999
, B = [old value of B]
, C = 0
[...]
, Z = [old value of Z]
Pretty horrible, huh! It has to do this because without a repdef it doesn't know how to uniquely identify a row, so it has to put in all those WHERE clauses. It also isn't clear on which columns it needs to update (send all? send only the ones that changed? either could be valid in some circumstances) so it updates them all.
With a table-level repdef you can least get rid of the extra WHERE clauses... but you still set every column. You have to also turn out "replicate_minimal_columns
Note without a repdef (or minimal_columns), you're updating every column including indexed columns and the PK! This means whatever style of update it was in the primary database, it's now a deferred update in the replicate, so the same update statement takes much longer to run than it did in the primary. I'd say this was your first problem. Repdefs are really crucial for performance...
Re. that error message - what are you using to connect to RS? This can also happen if you connect using an old style of interface that tries to connect in ANSI CHAINED mode... which issues a "BEGIN TRAN" upon connecting... which RS doesn't understand. You can apparently ignore these messages. You won't get them if you connect with any modern Sybase client that uses ct-lib... like the isql that shipped with RS?
I have some bad news for you - I don't think it's possible to effectively maintain a RS system with neither prior experience nor the standard training course. You can pick up ASE if you've used any other enterprise RDBMS before, but nothing really prepares you for RS. Reading it all from the manuals is a tough ask. You need the training course.
Lastly there is a fantastic tuning whitepaper available from Sybase at http://www.sybase.com/detail?id=1015811 ... but it's going to be a lot to swallow without the training course first. :(
use [RSSD database name]
go
rs_configure
go
Some other parameters are not RS-wide and apply only to specific connections/DSIs.
Re. database-level replication - this is a very simple and convenient way to do things, and unfortunately also the slowest. And guess what, the worst performance hit is to updates!
This is a long and complicated topic, but very briefly without a *table*-level replication definition (repdef), suppose you have a table with 26 columns and you only update one of them in the primary database:
update [table]
set C=0
where A=19999 -- let's say A is the PK
Without a repdef, RS translates this in the replicate database as:
update [table
set A=19999
, B=[old value of B]
, C=0
[...]
, Z=[old value of Z]
where A = 19999
, B = [old value of B]
, C = 0
[...]
, Z = [old value of Z]
Pretty horrible, huh! It has to do this because without a repdef it doesn't know how to uniquely identify a row, so it has to put in all those WHERE clauses. It also isn't clear on which columns it needs to update (send all? send only the ones that changed? either could be valid in some circumstances) so it updates them all.
With a table-level repdef you can least get rid of the extra WHERE clauses... but you still set every column. You have to also turn out "replicate_minimal_columns
Note without a repdef (or minimal_columns), you're updating every column including indexed columns and the PK! This means whatever style of update it was in the primary database, it's now a deferred update in the replicate, so the same update statement takes much longer to run than it did in the primary. I'd say this was your first problem. Repdefs are really crucial for performance...
Re. that error message - what are you using to connect to RS? This can also happen if you connect using an old style of interface that tries to connect in ANSI CHAINED mode... which issues a "BEGIN TRAN" upon connecting... which RS doesn't understand. You can apparently ignore these messages. You won't get them if you connect with any modern Sybase client that uses ct-lib... like the isql that shipped with RS?
I have some bad news for you - I don't think it's possible to effectively maintain a RS system with neither prior experience nor the standard training course. You can pick up ASE if you've used any other enterprise RDBMS before, but nothing really prepares you for RS. Reading it all from the manuals is a tough ask. You need the training course.
Lastly there is a fantastic tuning whitepaper available from Sybase at http://www.sybase.com/detail?id=1015811 ... but it's going to be a lot to swallow without the training course first. :(
ASKER
Thanks for your suggestions I was going to go ahead with them and I have run into another problem.
When I try to execute and commands in the repserver via isql I get error messages like this:
1> sysadmin purge_all_open, 117, 1
2> go
Msg 14024, Level 12, State 0:
Server 'RSMDR001':
Executor does not allow the entered command in the current mode.
1> admin who
2> go
Msg 14024, Level 12, State 0:
Server 'RSMDR001':
Executor does not allow the entered command in the current mode.
1> admin who_is_down
2> go
Msg 14024, Level 12, State 0:
Server 'RSMDR001':
Executor does not allow the entered command in the current mode.
1> add partition rep_dev08.dat 'D:\repData' with size 2048
2> go
In addition the RSM server dies peridically and then I loose the use of Sybase Central until I restart.
So know I'm stuggling to get anything done do you guys have any suggestions ?