SMO Explained from Scratch to End

SMO Explained from Scratch to End

Introduction:

What is SMO

SMO(SqlServer Management Object) name itself indicated that Object model is responsible for managing Sql Server Programitically like you can create Database Objects dynamically and perform lots common DBA tasks including Backups, Restores,Index Maintenance, Integrity checks and more.. I am going to start series of articles which will guide you how to use SMO Object model in .net world and Let us end up with creating one successful tool like query analyzer of our own using SMO(SqlServer Management Object).

• How to use Smo Object Model in .NET
• What Tasks You can perform using SMO(SqlServer Management Object)
• Create Sql Query Analyzer Like Tool

Before we will discuss anything about SMO,let me clear you one thing ,When ever you are going to use SMO Object Model be sure your Program includes Reference to Microsoft.SqlServer.Management.Smo namespace. Actually there are two ways to do this. The first one is by using SQL-DMO (Distributed Management Objects) which is a set classic classes that were used to programmatically manipulate and interact with SQL Servers, usually it was used before .Net World. With time and arrival of great .NET CLR Platform, lots of new changes were made to SQL-DMO Object model, which was named as SMO[Server Management Objects].True,name itself indicates object model is used to manipulate SqlServer programmatically .So we can say SQL Server Management objects (SMO) exposes the functionality of SQL Server database and replication management. SMO can be used to automate repetitive tasks or commonly performed administrative tasks. The SQL SMO is implemented as a .NET assembly and the model extends and replaces the SQL Server Distributed Management object (DMO) of the earlier versions. A number of enhancements have been made such as partial instantiation, capture mode execution, delegated execution, objects in space and integration with .NET framework. There are certain features that all Server Management objects share such as running methods, setting properties and manipulating collections. Specific tasks can be programmed using SMO objects. These include complex subjects that are required by programs with specialized functions such as backing up, monitoring statistics, replication, managing instance objects and setting configuration options.

SMO Object Model

The SMO object model is a hierarchy of objects with the Server object at the top level and all the instance objects residing within the Server object. The ManagedComputer is the top level class and has a separate object hierarchy. It represents Microsoft SQL Server services and network settings that are available through the WMI provider. A number of utility classes represent tasks such as Transfer, backup or Restore. The Model is made up of several namespaces and uses the System.Data.SqlClient object driver to connect to and communicate with different instances of SQL Server.
The SMO client requires SQL Server Native Client that is part of the SQL Server 2005/2008 and .NET Framework 2.0/above versions too.
Applications in SMO require the installation of Visual Studio 2005/2008.
Transaction processing in SMO is done by using the ServerConnection object which is referenced by the ConnectionContext property of the Server object. Methods such as StartTransaction, RollBackTransaction and CommitTransaction are available to the user.
SMO assembly provides classes categorized in following six namespaces:

Microsoft.SqlServer.Management.Smo	Contains instance classes and utility classes that are used to programmatically manipulate SQL Server.
Microsoft.SqlServer.Management.Smo.Agent	Contains classes that represent the SQL Server Agent.
Microsoft.SqlServer.Management.Smo.Broker	Contains classes that represent Service Broker.
Microsoft.SqlServer.Management.Smo.Mail	Contains classes that represent SQLiMail.
Microsoft.SqlServer.Management.Smo.RegisteredServer	Contains classes that represent Registered Server.
Microsoft.SqlServer.Management.Smo.Wmi	Contains classes that represent the WMI Provider.

It is not possible to upgrade a SQL DMO application to SMO. The application will have to be rewritten using SMO classes.
A number of SMO namespaces are not supported by earlier versions of SQL Server. Some of the unsupported namespaces are:

• Microsoft.SqlServer.Management.NotificationServices
• Microsoft.SqlServer.Management.Smo.Broker
• Microsoft.SqlServer.Management.Smo.Mail
• Microsoft.SqlServer.Management.Smo.RegisteredServer
• Microsoft.SqlServer.Management.Smo.Wmi
• Microsoft.SqlServer.Management.Trace

Microsoft.SqlServer.Management.Smo.Agent and Microsoft.SqlServer. Management. SMO namespaces are partially supported. Some classes in Microsoft.SqlServer. Management.Smo provide support for SQL Server 2000 and SQL Server 7.0 while some distributed Management objects have been laid off  in the transition.

Programming SQL Server Management objects

Connecting to SqlServer:

There are three ways to connect to SQL Server

1. The ServerConnection object variable can be used to provide connection information
2. The server object property can be set to provide connection information
3. The name of the SQL Server can be passed in the Server object constructor.

Let us see how to connect programitically 

public void ConnectSQLServer()
        {
            server.ConnectionContext.ServerInstance = ServerAndInstanceName;
            if (this.useWindowsAuthentication)
            {  server.ConnectionContext.LoginSecure = useWindowsAuthentication;
           } 
           else            
             { 
                server.ConnectionContext.LoginSecure = this.useWindowsAuthentication;
                server.ConnectionContext.Login = this.userName;
                server.ConnectionContext.Password = this.password;
            }            
             server.ConnectionContext.Connect();
        }           

Let we See How SMO Views or interacts with  SQL Server  Objects, as you can see from Fig1 that  
Let we See How SMO Views or interacts with  SQL Server  Objects, as you can see from Fig1 that SQL Server contains Objects in Tree View Control,just to show the SMO Object hierarchy like
Server Instance : contains below sub Objects

1.Databases Folder	Contain all Databases
2.Data Transformation Services	Local packages,Meta data service package...
3 Management	Sqlserver Agent,Backup,current activity,Sqlserver log, Maintaince plan
4 Replication	Publications, Subscriptions
5 Security	Logins, Server Roles, Linked Servers, Remote services
6.support services	SQLMail,Distributed Trans Coordinator
7.Meta data services	Content

