Sybase Replication Server vs Oracle Golden Gate...

Sybase RepServer

Sybase RepServer was developed by Sybase in the beginning of the 90's to cater the data synchronization, high availability and data warehousing without affecting the Primary databases performance. 

RepServer replicates committed database transactions. In ASE, a built-in replication agent continuously monitors the database transaction log for newly committed transactions that have been marked for replication. When it finds one, the replication agent sends it to the RepServer Queue that stores the transaction and runs somewhere outside ASE. RepServer will process and then forward the transaction to the designated replicate database and apply it.

Sybase RepServer can replicate data among all leading database products like Sybase, Oracle, MS SQL, DB2, ASA , ASIQ etc. One of RepServer's design strengths is that the replication agent sends the transaction to RepServer in a database-independent internal format. When a replicated transaction reaches the replicate database, the internal format is converted to the SQL dialect of that specific  database SQL and the transaction is applied. This way, you can replicate from, say, a primary DB2 database to a replicate Oracle database as well as to a replicate Microsoft SQL Server database -- and vice versa, since RepServer supports biderectional replication.

Because RepServer captures only data changes, there is no impact on the Primary Server/Databases, Applications already running on that Server. This means that RepServer can be used in pretty much any database system. Some common uses of RepServer are to facilitate migrations of databases or applications that require significant downtime; by setting up a replica with Repserver, this downtime can be almost fully eliminated. Because of its application transparency, Replication may also be seen as a mechanism to integrate different applications, by synchronising the data changes.

Sybase RS Architecture:

Replicated transactions flow through the system as follows:

1. Replication Agent forwards logged changes scanned from the transaction log to the Replication Server.

2. Replication Agent User thread functions as a connection manager for the Replication Agent and passes the changes to the SQM. Additionally, it filters and normalizes the replicated transactions according to the replication definitions.

3. The Stable Queue Manager (SQM) writes the logged changes to disk via the operating systems asynchronous I/O routines. The SQM notifies that Asynchronous I/O daemon (dAIO) that it has scheduled an I/O. The dAIO polls the O/S for completion and notifies the SQM that the I/O completed. Once written to disk, the Replication Agent can safely move the secondary truncation point forward (based on scan_batch_size setting).

4. Transactions from source systems are stored in the inbound queue until a copy has been distributed to all subscribers (outbound queue).

5. The Stable Queue Transaction (SQT) thread requests the next disk block using SQM logic (SQMR) and sorts the transactions into commit order using the 4 lists Open, Closed, Read, and Truncate. Again, the read request is done via async i/o by the SQT’s SQM read logic and the SQT notified by the dAIO when the read has completed.

6. Once the commit record for a transaction has been seen, the transaction is put in the closed list and the SQT alerts the Distributor thread that a transaction is available. The Distributor reads the transaction and determines who is subscribing to it, whether subscription migration is necessary, etc.

7. Once all of the subscribers have been identified, the Distributor thread forwards the transaction to the SQM for the outbound queue for the destination connections. This point in the process serves as the boundary between the inbound connection process and the outbound connection processing.

8. Similar to the inbound queue, the SQM writes to the queue using the async i/o interface and continues working. The dAIO will notify the SQM when the write has completed.

9. Transactions are stored in the outbound queue until delivered to the destination.

10. The DSI Scheduler uses the SQM library functions (SQMR) to retrieve transactions from the outbound queue, then uses SQT library functions to sort them into commit order (in case of multiple source systems) and determines delivery strategy (batching, grouping, parallelism, etc.)

11. Once the delivery strategy is determined, the DSI Scheduler then passes the transaction to a DSI Executor.

12. The DSI Executor translates the replicated transaction functions into the destination command language (i.e. Transact SQL) and applies the transaction to the replicated database.

Oracle Golden Gate

In 2009, Oracle acquired Golden Gate to strengthen its data replication technology as part of future strategies within data warehouse and real time transaction database. Before this acquisition, Oracle had its own replication technology which mainly constitutes basic replication, advance replication and stream replication.

Oracle's basic replication process had two different methods: log based and trigger based. In log based replication, snapshot schema and DB links had to set up between source and target database. Data was transported from online redo log to target database over network. In advance replication Oracle included multiple master replications from multiple environments and trigger based replication method.

In later versions of Oracle 9i, stream replication was introduced with improvements in its earlier replication technologies. It uses log based replication where committed transaction’s online redo log of source system is mined to target system over network. Also a new background process to manage communication and operations of replication activities has been introduced

However these replication technologies couldn't stand long as it involved complicacy and complex software development to harness the power of Oracle to Non Oracle database environment to allow the transaction to be moved between environment.

In 1990, a small software company Golden Gate came with different approach on data replication between cross platform. Instead of using different formats, Golden Gate implemented a uniform format and used a command prompt GGSCI (Golden Gate Software Command Interface) to perform data replication operation. Oracle Goldengate 11g supports both DDL and DML operation. Legacy Oracle 8i version or prior requires an upgrade to 9i or later to implement the Goldengate.

Goldengate supports all popular databases such as Oracle, MySQL, DB2, Sybase, Teradata, SQL server etc.

Goldengate contains three installable components which can be downloaded separately from Oracle's website and can be installed on both, source and target system depending on requirement. Below is the list of installable components for Goldengate:

• Oracle Goldengate which includes GGSCI, DEFGEN, LOGDUMP and REVERSE.
• Oracle Goldengate Director which includes Director Service application, Monitor Agent, Director Client and Administrator client.
• Oracle Goldengate Veridata which includes Agent, CLI, Web interface.

GG Architecture

Manager Process

• Start and manage Goldengate processes such as collector.
• Manage port numbers.
• Trail Management.
• Createerror, event and reports.
• Process command GGSCI.

1. Extract Process – Extract DML and DDL data from source database.
2. Data Pump Process: Option process but recommended. Read records from source trails, process them and passes over to target system.
3. Collector Process: This background process runs on Target system. It writes records on remote trail.
4. Replicate Process: ReadsRemote trail, process them and applies changes to target Database.
5. Trail: It is a Goldengate intermittent file written in proprietary format by either extract process (source system) or collector background process (target system).

 High Level Pros and Cons

There are many similarities in the both Replication products and uses, just mentioning 1 top item.

Ø  Sybase RepServer -> Very easy to skip a transaction and move to the next tran. In GG we need to find the current RBA and move to the next RBA to skip the current transaction which causes the replication down.

Ø  GoldenGate -> Very easy to discard all the Queues/Trans and start replication from now using begin now, but in Sybase RepServer we need to ignore LTM, reset rs_zeroltm and purge Queues etc.

Finally, All the commands can be executed in ggsci of GG i.e. to reset replication, reconfigure replication and clear the Queues etc., but in Sybase we should be clear on the commands as RepAgent in Primary Database, Replication Commands in RepServer and replication procedures in RSSD, which is little complex.

Happy replicating…

To get Rowcounts of Sybase ASE and ASA databases..

You can use the following Queries to get rowcounts for all the tables in Sybase.

for ASE:

This is a generic T-SQL script that would select all the tables and then does a row count of all tables in a Sybase ASE Database.

versions < 15.x

select o.name, rowcnt (i.doampg)
from sysobjects o, sysindexes i
where o.type = 'U'
and i.id = o.id
and i.indid < 2 

versions 15.x

select name, rowcnt(sysindexes.doampg)
from sysindexes
where name in
(select name from sysobjects where type = "U")
order by name

This is a generic T-SQL script that would select all the tables and then does a row count of all tables in a Sybase ASA Database.

for ASA:

select table_name, count

from systable

where primary_root<>0 and creator=1

order by 1