The SQL SMO Solution

SMO, DMO, who knows right?  Well, they are from a concept stand point the same thing.  They provide the ability programmatically manage SQL Server instances.  It is the way they do it that has changed.  SMO is a set of .NET assemblies and can be used via PowerShell, the DMO is not the DMO is also depreciated and is not available with SQL Server 2012 or greater, so they new way forward is the SMO (SQL Management Objects).

What can you do with the SMO?

Simple, SQL Server Management Studio uses these objects to aid in the management of any SQL Server instance.  If you have permissions to do so, then you can connect to it using the SMO.

Try this, Open a new project in Visual Studio, Include the following references into your project.  A new console project is fine for this.

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.ConnectionInfoExtended
  • Microsoft.SqlServer.Management.Sdk.Sfc
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoExtended
  • Microsoft.SqlServer.SqlEnum

Not all of these are used for this example but I have included so they are there in case you do with to expand on it.

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Server;
using Microsoft.SqlServer.Management.Smo;

Include those references into the code.  With these now in the code, we can now connect to the server and display the list of databases as a simple example.

Server server;
server = new Server(new ServerConnection("(local)");

//Connect to the server and use a secure login
server.ConnectionContext.LoginSecure = true;
server.ConnectionContext.Connect();

This is connecting to a local SQL Server instance but this can be a remote database if you can connect to it then you can through the SMO.

The SecureLogin property specifies if it is using Windows Authentication or SQL Server Authentication.  Since I am not providing a username or password I am using Windows Authentication to log into this server.

NOTE:

The Server object doesn’t support the System.IDisposable interface.  Why?  Not sure.  Since it is best practice to manage the scope of objects by implementing the using Statement (not the directive).  But you will see an error stating that the Server object can’t be converted to the System.IDisposable interface, which means it doesn’t implement it.

But when the variable goes out of scope it is automatically disconnected from the server.  But this is by no means a get out of jail free card for writing code that doesn’t clean up after itself.  To assume that the guys who developed the SMO got all of their stuff right is too much. 

Now we are connected to the database, we can now look at it like we can with any other object in code. 

foreach (Database tempDB in server.Databases)
{
	Console.WriteLine(tempDB.Name);
}

//Disconnect from the server
server.Disconnect();

Press CTRL+F5 to compile without debugging and you will see a list of the databases on your local system.

image

The blurred out ones are specific to work I do.

That is a simple as it gets really.  If you start looking at then the specific database object you will find Stored Procedures and Tables, Views, etc…  each collection enables enumeration through to get specific objects.

I will post more about the SMO as I delve into it more.  Before using the DMO, I would use SQL queries to get back data about the database.

SELECT * FROM sys.Tables

As an example certainly but there is many more ways to skin that cat.

Versions

This is just a quick note on version information for use of the SMO.  The version I have used is .NET 4.5 for my examples I have written in code, the samples above are version agnostic.  But the SMO was built using .NET 4.0.  This doesn’t mean you can’t use it with older versions of .NET.  I have tried it with a project that is written with .NET 3.5.  and it works.  They should work in most circumstances, but I have seen instances where using .NET 2.0 runtime will cause issues with the resolving of namespaces.  So if you are going to target a version make sure it is the latest version.

Also, the SMO can be used to manage SQL Server 2005 or greater, which is all currently supported versions of SQL Server, but service packs might also be required to make sure that they work.

But a note, when you add the references, you will get a dialog informing you that the runtime is not correct.

image

This dialog will show for all references that do not match the target framework version specified.  Though I do note that it doesn’t provide a version number in there.  But when I added this and then changed the Target framework version to be .NET Framework 4 this dialog did not display.

But I have tested it in .NET Framework 3.0 and there are namespace issues, .NET Framework 3.5 worked.  .NET Framework 4 works as did .NET Framework 4.5.

It was something that I wanted to bring to your attention.  So running the SMO using Visual Studio 2008 or better should be fine as this support the .NET Framework 3.5. 

To implement the SMO with older versions of .NET then older versions of the SMO will need to be obtained and you can get these from Microsoft.

Advertisements

Posted on October 8, 2013, in Development, Michael Rogers and tagged , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: