rior to SQL Server 2005, Microsoft’s SQL Distributed Management Objects (SQL-DMO) offered the most efficient way to manage SQL Server programmatically. SQL-DMO supported a COM-based interface that you could use to discover and manipulate SQL Server objects. With SQL Server 2005 Microsoft provided a new management framework called SQL Server Management Objects (SMO) that provides more extensive management capabilities neatly bundled into a .NET Framework 2.0 assembly. While you can still use SQL-DMO to manage parts of SQL Server 2005, SMO provides more capabilities, supports most new features in SQL Server 2005, and is optimized for better performance.
Microsoft defines SMO as “objects designed for programmatic management of Microsoft SQL Server.” SMO gives developers a comprehensive grasp of all the management capabilities of SQL Server: tables, columns, indexes, stored procedures, triggers, Service Broker, snapshot databases, snapshot isolation, backup and restore, file and filegroup management, and much more. This article covers some of SMO’s features by first discussing several SMO-related programming topics and then analyzing a sample SMO application. The sample application will demonstrate how to use SMO to discover SQL Server objects (and their properties) and generate T-SQL scripts for them.
SMO Basics
SMO is written in .NET Framework 2.0, so you must be using Visual Studio 2005 or later to create SMO applications. You can run SMO applications on Windows Server 2003, Windows XP, Windows 2000, and Windows NT (with SP5 and beyond). In addition, you can use SMO to connect to SQL Server versions 7, 2000, and 2005, but SMO does not support databases set to compatibility levels 60, 65, and 70. SMO degrades as gracefully as possible, but obviously, when you use it with older versions of SQL Server, many of its more advanced capabilities will not be available.
You will need the following assemblies:
- Microsoft.SqlServer.ConnectionInfo
- Microsoft.SqlServer.Smo
- Microsoft.SqlServer.SmoEnum
- Microsoft.SqlServer.SqlEnum
If you don’t see those assemblies in Visual Studio’s standard .NET references list, you may be able to browse to them in your C:Program FilesMicrosoft SQL Server90SDKAssemblies directory. If you don’t have the components, you can install them with the SQL Server Client Tools that ship with any version of SQL Server 2005.
In each code file that references any of the SMO objects, you will want to import the following namespaces to ease the tasks of referencing SMO classes:
Imports Microsoft.SqlServer.Management Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.CommonFor readability, it’s best to reference both the Microsoft.SqlServer.Management.Smo namespace and the Microsoft.SqlServer.Management namespace (the latter is a superset of the former) as shown above because they contain several classes whose names become ambiguous with classes defined in other automatically referenced namespaces in Windows Forms applications. In such cases, including both SMO namespaces lets you reference ambiguous SMO classes using the Smo namespace as a disambiguating prefix, for example, Dim objView as Smo.View.
Connecting to SQL Server Using SMO
SMO provides all the normal flexibility in connecting to SQL Server; it can connect to local and remote servers and use both SQL and Windows authentication. The simplest case occurs when connecting to the default instance of a local SQL Server service using Windows authentication. To do that, use the default constructor of SMO’s Server class with no parameters, as shown in line 30:
05 Imports Microsoft.SqlServer.Management 10 Imports Microsoft.SqlServer.Management.Smo 15 Imports Microsoft.SqlServer.Management.Common 20 Dim objServer as Server 25 Dim objSvrConn as ServerConnection '**************************************************** ' establish connection to default ' instance of local SQL Server using ' Windows authentication '**************************************************** 30 objServer = New Server()To establish a connection to a named instance of SQL Server using Windows authentication, whether the server is local or remote, use the Server constructor that accepts the server instance name, as shown in line 35:
'**************************************************** ' establish connection to remote ' instance of SQL Server using ' Windows authentication '**************************************************** 35 objServer = New Server("ServerNameInstanceName")For all other connections, including those using SQL authentication, populate a ServerConnection object with the server name, username, and password for the connection, and pass the ServerConnection object into the Server constructor as shown in lines 40-60:
'**************************************************** ' establish connection to SQL ' Server using SQL authentication ' using the ServerConnection class '**************************************************** 40 objSvrConn = new ServerConnection() 45 objSvrConn.ServerInstance = "ServerNameInstanceName" 50 objSvrConn.Login = "SMOSampleUser" 55 objSvrConn.Password = "SMOSamplePassword" 60 objServer = New Server(objSvrConn) 65 For Each objDB as Database in objServer.Databases 70 Debug.WriteLine(objDB.Name) 75 Next| Author’s Note: In an effort to improve the performance of SMO applications, connections to SQL Server are not established until the first call to the connection?which means you won’t get exceptions until the first call is made. For example, the preceding code creates the Server object on line 60, but if an exception occurs when connecting to the specified SQL Server instance, the exception won’t be thrown until line 65 executes. |
In some cases, your SMO application will need to provide the user with a list of available SQL Server instances from which to select. SMO provides a shared SmoApplication.EnumAvailableSqlServers method that returns a list of SQL Server instances detected on the network. The parameterless overload of this method queries all SQL Server instances on the network and returns them in a DataTable. A second overload of this method accepts a Boolean parameter that restricts the list of SQL instances to local instances (pass in True to get local instances only and False to get network instances only). A third overload restricts the list of returned SQL instances to a specified computer name you pass as the parameter. The code example below passes the value False to the EnumAvailableSQLServers method (see line 30), resulting in a list of all SQL instances detected on the network:
05 Imports Microsoft.SqlServer.Management 10 Imports Microsoft.SqlServer.Management.Smo 15 Imports Microsoft.SqlServer.Management.Common 20 Dim dtServers as DataTable 25 Dim strServer As String '**************************************************** ' build a list of all SQL Server instances ' on the network '**************************************************** 30 objServers = SmoApplication.EnumAvailableSqlServers(False) 35 For Each objRow as DataRow in objServers.Rows 40 strServer = CStr(objRow("Server")) 45 If Not TypeOf objRow("Instance") Is DBNull _ AndAlso CStr(objRow("Instance")).Length > 0 Then 50 strServer += "" & CStr(objRow("Instance")) 55 End If 60 Debug.WriteLine("SQL Server: " & strServer) 65 NextIn the loop beginning in line 35 of the preceding code, notice that the returned server list includes both a server name and an instance name. When passing a server name into the Server class constructor, be careful to include an appropriate instance name, if applicable. Lines 40-55 in the preceding code demonstrate the proper construction of a fully qualified SQL instance name using the information provided by SMO’s EnumAvailableSqlServers method.
One factor that can affect server discovery is security limitations. Security has become a paramount concern for most organizations, including Microsoft, so most modern desktop PCs have firewalls that normally block all broadcast traffic. If you’re running such a firewall or if broadcast traffic has been blocked on your network through other means, the ability of EnumAvailableSqlServers to discover instances of SQL Server running on other computers will be very limited. In most cases, you will be limited to the discovery of SQL instances running on your local PC.
Latency in receiving responses from the broadcast query on the network is another factor that can limit the discovery capabilities of EnumAvailableSqlServers. In such cases, you can call EnumAvailableSqlServers more than once to find additional servers that were slower to respond to the broadcast.
Retrieving Lists of Objects from SMO
In many cases, SMO provides two ways to fetch lists of items from SMO. For example, to build a list of Table objects contained in a database, you can either:
- Retrieve the group of objects via a property and receive a collection of objects.
- Call the EnumObjects method, which returns a DataTable containing the same information (though not the same functionality) in tabular format.
In general, calling the collection property yields more useful results, because you can query the individual objects further for more information. For example, if you retrieve a collection of Table objects, you can query each Table object in the collection for other properties, such as the column list, indexes, and triggers associated with the table.
In contrast, the EnumObjects method is often more convenient when you want to populate a grid or other data-aware object. The following code shows the process of retrieving a list of tables in a database using both methods:
05 Imports Microsoft.SqlServer.Management 10 Imports Microsoft.SqlServer.Management.Smo 15 Imports Microsoft.SqlServer.Management.Common 20 Dim objServer as Server 25 Dim objDatabase As Database 30 objServer = New Server() 35 objDatabase = objServer.Databases("SMOSample") '**************************************************** ' Use Tables property to get list of tables '**************************************************** 40 Debug.WriteLine("Tables in Database " & objDatabase.Name) 45 For Each objTable As Table in objDatabase.Tables 50 Debug.WriteLine("Table: " & strServer) 55 Next '**************************************************** ' Use EnumObjects method to get list of tables '**************************************************** 60 Debug.WriteLine("Tables in Database " & objDatabase.Name) 65 For Each objRow As DataRow In _ m_objDatabase.EnumObjects(DatabaseObjectTypes.Table, _ Smo.SortOrder.Name).Rows 70 Debug.WriteLine("Table " & CStr(objRow("Name"))) 75 NextSMO Class Hierarchy
Most SMO objects will seem intuitive after you begin coding an SMO application. At the highest level are the connection methods. After establishing a connection, you’ll find methods for managing service level attributes, such as retrieving a list of databases and logins, and for killing SQL processes. Each Server class contains a collection of Database classes, each of which contains collections of tables, stored procedures, views, etc. In most cases, the class hierarchy closely mimics the hierarchy models you’re probably familiar with from SQL Server Management Studio and SQL Enterprise Manager.
Sample Application
 | ? |
