Virtual Machine Snapshots as Backups for Sybase ASE?

Use of virtual machines has become common even in database production environments. For the SAP Sybase Adaptive Server Enterprise (ASE) DBA, the use of virtual machines can introduce many new issues that affect the health and performance of ASE. In this article, we’ll look at several issues to consider in database backup. Virtual machines, such as VMware or Hyper-V, have different methods of backup and restoration, including the ability to take snapshots of the environment. As a DBA, it’s important to be aware of a few critical considerations if you are using virtual machine snapshots as a way to backup and restore.

Freeze the database file and log files before a snapshot

When you take a snapshot of the server, you get a view of the whole server at a point in time. The snapshot is of the overall server and all the databases, not of individual databases. This may seem fine, but sometimes a snapshot can cause the different parts of the database (for example, the transaction log) to become out of sync with other database parts. If this happens, the database is marked as corrupt, and it won’t start again—and this is definitely not fine.

If you are going to backup an ASE database using a snapshot and you want to avoid this possible corruption, you should make certain the database files and log files stay frozen during the snapshot process. What does this mean? It means that either the databases must be quiesced before a snapshot is scheduled, or the server must be shutdown. You can lookup the quiesce database statement for details on how to use it.

If you don’t quiesce the database or shut the server down, and the database is actively in use during a snapshot, you should be very certain that you have the proper support should the databases fail to come back on line after the snapshot. If you don’t, you are gambling that your system will be recoverable, and that’s not a gamble we recommend.

Monitor the space consumed by the snapshot

You may also find an issue with the amount of space consumed by the snapshot. If the virtual machine snapshot tracks all changes within the database at all times, the snapshot may consume all the space available to it. For example, if the snapshot monitors bulk changes such as index rebuilds of large tables, the snapshot data may grow significantly and quickly exceed the space available. When this happens, modifications to the database will stop, and you must increase the snapshot space for the database to begin functioning again. To avoid this, you can get a snapshot before the task executes, and disable the snapshot from gathering further data until the task completes.

Become virtual machine aware

Virtual machine snapshots can be a useful backup and restore strategy, but carefully consider both freezing the database and log files prior to a snapshot and monitoring the snapshot size to avoid problems like database corruption and database disruption. DBAs must become savvy about the new issues virtualization introduces into how they manage databases, and these issues covered here are just a few to consider.