Replication Server 12.6 RS_Ticket

1. Replication Server 12.6RS_Ticket Heping Shang, Ph.D, heping@sybase.com Peterson Li, pli@sybase.com

2. What Is RS_Ticket? • RS_Ticket is an ease of use mechanism for measuring and monitoring • Repserver performance • Repserver module heartbeat • Replication health • Table level quiesce • RS_Ticket is an undocumented feature Sybase Confidential

3. What Is New? • New stored procedure • rs_ticketh1, h2, h3, h4 at primary database • rs_ticket_report at replicate database • New counter for EXEC, DIST, and DSI module • RSTicket • New function string • rs_ticket_report • New Repserver/connection configuration parameter • DSI_RS_TICKET_REPORT • New trace flag for EXEC, DIST, and DSI module • PRINT_RS_TICKET Sybase Confidential

4. How to Measure Performance? • For each replicate database • Configure DSI Alter connection to srv.rdb1 set “dsi_rs_ticket_report” to “on” • Customize stored procedure, rs_ticket_report • At the primary database Exec rs_ticket “start” Execute replication benchmarks Exec rs_ticket “stop” • That is all • No need to create heartbeat tables • No need to create additional repdef and subscription Sybase Confidential

5. How to Interpret RS_Ticket? • Collect results at each replicate databases V=1;H1=start;PDB(pdb1)=21:25:28.310;EXEC(41)=21:25:28.327;B(41)=324;DIST(24)=21:25:29.211;DSI(39)=21:25:29.486;RDB(rdb1)=21:25:30.846 V: version number. Current is 1. H: header information. Can have multiple headers. E.g., H1, H2, H3, … PDB: primary database name and the time rs_ticket is executed EXEC: EXEC thread number and the time rs_ticket passes through B: total bytes the EXEC thread received from Rep Agent DIST: DIST thread number and the time rs_ticket passes through DSI: DSI thread number and the time rs_ticket passes through RDB: replicate database name and the time rs_ticket_report is executed Sybase Confidential

6. Customize rs_ticket_report Create procedure rs_ticket_report @rs_ticket_param varchar(225) Begin set nocount on declare @t_new varchar(225), @c_time datetime -- For a string: “@rs_ticket_param;RDB(name)=hh:mm:ss.ddd” select @c_time = getdate() select @t_new = @rs_ticket_param + “;RDB(“ + db_name() + “)=“ + convert(varchar(8), @c_time, 8) + “.” + right(“00” + convert(varchar(3), datepart(ms, @c_time)), 3) insert rs_ticket_history values (@c_time, @t_new) end Sybase Confidential

7. How to Calculate Performance? • Collect results at each replicate databases V=1;H1=start;PDB(pdb1)=21:25:28.310;EXEC(41)=21:25:28.327;B(41)=324;DIST(24)=21:25:29.211;DSI(39)=21:25:29.486;RDB(rdb1)=21:25:30.846 V=1;H1=stop;PDB(pdb1)=21:25:39.406;EXEC(41)=21:32:03.200;B(41)=20534;DIST(24)=21:33:43.323;DSI(39)=21:34:08.466;RDB(rdb1)=21:34:20.103 • Make calculation • Horizontal calculation: pipeline delay • Vertical calculation: total module time • Diagonal calculation: total cross module time Sybase Confidential

8. How to Setup Module Heartbeat? • At primary database, execute rs_ticket at a regular time interval, e.g., every minute Exec rs_ticket “heartbeat” • Collect heartbeat number • For EXEC module heartbeat, log into PRS Admin statistics, RepAgent, RSTicket • For DIST module heartbeat, log into PRS Admin statistics, DIST, RSTicket • For DSI module heartbeat, log into RRS Admin statistics, DSI, RSTicket • Module heartbeat is the differences of this and previous collected RSTicket (divided by time) Sybase Confidential

9. How to Monitor RS Health? • RS_Ticket can be printed into Repserver log • To check EXEC thread health trace “on”, “EXEC”, print_rs_ticket • To check DIST thread health trace “on”, DIST, print_rs_ticket • To check DSI thread health trace “on”, DSI, print_rs_ticket • Execute rs_ticket at the primary and monitor Repserver log to see whether a module is stalled Exe rs_ticket “monitor”, @cnt Sybase Confidential

10. How to Quiesce Table Rep.? • Repserver sometimes requires quiesce replication system before altering a primary table schema • Quiescing is sufficient because it guarantees no data will refer to the old schema • Quiescing is not necessary because not need to quiesce other tables • Steps for quiesce a table • Open trace at the replicate Repserver trace “on”, DSI, print_rs_ticket • Stop primary table activity and execute rs_ticket exec rs_ticket “quiesce” • Once seeing rs_ticket with “quiesce” header in Repserver log, the table is quiesced Sybase Confidential

11. RS_Ticket Notes • RS_Ticket uses rs_marker to carry information through out Repserver • Repserver will not accept wrongly formatted rs_marker. If this happens, you have to skip one log page • Don’t call rs_marker directly. Always use rs_ticket • Don’t call rs_ticket too frequently. Rs_marker is not replicated efficiently. It may impact performance. • For GA release, rs_ticket stored procedure is available only for ASE Sybase Confidential

12. RS_Ticket Notes • To have rs_ticket printed in Repserver log, trace print_rs_ticket must be “on” • This trace can be opened for EXEC, DIST, and DSI • To use rs_ticket_report function string, connection configuration dsi_rs_ticket_report must be “on” • For GA Release, rs_ticket_report function string is not working for RSSD • Default rs_ticket_report has not effect, you must • Alter rs_ticket_report function string, or • Customize rs_ticket_report stored procedure Sybase Confidential

13. RS_Ticket Notes • DIST will not send rs_ticket to DSI unless there is at least one subscription from replicate site • If a replicate Repserver is less than RS 12.6, it will not understand rs_ticket command • DSI will be suspended once rs_ticket arrives • User has to resume DSI with skip transaction Sybase Confidential