Configure and manage SQL Server 2005 from your application using WMI

Ever need to write your own scripts or applications to manage SQL Server 2005 instances? There are 3 sets of APIs you can use to do this: SMO, SQL-DMO, or WMI. I will introduce WMI here, and give some sample code in C# which will enable/disable a certain protocol on the server side.

WMI (Windows Management Instrumentation) is a set of Windows API that provides a uniform way to manage windows components both locally and remotely. SQL Server 2005 has a WMI provider which is hooked up to the WMI service on the server side. WMI uses dynamic binding, therefore if your application is on a remote machine, you don’t have to install any SQL Server 2005 components in order to use WMI to manage the server.

Now let’s demonstrate how to write a managed C# application to perform this job. First you need to reference the management assembly. If you are using Visual Studio you need to add a .Net reference “System.Management” to your project. Then at the beginning of your code, add this line in order to facilitate using all the WMI classes:

using System.Management;

Next, in the body of your application you need to create a scope and management classes for “SqlService” and “ServerNetworkProtocol”:

ManagementScope manScope = new ManagementScope(@"\ (ServerName) rootMicrosoftSqlServerComputerManagement");
ManagementClass sqlServicesMan = new ManagementClass(manScope, new ManagementPath("SqlService"), null);
ManagementClass serverProtocolsMan = new ManagementClass(manScope, new ManagementPath("ServerNetworkProtocol"), null);

sqlServicesMan.Get();
serverProtocolsMan.Get();

Remember to replace (ServerName)  with the real server host name. The last 2 lines are necessary for the management class objects to connect to the server and retrieve schema information. Now these 2 management object are ready to work.

The following demonstrates how you can enable server side TCP protocol for the default instance of SQL Server:

foreach (ManagementObject prot in serversProtocolMan.GetInstances())
{
      prot.Get();
      if ((string)prot.GetPropertyValue("ProtocolName") == "Tcp" &&
       (string)prot.GetPropertyValue("InstanceName") == "MSSQLSERVER")
      {
            prot.InvokeMethod("SetEnable", null);
      }
}

You can replace “Tcp” with “Np” or “Via” to enable the named pipe or VIA protocol. If you want to disable a protocol, simply replace the “SetEnable” string above with “SetDisable”.

Now that you have changed the protocol setting, you need to restart the SQL Server 2005 instance so that these changes can take effect. The following code will do this:

const uint sqlServerService = 1;
const uint sqlServiceStopped = 1;
foreach (ManagementObject svc in sqlServicesMan.GetInstances())
{
      if ((uint)svc.GetPropertyValue("SqlServiceType") == sqlServerService &&
          (string)svc.GetPropertyValue("ServiceName") == "MSSQLSERVER")
      {
            svc.Get();
            if ((uint)svc.GetPropertyValue("State") != sqlServiceStopped)
            {
                  svc.InvokeMethod("StopService", null);
            }
            svc.InvokeMethod("StartService", null);
      }
}

The code above only demonstrates the necessary object and methods to call in order to get the job done. You will need to add your own exception handling to that. To run the code from a client machine you need to have sufficient authorization and if the server firewall is enabled, it must be configured to allow remote administration.

SQL Server 2005 WMI provider has a full set of features that enable the application to configure many other server and client parameters. You can find an overview and WMI class descriptions in the following Books Online section:

SQL Server 2005 Book Online -> SQL Server Programming Reference -> Database Engine Administration Programming -> WMI and SQL Server -> WMI Provider for Configuration Management

Ju-Yi Kuo, SQL Server Protocols
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Comments

• Anonymous
  April 09, 2006
  In my previous post, I provided some sample C# code that allows you to configure SQL Server connection...

• Anonymous
  September 25, 2006
  My maild id is kulkarni.sv@gmail.com.
  I have tried above code for enable TCP/IP protocol. But it is not working & not showing any error.
  Please send me if u have a correct code for enabling TCP/IP port.
  
  Thanks,
  Sachin.

• Anonymous
  December 18, 2006
  Hi, Thanks a lot for the code. Just one question though, how can we set the TCP Dynamic port value & TCP port value in the above code? Thanks, Anand.

• Anonymous
  January 17, 2009
  The comment has been removed