SMO Explained from Scratch to End
Introduction:
We will Start up with Basic Concepts and Let us end up with creating one
successful tool like query analyzer of our own using SMO. 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
- The ServerConnection object variable can be
used to provide connection information
- The server object property can be set to
provide connection information
- 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 transfer
Transfer 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);
