SQL Server Management Objects: Lessons from the Wild

SQL Server Management Objects: Lessons from the Wild

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.Common

For 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 Next

In 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:

  1. Retrieve the group of objects via a property and receive a collection of objects.
  2. 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 Next

SMO 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
Figure 2. Listing Databases: After connecting to a server, the SQL Connection dialog populates the databases available on the selected server.
Figure 3. Database TreeView: After connecting to a server, the application retrieves some basic information about the selected database and displays that in a tree view.

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 Next

The 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 Next

Scripting 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.

Share the Post:
Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular

XDR solutions

The Benefits of Using XDR Solutions

Cybercriminals constantly adapt their strategies, developing newer, more powerful, and intelligent ways to attack your network. Since security professionals must innovate as well, more conventional endpoint detection solutions have evolved

AI is revolutionizing fraud detection

How AI is Revolutionizing Fraud Detection

Artificial intelligence – commonly known as AI – means a form of technology with multiple uses. As a result, it has become extremely valuable to a number of businesses across

AI innovation

Companies Leading AI Innovation in 2023

Artificial intelligence (AI) has been transforming industries and revolutionizing business operations. AI’s potential to enhance efficiency and productivity has become crucial to many businesses. As we move into 2023, several

data fivetran pricing

Fivetran Pricing Explained

One of the biggest trends of the 21st century is the massive surge in analytics. Analytics is the process of utilizing data to drive future decision-making. With so much of

kubernetes logging

Kubernetes Logging: What You Need to Know

Kubernetes from Google is one of the most popular open-source and free container management solutions made to make managing and deploying applications easier. It has a solid architecture that makes