hyperbac

LogShipping Made Easy Using HyperBac

noreply@blogger.com (Rajesh Kadbet) — Sat, 05 Sep 2009 16:41:00 +0000

This article shows you how to use HyperBac to compress and encrypt your backups when deploying SQL Server Log Shipping. Just follow the steps outlined below and you will have the additional benefits of encryption and compression for your log-shipped database backups. Configure SQL Server Log Shipping using Mgt Studio and then install HyperBac on both the Primary and the Secondary Log Shipping Servers. Note that even though there are two servers in the mix, you only need to purchase one license for your primary server. This is because HyperBac allows you to restore/test your backups on multiple servers using a free restore-only licenses for a small maintenance fee. So you can just use one of these licenses on the secondary server.

The list of steps to configure Log Shipping are:

Step 1) Fire up HyperBac Configuration Manager and go to the “Extensions” Tab.
Step 2) Then click on "Add" Button and browse to the location of your LogShipping Backups.
Step 3) Click on the "File Extension" Tab and choose "Include All Extensions".
Step 4) Click on "Compression" Tab and choose "Enable HyperBac Integrated Compression".
Step 5) Click on "Encryption" tab to enable encryption and the relevant encryption level.
Step 6) Click on the "Format" tab to choose Backup Format only.
Step 7) Click on "Affinity" tab if you decide to limit the backups to a particular processors .

You can repeat the steps outlined above for your full database backup locations as well as for your tlog backup locations (if they exist in different locations). You will thus end up with two new extension settings in the HyperBac configuration manager.

I have posted the relevant screen shots for the steps below:

Step 1) Fire up HyperBac Configuration Manager and go to the “Extensions” Tab

Step 2) Then click on "Add" Button and browse to the location of your LogShipping Backups. Note that if the location is specified using UNC in the Mgt Studio log shipping configuration then you will have to specify the same here (for eg. the same location can also be specified as \\hbtech1\TPCH_LogShippingBackupsDestination)

Step 3) Click on the "File Extension" Tab and choose "Include All Extensions"

Step 4) Click on "Compression" Tab and choose "Enable HyperBac Integrated Compression"

Step 5) Click on "Encryption" tab to enable encryption and the relevant encryption level.

Step 6) Click on the "Format" tab to choose Backup Format only.

Step 7) Click on "Affinity" tab if you decide to limit the backups to a particular processors .

After progressing through the above steps when you click on "OK" then your custom extension settings will display in the configuration manager as shown below:

[Note: Due to a known MSFT limitation compressed backups cannot exist on the same device as uncompressed backups. Hence hyperbac will not try to compress your backups if you append your logShipping backups to an existing uncompressed backup device. If you want to use compression in this case, you will need to reinit the backup using the 'with init' option]

Run Oracle Databases Compressed with HyperBac

noreply@blogger.com (javen) — Wed, 02 Sep 2009 05:25:00 +0000

HyperBac for Oracle versions 4.0 and above introduce online compression capabilities to complement existing RMAN backup, export (EXP) and export data pump (EXPDP) compression and encryption capabilities.
The steps below describe a simple scenario to backup a tablespace to a compressed file and then restore this compressed tablespace backup to a compressed datafile and run the Oracle tablespace on this datafile, reducing space and improving read IO performance.

Step 1: Perform a compressed RMAN tablespace backup of the EXAMPLE tablespace using HyperBac


C:\Users\Administrator>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Aug 30 21:03:22 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1223623114)

RMAN> backup tablespace EXAMPLE format 'C:\Backup\Oracle\EXAMPLE.HBC';

As seen from the following screenshots the tablespace backup file is compressed by over 70%.

File size comparison

Tablespace Backup File Meta Properties shown in HyperBac WinExtractor

Validate the HyperBac compressed tablespace backup using RMAN


C:\Users\Administrator>rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Sun Aug 30 21:03:22 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1223623114)

RMAN> restore tablespace EXAMPLE validate

Starting restore at 30-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=130 devtype=DISK

channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece C:\BACKUP\ORACLE\EXAMPLE.HBC
piece handle=C:\BACKUP\ORACLE\EXAMPLE.HBC tag=TAG20090830T210524
channel ORA_DISK_1: validation complete, elapsed time: 00:00:04
Finished restore at 30-AUG-09

Steps 2 and 3 are performed in SQLPLUS connected as SYSDBA

Step 2: Take the EXAMPLE tablespace offline


ALTER TABLESPACE EXAMPLE OFFLINE TEMPORARY;

Step 3: Change the file names for the active datafile(s)


SET NEWNAME FOR DATAFILE 'C:\oradata\EXAMPLE01.DBF' TO 'C:\oradata\EXAMPLE01.DBFX';

Step 4: Restore the HyperBac compressed tablespace backup to a compressed datafile


RMAN> RESTORE TABLESPACE EXAMPLE;

Step 5: Recover the tablespace


RMAN> RECOVER TABLESPACE EXAMPLE;

Step 6: Bring the tablespace online


SQL> ALTER TABLESPACE EXAMPLE ONLINE;

The comparative before and after results of datafile compression with HyperBac are shown below:

EXAMPLE Datafile Without Compression

EXAMPLE Datafile With HyperBac Compression

You can now perform routine database functions as normal including INSERT, UPDATE, DELETE and SELECT operations (as seen below). The underlying datafile remains in a compressed (and/or encrypted) state in this case saving over 70% in raw storage space.

Furthermore backups of this particular tablespace will be further accelerated as both reads and writes are compressed. This yields the absolute fastest backup possible using any method.

For more information see Oracle tablespace compression using HyperBac

Appending Compressed Backups to Native

noreply@blogger.com (Rajesh Kadbet) — Wed, 26 Aug 2009 15:15:00 +0000

If a user decides to add a file extension that is commonly used by SQLServer native backups (For eg *.bak,*.trn etc) to be tracked for compression within HyperBac configuration manager then you need to be aware of the following implementation details. At this stage if SQLServer native backups already exist on a backup device and you try to append to this backup device using the 'append' option, Hyperbac is designed to ignore compression and continue the backup in an uncompressed form. By default, Microsoft backups (in MTF Format) do not support compressed backups alongside an uncompressed backup on the same backup device. This way HyperBac does not fail your backup process. In case you want compression to kick in you can always use the 'with init' option to reinitialize this backup device or just point your backups to a new device that will hold all your compressed backups.

Disk counters to monitor for bottleneck issues

noreply@blogger.com (Rajesh Kadbet) — Fri, 21 Aug 2009 13:43:00 +0000

Sometimes trying for a better backup/restore throughput is an investigative process. A logical starting point for this investigation could be your disk subsystem. To aid this investigation you could monitor the following perfmon counters during your tests. They will help debug your server's IO Subsystems and throughput performance. I have also included a few definitions alongside the perfmon counters to help clarify their usage. The counters in question are:

SQL Server Backup Device Object: Device Throughput Bytes/sec:
This counter measures how much data is being backed up or restored. While there is no absolute value this counter should show, it should give you an idea of how fast your backups or restores are occurring. If this value appears to be small in relation to how fast you think your I/O system is, then perhaps there is some bottleneck preventing your backups or restores from occurring faster.

SQL Server Databases: Backup/Restore Throughput/sec: This counter can be monitored for the throughput of the entire database backup or restore operation.

Physical Disk: % Disk Time: As a rule of thumb, the % Disk Time counter should run less than 55%. If this counter exceeds 90% for continuous periods when performing backups or restores (over 10 minutes or so) then your SQL Server may be experiencing an I/O bottleneck. If you suspect a physical disk bottleneck, you may also want to monitor the% Disk Read Time counter and the % Disk Write Time counter in order to help determine if the I/O bottleneck is being mostly caused by reads or writes.

