Starting SQL Trace using C#

Everybody uses SQL Profiler tool to capture useful information against SQL Server. But is it possible to capture SQL Traces programmatically? Well, by and large there are two possible ways that I can think of. Firstly we can call TSQL stored procedues such as sp_trace_create in the program. Secondly, we can utilize Microsoft.SqlServer.Management.Trace classes (aka Trace Management Object (TMO)).

Microsoft.SqlServer.Management.Trace is implemented in Microsoft.SqlServer.ConnectionInfoExtended.dll which is in the GAC. Typically you will need Microsoft.SqlServer.ConnectionInfo.dll in order to establish SQL connection. So add references to two DLLs in your C# project. Now, here is a small sample that starts SQL Trace and saves the result to a trace file.

using System;
using System.IO;
using System.Reflection;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;

namespace StartTrace
{
 class Program
 {
  static void Main(string[] args)
  {
    if (args.Length != 2)
    {
      string asmName = Assembly.GetExecutingAssembly().GetName().Name;
      Console.WriteLine("Usage: {0}.exe <serverName> <traceFile>", asmName);
      Console.WriteLine(@"   ex) {0}.exe TestServer test.trc", asmName);
      return;
    }
    string serverName = args[0];
    string traceFile = args[1];
    (new Program()).StartTrace(serverName, traceFile);
 }

 void StartTrace(string serverName, string traceFile)
 {
  // Sanity check
  if (string.IsNullOrEmpty(serverName))
   throw new ArgumentException("serverName is null or empty");
  if (string.IsNullOrEmpty(traceFile))
   throw new ArgumentException("traceFile is null or empty");
  if (File.Exists(traceFile))
  {
    Console.WriteLine("### WARNING ### '{0}' already exists. Press Q if you don't want to overwrite.", traceFile);
  }

  // Start TraceServer   : NOTE: change TDF path below if needed
  TraceServer trcSvr = new TraceServer();
  SqlConnectionInfo ci = new SqlConnectionInfo(serverName);
  string tdf = @"%ProgramFiles%\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Standard.tdf";
  tdf = Environment.ExpandEnvironmentVariables(tdf);
  trcSvr.InitializeAsReader(ci, tdf);

  // Press S to stop tracing, Q for quit.
  Console.WriteLine();
  Console.WriteLine("TRACE started at {0} : Server={1}, TraceFile={2}", DateTime.Now, serverName, traceFile);
  Console.WriteLine("Press 'S' to stop trace... Press 'Q' to quit...");
  while (true)
  {
   ConsoleKeyInfo key = Console.ReadKey();
   if (key.Key == ConsoleKey.Q)
   {
    trcSvr.Stop();
    return;
   }
   if (key.Key == ConsoleKey.S)  // Stop trace
   {
     trcSvr.Stop();
     break;
   }
  }
  Console.WriteLine();

  // Write trace to .trc file
  TraceFile writer = new TraceFile();
  writer.InitializeAsWriter(trcSvr, traceFile);
  writer.WriteNotify += new WriteNotifyEventHandler(writer_WriteNotify);
  while (writer.Write()) ;
  writer.Close();
 }

  // Optional Notify handler
  void writer_WriteNotify(object sender, TraceEventArgs args)
  {
  Console.WriteLine("Writing: {0}" , args.CurrentRecord[0]);
  args.SkipRecord = false;
  }
 }
}

In order to start SQL Trace, I created TraceServer object and called InitializeAsReader() method with SQL connection info and profiler template file (in this case, I used stanard.tdf. If you want to have replayable trace file, you have to choose tsql_replay.tdf template). Once InitializeAsReader is successful, it automatically starts SQL trace again the SQL Server. So now you can send some TSQL statements to the SQL server. When everything is done and you want to stop trace, you press S as source code tells you. Then, next it will write SQL trace data to the output .trc file. I on purpose added WriteNotify event handler just to show you that you can check each trace. If you want to omit unncessary trace data during trace write, this is the place you can control by setting SkipRecord = true.

You can also save the trace results to SQL Table by using TraceTable class and also can replay trace by using TraceReplay class.

NOTE: Sample tool using TMO can be downloaded from here Profiler Console

SQL WMI Provider

NOTE: PDF version of this article can be downloaded from here (SQL WMI Provider)

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

Once you attach the debugger, you can find a method you want to set breakpoint by simply examine
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* "