15. SQL Server Management Objects

Introduction

SQL Server Management Objects, known as SMO, is an object model for SQL Server and its configuration settings. SMO-based applications use .NET Framework languages to program against this in-memory object model rather than sending Transact-SQL (T-SQL) commands to SQL Server to do so. SMO makes it very straightforward to create applications that manage SQL Server because it encapsulates specific knowledge of each version of SQL Server and T-SQL in its object model.

SMO also provides some capabilities for managing other SQL Server applications, such as Analysis Services, Notification Services, and Reporting Services, but this chapter focuses on SQL Server itself—that is, the database engine, and specifically classes in the Microsoft.SqlServer. Management.SMO namespace, though this namespace covers only a portion of the capabilities of SMO.

In this chapter, a reference to SQL Server may mean an instance of the SQL Server database engine (sqlserver.exe) running as a service or the SQL Server product itself; context will differentiate the usage. “Use a SQL Server,” for example, means to use an instance of sqlserver.exe loaded as a service.

SMO supports SQL Server 2000 and SQL Server 2005. A SMO-based application requires .NET Framework 2.0 on the machine running the SMO application. SMO is part client tool for SQL Server 2005 and part of the Feature Pack for Microsoft SQL Server 2005. It can be downloaded as a redistributable from http://www.msdn.com/sql. Browse to Downloads, Tools and Utilities page, and look for the SQL Server 2005 Feature Pack.

SMO views a machine that hosts SQL Server separately from SQL Server itself. A machine is represented by an instance of the ManagedComputer class, and a SQL Server is represented by an instance of the Server class. In this respect, SMO has two independent object models: one for a machine hosting some SQL Server applications and another for SQL Server. Figure 15-1 shows a ManagedComputer that hosts the various services we expect from SQL Server 2005: SQL Server itself, full-text search, and so on. A Server, on the other hand, represents a database engine that contains, among other things, databases.

Figure 15-1. ManagedComputer and Server

ManagedComputer—that is, Microsoft.SqlServer.Management. Smo.Wmi.ManagedComputer—is used to manage the configuration of a machine with respect to the services provided by SQL Server. It can be used, for example, to enable or disable a SQL Server instance or to change its network configuration. SQL Server Configuration Manager, provided as part of the client tools for SQL Server, uses ManagedComputer, for example, to enable database engines and manage network configuration.

Server—that is, Microsoft.SqlServer.Management.SMO.Server—is used to manage an instance of SQL Server itself, a database engine. SMO looks at SQL Server as a collection of objects; each database, table, login, and so on is looked at as an object. SMO-based .NET Framework 2.0 applications program against these objects to add or remove databases, logins, tables, and so on.

The ManagedComputer object model is independent of the Server object model. The ManagedComputer object model cannot be used to obtain a reference to an object in the Server object model and vice versa. Several other object models supported by SMO, such as a Restore object module, can be used to restore databases, Backup object model, and so on. This chapter will start by looking at the Server object model and later will look at the ManagedComputer object model.

Sqlservr.exe is the SQL Server application, and it runs as a Windows service. It is possible to run more than one copy of Sqlserver.exe, in which case each service has a different name. In any case, each copy is seen by SMO as a Server object. A SQL Server service may contain a number of databases, which in turn may contain multiple tables, and so on. Typically, the name of the service is the same as the name of the machine on which it is running. Figure 15-2 is a pictorial representation of an instance of SQL Server and its corresponding SMO object model in a client application.

Figure 15-2. SMO object model

The left side of Figure 15-2 shows a SQL Server (1) named Mfg_Svr, which contains a database (2) named Audit, which in turn contains a table (3) named Drops.

On the right side of Figure 15-2 is a code fragment of an application that is using SMO to manage SQL Server. The Mfg_Svr Server object (1) in the client application represents the instance of SQL Server. Note that the name of the server it represents, “Mfg_Svr,” is the parameter that is passed into the Server constructor. The Audit object (2) represents the database named Audit, and the Drops object (3) represents the table named Drops. Each of these is accessed by its name in a corresponding collection of objects.

SMO can be used to write custom applications that manage SQL Server in any .NET Framework language and manage SQL Server directly through connections to SQL Server or to generate SQL scripts that can be applied to SQL Server at a later time. SQL Server Management Studio is an example of an application that uses SMO in its implementation. Another example is Visual Studio 2005, which uses SMO to implement its Server Explorer and database projects.

Why Use SMO?

SMO has no special access to SQL Server. A T-SQL script or an application written using ADO.NET, ODBC, or OLE DB can do any management operation that SMO can do. So why use SMO?

One of the compelling features of SMO is that all the tools and techniques used to develop .NET Framework applications work for SMO; after all, it is just a set of class definitions in some assemblies, just like any other library for .NET Framework. The application shown in Figure 15-2 refers to the instance of SQL Server by the symbolic name Mfg_Svr and accesses the Audit database by referring to the Audit object, the Drops table by referring to the Drops object, and so on. A SMO-based application can easily work with any database object in the same way that it works with any other object in the application.

A SMO-based application could be a custom GUI application that manages the access rights of employees in a corporate database. SMO is sometimes thought of as a DBA technology because it can be used to automate the administrative tasks done by DBAs. SMO, however, is useful for clientside and middle-tier application developers, too. Most of these applications need, or can be enhanced by the addition of, management features that can be used by non-DBAs, and using SMO is a straightforward way to do this.

An example would be a third-party application that managed maintenance data for machinery in a factory. An application like this might need the ability to add a new table to the database when a new kind of equipment is put into service. The maintenance personnel would define the attributes of a new kind of equipment in a dialog box that would use SMO to create a table for it.

The developer of this application could just use ADO.NET and T-SQL to add this kind of capability, but that would require, in general, knowledge of T-SQL, server configuration details, or even hundreds of system stored procedures in SQL Server. SMO encapsulates all that knowledge into its object model.

Figure 15-3 shows the C# code for a program to list databases in SQL Server on the console using ADO.NET.

Figure 15-3. Listing databases with ADO.NET

The example in Figure 15-3 implements a simple task but does have a fair amount of ADO.NET overhead code just to get the names of the databases in an instance of SQL Server. The only parts that are really doing any work are the SqlCommand (1) and the WriteLine (2); the rest is just boilerplate. To construct SqlCommand, you need the specific knowledge that database names can be found in the sys.databases view, as well as the syntax and semantics of the T-SQL language; IntelliSense will give no hints on how to write a T-SQL query or on what tables and views are available to you.

Note that this program has a bug in it; it will not work if executed against an instance of SQL Server 2000. There is no sys.databases in this implementation of SQL Server. In general, programs like this need additional code to detect the version of SQL Server and then apply specific knowledge of those versions to the T-SQL code they contain.

Figure 15-4 shows the results of running the ADO.NET program that lists databases in SQL Server. The name of the SQL Server (1) is passed to the program on the command line; then the list (2) of databases found is sent to the console.

Figure 15-4. Running ADOList program

Figure 15-5 shows a program based on SMO that will produce the same results as the program in Figure 15-3.

Figure 15-5. SMO program to list databases

Notice that the SMO program is much smaller and that the foreach (1) and WriteLine (2) make up almost the entire program. Everything is done using only techniques familiar to .NET Framework programmers. IntelliSense works for any of the classes in .NET Framework, so you always get a reminder of what the actual names of things are. In addition, this program will work when run against SQL Server 2000 or SQL Server 2005.

SMO can also be used in the upcoming Microsoft Command Shell, called Monad. Figure 15-6 shows a Monad interactive script that is the equivalent of the programs shown in Figure 15-3 and Figure 15-5. It starts by initializing the $server variable (1) with a SMO server object. Then it iterates (2) through all the databases in the server and prints out their names (3). Note that at this writing, Monad was available only as a beta; Microsoft had not announced anything about its specific availability. Search for “Monad” on http://www.msdn.com for more information about Monad.

