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.
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?
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.
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 SmoCollectionBase
t. 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 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.
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.
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
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.
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
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.
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.
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.
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
.
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
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.
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.
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
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
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.
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.
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
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.
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']."
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 DependencyTree
s: 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]
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
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.