hyperbac

Mirrored SQL Backups with Compression Using HyperBac

noreply@blogger.com (javen) — Thu, 07 May 2009 03:09:00 +0000

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

noreply@blogger.com (javen) — Fri, 24 Apr 2009 01:25:00 +0000

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

noreply@blogger.com (javen) — Thu, 11 Dec 2008 22:36:00 +0000

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

noreply@blogger.com (javen) — Sun, 23 Nov 2008 00:54:00 +0000

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

noreply@blogger.com (javen) — Tue, 11 Nov 2008 19:38:00 +0000

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

noreply@blogger.com (javen) — Sat, 18 Oct 2008 06:21:00 +0000

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

noreply@blogger.com (javen) — Fri, 10 Oct 2008 06:11:00 +0000

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.

Query SQL Backup Chain using TSQL

noreply@blogger.com (javen) — Tue, 02 Sep 2008 04:50:00 +0000

In a previous article, we described how to retrieve a table from a SQL Server backup chain using the SSIS Import Wizard - including a full backup and differential backup(s) using the HyperBac Backup Explorer object recovery OLEDB provider. This article uses the Backup Explorer provider to query the SQL Server backup chain using TSQL.

Consider the following scenario:

--Perform Full Backup
BACKUP DATABASE DynamicsGP
TO DISK = 'C:\MSSQL\Backup\DynamicsGP_FullBackup.hbc'
/* HyperBac Used for SQL Server Backup Compression in this example, however the Backup Explorer provider supports native SQL Server 2000, SQL Server 2005 and SQL Server 2008 backups as well */

Then, create a new table after the full backup was taken:

--Create new table in the Database
SELECT * INTO DynamicsGP..NEW_GL10001
FROM DynamicsGP..GL10001

Now perform a differential backup as follows:

--Perform Compressed Differential Backup
BACKUP DATABASE DynamicsGP
TO DISK = 'C:\MSSQL\Backup\DynamicsGP_DiffBackup.hbc'
WITH DIFFERENTIAL

Now create a Linked Server using sp_addlinkedserver in a TSQL query:

EXEC master..sp_addlinkedserver
@server = 'DYNAMICS_BACKUP_CHAIN',
@srvproduct = '',
@provider = 'HyperBac.oledbmtf',
@datasrc = 'C:\MSSQL\Backup\DynamicsGP_FullBackup.hbc,1;C:\MSSQL\Backup\DynamicsGP_DiffBackup.hbc,1'

NOTE: The syntax for the @datasrc parameter is:

'{full_path_to_backup_file_1},n;{full_path_to_backup_file_2},n;….'

Where n = the number of the backup set within the backup device (to support appended backup sets)

Now you can query the SQL Server backup chain, to SELECT and view data from the backup device(s) as follows:

--Query Dynamics Backup Chain to retrieve list of tables from the backup device
SELECT * FROM DYNAMICS_BACKUP_CHAIN...sysschobjs;

or

SELECT * FROM DYNAMICS_BACKUP_CHAIN...sysobjects;

NOTE: The Backup Explorer provider exposes physical base tables only, the exceptions are sysobjects, syscolumns, sysindexes. Although these are actually logical views in SQL Server 2005 and not physical entities, the provider has built in aliases for these views.

SELECT row level data from a table in the backup as follows:

--Query Dynamics Backup Chain to retrieve row(s) from new table
SELECT * FROM DYNAMICS_BACKUP_CHAIN...NEW_GL10001
WHERE BACHNUMB = 'CMXFR00000001';

You can also use INSERT INTO or SELECT INTO to recover data from the SQL Server backup chain into the original database or a new database as shown below:

SELECT * INTO NewTable
FROM DYNAMICS_BACKUP_CHAIN...GL10001
WHERE DSCRIPTN = 'Bank Transfer Entry';

INSERT INTO NewTable
SELECT *
FROM DYNAMICS_BACKUP_CHAIN...GL10001
WHERE JRNENTRY = 1543;

Visit HyperBac for SQL Server for a free trial or for more information about SQL Server Backup Object Recovery or SQL Server Backup Compression.

