Thoughts on backups

[Former Member]

Introduction

Over the years you will see a lot of different configurations for database dumps in SAP Sybase ASE (ASE). Most of the time these configurations are static and in a certain sense not meeting our companies expectations.

Let us ask a question: "How many of us do a regular check if our backups meet the company's Service License Agreements (S.L.A.)?" and another one: "Are our dumps performing within parameters"

Why ask these questions? The answer is simple. We should ask these questions over and over again and there is more. Beside the technical implementation there are also some other considerations to implement.

Beneath is list of subjects that are related to backups. This list will not always be applicable to our environment and not complete but it is a start. Think about them when you are responsible for the backup process of your ASE environment.

S.L.A.'s

A good way to start is by challenging your Service Level Agreement manager to come up with all the agreements where recovery of databases are involved. If you only have one S.L.A. its easy but if you have multiple customers you could have a challenge.

Make a list and compare the recovery times. Create an overall scheduling plan for backups and an update procedure for these S.L.A.'s together with your S.L.A. manager. The latter is for preventing future mismatches.

Key questions here are:

1. How long can you afford to down.
2. How much data can you afford to loose .

Internal processes

We all love them. These "take away" dumps for everyone within the company. See to it that they don't disturb your production backups. Database dumps can take time and consume processor time so it could interfere with your production environment. Make internal S.L.A.'s that fit your company's need.

Keep in mind that data could be private. Your Risk department should provide for information about who can have access to data. If in doubt depersonalize or encrypt the data.

External processes

Giving backup data to a 3rd party is always a risk. See to it that you have a disclosure document signed by management and the 3rd party. On the other hand, give information about the backup like version of ASE, type of backup, page size, character set and sort order when needed to the other party. This will help your colleague D.B.A.'s to restore quickly without having to ask.

24/7 Availability

This is a tough one. Although you can run backup's at all times, measure the load of the environment to get the right timing of things.

There are more options and configurations with ASE that can solve your availability but i leave that for another time or someone else to write.

No backup at all

We mention this because a lot off backups are made without good reason. The backup of environments consume resources and time.

A lot of environments are copies of other environments. They can be rebuild from scratch using the backup of other environments and some handy scripts. Think about what you can rebuild and remove them from your storage and daily schedules.

Regular Restore

Making backups is something you do. Restoring them is another thing. This should also be scheduled in your recovery plan. Restores take time. This can also affect the overall recovery time.

Recovering from tape or other solutions before you can actually restore also influences the overall recovery time. I you have to rely on other departments your restore might be at risk.

Technical considerations

Name conventions

See to it that you have a naming convention throughout your entire operation. This is for easy readability. Even if you have multiple R.D.M.S. running. Not that they are to be exchanged but it makes life just easier.

A basic naming convention could be: RDMS.SERVERNAME.DATABASE.DATETIME.STRIPENO.TYPE etc.

Point in time recovery

Yes please. But not always possible. Work with transaction dumps for meeting your SLA. This gives you the possibility to do point in time recovery. Your customers will get the minimum loss of data.

Not always possible? Sometimes cross database transactions are programmed within your environment. This will keep you from point in time recovery. The best way for a backup (dump) is to quiesce the databases involved. Dump the databases and release the quiesce. A down side to this is that you could loose transactions in the process.

I have seen an EMC solution with synchronization of the devices to make a "backup" but that is a complicated process and relies on other processes than ASE.

Striping to speed things up?

Does it? Do you know for sure? The keyword here is "measure". We have seen timing results between having no stripes and nine stripes and it struck us that having no stripes at all was faster.

It all depends. Depends on what? Well how our environments are configured. What type of tape, disk, storage, OS, I/O card(s), CPU etc we have. What was running on our system at the time of dump could also give other results.

Compression

This differs from version to version and what is needed. Again, measuring is important. With new compression levels you even can reduce the CPU consumption but will take more space. Test different options and choose wisely.

ASE Backup Server configuration

Although the backup server performs brilliantly out of the box there are some tweaks, like the -m, -P or maximum number of network connections option, that can be used to our advantage.

Even using a backup-server in or on another environment can take the load away from your production systems. Keep in mind that network performance can suffer from configurations like this.

Consistency

Although everyone performs database consistency checks all the time it is an important task to run before a dump. DBCC's give you a good view on database health before making a dump or after a restore.

System database have mixed data and log

Before creating a backup of the system database do a "DUMP TRAN TO ..." this will purge the log and reduce the size of the dump. I think this should be done for all databases that have a mixed data and log.

Overwriting a file

Before version ASE 15.0.2 it was better not to overwrite files with a dump statement due to the fact that the file would keep its original size if the data is less! If you want a file with the same name remove the old one first. In the newer versions the file is deleted first when creating a dump by ASE.

Documentation

There is the word we all love or hate. But keep in mind that someone else could take over the responsibility of making dumps. Good documentation in general gives satisfaction to all you work with and yourself.

Food for thought

Although above may not be complete for your situation and maybe not all options are covered. It is meant to start you thinking about your backups.

To push this even further. How about getting your own experience into the way we create backups. Can we let the system decide when to make backups, decide how many stripes it should use and where it should put them.......

Acknowledgements:

Sybooks Online Copyright © 2011. Sybase Inc.

Bret Halford