One question that comes up quite a bit pertains to how you can handle errors generated by the LiteSpeed Extended Stored Procedures (XP’s) in your own custom scripts.  Scripting with XP’s is fairly straight forward, because they accept parameters just like any other stored proc, but how they handle errors is a bit different.  Unlike  standard SQL Statements that will populate @@error and can be handled with TRY/CATCH blocks, XP’s simply return an error code.  By capturing the value returned by an XP you can succesfully trap and code around many types of errors.  View the following block of code to show how this is done.  In this example, the drive I am trying to backup to does not exist, therefore the backup fails with error code 50003.

DECLARE @rc INT
EXEC @rc = master.dbo.xp_backup_database
  @DATABASE='master'
  ,@filename ='V:\backup\database.BKP';
SELECT @rc

I know some folks out there have done some pretty cool things scripting with LiteSpeed.  If anyone has any scripts they’d like to share, we’d love to take a look!!

Microsoft SharePoint content databases are notoriously difficult to compress.  Users upload Word documents, Excel spreadsheets, and PowerPoint presentations into the SharePoint sites, and these bulky files go straight into the SQL Server database.  They’re tough to compress, and the files just keep piling up as more users start using the company intranet.

Even worse, us database administrators are told that SharePoint is now mission-critical, and we have to make sure we back it up as fast and as often as possible.  How do we do it?  In this five-minute video, Brent Ozar shows how to use LiteSpeed’s new FastCompression to pull it off.

When an issue occurs during your backup and recovery process such as performance spikes, it is extremely beneficial to get as clear a picture as possible of what activities are occurring in your SQL Server environment.  LiteSpeed for SQL Server offers this clarity by logging these activities for immediate analysis and isolation of any bottlenecks.  This video takes you through the process of enabling logging through the LiteSpeed for SQL Server console and will cover four different areas within the product.

• Backup Wizard
• Restore Wizard
• Maintanance Plans
• Console Logging

Special thanks to April Bucher on the LiteSpeed QA team for putting this short video together. 

Say 'hello' to my little friend!

We’re very excited to introduce a new addition to LiteSpeed brand – we’ve released LiteSpeed Engine for SQL Server, which is now available with LiteSpeed for SQL Server Enterprise.  Let’s nail some questions that you may be having:

• What is LiteSpeed Engine for SQL Server?  It’s a very light weight, drop and go compression and encryption ‘engine’ for SQL Server.

• Why is that important?  By offering LiteSpeed Engine for SQL Server, customers who already have a backup and recovery strategy in place using their own scripts and processes can now get the compression and encryption that is offered by LiteSpeed – without changing their current backup processes.

• What’s the difference between LiteSpeed Engine and LiteSpeed Enterprise?  LiteSpeed for SQL Server Enterprise offers the full monty – a very comprehensive backup and recovery solution that provides not just compression and encryption, but also Maintenance Plan management, Object level recovery, Fast Compression, etc.  Now, LiteSpeed Engine for SQL Server, as stated in the previous bullet, simply offers the compression and encryption capabilities of LiteSpeed which fits into an already established backup and recovery process.

• Can you give a brief usecase for this?  LiteSpeed Engine provides much more flexibility and choice to you as to how to deploy LiteSpeed.  For example, on your ‘mission/business critical’ systems, the full implementation of a backup and recovery strategy offered by LiteSpeed Enterprise will be very valuable.  On those less critical systems, or pre-production environments, or those databases that already have a backup and recovery plan in place, LiteSpeed Engine will be the missing piece that gives compression and encryption.

• How do I get LiteSpeed Engine for SQL Server?  Currently, LiteSpeed Engine is available to evaluate by downloading from our LiteSpeed for SQL Server page on Quest.com.  Take a look – we’d love your feedback.

• Is there any other resource where we can see how LiteSpeed Engine for SQL Server will work?  Yes!  We have a brief video that provides an overview of LiteSpeed Engine for SQL Server and how to configure.

We’re really excited about our introduction of LiteSpeed Engine for SQL Server and are confident that the flexibility now available in how you decide LiteSpeed fits into your backup and recovery strategy will blow you away.

