Transcription
SAP REPSERVER NOTESLast updated: 04/03/2019DBXperts LtdGarrett Devinewww.dbxperts.co.ukHandy tips for thebusy DBA
Version 2.0Page 104/03/2019Table of ContentsDocument Revision 1.9 . 3Introduction & Disclaimer . 3Repserver Components . 3More Detailed Look at the Components . 3Examine replication environment . 3Repserver BASICS . 4General Install . 4Table Defs Install. 4Warm Standby Install . 5Warm Standby Switch over . 6Database (MSA) repdef . 6Manually set up connections . 7setup primary db's for rep. 7Function Repdefs (stored procedure replication) . 8Replication Tuning Notes . 9Golden rules . 9Find Bottlenecks . 9Sizing caches sizes . 10SQT MAX CACHE SIZE . 10DSI SQT MAX CACHE SIZE . 10Tuning . 10Tuning Primary DB . 10Tuning Rep Server . 10Tuning RSSD. 10Tuning Replicate DB . 11Tuning DSI . 11Configure for SMP systems . 11Minimal Column Replication . 11Monitor Counters. 12Not requiring Setup . 12Requiring Setup . 12Disaster Recovery Notes . 12Recover from reloading Primary Database . 12Skipping transactions . 13Stop Replication . 13Replaying Transaction Logs . 13Rebuild a Stable Device - with tran log . 13Rebuild a Stable Device - without tran log . 14Restore the RSSD from backup . 14General Troubleshooting . 15Stable Queue Full . 15Ignoring duplicate keys – when we have a lot, use error class! . 15Reverse Engineering an Error Class . 16HowTo determine the error class configured for a connection . 16Row count mismatch – use a replication server error class. V15.2 . 16Error in repserver errorlog and connection down - rs get textptr . 17NO ROWS . 17MULTIPLE ROWS . 18Displays all Replication Server configuration parameters. . 18Determine Latency . 18Dropping Subscriptions Fast . 18Detecting loss . 19Repserver Trace Flags . 19www.dbxperts.co.uk1
Version 2.0Page 204/03/2019Configure the rep agent to trace LTL--write output to a trace file (not to ASE log) . 19Turn on Rep Agent tracing and DSI/function string tracing . 20Turn off Rep Agent tracing and DSI/function string tracing . 20Handy Tips . 20Renaming a replicate database . 20rs ticket Feature . 21Setup . 21Exclude rs ticket history from replication . 21Send your first 'ticket' . 21Checking the results of your ticket . 21How to implement . 22Get data from the RDB to trend the latency . 22SQL Statement Replication . 23Configure the primary database to log SQLDML . 24sp setrepdbmode: . 24Configure Replication Server to replicate SQLDML . 24MSA replication . 24Table Replication . 24Warm Standby Replication . 24Configuration Options and Restrictions. 24Thresholds Example: . 24Other Restrictions . 25SQLDML Implementation and Testing Example . 25Appendix A – Shell scripts . 26rs checkreplag.ksh . 26sp queueinfo . 27rs ticket.sh . 27Sybase Database ASE Server Startup & Stop script for linux RHEL for run level 3, 5: . 28Appendix B – troubleshooting . 30Uninstall repserver program . 30Logical Connection will not Drop . 30www.dbxperts.co.uk2
Version 2.0Page 304/03/2019Document Revision 2.0Introduction & DisclaimerThe notes contained in this document are intended as a fast find guide to using SAP Replication server andhave been built up over my time using reperver in the real world. It is not intended to be a completeexploration of all of replication server’s abilities, nor do I claim that all the notes are without error. If youfind errors or would like to submit your own top tip for the next edition of this guide, then please email meat garrett.devine@dbxperts.co.ukRepserver Components SQM (Stable Queue Manager) to manage inserts/deletes and prevent duplicates. One perQueueLTM, Log Transfer Manager. Reads the transaction log.Inbound queue. Holds transactions from LTM. 'admin who, sqm' shows these, e.g. 456:1. the':1' means inboundOutbound queue. Holds trans. to be replicated 'admin who, sqm' shows these, e.g. 457:0.the ':0' means outbound. Has 2 types of queue. Data Server Interface (DSI) andReplication Server Interface (RSI), used across routes.Distributor (DIST). Matches repdefs with subscriptions, so messages applied correctly toreplicate. One DIST thread per inbound queue.SQT (Stable Queue Transaction Manager) ensures queues are accessed in transactional manner. SQT has 4queues:* Open queue that holds transactions until commit or rollback is read from LTM* Closed queue holds completed transactions.* Read queue holds data that has been read from the Closed queue anda receipt of the transaction received. Tran is then removed fromqueue.* Truncation queue holds ‘begin tran’ record. Queue is used to determine whichtransactions can be deleted.More Detailed Look at the ComponentsAdmin who, sqm.Admin who, sqt.Sqt max cache sizeDISTDSIRSIFirst Seg.Block - Last Seg.Block data in queue (Mb)Next Read is the next segment, block & row to be read from queue.First Trans gives queue status. st status of 1st command, cmds noof commands in transaction. qid seg:block:row where tran starts.Full – if non-zero, sqt max cache size too small.cache available to SQT. Need 1M per queue. (Ensure value ofsqt max xcache size * num. of queues is less than memory limit)matched repdefs with subs. 3 components. SRE: matched repdefs withSubs. TD: packages transactions. MD: delivers messages if routesInvolved. ‘admin who, dist’ gives totals of commands processed &ignored.reads committed commands (in SQT closed queue) and applies them toreplicate DB. Prevents dumplicates. Groups transactions toreplicate. Grouping defined by dsi zact group size &dsi cmd batch size.Routes between repservers across WANsExamine replication environmentsp setreptable --in pdbadmin rssd name -- in RSadmin who-- in RSadmin logical status -- in RSrs helprep-- in RSSDrs helpdbrep-- in RSSDwww.dbxperts.co.uk3
Version 2.0Page 404/03/2019rs helpdb-- in RSSDrs helproute-- in RSSDrs helpsub-- in RSSD, details table subscriptionsrs helpdbsub-- in RSSDrs helppubsub-- in RSSD, if using publicationsrs helpdbpub-- in RSSD, details publication subscriptions, articles and subscibersrs helpuser-- in RSSDTo look at current connection settings, use ‘admin config’.admin config [,[{"connection" logical connection}, data server, database] ["route", repserver]] [, configuration name]Example:admin config, "connection", servername , dbname , dsi quoted server BASICS#-----------------------------------#If you use rs init to configure replication and it fails, you can sometimes get moreinformation out of the rs init log files. These are located at SYBASE/ SYBASE REP/init/logsGeneral InstallUse rs init to install repserver & set up the RSSD. Create stable queue files first(using ‘touch’). Once this is complete, you need to add connections to the primary andreplicate dataservers and databases. See the sections below on how to do this. To usethe GUI (rs init), create a rep maint user in the DB using sp adduser. Remove this laterand add as alias to dbo using sp addalias.Table Defs InstallIn PDB sp setreptable prim tab1, trueIn RS 1 create replication definition prim tab1 repdef with primary at SRV01 ASE.pdb12 with all tables named prim tab1 (a int, b char(10)) primary key (a)3 go1 2 3 1 2 3 1 2 3 1 2 3 define subscription prim tab1 sub for prim tab1 repdefwith replicate at SRV01 ASE.rdb1goactivate subscription prim tab1 sub for prim tab1 repdefwith replicate at SRV01 ASE.rdb1govalidate subscription prim tab1 sub for prim tab1 repdefwith replicate at SRV01 ASE.rdb1gocheck subscription prim tab1 sub for prim tab1 repdefwith replicate at SRV01 ASE.rdb1goAlter repdef ** This also fixes the subscription automaticallyalter replication definition prim tab1 repdefadd c char(10) nullTesting declare @cnt intdeclare @b val char(10)declare @c val char(10)select @cnt 2while @cnt 10BEGINselect @b val 'test' convert(char(5), @cnt)select @c val 'test' convert(char(5), @cnt @cnt)INSERT INTO pdb1.prim tab1(a,b,c) values (@cnt, @b val, @c val)select @cnt @cnt 1ENDwww.dbxperts.co.uk4
Version 2.0Page 504/03/2019Warm Standby InstallSetting up warm standby using rs init can be a bit tricky, so follow these steps below.Watch out for issues with the ‘maint’ user. In RS----1 create logical connection to "logical srv"."logical db"2 goIn ASE (source)-----1 use warmsby2 go1 sp reptostandby warmsby, 'all'2 goIn ASE (target)-----1 use warmsby copy2 go1 sp reptostandby warmsby copy, 'all'2 go------Logins-----sp addlogin warmsby maint, thisisapasswordgosp role 'grant', replication role, warmsby maintgoUSE warmsbygosp addalias 'warmsby maint','dbo'goSync syslogins & sysloginroles (make sure that warmsby maint is on both ASE servers)* BCP OUT/IN syslogins between serverscreate connection to "SRV1"."warmsby"set error class rs sqlserver error classset function string class rs sqlserver function classset username "warmsby maint"set password "thisisapassword"with log transfer on as active for "logical srv"."logical db"create connection to "SRV2"."warmsby copy"set error class rs sqlserver error classset function string class rs sqlserver function classset username "warmsby maint"set password "thisisapassword"with log transfer on as standby for "logical srv"."logical db" use dump markerConfigure the database for In PDB: Run in SYBASE/ SYBASE REP/scripts/rs install primary.sqlisql -SSRV1 -Usa -P pwd -Dwarmsby -i rs install primary.sqlConfigure rep agent------------------In PDB:use warmsbygosp stop rep agent warmsbygosp config rep agent warmsby,gosp config rep agent warmsby,gosp config rep agent warmsby,gosp config rep agent warmsby,gosp config rep agent warmsby,gosp config rep agent warmsby,gosp start rep agent warmsbygowww.dbxperts.co.uk'disable''enable', 'repserver', 'repserver ra', 'repserver ra ps''priority', '5''send buffer size', '16k''scan batch size', '1000''send warm standby xacts', true5
Version 2.0Page 604/03/2019sp setreplicate rs marker,"true"gosp setreplicate rs update lastcommit,"true"goDump'n'Load databases--------------------Immediatly dump and load the database from Active to Standby database.Make sure the "warmsby maint" has SELECT, DELETE, etc permissions are set on Standbydatabaseoruse warmsby copygosp dropuser 'warmsby maint'gosp addalias 'warmsby maint', 'dbo'goIn RS----resume connection to SRV2.warmsby copygoWarm Standby Switch overIn PDB (old active database)---------------------------sp stop rep agent warmsbygoIn RS----isql –Uuser [-Syourrepserver]-To switch over to warm standby server.admin logical statusgo--switch active for logialserver.logicaldb to wsserver.wsdb switch active for logical srv.logical db to SRV2.warmsby copygoadmin logical statusgoIn RDB (New active database)---------------------------sp configure 'enable rep agent threads', 1gosp start rep agent warmsby copygo-- if not already setIn RS----resume connection to SRV2.warmsby copygoNote: if the old primary database has been shutdown or is no longer contactable, thelogical status for it will remain as “Suspended/Waiting for Enable Marker” until it isfixed. Once the server comes backon line, resume the connection and ‘Operation inProgress’ will go back to ‘None’Database (MSA) repdef* Set up Replication server as normal using rs init* Add primary database to RS using rs init* make sure ‘ddl in tran is set on both databases’In PDB sp reptostandby DBNAME,"all"sp config rep agent pdb1, 'send warm standby xacts', 'true'In RS 1 create database replication definition pdb1 dbrepdef2 with primary at SRV1 ASE.pdb13 replicate ddl4 replicate functionswww.dbxperts.co.uk6
Version 2.0Page 75 replicate system procedures6 go1 2 3 4 5 6 create connection to “SRV1 ASE”.”test rep db”set error class to rs sqlserver error classset function string class to rs sqlserver function classset username to "rep maint"set password to "rep maint ps"go1 2 3 4 5 6 7 define subscription pdb1 subfor database replication definition pdb1 dbrepdefwith primary at SRV1 ASE.pdb1with replicate at SRV1 ASE.test rep dbsubscribe to truncate tableuse dump markergoIn RDB To avoid any permission issues in replicate DBUse test rep dbgosp addalias 'test rep db maint','dbo'goAt this point the live database should be dumped and loaded into replicate database.When the dumps have completed, resume the connection to the standby sites.In PRS resume connection to SRV1 ASE.test rep dbgoManually set up connections create connection to server1.dbnameset error class to custom error classset function string class to rs sqlserver function classset username to dbname maintset password to thisisapasswordGOcreate connection to server2.dbnameset error class to custom error classset function string class to rs sqlserver function classset username to dbname maintset password to thisisapasswordGOcreate connection to server3.dbname copy2set error class to custom error classset function string class to rs sqlserver function classset username to dbname maintset password to thisisapasswordGOalter connection to server1.dbnameset log transfer onGOalter connection to server2.dbnameset log transfer offGOalter connection to server3.dbname copy2set log transfer offGOsetup primary db's for rep for SRV in server1doisql -Usa -P password -S SRV -D DBNAME SYBASE/ SYBASE REP/scripts/rs install primary.sqlwww.dbxperts.co.uk704/03/2019
Version 2.0Page 804/03/2019isql -Usa -P password -S SRV EOFexec sp addlogin dbname maint,thisisapasswordgouse DBNAMEgosp addalias dbname maint,dbogoexec sp reptostandby DBNAME,"all"goexec sp config rep agent DBNAME,enable,repserver rs,repserver rs ra,repserver rs ra psgoexec sp config rep agent DBNAME,"send warm standby xacts",truegoexec sp config rep agent DBNAME,'priority','4'goexec sp config rep agent DBNAME,"scan batch size","10000"goexec sp config rep agent DBNAME,"send buffer size","16K"goexec sp config rep agent DBNAME,"send structured oqids","true"goexec sp config rep agent DBNAME,"short ltl keywords","true"gosp start rep agent DBNAMEgoEOFdoneFunction Repdefs (stored procedure replication)Implementing Stored procedure replication by ---------In PDB & RDB create table testtable1 (name varchar(10), phone int)gocreate procedure sp testtable1 insert @name varchar(10), @phone intasbegininsert into testtable1 (name, phone) values (@name, @phone)endgo-- mark sp for replication (ignore error #9137 if using warm stby)sp setrepproc sp testtable1 insert, functiongo--If your maint user is not dbo in the replicate db, then execute this in the RDBgrant execute on sp testtable1 insert to maint userRS Applied function sp is executed by maint userRequest function sp is executed by same user who executed SP at the primary database“create function replication definition” deprecated in repserver 15, use ‘applied orrequested’ instead.-- Note the repdef name exactly matches the proc name.create applied function replication definition sp testtable1 insert repdefwith primary at logical srv . logical db with all functions named 'sp testtable1 insert'(@name varchar(10), @phone int)go-- create subscriptioncreate subscription sp testtable1 insert subfor sp testtable1 insert repdefwith replicate at SRV2 ASE.test rep dbwithout materializationgocheck subscription sp testtable1 insert subfor sp testtable1 insert repdefwith replicate at SRV2 ASE.test rep dbgowww.dbxperts.co.uk8
Version 2.0Page 904/03/2019-- TESTING in PDB-sp testtable1 insert 'gary', 1234go--Dropping a function definitiondrop function replication definition sp testtable1 insert repdefReplication Tuning Notes Golden rules 1. Never have repdefs, which are not subscribed to. All transactions on replicatedtables are sent to the Inbound Queue (IBQ), sorted into commit order and translated toLog Transfer Language(LTL). Only then are they checked for subscriptions. This resultsin wasted space in the IBQ and processing by the SQT manager.2. Make sure SQT has enought memory allocated. Also, check memory limitrs configure 'sqt max cache size' to 'xxxxx'Find Bottlenecks select * from master.syslogshold--check for large uncommitted transactions.Measure diff between repagent position and end of log (1TP & --------------------rep agent - value of 'Current Marker' column, example (53550,1)sp help rep agent db name -- read until end of logdbcc traceon(3604)dbcc pglinkage( dbid , current marker , 0,2,0,1)example: dbcc pglinkage(5, 53550, 0,2,0,1)example outout: "3909 pages scanned"-- So repagent if 3909 pages behind log truncation marker.-- We should have very little lag!(see rs checklag.ksh inMeasure IBQ & OBQ size---------------------admin who, sqmInfo column of XXX:0 IBQInfo column of XXX:1 OBQdifference between 'Last Seg.Block" & "Next Read" should be minimalExample: Last Seg.Block 226.64Next Read 140.50.13(226-140) 86 Mb in IBQThis info is also stored in the RSSD db in rs diskpartitions, rs segmentsThis is used in sp queueinfo (see Appendix A)Check what is in the queues--------------------------Once we know which queues are filling up, use the command below to determine the sql inthe queues.sysadmin dump queue, q num , q type , -1, -2, -1, clientorsysadmin log first tran, srv , dbname Check ASE activity-----------------If monitoring tables are installed, discover busiest spid and extract SQL.(Useful tools for this: sp mon sql2 & sp capture sql)www.dbxperts.co.uk9
Version 2.0Page 1004/03/2019Sizing caches sizesSQT MAX CACHE SIZETo check if this parameter has been set too low, run an "admin who,sqt" at regularintervals and check if the columns "removed" or "full" contain a non-zero value.Example:configure replication server set sqt max cache size to '115343360' --bytes (110MB)DSI SQT MAX CACHE SIZE"admin who,sqt" also shows the DSI threads. So when "removed" or "full" are non-zero for a DSIthread, change the memory setting at the connection-level using "alter connection to dataserver.database set dsi sqt max cache size to ' new-value '"It's best to start increasing "sqt max cache size" before changing settings at the connection level.Do not increase sqt max cache size" to much. Oversizing the cache will in fact decreaseperformance.Tuning Tuning Primary DB----------------sp help rep agent db name , 'config'sp config rep agent db name , scan batch size, '10000' --max num records sent to RSsp config rep agent db name , 'batch ltl, 'true' --LTL cmds batched up then sent to RSsp config rep agent db name , send buffer size, '16k' -- network packet sizesp config rep agent db name , priority, '2' --default is 5. lower higher priorityWARNING: making changes to the rep agent can cause a warm stby connection to fail, if thereplicate DB name is different. Requires a resume connection.skip transaction. And theconfig changes to be repeated at the replicates rep agent.Tuning Rep Server----------------Note: 1 Repserver 1 CPUadmin who, sqtexisting values are stored in RSSD. Use:select optionname, charvalue from rs configconfigure replication server set sqt max cache size to '20971520' -–in RS, orrs configure 'sqt max cache size' to 'xxxxx' -- in RSSD,Ensure value of (sqt max cache size * num. of queues) is less than memory limit.Suggest setting sqt max cache size to 20mb (20971520 bytes)Max memory limit 2047 (just under 2Gb)Use RAW device for Stable Device.rs configure 'num threads’, 75 -- if using Open Server (replicating to non Sybase DB)configure replication server set sqm write flush to ‘dio’ -- only on Unix file systemsTuning RSSD----------sp config rep agent db name , priority, '2' --RSSD can have it's own repagentPut on same machine as RS.use 'localhost port ' in interfaces file for ASE and RSexample:REP1 RSmaster tcp ether localhost 10010master tcp ether server 10010query tcp ether server 10010--keeps rs system tables in memory.configure replication server set sts full cache rs classes to 'on'configure replication server set sts full cache rs columns to 'on'configure replication server set sts full cache rs config to 'on'configure replication server set sts full cache rs databases to 'on'configure replication server set sts full cache rs datatype to 'on'configure replication server set sts full cache rs diskaffinity to 'on'configure replication server set sts full cache rs functions to 'on'configure replication server set sts full cache rs objects to 'on'www.dbxperts.co.uk10
Version 2.0Page 1104/03/2019configure replication server set sts
the ':0' means outbound. Has 2 types of queue. Data Server Interface (DSI) and Replication Server Interface (RSI), used across routes. Distributor (DIST). Matches repdefs with subscriptions, so messages applied correctly to replicate. One DIST thread per inbound queue.
