Is there any WMI (Windows Management Instrumentation) support in SQL Server?
Well, if we navigate to rootMicrosoftSqlServer namespace from WMI CIM Studio, we see two WMI provider namespaces as follows.
We have 'ComputerManagement' namespace (implemented in sqlmgmprovider.dll) which is mainly used by SQL Server Configuration Manager, and the other one 'ServerEvents' (sqlwep.dll) which is used for server event notifications. (Note: if you search for all*. mof files under %Program Files%Microosft SQL Server folder, you will also notice that there are some xe*.mof files under MSSQLBinn. The schemas from these mof files go under root\WMI\EventTrace, and are used for SQL 2008 XEvent (Extended Events) feature.)
In this post, we will look into ComputerManagement WMI provider. Microsoft SQL WMI provider for Computer Management was first introduced in SQL Server 2005. Since then, a few classes were added to SQL Server 2008 / SQL Server 2008 R2. Major feature of this SQL WMI provider includes SQL server service control, SQL service enumeration, client network settings, server network protocol settings, etc. The SQL WMI provider uses different namespace depending on SQL version:
SQL 2005: root\Microsoft\SqlServer\ComputerManagement
SQL 2008: root\Microsoft\SqlServer\ComputerManagement10
SQL 11 : root\Microsoft\SqlServer\ComputerManagement11
Logical Architecture
Here is the high level logical architecture of the SQL WMI provider and its consumers. First, there are WMI clients that use the functionality of SQL WMI provider. SQL Server Configuration Manager(SQL CM) is the primary user, per se. SMO WMI (Microsoft.SqlServer.SqlWmiManagement.dll) provides all the SQL WMI functionality to .NET developers and SMO users. In addition, DBA can easily use VBScript or Powershell to access SQL WMI provider and of course C++/C# developers can use WMI API to write WMI client to access the SQL WMI provider.
SQL WMI Provider Components
The SQL WMI Provider consists of two main components, located in %Program Files%Microsoft SQL Server100Shared folder.
- Sqlmgmprovider.dll : This is a COM DLL that hosts all SQL WMI classes. Since it's a COM DLL, it should be registered by using regsvr32.exe. SQL setup will do this so you don't have to do anything but there might be rare cases you want to register again. (ex: C> regsvr32 sqlmgmprovider.dll)
- sqlmgmprovider*.mof : The MOF file contains the SQL WMI schemas. This mof file is handy if you want to check property name or method name of the specific WMI class. The schemas are saved to CIM repository by running mofcomp.exe. Again, SQL setup will do this for you but just in case you need to do it : C> mofcomp sqlmgmproviderxpsp2up.mof (in admin console for Vista/Win7)
The SQL WMI provider (sqlmgmprovider) uses WMI SDK Provider framework library (framedyn.dll). If you look at SQL WMI mof file (sqlmgmproviderxpsp2up.mof) , you can see that the provider is an instance provider and also a method provider. So all the classes in sqlmgmprovider have the following methods : EnumerateInstances, GetObject, ExecQuery, PutInstance, DeleteInstance and ExecMethod. Not all method implementation is required, though, so some methods simply can return WBEM_E_PROVIDER_NOT_CAPABLE.
One of interesting components that sqlmgmprovider relying on is svrenumapi.dll (SQL 2005) / svrenumapi100.dll (SQL 2008), which basically provides SQL service related funcationalities such as enumerating services, start or stop the service, etc. For 64bit, it's worth noting that there exist 64bit sqlmgmprovider.dll (under C:Program Files) and 32bit sqlmgmprovider.dll (under C:Program Files (x86)). So depending on situation, you can have 2 wmiprvse.exe processes for SQL WMI in 64bit. How to use SQL WMI provider
There are a slew of ways to use SQL WMI provider. Some people use scripting while others want more sophisticated control by using C++/C#. Let's take some examples.
Using Powershell
Let's try to find what kind of SQL server network protocol are enabled. The following script enumerates enabled protocol. The result shows only TCP/IP is enabled.
PS C:> Get-WmiObject -namespace "root\Microsoft\SqlServer\ComputerManagement10"
-class ServerNetworkProtocol -filter "Enabled=true"
__GENUS : 2
__CLASS : ServerNetworkProtocol
__SUPERCLASS :
__DYNASTY : ServerNetworkProtocol
__RELPATH : ServerNetworkProtocol.InstanceName="MSSQLSERVER", ProtocolName="Tcp"
__PROPERTY_COUNT : 5
__DERIVATION : {}
__SERVER : YONGSL
__NAMESPACE : rootMicrosoftSqlServerComputerManagement10
__PATH : YONGSL\root\Microsoft\SqlServer\ComputerManagement10:ServerNetworkProtocol.InstanceName="MSSQLSE
RVER",ProtocolName="Tcp"
Enabled : True
InstanceName : MSSQLSERVER
MultiIpConfigurationSupport : True
ProtocolDisplayName : TCP/IP
ProtocolName : Tcp
Now, let's enable NamePipe protocol against default instance. This time we use SetEnable() method in ServerNetworkProtocol class.
PS C:> $var = gwmi -namespace "root\Microsoft\SqlServer\ComputerManagement10" -
class ServerNetworkProtocol -filter "InstanceName='MSSQLSERVER' AND ProtocolName='Np'"
PS C:> $var.SetEnable()
After the method is done, we can check the result by using the first query above. But this time I used WQL (Wmi Query Language) to just take another query example.
PS C:> gwmi -namespace "rootMicrosoftSqlServerComputerManagement10" -query "SELECT * FROM ServerNetworkProtocol WHERE InstanceName='MSSQLSERVER'" | Select ProtocolName,Enabled ProtocolName Enabled
------------ -------
Sm False
Np True
Tcp True
Via False
Using VBScript
Here is a VBScript example. It uses GetObject with WINMGMTS moniker. Please note we added impersonatelevel and namespace in it. The script is pretty straigt forward. It simply connects to ComputerManagement10 namespace, gets SqlService objects and displays its property.
Set objNamespace = GetObject("WINMGMTS:{impersonationlevel=impersonate}//./root/Microsoft/SqlServer/ComputerManagement10")
Set inst = objNamespace.ExecQuery("SELECT * FROM ClientSettings")
wscript.echo "PRESS ENTER TO CONTINUE.... (Attach debugger if needed)"
WScript.StdIn.ReadLine
Set services = objNamespace.ExecQuery("SELECT * FROM SqlService")
IF services.Count = 0 THEN
WScript.Echo "No data found for SqlService"
END IF
WScript.Echo vbNewLine & "SQL Services"
FOR EACH svc IN services
WScript.Echo "ServiceName: " & svc.ServiceName & vbNewLine
NEXT
Using C#
The following samples are using .NET WMI classes whose namespace is System.Management. Sample1 enumerates all the instances of SqlService class, which basically list all SQL Server services.
using System.Management;
void Sample1() {
// Specify server, SQL WMI namespace and class name
ManagementPath mgmtPath = new ManagementPath();
mgmtPath.Server = "YONGSL";
mgmtPath.NamespacePath = @"root\Microsoft\SqlServer\ComputerManagement10"; //For SQL 2008
mgmtPath.ClassName = "SqlService";
// Get instances of the SqlService class
ManagementClass mgmtcls = new ManagementClass(mgmtPath);
ManagementObjectCollection mgmtColl = mgmtcls.GetInstances();
// Enumerates Sql Service info
foreach (ManagementObject mgmtObj in mgmtColl) {
string svc = string.Format("{0} ({1}) ServiceAccount={2}",
mgmtObj["ServiceName"],
mgmtObj["Description"],
mgmtObj["StartName"]);
Console.WriteLine(svc);
Console.WriteLine();
}
// Using WMI path<
ManagementPath mp = new ManagementPath(); mp.Path = @"\\YONGSL\root\Microsoft\SqlServer\ComputerManagement10:SqlService.ServiceName='MSSQLSERVER',SqlServiceType=1";
ManagementObject mo = new ManagementObject(mp);
Console.WriteLine("Startname = {0}", mo["Startname"]);
}
Another example - Sample2 - is using WQL to query SqlService with State property is 4 (which means Running service state). Some State values you might be interested in are Running=4, Stopped=1, Paused=7.
void Sample2()
{
// Search running SQL Services only
ManagementObjectSearcher search = new ManagementObjectSearcher(@"\\.\root\Microsoft\SqlServer\ComputerManagement10", // scope
"SELECT * FROM SqlService WHERE State=4"); // query
// Get resultset
ManagementObjectCollection coll = search.Get();
// Display results
Console.WriteLine("[ Currently running SQL Services ]");
foreach (ManagementObject obj in coll)
{
Console.WriteLine(obj["ServiceName"]);
}
}
Using C++
The following code illustrates the example of enumerating SQL Services in C++. First, we need to call CoInitialize to use COM and get WbemLocator. Once locator is aquired, connect to SQL WMI namespace. In this example, we used WQL to retrieve all SQL service instances.
#include <windows.h>
#define _WIN32_DCOM
#include <wbemidl.h>
#pragma comment(lib, "wbemuuid.lib")
#include <iostream>
using namespace std;
#include <comdef.h>
void _tmain(int argc, _TCHAR* argv[]) {
// CoInit
HRESULT hr = CoInitializeEx(NULL, COINIT_APARTMENTTHREADED);
hr = CoInitializeSecurity(NULL, -1, NULL, NULL, RPC_C_AUTHN_LEVEL_CONNECT,RPC_C_IMP_LEVEL_IMPERSONATE, NULL, EOAC_NONE, 0);
// Connect to SQL WMI
IWbemLocator *pWbemLocator = NULL;
IWbemServices *pServices = NULL;
hr = CoCreateInstance(CLSID_WbemLocator, NULL, CLSCTX_INPROC_SERVER, IID_IWbemLocator, (void**) &pWbemLocator);
_bstr_t bstrNamespace = L"root\Microsoft\SqlServer\ComputerManagement10";
hr = pWbemLocator->ConnectServer(bstrNamespace, NULL, NULL, NULL, 0, NULL, NULL, &pServices);
pWbemLocator->Release();
// Query for all SQL Services
IEnumWbemClassObject* pEnumerator = NULL;
hr = pServices->ExecQuery( bstr_t("WQL"), bstr_t("SELECT * FROM SqlService"), WBEM_FLAG_FORWARD_ONLY | WBEM_FLAG_RETURN_IMMEDIATELY, NULL, &pEnumerator);
IWbemClassObject *pSqlSvc;
ULONG ret = 0;
while (pEnumerator) {
hr = pEnumerator->Next(WBEM_INFINITE, 1, &pSqlSvc, &ret);
if (!ret) break;
VARIANT vName;
hr = pSqlSvc->Get(L"ServiceName", 0, &vName, 0, 0);
wcout << "Service Name : " << vName.bstrVal << endl;
VariantClear(&vName);
VARIANT vBinaryPath;
hr = pSqlSvc->Get(L"BinaryPath", 0, &vBinaryPath, 0, 0);
wcout << "-- Full EXE Command : " << vBinaryPath.bstrVal << endl << endl;
VariantClear(&vBinaryPath);
}
pServices->Release();
pServices = NULL;
CoUninitialize();
return;
};
Using SMO WMI
SMO provides a managed wrapper for SQL WMI provider. In order to use SMO WMI, you have to add reference to Microsoft.SqlServer.SqlWmiManagement.dll and then you will see the below Microsoft.SqlServer.Management.Smo.Wmi namespace (some dependent DLLs might need to be added) .
using System; using Microsoft.SqlServer.Management.Smo.Wmi;
namespace ConsoleApplication2
{
class Program
{
static void Main(string[] args)
{
ManagedComputer comp = new ManagedComputer("(local)");
// List all SQL Services
foreach (Service svc in comp.Services)
{
Console.WriteLine("{0}: {1}", svc.Name, svc.DisplayName);
}
// List enabled server protocols for default instance
Console.WriteLine("Enabled Server Protocols for MSSQLSERVER instance:");
foreach (ServerProtocol svrProt in comp.ServerInstances["MSSQLSERVER"].ServerProtocols)
{
if (svrProt.IsEnabled)
{
Console.WriteLine(svrProt.Name + " : Enabled");
}
}
// List enabled client protocols
Console.WriteLine("Enabled Client Protocols:");
foreach (ClientProtocol cliProt in comp.ClientProtocols)
{
if (cliProt.IsEnabled)
{
Console.WriteLine(cliProt.Name + " : Enabled");
}
}
}
}
}
How to debug SQL WMI provider
When SQL WMI provider is called from its client, wmiprvse.exe process (for XP or later) is created
and load sqlmgmprovider.dll and executes the corresponding methods. wmiprvse.exe is transient process
which means it is dynamically created on demand and dies if no more subsequent WMI call is requested.
So for debugging WMI provider, sometimes it's challenging to attach (remote breakin) the running
wmiprvse.exe process. In order to debug the WMI process, typically you can use the following steps.
One thing to note is that wmiprvse can - as I said - come and goes, sometimes quicker than you expect.
So need to attach debugger before it's gone.
- Run "C> tlist -m sqlmgmprovider.dll" You will get PID of wmiprvse.exe.
- Attach the debugger with the PID. Ex) C> windbg -p 2438
the class. Let us take a quick example. Say, for example, you want to look into SQL Service
enumeration part. To find the corresponding method, let's examine CSqlServerInstance C++ class
(this is SqlService WMI class).
0:006> x sqlmgmprovider!CSqlServerInstance::* 539a0324 sqlmgmprovider!CSqlServerInstance::PutInstance = <no type information> 5399b6a3 sqlmgmprovider!CSqlServerInstance::PauseService = <no type information> 5399c431 sqlmgmprovider!CSqlServerInstance::CreateComputerInstance = <no type information> 5399ae5e sqlmgmprovider!CSqlServerInstance::ExecMethod = <no type information> 53981818 sqlmgmprovider!CSqlServerInstance::`vftable' = <no type information> 5399bd2d sqlmgmprovider!CSqlServerInstance::SetServiceAccount = <no type information> 5399a85d sqlmgmprovider!CSqlServerInstance::CSqlServerInstance = <no type information> 5399af94 sqlmgmprovider!CSqlServerInstance::GetServiceType = <no type information> 5399b479 sqlmgmprovider!CSqlServerInstance::StopService = <no type information> 5399b228 sqlmgmprovider!CSqlServerInstance::GetObject = <no type information> 539a0324 sqlmgmprovider!CSqlServerInstance::DeleteInstance = <no type information> 5399b350 sqlmgmprovider!CSqlServerInstance::SetStartMode = <no type information> 5399b005 sqlmgmprovider!CSqlServerInstance::`scalar deleting destructor' = <no type information> 5399b02b sqlmgmprovider!CSqlServerInstance::EnumerateInstances = <no type information> 5399baa3 sqlmgmprovider!CSqlServerInstance::SetServiceAccountPassword = <no type information> 5399b005 sqlmgmprovider!CSqlServerInstance::`vector deleting destructor' = <no type information> 5399a897 sqlmgmprovider!CSqlServerInstance::~CSqlServerInstance = <no type information> 539a5165 sqlmgmprovider!CSqlServerInstance::ExecQuery = <no type information> 5399b57f sqlmgmprovider!CSqlServerInstance::StartServiceW = <no type information> 5399a8c1 sqlmgmprovider!CSqlServerInstance::LoadPropertyValues = <no type information> 5399b8a3 sqlmgmprovider!CSqlServerInstance::ResumeService = <no type information> Now you want to set breakpoint onto EnumerateInstances(). 0:006> bp sqlmgmprovider!CSqlServerInstance::EnumerateInstances 0:006> bl 0 e 5399b02b 0001 (0001) 0:**** sqlmgmprovider!CSqlServerInstance::EnumerateInstances 0:006> g ModLoad: 3fde0000 40221000 C:WINDOWSsystem32msi.dll ModLoad: 4ac60000 4ac75000 C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLBinnsqlboot.dll Breakpoint 0 hit eax=53981818 ebx=692eb8f8 ecx=539bbaac edx=7c90e514 esi=539bbaac edi=00000111 eip=5399b02b esp=00e0f05c ebp=00e0f06c iopl=0 nv up ei pl zr na pe nc cs=001b ss=0023 ds=0023 es=0023 fs=003b gs=0000 efl=00000246 sqlmgmprovider!CSqlServerInstance::EnumerateInstances: 5399b02b 682c020000 push 22Ch
And in order to look into class object, you can search them by using "x sqlmgmprovider!My* "
No comments:
Post a Comment