Easy Coding with SMO

SQL Management Objects (SMO), the new programming access layer in SQL Server 2005, extends and replaces SQL Distributed Management Objects (SQL-DMO). In this article I'll introduce you to SMO and show you how easy it is to use. You'll see how to use Visual Studio 2005 and SMO to develop managed code for three basic but important SQL Server 2005 administrative tasks: We'll build a simple backup application, enhance it for improved ease of use, then create a database and a table. You can then build upon this code, using it as the basis for more advanced tasks. SMO provides you programmatic access to the entire SQL Server object model; whatever you can do in data definition language (DDL), you can now do in object-oriented Common Language Runtime (CLR) managed (i.e., .NET) code. You'll see how much easier and faster you can develop with SMO and Visual Studio 2005 than with those traditional standbys Notepad and SQL-DMO, and you'll see how the programmer-friendly features in Visual Studio reduce coding errors and increase the likelihood that your solution will work correctly the first time.

Enterprise Manager (and its SQL Server 2005 replacement, SQL Server Management Studio) is great for managing a few servers, but DBAs managing multiple servers have learned that solutions programmed or scripted with SQL-DMO not only do a better job of ensuring consistent and repeatable results but they are also better because they don't require human intervention. This means that your solutions can run while you're at home sleeping. Better solutions mean better rest for you!

SMO offers even greater performance and scalability than SQL-DMO provides. (SQL-DMO remains in SQL Server 2005 only for backward compatibility.) SMO gives you full access to all SQL Server 2005 features, and you can use SMO to manage SQL Server 2000 and SQL Server 7.0. More importantly, because SMO is implemented as CLR managed code, you can quickly and easily develop robust solutions using object-oriented programming techniques.

Although you can use any text editor to develop managed code, Visual Studio 2005 offers a highly productive environment for rapid application development (RAD). Visual Studio 2005 speeds the development of SMO applications by providing standard templates, such as those for Windows Forms applications, ASP.NET Web Forms and Web services, console applications, and Windows services. By using a template to create your SMO application, you'll write less code because the template provides prewritten "glue" code that joins the pieces of the application together.

For purposes of this article, I assume that you're already familiar with some version of Visual Studio and with SQL Server. Ideally, you already know how to use Visual Studio to develop simple Windows applications. Although the examples in this article are coded in Visual Basic .NET and C#, if you have experience with Visual Basic, C++, or Java, you should be able to understand the code samples.

Object-Oriented Programming Model Basics

Before we go any deeper, let's briefly review some definitions of object-oriented programming terms. A library is a collection of classes. A class is an abstraction of an object. An object is an instance of a class. For example, a blueprint is a class (abstraction) and the house built from the blueprint is an object. Much like a house is made from a blueprint, an object must be instantiated (made from) a class. Each object is a specific instance of a class. A class has properties, methods, and events that are available for use by objects instantiated from that class. An object has all its properties, methods, and events defined by the class. A property is an attribute, something that defines or describes the object. For example, some of the many properties of a Database object are its name, size, and creation date. Methods—such as Create, Alter, Drop, and Grant—are actions associated with an object. Events are occurrences that are trapped by the object and that can be used to trigger an action. For example, clicking a button on a Windows Form raises a Button Click event. You write custom code to handle the event and to undertake a specific action, such as backing up the database. You'll see these principles of object-oriented programming in action in this article's examples. I explain some object-oriented programming concepts a little later, but first let's start building our backup application.

Building a Backup Application with SMO

We'll start by building an application to back up the AdventureWorks database, the new sample database for SQL Server 2005. With all the DBA tools and third-party backup utilities available, you probably wonder why someone would bother to code a database backup application. Consider a real case that involves a business-to-business data-loading requirement. If the data loading fails, a business need exists to automatically restore the database to the state it was in just before the load. To complicate matters, you can't schedule the backup because the data arrives from the business partners at unpredictable times. SMO lets you develop a fully automated solution that backs up the database immediately when data to be loaded is received.

