Introduction to Sybase and SAP IQ
Sybase Inc. was a software company providing software for enterprises with the main purpose of managing information in relational databases, with their efforts mostly focused on a product with a similar name – Sybase IQ (Intelligent Query). The Sybase company was acquired by SAP in 2010, and SAP ceased using Sybase as a name in 2014, renaming Sybase IQ into SAP IQ (which is a name that is used until this day).
However, SAP IQ is still referred to as Sybase IQ quite often, which is why the main terms that are going to be used in this article are Sybase and Sybase IQ. The main goal of this software system is to analyze large data volumes with speed and efficiency.
Sybase IQ and data safety
Since Sybase IQ’s main goal is data management, it should not be a surprise that it also has quite a lot of measures and features in place around protecting your data from corruption and inconsistencies. There are two different types of crashes that Sybase discerns and can provide a definite course of actions about – a system crash and a media failure.
The former is a result of the operating system going down in the middle of some sort of transaction – the reason for that might be the power failure, the sudden system reboot/crash, the influence of another application, or some other reason. Sybase IQ as a system can recover from most of the system crashes automatically with no input from the end user and no need to restore the database itself.
The latter, on the other hand, is a completely different type of issue – since it implies that either the storage device or the data itself within the database has become unusable for some reason. A media failure of any sort requires user input when it comes to restoring faulty or inconsistent data, which means that you actually have to have system backups beforehand to be able to restore them.
Sybase IQ and its built-in backup capabilities
There is also a dedicated command made solely for the purpose of Sybase backup creation – BACKUP. This command creates a backup of both your Sybase IQ data, as well as the SQL Anywhere database (or the catalog store) that the Sybase data works with. This command only works with the database you have connected to and there is no way to specify a backup target other than connecting to another database.
Sybase backup capabilities are also somewhat impressive on their own, offering several different approaches to data backup and multiple backup types. This is why even the built-in Sybase backup capabilities include not only full backup capabilities, but can also do incremental, differential (incremental since full) and virtual backups.
Full backup copies the entire database with no exceptions, virtual backup only leaves out the IQ store table data. Incremental backup creates a copy of every data that was modified since the previous backup (any type), and differential backup does the same as the incremental one, the only difference being the cut-off point – differential backup only copies everything from the last full backup.
These backup levels do not affect the temporary store data, but do work with metadata and other information that may be needed to restore the temporary store structure.
The aforementioned backup levels or approaches are database backup, OS-level backup, virtual backup and archival backup. Both database backup and system-level backup are the more regular backup procedures with a different scope – creating backups of either a specific database or an entire operating system. These backups can be customized by an assortment of additional commands, including, but not exclusive to:
- ATTENDED ON|OFF – specifying whether or not the system would be expecting a human intervention in the middle of the backup process in case there is a need to connect a new storage device, for example
- FULL/INCREMENTAL/INCREMENTAL SINCE FULL – choosing one of the aforementioned backup types (the differential backup type is seen in Sybase IQ as the incremental since full type)
- TO device_name – specifying one or more storage devices that are going to be used as the backup storage location
- CRC ON|OFF – the operation that compares checksums of the backed up files during every restoration operation for the sake of figuring out whether the backup in question is correct and not corrupted in some way
- BLOCK FACTOR number – specifies the number of IQ blocks that would be written to the target device at the same time, it has to be greater than 0 for the backup process to work, and the default values are 25 for UNIX platforms and from 1 to 120 depending on the database block size when it comes to Windows
It is also worth noting that, while the system-level backups can be initiated with the same BACKUP command, there are several other procedures that needs to be performed before and after this particular process, including shutting down the entire database, making sure that all of the system files are backed up, and so on.
As for the remaining two options, virtual backups are sometimes referred to as NULL backups and include everything in your IQ database aside from the IQ store table data. Virtual backup also has a requirement of separate OS-level backup of that same store – this copy would have to be restored before your virtual backup can be restored. The virtual backup itself copies IQ catalog data, IQ metadata, as well as the rest of metadata from that store (with the exception of data that is specific to a single table).
An archival backup is even more uncommon than the rest, since (as the name suggests) its main purpose is archival. The introduction and active reinforcement of a number of regulations led to this kind of backup becoming more and more popular, since at least HIPAA (Health Insurance Portability and Accountability Act) has a number of strict rules in regards to compliance and data archiving with easy access to it.
As such, WORM storage solutions appeared (write once, read many), allowing a storage location with only one write operation per storage with no way of tampering with it. WORM storage is referred to as read-only hardware when it comes to Sybase IQ – allowing for the backup operations to proceed as normal until the first write cycle is complete, and “freezing” the backup in question afterwards for a specific time period as specified in the compliance act.
Third-party Sybase backup solutions
As you can see above, the built-in Sybase backup capabilities are quite vast and varied, but they do have a specific skill level to operate properly, and it can get quite confusing when you need to perform some of the more unusual operations. However, that’s not to say that these tools are the only way to perform backups for Sybase IQ.
In fact, there is an entire market of multifunctional backup tools and solutions that are highly competitive and filled with all kinds of solutions, a lot of which also have support for Sybase backup and expand upon what Sybase itself can do.
For example, Bacula Enterprise offers a separate module specifically for Sybase backup purposes in order to simplify and streamline all kinds of Sybase backup operations without the need for the operator to have an extensive knowledge of Sybase as a system. Bacula Enterprise offers Point in Time Recovery, several different backup types, works on both 32 and 64-bit Linux platforms and makes it far easier to manage all of the Sybase backup operations at once via a centralized web console. With its other modules, Bacula is able to protect nearly all IT estates in their entirety, and combines this single platform ability with especially high levels of security and reliability. Learn more about Bacula’s Sybase backup module in this whitepaper.
Of course, this is not the only available option out there. Commvault also supports Sybase backup operations, offering several different backup targets, both full and incremental backups (as well as a transaction log backup), the ability to schedule and automate backups, and more. Its restoration capabilities offer the ability to restore either the entire system or specific databases, and there is also an option to restore data from a specific date range for some of the more unusual circumstances.
Some of the lesser-known backup solutions such as Handy Backup also offer the ability to work with Sybase databases, supporting all of the built-in Sybase backup types, a variety of target storage locations (from local to multiple different clouds, NAS units, FTP, and so on) and a number of other useful features – scheduling, data encryption, low hardware requirements or even the capability to start specific operations after an event of sorts (system log-off, USB drive plug-in, etc.).
Veritas NetBackup can also create Sybase backups in two different ways – either by using an appropriate command in the NetBackup interface, or by opening an isql utility and performing a DUMP command for a specific Sybase database or system. It is also possible to use a number of NetBackup’s overarching capabilities for these backups, such as backup automation, manual backup, user-directed backup, several restore operation types, and so on.
Some software providers also offer a somewhat different approach to backup of Sybase – integrating with the database type itself without providing too many new features – but rather simplifying the existing ones. MicroFocus Data Protector is one such example, offering Sybase integration that supports all of Sybase’s own backup levels. Data Protector makes it easier to perform backup and recovery operations due to its user-friendly interface and it can also offer a few useful advantages on its own, be it scheduling, backup cleanup, and more.
Sybase IQ, now widely known as “SAP IQ” is an extremely popular database management system that is used by many different organizations. There are a good number of backup and recovery features that Sybase provides in itself, and a host of additional features that third-party backup solution providers can offer. These additional features bring increased management, reporting and backup options. Indeed, integration into the third party solution, by definition, brings Sybase and its data into the overall backup solution’s environment and ecosystem with all the associated advantages.