Attix5 Pro FAQ
Search:     Advanced search

Compression efficiency of the MS SQL and VSS plug-ins

Article ID: 306
Last updated: 15 May, 2015

Contents

  1. Introduction
  2. Steps and technical details
  3. Results
  4. Conclusion
  5. References

Introduction

This article highlights backup compression efficiency of the Server Edition (SE) Backup Client when using the MS-SQL and VSS plug-ins on compressed, encrypted and unaltered MS SQL 2008 and MS SQL 2012 databases.

The test was performed on two virtual machines hosting a SQL Server 2008 and a SQL Server 2012 instance respectively. Each server had three instances of the same database (designated for MS SQL plug-in testing), of which one was compressed, one encrypted, and one left unaltered (‘Raw’). An additional three databases with the same steps performed were designated for testing of the VSS plug-in. The databases were named accordingly and the same configuration was produced on both SQL servers:

Server

Database

Plug-in tested

SQL Server 2008,

SQL Server 2012

Comp

MS SQL plug-in

Enc

Raw

SQL Server 2008,

SQL Server 2012

CompVSS

VSS plug-in

EncVSS

RawVSS

The following provides an overview of the test steps:

  1. The test consisted of performing a backup of the databases Comp, Enc and Raw using the MS SQL plug-in and a backup of the corresponding VSS databases using the VSS plug-in.
  2. Before transmitting data to the Storage Platform, the databases were automatically compressed during the backup by Attix5 Pro SE Backup Client.
  3. Subsequently, each database was altered in the same way with a custom SQL query and the backup process was repeated. The cached data from the previous backup resulted in patching of the databases and allowed for smaller compressed size of the databases (and less data transmitted).
  4. Recording and comparing these resulting sizes for both initial and the subsequent backups is the aim of the test:
    1. Each database was set to a SIMPLE recovery model.
    2. The test was subsequently repeated with the databases in FULL recovery model.

The results of both tests are presented in two tables (see the Results section below).

Steps and technical details

Details of the setup

The following versions of the software were used in testing:

Backup Client OS version:

Windows Server 2012 R2 Standard

Backup Client version:

Attix5 Pro SE V7.1114.9501

Plug-in versions:

MS SQL Plug-in: 3.12.1824

VSS Plug-in: 2.141824

Storage Platform version:

7.14.1028.14261

SQL Server versions:

SQL 2008 Enterprise R2 (SQL Server 10.50.1600)

SQL 2012 Enterprise (SQL Server 11.0.2100)

Steps

The steps of the test are explained in detail below:

  1. The database was imported from a 2.5GB .bak file and consists of four tables, three of which are of negligible size (3-5 rows). The fourth table, dbo.LogTable contained 16,997,275 rows of log messages and corresponding additional information columns, which translate to 2,463 MB of data space.
    Below is an example of five entries from the dbo.LogTable table:

    LogMessage

    Identifier

    Datetime

    HostId

    AccountName

    Message Type

    Thread

    Idx

    Mirror done

    MirrorController

    2013-07-09 05:44:02.200

    2

    NOACCOUNT

    1

    (0002205c.03)

    862

    Mirror started

    MirrorController

    2013-07-09 05:59:00.310

    2

    NOACCOUNT

    1

    (0002207c.11)

    863

    Updating settings for 5 accounts on MirrorServer

    MirrorController

    2013-07-09 05:59:00.340

    2

    NOACCOUNT

    1

    (0002207c.11)

    864

    Sent 0 files (0.00 KB) in 1.90 sec

    MirrorController

    2013-07-09 05:59:02.213

    2

    NOACCOUNT

    1

    (0002207c.11)

    865

    Mirror done

    MirrorController

    2013-07-09 05:59:02.213

    2

    NOACCOUNT

    1

    (0002207c.11)

    866

  1. Using the SQL Server Management Studio, the instances of this database were then page-compressed, encrypted using AES 256 algorithm and left unaltered, respectively. The databases were subsequently backed up using Attix5 Pro SE's MS SQL plug-in and the VSS plug-in. The compressed sizes of the transmitted databases were recorded.
  2. After the initial backup, a SQL query was executed on each of the databases, replacing each occurrence of the word 'Error' with the word 'Attix5'. The query has affected 130,854 rows (out of total 16,997,275 rows).

The exact lines of the SQL query were as follows:

UPDATE LogTable

SET LogMessage = REPLACE(LogMessage, 'Error', 'Attix5')

WHERE LogMessage LIKE '%Error%'

  1. Afterwards, a second backup was performed and the new compression sizes were recorded. It was expected to see the second backup to be smaller due to the cache being used.

Results

Below are tables showing the sizes (in megabytes) of transmitted data from the test for both SQL 2008 ad SQL 2012 servers. Both the initial (I) and subsequent (II) backups are shown. The sizes of compressed files from the column 'Backup transmission' are translated into percentages in the '% compression column'.

