WMI for SQL Server Philip Pursglove SQL Server comes

Document Sample
WMI for SQL Server Philip Pursglove SQL Server comes Powered By Docstoc
					WMI for SQL Server
Philip Pursglove
SQL Server 2000 comes with its’ own Windows Management Instrumentation provider. In this article I’ll explain how to
connect WMI to SQL Server and demonstrate how to use some of the myriad of classes that the provider exposes.

In my April and May articles, I introduced Windows Management Instrumentation and explained how you can use it to
retrieve information about your computer’s hardware, operating system, and software. Since then I’ve upgraded my
machine to SQL Server 2000, which comes with its’ own WMI provider which is specifically targeted to provide
information about SQL installations, and crucially, allows you to change settings, an ability the base WMI provider
doesn’t give you. The WMI install materials appear on the SQL Server 2000 CD in the x86\Other\WMI folder. To install
the WMI provider on a SQL Server 7.0 Server, you’ll need to copy the installer from the SQL Server 2000 CD onto the
machine – there is no download available from Microsoft.

The WMI provider is intelligent enough to provide information on either SQL Server 7 or SQL Server 2000 – the
installed version is detected after installation, and the provider subsequently supplies information for the appropriate
version. Many of the classes, properties and methods work equally well with either version, but some are limited to
one or the other, however these classes have an extra property called ‘SQLVersion’ which is set to ‘7.0’ or ‘8.0’
accordingly.

CONNECTING to the SQL Server Namespace
The SQL Server provider introduces a new namespace to WMI (namespaces are, roughly, groupings of similar or
complementary sets of data), which is used for connecting to the provider. The base installation of WMI installs the
default provider, which is ‘root\cimv2’, which is the namespace that you connect to if you don’t specify any other
namespace. How you access the SQL provider depends on how you are accessing WMI; if you use the Locator
object, then you can specify a namespace in the ConnectServer method call:

locSQL.ConnectServer(strNamespace:=”root\MicrosoftSQLServer”)

If you prefer to use the GetObject method combined with monikers, then you specify the namespace as part of the
moniker:

GetObject(“Winmgmts:root\MicrosoftSQLServer”)

It is also possible to alter the default namespace that WMI connects to by altering the value of the
HKEY_LOCAL_MACHINE\Software\Microsoft\WBEM\Scripting\Default Namespace registry key. However I would
recommend against this, especially on production servers – if you install something else at a later date that expects
the default namespace to be cimv2, then that software will break and the resulting error is one that is likely to be
difficult to trace.

The SQL Server provider also imports some classes from the default WMI provider for several of its’ association
classes. An association class is used to link two other classes together; an example is the
MSSQLLogin_Win32UserAccount class, which provides information on the relationship between a SQL Server login
and a Windows domain account. More on these later – for now, let’s dive into some of the classes.

DATABASE Classes
The first set of classes I want to cover are related to actual databases on SQL installations – I’ve grouped them under
the approximate heading of “Database Classes” but that’s my term, not Microsoft’s. Some of these classes return
information similar to that which we can retrieve using SQL-DMO, however WMI gives us a much richer picture of
what is going on with SQL installations. Classes in WMI can be accessed directly, so there’s no real object model,
however we can think of the MSSQL_SQLServer class as being a notional ‘top-level’ class as it represents a physical
machine with SQL Server installed. Among the information exposed by this class, we can query whether or not it is
part of a cluster, whether it is a SQL Server installation (as opposed to an MS Data Engine installation), the server
name and the specific version of SQL Server that is installed. This class also allows us to programmatically backup,
restore and verify databases, start or stop the SQL service, or attach or detach databases.

Dim   locServers As SwbemLocator
Dim   svcServers As SwbemServices
Dim   ostServers As SwbemObjectSet
Dim   wmoServer As SwbemObject

Set locServers = New SwbemLocator

Set svcServers = locServers.ConnectServer _
     (strNamespace:=”root\MicrosoftSQLServer”)
Set ostServers = svcServers.InstancesOf _
     (“MSSQL_SQLServer”)

For Each wmoServer In ostServers
     MsgBox wmoServer.TrueName & vbNewLine & _
          “Clustered: “ & wmoServer.Clustered & _
          vbNewLine & _ “Version: “ & _
           wmoServer.VersionMajor & “.” & _
          wmoServer.VersionMinor
Next wmoServer