< Seeing Further clasifucation,below figure show how other objects are arranged under Root or Parent Objects,Anyways Concern here is now ,How can we Interact with these objects

Figure 1

Let us list of SQL servers available on the network

public void GetServers() 
{ 
// Get a list of SQL servers available on the network 
DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false); 
foreach (DatRow row in dtServers.Rows) 
{ string sqlServerName = row["Server"].ToString(); 
if  (row["Instance"] != null && row["Instance"].ToString().Length > 0) 
sqlServerName += @"\" + row["Instance" ].ToString();
 } 
}            

Let us Retrieve list of databases

public  List<string>GetDatabaseNameList()
 { 
List<string>dbList = new  List<string>(); 
foreach(Database db in server.Databases) 
dbList.Add(db.Name); 
return dbList; 
}        

Let us Retrieve list of tables within database  using SMO

public void List<string>GetTableNameList(Database db) 
{ 
List<string> tableList =new  List<string>(); 
foreach (Table table in db.Tables) 
tableList.Add(table.Name);
return tableList; 
}         

Let us Retrieve list of StoredProcedures within database  using SMO

public void List<string> GetStoredProcedureNameList(Database db) 
{ 
List<string> storedProcedureNameList = new List<string>(); 
foreach (StoredProcedure storedProcedure in db.StoredProcedures)
 storedProcedureNameList.Add(storedProcedure.Name); 
return storedProcedureNameList;
}             

Let us Retrieve list of views within database  using SMO

public void List<string>GetViewNameList(Database db) 
{ 
List<string> viewNameList = new  List<string>(); 
foreach (View view in db.Views)
 viewNameList.Add(view.Name);
 return viewNameList;
 }           

Let us Retrieve Column Names of Table using SMO

public void List<string>GetColumnNameList(Table table) 
{ 
List<string> columnList = new List<string>(); 
 foreach (Column column in table.Columns)
 columnList.Add(column.Name); 
return columnList; 
}           

Let us Retrieve User Names using SMO

public void List<string>GetUserNameList(Database db)
 { 
List<string>userNameList = new  List<string>(); 
 foreach (User user in db.Users) 
userNameList.Add(user.Name); 
return userNameList;
 }

 I think it is enough for playing with the existing objects of the database. Now most important part of it is how to create our own objects like          database,tables,procedures..etc.

Let we see  one by one how to  create sqlserver database objects likea>

How to Create Table using SMO

Once you are connected to SQL Server 2005 with a SMO connection, you can start manage and create object. Here is an example on how you can create a table with a promary key (this sample is written in C# and use a beta version of SQL Server 2005 and of the .Net Framework 2.0). The Object sqlserver, is the object created after the connection in this post about smo connection
 In order to connect to SQL Server using SMO you have to reference the following namespace (and the corresponding assembly):

using Microsoft.SqlServer.Management. Smo;
using  Microsoft.SqlServer.Management. Smo.Agent;
using  Microsoft.SqlServer.Management. Common;
     Table tbl;
      Column col;
     Index idx;
    tbl = new Table(sqlserver.database, "MyTable");
     col =  new Column(tbl, "MyCol1", DataType.Int);
     tbl.Columns.Add(col);
     col.Nullable =false;                    
    // Add the primary key      index  idx =  new Index(tbl, "PK_MyTable");
      tbl.Indexes.Add(idx); 
idx.IndexedColumns.Add(new IndexedColumn(idx, col.Name));
idx.IsClustered =true;
idx.IsUnique =true;
idx.IndexKeyType = IndexKeyType.DriPrimaryKey;
 col = new Column(tbl, "myCol2", DataType.SmallInt);
tbl.Columns.Add(col);
col.Nullable = false; // Create the table tbl.Create();                       

Transfer a database; all objects and data

Server tgt =    new Server(".");    
// Setup source connection (in this sample source   is .\inst1 and target is '.' (the default instance)
Server svr =  new Server(@".\inst1");Database db = svr.Databases["testdb"];

            // Setup transferTransfer t =     new Transfer(db);
 t.CopyAllObjects = true;
 t.DropDestinationObjectsFirst = true;
t.CopySchema = true;
 t.CopyData = true;
t.DestinationServer = ".";
t.DestinationDatabase = "testdb";
 t.Options.IncludeIfNotExists = true;

  // Do the work
  t.TransferData();  
  // Or use ScriptTransfer()
  if you need to capture the script (without data)                                   

SMO connection

This sample is written in C# and use SQL Server 2005 beta 2 and a beta version of the .Net Framework 2.0. It show how, with SMO you can Connect to SQL Server 2005
In order to connect to SQL Server using SMO you have to reference the following namespace (and the corresponding assembly):

using Microsoft.SqlServer.Management.Smo;
using  Microsoft.SqlServer.Management.Smo.Agent;
using  Microsoft.SqlServer.Management.Common;
 //The following code allow you to connect with the intergrated  security :          
 ServerConnection conn;Server sqlserver;
conn = new ServerConnection();
conn.ServerInstance = "My Server"
 conn.LoginSecure = true conn.Connect();             
sqlserver = new Server(conn);
The object sqlserver is the object on with youe are going to work on
each SMO project (to have to list of database for example).
2) The following code allow you to connect to SQL Server with the SQL
Server authentification :
erverConnection conn;
Server sqlserver;
conn = new ServerConnection();
conn.ServerInstance = "My Server";
 conn.Login = "My login";
 conn.Password = "My Password";
 conn.LoginSecure =true;
 conn.Connect();
 sqlserver = new Server(conn);