E192 What’s New in Replication Server 12.5

E192 What’s New in Replication Server 12.5 • Naveen Puttagunta • Product Manager • Enterprise Solutions Division • Naveen.Puttagunta@sybase.com

Agenda • Why Replicate? • Replication & Alternatives • Architecture - Introduction • Replication Server – 12.5 • Future Direction

Why Replicate? • High Availability of data • Planned, Unplanned downtime and Disaster Recovery • Data Distribution • Decentralizations • Consolidations • Load balancing • Live decision support • Separation of OLTP and DSS

Minimize/eliminate user impact Protect against unplanned outages S/W, H/W, Application failure Unforeseen circumstances like data corruption Protect against planned outages S/W, H/W, Application upgrades Enable ops to perform maintenance Recover from natural disaster Without geographic restrictions Replication and High Availability Philadelphia Operations ASE Replication Server PRIMARY DATACENTER Denver Operations ASE Replication Server SECONDARY DATACENTER Warm Standby

Special Configuration: One Active, One Standby Database managed by One Rep Server May be configured to replicate the entire database – Complete Mirror! (publish-subscribe mechanism not required) DDL/Schema Change replication! Direction of Replication can be switched Performs failover and failback Ease of Administration, Low latency Active-Standby pair viewed as a single logical DB at other replicate sites Replication System Architecture: Warm Standby Logical DB Active DB Standby DB RS Add’l routes/ connections

Rep Server Rep Server Rep Server Rep Server Rep Server ASE ASE ASE ASE ASE Replication and Data Distribution • Continuous Secure Replication of changed data • Guaranteed Delivery For • Decentralization • Consolidation • Load Balancing Dealers CRM-sales, customer Financial Information Dealers New York Consolidation

Replication and Live Decision Support • Separate Operational data from Decision Support • Run intensive queries without effecting the performance of transaction processing system • Real-time alternative to Data Warehousing DSS OLTP Rep Server ASE Rep Server ASE

Replication and… • Portals • Setup external facing Operational Data Stores • Insulate internal business processes from external parameters (internet/intranet) • Application Integration • Connect various packaged applications (CRM, ERP) • Event-enable the DB: notification for DB changes • Mobile Users: occasionally connected users

High Availability Alternatives • Cluster Systems • ASE HA feature supports a 2 node cluster system • Companion node takes over processing and disks during failure • Cluster Architecture and Connectivity layer can switch applications (clients) • Limited geography – limited to data center – Does not protect against Disasters • Single copy of data – can not manage planned down time • Provides Zero-latency, quick fail-over

High Availability Alternatives • Disk/Volume Replication • Synchronous or Asynchronous movement of data at the disk track/sector/bit level • Break mirror and bring secondary site online when primary fails • Database integrity is not guaranteed • Database/Disk corruptions are also duplicated • Low hardware utilization at secondary during routine operation • Fail-over takes time – secondary site must be brought online • Addresses Disaster Recovery scenario only

Replication Server Advantages • Transaction Replication Advantages • Database Integrity is guaranteed since the granularity of replication is transactions; protects against corruptions • Redundant hardware can be leveraged for DSS or load balancing since the secondary site is fully usable • Only solution that addresses all three areas of HA • Fail-back procedures are well supported • Often times the solutions are complementary. Customers use multiple solutions in the same environment

Primary Sites Replicate Targets Replication Agent Replication Server • ASE • ASA • ASIQ • Oracle • Microsoft • DB2/UDB • Informix • AS/400 • ODBC, DRDA • Sources • Adaptive Server/Enterprise • Adaptive Server/Anywhere • Oracle • Microsoft SQL Server • DB2 UDB (Unix, NT, OS/390) • Informix • Replication Toolkit for OS/390 (IMS, VSAM) Replication Server Architecture

Standby DB Primary DB Connection Connection Replicate DB1 RS2 RS1 Add’l routes Replicate DB2 Replication System Configuration • System Configuration • Repservers manage connections to databases • Repservers exchange data via routes

Using the Replication System Once the Replication System topology is configured, the DBA(s) will • Publish the data at the primary site • Specify which primary data is available for replication • Mark the entire Database as replicated for Warm Standby – No other explicit publications necessary • Subscribe to the published data at replicate site(s) • Specify what primary data gets replicated where • In case of Warm Standby, the Standby site gets the entire database – no other explicit subscriptions necessary

