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.
Prev. page
[1]
2
next page 