Figure 15-6. Monad script to list databases

Monad has its own kind of IntelliSense, as shown in Figure 15-7. The get-member cmdlet can display the members of any object. cmdlets are what Monad calls commands.

Figure 15-7. Monad IntelliSense

All the information that SMO provides is in the form of properties on objects, and all the functionality is accessed by methods on objects. This makes it fit in nicely with the languages used in .NET Framework.

Contrast the second two programs with the ADO.NET program in Figure 15-3. For ADO.NET, you need to know the T-SQL language and pretty much have in your head all the names of the database objects and stored procedures for each version of SQL Server to use ADO.NET. Things like Books Online, SQL Server Management Studio, and Enterprise Manager help in looking up names, syntax, and metadata, but they really don’t integrate directly with the client/middle-tier language tools as SMO does.

Wow! IntelliSense, just one language to learn, and a familiar programming infrastructure at your service . . . why would anyone use ADO.NET?

ADO vs. SMO

One of the classic problems of using an object model to represent a database is that the object model in memory is often much larger than the information you are trying to manage by using it. The ADO.NET program in Figure 15-3 brought back only the names of the databases, and it streamed them back at that, so at any instant, there really were only a couple of names in memory, not necessarily all of them. All things being equal, an ADO.NET program probably takes less space at runtime than a SMO program and likely runs faster.

