Sybase Adaptive Server vs Microsoft SQL Server High Availability and Disaster Recovery Solutions.

I have been Administering, Maintaining and Mentoring both Sybase Adaptive Server and Microsoft SQL Server for the past decade with Sybase is my primary skillset but slowly transforming into MS SQL Server as Primary due to the requirements in the Projects.

It is very difficult to make the comparison between SQL Server and Sybase ASE to identify which is the better product as both are siblings.

In a Quick nutshell:

•        1984 - Sybase founded and creates first RDBMS designed for online transactions.

•        1990 - Sybase entered into a technology sharing agreement with Microsoft which resulted in Microsoft marketing its own SQL Server. Microsoft releases SQL Server 1.1 for OS/2 with the engine of Sybase SQL Server.

•        1993 – SQL Server 4.2 for NT was released with Microsoft taking more control.

•        1994 – Sybase and Microsoft split ways and launched their own products but with same architecture and features.

•        1995 – SQL Server 6.0 is released; the first version without Sybase Support.

Over the past 2 decades, Microsoft has facilitated multiple features, tools and utilities for the ease of access and maintenance and administering compared to Sybase which provided Sybase central only. From a DBA standpoint MS SQL Server is easier to configure and maintain with its GUI tools but with sound scripting knowledge and right configuration Sybase is still robust. The performance of your databases depends rather from the experience of the database developers and database administrator than from the Product. You can use both of these RDBMS to build stable and efficient system. 

Microsoft SQL Server and Sybase ASE have a common architecture because, until version 4.2, Microsoft simply licensed Sybase's database server software. So these products had one kernel. After version 4.2, these products have diverged. Microsoft has produced versions 6.0, 6.5, 7.0, 2000, 2005, 2008 and 2012 of Microsoft SQL Server, while Sybase has produced versions 4.8, 4.9, System 10 and System 11 of Sybase SQL Server and versions 11.5, 11.9, 12, 12.5, 15 and 15.7 of Sybase Adaptive Server Enterprise.

High Availability and Disaster Recovery Concepts. 

When we compare the HA nd DR features till the year 2010 i.e. SQL Server 2008 and Sybase ASE 15, I found setup and configuration of Replication is easier in SQL Server due to its GUI based tools, but Sybase has clearly an edge with the features and availabiity.

•        Major advantage of Sybase replication is the Secondary replica can be used for the Reporting purpose for all kinds of Replication.

•        Fine tuning the Replication process and Trouble shooting is easier.

•        Database level Replication to multiple replicas can be setup easily.

You can make the best selection of a database technology for a high availability and disaster recovery solution when all stakeholders have a shared understanding of the related business drivers, challenges, and objectives of planning, managing, and measuring RTO and RPO objectives. But, recent times and especially, I was pleased to see the path-breaking new feature provided in SQL Server 2012 which combines all the pros and features of HA and DR called AlwaysOn Availability Groups. It's really an edge over all the available HA/DR options. As per the requirements in the recent Projects, I have set up multiple times the AlwaysOn Availability Groups and could see tangible improvements in the Databases Availability, Performance and ease of Maintenance. 

I have not considered SAP ASE 16 or MS SQL 2014.

For a given software application or service, high availability is ultimately measured in terms of the end user’s experience and expectations. The principal goal of a high availability solution is to minimize or mitigate the impact of downtime. A sound strategy for this optimally balances business processes and Service Level Agreements (SLAs) with technical capabilities and infrastructure costs.

The resulting value is often expressed by industry in terms of the number of 9’s that the solution provides; meant to convey an annual number of minutes of possible uptime, or conversely, minutes of downtime.

Number of 9’s	Availability Percentage	Total Annual Downtime
2	99%	3 days, 15 hours
3	99.9%	8 hours, 45 minutes
4	99.99%	52 minutes, 34 seconds
5	99.999%	5 minutes, 15 seconds

Data redundancy is a key component of a high availability database solution. Transactional activity on your primary SQL Server instance is synchronously or asynchronously applied to one or more secondary instances. When an outage occurs, transactions that were in flight may be rolled back, or they may be lost on the secondary instances due to delays in data propagation.

•        Avoiding downtime. Outage recovery costs are avoided all together if an outage doesn’t occur in the first place. Investments include the cost of fault-tolerant and redundant hardware or infrastructure, distributing workloads across isolated points of failure, and planned downtime for preventive maintenance.