Physical Disk Object: Avg. Disk Queue Length: If the Avg. Disk Queue Length exceeds 2 for continuous periods when performing backups or restores (over 10 minutes or so) for each disk drive in an array, then you probably have an I/O bottleneck for that array. You will need to calculate this figure because Performance Monitor does not know how many physical drives are in arrays. Are the values that you see on your system consistently less than 2??

More info on SQL Server specific counters is available from microsoft at : http://msdn.microsoft.com/en-us/library/ms175903.aspx

HyperBac Silent Installation on 64-bit Systems

noreply@blogger.com (Rajesh Kadbet) — Sat, 15 Aug 2009 06:18:00 +0000

As HyperBac has a unique architecture which runs outside SQL Server at the Windows operating system level, therefore it does not require an install-time connection to a SQL Server to install. This gives it the distinct advantage of being able to be silently and remotely deployed by software distribution systems such as Group Policy, SMS, Altiris, ZenWorks and others with relative ease and simplicity as compared to alternative products.

The same HyperBac package can be used on multiple target platforms , using the MSIEXEC syntax below, the appropriate driver for the installation platform will be silently installed (eg x64 HyperBac driver on an x64 system, x86 HyperBac driver on an x86 system, etc):

Silent Installation on a 64-bit Operating System (x64):
msiexec /i HyperBacSQLServer-BackupExplorer.msi /qn PROCESSORTYPE="amd64" OSBITS="64"

Silent Installation on a 64-bit Itanium-based Operating System (ia64):
msiexec /i HyperBacSQLServer-BackupExplorer.msi /qn PROCESSORTYPE="ia64" OSBITS="64"

Silent Installation on a 32-bit Operating System:
msiexec /i HyperBacSQLServer-BackupExplorer.msi /qn PROCESSORTYPE="i386" OSBITS="32"

In all of the above cases the following actions are performed:

The appropriate HyperBac driver for the intended platform installed.
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.

The default configuration will allow for transparent compression and/or encryption of SQL Server backup and export operations (and online database compression in the case of HyperBac Online) as well as granular backup object recovery from physical backup devices or detached data files via TSQL, SSIS or DTS. Other customizations such as a custom configuration can be applied using MSI Transforms (MST files).

Furthermore as HyperBac’s configuration and licensing are file based, HyperBac can be easily configured and licensed remotely as well.

For more information, visit www.hyperbac.com

Extract SQLServer backups using Hyperbac Utility

noreply@blogger.com (Rajesh Kadbet) — Sat, 08 Aug 2009 00:50:00 +0000

As part of your disaster recovery process if you ever need to verify if your HyperBac backups can be successfully extracted to Microsoft SQL Server native format backups, here is a command line script that you can save as a .bat file and run on your test servers. This script will extract all your HyperBac backups located in a particular directory to a destination of your choice. The command-line "HyperUtil" utility that is bundled with the install is used in this extraction.

I have executed this on a windows 2003 server using the syntax:

for /f "tokens=*" %%a in ('dir /b *.zip') do "C:\Program Files\HyperBac\bin\HyperUtil.exe" -K"C:\Program Files\HyperBac\keys\AES_256.key" -E -S"C:\test\%tokens%%%a" -O"C:\NativeBackups\%tokens%%%a.bak"

The above syntax can be saved as a Native.bat file and will extract all the backup files with .zip extension from "c:\test" directory to the "c:\NativeBackups" location. Your SQL Server backups will now be available in the "NativeBackups" directory for restores.

Compress backups to particular directory

noreply@blogger.com (Rajesh Kadbet) — Mon, 03 Aug 2009 04:20:00 +0000

If you use maintenance plans to schedule your backup jobs then here is an easy way to compress and encrypt your backups at the same time. You can compress all the contents of the specific directory accessed by your maintenance plan backup jobs or be a little more selective and only compress files with a specific extension.

Ensure the following list of steps:

