Compression Technology slimming SQL Server backup files

However, the volume is still very large. Therefore, in daily work, how to reduce the size of SQL Server backup files is a concern of many database administrators.

Maybe Microsoft's database product design experts have heard the voices of many database administrators. In the latest MySQL 2008 database, the concept of backup compression is put forward. The basic principle is the same as using RAR and other compression tools to compress files, which can make the original backup files smaller in size. This directly brings about the benefit of saving the backup space of the server. In addition, if the sqlserver database is configured with remote backup, it can also save network bandwidth, shorten the time for remote backup, and so on.

Recently, I upgraded my database to 2008 and changed the backup configuration to support backup compression. I will share with you the management experience of backup compression. I hope these experiences can help you manage the backup and compression of SQL Server databases. Simply put, if you want to adopt the backup compression technology, the database administrator must understand several issues.

Question 1: Restrictions on backup compression technology.

Because the backup compression technology was introduced in version 2008, its compatibility may be limited. According to the official statement, all database Versions later than 2008 support this backup compression technology. Therefore, backward compatibility should not be a problem. The database administrator is concerned with some restrictions when upgrading a database from a lower version to a later version. Understanding these restrictions may reduce the risk of database upgrades. According to my understanding, there are at least two restrictions.

First, compressed backup and uncompressed backup cannot coexist in a media set. In the SQL Server database, to back up a dataset, you must first create a media set. After the upgrade, I made a full backup of the database, which did not use the compression technology. Later, when I tested compression and backup, I found that none of them was successful. Later, according to the error message, I found that the compressed backup and uncompressed backup cannot coexist in one media set. After the author re-establishes a media set, the backup compression technology will work.

Second, earlier versions of SQL Server databases cannot read compressed backups. In order to test the forward compatibility of the backup compression technology, the author uses the backup compressed database files to restore the database of Version 2005. Note that this database file is backed up immediately after it is upgraded to 2008. That is to say, in addition to this compression technology, there is no new technology or object of 2008. However, it is found that the database version 2005 does not recognize the account and does not recognize the compressed backup file. It can be seen that the data of earlier versions of SQL Server cannot read the compressed backup file.

These are the two restrictions that I found after testing. However, after searching some official materials, I found that there is still an important restriction. For example, the ntbackup tool cannot share the compressed Database Backup Tape. However, I did not use this content, so I did not test whether this is the case.

Question 2: What is the compression effect?

If the compression and backup technology is used, how much can the backup file be reduced? This is mainly related to databases. According to my understanding, the following factors will directly affect the final compression effect.

The first is related to the data type. If most of the data in the database is of the dense type, the compression will be better. If there are many numeric types, the backup files cannot be much smaller after the compression backup technology is used. This also provides a standard for determining whether database management elements should adopt the compression and backup technology.

Second, whether the data is encrypted. Under normal circumstances, if the data in the database is not encrypted, the compression effect will be obvious. On the contrary, if the database data is encrypted, the compression will be much smaller. For example, if the database administrator uses transparent data encryption to encrypt the entire database, after the compression backup technology is adopted, the compression backup will not reduce the size of the database or even reduce it at all.

Furthermore, it also has something to do with data table design. In general, if the table design is reasonable, the compression effect will be much better. If a page contains multiple rows, and a field contains the same value, this value can obtain a relatively large compression ratio. In contrast, if most of the data in the field is random (even if there is only a slight difference), the size of the compressed backup is almost the same as that of the uncompressed backup. That is to say, to achieve better compression performance, you need to consider it during database design. For example, you can use some list fields for user selection to improve the compression effect of the final backup file.

Question 3: What is the impact of compressed backup on performance?

After the database adopts compressed backup, the impact on the database is both beneficial and harmful.

The advantage is directly related to the database compression effect mentioned above. Because the compressed backup files of the same database are smaller than the original backup files, the device input and output required for compression backup is usually less, which greatly improves the backup speed. In addition, remote backup of databases can greatly shorten the network transmission time. Therefore, when the database compression effect is better, the database performance will be greatly improved.

The disadvantage lies in the resource consumption. If compression backup technology is used, compression will significantly increase CPU usage. The extra CPU occupied by the compression process may have a negative impact on the military operations. To minimize this adverse effect, you can adjust the backup policy of the SQL Server database. For example, put the backup time at midnight. At that time, there were basically no users using the database, or the chance of using the database would be greatly reduced. At this point, there is some additional CPU consumption, which is hard for users to notice.

In addition, you can reduce the priority of the database to reduce the adverse impact of compression and backup on the database. If a CPU contention occurs, the CPU usage of the Backup will be limited by resource control. You can map a specific user session to a resource Governor workload that limits CPU usage. However, this implementation is complicated. If you have the opportunity in the future, I will discuss the topic. For most enterprises, the use of databases has a significant high incidence and low tide. You only need to slightly adjust the backup policy to compress and back up the database during off-peak periods, so that you can easily avoid the negative impact of backup compression. There is no need for a thankless solution. Even for financial institutions like banks, users will be greatly reduced after. At this time, the CPU they released is enough for compression backup.

Fortunately, I used to adopt a backup policy that enables automatic database backup after PM. Therefore, after this compression backup is adopted, the impact on performance can be ignored.

Question 4: How to enable compressed backup?

By default, the database does not adopt compressed backup when performing backup. If the database administrator needs to enable compression backup for specific purposes, the administrator needs to start the backup manually. The default behavior of compressed backup is determined by the default option server-level configuration of backup compression in the database system.

To enable the compression and backup policy, you only need to go through three simple steps.

Step 1: Open the database object Resource Manager, right-click the server that needs to enable the compression backup policy, and then open the Properties dialog box.

Step 2: click Database settings node. Find the Backup and Restore tab. The compression Backup tab displays the current configuration of the default backup compression settings. This "compression backup" option determines whether the database uses a compression backup policy during Backup. If this option is selected, compression backup is enabled for the database by default.

Step 3: Create a new backup media. As mentioned above, compressed backup and uncompressed backup cannot be stored in the same media set. If the database administrator enables this compression backup policy midway through. That is, if there are uncompressed backup files in the original backup media, the database administrator must either delete the original backup file or recreate a backup media. The author's opinion is to re-establish the separate media and retain the original backup file. This is mainly for security considerations. In case the compression backup fails for some reason, there are still remedial measures.

Compressed backup is a new technology launched by sqlserver database. I think that if the enterprise database capacity is relatively small, there is no need to use this compression backup. Only when the database capacity is relatively large or remote backup is required, the effect of using compressed backup is shown. Due to the large restrictions and management difficulties of compressed backup, the database administrator still needs to evaluate the effect that compression backup may bring to the enterprise in terms of performance and compression effect. Make a choice after evaluation, whether to adopt compressed backup.