Recover SQL Data from a Backup Chain Using SSIS

noreply@blogger.com (javen) — Mon, 01 Sep 2008 05:09:00 +0000

This article covers SQL Server backup object recovery from a backup chain (including a Full backup and n Differential backups) using the HyperBac Backup Explorer OLEDB provider using the SQL Server Integration Services Import Wizard (SSIS).

Examples of using the Backup Explorer provider to SELECT data from a SQL Server backup chain using TSQL are discussed in the Query a SQL Server Backup Chain using Backup Explorer article.

The steps below describe the process of recovering granular data from a compressed SQL backup chain using the provider:

Step 1 – Perform a full backup of the database:

--Perform Compressed Full Backup of Dynamics Database
BACKUP DATABASE DynamicsGP
TO DISK = 'C:\MSSQL\Backup\DynamicsGP_FullBackup.hbc'

NOTE: The example provided uses HyperBac for integrated compression of the backups, but the HyperBac Backup Explorer OLEDB provider can be used with native, uncompressed SQL Backups as well.

Step 2 – Create a new table after the full backup:

--Create new table in MS Dynamics GP Database
SELECT * INTO DynamicsGP..NEW_GL10001
FROM DynamicsGP..GL10001

Step 3 – Perform a DIFFERENTIAL backup of the database:

--Perform Compressed Differential Backup of Dynamics Database
BACKUP DATABASE DynamicsGP
TO DISK = 'C:\MSSQL\Backup\DynamicsGP_DiffBackup.hbc'
WITH DIFFERENTIAL

Step 4 – Start the SSIS Import Wizard:

Select the Properties Button for the Backup Explorer Provider

Browse for the Full SQL Server Backup File and Click the “Add >” button:

Now browse for and add each subsequent Differential SQL Server Backup file:

Step 5 – Continue the SSIS Import Wizard to select the table to recover from the SQL backup chain:

NOTE ObjectRecoveryDB is an arbitrary destination for the recovered table, this could be the existing database or any other database

From this dialog you can “Preview” the data, which is a preview of the data from the table which is to be recovered from the SQL Server backup chain.

Step 5 – Complete the SSIS Import Wizard to recover the table from the SQL backup chain into a new database:

This is a very simple example, much more complex scenarios are capable using HyperBac’s unique Backup Explorer object recovery provider. Visit HyperBac for SQL Server to download a free trial version.

Trace File Compression Using Profiler

noreply@blogger.com (javen) — Tue, 22 Jul 2008 05:23:00 +0000

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

noreply@blogger.com (javen) — Sun, 06 Jul 2008 00:22:00 +0000

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

noreply@blogger.com (javen) — Mon, 30 Jun 2008 09:04:00 +0000

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

noreply@blogger.com (javen) — Thu, 19 Jun 2008 00:21:00 +0000

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

noreply@blogger.com (javen) — Thu, 08 May 2008 04:44:00 +0000

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

noreply@blogger.com (javen) — Sun, 13 Apr 2008 20:25:00 +0000

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

noreply@blogger.com (Mohammed Siam) — Mon, 07 Apr 2008 14:50:00 +0000

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

SQL Server 2005 Base Tables

noreply@blogger.com (javen) — Sat, 08 Mar 2008 07:02:00 +0000

Good reference article on the SQL Server 2005 System Base Tables. These tables are made available in read only mode using the HyperBac Backup Explorer (Backup Object Recovery) OLEDB Provider with via a Linked Server, SSIS or DTS connection. These tables can be used to recover DDL for Stored Procs, Views, UDFs and other objects from backup devices, as well as being able to recover meta data.

No Extractor Required

noreply@blogger.com (javen) — Thu, 06 Mar 2008 21:16:00 +0000

One of the issues with most third party database backup compression products for SQL Server (SQL Backup, SQL Safe, LiteSpeed, etc.) is that each of these products will backup to a completely proprietary data store. For example, if you were to perform a SQL Server backup using LiteSpeed, you could only get the data back from its proprietary compressed format in one of two ways:

