RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


SQL Server Management Objects: Lessons from the Wild

Harness the power of SQL Server Management Objects to create, document, and manage your SQL Server databases.

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 Files\Microsoft SQL Server\90\SDK\Assemblies 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("ServerName\InstanceName")
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 = "ServerName\InstanceName"
   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.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date