Because SQL Hyperbac works at the Windows I/O level, it can be configured to work not just with the database files, but also with the files for BCP, DTS, SSIS and SQL Trace/Profiler. This means that many ETL processes can be done faster, more securely and using less disk space than before

I wanted to start this article off with “Data is like underwear…”, a cheap gimmick to encourage the reader to keep going, so they could find out whether that statement could possibly be true, or whether I’d lost my mind in my SQL DBA dotage. Fortunately, I was smart enough to side-step that initial, potentially offensive thought and will start the article like this: Data and underwear have several things in common, the most important of which is that they can both be compressed. I realized this similarity on a recent two-day trip where I decided it would be cheaper and faster to just bring one bag with me as a carry-on. No checked bag fees and I could walk straight past my fellow passengers as they slump in desperation by the baggage claim carousel. My roll-aboard bag was stuffed to near capacity with all the accoutrements of a traveling IT professional, but it worked beautifully. As we know, data, like underwear, often spends its time in transit: surely it too would benefit its owner if it were compressed. It would fly faster and, ultimately, more cheaply to its destination. Red Gate’s SQL HyperBac provides an easy way to compress data from SQL Server with tools you already have and most likely use frequently. I am going to show you two of them in this article.

Note: SQL HyperBac does not compress underwear.

You may already be aware of SQL HyperBac’s ability to compress SQL Server backup files up to 95% or more. But you may not know that SQL HyperBac can also be used to compress data directly as it is exported from SQL Server via standard export tools like an SSIS package or BCP. In my experience, data compression for data file extracts that are part of an ETL process is usually an additional step, adding time to the process and one more moving part to administer. The same can be said for encryption of file extracts, which SQL HyperBac also supports simultaneously with the compression process.

To demonstrate using SQL HyperBac with SSIS and BCP we need data and lots of it. Of course, the amount of data we are talking about exporting or importing is relative to your individual experience. I will be using just over a million rows of data but it’s not uncommon to work with tens or hundreds of millions of rows. The sample data is taken from a table which has been gathering SQL Server connection information for me for many months now.

In this sample database I have loaded 1035818 rows into the SQL_Conn table. To get a benchmark of space utilization, and the time it would take to export that many rows without the benefit of compression, let’s walk through the Export Wizard in SQL Server Management Studio. Figure 1 shows the Wizard, which can be launched in SSMS by right clicking the database, selecting Tasks and choosing “Export Data…”. You can see that I’m choosing to use SQL Server Native Client 10.0 as the Data Source on the (local) SQL instance and will be connecting the DBA_Rep database.

Full article...