home articles news code blog links search  
 
         
  Code Samples

Articles and Presentations

sqldbatips.com blog

  
Getting Started with SMO in SQL 2005

Author Jasper Smith Hits 10269
Create Date 22-08-2004 Last Updated 08-08-2007
Versions SQL2005    

Overview

In this series of articles, I'll demonstrate how to use SMO (SQL Management Objects) to do a variety of common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more. These are some of the operations available in the SMO database maintenance utility available on this site - ExpressMaint. In this article we will concentrate on how to build a SMO application and how to connect to a SQL Server and retrieve some server properties. Example code will use VB.NET and C#. For those of you that don't have access to Visual Studio 2005 I'll include the command line compiler commands.. I'd also highly recommend the SMO sample applications that are part of the SQL Server Engine samples that come with SQL2005 and are also available for download here.

Building SMO applications

In order to use the SMO objects in a .NET application we need to add a reference to the SMO assemblies. The easiest way to do this in Visual Studio 2005 is to choose Project>Add Reference from the main menu and add select the following assemblies

  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.SqlEnum
    		•

    There are a number of other assemblies that need to be referenced for certain specific tasks such as managing Service Broker but these core assemblies contain all the fuctionality required for most basic administrative operations. See the SMO Programming reference in Books On Line for more details. If you don't have access to Visual Studio 2005, simply add references to the required assemblies on the compiler command line when compiling applications. An example commandline for the VB.NET and C# compilers is shown below (these can be found in the C:\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx folder) assuming a console application.

    Note that the entire command should be on one line even though the examples span multiple lines for formatting purposes

    [Visual Basic]
	
vbc /t:exe c:\SMOtest.vb /r:
"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"	

[C#]

csc /t:exe /out:c:\SMOTest.exe c:\SMOtest.vb /r:
"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
"C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"

    Getting Connected

    Getting connected to SQL Server could not be simpler. For a default instance connecting with Windows Authentication, we simply need to create a new SMO Server object as shown in the example below and we can begin obtaining information about the server such as the servername and version string.

    [Visual Basic]

Imports Microsoft.SqlServer.Management.Smo

Module SMOTest
	Sub Main()

      Dim svr As Server = New Server()
      Console.Writeline(svr.Name & " " & svr.Information.VersionString)

	End Sub
End Module

[C#]

using System;
using Microsoft.SqlServer.Management.Smo;

namespace SMOTest
{
    class Program
    {
     static void Main()
     {
      Server svr = new Server();
      Console.WriteLine(svr.Name + " " + svr.Information.VersionString);
     }
   }
}

    The same effect can be achieved by passing the server or server\instance in the Server constructor e.g.

    Dim svr As Server = New Server("(local)")
Server svr = new Server(@"(local)\INSTANCE01")

    However, if we want to specify more complex connection options or control the connection pooling behaviour of SMO, we can use the ConnectionContext object. This is a child object property of the Server object so we can specify these options after creating the Server object or by declaring a local variable as a ServerConnection object and passing it to the Server constructor. Using this we can specify such things as the authentication mode and username and password e.g.

    [Visual Basic]

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Module SMOTest
	Sub Main()

        Dim svr As Server = New Server()
        svr.ConnectionContext.LoginSecure = false
        svr.ConnectionContext.Login = "username"
        svr.ConnectionContext.Password = "password"		
        Console.Writeline(svr.Name & " " & svr.Information.VersionString)

	End Sub
End Module

[C#]

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

namespace SMOTest
{
    class Program
    {
     static void Main()
     {
      ServerConnection conn = new ServerConnection();
      conn.LoginSecure = false;
      conn.Login = "username";
      conn.Password = "password";
      Server svr = new Server(conn);	 
      Console.WriteLine(svr.Name + " " + svr.Information.VersionString);
     }
   }
}

    The default behaviour of SMO is to use connection pooling. Connections will be established and released as required. However, this behaviour can be changed by either setting the AutoDisconnectMode property of the ConnectionContext to AutoDisconnectMode.NoAutoDisconnect or by setting the NonPooledConnection property to true. If AutoDisconnectMode is set to NoAutoDisconnect and Connect is explicitly called then the connection is not returned to the pool until Disconnect is called. By setting the NonPooledConnection property to true we are explictily requesting a non pooled connection. The behaviour can be observed by using Profiler to monitor the SMO application as shown in the VB.NET code snippet below.

    Default behaviour - pooled connection

    Dim svr As Server = New Server()
Console.Writeline(svr.Name & " " & svr.Information.VersionString)

    Non pooled connection

    Dim svr As Server = New Server()
svr.ConnectionContext.NonPooledConnection = True
svr.ConnectionContext.Connect()
Console.Writeline(svr.Name & " " & svr.Information.VersionString)
svr.ConnectionContext.Disconnect()

    In the next few articles we will dive deeper into SMO and use it to perform some common DBA tasks such as Backup, Restore and Index maintenance. We will also look at how we can use one of the new features of SMO - Partial Instantiation - to optimize SMO applications.

    Related Articles

       Getting Started with SMO in SQL 2005 - Backups
       Getting Started with SMO in SQL 2005 - Restores
       Getting Started with SMO in SQL 2005 - Integrity Checks
       Getting Started with SMO in SQL 2005 - Verifying Backups

    Print this page  Print this page Email this article  Email this article Add to Favourites  Add to Favourites
    		 
             
    © Copyright sqldbatips Ltd 2008. Contact webmaster home | news | articles | code | blog | links | search  

    free tracking