You can enable compression to the directory where the maintenance plan job writes the backup by adding this location to the HyperBac Configuration manager. To do this you will need to go to the Configuration manager --> 'Extensions' tab --> click on 'Add' --> and then fill in information in the 'Add/Modify Registration Wizard'.
In this add/Modify Registration Wizard you will need to enter the following values in the corresponding tabs:
In the "File Path" tab please choose the 'Specify Path' and point the file path to the same location where your maintenance plan writes your native backups. If there are multiple folders underneath this chosen folder and you need to have compression enabled to these as well, please choose the 'include sub directories' option as well.
In the "File Extension" tab make sure that the 'specify file extension' options is selected and type in *.BAK as the extension to compress.
In the "Compression" tab ensure that "Enable Hyperbac Integrated Compression" is checked.
Also ensure that the "format" tab has "Backup format only" as the chosen option.

Executing the above steps should now turn all your future maintenance plan backups writing to this directory to successfully use Hyperbac compression.

In addition to the above, if you desire Encryption you can choose it from the "Encryption" tab in the add/modify registration wizard.

Compress SQLServer Datafiles Using HyperBac Online

noreply@blogger.com (Rajesh Kadbet) — Sat, 01 Aug 2009 02:43:00 +0000

This article will introduce users to HyperBac Online and how to host compressed SQL Server databases using HyperBac Online extensions. Using this unique compression technology a typical SQLServer environment would be miles ahead of the game in conserving space, especially when used with their larger databases. This is because you essentially end up running your databases using a fraction of the space and a much smaller disk footprint. And the good news is that this unique technology can be selectively applied to specific databases within your SQL Server Instance.

To give you an insight into how disk space utilization can be optimized, I have used a 5GB database to demonstrate the 'compressed database files' concept and how these files behave when a user does a massive 2 million rows insert into the database. The database has been intentionally left in a 'Full' recovery mode so that all inserts are reflected within the log file. While you would expect the log file to expand and grow by many multiples of it's original size, the final size when using compression is still quite a surprise. In fact it ends up being a fraction of what would result in a native SQL Server environment under a similar test.

A 5GB Database with 4.8GB data file and a 2MB log file (Fig.1) can be effectively brought online as shown in Figure 2 as a 426MB compressed data file and a 38KB log file. After inserting 2 million rows the same online data and log files expand out to 509MB data file and a 843MB log file as shown in figure 3 below. This translates to a total data footprint of only 1GB on your drives.

A similar 2 million rows when inserted into a 5GB SQL Server database would cause the data and log files to expand to 6.4GB and 12GB as shown in Figure 4 below. That's a total database footprint of around 18GB.

As shown in Figure 5 and 6, both these databases when accessed through SQL Mgt studio will be display their size as 18GB, but the disk footprint of the Hyperbac Online database in reality is just 1GB.

The relevant snapshots from my tests are below:

Figure 1 - A Native 5GB Database backup when restored to BG_LogUnShrinkableNative as a SQL Server database and brought online has it's data and log file sized at 4.8GB and 2MB.

Figure 2 - The same 5GB Database backup when restored to BG_LogUnShrinkableTest as a Hyperbac Online Database (with .mdfx,.ldfx extensions) gets sized to 426MB and 38KB respectively

Figure 3 - Inserting 2 million rows into HyperBac Online database BG_LogUnShrinkableTest causes the data and log files to grow to 509MB and 843MB.

Figure 4 - Inserting the same 2 million rows into native database BG_LogUnShrinkableNative expands it's data and log files to 6.4GB and 12.2GB

Figure 5 - The compressed data and log files from Figure 3 of database BG_LogUnShrinkableTest show up as 6.2GB and 11.9GB through Mgt studio.

Figure 6 - The Database size for HyperBac online database BG_LogUnShrinkableTest as seen through Mgt studio is 18GB.

Figure 7 - The data and log files of database BG_LogUnShrinkableNative show up as 6.2GB and 11.9GB through Mgt studio.

Figure 8 - The Database size for the native database BG_LogUnShrinkableNative as seen through Mgt studio is 18GB.

For more information visit Online SQL Server Database Compression using 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.