Saturday, June 26, 2010

SMO WMI : ManagedComputer

How to use SMO WMI

SMO WMI provides managed developers with some handy access to SQL WMI provider. This C# WMI client is included under SMO namespace, hence the name SMO WMI. It is internally using .NET WMI (System.Management) to access SQL WMI provider data.

In order to use SMO WMI in C#, first add references to the following files. (these are all GAC'd and located in C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies).
  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.Management.Sdk.Sfc.dll
  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.SqlWmiManagement.dll
  • Microsoft.SqlServer.WmiEnum.dll
The starting point of SMO WMI is ManagedComputer class. There are a few variation of the constructor of this class. The sample below uses local machine but remote machine name can be used. Once an instance of ManagedComputer is created, one can access the following classes by accessing the public properties of ManagedComputer class.
  • Services : access or control SQL related services (such as Start, Stop)
  • ServerInstances : access SQL Server instances such as MSSQLSERVER, INST2.
  • ServerProtocols : access server protocols (ex: named pipe, tcp) per SQL Server instance.
  • ClientProtocols : access client protocols such as named pipe, tcp.
  • ServerAliases : access server aliases if any.
Now here is a sample code.
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Wmi;

static void TestSmoWmi()
{
  ManagedComputer comp = new ManagedComputer("(local)");

  //Services
  Console.WriteLine("<> ManagedComputer/Services");
  foreach (Service svc in comp.Services)
  {
   Console.WriteLine("{0}: (Status={1}) ", svc.Name, svc.ServiceState);
   if (svc.ServiceState == ServiceState.Stopped &&
   svc.StartMode != Microsoft.SqlServer.Management.Smo.Wmi.ServiceStartMode.Disabled)
   {
     svc.Start();
   }
  }

  //ServerInstancesConsole.WriteLine("<> ManagedComputer/ServerInstances");
  foreach (ServerInstance inst in comp.ServerInstances)
  {
    Console.WriteLine(inst.Name);
  }
  Console.WriteLine();

  //ServerProtocols
  Console.WriteLine("<> ManagedComputer/ServerInstances[]/ServerProtocols");
  foreach (ServerProtocol svrProt in comp.ServerInstances["MSSQLSERVER"].ServerProtocols)
  {
    Console.WriteLine("{0}: {1}", svrProt.Name, svrProt.IsEnabled);
  }
  Console.WriteLine();

  //ClientProtocols
  Console.WriteLine("<> ManagedComputer/ClientProtocols");
  foreach (ClientProtocol cli in comp.ClientProtocols)
  {
    Console.WriteLine("{0}: {1}", cli.Name, cli.IsEnabled);
  }
  Console.WriteLine();

  Console.WriteLine("<> ManagedComputer/ServerAliases");
  foreach (ServerAlias alias in comp.ServerAliases)
  {
    Console.WriteLine("{0}: {1}, {2}", alias.Name, alias.ServerName, alias.ProtocolName);
  }
  Console.WriteLine();

}
The sample output is shown below.