Tools & Utilities

SQL Server Backup Compression vs. Quest Litespeed Engine

As more and more functionality is built into products like SQL Server it’s always worthwhile reviewing third-party tools and utilities when you’re considering an upgrade to see (a) if they’re still required and (b) if the tools themselves need to be upgraded.  With the introduction of Backup Compression in SQL Server 2008 R2 Standard Edition you could begin to think that the future is grim for Quest’s backup compression software LiteSpeed so I thought I’d do some testing to see exactly how it stacks up against the native compression.

LiteSpeed Engine

I’ve been using LiteSpeed on and off for a few years now and it has always been a great tool but I’ve always found it a bit of a drag to have to use the GUI to administer and setup jobs however in January 2010 Quest launched the LiteSpeed Engine for SQL Server which allows you to administer jobs using the native SQL Server tools.  The LiteSpeed Engine acts as a driver and the configuration tool allows you to define a variety of configuration profiles based on file extension and from that point onwards you can use the Management Studio to setup backup jobs, maintenance plans, etc. and all you have to do is specify the file extension of the profile you wish to use.

The configuration tool allows you specify the compression level from 1 to 8, encryption level including various bit-length versions of RC2, RC4, 3DES and AES though as you’ll see later the overhead of adding the highest level (256-bit AES) isn’t that great so I’d always shoot for the maximum.

Benchmark Structure

The test is relatively unscientific since I used only one database but it was carried out systematically, the data comes from a transactional billing system which I chose as it has a mix of strucured tables and raw transactions and comes in at about 6.5GB so it wouldn’t take too long to test.  I used the following configurations…

Benchmark Results

On my test database the baseline SQL Server native compression reduced the 6.2GB database to 765MB (12.2% of the original size) and took less than half the time (43%), to achieve the same level of compression using LiteSpeed I had to use Level 2 which gave me 12.2% of the original size and 40% of the original duration.

At first this doesn’t look great for the third-party tool but the benefit of using a mature backup compression engine is the flexibility and LiteSpeed’s configurations allow you to tweak the performance to solve whatever problem you have in your environment whether that be the absolute size of the backup, the backup window time or a mixture of the two.

If it’s size you’re after then Level 8 really did seem to work wonders on my test DB bringing the size down to 5.6% of the original at only 352MB though it did take 2.6 times the original duration, if it’s the backup window you’re looking to reduce then the basic Level 1 did manage to improve on the native compression by taking 0nly 37% of the original duration whilst still compressing to 13% of the original size.  If like most people you’re looking to have your cake and eat it (i.e. reducing size and backup window) I’d suggest that Level 3 is the best compromise giving 10.9% of the original size at 77% of the original duration so you get some benefit in both areas, though Level 4 takes compression a bit further and still gave a slight time reduction.

Clearly, the real answer is testing and since I’m at the beginning of data warehousing project I’m not in the position to make any firm decisions but I think that even if you don’t run out and purchase it now LiteSpeed is a very valuable tool to have in your mental arsenal so that if you come up against backup size/window issues or you’re faced with older versions of SQL Server you’ve got a solution in mind already.  Quest have an odd policy of keeping pricing quite opaque but I believe that the full Enterprise version (including the LiteSpeed Engine) retails for around £1,800 ($2,800) which isn’t too bad if you need that level of flexibility.

SQL Server Native Compression

LiteSpeed Compression (No Encryption)

LiteSpeed Compression (With Encryption)

Google Release Command Line Tool: GoogleCL

Having been raised on the good old fashioned ZX Spectrum and introduced to PCs via MS-DOS I have something of a nostalgic fascination with command line interfaces, there’s something beautifully simplistic about using a good command line – like you’re talking to the machine directly.  I’m not just talking about using the pseudo DOS-shell that comes with NT or the ubiquitous Unix/Linux command line, it’s the more exotic examples that pique my interest which is why I’m quite excited about the new Google command line tool.

Essentially GoogleCL is a Python application that can be executed at the command-line to make calls to various Google APIs, it currently offers limited support for Blogger, Calendar, Contacts, Docs, Picasa and YouTube but I’m certain that Google will deliver more features in the future.  In terms of security, there’s a one-time authentication process for each application whereby the command-line tool launches a page in your default web browser to grant access for the GoogleCL tool.  To me, the most interesting examples that Google provide are those allowing content creation…