In Figure 15-5, the line of code Server = new Server(arg[0]) made an object that represented an entire SQL Server instance. The line of code foreach(Database in server . . . made an object that represents a whole database. How big do you think these objects are?

The tradeoff is not quite as stark as it might seem, however. SMO is very aware of space problems that an object model in application memory can cause and makes sure that SMO minimizes its impact on the application that uses it and on SQL Server.

SMO has many features, such as partially loading object properties until needed, caching references to database objects rather than the objects themselves, and batching sequences of commands so that they can be done in a single round trip. All this can be controlled by the program that is using SMO.

By default, SMO will defer loading some information until you go to use it, but you can load all the properties for an object at the same time instead of doing a lazy load, if you want. Almost all this is completely transparent to the program using SMO. SMO is not your father’s object model.

But in the end, ADO.NET can do anything that SMO can do, and a well-thought- out, purpose-built, and possibly rather complicated T-SQL batch executed via ADO.NET can almost always outperform the corresponding SMO operation. ADO.NET versus SMO is an application/database impact versus development/maintenance time tradeoff. An ADO.NET application will usually require more time and effort to develop and maintain than a corresponding SMO application, but it will have less negative impact on the application and database.

This is why SMO is used to create applications that manage SQL Server. These kinds of applications are not going to be used by hundreds, or thousands, or more users at the same time to do OLTP operations, so their impact on the application itself or the database is not as important as the time it takes to develop and maintain them. The extra effort required to develop an ADO.NET solution is well worth it for updating an account in the general ledger by a typical OLTP application, but it really buys nothing for a typical management application.

SMO excels at applications that manage SQL Server. Unlike ADO.NET, it is not a general-purpose data access solution. It can be used only with SQL Server; it will not work with other databases, let alone other kinds of data stores. ADO.NET excels at applications that will be used by hundreds or thousands of users, or more, to store and retrieve data from SQL Server or other kinds of databases and data stores.

Object Model

SMO provides a rich object model that includes both SQL Server 2000 and SQL Server 2005. The object model for SQL Server contains many types of objects and includes a hierarchy composed principally of two kinds of objects: SqlSmoObject and SmoCollectionBase objects, with a single Server object, which is a SqlSmoObject, at its root. This hierarchy covers most of the objects developers are typically interested in, such as databases and tables.

Each object in the hierarchy has several properties, each of which describes something about the object or holds a reference to another SqlSmoObject or SmoCollectionBase object that extends the hierarchy further. Figure 15-8 diagrams a small part of the SMO hierarchy for a SQL Server.

Figure 15-8. Basic SMO hierarchy

The Server object in the middle of Figure 15-8 is a SqlSmoObject and shows three of its many properties: Information, Databases, and State.

The root of the SMO object of the hierarchy we will be looking at is always a Server object. The State property describes something about the Server object itself—namely, whether it represents an existing instance of SQL Server. The Information and Databases properties refer to other objects that extend the hierarchy.

The Information object on the right side of Figure 15-8 is a SqlSmoObject and contains some properties that further describe the SQL Server instance.

The DatabaseCollection object on the left side of Figure 15-8 is a SmoCollectionBaset. It contains a collection of Database objects that represent the databases in the Server. Each Database object is itself a SqlSmoObject that further extends the hierarchy, and so on.

A full object model for even a small SQL Server is very large, easily containing hundreds or even thousands of objects. It is not possible to put such a diagram in this chapter or even on a good-sized wall, for that matter! An example program, with source, available from the Web site for this book, can be used to explore a SMO object model to get a better feeling for its composition.

Figure 15-9 shows the dialog box displayed by an example program, SMO Object Model Explorer program. It displays the object model in the form of a tree. Each branch of the tree is an object in the hierarchy. The DatabaseCollection (1), for example, contains Database (2) objects, which in turn contain a TableCollection (3), and so on. Double-clicking a branch fills out its children, if it has any.

Figure 15-9. SMO object explorer

One of the differences between a SqlSmoObject and a SmoCollectionBase object is that a SqlSmoObject object has an Urn property, and a SmoCollectionBase object does not. The Urn object will be covered in detail later, but the Urn for a SqlSmoObject object uniquely identifies that object in SQL Server. The Urn can be used to access that object directly without drilling through the SMO hierarchy.

In the SMO Object Explorer, a SqlSmoObject object always has a quoted string displayed in its tree element. This string is the last part of the Urn for the object. The "/Database[@Name='AdventureWorks']" (2) in Figure 15-9 is an example of this. The ToolTip for an SqlSmoObject shows the full Urn value. Figure 15-10 shows the ToolTip for a Database in the DatabaseCollection of a Server.

Figure 15-10. ToolTip for SqlSmoObject

We will take a short look at the SMO Object Explorer program, which uses several programming techniques used by SMO applications. It works by recursively using reflection to drill though the properties of an object, starting with the Server object. When it finds a property that is a subclass of a SqlSmoObject or SmoCollectionBase, or a few other selected types, it adds it to a tree control. A fragment of the code used to do this is shown in Figure 15-11.

Figure 15-11. Code for SMO object explorer

First, to drill into the object hierarchy, the variable type (1) is set to the type of the current object. Each property (2) of the type is inspected (3) to see whether it derives from a SqlSmoObject or SmoCollectionBase object. If so, it is added to the tree (4). See the source for the SMO Object Explorer for a complete code listing.

This chapter will not cover all the specific kinds of objects in the SMO object model. The core of the SMO object model is built around SqlSmoObject and SmoCollectionBase objects, as we have just seen. Use the SMO Object Explorer to drill into an instance of SQL Server to get a feeling for the richness of the SMO object model.

SMO Projects

SMO can be used in any kind of .NET Framework 2.0 application except for a SQL Server 2005 CLR application—that is, CLR code written to be run inside SQL Server 2005, though it is possible that this capability may be added in the future.

Several assemblies contain SMO classes, but applications that work with common database objects, such as databases and tables, will use the Microsoft.SqlServer.Smo, Microsoft.SqlServer.Smo.Enum, and Microsoft.SqlServer.ConnectionInfo assemblies. This section covers using Visual Studio 2005 to build a C# GUI application that is based on SMO.

To get things started and see the basics of how SMO is used, let’s build a simple GUI application that lists the databases in an instance of SQL Server. Most of this section is about building a SMO-based GUI application using Visual Studio 2005. This application is a GUI version of the console applications shown in Figure 15-3, Figure 15-5, and Figure 15-6. This example goes through the steps of using Visual Studio 2005 to build a simple, SMO-based GUI application.

The UI for the application is shown in Figure 15-12. The application is called Database Lister, and the source code for it is available from http://www.pluralsight.comdanSQL2005Dev.htm.

Figure 15-12. Database lister application

To use the Database Lister application, you enter the name of an instance of SQL Server in the edit box (1) below the Instance Name label. When you click the List Databases button (2), the list box (3) below it shows the databases in the instance.

In this example, we are going to gloss over login issues to keep things focused on the SMO object model. Note that this application will log in to SQL Server 2005 with the credentials of the user running the applications. Later, we will discuss ways of controlling the identity used to log in to SQL Server 2005.

The Database Lister application is a Windows GUI app. We can create it easily by making a Visual Studio 2005 Windows project. This example is implemented in C#, but you could use any language supported by .NET Framework. The first thing that you do is use Visual Studio 2005 to start a Windows Project by opening Visual Studio, choosing File > New, and clicking Project (1), as shown in Figure 15-13.

Figure 15-13. Starting a Windows project

Visual Studio 2005 will present a New Project dialog box, as shown in Figure 15-14, after you click Project. In this dialog box, you must name the solution that will contain your project (1), select the directory that will hold the solution (2), name your project (3), and then click Windows Application (4) in the Visual Studio installed templates.

Figure 15-14. Naming a Windows project

SMO-based applications must have references to the appropriate SMO assemblies. This project, like most SMO projects, will need references to the Microsoft.SqlServer.Smo and Microsoft.SqlServer.Connection- Info assemblies; applications that use other features of SMO may need additional references. To do this, open Solution Explorer by choosing View Solution.

Next, open the Add Reference dialog box by clicking Add Reference, as shown in Figure 15-15.

Figure 15-15. Adding assembly references

In the Add Reference dialog box, shown in Figure 15-16, click Microsoft. SqlServer.ConnectionInfo (1) and Microsoft.SqlServer. Smo (2) while holding down the Ctrl key. Next, click the OK (3) button to add the assembly references.

Figure 15-16. Selecting assembly reference

Now you are ready to design the form. In this case, it is simple; just drop the appropriate controls on the form that Visual Studio 2005 presents to you, as shown in Figure 15-17. You should end up with the GUI shown in Figure 15-18.

Figure 15-17. Adding a list box

Figure 15-18. Form for database lister application

All the work for this application happens when the List Databases button is clicked. To implement this, we must add a handler for the button click. The easy way to do this is to double-click the button (1) in the form for the application, as shown in Figure 15-18. This will add the handler code and bring up a window where you can add your code.

Figure 15-19 shows the code for the button handler. The first thing this code has to do is clear (1) out the list box, named databaseList, that holds the database names.

Figure 15-19. Button handler

Next, make a Server object (2); this represents a SQL Server instance. The constructor for the Server object uses the name of the instance, which comes from the instanceName text box.

The Server object has a property named Databases, which you can iterate through using a C# foreach (3) statement and create a Database object for each database. When we have a Database object, we can easily add its name (4) to the list box.

It probably took more time for you to read how to create the Database Lister application than it will for you to build it. Although SMO is a fairly large object model, you will have Visual Studio 2005 IntelliSense to help you along the way, and most operations you will do with SMO will not require the use of T-SQL.

This brief introduction shows how SMO, Visual Studio 2005, Monad, or any other CLR-based programming language makes it very easy to implement custom management tools based on command-line, GUI, or scripting environments for SQL Server 2005.

Next, we will look at what makes up SMO. We will start by looking at what is needed to make a SMO application; then we will look at how SMO makes and manages connections to SQL Server. After that, we will look at the SMO object model and then generating scripts from SMO.

We won’t be looking at all the details of all the SMO objects; there are close to 200 kinds. We will be looking at features that they have in common and the specific properties of a few.

Connections

Any application that uses SQL Server must make a connection to it first. SMO uses an instance of the ServerConnection class, from the Microsoft.SqlServer.Management.Common namespace, to make a connection to SQL Server. The Server objects in the previous examples made a ServerConnection as part of their construction.

The parameterless constructor for ServerConnection will make a connection to the default instance of SQL Server on the local machine. A connection to a specific instance of SQL Server is made by passing its name into the constructor for a ServerConnection. When the ServerConnection object has been constructed, a physical connection can be made by calling the ServerConnection.Connect() method. Later, we will see that in typical usage, Connect() is not called.

The first connection (1) made in Figure 15-20 is made to the default instance of SQL Server on the local machine. The second connection (2) is made to the default instance of SQL Server on the machine named "CANOPUS5".

Figure 15-20. Connecting to SQL Server

A connection made with ServerConnection is authenticated in one of three ways. The Windows credentials of the user who ran the program that made the connection are used by default. The second method of authentication is impersonating a Windows user, using his name and password, and the third method is using a SQL Server login name and password.

Default Windows Credentials Connection

First, we will look at using the Windows credentials of the user running a program who makes a connection. It is straightforward to do this, as shown in Figure 15-21. Start by constructing a new ServerConnection object using the parameterless constructor and then execute the Connect (2) method.

Figure 15-21. Default connection

The behavior of the Connect method when called on a ServerConnection object built this way is the same as the SqlCmd utility, with no command- line switches or deprecated osql utility using just the –E commandline switch; the connection is made to the default instance on the local machine using the credentials of the person running the program. The sample program DefaultSMOConnection uses this technique, and the code for it is shown in Figure 15-22.

Figure 15-22. DefaultSMOConnection program

The DefaultSMOConnection program makes a connection object (1) and then connects (2) to the server. The ReadLine method is used to stop the program from terminating as soon as the connection is made so we can observe the effect of the program on SQL Server.

Figure 15-23 shows a command shell that was stated using the Runas utility with MiniDan’s credentials. Note that user names, such as MiniDan, are case insensitive, and some utilities will display them in lowercase. The DefaultSMOConnection program is run (1) in that shell. The SQL Server 2005 Activity Monitor shows that the DefaultSMOConnection program has done a login (2) as MiniDan on the Host CANOPUS5 (the local machine) with an application name of “.Net SqlClient Data Provider.” See Books Online for information about running the SQL Server 2005 Activity Monitor.

Figure 15-23. Using default connection

Using Runas

The machine being used to run the samples that illustrate ServerConnection has a couple of test Windows users on it. Specifically, it has Alice (1) and MiniDan (2), as shown in Figure 15-24. Both are ordinary users with no special privileges.

Figure 15-24. Windows test identities

An easy way to run a program that has a particular user’s identity is to start it with the Runas utility. The command shell is the utility cmd.exe, and starting it with a specific user’s identity is shown in Figure 15-25.

Figure 15-25. Using Runas utility

The Runas (1) utility requires a /user: command-line switch with the user’s name after it, followed by the name of the program to be run—in this case. cmd. It will prompt you for the user’s password before it runs the program. After you have entered the user’s password, Runas will start a command shell with the credentials of /user: and put the user’s name in the title of the window for the shell. Echoing the USERNAME environment variable shows Alice’s name (2). This is the technique used to run the following samples with a particular user’s credentials and is also useful when you need to test your application with an identity other than your own.

Automatic Connection

ServerConnection has a method named Connect. Calling this method makes a connection to SQL Server. You are not required to call the ServerConnection.Connect method to make a connection to SQL Server, and typically, it is not used. The connection will be opened automatically when you try to use the connection and it is not already open. If the connection is already open, calling the Connect method will not make another connection. If you do call Connect, it is important that you close the connection by calling Disconnect. If you do not call Connect, SMO will manage closing the connection for you.

Figure 15-26 shows a program that automatically connects to SQL Server. Construction of the ServerConnection (1) does not create a physical connection to SQL Server. Accessing the ProcessID property (2) requires connection to SQL Server, so the ServerConnection makes the connection and then uses it to get the ProcessID for the connection from SQL Server.

Figure 15-26. Automatic connection

To see that the connection is not actually made until SMO needs information from the SQL Server, run the program shown in Figure 15-26 in the debugger. Put a breakpoint on the line that reads the ProcessID (1), as shown in Figure 15-27; next, run to the breakpoint. Every connection to SQL Server 2005 is identified by a session id, which is listed in the SQL Server 2005 Activity Monitor as the Process ID. The ProcessID property of a connection returns this value. Use the SQL Server 2005 Activity Monitor to con- firm that the connection (2) has not been made. Next, do a single step and then refresh the Activity Monitor. You will see that one connection (3) has been added to SQL Server and that the Process ID in the Activity Monitor is the same as the ProcessID property of the ServerConnection.

Figure 15-27. Connection upon property access

Windows Identity Impersonation

In the previous example, SMO just used the credentials of whoever happened to run the DefaultConnection program. Sometimes, we want to create the connection using some other Windows credentials.

Windows credentials are used for authentication when the LoginSecure property of the ServerConnection is true, which is its default value. When LoginSecure is true, the ConnectAsUser property determines whether the credentials of the user running the program or some other user’s credentials are used to authenticate. The default value for Connect AsUser is false and indicates that the credentials of the user running the program should be used.

When ConnectAsUser and LoginSecure are both true, the Connect AsUserName and ConnectAsUserPassword properties of Server Connection are used to impersonate a Windows user when doing a login. The sample program ImpersonateConnect illustrates the use of these properties and is shown in Figure 15-28, where ConnectAsUser (1) is set to true.

Figure 15-28. ImpersonateConnect program

The result of running the ImpersonateConnect program is shown in Figure 15-29.

Figure 15-29. Impersonating Windows identity

The ImpersonateConnect program is run in a shell that was started using the Runas utility and Alice’s (1) credentials. The connection to SQL Server was made using MiniDan’s (3) login. MiniDan’s name and password (2) were passed to ImpersonateConnect on the command line.

Note that impersonating a Windows identity this way requires that the client application be running in the domain of the user or the domain that is trusted by the user being impersonated. In other words, you cannot use this technique to use Windows credentials to log in to SQL Server from a machine outside the domain, or trusted domain, in which the instance of SQL Server is running.

SQL Server Login

A ServerConnection will log in with a SQL Server log in when the LoginSecure property is false. It will log in using the Login and Password properties of ServerConnection. The sample program SQLConnect illustrates the use of these properties and is shown in Figure 15-30.

Figure 15-30. SQLConnect program

static void Main(string[] args)
{
  ServerConnection serverConnection =
    new ServerConnection();
  serverConnection.LoginSecure = false;
  serverConnection.Login = args[0];
  serverConnection.Password = args[1];
  serverConnection.Connect();
  Console.ReadLine();
}

The result of running the SQLConnect program is shown in Figure 15-31. In this example, Bob is a SQL Server login.

Figure 15-31. SQL Server login

Alice (1) is running the SQLConnect program. Bob (2) is the login using the connection.

The ServerConnect object can also be constructed using a SQL login name and password. This is shown in Figure 15-32.

Figure 15-32. SQL connection by constructor

The password (1) can also be passed into the constructor as a System. Security.SecureString, and this is a much more secure practice. Note that building a SecureString from an ordinary string defeats its purpose. The SecureString should be built one character at a time, as the user types the password, not passed on the command line. Search MSDN for “securestring application sample” for an example of how this is done.

Changing SQL Server Login Password

The password for the login using the connection can be changed if the login is a SQL Server login, but not if it is a Windows login. The ChangePassword method on the ServerConnection class is used to do this. An example of changing a password is shown in Figure 15-33.

Figure 15-33. Changing a password

The example shown in Figure 15-33 expects three strings to be passed in on the command line: a login, the password for the login, and the new password for the login.

The first argument is used to set the login name (1). The second argument (2) is used to set the password for the login. The third argument (3) is used to set the new password for the login. Note that this function, like the constructor for the ServerConnection, allows a SecureString to be used for the new password. The ChangePassword method can be used on an expired password.

Connection Management

SMO manages the physical connections a ServerConnection uses to connect to SQL Server. The management of connections is determined by connection properties of the ServerConnection, which are shown in Table 15-1.

Table 15-1. ServerConnection Connection Properties

By default, SMO uses pooled connections, but nonpooled connections have a more straightforward behavior, so we will look at them first.

SMO uses a single connection to an instance of SQL Server when Non-PooledConnection is true. None of the other connection properties has any meaning when NonPooledConnection is true. The connection to SQL Server is created when the ServerConnection.Connect method is called or when SMO needs a connection to SQL Server and none exists—for example, to retrieve some property of an object.

The connection is maintained until the ServerConnection.Disconnect is called or the application using the connection terminates. Calls to ServerConnection.Connect are not counted—that is, a call to ServerConnection.Disconnect will terminate the connection regardless of the number of times ServerConnection.Connect has previously been called when NonPooledConnection is true.

The default value for ServerConnection.NonPooledConnection is false, which enables connection pooling. Also by default, the value of Server Connection.AutoDisconnectMode is AutoDisconnectionMode. Disconnect IfPooled; ServerConnection.MinPoolSize is 0; MaxPool Size is 100; and PooledConnectionLifetime is 0. With these settings, SMO completely controls connecting to and disconnecting from SQL Server.

When connection pooling is enabled with the default property settings, SMO will create a connection to SQL Server whenever it needs one up to the limit specified by ServerConnection.MaxPoolSize. When no connection exists, SMO will always create one when it needs to get information from the SQL Server or ServerConnection.Connect is called.

When a connection already exists, sometimes SMO will create a new one when it needs information from SQL Server or ServerConnection.Connect is called. In no case will it create a new connection if Server.MaxPoolSize connections already exist.

ServerConnection.MinPoolSize works in conjunction with Pooled-ConnectionLifetime. Some connection-pooling mechanisms will immediately fill a pool to the minimum connection count when they start running. SMO does not manage the connection pool in this way.

SMO creates connections as it needs them up to ServerConnection. MaxPoolSize connections. When a connection is unused for longer than ServerConnection.PooledConnectionLifetime seconds, it is disconnected if there are more than ServerConnection.MinPoolSize connections to SQL Server. This is the same pooled-connection behavior that is found in ADO.NET.

When ServerConnection.AutoDisconnectMode is set to AutoDisconnectMode. NoAutoDisconnect, SMO in effect calls ServerConnection. Connection() the first time it needs a connection and does not call ServerConnection.Disconnect.

Server

The Server class from the Microsoft.SqlServer.Management.Smo namespace represents an instance of the sqlservr.exe program, SQL Server. A Server object is the root of the SMO object hierarchy for managing SQL Server.

A Server object requires a connection to SQL Server, and it uses a ServerConnection object for this purpose. You saw in Figure 15-2 earlier in this chapter you can construct a Server object just by passing the name of an instance of SQL Server to its constructor. A Server can be constructed with its parameterless constructor, with the name of the SQL Server instance, or with a ServerConnection. When a ServerConnection is passed as a parameter, the Server object is constructed for the SQL Server on that connection. The first two constructors behave as though they were constructed with a ServerConnection that itself was constructed with the parameter passed into the Server constructor.

Regardless of how the Server object is constructed, a reference to the ServerConnection that it uses is held in Server.Connection Context.

Figure 15-34 shows a Server object created using a previously created ServerConnection object (1). The Server.ConnectionContext (2) returns the same ServerConnection that was used to construct it. Note that in typical usage, you would not create a ServerConnection and pass it to the Server constructor. This example just illustrates that a Server object always uses an underlying ServerConnection to connect to SQL Server.

Figure 15-34. Server constructed with ServerConnection

Table 15-2 shows some of the properties of a Server object. It is beyond the scope of this of this introductory chapter on SMO to discuss these properties, but all are documented in Books Online in the description of the ServerConnection class.

Table 15-2. Server Properties

SMO Objects

Objects from the SMO object model are created from classes that derive from SqlSmoObject. Each object is uniquely identified by a Uniform Resource Name (URN) and contains properties that hold references to its parent object and its children in the hierarchy.

All the objects in the hierarchy can be accessed through these references, sometimes by sequentially drilling through them, or directly accessed via the URN that identifies them.

All the objects have properties that describe them. These properties can be accessed either dynamically or statically.

Object Identity and URNs

The SMO object model identifies each SqlSmoObject in a SQL Server with a URN. A URN serves as a persistent, location-independent resource identifier. The formal definition of the syntax of a URN can be found in RFC 2141, “URN Syntax.” The URN for a given object is in its SqlSmoObject.Urn property.

Having a URN for every object in SQL Server is a very useful feature. Given the URN for an object, you can retrieve it from a Server directly without having to traverse the object hierarchy. One thing that this means is that when you have a reference to a SqlSmoObject, you can remember its URN and then, in the future, retrieve it directly. Note that the URN is something that SMO uses; it is not a property of an instance of SQL Server itself.

URNs for SqlSmoObjects are significant, though URNs in general are not. That means that they are not arbitrarily created for an object, as a GUID might be, but are based on a formula that says the URN represents the path from the Server through the object hierarchy to the object. This means you can find an object in the SMO object hierarchy just by making its URN. Later, we will see how to use a URN to retrieve a SqlSmoObject, but first, we will look briefly at URNs.

The syntax for a URN breaks it into three parts: a scheme, a namespace identifier, and a namespace-specific string. The scheme for a URN is always urn: and is case insensitive. An example of a URN is shown in Figure 15-35. The namespace identifier is used to indicate how the namespace-specific string should be interpreted.

Figure 15-35. URN format

The namespace identifier is something you can make up to keep track of things. Likewise, the namespace-specific string can be almost any format you want. In practice, you would use the namespace identifier to figure out how to interpret the namespace-specific string. There are other rules that a URN must follow; see RFC 2141 for all the details.

The Server.GetSmoObject() method can be used to access an object in SQL Server using its URN. If the object specified by the URN exists, Server.GetSmoObject() returns a reference to it; otherwise, it returns null. Server.GetSmoObject() accepts most properly formatted URNs but does not require them. It ignores everything in the URN except the namespacespecific string.

The namespace-specific string is an XPath location path that selects an object from an instance of SQL Server. The XPath recommendation defines the syntax of a location path and is located at http://www.w3.org/TR/ XPath. In brief, an XPath location path consists of a number of location steps, each separated by a /. Each location step represents a level in the object hierarchy maintained by SMO.

Each location step in the XPath location path contains the name of a kind of SMO object in SQL Server and the name of that object. The namespacespecific string for the Authors table in the Pubs database on MySvr server, for example, would look like this: Server[@Name='MySvr']/Database [@Name='Pubs']/Table[@Name='Authors']

This XPath location path has three steps: Server[@Name='MySvr'], Database[@Name='Pubs'], and Table[@Name='Authors'].

In general XPath location paths ignore extra white space so the following namespace specific string would also identify the Authors table.

Server[@Name='CANOPUS5']
/Database[@Name='Pubs']
/Table[@Name=''Authors']

There are a couple of places where XPath does not ignore white space. One is in names. The following would produce an error if used because of the space in Ser ver:

Ser ver[@Name='CANOPUS5']
/Database[@Name='Pubs']
/Table[@Name=''Authors ']

Another place that XPath does not ignore white space is in the value of an attribute. An attribute is a name that is prefixed with @, and its value, in quotes, comes after the = sign that follows its name. The following namespace-specific string identifies the “My Table” table in a database, not “MyTable”—that is, the space between “My” and “Table” must exist in the name of the object in the database:

Server[@Name='CANOPUS5']
/Database[@Name='Scratch']
/Table[@Name='My Table']

The fact that Server.GetSmoObject() ignores, for the most part, white space in XPath location path and preserves it in attribute values is very convenient. But technically, URNs are not allowed to contain white space, and Server.GetSmoObject() will not automatically unescape white space character codes. The following will look for the table 'My%20Table', not 'My Table', when passed to Server.GetSmo Object():

Server[@Name='SVR1']
/Database[@Name='MyDB']
/Table[@Name='My%20Table']

If you are processing URNs from outside your control—that is, URNs that meet RFC 2141 but might contain escaped characters—you can use the System.Web.HttpUtility.UrlDecode method to convert the escaped characters to ones the SMO can handle. The C# code snippet below converts the %20 in the URN into an actual space so that SMO can handle it. You can use this technique defensively, on any string, even if it doesn’t have any escaped characters in it:

string urn = System.Web.HttpUtility.UrlDecode(
"Server[@Name='SVR1']/Database[@Name='My%20DB']");

So even though technically, a URN must have white space escaped, the URNs used by SMO may contain white space, and in general, SMO will not handle escaped white space as you might expect.

Server.GetSmoObject() will accept a complete URN as input, so the following will also identify the Authors table:

urn:www-danal-com:Server[@Name='CANOPUS5']
/Database[@Name='Pubs']
/Table[@Name='Authors']

SMO ignores the scheme and namespace identifier, and processes only the namespace-specific string. This means that URNs, as found in XML or HTML, can be used to identify SMO objects without any extra processing unless they contain escaped white space.

XPath calls @Name an attribute because it begins with the @, character and anything inside [] is a predicate that must be true. In the previous Pubs database examples, the effect of the step Server[@Name='Canopus5'] is to return all the Server objects that have an attribute called 'Name' whose value is 'MySvr'. There is only one, of course. The next step, /Database[@ Name='Pubs'], is asking for all the Database objects in the object found in the previous step whose name is 'Pubs'. Again, of course, there is only one.

GetSmoObject

Every SMO object in a Server has a unique name. This name is the SqlSmoObject.Urn property. You can retrieve a SqlSmoObject by passing its SqlSmoObject.Urn to the Server.GetSmoObject() method. Note that the @Name attribute of the Server location step of the Urn is optional. If it is used, it must match the name of the Server that SqlSmoObject is being called on.

SQL Server 2005 supports schemas, and as a result, SQL Server objects may be contained in a schema other than dbo. For details on database schemas in SQL Server, see Chapter 6. The previous URN examples using the Authors table in the Pubs database ignored this fact to focus on the overall structure of a URN. An Object that is contained in a schema has a second attribute, @Schema, in its location step.

Extra attributes can be added to a predicate of a URN using the and operator. Figure 15-36 shows how to use the complete URN for the Authors table to access it directly.

Figure 15-36. Direct object access

The URN for the Authors table contains an @Schema attribute (1) that is passed as a parameter of the Server.GetSmoObject() method. It returns a reference to the Pubs table object if it exists; otherwise, it would return a null. When an @Schema attribute is not part of a step, the object is looked for in the default schema of the current user.

GetSmoObject() will also accept a string as an input parameter, which makes it very convenient to access an object directly via its URN.

Figure 15-37 accesses the same table as Figure 15-36 but does so by passing just the string value of the object’s URN to GetSmoObject().

Figure 15-37. Direct object access via string

URN Retrieval Restriction

There is a restriction on using a URN to retrieve a database object: The URN must be from the Server on which the GetSmoObject is called. This means that the @Name attribute must correspond to the name of the Server. If the @Name for the Server is incorrect, GetSmoObject() will throw an exception. Also, it is permissible not to specify an @Name attribute for a Server. Note that the other steps in the URN require always require an @Name attribute.

The URN (1) in Figure 15-38 leaves out the @Name attribute. This means that GetSmoObject will return a reference to the Authors table on whatever server to which local happens to be connected. If the table does not exist on that server, it will return a null.

Figure 15-38. Default server

In some cases, it will be very convenient to leave out the @Name attribute for the Server locations step. In other cases—for example, when you want to document unambiguously a particular database object—it will be better to include the @Name attribute.

Figure 15-39 shows a program that uses a connection to the CANOPUS5 instance of SQL Server and then attempts to use it to access an object in the PLUTO5 SQL Server instance (1) via its URN. The result is shown in Figure 15-40; an exception is thrown.

Figure 15-39. Using wrong server to get URN

Figure 15-40. Exception due to mismatched server

SMO Object Model

You can access any object in an instance of SQL Server either by using its URN or by drilling down to it starting with the Server object for the instance. This chapter won’t go into the entire list of objects you can access using SMO; there are just too many for a single chapter. Refer to the Microsoft.SqlServer.Management.SMO namespace in Books Online for a comprehensive list of the object types used in the SMO object model. We will look at some specific features of a few objects that are representative of the many kinds of properties and features all SMO objects have.

Note that the names of many of the classes used by SMO are duplicated in other namespaces. All the classes we will be looking at come from the Microsoft.SqlServer.Management and related namespaces, not the Microsoft.AnalysisServices or Microsoft.ReportingServices namespace.

We will start by looking at features that are common to all the objects in the SMO object model.

SMO Properties

All the objects in the SMO object model share several features because they derive from SqlSmoObject. One of the shared features is a Properties property, which is an enumeration of all the properties of the object.

Each property of the object is described by a Property object, which itself has eight properties, as shown in Table 15-3. All these properties are System.Boolean type except for the Type property, which is a System.Type and Value.

Table 15-3. Properties of SqlSmoObject.Property

All the properties listed in the SqlSmoObject.Properties property have a corresponding property in the class definition of the object. The Server object, for example, has a property named Instance Name. There is a corresponding Server.InstanceName property of the Server class.

A property of a SqlSmoObject can be accessed dynamically in more than one way. SqlSmoObject.Properties.Item and SqlSmoObject. GetPropertyObject both access a property by its name or an ordinal.

Figure 15-41 shows an example that references properties dynamically and statically.

Figure 15-41. Using statically and dynamically referenced properties

The program shown in Figure 15-41 accesses the “ActiveConnections” property of a Database. The indexer that represents the Items property (1) is used to access the property and retrieve its value—that is, the returned value has its PropertyRetrieved property set to true.

Note that collections in SMO have a property named Item that typically is exposed in most languages as an indexer—the collection name with a [] suffix. So Properties["ActiveConnections"] (1) is actually using the Item property of the Properties property of database0.

The GetPropertyObject() method (2) is used to access the property and retrieve its value. A property is accessed without retrieving its value when SqlProperty.GetPropertyObject is called with a second parameter set to false (3). Last, the value of the property is retrieved when the corresponding instance property (4) is used.

Dynamically accessed properties also make it possible to write an application that displays the values of all the properties of an object without having to know the actual names of their corresponding instance fields. To do this, the properties of a SqlSmoObject are accessed by enumerating them from the SqlSmoObject.Properties property.

Figure 15-42 shows a method that will fill a Windows Forms ListBox named Properties with the names and values of the properties of a SqlSmoObject. The properties are enumerated by a foreach statement (1). The name (2) and value (3) of the property are added to the Properties ListBox.

Figure 15-42. FillPropertyList for filling ListBox with properties dynamically

This method does not have to know the names of the properties of the object at the time the code is written; it just enumerates each property and puts its name and value into a list box.

Each of the dialog boxes shown in Figure 15-43 uses the method in Figure 15-42 to fill the ListBox displayed in its window. The radio buttons are used to choose the properties of either the AdventureWorks database (1) or the Customers table (2) from the AdventureWorks database. Note that the database has different properties from the table, and the FillPropertyList method determined that dynamically.

Figure 15-43. Program using dynamic properties

It is important to note that reading the Property.Value does not make SMO read the value from SQL Server. The meaning of Property.Value depends on the values of Property.Retrieved and Property.Dirty. If Property.Retrieved is false, Property.Value has no meaning. Table 15-4 summarizes the interpretation of Property.Value with respect to Property.Retrieved and Property.Dirty.

Table 15-4. Interpretation of Property.Value

A property of a SqlSmoObject is categorized as expensive if Property.Expensive is true. Properties that are not expensive are read from SQL Server when the object they belong to is created. Expensive properties are not read from SQL Server by default. An expensive property is only when it is explicitly accessed or the Server.SetDefaultInit Field (true) method on the server object has been called. Note that several overloads of SetDefaultInitField allow a finer-grained selection of which expensive properties should be loaded when an object is created. Look for “Server.SetDefaultInitFields Method” in Books Online for more information.

SMO Parent Navigation

The SMO object hierarchy starts with the Server class. The object model is built out from the properties that derive from either SqlSmoObject or Smo- CollectionBase. A SqlSmoObject property holds a child object, and an AbstractCollectionBase property holds a collection of child objects. Note that the complete class hierarchy for SMO is very large. This section concentrates on a small part of it to explain how navigation between objects works in SMO.

Every SqlSmoObject, except Server, has a Parent property. This property holds a reference to its parent and is of the same type as its parent. Server.ServiceMasterKey, for example, holds a reference to a Service-MasterKey object, which is a child of a Server object in the SMO object model. ServiceMasterKey.Parent is a reference to an object of type Server, its parent.

The Parent property of an object that is a member of a SmoCollectionBase object holds a reference to the object that holds the collection, not the SmoCollectionBase object itself. Server.Databases, for example, is a SmoCollectionBase object that holds Database objects. The Database. Parent property of a Database object holds a reference to a Server object, not the Database object.

Figure 15-44 shows a Server object that is holding a reference to a ServiceMasterKey and a collection of databases. Each of the databases in the collection holds a reference back to the Server object, not the Databases collection object. The ServiceMasterKey object is not a collection, and its Parent property, as expected, refers to the Service object that contains it.

Figure 15-44. SMO parent

Most of the SMO collection objects have an indexer associated with them that is used to access an object in a collection by ordinal or name. Databases["AdventureWorks"], for example, returns a reference to the AdventureWorks database if the collection contains that database or a null otherwise.

The indexers for collections of objects that are contained in a schema have an optional extra parameter used to name the schema. If it is not specified, the default schema for the login used to make the connection to the server is used.

Figure 15-45 shows the use of collection indexers. Objects that are in the default schema for the user, such as the AdventureWorks database (1), are accessed by name in a collection. Objects that are contained in some other schema, such as the Customers (2) table in the AdventureWorks database, use both the name of the table and its schema. If only the name (3) of the table is used, the indexer will return a null unless the table is in the default schema for the current user.

Figure 15-45. Collection indexer

SMO State

SqlSmoObject.State reflects the state of an object in terms of its existence. The state of a SqlSmoObject is one of the values of the SqlSmoState enumeration. Table 15-5 shows the enumerated values of SqlSmoState.

Table 15-5. SqlSmoState Values

Note that the SqlSmoObject.State property is used by GUI tools such as SQL Server Management Studio and Visual Studio 2005.

Create, Alter, and Drop

Most SqlSmoObjects have Create, Drop, and Alter methods that correspond to similarly named T-SQL statements. Some SqlSmoObjects, such as TableViewBase, are meant to be used as base classes for other SqlSmoObjects that implement these methods and do not have these methods. In other cases, such as Server, which has only the Alter method, some of these methods are not implemented because they do not make sense for that object. Objects from classes that implement ICreateable, IAlterable, or IDropable interfaces have corresponding Create, Alter, and Delete methods. You can look in Books Online to see which of these methods a particular class implements.

Because Create, Drop, and Alter don’t make sense for all SqlSmoObjects, it is not possible for this class to specify them as virtual or abstract methods. SqlSmoObject does contain the implementation of these methods, however, in the protected methods CreateImpl, AlterImpl, and DropImpl.

Figure 15-46 shows the pseudocode for a typical Create method for a SqlSmoObject. In fact, the implementations of Create, Drop, and Alter, where they exist, merely delegate (1) their implementation to the corresponding Impl method in the base class.

Figure 15-46. Pseudocode for the implementation of Create

It might seem strange that a concrete method in a base class implements the behavior of a derived class. Later, we will see that SqlSmoObjects can generate T-SQL scripts. The base class Impl methods use the derived class’s ability to create this script to implement their behavior.

The general technique to use Create, Drop, and Alter is to get a reference to a SqlSmoObject and then call one of these methods as appropriate. The most straightforward example of this is the creation of a database.

Most SqlSmoObjects that implement Create also have a Parent property. A Database.Parent, for example, holds a reference to the Server that contains it. In addition, almost all SqlSmoObject that implement Create have a Name property; the few that don’t, such as UserDefinedMessage, are identified by other means. In any case, a Database needs both a Name and a Parent before it can be created, as is typical of most SqlSmoObjects.

Figure 15-47 shows a program that creates a database in the CANOPUS5 server. First, the database is constructed, using the constructor (1) that allows both the Parent and Name to be passed in as parameters. Next, Database.Create (2) is called. Note that for all SqlSmoObjects, you will need a reference to a server to be able to Create, Alter, or Drop them.

Figure 15-47. Database.Create

Alter is used to change an existing SqlSmoObject. We might want to change the recovery model and disable Service Broker in the database created in Figure 15-47, for example.

To alter a database, you first must obtain a reference to its SqlSmoObject. Figure 15-48 shows database ScratchDB in server Canopus5 being altered. Using the URN and SqlGetSmoObject (1) is a very straightforward way to get a reference to an object that you need to alter. A Database has several DatabaseOptions that can be altered (2). Change these options to the values you desire; then call Database.Alter to alter the actual database on the Server. Database.DatabaseOptions itself has an Alter method that could have also been used. In addition, these options could have been set before the database was created in Figure 15-47.

Figure 15-48. Altering a database

Dropping an object is done by getting a reference to an object and calling its Drop method. Figure 15-49 shows a database being dropped.

Figure 15-49. Dropping a database

static void Main(string[] args)
{
   Server Canopus5 = new Server("CANOPUS5");
   Database ScratchDB = (Database)
     Canopus5.GetSmoObject(
     @"Server/Database[@Name='ScratchDB']");
   ScratchDB.Drop();
}

The details of Create, Alter, and Drop are different for each SqlSmoObject. As one last example, let’s look at creating a database, adding a table with a primary key to it.

Figure 15-50 shows a program that creates a database using SMO. After it creates the database, it adds a table, Items (1), to it. Next, it adds a Serial Number column (2) to the table. The IsNullable property of the column is set to false because it is intended to be the primary key for the table. After the last column has been added to the table, the Create method is called on it.

Figure 15-50. Creating a database

Some more columns are created; then a primary key, PK_Items (3), is added to the table. An IndexedColumn is added (4) to the primary key to make Serial Number the primary key. Finally, the primary key is created.

Figure 15-51 shows the result of running the program shown in Figure 15-50. Notice that there is a ScratchDB5 database, and the Items table has its Serial Number column as the primary key for the table. We decide that we want to modify the database to remove the Size column and change the Value column to be of type Money.

Figure 15-51. Database with Table and Columns

Figure 15-52 shows a program that removes the Size column of the Items table and changes the type of the Value column to Money. Figure 15-53 shows the results of running the program shown in Figure 15-52.

Figure 15-52. Changing a database

Figure 15-53. Database after changes

Note that the previous examples that created database objects could have been done without using URNs, but URNs are the topic of this section.

You can literally send an e-mail to someone with admin privileges for a database and say, unambiguously, “Delete column

Server[@Name='BigServer']/
Database[@Name='MyDatabase']/
Table[@Name='Accounts']/
Column[@Name='Region']."

Scripts

T-SQL scripts are used to create, change, and drop objects in SQL Server. SMO can be used to create these scripts. SMO can also manage object dependencies so that creates and drops are scripted in the correct order.

SMO objects have a Script method. This method is used to make a T-SQL script that can create or drop the corresponding object. By default, the Script method will make a script to create the object. The script is returned as a StringCollection. Note that StringCollection is in the System. Collections.Specialized namespace.

The Script() method (1) in Figure 15-54 builds a script to create the Customer table in the AdventureWorks database. It returns a StringCollection named script. The strings in script are enumerated using a foreach statement and printed to the console (2). The script it generates is shown in Figure 15-55.

Figure 15-54. Scripting a table

Figure 15-55. Customer create script

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[Customer](
  [CustomerID] [int] IDENTITY(1,1)
    NOT FOR REPLICATION NOT NULL,
  [TerritoryID] [int] NULL,
  [AccountNumber] AS (isnull('AW'+
    [dbo].[ufnLeadingZeros]([CustomerID]),
    '')),
  [CustomerType] [nchar](1) COLLATE
    SQL_Latin1_General_CP1_CI_AS
    NOT NULL,
  [rowguid] [uniqueidentifier] ROWGUIDCOL
    NOT NULL,
  [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

A ScriptingOptions object passed into the Script() method determines how a script will be generated.

There are many scripting options available, far more than can be covered in this chapter. See Books Online for all of them.

Figure 15-56 creates a ScriptingOptions object (1), sets its Script-Drops and IncludeIfNotExists properties to true (2), and then passes it as an argument (3) to the Script method. The Script method generates the script shown in Figure 15-57 to drop the Customer table.

Figure 15-56. ScriptingOptions

Figure 15-57. Customer drop script

IF  EXISTS (SELECT * FROM
  sys.objects WHERE id =
  OBJECT_ID(N'[Sales].[Customer]') AND
  type in (N'U')
DROP TABLE [Sales].[Customer]

Most scripting tasks involve more than one object, and scripts that involve more than one object are sometimes large. A Scripter object can generate a script for a list of objects and can send the script directly to a file instead of a StringCollection.

A Scripter object requires a Server to generate a script. You can pass the Server in as a parameter to the Scripter constructor, as shown in Figure 15-58 (1), or set it into the Server property before you generate the script. A Scripter can script an array of SMO objects or URNs or an UrnCollection (2). To script directly to a file, the Scripter. Options.FileName and ToFileOnly must be set (3). Note that these techniques can also be used for scripting individual objects.

Figure 15-58. Scripting multiple objects

The file named in FileName will be created and the script written into it when the Scripter.Script() method is called (4) unless the Scripter. Options.AppendToFile option is set to true. If it is true, the script will be appended to the file.

By default, the Scripter.Script() method will generate a script to create the objects. A drop script can be generated by setting the Scripter. Options.ScriptDrops property to true.

The script generated by the example shown in Figure 15-58 will script out the objects in the order in which they appear in the array or collection, as shown in Figure 15-59. The Customers table (1) is scripted out before the SalesTerritory table (2), as the Customers table comes before the SalesTerritory table in the Urn collection.

Figure 15-59. Script to create objects

Sometimes, an object has dependencies on another objects, and a script may want to take this into account. The Scripter manages these dependencies in a couple of ways.

First, the simplest way to handle dependencies is to set Scripter. Options.WithDependencies to true before calling the Script method. This makes a script that has the objects in the list in the proper order, according to whether a create or drop script is being built. In addition, it includes in the script all the dependent objects of the objects in the list.

Figure 15-60 shows the use of Scripter.WithDependencies (1). One of the side effects of Scripter.WithDependencies=true is that it scripts out all the dependent objects, in addition to getting objects in the correct order. This example scripts out the urnLeadingZeros user-defined function and an alias type named Name, in addition to the Customer and SalesTerritory tables.

Figure 15-60. Scripting with dependencies

Figure 15-61 shows the results produced by the code in Figure 15-60. The alias type Name (1) is scripted out before the SalesTerritory table because the SalesTerritory table has a column of type Name. The urnLeadingZeros function (3) is scripted out before the Customer table because the Customer table has a computed column that uses this function.

Figure 15-61. Script with dependencies

In some cases, you want to get only the correct order for the objects in the script and want no additional objects in the script. The Scripter.Filter CallbackFunction lets you control which objects are output by the script when Scripter.WithDependencies is used.

The FilterCallbackFunction function is a delegate that is called once for each URN that is found by the Scripter in the process of determining all the dependencies of the objects in the list for which it is generating a script. A return value of true from this function means filter it—that is, do not create a script for it.

Delegates are a feature of .NET Framework, and if you are not familiar with them, look for delegates and anonymous delegates in MSDN for more information. One way of thinking of a delegate is as a function with no name, defined in place where it is used. Figure 15-62 shows an anonymous delegate function (1) defined in place for the Scripter.FilterCallbackFunction. This delegate has a URN as an input parameter. Note that Urns is the Urn-Collection defined at the beginning of the program. One of the useful features of an anonymous delegate is that it can refer to variables defined before it in the code. If the Urn passed into the anonymous delegate is not in the Urns, it returns true (2); otherwise, it returns false (3). When the FilterCallbackFunction returns true, it is saying to exclude this object from the script being produced. Figure 15-63 shows the script in dependency order.

Figure 15-62. FilterCallback

Figure 15-63. Script in dependency order

Unlike the code shown in Figure 15-60, which scripts out dependent objects, the code in Figure 15-62 scripts out only the objects in the Urn collection.

Behind the scenes, the Scripter object is using a DependencyTree to figure out what is dependent on what. A Scripter can build a DependencyTree that defines all the dependencies that the objects in a list have with other objects. There are two kinds of DependencyTrees: DependencyType.Parents and DependencyType.Children. DependencyType.Parents defines all the dependencies that the objects in the list have on other objects. DependencyType.Parents is used to find the objects that must be created before the others in the list are created. Likewise, DependencyType.Children is used to find all the objects that must be dropped before other objects in the list can be dropped.

A DependencyTree is a tree of DependencyTreeNodes, where each DependencyTreeNode represents an object identified by its URN in the DependencyTreeNode.Urn property. The DependencyTree itself has an Urn property that represents the root object of the tree, if there is one; otherwise, DependencyTree.Urn is null.

A DependencyTree can be navigated using the FirstChild and NextSibling methods of a DependencyTree or DependencyTreeNode object.

Figure 15-64 shows an example of navigating a DependencyTree of type DependencyType.Parents depth first. The TreeMember method (1) takes a DependencyTreeNode from a DependencyTree and writes its URN to the console, prefaced by tabs that indicate its depth in the tree. If that DependencyTreeNode has children, it recursively calls the TreeMember function (2) on its first child. Then it calls the TreeMember function (3) on its next sibling, if it has one.

Figure 15-64. Depth first dependency tree

Figure 15-65 shows the result of running the example in Figure 15-64. The two tables from the UrnCollection, Customer (1) and SalesTerritory (4), are siblings at the top of the hierarchy. The user-defined type “Name” is repeated (3) as a child of the SalesTerritory table (2) that is a child of the Customer table and as a child of the SalesTerritory table (2) that is a sibling of the Customer table.

Figure 15-65. Dependency tree

Operations performed on SQL Server through SMO objects can also be scripted. A ServerConnection has a CapturedSql property that can be used to capture SQL commands issued by SqlSmoObjects to SQL Server.

A script for commands is captured in the ServerConnection.Captured Sql property whenever the ServerConnection.SqlExecutionModes property is equal to SqlExecutionModes.CaptureSql or SqlExecution Modes. ExecuteAndCaptureSql. In the latter case, and when Server Connection. SqlExecutionModes is equal to ServerConnection.SqlExecutionModes. ExecuteSql, the SQL commands are also executed.

Figure 15-66 shows a program that uses SMO to capture SQL. It sets the SqlExecutionModes property (1) of the ServerConnection to CaptureSql.

Figure 15-66. Capturing SQL

It uses the same code that was used in Figure 15-52 to change the columns in the Items table of the ScratchDB5 database. When this program is executed, however, nothing in the ScratchDB5 database is changed; instead, the script to perform those changes is output to the console.

Figure 15-67 shows the script that is output to the console by the program shown in Figure 15-66.

Figure 15-67. Results of CaptureSql

USE [ScratchDB5]
ALTER TABLE [dbo].[Items] DROP COLUMN [Size]
USE [master]
USE [ScratchDB5]
ALTER TABLE [dbo].[Items] ALTER COLUMN [Value]
 [money] NULL
USE [master]

Configuration Management

The ManagedComputer class, in the namespace Microsoft.SqlServer.Management. Smo.Wmi, provides ways to manage those configuration settings of SQL Server that cannot be managed by T-SQL. An example of this is the network protocols used by SQL Server. These kinds of things typically are configured by Registry settings that may be machine or operating system dependent.

These configurations are meant to be managed through Windows Management Instrumentation (WMI). WMI exposes these configurations as data in a Common Information Model (CIM) repository. The data in this repository reflects the current configuration of SQL Server, and changes to the data in the repository cause the underlying configuration to change. WMI encapsulates all the machine and operating system dependent details in this way.

Using ManagedComputer is as straightforward as using a SqlSmoObject. Figure 15-68 shows a program that enumerates the network client protocols configured for SQL Server. First, it creates a ManagedComputer (1) by passing the name of the machine as a parameter to its constructor. Next, it uses foreach (2) to enumerate the client protocols available. Then it writes out to the console (3) the names of the protocols it found. The results of running this program are shown in Figure 15-69.

Figure 15-68. Enumerating client protocols

Figure 15-69. Client protocols on CANOPUS5

np
sm
tcp
via

ManagedComputer is really just a facade in front of WMI, and it uses WMI to manage the data in the CIM repository. Any access to the CIM repository has to be strictly controlled, of course. ManagedComputer does not log in to SQL Server; the CIM is part of Windows, and access to it is protected by Windows security. By default, ManagedComputer will use the Windows identity of the person running the code that constructs it to access WMI. A second and third parameter can be passed into the constructor for ManagedComputer: the name and password of the identity that ManagedComputer should use to access WMI.

ManagedComputer lets you manage, among other things, the network protocols that SQL Server can use; the SQL Server services that are hosted on the machine, such as the database engine or analysis engine, and the network connections each service uses.

Figure 15-70 shows a program that blocks all client network protocols (1) and all SQL Server services (2) on the Canopus5 machine.

Figure 15-70. Managing services

Where Are We?

SMO is a class library that is ideally suited to applications that manage SQL Server. Although at first glance, it seems made for DBA-type applications, in fact, it is useful for all applications that need SQL Server management facilities.

SMO integrates tightly with Visual Studio 2005 and presents a programming model that is identical to other tools for Visual Studio 2005, including full support of IntelliSense. It abstracts SQL Server so that in general, knowledge of SQL Server’s database schemas or the T-SQL language is almost never necessary.

SMO efficiently manages its object model to minimize any negative impact it might have on an application or SQL Server. Expensive to load properties are not loaded until needed; operations that can be combined into a single round trip are.

SMO can produce T-SQL scripts to manage SQL Server. The scripts can take into account dependences and can trace operations used to manage SQL Server.