Earlier in this chapter you saw how to register a server in SQL Server Management Studio. What if you have to register hundreds of servers? You can certainly do it through the user interface or create an XML file by hand and import it into the user interface, but that is still a lot of work. You are likely to run into a similar situation when managing a large number of servers, databases, or database objects. For situations like this, you need the ability to automate common management tasks.
SQL Server 6.5 first introduced a COM-based application programming interface (API) called SQL-DMO, which allowed automating management tasks using languages such as VBScript, C++, and Visual Basic. This API was further extended in SQL Server 7.
SQL Server 2000 continued supporting SQL-DMO to automate all the tasks that you can do by using SQL Server Enterprise Manager. SQL Server 2005 still ships SQL-DMO COM libraries, but it is not upgraded to support newer features, such as Service Broker, Database Mail, Database Mirroring, and so on.
SQL-DMO is now being superseded by a .NET-based object library called SQL Server Management Objects (SMO). In addition to SMO, SQL Server 2005 introduces other .NET object libraries to automate replication tasks (Replication Management Objects [RMO]) and to automate Analysis Services management tasks (Analysis Management Objects [AMO]). This chapter provides details on SMO and WMI support in SQL Server 2005. Refer to Chapter 4 for an overview of WMI.
SQL Server Management Objects (SMO)
The SMO API's primary goal is to provide a powerful yet easy-to-use programming interface to automate administration, deployment, and maintenance of SQL Server. In addition to providing maximum coverage of SQL Server 2005 features, the SMO API contains several other improvements over SQL-DMO. SMO is designed from the ground up so that the API can perform better, scale well, and increases flexibility. The SQL Server Management Studio internally uses the SMO API to interact with SQL Server. Therefore, anything you can do by using Management Studio, you can automate by using SMO. You can use SMO from any .NET programming language, such as Visual Basic .NET or C#. In addition, SQL Server 2005 provides a COM wrapper over this .NET library so that you can use SMO from scripting and unmanaged languages to automate management tasks.
You can use SMO to manage SQL Server versions 2000 and 2005. SMO does not support databases with the compatibility level set to 60 or 65.
The following are some of the improvements introduced in the SMO API:
The SMO API provides three types of classes:
Because SMO is a .NET-based object library, you need the Microsoft .NET Framework version 2.0 or higher installed on the machine in order for your SMO application to work. SQL Server 2005 provides an MSI setup for SMO, SQL-DMO, and SMO COM wrappers that you can redistribute with your application.
Here is an example of using SMO in VBScript:
Dim smoSvr Dim svrName Dim smoDB svrName = "DDGXP\YUKON" Set smoSvr = CreateObject("Microsoft.SqlServer.Management.Smo.Server") smoSvr.ConnectionContext.ServerInstance = svrName For Each smoDB in smoSvr.Databases WScript.Echo smoDB.Name Next Set smoSvr = Nothing
This script lists all the databases on a server. You can update the svrName variable with your SQL Server 2005 instance name, save the script, and run it. The script displays a message box that lists each database on the specified server.
Here is another example of using SMO in VBScript. This VBScript creates a new database on the specified server.
Dim smoSvr Dim smoDB Dim svrName Dim dbName svrName = "DDGXP\YUKON" dbName = "TestDatabase" Set smoSvr = CreateObject("Microsoft.SqlServer.Management.Smo.Server") smoSvr.ConnectionContext.ServerInstance = svrName 'Create the database Set smoDB = CreateObject("Microsoft.SqlServer.Management.Smo.Database") smoDB.Parent = smoSvr smoDB.Name = dbName smoDB.Create() WScript.Echo "Done! Database created successfully." Set smoSvr = Nothing Set smoDB = Nothing
Before concluding this chapter, let's see an example of WMI support in SQL Server 2005.
Windows Management Instrumentation (WMI)
As described in Chapter 4, WMI provides an industry-standard programming interface for managing an enterprise environment. WMI provides a consistent way of managing hardware such as disk drives and software such as IIS Web server and SQL Server. (Refer to Chapter 4 for an overview of WMI.)
WMI support in SQL Server 2005 primarily allows you to do the following:
Not enough time to work on this one! It's ok. Here is a VBScript example that uses WMI to monitor DDL events in SQL Server 2005:
Type this VBScript text into a text file or use the WMIEventMonitor.vbs script file provided with the code download, update the WMI namespace to include your SQL Server 2005 instance name in place of YUKON, and then run the VBScript file by double-clicking it in File Explorer.
The period (.) in the WMI namespace indicates the local server. If you want to monitor SQL Server on a different machine, you type the machine name instead of a period. Also, the word YUKON in the WMI namespace is the SQL Server 2005 instance name. You should change it to your named instance; for default or unnamed instance, you can replace YUKON with the word MSSQLSERVER.
After you launch the VBScript file, click OK, and then start SQL Server Management Studio or a SQLCMD session and connect to the server specified in the WMI script. Then you should create a table by using the CREATE TABLE DDL or by using Object Explorer. Or you can alter or drop a table, and for each DDL action you perform on this server, the script shows a message box showing the database in which DDL action is performed, the object name in context, and the XML formatted DDL action. To end the monitoring, you launch Windows Task Manager and end the wscript.exe process.