SMO - Manage your SQL Server!

• Download demo project - 21.95 KB

Introduction

May I introduce you to SMO, the Framework provided by Microsoft to manage your Microsoft SQL Servers? This article is a quick start tutorial. SMO is a replacement of the SQL-DMO - it's now managed code and it supports the features of the SQL Server 2005. The intention of this article is to show what SMO is, explain some basic steps, and to make you hungry for more.

Advantages

• .NET: Use your well known framework to develop your SQL Server applications.
• OOP: It's much easier to browse the object hierarchy with IntelliSense than browsing hundreds of stored procedures.
• Exceptions: SMO uses exceptions in an exemplary way.

Disadvantages

• Requires .NET Framework to be installed on the client PC
• Works only with Microsoft SQL Servers
• Needs SMO to be installed

Note that most (or even all) features provided by the SMO are also available as stored procedures on the SQL Server. So you could say the SMO is a wrapper for the stored procedures on the SQL Server.

System Requirements

• .NET Framework 2.0
• SQL Server 2000 or 2005 (NOTE: SMO Supports features that are new in SQL Server 2005 and do not work with SQL Server 2000)
• SMO needs to be installed

Skill Requirements

• Basic C# knowledge + Windows Forms
• Basic SQL Server knowledge
• About 30 minutes of time

Adding a Reference to SMO

To use the SMO, you have to add it as a Reference to your Project. This screen shots have been made with Visual Studio 2005 Express Edition.

Solution Browser - > References -> Add Reference

Select SQL Server Components and Hit OK.

Getting Connected

The first thing we have to do is make a connection to our server.
Now you might be thinking "Hey, there is already a class existing to connect to a SQL Server - System.Data.SqlClient.SqlConnection", and you are all right - you can use this class to build your connection to the SQL Server.

Microsoft.SqlServer.Management.Smo.Server server;
///  <summary>
/// Initializes the field 'server'
/// </summary> 
void InitializeServer()
{
    // To Connect to our SQL Server - 
    // we Can use the Connection from the System.Data.SqlClient Namespace.
    SqlConnection sqlConnection = 
      new SqlConnection(@"Integrated Security=SSPI; Data Source=(local)\SQLEXPRESS");

    //build a "serverConnection" with the information of the "sqlConnection"
    Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = 
      new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);

    //The "serverConnection is used in the ctor of the Server.
    server = new Server(serverConnection);
}

Object Hierarchy

Once you have got a connection to your server - accessing databases is very simple. Most of the SMO objects are stored in a Parent/Child Collection ownership.
A Server has got a collection of databases (The databases Parent is the Server), a database has got a collection of Tables, a Table has got a collection of Columns.....

//this Code adds all known Databases to a Listview

//clean up the listview first.
listView1.Clear();
listView1.Columns.Clear();

//building the Columns
listView1.Columns.Add("Name");
listView1.Columns.Add("# of Tables");
listView1.Columns.Add("Size");

//iterate over all Databases
foreach( Database db in server.Databases )
{
    //add the Data to the listview.
    ListViewItem item = listView1.Items.Add(db.Name);
    item.SubItems.Add( db.Tables.Count.ToString() );
    item.SubItems.Add(db.Size.ToString());
}

This code shows how to enlist Backup Devices:

listView1.Clear();
listView1.Columns.Clear();

listView1.Columns.Add("Name");
listView1.Columns.Add("Location");

foreach (BackupDevice backupDevice in server.BackupDevices)
{
    ListViewItem item = listView1.Items.Add(backupDevice.Name);
    item.SubItems.Add(backupDevice.PhysicalLocation);
}

Create a New Database

Of course - we are not limited to getting information about our SQL Server - we can also create, drop and alter objects. Most SMO objects have 2 requirements - a valid (unique) Name and a valid Parent.

database.Name = dbName.Text;
database.Parent = server;
database.Create();

You see - SMO uses really compact code :-) Now - let's create a backup device.

backupDevice.Parent = Server;
backupDevice.Name = "myBackupDevice";
backupDevice.PhysicalLocation = @"C:\myNewBackupDevice.bak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;
backupDevice.Create();

Scripting with T-SQL!

In some cases, you might want to have a T-SQL script of an operation. Let's take the example from above - we want a script for adding a Backup Device to our SQL Server.

backupDevice.Parent = Server;
backupDevice.Name = "myBackupDevice";
backupDevice.PhysicalLocation = @"C:\myNewBackupDevice.bak";
backupDevice.BackupDeviceType = BackupDeviceType.Disk;
StringCollection strings =  backupDevice.Script();
//results:
// strings [0] = "EXEC master.dbo.sp_addumpdevice  @devtype = N'disk',
// @logicalname = N'myBackupDevice', @physicalname = N'C:\myNewBackupDevice.bak'"

Doing a Backup

Finally, I want to show you how to do a backup of your database. Note that the class Backup doesn't represent a BackupDevice - it represents a "Backup Operation".

Backup backup = new Backup();
//we assume that there is a Logical Device with the Name "myBackupDevice"
backup.Devices.AddDevice("myBackupDevice", DeviceType.LogicalDevice);
backup.Database = "Master";
backup.SqlBackup(server);

Additional Features

The functional range of SMO is amazing!
SMO supports really everything you will need.
Indexes,
Constraints,
Relationships,
Permissions,
Stored Procedures,
Full Text Catalogues,
HTTP Protocol,
Triggers,
Mirroring,
Replication,
Asymmetric Encryption,
.
.
.

In short:
Everything you desire :)
And if you understand the basics of a specific feature, you won't have problems to implement it with SMO.

History

• 04/11/06 - First version of article
• 04/12/06 - Fixed wrong requirements (thx to akosows) - Added hint for SQL-DMO
• 05/31/06 - Minor changes