You are previewing Sybase 15.0 Replication Server Administration.
O'Reilly logo
Sybase 15.0 Replication Server Administration

Book Description

Sybase 15 Replication Server Administration addresses the needs of a wide range of database professionals, explaining to both beginners and experts how to administer Sybase’s newest Replication Server release. This book examines all the knowledge, background information, and conceptual frameworks needed in order to get started on installing and administering Sybase Replication Server, and explores the world of contemporary cross-platform compatible Sybase Replication Server administration. Learn how to replicate business-critical data; configure database connections and routes; manage replicated tables, stored procedures, and subscriptions; set up a warm standby system; monitor replication performance and tune the database system; provide up-to-the-minute high availability of data; recover from failures and prevent data loss; troubleshoot the replication system.

Table of Contents

  1. Contents
  2. Acknowledgments
  3. Introduction
  4. Chapter 1 Data Replication
    1. Why Replicate Data?
    2. Advantages of Data Replication
    3. Data Replication and Failover Methods
      1. Hardware Data Replication
      2. Software Data Replication
      3. Software/Hardware Replication System
  5. Chapter 2 The Replication Server Architecture
    1. ID Server
    2. Replication System Domain
    3. Inbound Queue
    4. Outbound Queue
    5. Connection
    6. Route
    7. Replication Server Threads
      1. RepAgent
      2. Stable Queue Manager (SQM)
      3. Stable Queue Transaction (SQT)
      4. Executor (EXEC)
      5. Distributor (DIST)
      6. Subscription Resolution Engine (SRE)
      7. Transaction Delivery Module (TD)
      8. Message Delivery Module (MD)
      9. Data Server Interface (DSI)
        1. Duplicate Detection
        2. Loss Detection
      10. Replication Server Interface (RSI)
    8. Replication Server Daemons
      1. Alarm Daemon (dAlarm)
      2. Async I/O Daemon (dAIO)
      3. Connection Manager Daemon (dCM)
      4. SySAM Daemon (dSYSAM)
      5. Subscription Retry Daemon (dSUB)
      6. Recovery Daemon (dREC)
  6. Chapter 3 Replication Design and Implementation Strategies
    1. Replication Design Terminology
      1. Distributed Data vs. Replicated Data
      2. Latency
      3. Tight Consistency vs. Loose Consistency
      4. Asynchronous Replication
    2. Replication Server Process Flow
    3. A Simplified Replication Model
    4. Application Types for Replication
    5. Replication Designs
      1. One-to-Many Regular Replication Model
      2. Multi-Site Availability Model with Warm Standby
      3. Peer-to-Peer Replication Model
        1. One-to-Many Peer-to-Peer Replication Model
        2. Many-to-Many Peer-to-Peer Replication Model
        3. A Peer-to-Peer Replication Model with Warm Standby
    6. Replication Design Strategies
      1. Replication Strategy for Backup
      2. Replication for Application Partitioning
      3. Business Intelligence
    7. Managing Intersite Update Conflicts
  7. Chapter 4 Replication Server Installation and Upgrade
    1. Pre-installation
    2. Replication Server Installation
      1. Software Extraction Using InstallShield
      2. Replication Server Installation Using Console Mode
      3. Generating a Reusable Resource File
      4. Preparing a Resource File or Response File
      5. Installing Replication Server Using rs_init Utility with the Response File
        1. Configuration File
        2. Typical RUN File
    3. Post Installation
      1. Replication Server Initial Configuration
      2. Adding a Database to the Replication System
    4. Replication Server Upgrade
      1. Installing the New Replication Server Software
      2. Upgrading the Replication Server RSSD
      3. Post RSSD Upgrade Process
      4. Upgrading a User Database
      5. Setting the Site Version
  8. Chapter 5 Configuration Parameters
    1. Configuring Replication Server
      1. batch
      2. batch_begin
      3. byte_order
      4. cm_fadeout_time
      5. cm_max_connections
      6. ha_failover
      7. memory_limit
      8. num_concurrent_subs
      9. num_msgqueues
      10. num_msgs
      11. num_mutexes
      12. num_stable_queues
      13. num_threads
      14. oserver
      15. password_encryption
      16. rec_daemon_sleep_time
      17. rssd_error_class
      18. smp_enable
      19. sqm_recover_segs
      20. sqm_warning_thr1
      21. sqm_warning_thr2
      22. sqm_warning_thr_ind
      23. sqm_write_flush
      24. sqt_init_read_delay
      25. sqt_max_cache_size
      26. sqt_max_prs_size
      27. sqt_max_read_delay
      28. sre_reserve
      29. sub_daemon_sleep_time
      30. sts_cachesize
      31. sts_full_cache_rssd_system_table
      32. stats_sampling
      33. stats_show_zero_counters
      34. use_security_services
      35. use_ssl
      36. varchar_truncation
    2. Configuration Parameters for Both Database Connection and Route
      1. disk_affinity
      2. msg_confidentiality
      3. msg_integrity
      4. msg_origin_check
      5. msg_replay_detection
      6. msg_sequence_check
      7. mutual_auth
      8. save_interval
      9. security_mechanism
      10. unified_login
    3. Configuration Parameters Unique to Database Connection (DSI)
      1. command retry
      2. db_packet_size
      3. dist_sqt_max_cache_size
      4. dsi_charset_convert
      5. dsi_cmd_batch_size
      6. dsi_cmd_separator
      7. dsi_commit_check_locks_intrvl
      8. dsi_commit_check_locks_log
      9. dsi_commit_check_locks_max
      10. dsi_commit_control
      11. dsi_exec_request_sproc
      12. dsi_fadeout_time
      13. dsi_ignore_underscore_name
      14. dsi_isolation_level
      15. dsi_keep_triggers
      16. dsi_large_xact_size
      17. dsi_max_cmds_to_log
      18. dsi_max_text_to_log
      19. dsi_max_xacts_in_group
      20. dsi_num_large_xact_threads
      21. dsi_num_threads
      22. dsi_partitioning_rule
      23. dsi_replication
      24. dsi_replication_ddl
      25. dsi_rs_ticket_report
      26. dsi_serialization_method
      27. dsi_sql_data_style
      28. dsi_sqt_max_cache_size
      29. dsi_text_convert_multiplier
      30. dsi_xact_group_size
      31. dump_load
      32. dynamic_sql
      33. dynamic_sql_cache_management
      34. dynamic_sql_cache_size
      35. exec_cmds_per_timeslice
      36. exec_sqm_write_request_limit
      37. md_sqm_write_request_limit
      38. parallel_dsi
      39. rep_as_standby
      40. sub_sqm_write_request_limit
    4. Configuration Parameters Unique to Replication Server Route
      1. rsi_batch_size
      2. rsi_fadeout_time
      3. rsi_packet_size
      4. rsi_sync_interval
    5. Security Configuration Parameters for Connecting to the ID Server
      1. id_msg_confidentiality
      2. id_msg_integrity
      3. id_msg_origin_check
      4. id_msg_replay_detection
      5. id_msg_sequence_check
      6. id_mutual_auth
      7. id_security_mechanism
      8. id_server
      9. id_unified_login
    6. Other Valid Replication Server Configuration Parameters
      1. current_rssd_version
      2. minimum_rssd_version
      3. md_source_memory_pool
      4. memory_max
    7. Embedded RSSD Configuration Parameters
      1. erssd_backup_interval
      2. erssd_backup_path
      3. erssd_backup_start_time
      4. erssd_backup_start_date
      5. erssd_ra
  9. Chapter 6 Replication Agent
    1. Replication Agent 15.0
      1. Oracle Support
      2. UDB Support
      3. Microsoft SQL Server 2005 Support
      4. Sybase Adaptive Server Enterprise 15.0 Support
    2. Common Installation Requirements for Replication Agent 15.0
    3. Replication Agent Configuration
    4. Replication Agent Installation for Sybase Databases
      1. Add Replication Agent for Sybase Databases
      2. Configure RepAgent for the Database
    5. Replication Agent Installation for Non-Sybase Databases
      1. Pre-installation Requirements
      2. Installation Process
      3. Post-installation Administration
  10. Chapter 7 Connections
    1. Creating Connections Using Create Connection
    2. Altering a Connection
    3. Dropping Connections
    4. Monitoring Connections
  11. Chapter 8 Routes
    1. Types of Routes
      1. Direct Route
      2. Indirect Route
      3. Unsupported Routes
    2. Creating Routes
    3. Altering Routes
      1. Altering RSI User Password
      2. Changing an Indirect Route to a Direct Route
      3. Changing a Direct Route to an Indirect Route
      4. Changing the Next Site (Intermediate Site) for an Indirect Route
      5. Changing Route Parameters
    4. Suspending and Resuming Routes
    5. Dropping Routes
    6. Monitoring Routes
    7. Upgrading Routes
  12. Chapter 9 Managing Replicated Tables and Stored Procedures
    1. Replication Definitions and Subscription Definitions
      1. Create a Replication Definition
      2. Altering a Replication Definition
      3. Marking Tables for Replication
      4. Replicating Tables with text, unitext, image, and rawobject Datatypes
      5. Replicating Tables with Computed Columns
      6. Replicating Tables with Encrypted Columns
      7. Replicating Tables with Different Ownership
      8. Creating Replication Definition for Java Columns
    2. Managing Replicated Stored Procedures
      1. Function Replication
      2. Asynchronous Procedures
      3. Setting Up Function Replication
        1. Setting Up Applied Function Replication
      4. Setting Up Request Function Replication
    3. Creating Publications
      1. Dropping Publications
      2. Dropping Articles
      3. Disabling Replication for a User Object
    4. Dropping Replication Definitions
  13. Chapter 10 Subscriptions
    1. Subscription Coordination Process
    2. Materialization
      1. Atomic Materialization
      2. Nonatomic Materialization
      3. No Materialization
      4. Bulk Materialization
    3. Subscription Dematerialization
    4. Creating Subscriptions Using Atomic Materialization
    5. Creating Subscriptions Using Bulk Materialization
    6. Subscriptions and Autocorrection
    7. Materializing text, unitext, image, and rawobject Data
  14. Chapter 11 Functions and Function Strings
    1. DSI Thread and Function Strings
    2. Managing Function Strings
    3. Creating a User-defined Function
    4. Creating a Function-String Class
    5. Creating a Function String
      1. Assigning a Function-String Class to a Connection
    6. Dropping a Function String
    7. Dropping a Function
    8. Dropping a Function-String Class
  15. Chapter 12 Warm Standby
    1. Warm Standby vs. Hot Standby System
    2. Warm Standby Components
    3. Regular Replication vs. Warm Standby
      1. Data Modification Language (DML)
      2. Data Definition Language (DDL)
    4. Enabling Replication to Standby Database
    5. Setting Up Warm Standby
      1. Creating a Logical Connection
      2. Adding the Active Database
      3. Enabling Replication in Active Database
      4. Adding the Standby Database
        1. Mapping SUIDs
        2. Adding the Maintenance User to the Standby Database
        3. Initializing the Standby Database
        4. Adding the Standby Database to the Replication System
        5. Maintenance User Permissions
      5. Enabling Replication in the Standby Database
        1. Altering the Warm Standby Connections
        2. Altering the Logical Connection
        3. Replicating truncate table to the Standby Database
        4. Altering the Physical Connections
    6. Warm Standby as Primary
    7. Warm Standby as Replicate Database
    8. System Tables and System Stored Procedures for Warm Standby Setup
      1. rs_databases
      2. rs_helpdb
      3. rs_lastcommit
      4. rs_locater
    9. Using Replication Definitions and Subscriptions
    10. Switchover
  16. Chapter 13 Multi-Site Availability
    1. RSSD System Tables Relevant to MSA
    2. MSA Setup
      1. Setting Up Multiple Replicate Databases
        1. Basic Steps
        2. Switchover
        3. Dropping a Database Subscription
        4. Dropping Database Replication Definition
        5. Altering a Database Replication Definition
    3. Warm Standby vs. MSA
    4. MSA Limitations
  17. Chapter 14 Performance Monitoring and Tuning
    1. Common Mistakes
      1. Monitoring with admin who
      2. Improper Replication Server Configuration
      3. Ignoring the Replicate DBMS
      4. Application Design Issues
      5. Unrealistic Expectations
    2. Performance Monitoring Tools
      1. rs_ticket
        1. rs_ticket Implementation
        2. rs_ticket Calculations
        3. rs_ticket Usage
      2. RS Monitor Counters
        1. Statistics Configuration
        2. Starting the Sampling
        3. Run-time/Client Statistics Viewing
        4. Stopping the Sample
        5. Analyzing the Statistics
      3. DBMS Metrics
      4. Third-Party Solutions
    3. Analyzing Performance Metrics
      1. Metrics to Focus On
        1. Rate
        2. Time
        3. Bell Ringers
        4. Caches and Queues
        5. Configuration
      2. Replication Agent User (EXEC)
        1. Common Problems
        2. Metrics to Focus On
          1. Rate
          2. Time
          3. Bell Ringers
          4. Caches and Queues
        3. Tuning for Performance
          1. RepAgent Packet and Buffer Size
          2. RepAgent Scan Batch Size
          3. LTL Syntax Compression
      3. Stable Queue Manager (SQM)
        1. Common Problems
        2. Metrics to Focus On
          1. Rate
          2. Time
          3. Bell Ringers
          4. Caches and Queues
        3. Tuning for Performance
          1. disk_affinity
          2. exec_sqm_write_request_limit and md_sqm_write_request_limit
          3. sqm_recover_seg
          4. sqm_write_flush
          5. init_sqm_write_delay and init_sqm_write_max_delay
      4. Stable Queue Transaction (SQT)
        1. Common Problems
        2. Metrics to Focus On
          1. Rate
          2. Time
          3. Bell Ringers
          4. Caches and Queues
        3. Tuning for Performance
          1. sqt_max_cache_size and dist_sqt_max_cache_size
          2. sqt_init_read_delay and sqt_max_read_delay
      5. Distributor (DIST)
        1. Common Problems
        2. Metrics to Focus On
          1. Rate
          2. Time
          3. Bell Ringers
          4. Caches and Queues
        3. Tuning for Performance
          1. sts_cachesize
          2. sts_full_cache
      6. Data Server Interface (DSI)
        1. Common Problems
        2. Metrics to Focus On
          1. Rate
          2. Time
          3. Bell Ringers
          4. Caches and Queues
        3. Tuning for Performance
          1. dsi_sqt_max_cache_size
          2. dsi_max_xacts_in_group and dsi_xact_group_size
      7. DSI Executor (DSI EXEC)
        1. Common Problems
        2. Metrics to Focus On
          1. Rate
          2. Time
          3. Bell Ringers
          4. Caches and Queues
        3. Tuning for Performance
          1. Replication Definitions and Unique Indexing
          2. Disabling or Optimizing Triggers
          3. Replication Definitions and Minimal Column Replication
          4. Increase ULC and Enable Delayed Commit
          5. Use Dynamic SQL/Prepared Statements
          6. Enable Statement Cache with Literal Parameterization
          7. Parallel DSI Contention and Group Sizing/Partitioning
          8. Parallel DSIs and “Collapsing Updates”
      8. Replication Server Interface (RSI) and RSI User
        1. Common Problems
        2. Metrics to Focus On
          1. Rate
          2. Time
          3. Bell Ringers
          4. Caches and Queues
        3. Tuning for Performance
          1. disk_affinity
          2. rsi_batch_size
          3. rsi_sync_interval
          4. rsi_packet_size
    4. Monitoring Performance
      1. Establishing a Heartbeat
      2. Collecting Replication Server Monitor Counters
      3. Primary and Replicate DBMS
  18. Chapter 15 Disaster and Recovery
    1. Types of Failures
      1. Short Duration Transient Failures
      2. Long Duration Transient Failures
      3. Failures Causing Data Loss
    2. Disaster Recovery Fundamentals
      1. Functionality of Adaptive Server Enterprise Transaction Log
      2. Secondary Truncation Point Issues
      3. Backup and Recovery
        1. Creating a Coordinated Dump
        2. Up-to-the-Minute Recovery
      4. Sybase HA Failover
        1. Configuring Replication Server for HA Failover
      5. Save Interval and Recovery
      6. The Origin Queue ID and the Generation ID
        1. Duplicate Detection
          1. Incrementing Generation ID
      7. rs_subcmp to Reconcile Data and Schema
    3. Recovering the Primary Database
      1. Recovery with Coordinated Dumps
      2. Loading Primary Database from Dumps
      3. Recovering from Truncated Primary Database Transaction Log
    4. Recovering the RSSD Database
      1. Recovering the RSSD Database with Up-to-the-Minute Backup
      2. Basic Recovery of an RSSD Database
      3. Subscription Comparison Procedure for Failed RSSD Database
        1. Sample rs_subcmp Select Statements
      4. Subscription Recreation for a Failed RSSD
      5. Deintegration/Reintegration Procedure
    5. Removing Replication Server from the Replication System
    6. Recovering from a Damaged Partition
      1. RSSD System Tables that Store Partition Information
      2. Recovering from a Failed or Lost Partition
      3. Message Recovery from the Online Database Transaction Log
      4. Message Recovery from the Offline Database Transaction Log
    7. Summary Plan for Recovery
  19. Chapter 16 Replicating Oracle Data
    1. Sybase Replication System for Non-Sybase Data Servers
      1. Replication Agent
        1. Types of Replication Agents
      2. Sybase Enterprise Connect Data Access
        1. Installation Steps
    2. Replication Agent Installation
    3. Configuring DirectConnect for Oracle
      1. Creating Maintenance User in Oracle
      2. Creating DDL User in Oracle
      3. Creating Objects and Public Synonyms in Oracle SID
        1. Checking Current Archiving Settings of Redo Logs
        2. Disabling Automatic Archiving
        3. Forced Logging of All Database Changes
        4. Permissions
    4. Configuring Replication Server
      1. Configuring a Replication Agent Instance
      2. Creating a Replication Agent Instance
      3. Starting a Replication Agent Instance
      4. Stopping a Replication Agent Instance
      5. Quiescing a Replication Agent Instance
      6. Initializing a Replication Agent Instance
      7. Suspending a Replication Agent Instance
      8. Creating Administrator User Login
    5. Configuring Replication Agent Connection Configuration Parameters
      1. Configuring Connection Parameters for Oracle SID (Primary Data Server)
      2. New Replication Agent Configuration Parameters for Oracle RAC in Replication Agent 15.1
      3. Configuring Connection Parameters for the Replication Server
      4. Configuring Replication Agent Instance Connection Parameters for the RSSD (or ERSSD)
    6. Replication Agent Transaction Log Management
      1. Removing the Replication Agent Transaction Log
      2. Truncating the Replication Agent Transaction Log
        1. Automatic Truncation
        2. Manual Truncation
    7. Marking Objects for Replication in the Primary Database
      1. Enabling and Disabling Replication for User Objects
      2. Enabling and Disabling DDL Replication
      3. Enabling and Disabling Stored Procedure Replication
      4. Enabling and Disabling Sequence Replication
    8. Oracle Database Specific Issues
      1. Character Case of Database Object Names
      2. Datatype Compatibility Issues
        1. Oracle Large Object (LOB) Datatype Support
        2. Oracle User-Defined Datatypes
    9. Configuring and Tuning Replication Agent for Oracle
      1. Parameters for Tuning Replication Agent Throughput
      2. Parameters for Tuning Replication Agent Latency
      3. Tuning the Size of the Replication Agent Instance System Log
    10. Troubleshooting Replication Agent
    11. Troubleshooting Replication Agent System Database (RASD)
      1. Backing up RASD
      2. Updating the Log Device Repository in RASD
      3. Recovering from Corrupted RASD
      4. Restoring RASD from Backup
    12. Typical Topology of Heterogeneous Replication
      1. One-way Replication from Oracle to Sybase Adaptive Server Enterprise
      2. Replicating from Sybase Adaptive Server Enterprise to Oracle
      3. Replicating from Non-Sybase Primary to Non-Sybase Replicate Database
      4. Bidirectional Replication with Non-Sybase Primary and Non-Sybase Replicate Databases
  20. Chapter 17 Security
    1. Login Names and Password Management
      1. Primary User and Maintenance User
        1. Setup and Function
        2. Password Management
      2. RepAgent User
        1. Setup and Function
        2. Password Management
      3. ID Server Login and Password
      4. Replication Server Interface (RSI) Login and Password
      5. Adaptive Server Maintenance User Login and Password
    2. Client Connections and Encrypted Passwords
    3. Encrypting Existing Passwords
    4. Disabling Password Encryption
    5. RSSD System Tables and Password Encryption
    6. Network-based Security
      1. Setting Up Network-based Security
        1. Changing the Configuration Parameters
        2. Identifying the Replication Server Principal User
        3. Activating Network-based Security
  21. Chapter 18 Internationalization
    1. Message Language
    2. Character Set
    3. Unicode
      1. UTF-8
      2. UTF-16
      3. UTF-32
    4. Sort Order
      1. Unicode Sort Order
    5. Changing the Character Set and Sort Order
  22. Chapter 19 ERSSD
    1. Installing ERSSD Using rs_init
    2. Special Features of ERSSD and Replication Server 15.0 Enhancements
    3. ERSSD Files
    4. ERSSD Maintenance
      1. ERSSD Backup
      2. Reconfiguring the ERSSD Backup Settings
      3. Relocating the ERSSD Backup Files
    5. ERSSD Users
    6. ERSSD and Routes
    7. ERSSD Recovery
      1. ERSSD Log Recovery
      2. ERSSD Data Recovery
  23. Chapter 20 Troubleshooting
    1. Replication Agent
      1. Invalid Login
      2. Missing Replication Server Name in Interfaces
      3. Permission Issue
      4. Misconfigured Replication Agent
      5. Invalid Truncation Page
      6. Syslogs Corruption
      7. Primary Database Transaction Log Full
      8. Standby Database Transaction Log Full
      9. Error 9209
      10. Replication Status Inconsistency of text, unitext, or image Columns
      11. Error 2033
      12. Error 9202
      13. Errors 9204 and 9254
      14. Error 9205
      15. Error 9240
      16. Error 9255
      17. Error 9261
      18. Errors 9278 and 9279
      19. Error 9286
    2. DSI Issues
      1. States of DSI Scheduler Thread
      2. States of DSI Executor Thread
      3. Creating an Error Class for Duplicate Key
      4. Turning On DSI Trace Flags
      5. Turning Off DSI Trace Flags
      6. Connectivity Issues
      7. Incorrect Login/Password
      8. Permission Issues
      9. SUID Mismatch
      10. Connection Failures with Asynchronous Transaction and DDL Replication
    3. Data Server Errors
      1. Unique Key Violation
      2. Datatype Issue
      3. text/image Column Replication Status and the Null Property
      4. Missing Column in Replication Definition
      5. Corruption Errors
    4. Replication Server Errors
      1. WARNING #32020
    5. Stable Queues
      1. Queues Are Full, They Are Not Getting Truncated
    6. Open Transaction in the Inbound Queue
      1. The Origin Queue ID
    7. Data Not Being Replicated
      1. Duplicates Being Ignored
      2. Error 6025
      3. Corruption in the Queue
    8. Data Latency
  24. Appendix A Origin Queue ID
  25. Appendix B Removing the Active Replication Server from the Replication System
  26. Appendix C Useful Queries
  27. Index