| Figure 1. Connection Methods: The SQL Connection dialog supports all the connection methods supported by SMO. |
The downloadable sample application demonstrates how to navigate the SMO hierarchy programmatically to produce a tree-like structure similar to that displayed in SQL Server Management Studio. When you launch the sample application, you’ll see an empty form. Click the Connect button to open the SQL Connection dialog shown in Figure 1. The sample application supports all the connection methods described above.
When the SQL Connection form first opens, it uses SMO’s shared EnumAvailableSqlServers method to find instances of SQL Server running on the network. Next, it uses the resulting list to populate the Server combo box control on the form, using code similar to that which you saw earlier in the article.
Select a SQL instance and click the Connect button to create a Server object, connect to the selected server, and retrieve the list of databases from that server to populate the Database combo box (shown in Figure 2). Here’s the code:
05 For Each objDB As Database In Me.SMOServer.Databases 10 Me.comboDatabase.Items.Add(objDB.Name) 15 Next
|
|
Select a database from the list, and then click the OK button. The SQL Connection dialog closes and the main form populates its tree view with tables, columns, keys, constraints, indexes, views, and stored procedures from the selected database (see Figure 3).
Performance Issues
As you will recall, one reason to use SMO instead of DMO is increased performance. In addition to delaying connections to SQL Server, SMO has increased performance by limiting the amount of information retrieved for each of the objects provided in the framework. SMO loads only a minimum set of properties when you create an object; in other words, the objects are only partially initialized. If you later attempt to read an uninitialized property, SMO automatically makes a request to the server to retrieve the property details. Used improperly, however, you can quickly lose the performance gain provided by partially initialized objects.
The sample application demonstrates an example of this kind of performance loss. The application doesn’t show system objects by design; instead, it queries the IsSystemObject property of each object to determine whether to display the object. Unfortunately the application took a long time to load even a very simple schema into the tree view after this feature was added because the IsSystemObject property is not among the properties fetched by default, so querying the property on every object resulted in individual calls back to SQL Server to retrieve this property for each object.
Thoughtfully, Microsoft lets you alter the set of field values initialized by default. For example, to make the IsSystemObject one of the default initialized fields when creating objects, the Server class exposes a SetDefaultInitFields method. The following code shows how to use the SetDefaultInitFields method to fetch the IsSystemObject property for the Table, View, and StoredProcedure classes. Notice that you must request the property name (IsSystemObject, in this case), for each type of object you plan to query it from. The sample application checks the IsSystemObject property for tables, views, and stored procedures, so it requires three calls to SetDefaultInitFields:
05 m_objServer.SetDefaultInitFields(GetType(Table), "IsSystemObject") 10 m_objServer.SetDefaultInitFields(GetType(Smo.View), "IsSystemObject") 15 m_objServer.SetDefaultInitFields(GetType(StoredProcedure), "IsSystemObject")After adding that code, the sample application loaded a database schema much faster.
Default Constraints and Primary Keys
Unlike check constraints, which exist at the table level in the SMO hierarchy, default constraints are properties of the table columns. The sample application populates a list of DefaultConstraint objects as it traverses the list of columns for each table. Then, after adding the table columns to the tree view, it adds the list of DefaultConstraints:
05 Dim aobjTableDefaultCons As New List(Of DefaultConstraint) . . . 30 For Each objTableColumn As Column In objSMOTable.Columns . . . 55 If Not IsNothing(objTableColumn.DefaultConstraint) Then 60 aobjTableDefaultCons.Add(objTableColumn.DefaultConstraint) 65 End If . . . 85 NextThe sample application treats primary keys similarly, which are not a property of the SMO table class. Instead, you can discover the primary key of a table by looping through a table’s indexes and querying the IndexKeyType property of each index. An index with its IndexKeyType set to DriPrimaryKey is the primary key, or part of the primary key, for that table. Here’s an example:
05 For Each objIndex As Index In objSMOTable.Indexes . . . 30 If objIndex.IndexKeyType = IndexKeyType.DriPrimaryKey Then . . . ' This index is the primary key for the table . . . 55 End If . . . 85 NextScripting SQL Server Objects
Until now, this article has focused on discovering SQL objects using SMO, but SMO is also useful for generating the T-SQL scripts that define many SQL Server objects. In most enterprises, database administrators use SQL Server Management Studio to generate scripts so they can push schema changes from one database to another. To demonstrate SMO’s script-generation features, the tree nodes for the tables, views, and stored procedures in the sample application’s tree view have a context menu that you can use to script the object. After creating the script using SMO, the sample application places the script on the clipboard.
Using the defaults provided by SMO, you generate an object’s script simply by calling the Script method on that object. The default script is generally very simple, including only the object creation code. For example, here’s a table-creation script generated from the sample application using the default scripting options:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[PeopleIKnow]( [FriendID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LastName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Nickname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Friend] [int] NULL, [College] [int] NULL ) ON [PRIMARY] GO| Author’s Note: The output from the Script method does not include the GO statements shown above. Instead, the Script method returns a StringCollection where each string in the collection contains one of the statements shown above. The sample application makes the script more usable by adding GO statements after each string in the StringCollection, similar to the behavior of SQL Server Management Studio. |
Such basic object-creation scripts often aren’t sufficient; most scripts will also need to include script to create related objects. For example, a table-creation script will also need to include indexes, permissions, constraints, etc. To modify the default scripting behavior of SMO, you populate a ScriptingOptions object and pass it as a parameter to the object’s Script method, as in the example below:
05 Dim strc As StringCollection 10 Dim objScriptingOptions As ScriptingOptions . . . 25 objScriptingOptions = New ScriptingOptions 30 objScriptingOptions.ClusteredIndexes = True 35 objScriptingOptions.NonClusteredIndexes = True 40 objScriptingOptions.DriAll = True 45 objScriptingOptions.Indexes = True 50 objScriptingOptions.IncludeDatabaseContext = True 55 objScriptingOptions.Permissions = True . . . 75 strc = objTable.Script(objScriptingOptions)You can find complete documentation for the ScriptingOptions class on MSDN. Listing 1 shows a script generated for the same table shown earlier, but with the scripting behavior modified to include indexes, permissions, constraints, and so on.
Perhaps you have found that SQL Server Management Studio, while very useful, is lacking some essential tools you need to manage SQL Server in your enterprise. For example, one common complaint about SQL Server Management Studio is that its scripting capabilities don’t produce both DROP and CREATE statements in the same script. Most developers who script schema changes and push them to other platforms find they need to build both DROP and CREATE scripts for most objects. As you’ve seen, you can use SMO to do that as well as build other custom applications tailored to the specific needs of your enterprise.
