0

Background: I need to revise/improve an automated process I created a couple years ago on a group of servers. The process goes as follows:

  1. Generate full backups of all SQL databases available in a particular SQL instance (hosted on server A)
  2. Grab all of them and compress them into a single file using 7zip compression utility (compression process takes place on server A)
  3. Send the single, compressed 7z file over the local network to store on a separate server (hosted on server B)

This process technically works, but the size and number of databases has grown over the years to be somewhere between 70-100 GB. The process runs daily, and should be switched over to utilize differential backups just to reduce the amount of data involved; for now it performs full backups.

Problem: The problem I am having is the amount of time it takes for 7zip to compress such a massive amount of data. It is taking 7zip about 14 hours to compress all of these databases into a single .7z file. The server in question is a dual-core, W2008R2 64-bit, 16GB RAM machine--which also hosts SQL server for a web application for about 300-500 users. Regarding 7zip specifically, we have it set to perform maximum level compression (ultra / level 9).

The risk is that 7zip consumes too much CPU power during peak hours for our users, so the performance of SQL is jeopardized.

Question: Is there a compression utility available for Windows which could be used to compress SQL database backups which would perform the process more quickly (~80GB in less than 14 hours) or consume less CPU usage than 7zip?

nairware
  • 309
  • 2
  • 10
  • 17
  • What SQL server are you using? MS SQL (since 2008 I believe) and PostgreSQL have compressed backups which may be good enough for your purposes. – afrazier Jan 03 '14 at 05:09
  • SQL 2008R2 64bit. 7zip has been able to compress to about 15% of the original file size, in my experience. This is really good, in my opinion, but I would be willing to sacrifice some compression for quicker compression times (or less demanding compression processes). 7zip does not seem to improve its compression times much with lesser compression levels in my experience. – nairware Jan 03 '14 at 05:41

3 Answers3

2

For the most part you're always going to trade speed for compression. Simply decreasing the compression, or selecting a less efficient algorithm will make this process faster. There's definitely no magic bullet that provides both excellent compression and at great speed.

Chris S
  • 77,945
  • 11
  • 124
  • 216
1

You could use Sql Server's own backup compression but this will also increase CPU usage.

Backup directly onto a shared drive from server B, and then use server B to do the compression. Alternatively, share the drive from A, and use server B to do the compression before copying the file to B.

Or take backups from a secondary database (which has log shipping from the primary db). Presumably you do not yet have a secondary database but if you are taking daily full backups, then it is probably important enough to consider it. Set up log shipping from primary to secondary, and take backups from the secondary, while all your users continue to use the primary database and so do not have their performance compromised while you take (compressed) backups.

In the short term, use lower level compression with 7zip, but this is not a permanent solution.

ramruma
  • 2,740
  • 1
  • 15
  • 8
0

I use the commandline 7zip, 7za, to compress 55GB database files using bzip2 algorithm and zip format. The compressed file is 5.5GB, the server use 2 processor 4core E5520 2.27GHz cpu and the compression time is less the 2 hours

7za a -tzip -mm=BZip2 <destination file>  <source dir>
dwarf
  • 21
  • 3