Quest’s new LiteSpeed Engine for SQL Server is a transparent way to compress and encrypt your Microsoft SQL Server backups – without changing your existing backup scripts or maintenance plans.  Instead of calling special stored procedures at backup time, just configure the Engine once and be done with it.  Learn how to set it and forget it in this video by Brent Ozar.

I read a post recently on The Grateful DBA that describes his analysis in testing different compression levels in LiteSpeed for SQL Server and their impact on both backup size and efficiency.  In his own words this is a “non-scientific, ground-level look at LiteSpeed’s compression ratios”, but I think this approach is a real world approach.  The post describes the pros and cons of higher vs. lower compression levels and where each is appropriate depending on the database environment.  There’s good food for thought in the blog entry and there’s  a pretty graph!  Mmm…food for thought and pretty graph….

Anyway, again, I just waned to repost the blog entry and invite discussion.  Here’s the link again:  http://troygallant.com/?p=155  -  thank you Grateful DBA.

Have a great weekend everybody and good luck getting your last minute Christmas  shopping out of the way.

When it absolutely, positively has to be backed up, don’t just rely on one backup file.  Quest LiteSpeed for SQL Server can write database backups simultaneously to two places.  This protects DBAs from other users accidentally deleting backup files, from crashed file servers, and from corrupted tapes.  Learn how to mirror your LiteSpeed backups in this video by Brent Ozar:

Need to send a SQL Server backup to someone, but it’s too big and you’re not sure if they use LiteSpeed?  Quest LiteSpeed for SQL Server can back up databases into a single executable file.  You can send that file to someone else or copy it to another server, and that other server doesn’t need LiteSpeed.  The person doing the restore doesn’t even have to understand how SQL Server works – LiteSpeed takes care of all the details.

In this video, Brent Ozar shows how both the backup and restore process works:

LiteSpeed’s Backup Analyzer can do more than just find the right backup compression and encryption settings like I discussed in an earlier LiteSpeed video tutorial.  It can also help pinpoint your SQL Server’s backup bottleneck, showing why you can’t back up your databases faster.

Here’s a screenshot of the Backup Analyzer’s report:

LiteSpeed Backup Analyzer

When you’re looking for the backup bottleneck, check out these columns:

• Read Speed – indicates how fast your data files can pull the database off disk and give it to the CPU.  This is the first step in the backup process – getting the uncompressed data in.  This number is dictated by the speed of the storage subsystem where your SQL Server data files live, and by the connection between your server and the data files.
• Throughput – indicates how fast your CPUs can process the data.  The more compression and encryption performed, the less data can move through here.  To improve this performance, use less compression or a weaker encryption method.  (Generally, though, this isn’t a bottleneck except on very old servers with slow CPUs.)
• Write Speed – indicates how fast your storage subsystem can write out the compressed data.  This is the last step in the backup process.  To improve this number, you can use faster drive arrays or stripe the data between multiple drives, and the Backup Analyzer can be used to test those scenarios too.

In my screenshot above, my lab server’s data drives are connected via iSCSI.  Notice that my read speeds are consistent across all of my tests.  That’s because I just flat out can’t read the data any faster than I’m reading it.  The only solution to make this server faster is to upgrade the connection between the data file drives and the server itself.

However, since I know I just can’t read that fast, that means I can use as much encryption and compression as I want without affecting performance.  In this situation, I might retest with stronger encryption methods and higher compression levels.  Even though I’m read-constrained, that doesn’t mean I can’t get benefits from using LiteSpeed’s compression.

If you’re confused about the output from Backup Analyzer, feel free to contact Quest support or leave us a comment below with the output from your tests.

Are you the kind of database administrator who likes to get under the hood and tweak settings for CPU affinity masking, throttling, transfer sizes, and more?  This video is for you.

Quest LiteSpeed for SQL Server has advanced configuration options that will let you finely tune how much power LiteSpeed uses, and on which processors.  Learn how in this video by Brent Ozar: