SMO (SQL Server Management Object) in SQL Server 2005/2008
By : Kasim Wirama, MCITP, MCDBA
SMO has been introduced since SQL Server 2005. The earlier version of SMO is DMO (Distributed Management Object) which is used by previous version of SQL Server 2005. What is purpose of SMO and DMO? The purpose of SMO and DMO is to bring SQL Server administrative feature into flexibility format to be reused by application developer to build custom SQL Server administration tool. Examples of SQL Server administration activities that might be incorporated into custom application are attaching database when an application setup is installed or building simple database security management as part of an application or building automated backup restore that runs without SQL Server Agent. Both SMO and DMO come in form of APIs library (wrapped into assembly) that contains libraries to accomplish the above task examples.
SMO comes in assembly called Microsoft.SqlServer.Smo.dll, and it is in Program Files\Microsoft SQL Server\100\SDK\Assemblies folder for 32 bit.
Below are list of useful SMO namespaces with the description :
1. Microsoft.SqlServer.Management.Common
It contains SMO common class and RMO (Replication Management Object).
2. Microsoft.SqlServer.Management.Smo
It contains core classes and enumerations for manipulating SQL Server
3. Microsoft.SqlServer.Management.Smo.Agent
It contains classes for SQL Server Agent.
4. Microsoft.SqlServer.Management.Smo.Wmi
It used when implement Wmi functionality (conjunction with assembly Microsoft.SqlServer.SqlWmiManagement.dll)
5. Microsoft.SqlServer.Management.Smo.RegisteredServers
It is used when working with registered servers (conjunction with assembly Microsoft.SqlServer.SmoExtended.dll)
6. Microsoft.SqlServer.Management.Smo.Mail
It is used when implementing database mail
7. Microsoft.SqlServer.Management.Smo.Broker
It is used for SQL Server Service broker functionality
SMO could be used to administer from SQL Server 7.0 until latest release of SQL Server (SQL Server 2008).
If administration functionality could be delivered with Transact SQL (T-SQL), why do we bother to use SMO? The answer is because of security. T-SQL execution exposes risk of SQL injection, SMO reduces attack surface because developer defines their own functions when interacting to SQL Server through compiled .NET code. It is not possible to attack with SQL injection when administration function is done through SMO and compiled .NET code.