Trace File Compression Using Profiler

In a previous blog entry SQL Server Trace File Compression we discussed using HyperBac to achieve in-line compression of SQL Server server-side trace operations with the compressed trace files readable by the SQL Profiler application.

Similarly, HyperBac can be used to compress trace files generated by the Profiler application (client side trace operations). In this case, the following steps are necessary:

• Install the HyperBac service on the system where the Profiler application is to be run


• Request a custom generated license file (either evaluation or full) from HyperBac Technologies supporting Profiler


• Use the HyperBac Configuration Manager application to either register the .trc extension for all paths or a specified path on the system, or register all extensions for a specified path where the trace files will be written.

Simply applying the custom generated license file from HyperBac Technologies support personnel and following the steps above, SQL Server trace files generated using Profiler can be compressed (and/or encrypted) as they are written out, saving up to 90% or the DASD required to store this data. Furthermore, the compressed trace file images can be read directly by the Profiler application without being decompressed first, simplifying management of SQL Server trace data.

Using HyperBac with DFS

HyperBac is fully compatible with Windows DFS (Distributed File System) in Domain or Stand Alone mode. DFS allows remote storage to be addressed with a uniform share (DFS root), consider the following example performing a HyperBac SQL Server compressed backup operation of a SharePoint database:

Direct UNC Access:

BACKUP DATABASE WSSContent
TO DISK = '\\DC1\dfsroot\WSSContent_Backup.HBC'

Accesses the same path as above using Domain DFS:

BACKUP DATABASE WSSContent
TO DISK =
'\\hyperbactech.corp\dfsroot\WSSContent_Backup.HBC'


As DFS is an existing file system driver on the system, HyperBac will be bypassed after initial installation until the system is restarted. As long as the HyperBac Control Service is set to start up automatically (which is the default) after a one-off restart, HyperBac will integrate seamlessly with DFS to provide streaming compression and encryption for specified backup/restore or export/import operations.

Reduce SQL Server Database Restore Times

You can instantly reduce SQL Server restore times with HyperBac by adding the following WITH clause to the RESTORE statement:

WITH MAXTRANSFERSIZE = 1048576

Natively SQL Server defaults to 64K blocks for restore operations whereas all of the major VDI based backup products (LiteSpeed, Red Gate SQL Backup, SQL Safe, etc) will override this behind the scenes in VDI to 1MB blocks for performance reasons.

Using this clause with a native command to restore a compressed HyperBac backup will reduce the time by 50% or more depending upon the system.

BCP Compression Using HyperBac Video Demonstration

A recent instructional video has been posted on the MidnightDBA website which walks through a practical scenario to perform a BCP operation with on the fly compression using HyperBac.

In the video, an Address table is bcp'ed out directly to a compressed output file using HyperBac, the compressed file was then bcp'ed directly in using HyperBac without requiring the file to be decompressed first.

The video also discusses the benefit of the ZIP compatible output format feature unique to HyperBac, which allows DBA's to export directly to a file which can be extracted by WinRAR, WinZip, 7Zip, etc. This is especially important for interchanging compressed files with third parties without requiring them to have HyperBac installed.

The instructional video can be found at:

http://midnightdba.itbookworm.com/hyperbacbcp/hyperbacbcp.html

Furthermore the MidnightDBA website contains many other instructional videos and informative articles on SQL Server administration and development topics such as backup, SSIS, SQL Server 2008 and much more. It is well worth a visit:

http://midnightdba.itbookworm.com/blog/

Unattended Installation of HyperBac for SQL Server

An advantage of HyperBac is that silent, unattended deployment to SQL Server and Oracle on Windows systems is very straightforward. HyperBac's architecture makes integration and deployment to remote SQL Server systems via SMS, Group Policy, ZenWorks, Altiris or any other software deployment systems a simple task. An example of an unattended Typical installation of HyperBac for SQL Server via MSIExec is provided below:


msiexec /i HyperBacSQLServer-BackupExplorer.msi /qn


In this scenario the following actions will be performed:

• The HyperBac service, OLEDB Object Recovery Provider and other related binaries will be installed into the %PROGRAMFILES% directory on the remote system


• The HyperBac service will be started with the default configuration

HyperBac is unique in that no connection to SQL Server is necessary and no components are installed inside the SQL Server instance. The default configuration will allow for transparent compression and/or encryption of SQL Server backup and export operations and granular backup object recovery from physical backup devices via TSQL, SSIS or DTS. Other customizations such as a custom configuration can be applied using MSI Transforms (MST files).

Dell Performance Review of HyperBac

Senior Systems Engineers from Dell, Zafar Mahmood, Anthony Fernandez and Naveen Iyengar, recently concluded a detailed evaluation of the HyperBac for SQL Server and Oracle (on Windows and Linux) products. HyperBac was tested for performance and compression with very favorable results. The SQL Server tests were conducted against a 500MB TPC-E database and achieved the following results:

	Native	HyperBac	Result
Backup File Size (KB)	461,928,551	152,062,618	67% Compression
Elapsed Backup Time (s)	8,806	2,987	66% Backup Time Reduction
Elapsed Restore Time (s)	8,534	5,400	37% Restore Time Reduction

The Oracle tests were conducted against the large implementation of the Dell DVD Store Application database on the Windows and Linux platforms in which HyperBac achieved 71 % compression and was 4.4 times faster than the equivalent Oracle 10g compressed backup set operation performed without HyperBac. Oracle Export and Export Data Pump tests as well as SQL Server BCP tests incorporating HyperBac integrated compression were conducted as well. The results are detailed in the white paper which can be downloaded from the link below:

White Paper - HyperBac Delivers High-Performance Database Backup for SQL Server and Oracle on Dell-EMC Infrastructure

SQL Server Backup Compression Shootout

HyperBac for SQL Server recently featured as the editors choice in the April edition of SQL Server Magazine in an article titled 'SQL Server Backup Compression Shootout'. In the article 3 well known SQL Server backup products including HyperBac were tested against one another with HyperBac coming out on top with a 4 out of 5 star rating. The article can be viewed online on the SQL Mag website at:

http://www.sqlmag.com/Article/ArticleID/98180/sql_server_98180.html