Sql-server – What Backup Compression Rate Can I Expect for a Database with Mostly JPG Data

backupblobcompressionsql server 2014

I am considering using a database to store one or more images (.JPG) associated with a record identifier and filename.

CREATE TABLE Images (
    RecordID BIGINT NOT NULL,
    ImageFilename VARCHAR(50) NOT NULL,
    ImageData VARBINARY(MAX) --expected avg. size: 256K
)

I anticipate that 90% of the space utilization on the database will be coming from the BLOBs. I know that SQL Server data compression will not compress the BLOB data through row or page compression. Can I expect any savings when compressing the backups, other than the compression taking place on the RecordID and ImageFilename fields?

Best Answer

I finally went ahead and got a test database loaded with data. Table structure is:

CREATE TABLE [dbo].[ImageTable](
    [CompositeID] [bigint] NOT NULL,
    [ImageID] [tinyint] NOT NULL,
    [FullImage] [varbinary](max) NULL,
    [ThumbnailImage] [varbinary](max) NULL,
    CONSTRAINT [XPK_ImageTable] PRIMARY KEY CLUSTERED ([CompositeID] ASC,[ImageID] ASC)
)

The table was loaded with approximately 2.8M records. The FullImage value was set, and the ThumbnailImage value was left as NULL for each record.

The database itself is 154GB in size. The uncompressed backup is 154GB in size. The compressed backup is 134GB in size.

Given that the two PK fields account for approximately 24GB (2.8M records * 9 bytes/record), it looks like the only savings from compression are being realized by compressing the PK fields.

Related Question