Mirrored SQL Backups with Compression Using HyperBac

Beginning in SQL Server 2005, SQL Server provides the capability to mirror a backup operation to an alternative location. This provides redundancy and can be an integral component to an offsite disaster recovery and/or business continuity plan.

HyperBac can be used to accelerate and optimize this process by compressing one or both of the primary backup file and/or the remote offsite mirrored backup file.

Furthermore using HyperBac, compression and/or encryption can be achieved seamlessly using native SQL Server commands and procedures.

Some simple examples are provided below with code, assuming HyperBac is installed on the server performing the backup operation and the HyperBac Control Service is running, note that HyperBac does not need to be installed on the mirrored backup location.

First a native example without compression to be used as a baseline:



Figure 1 – Conventional backup operation mirrored to a remote path without compression

As seen in this example, the remote UNC mirror path introduces a substantial bottleneck and adversely affects the throughput and elapsed time for the backup operation.



Figure 2 – Local backup directory



Figure 3 – Remote (Mirror) backup directory

Now we will introduce compression to the remote mirror destination using HyperBac, the code to be used is as follows:

/* Uncompressed Primary Backup File and COMPRESSED Mirrored Backup File /*
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\MSSQL\Backup\AdventureWorks_Uncompressed_FullBackup.bak' MIRROR TO
DISK = '\\192.168.0.3\C$\MSSQL\Backup2\AdventureWorks_Compressed_MirroredBackup.hbc'
WITH FORMAT



Figure 4 – Backup operation (not compressed locally) mirrored to a remote path with compression

As seen above by compressing the remote mirror destination using HyperBac the backup operation runs twice as fast as the equivalent uncompressed operation. The output files in the remote mirrored backup location are shown below:



Figure 5– Remote backup directory

As visible from the above, the compressed backup file is approximately 25% of the uncompressed backup file. Double clicking the file 'AdventureWorks_Compressed_MirroredBackup.hbc' brings up the file properties using the HyperBac WinExtractor with the details below:



Figure 6 – File properties for the HyperBac compressed backup file

To speed up the operation even further, compress both the local and remote backup file on the fly using HyperBac. The code used is as follows:

/* COMPRESSED Primary Backup File and COMPRESSED Mirrored Backup File /*
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\MSSQL\Backup\AdventureWorks_Uncompressed_FullBackup.hbc' MIRROR TO
DISK = '\\192.168.0.3\C$\MSSQL\Backup2\AdventureWorks_Compressed_MirroredBackup.hbc'
WITH FORMAT



Figure 7 – Backup operation mirrored to a remote path with compression locally and on the remote destination

We can now see the files are compressed in both the local backup directory and the remote path.



Figure 8 – Local backup directory after HyperBac compressed backup operation



Figure 9 – Remote backup directory after HyperBac compressed backup operation

Similarly, HyperBac can produce a ZIP compatible output file, enabling decompression using WinZip, WinRar, PKZip and others as well as being able to restore the compressed file directly with native RESTORE commands using HyperBac. The code used to produce ZIP compatible compressed backup files on the local and remote location is as follows:

/* COMPRESSED Primary Backup File and COMPRESSED Mirrored Backup File (ZIP Compatible) /*
BACKUP DATABASE AdventureWorks
TO DISK = 'C:\MSSQL\Backup\AdventureWorks_Uncompressed_FullBackup.zip' MIRROR TO
DISK = '\\192.168.0.3\C$\MSSQL\Backup2\AdventureWorks_Compressed_MirroredBackup.zip'
WITH FORMAT



Figure 10 – Compressed mirrored backup operation to ZIP compatible output files

For more information visit SQL Server Backup Compression using HyperBac

Stephen Wynkoop Review of HyperBac

Stephen Wynkoop, President of the SQL Server World Wide Users Group recently reviewed HyperBac on the SSWUG.ORG website. And excerpt from the review is included below:

I’d highly recommend you take a look at HyperBac. This is a significant step forward – not just an evolution in backup, but more of a revolution in storage management. The integration, the tools, the seamless integration and how it’s implemented are just too important to miss out on for your systems. They’ve simplified the licensing, they’ve gone well beyond just backup compression and encryption, addressing storage as a whole.

This is one not to miss trying out on your systems. I can’t imagine a case where this doesn’t deserve serious consideration for installation and usage.

The entire article can be viewed at Stephen Wynkoop HyperBac Product Review

Reduce Backup Times Using HyperBac

Background
Performance Tuning Methods

Background

HyperBac delivers equivalent performance results to any other compressed backup solution for SQL Server on the market today (including Quest LiteSpeed, Red Gate SQLBackup, Idera SQLSafe and others). However HyperBac works fundamentally different to all of these other products which extends the benefits of compression well beyond any of these other products.

The other solutions, which based on VDI or the "Virtual Device Interface" (an API initially developed by Microsoft for Tape backup vendors). With these products you typically issue a vendor created extended stored procedure which runs in the SQL Server memory process space and communicate to a vendor process (eg SQLLiteSpeed.exe) via some sort of proprietary process. The vendor process creates the VDI object to map memory from this process to the relevant SQL Server memory process. Once established, it can then be used for backup related operations. Conversely, HyperBac uses file system filter technology which means that HyperBac is completely transparent to SQL Server.

The VDI solutions, such as LiteSpeed, will generally create multiple "Virtual Devices" or files, based upon the number of threads specified in the LiteSpeed extended stored procedure or command line statement. Although you may only specify one output backup file, one virtual file is created per thread specified. This is why if you ever extract a multi-threaded backup file you may end up with multiple backup files. Meaning that you would need to perform a striped native restore even though you did not issue a striped backup statement.

If you do not specify an overriding @threads (or equivalent in other products) value, this value may be set automatically by the product to what it believes will yield the best time result. A practical example using LiteSpeed is given below:

--LiteSpeed backup command
EXEC master.dbo.xp_backup_database
, @database='myVLDB'
, @filename='X:\myVLDB.SLS'
, @init= 1


Because an @threads parameter was supplied, LiteSpeed will default this value to a value greater than 1.

The corresponding HyperBac operation would be:

BACKUP DATABASE myVLDB TO DISK = 'X:\myVLDB.HBC'

Time differences may be seen between the above two operations based upon many factors, although the HyperBac statement was issued by SQL Server "as is" and the LiteSpeed statement was not.

The time differences can be compensated by several methods, making HyperBac as fast and in many cases faster than LiteSpeed, all of which are implemented in various ways in the background by these other vendors:

Performance Tuning Methods

Run the backup across multiple threads

To match or better the performance available from the VDI based backup products you may need to use the equivalent amount of threads with the HyperBac backup process. This is done by backing up to multiple physical files (or “striping”).

The optimum amount of files or stripes (threads used by the backup process) will vary between systems. It is not necessary to stripe across different volumes to attain the equivalent threading model as the VDI based backup products, however depending upon the disk/controller configuration there can be a further performance gain by doing so.

Each file/thread that is created will be able to read from a data file and write to a backup file independently of the other threads. It is possible the use too many threads which can diminish performance of the service by managing too many compressed files and so multiple scenarios should be tested to find the maximum performance benefit for the particular system.

Specify the MAXTRANSFERSIZE parameter

This was referenced in a previous blog Reduce SQL Server Database Restore Times in the context of RESTORE. But it can get result in a performance increase for BACKUP as well.

The MAXTRANSFERSIZE value is internally set by VDI based product vendors to maximize the block sizes for compression benefits. It specifies the size of memory blocks that SQL Server will use to buffer backup data for both the backup and restore operations. In some cases a performance gain can be obtained by specifying this value for HyperBac backup operations since a single 1MB operation can be handled more efficiently than a number of smaller 64KB operations. SQL Server does change this value from time to time depending on system resources available to SQL Server, and so this may not be necessary.

In all cases, you should try each of these in isolation (typically in the order provided in this blog) before experimenting with combinations of tuning parameters, as is you 'over tune' you can degrade performance. But used effectively these tuning parameters and tools will help you get the maximum performance and time savings benefits from HyperBac, delivering as good or better performance to any other solution available, but with extensibility and flexibility that is unsurpassed by any other vendors solution.

HyperBac Online Previewed at SQL Pass Community Summit 2008

The new technology from HyperBac, HyperBac Online, was demonstrated at the SQL Pass Community Summit 2008 in Seattle, Washington this week.HyperBac Online extends the current capabilities of HyperBac to allow DBAs to seamlessly run SQL Server databases on compressed MDF, NDF, LDF files.

In addition, HyperBac Online can be used to create compressed virtual hard disk files for VMware, Virtual Server/Virtual PC and other virtualization platforms and run the guest systems directly on these compressed files with no additional changes or configuration required to the virtualization platform.

HyperBac Online presents a significant savings opportunity for development, test an disaster recovery systems as well as read only databases or filegroups, decision support systems (DSS) or data warehouse applications.

HyperBac Online is currently available in beta and will be generally available in January 2009.

For more information on HyperBac Online, visit the HyperBac Online web page.

HyperBac Object Recovery with PowerShell

HyperBac’s Backup Explorer object level recovery provider allows DBAs to query SQL Server backup files in several ways without requiring these backup devices to be restored. Methods of accessing data using the Backup Explorer OLEDB provider include:

• “SELECT FROM Backup” using Linked Servers
• SSIS or DTS Import Wizard
• Custom developed application using ADO or ADO.NET
• PowerShell

This article specifically discusses using Windows Powershell to query, view or recover granular row level data from a full SQL Server backup or backup chain. The programmable nature of Backup Explorer allows it to be instantiated and accessed via PowerShell, a simple CmdLet is shown below which queries the Customer table in a full HyperBac compressed SQL Server backup (although the Backup Explorer provider can be used with a native uncompressed SQL Server 2000, 2005 or 2008 backup device as well):

$backupfile = "C:\MSSQL\Backup\hyperbac.hbc"
$backupfile = $backupfile + ",1+"
$connString = "Provider=hyperbac.oledbmtf;Data Source=$backupfile"
$conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$cmd = new-object System.Data.OleDb.OleDbCommand("SELECT * FROM Customer",$conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$dt | select -First 5 | Format-Table -AutoSize

The example output from PowerShell is shown below:



This is a basic example, more advanced operations are fully supported, such as WHERE or JOIN clauses on the SELECT statement as well as GROUP BY, ORDER BY, UNION and many other clauses. In addition using the Out-File instruction output can be redirected to a text file, for instance the last line could read:

$dt | select -First 5 | Format-Table -AutoSize | Out-File -FilePath C:\MSSQL\Backup\CustomerTableFromBackup.txt

Further customization and scripting within PowerShell is possible as well, including data comparison operations and much more.

For more information on Backup Explorer including "SELECT FROM Backup" query capabilities, visit the HyperBac SQL Server Backup Object Level Recovery web page.

Using ApexSQLLog with HyperBac

ApexSQL delivers a class leading transaction log reading solution for SQL Server - ApexSQLLog, which can read the online transaction log for auditing purposes or recover deleted rows or dropped objects from a transaction log backup.

This article describes the process of using the ApexSQLLog Recovery Wizard to connect to a HyperBac compressed and encrypted backup file to create “roll back” DML to recover rows which may have been inadvertently deleted during the period covered by the transaction log backup.

In this scenario, we are using a native database maintenance plan in SQL Server 2005 along with HyperBac to create compressed and encrypted transaction log backup files.



Consider the following timeline:

2217  :  All rows in the EmployeeAddress table are inadvertently deleted by a user:



(the user intended to specify a where clause to delete a specified row, but mistakenly executed the DELETE statement without highlighting the WHERE clause therefore deleting all rows in the table)

2230  :  Transaction Log Backup Plan Executes - AdventureWorks_backup_200810182230.tlog.hbe file created



Backup directory contents are shown below:



To integrate ApexSQLLog with HyperBac , you simply open the hyperbac.conf file (located in the HyperBac bin directory) with notepad. Find the parameter for IncludeReadProcessList and add the ApexSQLLog process to this list (which is delimited by ;;), for example:

IncludeReadProcessList=*|...;;ApexSQLLog.exe

The ... represent existing processes already in the IncludeReadProcessList

ApexSQLLog can now seamlessly read any HyperBac archive in any format (HBC – Compressed, HBE – Compressed/Encrypted, ZIP – HyperBac ZIP Compatible Archive, or any other HyperBac formats).

We can now access the AdventureWorks_backup_200810182230.tlog.hbe file using the ApexSQLLog Recovery Wizard and restore the table as shown below:

Step 1: Select the database pertaining to the recovery operation



Step 2: Select the desired recovery action - in this case we want to recover from a DELETE statement



Step 3: Specify that Relevant Data is Available



Step 4: Select the transaction log backup file from the list which covers the period of time in which the DELETE operation occured



Step 5: Choose the table(s) which rows were deleted from



Step 6: Select an output sql file for the Rollback DML (INSERT statements)



Click Finish



Finally all DML INSERT statements are created and can be executed in SQL Management Studio to recreate all rows which had been mistakenly deleted.



Further and more complex recovery scenarios are possible as well using ApexSQLLog against HyperBac compressed/encrypted backup devices. More examples will be covered off in future blog posts.

In summary, ApexSQLLog and its granular transaction auditing and recovery functionality is very much complementary to HyperBac ‘s capabilities to compress/encrypt backup operations in SQL Server and the capability of the Backup Explorer OLEDB provider to query full and differential backup files.

More information about ApexSQL and the ApexSQLLog product can be found at www.apexsql.com

For more information on HyperBac, visit the HyperBac for SQL Server web page

HyperBac Supported ZIP Decompression Utilities

HyperBac for SQL Server has the unique capability to allow you to backup direct to DEFLATE compressed storage format (more commonly referred to as ZIP format). The performance is on par with all other utilities on the market (LiteSpeed, Red Gate SQLBackup, Idera SQLSafe), and the files can be restored as compressed images on a system with HyperBac running without having to decompressed them first.

The key differentiator with other solutions available is that the HyperBac ZIP compatibility removes the long term reliance on a third party vendor to access your data should you require on any machine which does not have HyperBac installed and running. Such is not the case with products such as LiteSpeed which require you to use a Quest tool to either extract or restore these backup files.

The ZIP compatibility option also complements HyperBac’s unique capability to compress BCP, and flat file SSIS or DTS operations on the fly, whereas data can be exported directly to a ZIP file and shared with literally anyone in the world with no compatibility issues.

Several commonly used, widely accepted and industry standard ZIP decompression utilities supported by HyperBac are listed below:

WinRAR

WinRAR is an easy to use, lightweight, flexible data compression utility. Able to create native RAR format archives as well as offering decompression support for a large number of other archive formats, including ACE, BZ2, JAR, ISO, and ZIP.

HyperBac archives are supported on WinRAR versions 3.5 and above for unlimited file sizes. The current released version of WinRar is 3.8 and can be downloaded using the link below:

http://www.rarlab.com/download.htm

WinZip

WinZip, one of the better known and widely used compression utilities natively supports the PKZIP format but also offers support for other archive formats as well.

HyperBac archives are supported on WinZip versions 10 and above with no size limitations. The current version of WinZip is version 12.0 and can be downloaded from the link below:

http://www.winzip.com/prod_down.htm

7Zip

7-Zip is an open source file compression/decompression utility designed for Microsoft Windows. p7zip, the command-line version of 7-Zip, provides support to other platforms including GNU/Linux, BSD, Mac OS X and AmigaOS. It can be made compatible with DOS by using the HX-DOS extender to run the Windows command-line version.

7-Zip operates primarily with the LZMA algorithm (Lempel-Ziv-Markov chain-Algorithm), an improved implementation of the LZ77 compression algorithm. 7-Zip supports the 7z archive format, as well as supporting decompression of several other archive formats. In operation a user can use the command line, graphical user interface or Windows shell integration.

HyperBac archives or unlimited sizes are supported by 7Zip version 4 and above and can be downloaded from the link below:

http://www.7-zip.org/download.html


IZArc

IZArc (pronounced "easy-arc") is a proprietary file archiver for Microsoft Windows developed by Bulgarian programmer Ivan Zahariev. The program is freeware, but not open source. In addition to the most commonly used archive formats, like zip, rar, gzip, tar.gz, bzip2, and 7z, IZArc handles a large number of less common compression formats. IZArc is also able to convert archives into different formats.

HyperBac archives can be decompressed using IZArc version 3.81 and above with no size limitations. IZArc can be downloaded from:

http://www.izarc.org/download.html

Visit the HyperBac for SQL Server page for more information or to obtain a fully functional evaluation version.