We’re doing a free all-day live virtual conference on how to use dynamic management views (DMVs) to do SQL Server performance tuning and troubleshooting. The whole thing will be broadcast live in 720p, and we’ll be taking questions via chat and Twitter. We’re going to have sessions at the beginner, intermediate, and advanced levels taught by:

• Kevin Kline (Blog – Twitter) – Microsoft MVP since 2004, author of SQL in a Nutshell, founding board member of the Professional Association for SQL Server, and all around good guy.
• Ari Weil (Blog – Twitter) – Product Manager for Quest’s performance products, and knows way more about waits, performance tuning, and SQL Server architecture than anybody should.
• Brent Ozar (Blog – Twitter) – your humble author. Okay, well, author anyway.

Register now and just for registering, you’ll be entered into a contest for free goodies!

Some time ago a component executable in the Performance Analysis client was temporarily flagged as a trojan by some leading anti-virus companies.  A virus definition file update two days after the initial identification resolved this issue.  In order to be 100% certain there were no potential threats, we fully recompiled the client and worked to ensure that subsequent updates to the virus definition files would not recreate the problem.  So, we are happy to report that the issue is resolved, and the updated Performance Analysis clients are available on quest.com for Oracle and for SQL Server.

Looking to use LiteSpeed for SQL Server for Log Shipping?  Check out this video, created by Nina Philippova our lead technical writer for LiteSpeed, for a step-by-step overview of configuring a Log Ship plan through the LiteSpeed UI.  Please provide any feedback that you may have, we’d love to hear it.  Thanks!

Quest introduced monitoring for SQL Server Analysis Services (SSAS) with Spotlight on SQL Server Enterprise 6.0 last November.  Since then we’ve gotten great feedback from new and existing customers on how Spotight gives them the confidence they need to keep tabs on every aspect of their SQL Server environment. 

We recently closed an important case with Microsoft support that resolves an issue where the WMI classes required to monitor default SSAS instances were missing on the host server.  What this means to you is if you want to monitor the SQL Server relational engine, Analysis Services, or just the Windows server hosting the solution,  you really should check out Spotlight on SQL Server Enterprise.  We’ve got you covered from Windows and SQL Server 2000 to the latest releases of 2008.

Microsoft SQL Server 2008 introduced data compression, a new feature that will compress your objects.  The theory is that it will cut space requirements inside the database, and a smaller database is easier to read off the hard drives.  Your IO time should decrease, and your CPU time shouldn’t be affected that much.

But I never hear about people using it.

So I’m asking you in a short survey: why aren’t you using it?

At Quest, we have a Pain of the Week webcast series that’s gotten a lot of great community feedback lately.  We spend the first 30-45 minutes talking about a SQL Server problem and how to solve it just with the native tools like SQL Server Management Studio and T-SQL scripts.  In the last 10-20 minutes, we talk about how a Quest product can solve it even easier.

You might not know, though, that we’ve been doing these webcasts for years, and you can watch any of our archives absolutely free of charge.  Here’s just a sampling of our free training sessions:

• “But It Worked On My Machine!” – Solving T-SQL Scaling Problems – with Brent Ozar and Ari Weil
• Generating and Optimizing SQL Server Query Plans – with Kevin Kline and Simon Sabin
• Getting Started with SQL Server Management Studio – with Brent Ozar and Michelle Ufford
• Hidden Treasures in SQL Server – with Brent Ozar and Tim Ford
• How to Be a Backup and Recovery Hero – with Brent Ozar
• Is Consolidation or Virtualization Right for You? – with Brent Ozar, Kevin Kline, and Ron Talmage
• Simplify SQL Server Management with DMVs – with Buck Woody, Brent Ozar, Kevin Kline, Louis Davidson, and Tim Ford
• Top 10 DMVs for Easier Server Monitoring – with Brent Ozar and Tim Ford
• When to Use CLR Code Versus T-SQL – with Ari Weil and Thomas LaRock

For more webcasts, check out the SQL Server webcast archive at Quest.

Continuing a string of posts on tips and tricks for LiteSpeed, I thought I would take an opportunity to discuss how LiteSpeed allows you to select databases to backup in a maintenance plan.  Whether or not to use maintenance plans to backup your SQL Servers is very much a matter of personal preference.  LiteSpeed has added some great functionality to make maintenance plans more configurable than what you get with SQL Server.

