Windows Management Instrumentation

 

Suportul WMI in SQL Server 2005 a fost extins si este folosit prin intermediul a doi provideri WMI:

1.  WMI provider pentru Configuration Management –  este folosit pentru configurarea serviciilor SQL si setarilor pentru retea. Surface Area Configuration si Configuration Manager sunt doua utilitare pentru configurare care folosesc WMI. Pentru utilizare sunt disponibile urmatoarele metode:

• Un limbaj de script ca VBScript, Jscript si Perl din care pot folosi Windows Query Language (WQL). WQL este un subset simplificat al SQL cu niste extensii specifice WMI.
• Prin intermediul SMO folosind obiectul ManagedComputer.
• Folosind utilitarul SQL Server Configuration Manager sau consola MMC cu SQL Server WMI provider snap-in.

2. WMI provider pentru Server Events – folosit pentru a monitoriza evenimentele unei instante SQL Server.

 

Urmatorul exemplu va utiliza  o interogare WQL folosind WMI Provider pentru a enumera serviciile SQL Server.

Pentru a rula exemplul trebuie sa executati pasii urmatori:

1. Intr-un editor text gen Notepad deschideti un nou document si copiati codul specificat in exemplul de mai jos.
2. Salvati documentul cu un nume de exemplu WQLQuery.vbs.
3. Din linia de comanda rulati cscript WQLQuery.vbs.

Exemplu:

set wmi = GetObject("WINMGMTS:\\.\root\Microsoft\SqlServer\ComputerManagement")

for each prop in wmi.ExecQuery("SELECT * FROM SqlService")

 WScript.Echo "ServiceName: " & prop.ServiceName

 WScript.Echo "Description: " & prop.Description

 WScript.Echo "DisplayName: " & prop.DisplayName

 WScript.Echo "HostName: " & prop.HostName

 WScript.Echo "ProcessId: " & prop.ProcessId

 WScript.Echo "SQLServiceType: " & prop.SQLServiceType

 WScript.Echo "StartMode: " & prop.StartMode

 WScript.Echo "StartName: " & prop.StartName

 WScript.Echo "State: " & prop.State

 WScript.Echo "---------------------------------------------------------------"

next

 

-- Output pe calculatorul meu

Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

ServiceName: MSFTESQL
Description: Quickly creates full-text indexes on content and properties of structured and semi-structured data to allow fast linguistic searches on this data.
DisplayName: SQL Server FullText Search (MSSQLSERVER)
ErrorControl: 1
ExitCode: 0
HostName: MICROTRAINING
ProcessId: 3088
SQLServiceType: 3
StartMode: 3
StartName: LocalSystem
State: 4
------------------------------------------------------------
ServiceName: MSSQL$SQLEXPRESS
Description: Provides storage, processing and controlled access of data and rapid transaction processing.
DisplayName: SQL Server (SQLEXPRESS)
ErrorControl: 1
ExitCode: 1077
HostName: MICROTRAINING
ProcessId: 0
SQLServiceType: 1
StartMode: 3
StartName: NT AUTHORITY\NetworkService
State: 1
------------------------------------------------------------
ServiceName: MSSQLSERVER
Description: Provides storage, processing and controlled access of data and rapid transaction processing.
DisplayName: SQL Server (MSSQLSERVER)
ErrorControl: 1
ExitCode: 0
HostName: MICROTRAINING
ProcessId: 1180
SQLServiceType: 1
StartMode: 2
StartName: LocalSystem
State: 4
------------------------------------------------------------
ServiceName: ReportServer$SQLEXPRESS
Description: Manages, executes, renders, schedules and delivers reports.
DisplayName: SQL Server Reporting Services (SQLEXPRESS)
ErrorControl: 1
ExitCode: 1077
HostName: MICROTRAINING
ProcessId: 0
SQLServiceType: 6
StartMode: 3
StartName: LocalSystem
State: 1
------------------------------------------------------------
ServiceName: SQLBrowser
Description: Provides SQL Server connection information to client computers.
DisplayName: SQL Server Browser
ErrorControl: 1
ExitCode: 0
HostName: MICROTRAINING
ProcessId: 1816
SQLServiceType: 7
StartMode: 2
StartName: NT AUTHORITY\NetworkService
State: 4
------------------------------------------------------------
ServiceName: SQLSERVERAGENT
Description: Executes jobs, monitors SQL Server, and fires alerts, and allows automation of some administrative tasks.
DisplayName: SQL Server Agent (MSSQLSERVER)
ErrorControl: 1
ExitCode: 1077
HostName: MICROTRAINING
ProcessId: 0
SQLServiceType: 2
StartMode: 3
StartName: LocalSystem
State: 1
------------------------------------------------------------

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro

Ca o completare pe blog-ul SQL Server 2005 Express Edition este un exemplu dedicat detectiei SQL Express folosind WMI:

http://blogs.msdn.com/sqlexpress/archive/2006/07/29/faq-detecting-sql-server-2005-using-wmi.aspx

Pentru generare cod va recomand utilitarul WMI Code Creator v1.0.

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro