DB-3: OpenEdge

1
DB-3 OpenEdge Replication How-To
David Eddy
Senior Consultant
2
Agenda

• What is OpenEdge Replication?
• Planning for your installation
• First-time setup
• Common procedures
• Things you need to know
• Things you should think about
• Summary

3
What is OpenEdge Replication?

• Optional component of OpenEdge RDBMS
• Maintains 1 or 2 copies of a database
• In near-real-time synchronization
• Basically a tap on the After-Image log

Client
ReplAgent
ReplServ
BI
AI
BI
4
Ordinary Replication vs. Role Reversal
Client
Client
Client
Client
10.1A automatesback-replicationand similar
tasks
source(live)
source(live)
target
target
transition(drop replication)
transition(role reversal)
?
target
source(live)
live
Client
Client
Client
Client
5
Agenda

• What is OpenEdge Replication?
• Planning for your installation
• First-time setup
• Common procedures
• Things you need to know
• Things you should think about
• Summary

6
Planning for your InstallationOS Versions and
Management

• Must have same operating system on source and
  target machines
• Ideally, patch levels synchronized as well
• Other combinations may work but are not supported
• Develop a plan to maintain both systems
• Synchronised
• Staged with planned fail-over/fail-back for
  continuous availability

7
Planning for your InstallationThe Network Link

• Must have enough bandwidth to handle AI data
• Otherwise source DB might be throttled back
• How much is sufficient?

diagram hereconcept bottleneck
8
Computing Required Network Bandwidth

• Examine BI block writes
• promon RD/2/5 or _ActBI VST
• Net replication traffic will be 1.5x BI data rate
• E.g. 10 8k BI block writes second
• ? 15 x 8k 120kB/sec replication traffic
• ? 1.2Mbits/sec network bandwidth required
• If Ethernet hardware, allow 1.5-2x utilisation
  fudge factor due to CSMA/CD inefficiency

9
Computing Required Network Bandwidth
Considering Peaks
10
Computing Required Network Bandwidth
Considering Peaks

• Consider peaks when measuring BI block writes
• Are you willing to sacrifice performance for
  cost?
• When do peaks occur?
• Can they be handled by buffering?
• -pica broker parameter specifies replication
  buffers
• Is buffering a desirable policy option?
• buffering falling behind

11
Computing Required Network Bandwidth
Considering Peaks case study

• Average volume 2 BI writes/sec
• 256kb/sec sufficient for average
• Peaks 7/sec for 5 mins 3.3/sec for 25 mins

seconds to transmit
BI write rate Duration (s) Total blocks written
7 300 2100
3.3 1500 4950
Blocks to transmit 256kb/s 512kb/s 1Mb/s 2Mb/s
2100 656 328 164 82
4950 1546 773 386 193

Blocks/sec 3.2 6.4 12.8 25.6
performance-oriented
cost-effective
12
Planning for your InstallationDB Backup and
After Image Log Management

• On-line backup is preferable
• Implications for target DB if source is shut down
• Must plan how to handle AI data
• Back up DB AI extents
• Typically just throw away AI data after backup
• Some people choose to archive AI logs
• Use AI File Archiver if OpenEdge 10.1A

13
Replica DB Setup

• Need to plan replica DB(s)
• Layout, extent size, memory usage
• Recommend same as production
• Ordinary replication Source DB needs to know
  host and port of target DB(s)
• Role reversal replication (OpenEdge 10.1A only)
  Source and Target DBs need to know each others
  host/port

14
Agenda

• What is OpenEdge Replication?
• Planning for your installation
• First-time setup
• Common procedures
• Things you need to know
• Things you should think about
• Summary

15
First Time SetupRecommended Workflow
Plan
Enable source DBfor replication
brief (5 min)downtime
Implementinfrastructure
Backup source move to target system
Install Progress
Create target DB enable
Prepare DB structureand property files
Review / alter DB start parameters backup
scripts
Verify replicationrunning
16
First Time SetupPrepare Structure Files

• Source database needs After Image extents
• Recommend 5-7 extents
• Must be enough to cope with issues
• Target database needs structure description
• Recommend its the same as source DB
• At least make extents same size if you can

17
First Time SetupPrepare Property Files

• Replication requires property file
  dbname.repl.properties in .db directory
• Both source and target databases
• Template files located in DLC/properties
• source.repl.properties
• target.repl.properties
• Can be combined if you wish
• Not recommended for ordinary replication

18
First Time SetupPrepare Property Files Source
server control-agentsagent1
databasedbname transitionmanual
transition-timeout1200 defer-agent-startup14
00 schema-lock-actionwait control-agent.ag
ent1 nameagent1 databasedbname
hosttargetdb_host porttargetdb_port
connect-timeout120 replication-methodasync
critical0
Changes from sourcetemplate are shown in red
Unlikely these will need changing, but possible
19
First Time SetupPrepare Property Files Target
agent nameagent1 databasedb-name
listener-minport1500 adjust if needed
listener-maxport4500 adjust if needed

• This file usually does not need to be changed
  from template DLC/properties/target.repl.properti
  es

20
First Time SetupReview / Change Backup Scripts

• Need to empty any full AI extents
• Might even want to archive first
• probkup and rfutil mark backedup force AI extent
  switch
• May as well deal with AI in backup script
• Simple addition to current script

21
First Time SetupChanged Backup Scripts Some
Examples
One Unix site did this
while frfutil dbname C aimage extent full
do archive f here if you want to
rfutil dbname C aimage extent empty f done
One Windows site did this
rfutil dbname C aimage extent empty 1 rfutil
dbname C aimage extent empty 2 rfutil dbname C
aimage extent empty 3 rfutil dbname C aimage
extent empty 4 rfutil dbname C aimage extent
empty 5 rfutil dbname C aimage extent empty
6 rfutil dbname C aimage extent empty 7
22
First Time SetupEnable Source DB for
Replication (1 of 2)

• Shut down broker
• Add after-image log extents if not already
  present prostrct add dbname ai.st
• Enable after-imaging if not already
  enabled rfutil dbname C mark backedup rfutil
  dbname C aimage begin
• Remove any dbname.repl.recovery file (if present)
• Enable DB as replication sourceproutil dbname C
  enablesitereplication source

23
First Time SetupEnable Source DB for
Replication (2 of 2)

• Ensure broker parameters presentDBService
  replserv
• Start the broker (and use DB normally)
• The BI has been truncated you may wish to
  perform a proutil bigrow first
• Take a full online backupprobkup online dbname
  backup_file -REPLTargetCreation
• Dont forget REPLTargetCreation!
• If you do, you wont be able to establish a
  replication database from the backup
• Its case sensitive beware

24
First Time SetupTransport Backup to Target
System

• Use a method that suits your needs and resources
• Send via network
• Walk tape across room
• Drive tape to remote site
• Set up replica system at source site then
  transport to remote site
• Theres no particular hurry
• Reliability is critical

25
First Time SetupCreate Target DB (1 of 2)

• Make sure dbname.st file is in .db directory
• Restore backup prorest dbname backup_file
• This will implicitly call prostrct create with
  the correct block size
• Eliminates a source of error
• Remove any dbname.repl.recovery file (if present)

26
First Time SetupCreate Target DB (2 of 2)

• Convert repl. source DB to repl. targetproutil
  dbname C enablesitereplication target
• Ensure broker parameters presentDBService
  replagent
• Start the target DB broker

27
First Time SetupVerify Replication is Running

• Check source db.lg file, or
• Use dsrutil dbname C monitor
• Might have to wait up to 5 minutes
• defer_agent_startup checks every 5 minutes, tries
  for 1 minute each time

28
First Time SetupCommon Mistakes (1 of 2)

• Source/target port mismatch
• e.g. source.repl.properties says to expect target
  DB to be listening on 7001, but target actually
  listening on 7701