Use a licensed copy of LiteSpeed to restore the backup

Use the vendor provided extraction utility

Both of these cases create a long term depenence upon the third party ISV to recover what is essentially yours (your data). Even options such as 'double click' restore, have a reliance on the vendor to support the application and the data is still in a proprietary, closed format.

HyperBac (founded by several of the founding developers of LiteSpeed) introduces an innovative solution to this issue with the ZIP compatible output format option. Using HyperBac, you can perform a compressed backup with speed and compression on par with all other products on the market with a key difference. The compressed data is stored in a non-proprietary, RFC-compliant, DEFLATE format (colloquially reffered to as a ZIP file). This file can be restored directly by HyperBac (additionally 'restore only' systems are licensed royalty free) but can also be extracted back to a native SQL Server backup format (MTF), using any widely available decompression utility supporting this industry standard format (WinZip, WinRAR, PKZIP, etc). An example use case is provided below:

/***
Perform a Native SQL Server backup to an output file or files with a ZIP file extension with the HyperBac Control Service running on the SQL Server
***/
BACKUP DATABASE [AdventureWorks]
TO DISK =
'C:\Backup\AdventureWorks_HyperBac_Backup.ZIP' WITH INIT

Compare the output file size with that of a uncompressed SQL Server backup below, you can see that HyperBac achieved 80% integrated compression, moreover the backup was twice as fast as the uncompressed backup, compression and performance figures on par with any of the major SQL Server backup software products on the market, however the file itself is simply a ZIP compatible archive.


C:\Backup>dir

 Volume in drive C has no label.
 Volume Serial Number is 1CC3-BDE4

 Directory of C:\Backup

07-Mar-08  09:57 AM        40,041,984 AdventureWorks_HyperBac_Backup.ZIP
07-Mar-08  09:57 AM       172,050,944 AdventureWorks_Native_Backup.BAK

As mentioned previously, with the HyperBac Control Service installed on the SQL Server the backup file (or files) can be restored directly as compressed images using standard syntax (no extended stored procs or anything installed within the SQL Server instance itself) as seen below:

/***
Perform a Native SQL Server restore from the HyperBac comrpessed backup file or files with the HyperBac Control Service running on the SQL Server
***/
RESTORE DATABASE [AdventureWorks]
FROM DISK =
'C:\Backup\AdventureWorks_HyperBac_Backup.ZIP'

Should you be in a situation where HyperBac (or the royalty free HyperBac graphical or command line extraction utils are not available to you for instance exchanging data with an external partner), below we demonstrate using the Free PKWare ZIP Reader utility to extract the file back to a native format:


C:\Backup>dir

 Volume in drive C has no label.
 Volume Serial Number is 1CC3-BDE4

 Directory of C:\Backup

07-Mar-08  09:57 AM       172,050,944 AdventureWorks_Extracted_Backup.BAK
07-Mar-08  09:57 AM       172,050,944 AdventureWorks_HyperBac_Backup.BAK
07-Mar-08  09:57 AM        40,041,984 AdventureWorks_HyperBac_Backup.ZIP

Customers are under more and more pressure to retain backup data for longer periods of time (up to 70 years in some cases). The world will have changed substantially in several years and there is security and peace of mind in knowing that the data you retained will be accessible into the future without having to rely on a third party ISV (who may or may not even be around or may have 'sunset-ed' the backup product at the time).

More information or a fully functional eval is available at hyperbac.com/sqlserver

History of SQL Server Backup Compression 2001-2007

noreply@blogger.com (Mohammed Siam) — Thu, 28 Feb 2008 04:37:00 +0000

A good chronology of SQL Server backup compression from SQL LiteSpeed to HyperBac to SQL Server 2008 and beyond.

http://databasechronicles.wordpress.com/2008/02/19/history-of-backup-compression-for-sql-server-part-i/

Oracle Backup Encryption in 9i and 10g

noreply@blogger.com (Mohammed Siam) — Sat, 02 Feb 2008 00:12:00 +0000

