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”:

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:

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:

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.