FAQ: Detecting SQL Server 2005 using WMI
I've gotten a number of e-mails asking how to detect if SQL Express is installed on a computer. I'm finally getting around to putting this in the Blog so I don't have to keep typing the answer. We've made discovery much easier in SQL Server 2005 with the addition of the SQL Server 2005 WMI Provider. This method will work for all editions of SQL Server 2005, but the example is tailored to SQL Express.
Why can't I just use the registry?
Do a little research on this topic and you'll find a number of examples on how to use the registry to detect SQL Server. Don't do it this way!
The problem with using the registry to detect SQL Server is that Microsoft doesn't make any promises that we won't change the registry without warning. (It's our registry, we can change it.) We changed the registry between SQL 2000 and SQL 2005 and it's a good bet well do the same between 2005 and what ever comes next. Enter the SQL WMI Provider, this provider abstracts the registry and allows you to discover information about SQL Server 2005. We will be maintaining the SQL WMI provider in future versions so that your detection code will continue to work on later versions.
Do I detect a catch?
Yes, you do. The SQL WMI Provider doesn't work for SQL Server versions prior to 2005. If you need to detect earlier versions, say 2000, you're pretty much back to your old registry tricks.
What else can the SQL WMI Provider do?
What, you want more? Fine. The SQL WMI Provider actually covers a bunch of different functionality beyond detection and is broken into two separate providers:
WMI Provider for Server Events - With this provider you can monitor events for a given instance of SQL Server.
WMI Provider of Configuration Management - With this provider you can detect and manage a given instance of SQL Server.
(If the links don't work for some reason, say MSDN completely restructures its content, just search for the topics in Books Online.)
Why not use SMO?
(Added in response to Jens' comment.)
Many observent readers (OK, it was just Jens) pointed out that SMO contains the ManagementServer namespace which offers similar functionality. I chose not to use SMO for a couple reasons:
- WMI does not require managed code or the .NET Framework. Yes, this is a C# sample, but the same WQL would work from VBScript on a computer without the framework installed. Sometimes it's important not to have a dependency on managed code.
- The SQL WMI Provider calls seem to handle being run on computers without SQL Server 2005 installed where SMO did not in my hands. I just had problems getting SMO based code to fail correct when SQL wasn't installed. I'm no SMO genious, so it's likely this was pilot error.
WMI is the detection mechanism recommend by our Servicing and Lifecycle Platform team and it does not have as many prerequisite as SMO. If you can count on the correct prerequisites being on the computer where you're wanting to detect SQL Express, feel free to check out the SMO ManagementServer namespace. If you want to use the recommended method, stick with WMI.
Hey Explanation Boy, how about a sample?
Ok already, I'll get to the sample. It’s in C# if you didn’t notice.
using System;
using System.Management;
namespace WMISample
{
// The WMI query for this class was created using the WMI Code Creator tool
// that is available from
// https://www.microsoft.com/downloads/details.aspx?FamilyID=2cc30a64-ea15-4661-8da4-55bbc145c30e&DisplayLang=en
public class MyWMIQuery
{
public static void Main()
{
bool foo = isExpressInstalled();
if (foo)
{
Console.WriteLine("You have SQL Express SP1. Sweet!!");
}
else
{
Console.WriteLine("No instances named SQLEXPRESS exists on this computer.");
}
Console.WriteLine("Hit Enter to continue.");
Console.Read();
}
public static bool isExpressInstalled()
{
const string edition = "Express Edition";
const string instance = "MSSQL$SQLEXPRESS";
const int spLevel = 1;
bool fCheckEdition = false;
bool fCheckSpLevel = false;
try
{
// Run a WQL query to return information about SKUNAME and SPLEVEL about installed instances
// of the SQL Engine.
ManagementObjectSearcher getSqlExpress =
new ManagementObjectSearcher("root\\Microsoft\\SqlServer\\ComputerManagement",
"select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and ServiceName = '"
+ instance + "' and (PropertyName = 'SKUNAME' or PropertyName = 'SPLEVEL')");
// If nothing is returned, SQL Express isn't installed.
if (getSqlExpress.Get().Count==0)
{
return false;
}
// If something is returned, verify it is the correct edition and SP level.
foreach (ManagementObject sqlEngine in getSqlExpress.Get())
{
if (sqlEngine["ServiceName"].ToString().Equals(instance))
{
switch (sqlEngine["PropertyName"].ToString())
{
case "SKUNAME":
// Check if this is Express Edition or Express Edition with Advanced Services
fCheckEdition = sqlEngine["PropertyStrValue"].ToString().Contains(edition);
break;
case "SPLEVEL":
// Check if the instance matches the specified level
fCheckSpLevel = int.Parse(sqlEngine["PropertyNumValue"].ToString()) >= spLevel;
//fCheckSpLevel = sqlEngine["PropertyNumValue"].ToString().Contains(spLevel);
break;
}
}
}
if (fCheckEdition & fCheckSpLevel)
{
return true;
}
return false;
}
catch (ManagementException e)
{
Console.WriteLine("Error: " + e.ErrorCode + ", " + e.Message);
return false;
}
}
}
}
isExpressInstalled() is the worker bee here. This method runs a WQL query that specifically looks for instances of the SQL Server Engine (SQLServiceType = 1) where the Instance Name is SQLEXPRESS (ServiceName = MSSQL$SQLEXPRESS). In the sample, I happen to pass the Instance Name as a variable instance, but you can do it any way you want. I choose to look specifically for SQLEXPRESS for a couple reasons: It's the default Instance Name, Visual Studio Express and ClickOnce deployment use this Instance Name, and we'd like to see more applications pointing to a single Instance Name, so the default makes sense. Once it's established that an instance named SQLEXPRESS exists, a further check is made to ensure that it is actually one of the SQL Express Editions and that it is running at the correct service pack level, in this case, SP1
That's pretty much it, a few checks to see what happened, and the function returns either True or False to the calling routine. You can call this from where ever you want and adjust the parameters as appropriate. One final note, check out the WMI Code Creator as mentioned in the code comment. This is the tool that will help you explore the SQL Server 2005 WMI Provider to find other ways to use it and other properties that you can query.
Mike
Addendum
Barry Sumpter converted this code into VB.NET in a forum post and I thought I'd add it here so it's easier to find.
Imports System
Imports System.Management
Namespace WMISample
Public Class MyWMIQuery
Public Shared Sub Main()
Dim foo As Boolean = isExpressInstalled()
If foo Then
Console.WriteLine("You have SQL Express SP1. Sweet!!")
Else
Console.WriteLine("No instances named SQLEXPRESS exists on this computer.")
End If
Console.WriteLine("Hit Enter to continue.")
Console.Read()
End Sub
Public Shared Function isExpressInstalled() As Boolean
Const edition As String = "Express Edition"
Const instance As String = "MSSQL$SQLEXPRESS"
Const spLevel As Integer = 1
Dim fCheckEdition As Boolean = False
Dim fCheckSpLevel As Boolean = False
Try
Dim getSqlExpress As ManagementObjectSearcher = New ManagementObjectSearcher("root\Microsoft\SqlServer\ComputerManagement", "select * from SqlServiceAdvancedProperty where SQLServiceType = 1 and ServiceName = '" + instance + "' and (PropertyName = 'SKUNAME' or PropertyName = 'SPLEVEL')")
If getSqlExpress.Get.Count = 0 Then
Return False
End If
For Each sqlEngine As ManagementObject In getSqlExpress.Get
If sqlEngine("ServiceName").ToString.Equals(instance) Then
Select Case sqlEngine("PropertyName").ToString
Case "SKUNAME"
fCheckEdition = sqlEngine("PropertyStrValue").ToString.Contains(edition)
Case "SPLEVEL"
fCheckSpLevel = Integer.Parse(sqlEngine("PropertyNumValue").ToString) >= spLevel
End Select
End If
Next
If fCheckEdition And fCheckSpLevel Then
Return True
End If
Return False
Catch e As ManagementException
Console.WriteLine("Error: " + e.ErrorCode + ", " + e.Message)
Return False
End Try
End Function
End Class
End Namespace