SQL Server 2005 and WMI

A very effective way of administering Windows is through scripts in a scripting language that use Windows Management Instrumentation (WMI) objects. SQL Server 2005 brings WMI provider for configuration management and WMI provider for server events. You can use the first one for configuring SQL Server through a script, and the second one to monitor events on SQL Server through a script. Here is an example of  VBScript script that uses WMI to enlist the protocols and enable named pipes.

‘ enum protocols and show status

set wmi = GetObject(“WINMGMTS:.rootMicrosoftSqlServerComputerManagement”)

for each prop in wmi.ExecQuery(“select * ” & _

      “from ServerNetworkProtocol ” & _

      “where InstanceName = ‘mssqlserver'”)

 WScript.Echo prop.ProtocolName & ” – ” & _

              prop.ProtocolDisplayName & ”  ” & _

              prop.Enabled

next

‘ enable named pipes

for each changeprop in wmi.ExecQuery(“select * ” & _

      “from ServerNetworkProtocol ” & _

      “where InstanceName = ‘mssqlserver’ and ” & _

      “ProtocolName = ‘Np'”)

 changeprop.SetEnable()

next

Note you have to restart the service if you want changes in network protocols to take effect. I leave it to you to find a way how to restart an instance of SQL Server through script.