DM206 Sybase Replication Agent for Microsoft NT and UNIX

1
DM206Sybase Replication Agent for Microsoft NT
and UNIX
Wanda Beck Senior Software Engineer Enterprise
Solutions Division wbeck_at_sybase.com
2
Overview

• e-Business Replication
• Quick Replication Overview
• What is Replication?
• What are the Components of Replication?
• What is a Replication Agent?

3
Overview (cont.)

• SybaseReplication Agent for Microsoft NT and
  UNIX
• Architecture
• Specific Functions
• Large Object Replication
• Function Replication
• Management by RepServer Manager
• Heterogeneous Replication
• Migration from existing Replication Agents

4
Sybase e-Business Strategy
Mobile Wireless
Vertical Solutions
Enterprise Portal
5
Portal-Ready Requirements

Database Requirements
6
Sybase Replicatione-Business Infrastructure
7
Quick Replication Overview Replication Choices

• Batch or Snapshot Updates
• Can be large amounts of data
• Data is not up to date
• Real-time Updates
• Application based ( e.g., 2-phase commit )
• Not easily expanded
• Transaction Replication
• Sybase Replication

8
Quick Replication Overview Transaction
Replication

• Sybase replication maintains replicated data in
  multiple databases while ensuring the integrity
  and consistency of the data
• Sybase replication can replicate subsets of data
  at the table, data row, or column level
• Sybase replication supports heterogeneous data
  servers
• Sybase replication uses a basic
  publish-and-subscribe model

9
Quick Replication OverviewAdvantages of
Replication

• Remote Distribution
• Provide immediate access to current information
  such as inventory, pricing
• Corporate Rollup
• Provide immediate updates to central decision
  support applications
• Acquisition
• Local Duplication

10
Quick Replication OverviewAdvantages of
Replicating Local Data

• Provides clients with local access to enterprise
  data
• Improved performance and greater data
  availability
• Replicate databases contain accurate, current
  copies or subsets of data from a primary database
• Allows slower, process-intensive queries to run
  against the same data without slowing
  mission-critical applications

11
Replications Main ComponentsPrimary Database
12
Replications Main ComponentsPrimary Database

• The primary database is the source of the
  replicated transactions
• Transactions are replicated by table or procedure
• A primary table or procedure must be identified
  (marked) for replication so that a Replication
  Agent can replicate the transactions affecting
  the data in that table

13
Replications Main ComponentsReplication Agent
Primary Database
Replication Agent
14
Replications Main ComponentsReplication Agent

• Replication Agent transfers transactions from the
  primary database to Replication Server for
  distribution to replicate databases
• Accesses transactions in a transaction log
• Generates Log Transfer Language commands (LTL)
  for Replication Server

15
Replications Main ComponentsReplication Server
RSSD
Primary Database
Replication Agent
Replication Server
16
Replications Main ComponentsReplication Server
RSSD

• A Replication Server may be a primary, or a
  replicate, or both
• Replication Server replicates data to one or more
  replicate sites
• Transactions are stored in stable queues
• Replication Server guarantees transaction
  delivery
• Replication Definitions and Subscriptions
• Information is stored in the Replication Server
  System Database (RSSD)

17
Replications Main ComponentsReplicate Database
Primary Database
Replication Agent
Replication Server
Replicate Database
18
Replications Main ComponentsReplicate Database

• The replicate database is any target that will
  hold the results of replicated transactions
• Sybase supports various targets, including
• Direct Connect DB2, Oracle, Informix
• InfoHub DB2, VSAM, IMS, IDMS, ADABAS
• OmniConnect DB2, Informix, Oracle, DEC RDB,
  Ingress, SQL Server
• Replication Driver for ODBC Lotus Notes,
  Microsoft SQL Server, Microsoft Access

19
Replications Main ComponentsSummary
Replicate Sites
Primary Sites

• Adaptive Servers/IQ/ Anywhere/ Enterprise

Replication Agents
Replication Server

• DB2
• AS/400
• Oracle
• ODBC

• Informix
• MS SQL
• UDB

DirectCONNECT OmniCONNECT

• OS/390 DB2
• Adaptive Server Enterprise
• Adaptive Server Anywhere
• Replication Toolkit for MVS
• Oracle
• Informix
• Microsoft SQL Server
• IBM DB2 Universal Database

Mobile Users
SA
SQL Remote
Adaptive Server Anywhere
20
Which Replication Agent For Your System?

• RepAgent - for Adaptive Server
• Log Transfer Manager (LTM) - for Sybase SQL
  Server data servers
