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);
}
}
}