This article discusses a simple approach to achieving data security and encryption for Oracle backup and export files using HyperBac, unique technology invented by the original developers of the LiteSpeed (Quest) backup software product.

Assuming HyperBac is installed on your Oracle system with the HyperBac service/daemon running with a default configuration, integrated AES-256 bit certificate based data encryption for RMAN disk based backupsets and image copies as well as EXP and EXPDP operations can be achieved by simply specifying a HBE file extension for the output backup or export file or files.

For example, if you were performing an RMAN backupset as follows:

RMAN> BACKUP DATABASE FORMAT '%U';

you could simply add a HBE extension to this operation:

RMAN> BACKUP DATABASE FORMAT '%U.HBE';

The result of this operation is as follows:

The backup piece is encrypted with AES-256 bit encryption, requiring the certificate stored in the Program Files\HyperBac\keys or etc/hyperbac/keys directory to be present to seamlessly restore the backup or revert the backup to native uncompressed, decrypted format
The file size for the operation is reduced by 75 - 90% using integrated compression
The backup operation will typically be anywhere from 2 to 5 times faster than an operation not using HyperBac

Similarly, encryption operations can be performed on logical operations such as EXP (Oracle Export) or EXPDP (Export Data Pump) using the same HBE extension.

HyperBac is also available for SQL Server and other database platforms, more information on HyperBac for Oracle is available from hyperbac.com/oracle.

SQL Server Trace File Compression

noreply@blogger.com (javen) — Wed, 23 Jan 2008 21:20:00 +0000

It is sometimes necessary to trace SQL Server user activity for extended periods of time to diagnose SQL Server or application performance issues. Often these files (especially on a high transaction volume or volatile system) will consume an excessive amount of disk space or worse network bandwidth to copy these files to another system or vendor for analysis.

Using HyperBac, you can compress these files as they are written out by a server side trace (or SQL Profiler). Moreover you can compress these direct to a ZIP compatible format. You can also read these compressed file directly by an application such as SQL Profiler on a system with the HyperBac service installed and running.

This is very simple to configure, once configured it is a seamless process to write and read the compressed trace files. The steps are outlined below:

1) Using the HyperBac Configuration Manager, create a new extension using the 'Add' button on the 'Extensions' tab. Specify a path for where you will be writing the trace files to, in our case C:\TraceOutputDir:

2) On the Next tab ('File Extension'), select the 'Include All Extensions' radio button as follows

3) On the 'Compression' tab, select 'Enable Compression', in this case we have also specified 'Enable ZIP Compatible Output Format', which will enable to trace files to be opened and extracted by applications such as WinZip or WinRAR

4) Be sure to Save this configuration in the Configuration Manager:

5) Configure and run your server side traces as normal (using sp_trace_create, sp_trace_setevent, sp_trace_setstatus

The files created by the server side trace operation will be compressed on the fly (as they are written by SQL Server) into a ZIP compatible file (using minimal CPU resources).

You can prove that the files are compressed by opening them with an application such as WinZip or WinRAR.

6) To transparently read the trace files back from SQL Profiler, you simply need to manually register the Profiler process in the HyperBac Configuration File (hyperbac.conf) as follows:

Open the %PROGRAMFILES%\HyperBac\bin\hyperbac.conf file using Notepad, add the following line to the bottom of the file:

IncludeReadProcessList=*|profiler.exe;;profiler90.exe

profiler90.exe is required for SQL Server 2005 systems

Thats it! You can now seamlessly write and read SQL Server trace files, saving countless disk storage space and network bandwidth.

More information on HyperBac for SQL Server is available from hyperbac.com/sqlserver

Review of HyperBac on DBASupport.com

noreply@blogger.com (javen) — Thu, 30 Aug 2007 08:52:00 +0000

Jim Czuprynski, a senior Oracle trainer and writer for DBASupport.com, reviews HyperBac for Oracle. Includes examples of using HyperBac to perform compressed, encrypted RMAN Image Copy and Export Data Pump operations on Oracle 10g/Linux. Read the article here