It is apparent that the differences between the two SQL editions are negligible. The tables below contain results for the databases set to SIMPLE and FULL recovery mode.

SIMPLE mode results

 SQL 2008

Backup transmission (MB)

% compression

Plug-in details

Database size (MB)

I

II

I

II

MS SQL Compression

1096

476

476

56,60%

56,55%

VSS Compression

3636

1083

115

70,22%

96,84%

MS SQL Encryption

2478

2478

530

0,00%

78,61%

VSS Encryption

2530

2514

479

0,65%

81,06%

MS SQL Raw

2478

597

124

75,91%

95,01%

VSS Raw

2530

602

113

76,20%

95,52%

SQL 2012

Backup transmission (MB)

% compression

Plug-in details

Database size (MB)

I

II

I

II

MS SQL Compression

1106

479

481

56,70%

56,54%

VSS Compression

3634

1077

146

70,36%

95,99%

MS SQL Encryption

2478

2478

531

0,00%

78,58%

VSS Encryption

2530

2515

479

0,62%

81,06%

MS SQL Raw

2478

598

124

75,89%

95,00%

VSS Raw

2530

604

113

76,15%

95,52%

FULL mode results

 SQL 2008

Backup transmission (MB)

% compression

Plug-in details

Database size (MB)

I

II

I

II

MS SQL Compression

1096

477

476

56,50%

56,54%

VSS Compression

4741

1597

158

66,32%

96,66%

MS SQL Encryption

2478

2478

530

0,00%

78,61%

VSS Encryption

2494

2488

491

0,25%

80,33%

MS SQL Raw

2478

596

124

75,95%

95,01%

VSS Raw

2494

597

117

76,08%

95,31%

SQL 2012

Backup transmission (MB)

% compression

Plug-in details

Database size (MB)

I

II

I

II

MS SQL Compression

1106

479

160

56,72%

85,50%

VSS Compression

4731

1589

160

66,42%

96,62%

MS SQL Encryption

2478

2478

531

0,00%

78,58%

VSS Encryption

2494

2490

492

0,18%

80,29%

MS SQL Raw

2478

597

124

75,92%

95,00%

VSS Raw

2494

597

117

76,07%

95,30%

Compression

Compressing a database results in a .mdf file whose size appears larger on disk than the original database. This is perhaps due to white-space padding. The actual data stored, as reported by the SQL Management Studio, is in fact less than before the compression. This can be seen in the differences between the MS SQL and VSS plug-ins for the database size of the compressed database.

Because the MS SQL plug-in is able to interpret the SQL native compression method, it reports the “database size” as the size of the resulting .bak file at 1GB (“1106 MB” highlighted in the table above). For the VSS plug-in, on the other hand, the “database size” is the actual on-disk size (3.6GB) of the .mdf file.

The MS SQL plug-in performed very well in the initial backup and transmitted less data than the uncompressed database but the size transmitted remained the same in the second backup. The VSS plug-in, despite its large initial backup, subsequently transmitted a small 115/146 MB patch, however the 113 MB patch in the case of uncompressed database was better still.

Encryption

Encrypting the database resulted in almost no compression before the transmission and hence the whole database had to be transmitted during the initial backup. The subsequent backup (patched) size was reduced by approximately a factor of four but still the worst patching result observed in the test.

Raw database

BEST RESULT.

Conclusion

For best results do not use compression or encryption. When compression is used, the MS SQL plug-in performs better than VSS on initial backups but the VSS plug-in transmits smaller patches on subsequent backups. This conclusion applies to both FULL and SIMPLE recovery mode.

Notes and thoughts

When compressing, one can use the shrink functionality on the database. This could potentially improve the performance of the VSS plug-in with compressed databases. However, shrinking is generally discouraged as it decreases the performance of the database.

References

Compression:

http://msdn.microsoft.com/en-us/library/cc280449.aspx

http://msdn.microsoft.com/en-us/library/hh710070.aspx

http://technet.microsoft.com/en-us/library/dd894051%28v=sql.100%29.aspx

http://sqlmag.com/database-performance-tuning/practical-data-compression-sql-server

http://msdn.microsoft.com/en-us/library/cc280449%28v=sql.105%29.aspx

Encryption:

http://technet.microsoft.com/en-us/library/cc278098%28v=sql.100%29.aspx#_Toc189384672

Article ID: 306
Last updated: 15 May, 2015
Revision: 3
Views: 1125
Comments: 0
Posted: 04 Mar, 2015 by Du Plessis S.
Updated: 15 May, 2015 by Du Plessis S.
This article was:  
Prev   Next
Article 282 - How to truncate data or log files using the Script plug-in     Article 313 - How to restore the AccountServer database after a new install