How do I figure out where I’m using LiteSpeed for SQL Server in my environment? It’s a question that comes up frequently from customers. And like most questions, the answer is “It depends.” Am I within compliance on my licensing? That’s the follow-up question. For that one, you'll need a list of all your LiteSpeed assets. Your sales rep will be more than happy to oblige, so give him or her a call, say hello, and let them know you need a complete asset list.
I'm Lucky because I Use a Central Repository
You are lucky. Pat yourself on the back for using one of LiteSpeed’s important features. The central repository is the nexus for LiteSpeed information. All LiteSpeed activity is pushed there from local installs to give you a complete picture of what's going on in your environment. If you're not using one now, I would recommend you consider the option in the future (it’s free after all). Not only is the central used to see which backups completed and failed across your environment, you can also use it to see where these backups are running.
Here are two queries:
The first returns the most recent Full backup for each database on each instance that occurred within the last year. If your backups haven’t run in longer than a year, the instance may no longer be active or you might not be running backups at all. If it’s the latter, you're in more trouble than you thought. You can adjust the time period as needed.
If you run this query from Toad for SQL Server, you can use the cool TOAD:GROUPBY command to automatically group the results.
-- This query can be run on a LiteSpeed Central Repository or can be run using
-- Toad for SQL Server Group Execute on local repositories
-- Returns the last LiteSpeed full backup for each database completed in the last year
-- TOAD:GROUPBY Server
CONVERT(VARCHAR(20), la.StartTime, 120) "LastBackupDate",
ld.ServerName = lv.ServerName
ld.Deleted = 0
la.DatabaseID = ld.DatabaseID
la.ServerName = lv.ServerName
AND la.ActivityID = (
SELECT TOP 1
la2.DatabaseID = ld.DatabaseID
la2.ActivityTypeID = 1
la2.ServerName = ld.ServerName
la.StatusTypeID = ls.StatusTypeID
la.StatusTypeID in (2,3) -- Completed or Failed
DATEDIFF(d, la.StartTime, GETDATE()) < 367
la.StartTime IS NOT NULL
The second query returns the most recent Full backup run on the instance. One row per instance. A much more simplified set of results, but it's missing the detail of which databases are being backed up with LiteSpeed.
-- Toad Group Execute on local repositories
-- Returns the last LiteSpeed full backup performed on each instance
CONVERT(VARCHAR(20), la.StartTime, 120) "LastBackupDate"
la2.ServerName = lv.ServerName
I'm Not So Lucky because I have no Central Repository, but I use Local Repositories
You are not as lucky as the first group. Getting the results is going to require more work and a feature like Toad’s Group Execute. I know Management Studio 2008/R2 has such a feature, but I find the one in Toad pretty powerful. If you only have Management Studio, check out this post on toadworld.com/platforms/sql-server.
If you use the same local repository database name for every instance, it simplifies things. Add the database name to the table names in the previous scripts and you're good to go. If your local LiteSpeed repository database names vary by instance, then you need Toad for SQL Server for this because its Group Execute feature has a database selection capability. It also allows you to save the queries with Group Execute selections intact for easier re-use. If you need to select databases with Toad, check off the local repository in each instance. It will take a few minutes or longer depending on the number of instances you have, but since you can save the results, you won't have to do this again. If you don't have Toad, you can download a trial from quest.com here: https://www.quest.com/products/toad-for-sql-server/
Use Group Execute to run the queries and results are intelligently tagged with the instance name:
I'm Really Unlucky because I don’t use Repositories
If there are no repositories to query, then you are going to have to use Group Execute and hit the msdb database directly. And unfortunately, you're going to have to write a query to do this. This blog post should help you get started. My contribution: Look at the device_type column in backupmediafamily. A value of 7 means a virtual device – which could mean LiteSpeed. So make sure to only look for that value.
But if you don't feel like writing a query and just want to know if LiteSpeed is installed on an instance, then hit up the next section.
Is LiteSpeed Installed?
If you want to know where LiteSpeed is installed, what type of license is used, and which version, here's the solution. But you need to first know your SQL Servers. All of them. If you don't know where your SQL Server are because they multiplied behind your back while you were talking to your coworkers about the latest American Idol episode, you'll need to find them first. The Discovery Wizard for SQL Server may help (https://www.quest.com/). Toad also has a similar discovery mechanism (Tools | SQL Server Discovery). Discovery is more of an art than science, so don't expect perfection. Your firewalls may not let you. I recommend you try all discovery methods. I have found that IP Address scans tend to work best, but your results may vary. This old blog post may help you get started: https://www.quest.com/community/products
Now that you have identified all SQL Servers and added them to Toad (or Management Studio), you can use the Group Execute feature to run the following queries to pull back the license and version.
-- These queries check the LiteSpeed License and Version
-- best to use the Toad for SQL Server Group Execute feature
Let us know what Worked for you
Comments are open, so feel free to post what method worked for you or any adventures into query writing you care to share. Good luck. And good compliance.