To view the databases on a server, we can specify a machine to connect to in the ConnectServer call e.g.

locServers.ConnectServer(strServer:=”MySQLServer”, _
     strNamespace:=”root\MicrosoftSQLServer”)

or if using the GetObject function with WMI monikers, you can specify a machine in the moniker path e.g.

GetObject(“Winmgmts://MySQLServer/MicrosoftSQLServer”)

and then specify the MSSQL_Database class using the InstancesOf function.

Once we have access to the database class, we can interrogate the databases for information such as the Collation,
or the space available in the database. The database class also has a number of methods, including Checkpoint,
which issues a database checkpoint in the same way as the CHECKPOINT statement in T-SQL, and Shrink, which
mirrors the effect of shrinking the database files using SQL Enterprise Manager, although strangely you cannot grow
the database through WMI. The WMI class provides some very useful methods such as EnumerateStoredProcedures,
which allows you to check all the stored procedures in a database and return the names of those stored procedures
that contain a specified string, and Transfer, which moves schema and/or data information from one database to
another. You can also use the Database class to create new databases through its’ Create method, however this is
quite an involved process and I would suggest that you are better served by using one of the more conventional
means of creating databases. Readers who are curious should consult one of the sample programs installed with SQL
Server, dbcreate.vbs, which is a VBScript program that uses the WMI objects to create a new database – it can be
found in the DevTools\Samples\WMI folder.

What if we want to manage a table or a view, or even an individual column inside a database on a server? WMI
makes this possible and you still have the ability to access these objects directly – the only thing you need to change
is your connection parameter, using what the documentation refers to as a “multiple property key”. For instance to
connect to the Products table in the Northwind database, a GetObject call would look like this:

GetObject(“Winmgmts:\\.\root\MicrosoftSQLServer.” & _
     “MSSQL_Table.SQLServerName   = ‘MySQLServer’, “ & _
     DatabaseName = ‘Northwind’, Name=’dbo.Products’)


ADMINISTRATIVE Classes
The other major set of classes in the SQL provider perform administrative functions, and hence I group them under
the heading of “Administrator Classes”. This, for me, is where the ‘Management’ part of Windows Management
Instrumentation really kicks in – there are some very powerful, not to mention dangerous, methods exposed here.

I mentioned that the MSSQL_SQLServer class allows us to programmatically perform backup and restore operations,
however it’s a little more involved than just calling one method on the class. The Backup method uses a ‘helper’ class
called MSSQL_BackupSetting, which specifies which database to back up, whether to do a complete or incremental
backup etc. To use this functionality, you need to create an empty instance of the MSSQL_BackupSetting class,
populate its’ properties, and then pass it into the Backup method of the SQLServer object.

Dim   locBackupServer As SwbemLocator
Dim   svcBackupServer As SWbemServices
Dim   wmoBackupSettings As SwbemObject
Dim   ostBackupServers As SWbemObjectSet
Dim   wmoBackupServer As SWbemObject

Set locBackupServer = New SwbemLocator

Set svcBackupServer = locBackupServer.ConnectServer _
     (strServer:=’MySQLServer’, _
     strNamespace:=’root\MicrosoftSQLServer’)

Set wmoBackupSettings = svcBackupServer.Get & _
     (“MSSQL_BackupSetting”)
With wmoBackupSettings
     .Database = ‘pubs’
     ‘Device should be a reference to an instance _
          of MSSQL_BackupDevice
     .Device = ‘MyBackupDevice’
     .FormatMedia = True
     ‘TargetType allows us to backup the full _
          database, differential backup _
          or backup the transaction log
     .TargetType = 0
End With

Set ostBackupServers = svcBackupServer.InstancesOf _
     (“MSSQL_SQLServer”)

For Each wmoBackupServer In ostBackupServers
     WmoBackupServer.SQLBackup Backup:=wmoBackupSetting
Next wmoBackupSetting

In the same way, you can do a programmatic restore by creating an instance of MSSQL_RestoreSetting and passing
it into the SQLRestore method of the server class. The MSSQL_RestoreSetting instance can also be used in a call to
the SQLVerify method to ensure that you have a complete backup which can be restored.

The other methods of the MSSQL_SQLServer class worth mentioning in this section are the KillDatabase and
KillProcess methods. The KillDatabase method will drop a database immediately! Let me say that again –
KillDatabase drops a database immediately, ignoring any state the database may be in and regardless of how many
users are connected to it. Needless to say, you should be careful with this function – it’s probably the most powerful
method exposed by the provider.
Of more use is the KillProcess which, as astute readers have already guessed, allows you to kill a SQL Server
process (NB This is a SQL Server process, not an operating system process – SQL processes typically represent user
connections, as shown by the sp_who stored procedure). The KillProcess method takes one parameter, the process
id of the process that you want to kill (which you can get from sp_who). This method allows you to clear blocks in the
database by killing the blocking process.

As you might expect, processes have their own class – MSSQL_Process. This class gives you a great deal of useful
information about individual processes, including items such as whether or not it is blocked and the ID of the process
which is blocking it, although you can’t go the other way and tell from a process which processes it is blocking. You
can also determine the client application and client workstation a process is associated with, view the commands that
the process runs, and monitor the memory usage of a process. One useful application of this class would be to use
the event notification capabilities of WMI to watch for runaway processes that use large amounts of memory and/or
block many other processes, and kill them automatically.

WMI allows you to administer SQL Servers’ startup parameters by providing the MSSQL_RegistrySetting class, which
provides an object-oriented view on the values stored in the Registry. Using this class you can control which services
are started when the server is booted, where SQL keeps its’ error log, and you can control the network settings for the
server e.g. which Named Pipe or TCP port to use.

Dim   locSettings As SwbemLocator
Dim   svcSettings As SwbemServices
Dim   ostSettings As SwbemObjectSet
Dim   wmoSettings As SWbemObject
Dim   strSettingsInfo As String

Set locSettings = New SWBemLocator

Set svcSettings = locSettings.ConnectServer _
     (strNamespace:=”MicrosoftSQLServer”)

Set ostSettings = svcSettings.InstancesOf _
     (“MSSQL_RegistrySetting”)

‘Show me the paths from the Registry
For Each wmoSettings In ostSettings
     StrSettingsInfo = “”
     StrSettingsInfo = “Backup Directory: “ & _
       wmoSettings.backupDirectory & vbNewLine
     StrSettingsInfo = strSettingsInfo & _
       “Error Log Path: “ & wmoSettings.ErrorLogPath _
     & vbNewLine
     StrSettingsInfo = strSettingsInfo & _
       “Master Database Path: “ & _
       wmoSettings.MasterDBPath & vbNewLine
     StrSettingsInfo = strSettingsInfo & “Data Root: “ _
           & wmoSettings.SQLDataRoot & vbNewLine
      StrSettingsInfo = strSettingsInfo & “SQL Root: “ _
           & wmoSettings.SQLRootPath & vbNewLine

     Msgbox strSettingsInfo, vbInformation, ”SQL Server Paths”
Next wmoSettings

Another class that is useful for monitoring purposes is MSSQL_TransactionLog, which exposes several properties
useful to the DBA. Among these are the LastBackup property, which records the date of the last backup performed
against the transaction log, and the SpaceAvailableInMB property, which tells you how much free space exists in the
transaction log, down to two decimal places although it’s difficult to imagine why you would need that kind of precision.
Again, the event notification mechanisms in WMI would be useful here to provide an automatic monitor to pick up on
the transaction log getting filled up – another indication of an errant process somewhere. The MSSQL_TransactionLog
has one method, Truncate, which marks committed records as archived, allowing the space in the log to be re-used.
However the documentation points out that the transaction log is normally truncated during the log backup process,
going as far as to say that if you use a log backup strategy, then ‘the Truncate method should never be called’ since it
will lead to unpredictable results and almost certainly invalidate your backups.

ASSOCIATION Classes
I mentioned association classes earlier – they are classes whose properties are other classes, and exist to denote a
relationship between two or more classes, not unlike the link table in a many-to-many relationship. Association classes
work generally like any other WMI class – they can be queried or enumerated, and some may have extra properties or
methods describing the relationship that the class encapsulates. However most of the time they have just two string
properties that give you the WMI object paths to either end of the relationship – the names of the properties vary
according to the type of relationship that the association class describes. You can then use the object path in the
Services’ Get_ method to return the particular object instance.

Examples of association classes in the SQL WMI provider are databases related to logins, or tables related to triggers,
or the one I mentioned at the top of the article which relates SQL Server logins to Win32_UserAccounts from the base
WMI provider. There are several ways to get to associations – you can use the Services object to return an ObjectSet
of all the instances of an association class:

Dim   locLoginAccounts As SwbemLocator
Dim   svcLogInAccounts As SwbemServices
Dim   ostLoginAccounts As SwbemObjectSet
Dim   wmoLoginAccount As SwbemObject
Dim   ostLoginAssociators As SwbemObjectSet
Dim   wmoAssociator As SwbemObject

(…)

Set ostLoginAccounts = svcLoginAccounts.InstancesOf _
     (“MSSQL_LoginWin32UserAccount”)

For Each wmoLoginAccount In ostLoginAccounts
     Msgbox wmoAssociator.Properties_.(“Antecedent”).Value
     MsgBox wmoAssociator.Properties_(“Dependent”).Value
Next wmoLoginAccount

Or you can use the Associators_ property on an individual object, which provides an ObjectSet containing the
instances of each object that the source object is linked to. You can then iterate through the ObjectSet and pick out
the instances you want to examine in code.

Dim   locServer As WbemScripting.SWbemLocator
Dim   svcServer As WbemScripting.SWbemServices
Dim   ostServers As WbemScripting.SWbemObjectSet
Dim   wmoServer As WbemScripting.SWbemObject
Dim   ostServerAssociators As WbemScripting.SWbemObjectSet
Dim   wmoServerAssociator As WbemScripting.SWbemObject

Set ostServers = svcServer.InstancesOf _
     (strClass:="MSSQL_SQLServer")

For Each wmoServer In ostServers

Set ostServerAssociators = wmoServer.Associators_

     For Each wmoServerAssociator In ostServerAssociators
          MsgBox wmoServerAssociator.Path_.DisplayName
     Next wmoServerAssociator
Next wmoServer
Another way to return the associations of an object is to use a Windows Query Language (WQL, pronounced ‘wickle’)
query. The WQL spec gives you a specific ‘ASSOCIATORS OF’ query in place of a SELECT query, which you can
then use with the ExecQuery method of a Services object. The ASSOCIATORS OF query is too complex to go into
detail here, but it is both very powerful and quite limited. Powerful in that you can filter the set of returned objects in a
number of different ways including returning only the class names or classes that play a particular role in associations,
limited in that the only comparison operator is equals (=) and there is no support for AND or OR unlike regular SQL.
Interested readers can find a more in-depth treatment of ASSOCIATORS OF in the MSDN Library in the WMI section
of the Platform SDK documentation.

SUMMARY
WMI is the coming thing and I expect to see more server products from Microsoft supporting it. The SQL Server
provider is a useful addition to the SQL product – it provides a lot more functionality than SQL-DMO, and has the
advantage that it only needs to be installed on the server; client programs just connect to it through (D)COM and do
not need to have WMI installed. I think it’s easier to use than the default WMI provider, probably because it has fewer
classes and is therefore easier to understand. One criticism I do have is with the documentation that ships with the
provider; whilst it documents all the classes for the SQL Server provider, it has no examples for using the classes. My
other criticism is that there is no public download available from the MSDN website – you could use WMI to build a
strong admin tool, especially for MSDE, but you still need the SQL 2000 CD, essentially cutting off SQL 7 users and
DBAs from what is a good product.

** sidebar **
Management Instrumentation in Hungarian

Microsoft haven’t provided any Hungarian prefixes for the WMI objects in their documentation, so in the course of
writing articles on WMI, I’ve created my own, which I’m presenting here for your use. For the uninitiated, Hungarian
coding is the practice of prefixing object names with a string of letters (usually three) indicating the type of the object.
(For clarity, I’ve omitted the Swbem prefix of the object names here)

•   Locator – loc
•   Services – svc
•   ObjectSet – ost
•   Object – wmo
•   ObjectPath – pth
•   NamedValue – nvl
•   NamedValueSet – nst
•   Method – met
•   MethodSet – mst
•   EventSource – evs
•   Sink – snk
•   Security – sec
•   Privilege – prv
•   PrivilegeSet – pvs
•   Qualifier – qfr
•   QualifierSet – qst
•   Property – wmp
•   PropertySet – pst

** End sidebar **

Philip Pursglove, MCSD, is a developer for the Willis Group, based in Ipswich, UK. Philip has been working with VB since
version 3.0, and is currently working to understand the implications of .NET. When he isn't working, he enjoys cooking, playing
his trumpet and listening to jazz. He can be reached at pursglovep@willis.com.