Using the Replication SystemPublishing the Data • Specify which tables get published • Mark individual tables or In case of Warm Standby, mark entire database • Limit the columns published • e.g., Central office does not publish customer Social Security numbers to branch offices • Limit the data published with a where clause • e.g., Only publish online accounts to the web accounts database (where actype = ‘web’) • Group related data together: group tables, stored procedures together into a big publication

Using the Replication SystemSubscribing to the Data • Subscribe at the replicate sites • Specify which publication to subscribe to • Limit the data subscribed to with a where clause • e.g., Only subscribe to large accounts for the Major Accounts Office (balance > $1M) • Subscribe to grouped data with one subscription • Example create replication definitionAllAccounts (id int, name char(30) , address varchar(255), balance money) withprimaryat NY_DB… create subscription MajorSub for Accounts withreplicateat SF_DB where balance > $1M

WebAccounts AllAccounts PrimaryDB Primary Table Accounts Id Name Address Balance SSNo actype = ‘web’ replication definition Id Name Address Balance Id Name Address Balance publication balance > $1M Replication Layer subscription: allsub subscription: majorsub (where balance > $1M) WebDB MajorAccountsDB Using the Replication SystemExample

Requirements for e-Business Data Movement • Increasing demand for assurance of privacy • Solution: Secure, guaranteed replication for data distribution • Flexibility in moving data around the enterprise • Solution: Support replication of e-business data across heterogeneous databases • 24/7 system availability • Solution: High Performance Data Replication through Replication Server Warm Standby

Replication Server 12.5: New Features • Performance • SMP Support • Configurable Transaction Partitioning for Parallel DSI • Commit Sequencing Enhancements for Parallel DSI • Security: Advanced Security Option – SSL Support • Flexibility • Unicode Support • LDAP Support • Extended Limits Data Support • Multi-Vendor Replication

Replication Server 12.5Performance Enhancements • SMP Support – Will be released in an EBF in Sep.02 • Configurable Transaction Partitioning for Parallel DSI • Commit Sequencing Enhancements for Parallel DSI – Will be released in an EBF in Sep.02

Performance EnhancementsSMP Support • Takes advantage of SMP architecture and improves scalability • Parallelizing internal Replication Server processing decreases latency and increases throughput • Scales well for replication of multiple sources and targets within the same server

Performance Enhancements SMP Support • Replication Server will utilize multiple processors on the machine • Built on SMP Open Server • Based on a threading model rather than an engine model • Uses OS Native threads in pre-emptive mode • Server spawns numerous cooperating threads that run concurrently • Number of CPUs used by the server can be controlled by binding the CPUs to the server using OS-specific mechanisms • e.g., psrset/pbind on Solaris • Server can be forced into a single processor mode using a switch • configure replication server set smp_enable to {on | off}

Performance EnhancementsConfigurable Transaction Partitioning Background • Parallel DSI threads in RS attempt to apply transactions in the queue to the replicate DB in parallel • Each DSI thread opens a connection to the replicate DB • Commit order is still maintained – only the “body” of the transactions are applied in parallel • Transactions can also be grouped together to apply “more” in each unit of work – grouping rules decide which transactions can be grouped • Not all transaction “bodies” can be applied in parallel • Some of them may deal with the same tables/rows • Resulting in deadlocks and rollbacks, reducing throughput • Transaction grouping increases the chance of contention

Performance EnhancementsConfigurable Transaction Partitioning • Traditional Transaction Partitioning • The transactions (transaction groups) are partitioned across DSI threads in a round robin fashion • Possibility of deadlocks or contention if transactions (groups) assigned to different threads work on the same tables or rows • Both transactions are rolled back and applied serially if a deadlock is detected T1 T1 T2 T2 T3 T3 Queue

T1 T1, T2 T4, T5 T2 T3 T3 Queue Tran Partitioning Rules Engine Performance EnhancementsConfigurable Transaction Partitioning • Configurable Transaction Partitioning • The transactions are grouped and partitioned across DSI threads intelligently based on transaction attributes (configurable) • The goal is to reduce contention by recognizing which transactions can be applied in parallel and which transactions can be grouped • Transaction commit time, Transaction name and User name are used to determine how transactions can be partitioned

Performance EnhancementsConfigurable Transaction Partitioning • Username Based Transaction Partitioning • Transactions owned by the same user are forced to execute serially (possibly in the same transaction group) since they were probably executed one after the other at the primary by the same user • Transaction Name Based Transaction Partitioning • Transactions with the same name are forced to execute serially (possibly in the same transaction group) since they probably represent operations on related data and might lead to contention if attempted in parallel • Commit Time based Transaction Partitioning • Transactions that have overlapping execution times are assigned to (different groups and) parallel threads since they were applied concurrently at the primary – less chance for contention

Performance EnhancementsConfigurable Transaction Partitioning • Replication Server attempts to mimic users at primary DB • Brings replicate throughput closer to primary DB throughput • Reduces contention among multiple RS threads (DSI) applying transactions at the replicate DB • Optimal resource utilization by minimizing deadlocks and rollbacks • Parallel DSI Processing is made more efficient by reducing the contention and deadlocks in the replicate DB

Performance EnhancementsCommit Sequencing Enhancements Background • Parallel DSI threads in RS attempt to apply transactions in the queue to the replicate DB in parallel • Commit order is still maintained – only the “body” of the transactions are applied in parallel • Replication Server relies on a table in the replicate DB (rs_threads) to detect deadlocks and help sequence commits • Additional overhead on the replicate DB • Additional network roundtrips (between RS and replicate DB) for each transaction (group) • Limits the usability of Parallel DSI to ASE environments

Performance EnhancementsCommit Sequencing Enhancements Internal Commit Sequencing • Deadlock detection for transactions distributed across Parallel DSI threads is now done internally in Replication Server • Commit sequencing is now completely internal to RS • Eliminates a network roundtrip for each transaction – reduced I/O • Less overhead for each transaction (group) – faster commit processing • Removes the reliance on replicate DB • Eliminates a bottleneck (rs_threads) in the replicate DB • Allows non-ASE environments to take advantage of the Parallel DSI feature

Security - SSL Support Background • SSL – Secure Socket Layer – is an industry standard for sending socket-level encrypted data over network connections • Different Encryption algorithms can be used for data security • Data integrity is guaranteed through Digital Signatures for tamper protection and non-repudiation • Mutual authentication of servers and clients through Digital Certificates • Typically for communications over a wide area network • The computing overhead of encryption limits its use in LAN environments behind the firewall

DSI Route RS SSL SSL SSL RepAgent Security - Advanced Security Option – SSL • Replication Server provides advanced security for data communications through SSL Support • All data exchanges with other servers (ASE or RS) can use SSL for data encryption • SSL can be used selectively on specific connections or routes • SSL Support is a separately license-able option

Security - SSL Support • Replication Server can act as a client and a server for SSL communication • Acts as a client when connecting to ASE (DSI connections) or to other Replication Servers (routes) • Acts as a server when accepting connections from Replication Agent or from other Replication Servers (routes) • A configuration parameter determines whether Replication Server can accept SSL connections • The parameter determines its behavior as a server, not as a client • configure replication server set use_ssl to {on | off}

WAN/Internet SSL SSL NJRS NYRS London RS Route Interface file with SSL Entry for NYRS Interface file with plain Entry for NYRS Security - SSL Support • The interfaces file entry indicates to the client that the server requires SSL connections • If the target (ASE or RS) requires SSL communication (through the interface entries), the client automatically uses SSL to connect • Different clients can use or not use SSL to connect to the same server using different interface files • SSL incurs considerable overhead at connection setup

Security - SSL Support Prevent interception of sensitive information • SSL Encryption protects from both accidental disclosure as well as wire taps • PKI-certificates authenticate servers to clients • Fully embedded in product, reducing complexity toclient applications • Complete end-to-end security services support for ASE replication environment

German operations Japan operations No data conversion, no data loss New York Consolidation Flexibility - Unicode Support • Replication Server 12.5 supports utf8 character set as a default character set for the server • Greatly benefits global environments with multiple languages and character sets • By running all Replication Servers in the environment with utf8 character set, you minimize data conversions and thereby, minimize data loss

Flexibility - Unicode Support • Supports replication of Unicode data (UCS-2) • Supports replication of two new ASE 12.5 data types: unichar, univarchar • Unichar is a fixed-width non-nullable data type • Univarchar is a variable width, nullable data type • Both use UTF-16, a fixed two-byte encoding of Unicode • Support includes: • Replication to Standby databases • Replication to Replicate databases • Data types can be included in replication definitions • As searchable columns (subscriptions can use them in where clauses)

Flexibility - Unicode Support • Replication of Unicode data • Different sort order used for Unicode data (unichar, univarchar columns), defined by the new RS_unicode_sortorder parameter in the configuration file (default: binary) • RS_subcmp supports the unicode data types • Mixed Version Support • Replication Definition that includes unicode data types is not propagated to pre-12.5 servers (dropped from pre-12.5 servers if it already exists) • Replication Definition that is subscribed by pre-12.5 servers can not be altered to include unicode data types