• Firewall in the way
• Connection between ReplServ and ReplAgent is
  independent of initial connection to DB
• Manipulate port range using agent
  listener-minport and listener-maxport parameters

29
First Time SetupCommon Mistakes (2 of 2)

• db.repl.recovery file left behind
• This file is used for recovery processing
• Must be deleted if initialising replication anew
• Seldom an issue in first-time install but common
  when re-establishing replication
• DBService parameter issue
• Required parameter in replication
• Mode must agree with database mode
• Must not be present if not replicating

30
First Time SetupOptimising Performance

• Configure / tune target DB as though it were live
• Likely needs less B but otherwise the same
• Dont forget BI block and cluster size
• Ensure you start APW BIW
• If you get slowdowns on source system, try adding
  replication buffers
• Max is pica 256
• Should not be necessary to tweak Replication
  polling properties

31
Agenda
Subtitle Goes Here - Arial Italic 24 pt.

• What is OpenEdge Replication?
• Planning for your installation
• First-time setup
• Common procedures
• Things you need to know
• Things you should think about
• Summary

32
Common procedures

• Using a DB copy or restored backup
• Handling throughput spikes
• Failing over
• Failing back

33
Common ProceduresUsing a DB Copy or Restored
Backup

• Its common to restore a backup or copy a live DB
  for analysis or reporting purposes.
• If you copy or restore a replication-enabled DB,
  it will still be replication-enabled afterwards.
• You need to disable the replication before you
  use it (specify the appropriate mode) proutil
  dbname C disablesitereplication
  source target

34
Common ProceduresThroughput Spikes

• Spikes in transaction throughput are common
• Usually associated with batch processing
• Can overwhelm capacity of network between source
  and target
• Replication buffers fill up
• Transaction activity paused until a replication
  buffer becomes available
• Might also be caused by competition for bandwidth

35
Common ProceduresHandling Throughput Spikes

• If objectionable slowdown occurs
• Stop replication server dsrutil dbname C
  terminate server
• Wait a short time
• Restart the replication server dsrutil dbname
  C restart server
• Replication will move to catchup mode
• Reading from AI file, not AI buffers
• Will resume using buffers when caught up (when
  surge passes)

36
Common ProceduresFailing Over (1 of 2)

• First, decide to fail over
• May prefer to recover/roll forward local DB if
  remote system or network is slow
• Recommend never allow automatic transition
• If source system still available, copy AI log
  files to target system
• Use rfutil db C aimage scan to determine log(s)
  to transport/apply
• Apply logs using dsrutil db C applyextent
  aifile

37
Common ProceduresFailing Over (2 of 2)

• Transition target DB to allow updates dsrutil
  targetdb C transition
• Check broker startup parameters
• Enable AI, source replication if you wish
• Done as part of transition in OpenEdge 10.1A
• Start DB

38
Common ProceduresOther Aspects of Failing Over

• Failing DB over is easy part
• You must manage client connections to new live
  system
• Many use a virtual DNS entry for DB /
  application server
• Hostname has no physical existence
• Change IP addr to suit current live host
• Requires no user training and no multiple icons
  (etc).
• User authentication, application currency, etc.

39
Common ProceduresFailing Back

• Fail back is resuming normal operation after
  the reason for a fail-over has gone away
• Is a planned fail-over
• Are a number of techniques
• Backup-restore-reinitialise
• Back-replication
• Recommend back-replication technique
• More complex but less downtime

40
Common ProceduresFailing Back

• Set up replication to repaired primary system
• Manual form of OpenEdge 10.1A role reversal
• Once synchronised, fail over to primary
• At a convenient time e.g. weekend
• Re-establish replication once failed over
• OpenEdge 10.1A role reversal removes need to
  transport backup to secondary all over again

41
Agenda

• What is OpenEdge Replication?
• Planning for your installation
• First-time setup
• Common procedures
• Things you need to know
• Things you should think about
• Summary

42
Things You Need to Know