Consider the screen that allows you to select which databases to backup.  In SSMS 2008 (and 2005/2000) you can select to backup either all databases, all system DB’s, all user DB’s, or you can select a subset of your DB’s to backup with an associated plan.

Native Database Selector

There are two main challenges with manually selecting a subset of your databases.

1. Once you select a subset of your databases, any new database added to the system will not be automatically picked up by the maintenance plan.
2. The order in which the databases are backed up is not configurable.  The critically of your databases is not determined alphabetically, and if you have a maintenance plan run long, wouldn’t you want your most important databases backed up first?

LiteSpeed Maintenance Plans have addressed these two concerns.  In the database selector for a LiteSpeed maintenance plan, you have two options that you don’t get with native plans.

1. LiteSpeed lets you select databases for exclusion (not inclusion as in a native plan).  Essentially, you are telling LiteSpeed to backup all databases except the chosen databases.  This means that any newly created databases will be picked up by your plan, yet you can define databases that should never be backed up.
2. You can order the databases that you select.  By moving databases up and down you can determine which databases get backed up first.  I highly recommend that you let your maintenance plan tackle the most critical databases first so that should anything occur during your maintenance window, your most critical databases have a higher probability of completing successfully.

LiteSpeed Database Selector

In the example above, master and msdb are backed up first, followed by the Quest repositories, followed by the remainder of my databases.

As you can see, not only does LiteSpeed compress and/or encrypt your database backups, but also has functionality built in to assist with the management of your backups as well.  Plenty more tips and tricks to come.  If you have any questions about this or anything else, feel free to comment or visit the forums.

More than once I have had the need to recover the native backup file from within the compressed Litespeed backup file. Fortunately Quest provides an extractor utility in order to help with the process. Otherwise, you would be forced to do a restore followed by a native backup. If your database is of a considerable size then the time needed to accomplish that task could be prohibitive; it will always be simpler to do the extraction as opposed to a restore and a backup.

Extraction is very straightforward. First step is to open a command window and navigate to the directory where the utility is located, this is typically the C:\Program Files\Quest Software\LiteSpeed\SQL Server directory.

Next, issue the following command to extract the native backup files.  Yes, files. In fact you will have one file created for each thread that was used when Litespeed was taking the initial database backup. The command is roughly as follows:

extractor.exe -F [path to backup file taken by litepseed] -E [name of file you want to be created]

So, if you had a backup of AdventureWorks saved to C:\AdWks.bak, and you wanted to extract the native dump from that file and create new files in the same directory, you would run the following command:

extractor.exe -F c:\AdWks.bak -E c:\AdWks_ext.bak

This command results in the creation of multiple files. You can configure a path and filename here if you want to have the files stored somewhere other than the current directory.

OK, so what do you do with these files? Well, you restore them, of course, using either T-SQL or SSMS. One common reason for needing to have a native backup is for those rare times when you need to provide a vendor with a copy and that same vendor does not have a copy of Litespeed. And while the extraction utility makes it easy to provide the necessary files, you have no idea who is on the receiving end of the multiple files. I have lost count of the number of vendors I have sent the extracted files to and received an email back that either says “we don’t know what to do with these files” or “can you send us just one file instead?”

Sure, I can do that as well. Or, I could take a minute to teach you how to do it for yourself. Here goes:

Open up SSMS and all you need to do is point to the files. Just add in each file as shown below, and click OK. Then, select your restore point and click OK again. You should be fine, the database restore should be underway at this point. 

Now, if you prefer T-SQL, the equivalent syntax would be:

RESTORE DATABASE [AdventureWorks]
FROM  DISK = N’C:\AdWks_ext.bak2′,
DISK = N’C:\AdWks_ext.bak1′,
DISK = N’C:\AdWks_ext.bak0′
GO

It’s that simple.

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!!

Product documentation and product training are two areas where you (our customers) consistently press us to improve…and we have.

If you navigate to www.quest.com/foglight-performance-analysis-for-sql-server and click on Key Product Documents you’ll be taken to a page that lists all the documents you should need to get you from initial evaluation, to the demo/POC phase, and through implementation and use case training.

Also, a little while back we introduced some new micro workshops so you can get help with things like sizing your environment prior to installation, and with training on using Performance Analysis effectively once it’s installed without breaking the bank. Just navigate to www.quest.com/foglight-performance-analysis-for-sql-server and you’ll see a link to Find out about Technical Training.

Easy, right?  Want more?  We’re happy to help.  Contact your account representative, your favorite Quest SQL Server contact, or participate in one of the forum discussions and ask.