Begin by starting Visual Studio 2005. Go to the menu bar and choose File, New, Project. When the New Project dialog box appears, select the Project Type and Template for your project, as Figure 1 shows, then click OK. For this example, I chose a Console Application template (which, like sqlcmd and osql, runs in a command window) because it requires the least amount of code to demonstrate object-oriented programming using the .NET Framework class libraries (SMO is part of the .NET Framework 2.0). A little later, when we enhance our database backup example, we'll use a Windows Application template because we want a rich UI. When an application needs to run in the background as a service, a Windows Service template is the appropriate choice.

Next, add a reference to Microsoft.SqlServer.Smo, which is a CLR (i.e., .NET) class library. Go to the Project menu and choose Add Reference. In the Add Reference dialog box, which Figure 2 shows, click the .NET tab, select Microsoft.SQLServer.Smo, then click OK. A code window appears, showing a few lines of code that the Console Application template provides. Delete all the original code in the window, then insert the Visual Basic .NET code that Listing 1 shows or the C# code that Web Listing 1 shows. That's all the code required to back up the AdventureWorks database. It's that easy.

Notice that unsaved changes are marked by yellow edit marks in the far-left margin of your code window. You can save your changes now, but it's not required. When you run the application, Visual Studio 2005 automatically saves changes and the yellow marks change to green. But before you run the SQLBackup application, I recommend that first you review and understand the code. The samples in Listing 1 and Web Listing 1 illustrate several important general object-oriented programming concepts, which I explain in a moment.

Running an application in Visual Studio 2005 is like running a query in SQL Server 2000 Query Analyzer. You start by clicking the Start icon (green triangle) or by pressing the F5 key. A Console window appears while the application is running, and when it disappears (after a minute or so), the database in our example is backed up. You can then use Windows Explorer to confirm that the database backup file was created as C:AdventureWorks.bak.

Some Object-Oriented Programming Concepts Explained

You can see that we began with the Imports statement in Listing 1's Visual Basic .NET code or the using statement in Web Listing 1's C# code. Adding these statements at the beginning of the code lets you use shorthand notation. Although adding this statement is technically optional, it's recommended for speed and accuracy. For example, if you omit the statement, you must enter the string BackupActionType.Database as Microsoft.SqlServer.Management.Smo.BackupActionType.Database.

You must use an object's constructor (the New method in Visual Basic .NET or the new method in C#) to create (instantiate) the real coding object as distinct from its abstract definition that exists in a class library (such as the SMO class library). When a constructor has more than one form, it's known as an overloaded constructor. Each form of an overloaded constructor has a different signature, which is another way of saying each constructor has a unique set of parameters. The Visual Studio IntelliSense feature helps you decide which overload to use by showing you all of a constructor's overloads. (I tell you more about IntelliSense in a moment.)

In object-oriented programming, you usually set at least some of an object's properties after instantiating the object. Our simple database backup application sets four properties: type of backup, name of backup set, name of database to be backed up, and type of backup device. You must call an object's methods to get the object to undertake an action. Our application uses the Backup object's Add method to add the fully qualified backup file's name to the Backup devices collection. A Server object is instantiated to establish a connection. The last step calls the Backup object's SqlBackup method to actually perform the backup.

Some Visual Studio 2005 Advantages

Visual Studio provides two features that greatly improve the speed and accuracy of your coding: IntelliSense and context-sensitive help. IntelliSense greatly simplifies code development compared to writing SQL-DMO scripts in Notepad. As you type code, IntelliSense provides a list and brief description of an object's properties and methods, as Figure 3 shows for the SQLBackup object. To see context-sensitive help about the code that the cursor is pointing to, press the F1 key.

Enhancing the SMO Backup Application

Our application backs up the database, but it has two obvious shortcomings: the database name is hardcoded and the backup isn't verified. It's easy to improve the application to solve these two problems, as you can see in this example when we provide the user with a list of database names to choose from, and we verify the backup by instantiating a Restore object and calling its Verify method.