Transparent SQL Server Backup Encryption

noreply@blogger.com (javen) — Fri, 01 Jun 2007 20:19:00 +0000

Data encryption requirements are becoming more and more of a reality for DBA's with ever increasing statutory requirements such as HIPAA, GLB, PCI and others. SQL Server backup data is no exception; in fact this is often the weakest link in the data security chain.

IT Administrators often make significant cost and time investments in perimeter security, intrusion detection, firewalls, spyware, malware and anti virus protection and unwillingly let their most precious and sensitive asset (corporate data) walk out the door in plain text. Consider the following scenario:

A backup is done of a Customer Management System
The tape backup of the database is routinely sent to an off site location by an independent courier

This is not an uncommon scenario, in fact this happens hundreds of thousands of times a day in organizations around the world.

Let's consider what is going out the door (in plain text). Below is an excerpt from a sample AdventureWorks database showing the CreditCard table:

S u p e r i o r C a r d 3 3 3 3 3 5 8 8 0 5 5 8 7 5 0

Surprising? This is a credit card number in Unicode, quite easily distinguished. This potentially crippling data just left the door in plain text to a complete stranger. This has far reaching potential implications.

Also don’t be mislead, there is a distinct difference between 'encoding' and 'encrypting', having the data in a compressed format by itself is only encoded not encrypted and can be easily reverse engineered (remember the people doing this are exceptionally clever!)

The solution is to apply transparent backup encryption (TBE) using HyperBac. HyperBac will seamlessly compress and encrypt the data as it is being backed up, so the images on disk and tape will be inherently encrypted. Below is an excerpt of a backup of the same database using HyperBac HBE (compressed, encrypted) format:

fñ·oŽêÚ…!\Pmú§øêÊÑËú€jkä‰göXt ~ÿv^PÆ—U§ü Ð,>}Ò

Try to make heads or tails of this...

Moreover, HyperBac (unlike all other third party backup products for SQL Server on the market) uses certificate based AES data encryption as opposed to secret key (or password) based encryption.