• Replication Agents for foreign data servers
• IBM DB2
• Replication Toolkit for MVS
• Oracle
• Informix
• Microsoft SQL Server
• IBM DB2 Universal Database

21
ArchitectureWhat makes a Replication Agent?

• Transaction Log
• Application Interface
• Log Reader
• Log Transfer Interface
• Log Transfer Manager

22
What makes a Replication Agent?Transaction Log
Transaction Log
Replication Agent
23
What makes a Replication Agent?Transaction Log

• This can be a log provided by the Primary
  Database ( e.g., DB2 ) or can be created by the
  Replication Agent ( Sybase Replication Agent )
• The log must contain
• Transaction begin/commit boundaries
• Operation type ( Insert, Update, Delete )
• Before and after images of the data
• Column metadata

24
What makes a Replication Agent?Application
Interface and Log Reader
Transaction Log
Application Program Interface
Replication Agent
25
What makes a Replication Agent?Application
Interface and Log Reader

• Application Interface
• The set of exposed commands that allows
  configuration or manipulation of the Replication
  Agents processing and selection of objects to be
  replicated
• Log Reader
• The Log Reader reads the Transaction Log data for
  changes to tables marked for replication. It
  retrieves all the relevant information and
  delivers it to the Log Transfer Interface.

26
What makes a Replication Agent?Log Transfer
Interface
LTL
Log Transfer Interface
Transaction Log
Application Program Interface
Replication Agent
27
What makes a Replication Agent?Log Transfer
Interface

• Translates the command structures received from
  the Log Reader into Log Transfer Language (LTL)
• Batches the LTL in a buffer, and delivers the
  buffer to the Replication Server
• Replication Server translates the LTL statements
  into transactions to be applied at the replicate
  site(s)

28
What makes a Replication Agent?Log Transfer
Manager
Log Transfer Manager
LTL
Log Transfer Interface
Transaction Log
Application Program Interface
Replication Agent
29
What makes a Replication Agent?Log Transfer
Manager

• The Log Transfer Manager is the controlling agent
• Manages the interaction of the Log Reader and Log
  Transfer Interface
• Manages connections to the Primary Database and
  Primary Replication Server

30
What makes a Replication Agent?Summary
Log Transfer Manager
LTL
Log Transfer Interface
Transaction Log
Application Program Interface
Replication Agent
31
ArchitectureSybase Replication Agent

• Object-Oriented model written in Java
• The Write Once, Run Anywhere promise
• Memory Management
• Speed
• JDBC ( Java DataBase Connectivity ) for Primary
  database communication
• Simplifying the communication topography

32
ArchitectureSybase Replication Agent
Sybase RepAgent
33
ArchitectureConnectivity to Primary Database
Sybase RepAgent
34
ArchitectureConnectivity to Replication Server
Sybase RepAgent
35
ArchitectureAdministration - isql
Sybase RepAgent
36
ArchitectureAdministration - RSM
Sybase RepAgent
37
ArchitectureAdministration
Sybase RepAgent
38
ArchitectureSybase RepAgent Transaction Log

• Triggers are added to each table
• Transaction information is stored in shadow
  tables and transaction logs that exist in the
  primary database
• Other tables are installed in the Primary
  Database for other housekeeping
• Stored procedures and functions to reduce network
  traffic and improve turnaround

39
ArchitectureSybase RepAgent Transaction Log
(cont.)
Primary Tables
AUTHORS
TITLES
PUBLISHERS
Transaction Log Tables
preTRAN_LOG_
preSH_a
preSH_b
preSH_c
shadow tables
40
ArchitectureSybase RepAgent Transaction Log
(cont.)
preMARKED_OBJS_
Insert
preSH_a
AUTHORS
preTRAN_LOG_
41
ArchitectureSybase RepAgent Replication
Log Transfer Manager
JDBC Result Sets
LTL
Log Transfer Interface
Sybase Replication Agent
42
ArchitectureSybase RepAgent Transaction Log
(cont.)

• Primary Database Impact
• Oracle With Sybase Replication Agent on Oracle
  and logging turned on, the Oracle server takes
  about four times as long to complete the same
  transaction set
• Informix With Sybase Replication Agent on
  Informix and logging turned on, the Informix
  server takes not quite three times as long to
  complete the same transaction set
• Note These are casual comparisons

43
Sybase Replication AgentSpecific Functionality

• Configurable Transaction Log Naming
• Configurable Transaction Log Location
• Optional Script Execution
• Object Marking
• Tables, Stored Procedures, Large Objects
• Case-sensitive names are supported
• Multi-part names are supported (e.g.,
  owner.tablename)
• Aliases or synonyms are supported
• Replicated As
• Disabling Replication
• Individual marked objects