Now that you've seen the basics of building an application with Visual Studio 2005, let's create an application that has a Windows UI. Windows applications require considerably more code than do console applications, so we start by using a Windows Application template to create a new project. Visual Studio 2005 automatically adds the additional code that the project needs. Unlike earlier Visual Studio releases, Visual Studio 2005 stores autogenerated code in a file separate from user-written code. In Visual Basic .NET, this file is named formname.Designer.vb. In C#, the file is named formname.Designer.cs. This file is hidden by default; to see the file in Solution Explorer, you must select the Show All Files option. You don't manually change the code in the Designer file, although you do change the code indirectly when you add controls to a form. User-written code belongs in either the formname.vb or formname.cs file. If you keep the default form name of Form1, the file for your code will be named Form1.vb or Form1.cs.

In Visual Studio 2005, go to the menu bar and choose File, New, Project. Select a Windows Application template for the language you prefer. Type VerifiedBackup for the project name. The Visual Designer appears with a blank Form object. Using the Toolbox, drag a Button control and a ListBox control onto the Form. Using the Properties window, name the ListBox control Databases and the Button control DoBackup with a Text property of Backup. As in the previous example, add a reference to Microsoft.SqlServer.Smo. You must also add a reference to Microsoft.SqlServer.ConnectionInfo. Double-click the Button control to create an event handler for the button and also to switch to Code View. Your screen should now look similar to Figure 4.

Now we want to make the backup application more user friendly by giving the user a list of database names to choose from. Enhance the SqlBackup application by adding code to a Form Load event handler that will populate the ListBox control with the names of the databases on the server. To back up the database, the user can simply select a database and click Backup. Finally, to verify the backup, add a Restore object and use a MessageBox control to provide backup verification status to the user. Listing 2 and Web Listing 2 show you how.

As with the backup application example, you can replace all the code in Form1.vb with the Listing 2's code or replace the code in Form1.cs with the code in Web Listing 2. First, you must double-click the Form control and the Button control to register your event handlers with the Visual Designer code file. Be sure to use the Form, ListBox, and Button control names Form1, Databases, and DoBackup to match this article's code examples. Keep in mind that C# is a case-sensitive language, which means that doBackup and DoBackup represent different objects.

To make coding even easier and to avoid errors, you may want to download and work with the complete solution instead. After downloading the code, double-click the vbproj or csproj file to load the project into Visual Studio 2005. You might see error messages such as "The automatically saved settings file '%user_documents%visualstudiosettingsvisualstudio8.0currentsettings.vssettings' cannot be found." Don't worry; just click OK. You can ignore these messages because Visual Studio creates the settings when you load the project into Visual Studio.

Now you're ready to run the enhanced backup application. Press F5 or click the Start icon (green triangle). Select a database, then click Backup. As Figure 5 shows, when the backup is complete, a message box appears, showing the backup status. Now let's move on to the third example, creating a database and a table using SMO.

Creating a Database and a Table Using SMO

For many years, database developers have used program code to create tables indirectly. The general approach is to create a string consisting of a T-SQL CREATE TABLE statement that the code sends to the database server to be executed. SMO offers a cleaner, better, object-oriented way of creating database objects directly through code without using T-SQL as an intermediary.

Listing 3 and Web Listing 3 show you how to create both a new database and a table completely in code without writing any T-SQL statements. We use a Database object to create the database and a Table object to create the table. To keep the coding simple, choose the Console Application template and name it CreateTable. (You can add the code to other templates, depending on the type of UI you want.) Next, add references to both Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo.

Now run the application. Use SQL Server Management Studio to examine the database and table you've created. (If SQL Server Management Studio is already running, you may need to first click the Refresh icon in Object Explorer.)

Take Advantage of SMO

As you've seen in these examples, coding using SMO and Visual Studio 2005 is fast and easy. SMO and Visual Studio 2005 offer DBAs and developers tremendous flexibility in developing powerful database applications while minimizing complexity and development time. With better performance, better scalability, and more features than SQL-DMO, as well as backward compatibility to SQL Server 2000 and SQL Server 7.0, SMO in SQL Server 2005 is something to look forward to.