SQL Safe allows you to set the compression rate suited to your backup needs. You designate a default compression level during the initial setup of SQL Safe. Any time prior to initiating a backup, you can modify your compression level.
How do you choose the best compression level for my environment?
The compression level that is best for your environment depends on your storage and performance needs. Before you choose a compression level, determine whether you need maximum storage and compression (lower performance) or maximum performance (lower compression).
Compression rates and backup times depend on the following factors:
- Whether the SQL Server computer utilizes multiple processors
- Whether you are striping data to multiple backup files
- Available bandwidth on your network connections
- Current processing load, such as backing up multiple databases in the same job
- The type of data you are backing up (for example, text compresses to a smaller size than binary data)
Level 1
Low compression. Provides high execution speed and minimal server load. This compression level typically provides 75-90% compression rates on text data. This compression rate may significantly decrease if you are backing up a database that contains binary data or previously compressed data.Use this compression level if you want to perform fast backups, sometimes during business hours, at the expense of a larger size.
In environments with a slow write speed, this level will not produce backups as fast as higher levels of compression.
Level 2
Medium compression. Provides good data compression while maintaining high-speed execution. This compression level places a moderate load on your server to provide increased compression. This compression level works well in environments with a good balance between multi-processor servers (for example, a 4- to 6-way SMP server) and IO speed.
Use this compression level if your environment includes one or more of the following conditions:
- You want to increase compression without significantly impacting performance
- You can schedule backups during off-hours, if needed
Level 3
High compression. Provides a high level of compression while slightly decreasing execution speed. This compression level provides significant reduction in backed up data size, while placing a higher load on your server. This compression level works well for nightly backups in environments with a powerful multi-processor servers (for example, an 8-way SMP server) where saving space is a high priority.
Use this compression level if your environment includes one or more of the following conditions:
- You want to maximize compression without significantly impacting performance
- You can schedule backups during off-hours, if needed
Level 4
Ultra-high compression. Provides the highest level of compression, to be used when saving space is critical. This compression level places a high load on your server. To achieve acceptable run times, this level should be used on very powerful servers with 8 or more processors and generally only during off-peak periods.
Use this compression level if reduction in backed up data size is your primary objective.