There may not be many obvious ties to the world of Business Intelligence here as GoogleCL is still in its infancy but for now at least you could perhaps drive scheduling through Google Calendar, maintain distribution lists in Google Contacts or automatically upload reports to Google Docs.  I’m quite sure the possibilities will expand over time though especially since some major Google products are currently not included (e.g. Search, Gmail) – I, for one, will be watching with great expectations.

How To Install GoogleCL on Mac OSX Snow Leopard

If you’ve not heard the buzz already Google have released a Command Line tool called GoogleCL, you can install in in Windows by following Isaac Truett’s guide to”Setup GoogleCL on WinXP“ but if you’re using a Mac and you’d like to install it and have a play here’s a few simple instructions…

1. Enable your Root login (instructions from Apple in KBHT1528).
2. Log in as Administrator (bear in mind your normal user shouldn’t have Admin rights).
3. Download and install Xcode.
4. Download and install MacPorts.
5. Open up Terminal.
6. Edit your ‘paths’ file: sudo vi /etc/paths
7. Add a new line (press ‘i’ then scroll to the bottom first): “/opt/local/bin“.
8. Save the file (press ESC, then type “wq!”).
9. Close Terminal and re-open.
10. Type: sudo port install googlecl, and press Enter (this takes a while).
11. Log off as the Administrator.
12. Log back in as yourself and test (see examples).

FTP MGET – Get Multiple Files Without Prompt

Every now and again I have to use FTP (and Secure FTP) from the command line, in fact it’s actually my preferred method as it keeps my knowledge of the syntax nice and sharp rather than relying on GUI clients (though if you do need a good free FTP, SFTP and FTPS application you should try FileZilla). 

Today I had to retrieve a set of log files from a supplier, we can use the MGET command to fetch multiple files using wildcards (e.g. *.csv) but the default behaviour of MGET is to ask the user to confirm that they want to download every file – not very convenient if you’re talking about tens of thousands of logs!  Instead of resting a book and a penlid on the ‘Y’ key (I’ve seen it done) you can turn off the interactive prompts simply by issuing the PROMPT command to toggle on/off the prompts. 

Additionally if you’d like your FTP responses to be less wordy you can use the VERBOSE command which will pare down responses to the minimum.

Extract Rows from Files using QGrep

Whether you work with enterprise Data Warehouses, departmental Data Marts or live operational OLTP systems there’s a good chance that at some point you’ll have a need to import or export data using flat files.  Flat files are great to read because they’re typically in common easy to understand formats such as CSV, the problem comes when you’ve got extremely large volumes of data that exceed your ability to handle them in Excel (e.g. over 65,536 rows for Excel 2003) or when you absolutely must preserve the initial file format.

I had a requirement recently to extract all rows relating to just one customer from an input file containing tens of thousands of rows and I had to leave the structure intact so that the existing SSIS loader would recognise the file.  Sadly the standard Windows command-line utilities are a bit lacking in this regard but you can download the Windows Server 2003 Resource Kit which among other things includes the QGrep tool.

QGrep is simply a Windows equivalent of the Grep command available in Linux and Unix, there is no GUI but if you’re doing something simple the syntax is fairly straight-forward (see SS64.com for more info), in my case I needed to extract all records from “input.csv” containing the CustomerID “476226235076″ and place them in “output.csv” so the command was…

qgrep “476226235076″ inputs.csv > output.csv

Useful SQL Colouring Tool

I guess it depends on your background but if you’re anything like me you’ll prefer to have your SQL coloured and formatted, I began writing SQL in Microsoft SQL Server’s Query Analyser which colours comments green, keywords blue, aggregate functions pink and string values in red – and it’s something I (almost) can’t live without.

It’s become a fairly common scheme for viewing SQL and years of being used to colours mean that reading too much monochrome SQL drives me crazy so when I started this blog I was a concerned about how off-putting it would be if I posted reams of plain old SQL, luckily a bit of Googling led to me finding Instant SQL Formatter.

Instant SQL Formatter allows you to format and colour a SQL statement and then provides you with the HTML that you can paste right into your blog, there are also lots of options to tweak the formatting based on how you’d like to see the SQL laid out.  Here’s some example SQL I wrote using the tool…

– TESTING COLOURED SQL QUERY
SELECT Species,
COUNT(DISTINCT AnimalID) AS NumberOfAnimals
FROM tblAnimal
WHERE Species IN (‘Bonobo’,‘Vervet’,‘Chimpanzee’)
AND IsHealthy = 1
GROUP BY Species
HAVING COUNT (DISTINCT AnimalID) > 1