Can We Manage SQL Server 2000 With WMI and Visual Basic.NET?
The previous article mainly concentrated on fetching information available in SQL Server 2000. This article explains how to manage SQL Server 2000 using WMI together with Visual Basic.NET language.
A downloadable file for this article is available here.
The sample downloadable solution (zip) is entirely developed using Visual Studio.NET 2003 Enterprise Architect on Windows Server 2003 Standard Edition together with SQL Server 2000 Enterprise Edition. But, I am confident that it would work with other versions of Windows (which support .NET 1.1) versions as well.
Is SQL Server 2000 service started on your computer?
I strongly suggest you refer to my previous article “Can we integrate Visual Basic.NET, SQL Server 2000 and WMI?” before proceeding through this article. In my previous article, I mainly focused on retrieving SQL Server database objects using WMI. Now, in this article, I shall introduce you to managing a SQL Server database using WMI together with Visual Basic .NET.
Before managing the database, generally every developer would check whether the instance of SQL Server started running or not. Everyone knows how to check it using the Service Manager available in SQL Server. Now we shall achieve almost the same thing using the following code:
Private Sub btnStatus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStatus.Click
Dim PrivateLateBoundObject As ManagementObject
'PrivateLateBoundObject = New ManagementObject(Nothing, New ManagementPath("\\SERVER\root\CIMV2: Win32_Service.Name=""MSSQLServer"""), Nothing)
PrivateLateBoundObject = New ManagementObject(Nothing, New ManagementPath("\\SERVER\root\MicrosoftSQLServer: Win32_Service.Name=""MSSQLSERVER"""), Nothing)
From the above code, you can understand that I am creating an object (PrivateLateBoundObject) based on the “ManagementObject” class. The “ManagementPath” starts with “\\yourhostname”. There exists a WMI class called “Win32_Service” within the “MicrosoftSQLServer” namespace (which comes onto the scene after you installed the “WMI SQL Server Administration Provider”).
In another scenario, you need not use only the “MicrsoftSQLServer” namespace in WMI; you can directly use “CIMV2” as well (as commented in the coding). If you don’t install “WMI SQL Server Administration Provider” (as specified in my previous article), you will not be able to work with the “MicrosoftSQLServer” namespace. But you would still be able to work with “CIMV2”, which comes with default installation of the Windows OS.
There exists a WMI property called “Started” (of type Boolean) which is used to check the status of the Microsoft SQL Server 2000 service. It returns “true” if the service is running (or started) or else returns “false” if it is not.
Everybody knows that we can start/stop the SQL Server 2000 service in several ways. The common methods in doing so include the following:
Using the Service Manager
Using the “services” snap-in of “Computer Management”
Using the “NET START/STOP” at DOS prompt
Using the SQL-DMO based application
Using the WMI
The last method, “Using the WMI,” has several meanings. WMI can be used not only with SQL Server 2000, but also with several other services. But now, we will concentrate on WMI with “specific” to SQL Server namespace, using only Visual Basic.NET.
The following code would make the SQL Server 2000 server start:
Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click
Dim PrivateLateBoundObject As ManagementObject
PrivateLateBoundObject = New ManagementObject(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer: MSSQL_SQLServer.Name=""(local)"""), Nothing)
And here, the script is quite different from any of the scripts available in my previous article. In the above script, I started working with “InvokeMethod”, which is used to execute a WMI method dynamically! So the following is the most important statement within the above code:
The line above starts the MS SQL Server 2000 service immediately. If it is already started and you still try to start it, I don’t think you will run into a problem. Anyway, try checking for yourself.
And I hope everyone can easily figure out how to stop the MS SQL Server 2000 service. The following code does the same:
Private Sub btnStop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStop.Click
Dim PrivateLateBoundObject As ManagementObject
PrivateLateBoundObject = New ManagementObject(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer: MSSQL_SQLServer.Name=""(local)"""), Nothing)
Now we come to dealing with SQL Server logins. How do you retrieve the “logins” available in SQL Server 2000 using WMI? Actually it is simpler than you might have expected. Let us go through the following code:
Private Sub btnListLogins_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnListLogins.Click
Dim PrivateLateBoundClass As ManagementClass
PrivateLateBoundClass = New ManagementClass(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer: MSSQL_Login"), Nothing)
Dim logins As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
Now, I am working on the “ManagementClass” rather than the “ManagementObject”. Even though I didn’t specify that it needs to connect to “(local)”, it would automatically get connected to the local database!
“MSQL_Login” is another WMI class I used now (in the above code), to retrieve the entire set of logins existing in the local SQL Server 2000 instance. Since there may exist more than one login, I need to work with the “collection” (which is why I used “ManagementObjectCollection”). The following is the loop, which helps us to retrieve all the “user logins” available in SQL Server 2000 automatically.
Dim logins As ManagementObjectCollection = PrivateLateBoundClass.GetInstances()
“GetInstances” now would retrieve all “user login” instances. The loop iterates through each “user login” instance, and appends the values of “SQLServerName” and “Name” (user name or login name), which are WMI properties, to the string “sLogin”. Finally, it displays a message box with the string (list of user logins) we framed.
The previous section gave you only the list of logins available through your SQL Server 2000 instance. Now, I would like to extend this to modifying the password of a particular SQL Server login using WMI. Do you find this very interesting? Good, then let us follow the code now:
Private Sub btnChangePassword4Login_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnChangePassword4Login.Click
Dim PrivateLateBoundObject As ManagementObject
PrivateLateBoundObject = New ManagementObject(Nothing, New ManagementPath("\\server\root\MicrosoftSQLServer: MSSQL_Login.SQLServerName=""(local)" ",Name=""sa"""), Nothing)
Dim inParams As System.Management.ManagementBaseObject = Nothing
Dim outParams As System.Management.ManagementBaseObject = PrivateLateBoundObject.InvokeMethod("SetPassword", inParams, Nothing)
MessageBox.Show("Password changed succesfully")
End Sub
From the above code, if you observe the “ManagementPath”, I included even the “system administrator” login, which is necessary for the privileges (as part of SQL Server security). To change the password of an existing login, we need to provide the “new password” along with the “old password” as part of the parameters to “SetPassword” (which is a WMI method to modify the password of SQL Server login).
“inParams” (which is of type System.Managment.ManagmentBaseObject) is mainly used to pass parameters (input parameters) to the “SetPassword” method (WMI method) dynamically. Similarly, “outParams” generally contains the result of method execution.
In the above script, I again worked with “InvokeMethod”, which is used to execute a WMI method dynamically.
For those who are familiar with VBScript (especially working with WSH), you can still manage SQL Server 2000 using WMI, even without .NET Framework, using the following code:
sComputer = "server" sSQLServer = "(local)" Set oInstance = GetObject("winmgmts:{impersonationLevel=impersonate}//" & _ sComputer & "/" & "root/MicrosoftSQLServer:MSSQL_SQLServer.Name=" & _ Chr(34) & sSQLServer & Chr(34)) Set oOutParam = oInstance.Start() If oOutParam.ReturnValue = 0 Then WScript.Echo sSQLServer & " SQL Server started successfully" Else WScript.Echo "Starting of " & sSQLServer & " SQL Server failed with error " & _ oOutParam.Description
End If
The above is only a sample (especially for system administrators). You need to work with the “WScript” or “CScript” command at the MSDOS prompt to execute the above code. You can modify it according to your requirements.
I hope you can now confirm that we can use WMI to manage SQL Server 2000 together with the .NET framework. But one should consider that WMI is still an evolving technology. Day by day, WMI is getting updated with various features, opening a new world of “automated management” for system administration.
Since this is an introductory article, I could not go deep into “managing SQL Server 2000”. But you can expect a few more articles on this subject soon. Check this site regularly or sign up for the newsletter.
You can further enhance the coding I provided with all the necessary ingredients to polish it as an application. You can also shift this code to ASP.NET, which gives you the way to manage SQL Server 2000 using web applications together with WMI! Further areas of improvement include proper error or exception handling, using a few more flexible classes existing in WMI SQL Server Administration Provider, and so forth. You can get the entire list of classes (together with hierarchy) at MSDN online. Good luck.
Any comments, suggestions, bugs, errors, feedback, discussions etc. are highly appreciated at jag_chat@yahoo.com.
MS SQL Server 
Can We Integrate Visual Basic.NET, SQL Server 2000 and WMI?
