I have stated in previous columns that, while I do like to play with new technology, I usually don’t put it into production just because it is the latest and greatest, unless there is a need.  Such was the case with SQL Server 2005.  Back when SQL Server 2005 was in beta 2, my group started quite a large project using SQL Server 2000 and went with that for quite a while.  But a number of issues arose pertaining to the complexity of some of the reports needed in this application.  Still not thinking about SQL Server 2005, we trudged ahead with trusty SQL Server 2000. 

 

As the beta 2 cycle was nearing its end, a co-worker and I were complaining about some of the heinous T-SQL we were writing in many of our stored procedures to meet the requirements of some of the complex reports.  As we were talking one day we wondered if SQL Server 2005 provided a better way to do some of the things we were doing in SQL Server 2000.  We started looking into the many new features of SQL Server 2005 and came across many of the new T-SQL enhancements such as Common Table Expressions (CTE’s), the PIVOT and UNPIVOT operators, as well as others. 

 

I don’t know if I have ever seen a company migrate to a new technology so fast as we did that day.  Within a matter of a couple of days we had migrated to SQL Server 2005 and Visual Studio 2005, and have not looked back since.  Many of the new T-SQL enhancements have saved us countless hours in programming and we have seen a pretty good improvement in application and database performance as well.  In my last column I discussed how we were able to utilize the xml data type as a parameter in many of our stored procedures, and we will shortly be using the support for HTTP endpoints that SQL Server 2005 has available.  We have not yet looked at any of the new BI features of SQL Server 2005, even after all the pushing and prodding from the president of the local SQL Server user group here in the area, who happens to be a huge BI fan. 

 

However, this column is not about any of those features, rather it is about something else that we thought we wouldn’t need to use so soon.  I, along with several others in my group, have quite a bit of experience with SQL-DMO (SQL Distributed Management Objects).  However, with the recent introduction of SQL Server Management Objects (SMO), we recently found ourselves taking a look at some of that functionality.  Therefore, this article will take a look and discuss some of the new features in SMO.

 

Just like SQL-DMO, SMO provides you with a set of objects that allows you to manage many aspects of Microsoft SQL Server.  SMO contains a vastly improved programming and object model to provide a more robust programming experience.

 

The list of new features include the following:

 

• Improved performance.
• Support for the XML data type.
• Advanced scripting.
• HTTP endpoint support.
• Service broker support.
• Synonym support for object names.
• Support for Notification Services.
• Snapshot isolation
• The ability to trace and replay SQL Server events.
• Improved database object coverage.

 

I have not listed all the new features here, in fact I probably have not listed ½ of them.  There are many more new features to SMO and if you are using SMO or are thinking about using SMO then I recommend taking a look at the complete list, found in the SQL Server BOL (Books-Online).

 

The functionality provided SMO is provided by the different namespaces that make up the SMO object model and programming model.  Each namespace provides a specific functionality within SMO.  Those namespaces and the functionality they provide are listed in the following table:

 

Namespace	Description
Microsoft.SqlServer.Management.Smo	Classes and enumerations used to manage SQL Server programmatically.
Microsoft.SqlServer.Management.Common	Classes common to both SMO and RMO (Replication Management Objects).
Microsoft.SqlServer.Management.Smo.Agent	Classes used to manage the SQL Server Agent.
Microsoft.SqlServer.Management.Smo.Wmi	Classes used to manage the WMI provider.
Microsoft.SqlServer.Management.Smo.RegisteredServers	Classes used to manage the Registered Server.
Microsoft.SqlServer.Management.Smo.Mail	Classes used to manage database mail functionality.
Microsoft.SqlServer.Management.Smo.Broker	Classes used to manage the Service Broker.
Microsoft.SqlServer.Management.Nmo	Classes used to manage Notification Services.

 

Unless you are going anything with WMI (Windows Management Instrumentation), SQL Server Service Broker, or Notification Services, the Microsoft.SqlServer.Management.Smo namespace will provide most of the functionality you need.  Connections to SQL Server are handled via the Microsoft.SqlServer.Management.Common namespace, as this namespace contains all the necessary classes used to establish and maintain connections to SQL Server instances and their connection settings.

 

The Microsoft.SqlServer.Management.Smo namespace provides the ability to view and modify SQL Server options and settings, view and modify SQL Server engine objects, backup and restore databases (via access to databases and database backup devices), as well as provide access to all DDL (data definition language) tasks.  This namespace also lets you view and script database dependencies.

 

For example, the following code illustrates how to use the Microsoft.SqlServer.Management.Common and Microsoft.SqlServer.Management.Smo namespaces to create a connection to SQL Server

 

Dim ServerConn As Management.Common.ServerConnection

Dim SqlServerSelection As Management.Smo.Server

ServerConn = New Management.Common.ServerConnection

 

ServerConn.ServerInstance = "MySqlServer"

ServerConn.Login = "sa"

ServerConn.Password = "mysecretpassword"

 

ServerConn.SqlExecutionModes = Management.Common.SqlExecutionModes.ExecuteAndCaptureSql

 

ServerConn.ConnectTimeout = 300

ServerConn.Connect()

SqlServerSelection = New Management.Smo.Server(ServerConn)

 

Using the Common class of the Microsoft.SqlServer.Management.Common namespace, a connection is created and opened to an specific instance of SQL Server.  That connection is then handed to the Server class of the Microsoft.SqlServer.Management.Smo namespace at which time I can freely use the Microsoft.SqlServer.Management.Smo namespace to work with SQL Server objects.

 

For example, now that I have a connection to SQL Server, I can now restore a database from a backup device.

 

Dim db1 As Management.Smo.Database

Dim restore As Management.Smo.Restore

Dim backDeviceItem As Management.Smo.BackupDeviceItem

Dim RelDBF As Management.Smo.RelocateFile

Dim relLOG As Management.Smo.RelocateFile

 

db1 = New Management.Smo.Database

db1.Name = "MyHugeDatabase"

 

restore = New Management.Smo.Restore

 

restore.Action = Management.Smo.RestoreActionType.Database

 

restore.Database = db1.Name

 

backDeviceItem = New Management.Smo.BackupDeviceItem("MyHugeDatabaseBackup", Management.Smo.DeviceType.LogicalDevice)

 

restore.Devices.Add(backDeviceItem)

 

restore.ReplaceDatabase = True

 

restore.SqlRestore(SqlServerSelection)

 

Very simple and straight forward.  This example used the Restore and Database classes to create an instance of a Restore and Database object.  A new BackupDeviceItem is created, passing it the name of the backup device that contains the backup.  A couple of properties are set on the Restore object, such as telling it to replace the existing database.  The restore operation is then kicked off.

 

Now, this example assumes that you have everything on a single machine.  But what happens if you copy backup devices back and forth between machines?  For example, what if I create a database and backup device on my pc with everything on drive C, but my co-worker wants to use my database backup, but he has everything on drive D?

 

Well, the above code won’t work because if my co-worker runs the above code the restore will fail because it can’t find the database on drive C.  When a database is backed up to a device, it contains information as to where it was backed up from.

 

However, this is easily remedied by replacing the following code from above:

 

backDeviceItem = New Management.Smo.BackupDeviceItem("MyHugeDatabaseBackup", Management.Smo.DeviceType.LogicalDevice)

 

With this code:

 

RelDBF = New Management.Smo.RelocateFile

relLOG = New Management.Smo.RelocateFile

 

backDeviceItem = New Management.Smo.BackupDeviceItem("d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"MyHugeDatabase.bak", Management.Smo.DeviceType.File)

 

RelDBF.LogicalFileName = "MyHugeDatabase.dbf"

relLOG.LogicalFileName = "MyHugeDatabase_log.ldf"

 

RelDBF.PhysicalFileName = "d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyHugeDatabase.mdf"

 

relLOG.PhysicalFileName = "d:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyHugeDatabase_log.ldf"

 

restore.RelocateFiles.Add(RelDBF)

restore.RelocateFiles.Add(relLOG)

 

Now when my co-worker restores from the backup, the restore knows that the backup is actually in a different location and to restore into his database.

 

Summary

SMO is nothing trivial, in fact if you take a look at the information on SMO in the SQL Server 2005 Books Online you would see how big and encompassing it really is.  The examples in this article don’t even begin to show you how broad SMO really is, but hopefully you can start to get a sense of how significant it is.