Replication Concept & Methods
Replication methods
- Regular replication: – table level replication from a primary to a replicate database
- Multi-site availability (MSA):- database level replication from a primary to replicate database
- Warm Standby: – database level replication to maintain a standby database
- Hot Standby: – transactions are run simultaneously in the primary and replicate database using distributed transaction management
- Warm Standby: – transactions are captured in the primary database and distributed to the replicate database
- Cold Standby: – the primary data is copied to the replicate database on a scheduled basis
Concept in replication
Primary and replicate data ( prim db to repl db )
Peer–to–Peer replication ( multiple prim db, each db is both prim & repl, update anywhere)
Heterogeneous replication ( any db to any db e.g oracle,mssql,db2 )
Replicated operations:- can not replication min log operation and manually DML on sys tbl
DML – data modification Lang (ins, upd, del, slow bcp, trun)
DDL – data definition Lang (create, alter, drop, sp_* )
DCL – grant & revoke, sp_adduser, sp_addalias, sp_changegroup
Min log operation: – select into, fast bcp, reorg rebuild, write text, update stat
Internal of sybase replication server
- RepAgent reads the record from Transation log of the PDB for the tables which are marked for replication.
- Logs into the PRS and write transactions in inbound queue of PDB in stable device.
- Holds Data in inbound queue , untill it recieves commit.
- Uses subscription information in its RSSD to decide what to do with the each transaction, after the commit:
i Discards the trans if there is no subscription.
ii Writes the transaction to the out bound queue if there are subscription.
- Writes commited trans only in outbound queue according to subscription.
- Sends transactions to their destination, it depends upone two things
- i) if Replicated Database is managed by PRS
- Apply changes to RDB using the DSI thread our the connection.
- ii) If their are two server, RRS is managing RDB
- Send commited trans to RRS over route.RRS apply that changes in RDB
- If appropriate, uses function string information in RSSD to compose command to submit to replicate database.
As you people are seeing, there are lot of movement of trans/record , and for these trans movements Replication Server uses lot of threads(DSI,RSI,SQT,SQM etc).
If any one of thread stops , replication ceases , even it can hamper PDB performance.
Thats why it is little bit difficult to manage, not difficult, we can say, its need better monitoring.
Architecture
SD:- Stable Device
- The nonvolatile store area on which replication server stores the transactions it receives from repagents or from other replication servers
- Each replication server requires its own stable device
- Managed directly by a replication server, but is not a database
- Contains multiple stable queues
- The disk device on which replication server stores data during processing
SQ:- Stable queues
- Data structures within each stable device that hold data rows during the replication process
- A stable device contains many stable queues, including one queue for eachRepagent connected to the replication server
- Replication server to which the replication server has a route
- Dataserver to which the replication server has a connection
- For warm standby, a single inbound queue exists for the logical connection
- For other replication relationships, there are four types of queues
- Inbound Queue (IBQ):- There is one inbound queue per primary database which contains data rows and transactions that have begun at the primary database but have no yest been committed
- Outbound Queue(OBQ):- There is one outbound queue for each RDB to which replication server has a connection, replication server to which it has a route , contains data for transactions that have been committed, but are still in the process of begin ( applied at the RDB, written to the stable device of another replication server )
- Materialization Queue(MatQ):- holds data and commands during automatic materialization ( select the entire subscription set from the PDB, writes the subscription set to the materialization queue, insert the data into the RDB in one large transaction )
- DeMaterialization Queue(DeMatQ):-
Rs_lastcommit:-
- Replication server adds a table called rs_lascommit to each primary/active database in the replication system
- Rs_lastcommit stores the last committed transaction that is in the stable device of the database
- Replication server uses the origin_qid to ensure that no duplicate transactions are processed
Route:-
- A route is a uni-directional connection between two replication servers
- A replication server thread called the replication server interface(RSI) uses routes to send data and messages to other replication servers
- Setting up a route automatically creates subscriptions in the destination replication server’s RSSD for the table in the source replications server’s RSSD
ID Server:-
- is one replication server in the RSS that registers all the replication servers and databases with in the system
- Provide each RS, DS, DB with a unique identifier
- Does not have to participate in replication
- Doses not to be running unless you are adding or removing RS, DB or routes to RSS
Threads
REP AGENT: – Replication agent Thread
- Reads the primary database transaction lo to find transactions (SQL statements or sp exec ) that have occurred against tables that are marked for replication
- Forwards transactions to the replication server using a proprietary language called Log Transfer Language (LTL)
- Function as a connection manager for the repagents and passes the changes to SQM
- Maintains a secondary truncation point in transaction log, which prevents transactions from being truncated until they are safely stored in the replication server stable device.
- Coordinate recovery between the transaction log and replication server
- Each database may only have one Repagent thread
- Repagent is enabled on the standby, but is not turned on
DSI EXEC: – data server interface execution thread
- Translates the replication transactions functions into destination command language (TSQL) and applies the transaction to replicate database.
DSI: – data server interface thread
Connection:-
- Connection exists between replication server and the database they manage
- A Replication Server has a connection to reach replicate database it manages.
- A Replication thread DSI uses this connections to send updates to the replicate database
- The DSI logs into the RDS as a regular client connection using the maintenance user login.
- A maintenance user login is a special userid used by replication servers to make changes in replicate database and RSSD.
SQM: – Stable Queue Manager thread
- is the only thread that interacts with the stable queue it performs all logical I/O to the stable queue (physical i/o actually performed by the dAIO daemon)
- writes the logged changes to disk via os i/o routine, notify that async i/o deamon (dAIO)
- The SQM is responsible for the following:
- “Queue I/O”. All reads, writes, deletes and queue dumps from the stable queue “Duplicate Detection”. Compares OQID’s from LTL to determine if LTL log row is a duplicate of one already received.
dAIO:- async i/o deamon thread
- Polls the o/s for completion and notify the SQM that i/o completed
SQT: – The Stable Queue Transaction thread
- Responsible for sorting the transactions into commit order.
- Request the next disk block from the SQM and sort the transaction into commit order again read request is done via SQM->dAIO once the commit record for transaction has been seen he SQT alert distribution thread (DIST) that transaction is available.
DIST: – Distribution thread
- Read transaction ad determine who is subscribing to it whether subscription migration is necessary once all of the subscriber is identified the DIST thread forward the transaction to the SQM for the outbound queue for destination connection.
RSI :- Replication server interface
dSUB
dCM
dREC
dSTATS
dALARAM
USER
MSA Replication Configuration
PDS : Primary Data Server
PDB : Primary Data Base
RDS : Replicated Data Server
RDB : Replicated Data Base
PRS : Primary Replication Server
RRS : Replicated Replication Server
RSSD : Replication System Database
isql -Usa -SPDS -Psapasswd << EOF
use master
go
EXEC sp_dboption DBNAME,'abort tran on log full',true
go
EXEC sp_dboption DBNAME,'select into/bulkcopy/pllsort',true
go
EXEC sp_dboption DBNAME,'trunc log on chkpt',true
go
use DBNAME
go
sp_dropuser mnt_usr
go
sp_addalias mnt_usr,'dbo'
go
use master
go
sp_adduser mnt_usr,mnt_usr,'public'
go
use DBNAME
go
sp_reptostandby DBNAME,'all'
( ***** sp_reptostandy will pass transactions that have been performed by the maintenance user )
go
use master
go
grant set session authorization to mnt_usr
( ***** With the proxy authorization capability of Adaptive Server, System Security Officers can grant selected logins the ability to assume the security context of another user, and an application can perform tasks in a controlled manner on behalf of different users. If a login has permission to use proxy authorization, the login can impersonate any other login in Adaptive Server.
***** )
go
use DBNAME
go
( *****
scan batch size 'no_of_qualifying_records'
Specifies the maximum number of log records to send to Replication Server in each batch. When the maximum number of records is met, RepAgent asks Replication Server for a new secondary truncation point. The default is 1000 records.
***** )
sp_config_rep_agent DBNAME , "send warm standby xacts", "true"
( ***** Replication Server automatically puts a begin tran and commit around all transactions. This will cause this behavior on the replicate database. There is a paramtater in the Replciation Agent "send warm stanby xacts" that needs to be set to true when setting up MSA replication. This paramater allows the DDL to be replicated properly without recieving the above server message.
Error :-
MSA Replication with DDL – Some Store Procedure's don't replicate. Creation message "Message from server: Message: 2762, State 3, Severity 16 —
'The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.
***** )
go
sp_config_rep_agent DBNAME , "send buffer size","8k"
( ***** Controls the size of the send buffer that RepAgent uses to communicate with Replication Server. increasing the size of the send buffer reduces the number of times RepAgent communicates with Replication Server, but increases the amount of memory used.
The default value is 2K.
***** )
go
sp_config_rep_agent DBNAME , "short ltl keywords","TRUE"
( ***** Specifies whether RepAgent sends an abbreviated form of LTL to Replication Server, requiring less space and reducing the amount of data sent. The default value is “false.”
***** )
go
sp_config_rep_agent DBNAME , "priority","4"
( ***** Sets relative priority values for individual RepAgents.
4 – high priority
5 – medium priority
6 – low priority
The default value of priority is 5.
***** )
go
exec sp_config_rep_agent DBNAME , "send structured oqids","TRUE"
( ***** Specifies whether RepAgent sends origin queue IDs (OQIDs) as structured tokens, which saves space in the LTL and thus improves throughput, or as binary strings. The default value is “false.”
***** )
go
sp_stop_rep_agent DBNAME
go
sp_start_rep_agent DBNAME
go
EOF
isql -Usa -SPRS -Psapasswd_rs << EOF
alter connection to PDS.DBNAME set dsi_replication_ddl 'on'
go
( ***** Bi-directional MSA setup, DDL is replicating back to PDB.
***** )
alter connection to PDS.DBNAME set dsi_keep_triggers 'off'
go
alter connection to PDS.DBNAME set dynamic_sql 'on'
go
suspend connection to PDS.DBNAME
go
resume connection to PDS.DBNAME
go
EOF
isql -Usa -SPDS -Psapasswd << EOF
use DBNAME
go
sp_start_rep_agent DBNAME
go
EOF
isql -Usa -SRDS -Psapasswd << EOF
use master
go
EXEC sp_dboption DBNAME,'abort tran on log full',true
go
EXEC sp_dboption DBNAME,'select into/bulkcopy/pllsort',true
go
EXEC sp_dboption DBNAME,'trunc log on chkpt',true
go
use DBNAME
go
sp_dropuser mnt_usr
go
sp_addalias mnt_usr,'dbo'
go
use master
go
sp_adduser mnt_usr,mnt_usr,'public'
go
use DBNAME
go
sp_reptostandby DBNAME,'all'
go
use master
go
grant set session authorization to mnt_usr
go
use DBNAME
go
sp_config_rep_agent DBNAME , "send warm standby xacts", "true"
go
sp_config_rep_agent DBNAME , "send buffer size","8k"
go
sp_config_rep_agent DBNAME , "short ltl keywords","TRUE"
go
sp_config_rep_agent DBNAME , "priority","4"
go
exec sp_config_rep_agent DBNAME , "send structured oqids","TRUE"
go
sp_stop_rep_agent DBNAME
go
sp_start_rep_agent DBNAME
go
EOF
isql -Usa -SRRS -Psapasswd_rs << EOF
alter connection to RDS.DBNAME set dsi_replication_ddl 'on'
go
alter connection to RDS.DBNAME set dsi_keep_triggers 'off'
go
alter connection to RDS.DBNAME set dynamic_sql 'on'
go
suspend connection to RDS.DBNAME
go
resume connection to RDS.DBNAME
go
EOF
isql -Usa -SRDS -Psapasswd << EOF
use DBNAME
go
sp_start_rep_agent DBNAME
go
EOF
Tips :-
drop subscription manually from rs_subscriptions, if it has no other subscription for database than delete it from rs_repdbs, and if ct has filter on the database than rs_dbsubsets. Than recycle the rep server.
***********************************************************************
To reset the locater, update the rs_locater table in the RSSD for the Replication
Server controlling the database using the Adaptive Server stored procedure
rs_zeroltm:
rs_zeroltm data_server, database
***********************************************************************
Use admin who, sqm – check if First.Seg.Bock and Last.Seg.Block are the same indicating the queue is empty
***********************************************************************
Use admin who, sqt – verify that there are no transactions in the ‘Closed’ column. ‘
Closed’ shows the number of committed transactions in the SQT cache.
The transactions have been read from the stable queue and await processing.
***********************************************************************
Once a stored procedure is marked as replicate, it is no more possible to replicate data updated by this procedure.
***********************************************************************
Create proxy table and drop proxy table are not supported in warm standby and MSA replication.
CR 324773 has been filed. There is no workaround but resume connection and skip transaction
***********************************************************************
Dump marker can only activate one db repdef at a time. The correct way to do this is:
Create db sub for r1. Dump p1, wait and load r1.
Create db sub for r2. Dump p1, wait and load r2.
***********************************************************************
*MSA When there are two database (db) replication definitions (repdefs) –
One primary db with two db repdefs each with filters and each replicates to a seperate replicate database.
If the 2nd db repdef’s filter is modified by RM or isql, the 1st db repdef’s filter stops working.
***********************************************************************
Error :- RS1501ESD#3. Bi_directional MSA w/table repdef. Can not drop connection after dropping all table replication definition,The error is:Database ‘DBCI2.ci’ is the primary for some replication definitions. but all repdef have been dropped.
Solution :- When a repdef was ever being used MSA or standby connection (with or without the send standby option), when it is dropped, the repdef is renamed to rs_drp0x0s. However, it is a bug that the user have to manually delete rs_drp0x0s before they can drop a connection.Workaround:update rs_objects set dbid = 0 where objname like “rs_drp%” and dbid =
before dropping the connection.
Repserver Bug 500356 will be fixed in 15.0.1 ESD #4 and 15.1 ESD #1.In this case, customer manually deleted rs_drp0x0s from rs_objects before drop the connection. Or you update rs_objects set dbid 0 where objname like ‘rs_drp%’. Then.drop the connection.
***********************************************************************
sp_setrepproc stored_procedure_name
go
exec stored_procedure_name
go
This will force the stored procedure to execute on the target instead.
***********************************************************************
To set traces, add the following lines into the .cfg file of the Replication Server
you want to trace.
1 This line forwards the output to the standard output or to the errorlog:
trace=GEN,TRC_STDERR or trace=GEN,TRC_ERRLOG
2 This line gives the output of commands that are sent to the replicate site.
trace=DSI,DSI_CMD_DUMP
***********************************************************************
Dumping stable queues
sysadmin dump_queue, q_number, q_type, seg, blk, cnt [,RSSD | Client ]
q_number, q_type :- check with admin who or admin who,sqm
seg :- -1 1st active segement , -2 1st segement ( act or inactive )
Blk :- start from 1 to 64
if seg -1 and blk -1 : start with 1st undeleted blk of queue
if seq -1 and blk -2 :- start with 1st unread blk of queue
cnt :- number of blk to dump
-1 end of current seg is last
-2 end of queue
RSSD :- o/p to rssd table (rs_queuemsg, rs_queuemsgtxt )
Client :- o/p to client isql
If no option then written into rep server log file or dump_file
eg :- sysadmin dump_queue,103,1,0,15,65
sysadmin dump_file, qdump
sysadmin dump_queue,103,1,-1,1,-2
sysadmin dump_file
*********************************************************************