Password based encryption relies on humans to generate (and remember) a strong password. Quite often users will have a 256-bit encrypted backup file and specify 'Password' or some other English word as the password. This is an inherent insecurity. Certificate based encryption on the other hand, generates a unique, non-reproducible key file which must be present or supplied to access the data in the data file (be it for a restore operation or to extract the data to a native backup file.

HyperBac is currently the only third party SQL Server backup software vendor to offer encryption as an option at no extra cost (eg not in an 'Enterprise Edition', etc) and to charge a premium for this capability.

To get more information or to obtain a full functional evaluation copy of HyperBac for SQL Server (or any other database platform), please visit the hyperbac.com web site.

SQL Server Backup Compression - A New Approach

noreply@blogger.com (Mohammed Siam) — Mon, 15 Jan 2007 23:42:00 +0000

Today a new alternative approach to the third party compressed backup software market for SQL Server has been released.

HyperBac, developed by the initial developers of LiteSpeed (prior to the Quest Software acquisition) , has extended support to the Microsoft SQL Server platform (SQL Server 2000 and SQL Server 2005).

HyperBac for SQL Server takes a completely new and unique approach to SQL Server backup. Existing products including LiteSpeed, SQLSafe by Idera, SQLBackup by Red Gate use a VDI architecture. This architecture requires extended stored procedures (xp's) or proprietary third party GUI interfaces to operate and also limits integrated compression to physical backup operations only (no etl or data transformation processes). Unlike VDI based technologies, HyperBac uses a file system filter based technology, which offers several architectural benefits including;

Uses native BACKUP and RESTORE statements (no xp's or 3d party GUIs required)
Extends compression to other operations such as DTS, BCP, SSIS, SQL Trace Files and more
ZIP/RAR file format compatibility - with performance equivalent or better than other VDI based products on the market - eliminating long term dependence on third party ISVs for extraction utils
Certificate based AES data encryption (at no extra cost) - as opposed to existing technologies which implement password or secret key encryption only
Comparable or better compression and performance to LiteSpeed, SQLSafe, SQLBackup, etc
Runs outside the SQL Server memory space (therefore not architecturally subject to many of the issues other products may be subject to)

HyperBac is also available for Oracle, Sybase, MySQL and DB/2, more information is available at hyperbac.com.

Backup Compression in Oracle 9i

noreply@blogger.com (javen) — Thu, 28 Sep 2006 20:15:00 +0000

This article discusses how to perform database backups in Oracle 9i with integrated compression. Oracle 9i does not provide this capability natively however on-the-fly high performance binary compression can be obtained with Oracle 9i using HyperBac's unique filter technology.

Once you have performed a default installation of HyperBac for Oracle on your Oracle 9i database system, you can perform compressed Oracle backups by simply specifying a registered HyperBac file extension in the FORMAT specifier for the RMAN backup command, by default .HBC will yield a compressed backupset using RMAN with the HyperBac Control Service (or hyperbac daemon on Linux/Unix systems) running, as follows:


RMAN> backup database format 'c:\backup\%u.hbc';

Starting backup at 28-SEP-06
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00005 name=C:\ORACLE\ORADATA\ORCL\EXAMPLE01.DBF
input datafile fno=00010 name=C:\ORACLE\ORADATA\ORCL\XDB01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\ORCL\INDX01.DBF
input datafile fno=00009 name=C:\ORACLE\ORADATA\ORCL\USERS01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\ORCL\CWMLITE01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\ORCL\DRSYS01.DBF
input datafile fno=00007 name=C:\ORACLE\ORADATA\ORCL\ODM01.DBF
input datafile fno=00008 name=C:\ORACLE\ORADATA\ORCL\TOOLS01.DBF
channel ORA_DISK_1: starting piece 1 at 28-SEP-06
channel ORA_DISK_1: finished piece 1 at 28-SEP-06
piece handle=C:\BACKUP\1PJ9ROCB.HBC comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
Finished backup at 28-SEP-06

Figure 1 - Oracle 9i RMAN Backup with Compression
The results of this operation were as follows:

77% Integrated Compression/Backup Storage Reduction

55% Reduction in Backup Times as Compared to an Uncompressed Operation

A directory output demonstrating the compression benefits is below; the files with a HBC file extension are compressed while the files without an extension are not compressed.


C:\Backup>dir
 Volume in drive C has no label.
 Volume Serial Number is 1CC3-BDE4

 Directory of C:\Backup

09/28/2006  02:55 PM       568,298,112 1OJ9RO4S
09/28/2006  03:55 PM       127,538,816 1QJ9RRLI.HBC

Figure 2 - Oracle 9i Compression Figures

With the HyperBac service running, the compressed Oracle 9i backup can be restored directly using RMAN as follows:


RMAN> restore database validate;

Starting restore at 28-SEP-06

using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\BACKUP\1QJ9RRLI.HBC tag=TAG20080228T155425 params=NULL
channel ORA_DISK_1: validation complete
Finished restore at 28-SEP-06

Figure 3 - Restoring the Compressed Oracle 9i RMAN Backup

The HyperBac compressed RMAN backup piece can also be reverted back to a native uncompressed image at any stage using the HyperBac Extraction Utility (which is provided royalty free), an example usage of the extractor executable/binary is below:


C:\>extractor -S"C:\BACKUP\1QJ9RRLI.HBC" -D"C:\BACKUP\Native_Backup.DMP" -V
Original Name: BACKUP37J9R4SI.HBC
Compressed file size (MB): 127.5
Original file size (MB): 568.3
Compression percentage: 77.6%
Compression type: Shrink
Encryption type: None
Create date: Thursday, Sep 28, 2006
Create time: 09:25:40

Determining available destination bytes...4858.3(MB)
Extracted bytes: 568.3MB, Est. time remaining: 00:00:00

Figure 4 - HyperBac Extractor Usage

HyperBac for Oracle is available for Unix, Linux and Windows system, as well as other DBMS platforms such as SQL Server. More information or fully functional evaluation versions are available at hyperbac.com.