• Replication and AdminServer-managed DBs
• Schema updates
• Effect of DB stops and starts
• Effect of network failures
• Moving tables and indexes
• Truncating a storage area

43
Things You Need to KnowReplication and
AdminServer-Managed DBs

• AdminServer detects replication status of a
  database
• Shows properties in Progress Explorer
• Allows you to update properties
• Detection of mode is based on .repl.properties
  file, not DB master block
• server section means its a source DB
• agent section means its a target DB

44
Things You Need to KnowReplication and
AdminServer-Managed DBs

• AdminServer will automatically supply a
  DBService parameter(s) based on detected
  mode(s)
• Convenient!
• Will supply both DBService replserv and
  -DBService replagent if both modes detected
• Will supply DBService even if a transitioned
  database
• Result DB wont start
• Workaround Rename .repl.properties file

45
Things You Need to KnowSchema Updates

• You have to have an active replication connection
  established to perform a schema update
• Progress requires schema locks in both source and
  target databases simultaneously.
• Implications for DBA practise
• Keeping users out of a multiuser-enabled database.

46
Things You Need to KnowEffect of DB Stops and
Starts

• Source DB is shut down
• Target DB sees this as normal exit
• Replication agent exits
• Source DB restart does not cause agent to restart
• Must bounce target DB as its not possible to
  restart the replication agent.
• If OpenEdge 10.1A, can configure away replic
  agent shutdownserver agent-shutdown-action
  recovery

47
Things You Need to KnowEffect of DB Stops and
Starts

• Target DB is shut down
• Source DB sees this as normal exit
• Replication server ends if this was the only
  target
• After restarting target DB, need to restart
  replication server dsrutil dbname C server
  restart

48
Things You Need to KnowEffect of DB Stops and
Starts

• Source DB crashes
• All is well replication agent remains running
• Will resync when source DB is restarted
• If wait too long replication agent may exit
• Restart target DB or
• Use dsrutil sourcedb C connectagent database all
  
• Target DB crashes
• All is well replication server remains running
• Will resync when target DB is restarted
• Replication data will build up in AI log files
  until replicated

49
Things You Need to KnowEffect of Network
Failures

• All is well both replication server and agent
  remain running
• When connection re-established replication will
  resync
• Replication data will build up in AI log files
  until replicated
• Depends on hardware connection sensing
• Networks that spoof this can cause problems
  worked around with repl-keep-alive property

50
Agenda

• What is OpenEdge Replication?
• Planning for your installation
• First-time setup
• Common procedures
• Things you need to know
• Things you should think about
• Summary

51
Things You Should Think About

• Handling extended replication outage
• Managing database extents
• Managing database parameters
• Automation

52
Things You Should Think AboutExtended
Replication Outage

• Problem Unreplicated data in AI logs cause logs
  to be Locked not Full
• Cannot be cleared while replication still enabled
  and no connection established
• Extended outage will cause AI logs to fill
• You need to plan what do to if this happens
• Disable replication?
• Add AI extents?
• Relocate replication target until outage fixed?
• etc

53
Things You Should Think AboutManaging Database
Extents

• Replica database is an ordinary database with
  usage constraints
• Grows just like the source DB
• Plan how youre going to monitor free space in
  the replica DB
• Periodic login?
• Automated scripts with e-mail?
• OpenEdge Management?
• Avoid the problem Same structure as source
• Perform all prostrct operations on both DBs

54
Things You Should Think AboutManaging Database
Parameters

• Target DB needs to support same transaction
  activity as source
• Most broker parameters should be the same
• Target system may be smaller (memory)
• Requires smaller B parameter
• Care must be taken in replicating application
  files if they include .pfs or scripts

55
Things You Should Think AboutAutomation

• Automation is the DBAs friend
• Especially in this case
• Use scripts to coordinate local/remote DB stops
  and starts via remote execution
• Use scripts to hide details of DBService
  provision
• Hide messy details of Replication
• Dont forget to document, though!