•        Automating recovery. If a system failure occurs, you can greatly mitigate the impact of downtime on the customer experience through automatic and transparent recovery.

•        Resource utilization. Secondary or standby infrastructure can sit idle, awaiting an outage. It also can be leveraged for read-only workloads, or to improve overall system performance by distributing workloads across all available hardware.

SQL Server AlwaysOn

AlwaysOn is a new integrated, flexible, cost-efficient high availability and disaster recovery solution. It can provide data and hardware redundancy within and across data centers, and improves application failover time to increase the availability of your mission-critical applications. AlwaysOn provides flexibility in configuration and enables reuse of existing hardware investments.

An AlwaysOn solution can leverage two major SQL Server 2012 features for configuring availability at both the database and the instance level:

•        AlwaysOn Availability Groups, new in SQL Server 2012, greatly enhance the capabilities of database mirroring and helps ensure availability of application databases, and they enable zero data loss through log-based data movement for data protection without shared disks.

Availability groups provide an integrated set of options including automatic and manual failover of a logical group of databases, support for up to four secondary replicas, fast application failover, and automatic page repair.

•        AlwaysOn Failover Cluster Instances (FCIs) enhance the SQL Server failover clustering feature and support multisite clustering across subnets, which enables cross-data-center failover of SQL Server instances. Faster and more predictable instance failover is another key benefit that enables faster application recovery.

Significantly Reduce Planned Downtime

The key reason for application downtime in any organization is planned downtime caused by operating system patching, hardware maintenance, and so on. This can constitute almost 80 percent of the outages in an IT environment.

SQL Server 2012 helps reduce planned downtime significantly by reducing patching requirements and enabling more online maintenance operations:

•        Windows Server Core. SQL Server 2012 supports deployments on Windows Server Core, a minimal, streamlined deployment option for Windows Server 2008 and Windows Server 2008 R2. This operating system configuration can reduce planned downtime by minimizing operating system patching requirements by as much as 60 percent.

•        Online Operations. Enhanced support for online operations like LOB re-indexing and adding columns with default values helps to reduce downtime during database maintenance operations.

•        Rolling Upgrade and Patching. AlwaysOn features facilitate rolling upgrades and patching of instances, which helps significantly to reduce application downtime.

•        SQL Server on Hyper-V. SQL Server instances hosted in the Hyper-V environment receive the additional benefit of Live Migration, which enables you to migrate virtual machines between hosts with zero downtime. Administrators can perform maintenance operations on the host without impacting applications.

Few more significant merits not found in previous versions:

•        Using the WSFC APIs to perform failovers. Shared storage is not required

•        Utilizing database mirroring for the data transfer over TCP/IP

•        providing a combination of Synchronous and Asynchronous mirroring

•        providing a logical grouping of similar databases via Availability Groups

•        Creating up to four readable secondary replicas (upto 2 Sync)

•        Allowing backups to be undertaken on a secondary replica

•        Performing DBCC statements against a secondary replica

•        Employing Built-in Compression & Encryption

•        Automatic Page Repair

Eliminate Idle Hardware and Improve Cost Efficiency and Performance

Typical high availability solutions involve deployment of costly, redundant, passive servers. AlwaysOn Availability Groups enable you to utilize secondary database replicas on otherwise passive or idle servers for read-only workloads such as SQL Server Reporting Services report queries or backup operations. The ability to simultaneously utilize both the primary and secondary database replicas helps improve performance of all workloads due to better resource balancing across your server hardware investments.

Easy Deployment and Management

Features such as the Configuration Wizard, support for the Windows PowerShell command-line interface, dashboards, dynamic management views (DMVs), policy-based management, and System Center integration help simplify deployment and management of availability groups.

Contrasting RPO and RTO Capabilities

The business goals for Recovery Point Objective (RPO) and Recovery Time Objective (RTO) should be key drivers in selecting a SQL Server technology for your high availability and disaster recovery solution.

This table offers a rough comparison of the type of results that those different solutions may achieve:

