Compress technology to backup files to SQL Server thin body _mssql

However, its volume is still very large. So, in daily work, how to slim down the backup files of SQL Server is a problem that many database administrators care about.

Perhaps Microsoft's database product design expert hears Many database administrator's voice. In the latest version 2008 SQL Server database, the concept of backup compression is presented. The basic principle and the use of RAR, such as compression tools compressed files, you can make the original backup file volume smaller. The immediate benefit is that you can save your server's backup space. In addition, if the SQL Server database is configured offsite backup, it can also save network bandwidth, shorten the time of offsite backup, and so on.

The author has recently upgraded the database to 2008 and changed the backup configuration so that the database supports backup compression. The following is the author of the backup compression management experience to share with you. I hope the author of these experiences can help you do a good job of SQL Server database backup compression management. Simply put, if you're going to use backup compression, then the database administrator needs to figure out a few things.

Problem One: Limitations of backup compression technology.

Because the backup compression technology was presented in version 2008, the compatibility may be limited. According to the official version, the backup compression technology will be supported in the database versions since 2008. Therefore, backward compatibility should not be a problem. The database administrator should be concerned about some of the limitations of upgrading from a lower version to a higher version of the database. Mastering these restrictions may cause database upgrades to run into fewer problems. According to the author's understanding, there are at least two restrictive conditions here.

One is that compressed and uncompressed backups cannot coexist in a single media set. In a SQL Server database, if you want to back up a dataset, you first need to establish a media set. After the author completes the upgrade, a full backup of the database is made, which does not use compression technology. Later, the author in the test compressed backup, but found how unsuccessful. Later, according to the error prompted to query the relevant data and to test themselves, only to find that compressed backup and uncompressed backup can not coexist in a media set. After the author has established a media set, the backup compression technology can work.

The second is that a compressed backup cannot be read by an earlier version of the SQL Server database. In order to test the forward compatibility of backup compression technology, the author made use of backup compressed database files, to restore the 2005 version of the database. Note that this database file is backed up immediately after upgrading to 2008, which means that, in addition to this compression technology, 2008 of new technologies and objects are not used. However, it is found that the 2005 version of the database is not admit, do not recognize this compressed backup file. Visible, earlier versions of SQL Server data could not read compressed backup files at all.

This is the author after the test found two restrictions. However, the author inquires some official information and found that there is an important limitation. such as the Ntbackup tool cannot share a compressed database backup tape. However, because the author does not use this content, so there is no test is so.

Question two: What is the effect of compression?

If the compression backup technology is used, how much can the backup file thin down? This is mainly related to the database. According to the author's understanding, the following factors will directly affect the final compression effect.

The first is related to the data type. If the database is mostly character-type data, the compression effect is better. And if the number type is more, then the compression backup technology, backup files can not be much smaller. This also provides a standard for determining whether the database management element should use compressed backup technology.

The second is whether the data is encrypted. Under normal circumstances, if the data in the database is not encrypted, its compression effect is more obvious. Conversely, if the data in the database is encrypted, the degree of compression is much smaller. If the database administrator uses transparent data encryption method to encrypt the entire database, then the compression backup technology, compressed backup does not reduce the database how much, or even not reduce.

Furthermore, it is related to the design of the datasheet. Under normal circumstances, if the table design is more reasonable, then its compression effect will be much better. If a page contains more than one row, and one of the fields contains the same value, the value can get a larger compression rate. Conversely, if the data in a field is mostly random data (even if there is only a slight difference), its compressed backup is almost the same size as an uncompressed backup. This means that if you want to achieve a better compression effect, you need to consider when designing a database. If you can use some list fields for the user to choose, you can improve the compression effect of the final backup file.

Question three: How does a compressed backup affect performance?

Database after compression backup, the impact on the database is dual, that is beneficial and harmful.

The benefit is directly related to the database compression effect mentioned above. Because the compressed backup file of the same database is smaller than the original backup file, the compression backup requires less device input output, so the backup speed can be greatly improved. In addition, the database for off-site backup, but also can greatly shorten the network transmission time. Therefore, when the database compression effect is better, for the database performance, there will be a great improvement.

The disadvantage lies in the consumption of resources. If compressed backup technology is used, compression can significantly increase CPU usage. The additional CPU consumed by the compression process may have a negative impact on the art of war. In order to minimize this adverse impact, you can do is to adjust the SQL Server database backup strategy. If you put backup time in the middle of the night. At that time, there was basically no user using the database, or the database would be significantly less likely to be used. At this point, it is more than a few additional CPU consumption, the user is also difficult to detect.

Alternatively, in the database, you can reduce the negative impact of compressed backups on the database by reducing the priority level. If CPU contention occurs, the CPU usage of this backup is limited by resource control. Implemented by mapping a specific user session to a resource governor workload that limits CPU usage. However, this implementation is more complex, the future if there is a chance, I will be the topic of the story. For most enterprises, the use of database has obvious high and low ebb period. It is easy to avoid the negative impact of compressing backups by simply adjusting the backup strategy and compressing the database at low tide. And there is no need for a thankless task to adopt such a complex solution. Even financial institutions such as banking have a significant reduction in the number of users after 12 o'clock in the evening. At this point, they freed the CPU for compressed backup use is enough.

Fortunately, my previous backup strategy was to have the database automatically backed up after 12 o'clock in the evening. So this time with a compressed backup, the impact on performance can be ignored.

Question four: How do I enable compressed backup?

By default, a database does not use a compressed backup when it performs a backup. If a database administrator wants to enable a compressed backup for specific needs, the administrator will need to manually start it. The default behavior for compressed backups is determined by the backup compression default option server-level configuration in the database system.

If you need to enable a compressed backup strategy, you only need to go through a simple three steps.

First step: Open the Database Object Explorer, right-click the server on which you want to enable the compression backup policy, and then open the Properties dialog box.

Step Two: Click the Database Settings node. Locate the Backup and Restore tab. The current configuration of the backup compression defaults is displayed in the compressed Backup page check. This "Compress backup" option determines whether the database should be backed up with a compressed backup strategy. If selected, the database will enable compressed backups by default.

Step three: Create a new backup media. As I mentioned above, compressed backups cannot be stored in the same media set as uncompressed backups. If the database administrator is enabling this compressed backup policy halfway through. That is, there are already uncompressed backup files in the original backup media, so the database administrator either needs to delete the original backup file or rebuild a backup media. The author's opinion is to re-establish the media, while retaining the original backup files. This is mainly due to security considerations. If a compressed backup is unsuccessful for some reason, there can still be a remedy.

Compressed backup is a new technology introduced by SQL Server database. I think that if the enterprise database capacity is small, it is not necessary to use this compression backup. The effect of a compressed backup is only apparent if the database capacity is large, or if you want to make offsite backups. Because compressed backup has more restrictive conditions and management difficulties, the database administrator needs to evaluate the effect that compressed backup may bring to the enterprise in terms of performance, compression effect, and so on. Evaluate and then choose whether to use a compressed backup.