56
In Summary

• Replication is simple to install and use
• Gives near real-time replica for reporting or D.R.

57
Questions?
58
Thank you foryour time
59
(No Transcript)
60
Appendix Setting up Role-Reversal Replication

• Differs mainly in setup of property files
• Both primary and secondary files have both source
  and target components
• Some terminology differences
• Primary Normal source (acts as target in
  fail-over situation)
• Secondary Normal target (acts as source in
  fail-over)
• Secondary DB should have AI extents
• Not needed in ordinary replication

61
Prepare Property Files OpenEdge 10.1A (1 of 2)
server control-agentsagent1
databasedbname transitionmanual
transition-timeout600 defer-agent-startup144
0 schema-lock-actionwait control-agent.age
nt1 nameagent1 databasedbname
hostpartner_db_host portpartner_db_port
connect-timeout120 replication-methodasync
critical0 ...
62
Prepare Property Files OpenEdge 10.1A (2 of 2)
... transition database-rolereverse
responsibilityprimary restart-after-transiti
on0 auto-begin-ai1 auto-add-ai-areas0
backup-methodmark agent nameagent1
databasedbname listener-minportminport
listener-maxportmaxport connect-timeout120
63
Enable Primary DB for Replication (1 of 2)

• Shut down broker
• Add after-image log extents if not already
  present prostrct add dbname ai.st
• Enable after-imaging if not already
  enabled rfutil dbname C mark backedup rfutil
  dbname C aimage begin rfutil dbname C
  aiarchiver enable
• Enable DB as replication sourceproutil dbname C
  enablesitereplication source

64
Enable Primary DB for Replication (2 of 2)

• Add to broker parametersDBService replserv
  aiarcinterval 0 aiarcdir /dev/null
• Start the broker (and use DB normally)
• The BI has been truncated you may wish to
  perform a proutil bigrow first
• Take a full online backupprobkup online dbname
  backup_file -REPLTargetCreation
• Dont forget REPLTargetCreation!
• If you do, you wont be able to establish a
  replication database from the backup
• Its case sensitive beware

65
Create Secondary DB (1 of 2)

• Make sure dbname.st file is in .db directory
• Make sure dbname.repl.properties is present and
  correct
• Restore backup prorest dbname backup_file
  -REPLTransition
• This will implicitly call prostrct create with
  the correct block size
• Eliminates a source of error
• Enable AI archiver rfutil dbname C aiarchiver
  enable

66
Create Secondary DB (2 of 2)

• Remove any dbname.repl.recovery file (if present)
• Ensure broker parameters presentDBService
  replagent aiarcinterval 0 -aiarcdir /dev/null
• Start the target DB broker

67
Other Issues in Role-Reversal

• It is possible to automatically trigger both
  halves of a transition
• Personally, I recommend against this
• Better to have the ability to decide whether to
  transition the source after seeing whether the
  target transition succeeded
• It is possible to have the database automatically
  started after transition
• I also recommend against this as it constrains
  your DB parameter specification choices

68
Computing Required Network Bandwidth
Network Latency

• Latency is time required for round trip
• Effectively 0 in modern LAN
• Typically 30ms for trans-continental WAN link
• But can vary widely gtgt100ms depending on traffic,
  QoS, etc, etc, etc
• Replication agent also uses some time
• AI blocks must be buffered until ACK reaches
  server

69
Computing Required Network Bandwidth
Buffering needed to support network latency
Number of buffers needed to support network
latency
Round-trip latency including replication processing (ms) Round-trip latency including replication processing (ms) Round-trip latency including replication processing (ms) Round-trip latency including replication processing (ms) Round-trip latency including replication processing (ms) Round-trip latency including replication processing (ms) Round-trip latency including replication processing (ms)
Data rate 10 20 50 100 200 500 1000
2 1 1 1 1 1 1 2
5 1 1 1 1 1 2.5 5
10 1 1 1 1 2 5 10
20 1 1 1 2 4 10 20