High Availability and Disaster Recovery SQL Server Solution	Potential Data Loss (RPO)	Potential Recovery Time (RTO)	Automatic Failover	Readable Secondaries(1)
AlwaysOn Availability Group - synchronous-commit	Zero	Seconds	Yes	0 - 2
AlwaysOn Availability Group - asynchronous-commit	Seconds	Minutes	No	0 - 4
AlwaysOn Failover Cluster Instance	NA	Seconds -to-minutes	Yes	NA
Database Mirroring(2) - High-safety (sync + witness)	Zero	Seconds	Yes	NA
Database Mirroring(2) - High-performance (async)	Seconds	Minutes	No	NA
Log Shipping	Minutes	Minutes -to-hours	No	Not during a restore
Backup, Copy, Restore(3)	Hours	Hours -to-days	No	Not during a restore
Sybase WarmStandBy	Seconds	Minutes	No	1
Sybase MSA	Seconds	Minutes	No	Multiple

SAP ASE 16 - Extreme Transaction Ready. (Source - sdn.sap.com)

SAP Adaptive Server Enterprise 16 has been designed to deliver the speed, scalability, security and simplicity that enterprises need to meet the challenges of extreme transactional workloads, efficiently and economically. SAP ASE 16 is now on-par with competitors with all the features and options like automatic backup-schedule, create/replace object command support, relaxed database recovery and many more  with less TOC.

A few exciting features of ever-new SAP ASE 16.

In addition to the compression advisor utility and the automatic backup scheduling capability, a few of SAP ASE 16 features are added listed below:

Feature Support Provided in SCC

Full database encryption Manage database encryption for new and existing databases, 
                                                                      and key management encryption. 

Create or replace command Provide the Create wizard to implement these commands.

Partition-level locking Enable and disable partition locking and provide ‘online’ 
                                                                      option for move, merge, split, delete partition operations.

Index compression Enhance Create Table and Create Index wizards to set index 
                                                                      compression  for table/index/local index partition level.

Error log monitoring   Enhance Error Log monitoring support to import/ export error 
                                                                      flags to multiple servers for ease of configuration and use.

Server configuration alert Allows users to configure alerts to fire when the utilization of 
                                                                      monitored configurable resources exceeds a threshold.

Secure Store Allow removal of residual data when a database, table or index is dropped

Data Cache Spinlock Enhance Monitoring View for SAP ASE with metrics for data spinlock contention.

.

How to RESET Sybase ASA Replication after a Refresh or lost a LOG.

SQL Anywhere LTM

One of the differences between the Adaptive Server Enterprise LTM and the SQL Anywhere LTM is that while the Adaptive Server Enterprise LTM depends on a temporary recovery database for access to old transactions, the SQL Anywhere LTM depends on access to old transaction logs. No temporary recovery database exists for the SQL Anywhere LTM.

Replication depends on access to operations in the transaction log, and for SQL Anywhere primary site databases, sometimes access to old transaction logs. This section describes how to set up backup procedures at a SQL Anywhere primary site to ensure proper access to old transaction logs.

Issue of lost transaction logs

Good backup practices at SQL Anywhere primary database sites are crucial. A lost transaction log could mean rematerializing replicate site databases. At primary database sites, a transaction log mirror is recommended.

The LTM configuration file contains a directory entry, which points to the directory where backed up transaction logs are kept. This section describes how to set up a backup procedure to ensure that such a directory stays in proper shape.

How to reset/restart Replication, when we lost logs

With the Backup utility, you have the option of renaming the transaction log on backup and restart. For the dbbackup utility, this is the -r option. It is recommended that you use this option when backing up the primary database and replication database transaction logs.

For example, consider a database named primedb.db, in directory c:\prime, with a transaction log in directoryd:\primelog\primedb.log. Backing up this transaction log to a directory e:\primebak using the rename and restart option performs the following tasks:

1. Backs up the transaction log, creating a backup file e:\primebak\primedb.log.
2. Renames the existing transaction log to d:\primelog\YYMMDDxx.log, where xx are sequential characters ranging fromAA to ZZ.
3. Starts a new transaction log, as d:\primelog\primedb.log.
   After several backups, the directory d:\primelog contains a set of sequential transaction logs. The log directory should not contain any transaction logs other than the sequence of logs generated by this backup procedure.
4. Modify the LTM configuration file and set SQL_log_files to d:\primelog instead of e:\primebak. The e:\primebakdirectory is only used for recovery, not log scanning.

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