Thursday, March 11, 2010

How to call 32bit / 64bit SQL WMI providers

I previously wrote the overview of SQL WMI provider. Today I am going to explain a little more about the behavior of SQL WMI provider on 64bit machine. In 64bit machine, SQL Server installs two SQL WMI providers – one 32bit SQL WMI provider and the other 64bit SQL WMI provider.
Even if you only install 64bit version of SQL Server, both 32bit and 64bit SQL WMI providers are installed on the machine. But in this case SQL Configuration Manager will disable [SQL Server Network Configuration (32bit)] node as seen below since there is no need to update 32bit server network protocols. SQL Client Configuration nodes are both enabled for 32/64bit, though, because this settings are for client, not server.

The reason we have 32bit / 64bit providers is because of 64bit WOW registry redirection. In WOW, 32bit application has its own registry and 32bit registry data should be distinguished from 64bit one.
If you install both 32bit and 64bit SQL Server instances on the 64bit machine, each 32/64 WMI provider will work with the corresponding 32/64bit SQL instances. One of the interesting thing is if you want to write an application that accesses SQL WMI provider, you might get stuck in a conflict situation: if you compile your application as 32bit app, your application only accesses 32bit SQL WMI provider. For 64bit application, as you guess, only accessing 64bit WMI provider. How can we solve this problem?
This can be addressed by using IWbemContext. In C++, context object can be created as follows and then passed as one of arguments of ConnectServer() method.

     CoCreateInstance(CLSID_WbemContext, NULL, CLSCTX_INPROC_SERVER, IID_IWbemContext, (LPVOID*)&pContext);
     CComVariant v32bit(32, VT_I4); 
     pContext->SetValue("__ProviderArchitecture", 0, &v32bit);

hr = pWbemLocator->ConnectServer(bstrNamespace, NULL, NULL, NULL, 0, NULL, pContext, &pServices);

In C#, the context can be set in ManagementScope.Options.Context. The following example illustrates how one application (either 32 or 64) can access both 32bit/64bit SQL WMI providers.
static void Main(string[] args) { // Connect to 64bit WMI provider ManagementScope scope64 = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement"); scope64.Options.Context.Add("__ProviderArchitecture", 64); // Enable TCP for 64bit default instance ManagementPath path = new ManagementPath(@"ServerNetworkProtocol.InstanceName='MSSQLSERVER',ProtocolName='Tcp'"); ManagementObject mo = new ManagementObject(scope64, path, null); mo.InvokeMethod("SetEnable", null); // Connect to 32bit WMI provider ManagementScope scope32 = new ManagementScope(@"\\.\root\Microsoft\SqlServer\ComputerManagement"); scope32.Options.Context.Add("__ProviderArchitecture", 32); // Disable TCP for 32bit named instance (MSSQL$INST) ManagementPath path2 = new ManagementPath(@"ServerNetworkProtocol.InstanceName='INST',ProtocolName='Tcp'"); ManagementObject mo2 = new ManagementObject(scope32, path2, null); mo2.InvokeMethod("SetDisable", null); // Restart all SQL server instances ManagementClass svcClass = new ManagementClass(scope32, new ManagementPath("SqlService"), null); foreach (ManagementObject svc in svcClass.GetInstances()) { if ((uint)svc["SqlServiceType"] == 1) /* type 1 = SQL Server */ { if ((uint)svc["State"] == 4) /* state 4 = Started */ { svc.InvokeMethod("StopService", null); } svc.InvokeMethod("StartService", null); } } }

Thursday, March 4, 2010


SQLDumper is one of SQL Server tools, which creates user mode process dump and optionally uploads the dump to Microsoft Watson site.

In order to create user mode dump, it is using Dbghelp.dll and this is why dbghelp.dll exists in the same folder.

For uploading, SQLDumper is using Watson client program (DW20.EXE, HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PCHealth\ErrorReporting\DW\Installed)

Here is the SQLDumper usage help. (sqldumper.exe -?)

There are quite a few options for SQLDumper.exe but generally we use a few arguments.

(You see some Ptr arguments such as SqlInfoPtr, ExceptionRecordPtr, these are for internal use. Some SQL tools calls sqldumper and passes some contextual pointers)

To show simple case, here is how to create mini dump for SQL service.
(1) Let’s say we found PID (368) for SQL Server service by using tlist.exe.
(Technically it can dump any user process but the example focuses on SQL related process)

C:\Program Files\Microsoft SQL Server\100\Shared> tlist /s
   0 System Process
   4 System
368 sqlservr.exe    Svcs:  MSSQLSERVER
3548 Ssms.exe        Title: Microsoft SQL Server Management Studio
(2) Now run SQLDUMPER.EXE as below. 368 is PID for sqlservr.exe and 0 means not specifying particular thread. 0x130 is OR combination of 0x100 (Verbose display to console) and 0x20 (dump all threads info).
(You can check those FLAGS in usage help as seen above.)
If you want to all memory dump, then you can specify 0x130 instead of 0x120. You can specify 0x1000 for full dump, and 0x400 to send to Watson site. Developers like to have full dump since it has more information but the dump file is way bigger, so not always good. Once ran successfully, in this case(see output below), the SQLDumpr0008.dmp was generated in the current folder.

C:\Program Files\Microsoft SQL Server\100\Shared> Sqldumper 368  0  0x120 

If you look at the usage help, you can see MiniDumpFlags. This gives more options for mini dump.

In simple case above, the option was 0x120 and with this option Handle information was not generated.
0:000> !handle
ERROR: !handle: extension exception 0x80004002.
    "Unable to read handle information"

If handle information is required, we can append MiniDumpFlags (0x0004) after 0x120 as below.
C:\Program Files\Microsoft SQL Server\100\Shared> SqlDumper 368  0  0x120:4

Now handle information is available.
0:000> !handle 00000b38 7
Handle 00000b38
  Type                    File
  Attributes         0
  GrantedAccess               0x120089:
  HandleCount  2
  PointerCount   3

Even if SQLDumper has valuable features, it is not intended for general purpose dump tool. For general purpose dump, CDB/Windbg, ADPlus are great tools. For more details, please see this post.