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.

Dejan Sarka

Independent Consultant at Dejan Sarka S.P.
I am a trainer and consultant that focus on development of database & business intelligence applications.

Besides projects, I spend about half of the time on training and mentoring. I am the founder of the Slovenian SQL Server and .NET Users Group. and the main author or coauthor of eleven books about databases and SQL Server. I also developed many courses and seminars for SolidQ.
Dejan Sarka