LiteSpeed Licensing Compliance: A How-To Guide

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

SELECT

lv.ServerName "Server",

ld.DatabaseName "DatabaseName",

ld.DatabaseID "DatabaseID",

CONVERT(VARCHAR(20), la.StartTime, 120) "LastBackupDate",

ls.StatusName "Status"

FROM

dbo.LitespeedServer lv

LEFT JOIN

dbo.LitespeedDatabase ld

ON

ld.ServerName = lv.ServerName

AND

ld.Deleted = 0

LEFT JOIN

dbo.LitespeedActivity la

ON

la.DatabaseID = ld.DatabaseID

AND

la.ServerName = lv.ServerName

AND la.ActivityID = (

SELECT TOP 1

la2.ActivityID

FROM

dbo.LitespeedActivity la2

WHERE

la2.DatabaseID = ld.DatabaseID

AND

la2.ActivityTypeID = 1

AND

la2.ServerName = ld.ServerName

ORDER BY

la2.StartTime DESC)

LEFT JOIN

dbo.LitespeedStatusType ls

ON

la.StatusTypeID = ls.StatusTypeID

AND

la.StatusTypeID in (2,3) -- Completed or Failed

WHERE

DATEDIFF(d, la.StartTime, GETDATE()) < 367

AND

la.StartTime IS NOT NULL

ORDER BY

lv.ServerName,

LastBackupDate DESC,

ld.DatabaseName;

GO

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.

 

-- This query can be run on a LiteSpeed Central Repository or can be run using

-- Toad Group Execute on local repositories

-- Returns the last LiteSpeed full backup performed on each instance

SELECT

lv.ServerName "Server",

CONVERT(VARCHAR(20), la.StartTime, 120) "LastBackupDate"

FROM

dbo.LitespeedServer lv

LEFT JOIN

dbo.LitespeedActivity la

ON

la.ServerName = lv.ServerName

AND la.ActivityID = (

SELECT TOP 1

la2.ActivityID

FROM

dbo.LitespeedActivity la2

WHERE

la2.ServerName = lv.ServerName

AND

la2.ActivityTypeID = 1

ORDER BY

la2.StartTime DESC)

WHERE

DATEDIFF(d, la.StartTime, GETDATE()) < 367

AND

la.StartTime IS NOT NULL

ORDER BY

lv.ServerName;

GO

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

EXEC master.dbo.xp_sqllitespeed_licenseinfo;

GO

EXEC master.dbo.xp_sqllitespeed_version;

GO

 

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.

About the Author
David.Gugick
David Gugick manages the LiteSpeed for SQL Server and vRanger product lines for Dell. David brings more than 20 years of management and technical experience in product and portfolio management, application...