44
Specific Functionality ( Cont. )

• Transaction Log Management
• Manual or Automatic Truncation
• Accessing the RSSD
• Filter out columns not included in the
  Replication Definition
• Remove column names from the LTL
• Filtering Maintenance User Transaction
• LTL Column Compression

45
Specific Functionality ( Cont. )

• Internal Statistics Generation
• Logging and Tracing
• One installation can execute multiple instances
  of the Replication Agent

46
Specific FunctionalitySummary

• Increased Flexibility
• Over 100 commands and configuration options
• Simplified topology
• Improved Performance
• Multi-threaded
• Reduced Network Traffic
• Usability
• Log Management, Tracing

47
More Features LOB replication

• Large Objects ( LOB ) Character or Binary
• LOB columns are marked independently
• Performance Concerns
• Those highly concerned with replication
  performance should avoid LOB replication.
• Put LOBs in their own tables
• Logging Limitations
• Fetching data directly from the primary database
• Transaction Integrity Concerns

48
More Features LOB replicationTransaction
Integrity Concerns
49
More Features Function Replication

• Function Replication for Performance
• A procedure that updates 50,000 rows can be
  replicated to execute the same procedure at the
  replicate database instead of replicating 50,000
  rows of update operations
• Supports both Applied and Request Function
  Replication
• Applied Function executed at the primary
• Request function executed at the replicate
• Disabling Replication

50
Function ReplicationApplied Function
Execute mass_update
Execute mass_update
Replication Agent
Replication Server
START
51
Function ReplicationRequest Function
Execute mass_update
Execute mass_update
Replication Agent
Replication Server
Execute request_update
START
Execute request_update
Replication Agent
52
Management byReplication Server Manager

• Rep Server Manager ( RSM ) is a client/server
  application that you use to administer the
  servers in a distributed replication system
• RSM is a plug-in to Sybase Central
• RSM accesses the Replication Agent via an Open
  Server Interface
• The Replication Agent acts as RSMs gateway
• RSM monitors the Replication Agent System log
• RSM can alter Replication Agent configuration
  parameters

53
Replication Server Manager
54
Replication Server Manager
55
Replication Server Manager
56
Replication Server Manager
57
Heterogeneous ReplicationWhat if I want to
replicate DB2 to Oracle?

• Different data servers support different types of
  data and different formatting
• Create Custom Function Strings to convert
• Can be a lot of work
• Difficult to maintain
• Use OMNI/CIS as a Proxy to hidden Replicate
• Adds another layer of complexity

58
Heterogeneous ReplicationWhat if I want to
replicate DB2 to Oracle?

• Rep Server 12.0 avoids Sybase proxy or custom
  function string creation
• DB2 mask CCYY-MM-DD-hh.mm.ss.fffff
• Oracle mask MM-DD-YY
• Supplied with the Replication Agent
• Default function strings for Target
• Default data type conversions

59
Heterogeneous ReplicationWhat if I want to
replicate DB2 to Oracle?

• Next Major Release
• No Sybase data type limitation ( char and
  varchar gt 255 )
• User defined data types

60
Sybase Replication AgentSummary

• Improved Flexibility and Performance
• LOB and Function Replication
• RSM management
• Heterogeneous Replication

61
Migration from existingReplication Agent for
Oracle

• Overall increase in performance and flexibility
• Access Server replaced with JDBC Driver
• Several executables now combined into one
• Backup, Dump and Restore utilities removed
• Existing connection / RepDefs can be used
• LOB and Function replication
• Update configuration parameters real-time

62
Migration from existingReplication Agent for
Oracle (cont.)

• Configure to use RSSD information
• Trigger creation on primary tables now merges
  with existing triggers
• Table naming convention changed to eliminate
  possibility of exceeding 30-character names
• Migration requires quiescing RepServer and
  zeroing rs_locator
• Requires full removal of old RAO Transaction Log

63
Migration from existing Replication Agent for
Informix

• Overall increase in performance
• LOB and Function replication
• Migration requires quiescing Rep Server and
  zeroing rs_locator
• Existing connection / Rep Defs can be used
• Requires full removal of old RAI Transaction Log

64
Sybase Replication Agent for Microsoft NT and UNIX

• Availability
• Now
• Informix versions 7.2, 7.3
• Microsoft SQL Server versions 6.5, 7.0
• Oracle versions 7.3, 8.0, 8.1.5
• Platforms NT 4.0 SP4, Solaris 2.6, AIX 4.3.2,
  HP-UX 11.0
• Q4, 2000
• IBM DB2 Universal Database version 6.1
• Informix 2000 version 9.2
• Questions?