LDAP Directory RS ASE ASE RS RS ASE Flexibility - LDAP Support • Allows Replication Server to retrieve Server connection information from an LDAP server • Ease of administration in distributed, enterprise-wide environments with heterogeneous platforms • A standard, central wayof managing directory information canbe supported inreplicationenvironments

Flexibility - Extended Limits Data Support • Complete support for replication of extended limits data – wide columns, wide tables, wide stored procedures etc. • Column widths up to 32K bytes • Tables up to 1024 columns • Stored Procedures up to 1024 parameters • Internal limitation of replication message length of 16K bytes eliminated • Support for larger page sizes in ASE (2K, 4K, 8K or 16K)

Flexibility - Extended Limits Data Support • Replication Definition can include • Wider columns (up to 32K bytes) and up to 1024 cols • Can be marked as primary columns or searchable cols • No limit (up to 1024) on the number of columns in primary key or searchable list • Only propagated to 12.5 sites if new limits are used • Before 12.5, Replication Message length (length of each message passed between ASE-RS and RS-RS) had to be below 16K bytes (fit within a Stable Queue block) • Starting with 12.5, replication messages can span multiple blocks • Replication of wider column data is likely to result in wider replication messages

Flexibility - Extended Limits Data Support Mixed Version Support • ASE Replication Agent will detect if the Replication Server can handle wide data • If not, Replication Agent will truncate or skip the data (as configured) before sending it over to Replication Server • “Route Version” will determine if wide data can be handled by Replication Servers at either end of the route • Replication Server will skip over any messages that are larger than the 16K limit before sending data over to other Replication Servers that can’t handle wide data • Replication Definitions that contain wide columns are not propagated to sites that can’t handle wide data, hence replication to older sites is limited to older limits

Flexibility - Extended Limits Data Support Migration • Replication Server can be used in conjunction with the ASE Migration Tool to migrate between databases of different page sizes over a period of time • Migration Tool can be used to setup the secondary copy, while Replication Server can keep the copies in synch, such that migration occurs over a period of time and applications can be fully tested • The same technique (with dump/load) can be used to migrate from one server version to another (or a combination of version and page size change) with almost no downtime

Primary DB (Oracle) Rep Agent for Oracle RS Add’l routes/ connections Replicate DB (MS SQL) DirectConnect for MS SQL Flexibility - Multi-Vendor Replication • Replication Agents pull data out of non-Sybase sources • DirectConnects are used as gateways to replicate into non-Sybase targets • Support for vendor-specific datatypes, translations etc. • Some Replication Agents read the transaction log (DB2), some build a virtual log with triggers (Oracle, MS SQL)

Flexibility - Heterogeneous Replication • Extended Limits Data • Wide data (columns, tables and stored procedures) can be replicated between multi-vendor databases – ASE, Oracle, DB2 etc. • Replication Agent, DirectConnect supports replication of wide data • LOB (Text/Image) Data • Replication Agent now supports LOB replication out of DB2 UDB • LOB support by Direct Connect for Oracle allows end-to-end replication of text/image data for Oracle environments

Flexibility - Heterogeneous Replication • Unicode • Unicode data can be replicated between multi-vendor databases • Replication Agent and Direct Connect for Oracle support replication of unicode data and UTF-8 character set • Platform Support • Oracle 8.1.7 and 9i (Replication Agent, Direct Connect) • IBM DB2 UDB 6.1, 7.1 and 7.2 (RA, DC) • SQL Server 7.0 and 2000 (aka 8.0) (RA, DC): Replication Agent is now available on Unix and Windows 2000 • Informix 7.3 and 9.2 (RA, DC)

Replication Server Packaging Replication Server Base Server License Replication Server Manager Sybase Central Plug-in for Replication Server Replication Server Base Package Replication Server - Advanced Security Option Option for Oracle Option for Informix Option for Microsoft Option for DB2/UDB RA RA RA RA Replication Server Options Package DC DC DC DC ASE ASE ASE ASE

Summary • The e-Business data management platform • Sybase fundamentals: open, flexible, secure,high-performance, high availability • New enhancements to support evolution to e-Business • e-Business Security • Protecting valuable information • High Availability • Ensures data is available when needed • Dynamic Performance • Ensuring your mission-critical information is available where needed

E192 What’s New in Replication Server 12.5