SQL Server Management Objects (SMO), first introduced in SQL Server 2005 to replace DMO, made it much easier to manage SQL Server from scripts, and automate common SQL Server administrative tasks programmatically.

Yes, that is for what SMO is: it's an API, or object library, written on top of .NET framework that allows us to write code to manage SQL Server and to automate common SQL Server tasks. The SMO object library has a logical object structure, with the Server object at the top, and within the hierarchy, we can find objects to manage practically every SQL Server feature you care to name. For example, SMO includes a Database object for managing all database level objects (tables, views, stored procedures, and so on), a JobServer object for managing the alerts and jobs, a Login object for managing logins, and more.

Using any scripting or programming language that supports objects instances, we can write programs or interfaces that use SMO objects to manage SQL Server tasks. In this stairway, we'll write PowerShell programs that will load the SMO libraries into the PowerShell memory space and allow us to work with the SMO objects. Therefore, this Stairway assumes a working knowledge of PowerShell. If you're a PowerShell novice you can still follow along, but will need to refer to the SQL PowerShell stairway, or elsewhere, for the PowerShell details.

Over the course of the stairway, we'll use PowerShell and SMO for relatively simple tasks such as scripting out a database, to stopping, pausing or restarting a SQL Server services as part of a broader maintenance routine, to more complex tasks such as testing all SQL Server instances for compliance with our documented best practices for the settings of various options and properties. We can even use SMO to export the results to an Excel or Word document!

In this first level, we will introduce some core SMO concepts, such as namespaces, and then begin to explore the SMO object model, and the sort of useful tasks we can perform with two of the most important SMO classes, namely Server and Database.

Why do I need SMO? Can't I just automate tasks with T-SQL?

Yes, you can. While the primary focus of T-SQL is data manipulation, it is also capable of performing routine database maintenance and administration tasks. In fact, there is no magic going on under the covers of SMO; just T-SQL. We can see this for ourselves; start a SQL Profiler trace on a SQL Server instance, and then run the simple PowerShell script in Listing 1, which loads the SMO assembly, instantiates a new Server objects for a particular SQL Server instance (called DeathStar, in my case) and lists the values of the properties of this server object (deeper details of how this script works will come as we progress).

$MyServerInstance='DeathStar'#The SQL Server instance
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $MyServerInstance 
$Server 

Listing 1

The Profiler trace output will look as shown in Figure 1.

Figure 1

As you can see it runs a bunch of T-SQL statements to return all of the required property values for the server. So, you are probably thinking "Laerte, if it is T-SQL, why not just use T-SQL?"

The first answer is that it would take approximately 500 lines of T-SQL to return the same information that we retrieved with three lines of PowerShell and SMO. Everything we need is already encapsulated in the SMO Objects, so we don't need to worry about which metadata to query to retrieve the information. Also, these 500 lines of T-SQL will be subtly different for each different version of SQL Server, because each new version incorporates new features and metadata information. When we use SMO, the same script will work on all versions of SQL Server. Of course, SMO changes between versions too, but the huge difference is that we don't need to have tons of T-SQL lines in different scripts for each version, all of which we need to maintain.

With SMO, we just need to load the correct assembly and then access the SMO objects. In this way, SMO centralizes the information: Consequently the maintenance of the code will be much, much easier. Also, if I need to scale out the code to several instances I just need to add the new instance in my code!

Of course, there are still many routine tasks that we can handle perfectly well in T-SQL, and then automate by using tools such as SQLCMD. Also, there are tasks, such as creating database objects, which are much easier and more efficient to do in T-SQL than in SMO. However, many database tasks, such as scripting out SQL Server Objects, are hard and inefficient using T-SQL while SMO makes the same task ridiculously easy to script and automate.

Armed with a powerful scripting language, focused on automation, and the entire SQL Server encapsulated in objects, it becomes a trivial job to write smart, automated processes. If, for example, you run a T-SQL script on all of your SQL Server instances every morning, to check for failed jobs, then my advice is to stop doing that! Instead, automate the task using SMO and PowerShell; it reduces repetition, human error and boredom, and makes time for much more creative tasks.

Over the course of this stairway, I intend to demonstrate real world examples the sort of database tasks for which SMO is ideally suited.

Installing SMO

SMO is available in any version of SQL Server since SQL Server 2005 and it is automatically installed when you install the SQL Server Management tools. You can also install it separately, without SQL Server, by selecting Shared Management Objects from the SQL Server feature pack.

By default, the DLLs are installed in the C:\Program Files (x86)\Microsoft SQL Server\XXX\SDK\Assemblies.

The SMO Namespaces

SMO uses namespaces to organize into 'workspaces' objects of related functionality so that every object can be referred to without ambiguity, much like we use database schemas to group database objects in logical application areas.

Figure 2 shows the SMO namespaces, implemented as assemblies (DLLs).

Figure 2

The classes in the main Microsoft.SqlServer.Management.Smo namespace represent all of the core database engine objects, such as server instances, databases, tables, stored procedures, index and so on. The .Common namespace provides connection classes that allow us to control connections settings, run T-SQL statements, manage transactions, and so on. We can use the .WMI namespace to stop, start, and pause SQL Server and SQL Server Agent services, set protocols, network libraries and IP addresses from SQL Server instances.

Beyond those, we have namespaces the provide classes to manage SQL Server agent settings, operators, alerts and jobs (.Agent), manage all aspects of Service Broker queues, routes, messages types, contracts, events and so on (.Broker), and more. I'll describe each namespace in more detail, as and when we use this in examples.

Each of our scripts will need to load the objects in the required namespaces, as we saw in Listing 1, where we used the LoadWithPartialName command to load the Microsoft.SqlServer.SMO assembly.

Listing 2 uses the same approach to load the WMI assembly and then instantiates an instance of the ManagedComputer class and uses its Services property (a collection of Service objects, each one representing a SQL Server service) and returns the Name and ServiceState of all SQL Server services on that instance.

$MyServerInstance='DeathStar'#The SQL Server instance
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | out-null
$Server = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $MyServerInstance
$Server.Services |
Select Name,ServiceState 

Listing 2

Though still widely used, LoadWithPartialName is considered a deprecated way to load assemblies since if you've installed multiple versions of SQL Server you may load the wrong version of the assembly (though newer versions tend to be backward compatible). An alternative is to use the Add-Type cmdlet, which also use to specify a specific version, but also means that we also have to specify the Culture and the PublicKeyToken of the required assembly.

Add-Type –AssemblyName "Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" 

In fact, perhaps the easiest way to install and load the SQL Server PowerShell (SQLPS) module, which will then automatically load and register all of the assemblies. Simply replace any LoadWithPartialName lines of code with the following:

import-module 'sqlps' -DisableNameChecking

The SMO Object Model

A class is a specification of how to provide some service. In providing this service, we create objects from this class, and control their state (properties), behavior (methods), events, or elements. For instance, let's say we have a dog class that lives in the animals namespace. A dog has properties such as size, color, and pedigree. Also, it can pee and poo (methods).

SMO namespaces contain a lot of classes that we can work with, and they split down broadly into two categories:

• Instance Classes – represent the core SQL Server database engine objects, such as databases, tables, stored procedures, triggers and so on, all of which we have access to having first connected to the SQL Server instance.
• Utility Classes – groups of objects that have been created to perform a specific task in SQL Server independently of the SQL Server Instance, such as:
  • Transfer class – transfer schema to another database. We can use it to script every object in database, as I'll demonstrate later.
  • Backup/Restore classes – to backup and restore databases
  • Scripter class – script out specified database objects. It also controls the mapping out of object dependencies and relationships.

All the objects are hierarchically represented. At the top of the hierarchy are the server instances, followed by databases, tables, columns and so on, as shown in Figure 3, which is extracted from the complete SMO object model diagram here: SMO Object Model Diagram.

Figure 3: The SMO object model

We'll encounter many of these classes, collections, objects and properties as we progress through the stairway. For now though, let's take a first look at two very important classes, the Server class, and the Database class.

The Server class

The class Server represents an instance of SQL Server. Beneath it in the hierarchy are all manner of properties, collections and other classes and objects that we can use to manage instance-level settings and options, as well as to access the core database-level objects. Figure 2 shows just a few of those that we'll use frequently in the stairway.

Figure 4: Server Object and Collections

We'll cover all of these in more detail as we progress, but briefly:

• Databases property –  represents a collection of Database objects, one for each database on the SQL Server instance, which allow us to get and set various database-level properties (collation setting, compatibility level, and many more), and to manage and manipulate all database level objects (tables, views, stored procedures, and so on).
• Configuration class – allows us to get the server configuration options, which you would otherwise access using the sp_configure system stored procedure.
• Information class – returns various bits of read-only information about the SQL Server instance such as the SQL Server (Version) and operating system version (OSVersion), the number of processors installed (Processors) whether or not the instance IsClustered, and more.
• Settings class – allows us to get and sometimes set various configurable instance-level settings, such as the default BackupDirectory.
• JobServer class – we use JobServer object objects for managing the alerts and jobs on the server.
• Login class – each login object represent a SQL Server login. We can, for example, use its EnumDatabaseMappings method to list all users in every database with its associated login.

Working with the Server class

The Server class has 3 constructors:

• Server() – initialize a new instance of Server class.
• Server(String) – initializes a new instance of the Server class with the specified name
• Server(ServerConnection) – initializes a new instance of the Server class based on the specified connection

When we create a class, constructors set the default values of the input parameters. For example, if we have a class Dog with properties Name and Pedigree, we might want to ensure that no-one can instantiate a Dog object without supplying values for the Name and Pedigree parameters.

Server()

With the Server() constructor, we do not pass any parameters, so it will use the trusted connection and the default SQL Server instance; in fact, it will only find the local default SQL Server instance. Listing 4 will return the server name and then the name of each database on that instance, plus the values of all of the various database properties and methods, for each database.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server')
$Server.Name 
$Server.Databases 

Listing 4

The results of this are shown in:

Figure 5

Server (String)

If we want to specify a named SQL Server instance, we simply pass the name as a parameter, using the Server(String) constructor, as we saw previously in Listings 1-3.

As another example, Listing 5 returns MaxServerMemory and SQL Server version information for the DeathStar instance.

$MyServerInstance='DeathStar'#The SQL Server instance
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server')
                                                   $MyServerInstance
$Server.Configuration.MaxServerMemory
$server.Information.Version  

Listing 5

Server(ServerConnection)

Finally, with the Server(ServerConnection) constructor, we create a new Server object based on the connection string that we pass in. This is useful when you want to change, by instance, on which port the SQL Server instance is listening, or don't want to use the trusted connection, because you can pass the SQL Login and Password in the connection string to work with SQL Authentication.

Using this constructor is not quite as straightforward as it may first appear. For example, Listing 6 specifies the connection string for DeathStar in the constructor and then attempts to list out the name of each database.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') ("data source = DeathStar; initial catalog = master; trusted_connection = true;")
$server.Databases.name

Listing 6

Hmm, but something is not quite right here; we see the following error:

The following exception occurred while trying to enumerate the collection: "Failed to connect to server data source = DeathStar; initial 
catalog = master; trusted_connection = true;.".
At line:3 char:1
+ $server.Databases.name
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator 

The connection string is right but it is showing connection failed. What is happening? If we take a look in the MSDN documentation for the Server (ServerConnection) constructor we will see that the type of the ServerConnection parameter isn't a string, which is what we passed in; its value is a value Type of: Microsoft.SqlServer.Management.Common.ServerConnection

Figure 6

It means that we need to pass a parameter as a ServerConnection object. There is more. The ServerConnection class has a lot more constructors allowing you a flexible way to connect in the SQL Server instance, allowing us to specify particular connection parameter and other connection information, such as defining the database default, specifying SQL Login and Password details, changing the port, and so on.

Listing 7 shows the correct version of our previous attempt to list the name of each database on DeathStar.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.Management.Common')  | out-null
$ServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$ServerConnection.ConnectionString = ("data source = DeathStar; initial catalog = master; trusted_connection = true;")
$Server = New-Object Microsoft.SqlServer.Management.Smo.Server ($ServerConnection)
$server.Databases.name 

Listing 7

The Database class

Each database on the SQL Server instance is represented by a Database object, which we instantiate from the Database class. It offers a vast array of properties that allow us to, for example:

• Get and set database level options such as FORCED PARAMETERIZATION, AUTO_SHRINK and many others.
• Access database object collections, such as schemas and tables, along with their indexes and constraints.
• Manage other database objects such a stored procedures, views roles and so on.
• Access FileGroups and LogFiles collections, which contain the objects to define the physical files used by the database.

It also offers methods that will allow us to create, alter and drop these database objects, find the permission set for a given object, execute queries on the database, and much more.

Figure 7 shows only the properties of the Database object that allow us to access views, stored procedures, user defined functions, roles and users. You can see that these objects, in turn, expose many useful properties, allowing us to see the parameters required by a stored procedure or function, or to return the definition of a view or stored procedure (TextBody) and so on.

Figure 7

We'll use the Database object and its properties and methods extensively as we progress through the stairway, but in this level, let's look at a few very simple examples of what is possible.

Listing 8 accesses the tables collection of a database, and the columns collection of each table, then uses the datatype property to alter the collation of any columns with textual data types, in all tables.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null
$Server= New-Object ('Microsoft.SqlServer.Management.Smo.Server') "DeathStar"
$Server.Databases['Alderaan'].tables |
Foreach-Object {
    $_.columns |
    Where-Object { $_.datatype -match "char|varchar|nvarchar|text|ntext|nchar"} |
    Foreach-Object {
        $_.Collation = 'SQL_Latin1_General_Cp437_BIN'
        $_.alter()
    }
}

Listing 8

It is far easier to script out database objects in SMO than in T-SQL. There are a couple of different ways to do this, depending on what you need, using either the Transfer or Scripter utility classes, alongside the Database class. The Transfer class is a quick 'utility' way of scripting out an entire database and all its objects in the right build order. Under the covers, it uses the Scripter class and some other magic to do this.

Listing 9 shows a very simple way to use the Transfer object to script out every object in a database to a single file.

Import-Module SQLPS -DisableNameChecking
$MyServerInstance = "DeathStar"
$MyDatabase = "Alderaan"
$MyScriptPath ="$($env:HOMEDRIVE)$($env:HOMEPATH)\Documents\$($MyDatabase).sql" 
$My='Microsoft.SqlServer.Management.Smo'
$s = new-object ("$My.Server") $MyServerInstance
$transfer = new-object ("$My.Transfer")  $s.Databases[$MyDatabase]
$transfer.ScriptTransfer() | Out-File $MyScriptPath 

Listing 9

This will work fine, producing a file with a database build script, but you'll soon want more. You won't want a separate PowerShell script for every database and server, for example, so you'd want to pass parameters to it. You might also want to generate a script to drop the existing database objects in the right order, if they exist. You would probably want to save the contents of those tables with static data in them that are required for the proper functioning of the database. You will probably want to store each routine in a separate file if you like working from a script or need to save into source control. You may only want to save the tables, and their dependent objects such as indexes, constraints and triggers.

Listing 10 shows how to use the Scripter class to script out tables in the Alderaan database. It uses the options property of the Scripter object to access the scripting options, and specifies the scripting of each table's dependent objects. It then simply uses the Script method to generate the script for each table, using the Urn class to uniquely identify each object.

$MyServerInstance='DeathStar'#The SQL Server instance
$MyDatabase='AdventureWorks2014'#The SQL Server instance
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$Server= New-Object 'Microsoft.SqlServer.Management.Smo.Server' $MyServerInstance
$scrp = New-Object 'Microsoft.SqlServer.Management.SMO.Scripter' $Server
$scrp.Options.ScriptDrops = $false
$scrp.Options.WithDependencies = $true
$scrp.Options.IncludeIfNotExists = $true
$scrp.Script($server.Databases[$MyDatabase].tables.urn)

Listing 10

Of course, we could expand this technique to create individual script files for each object, in subdirectories, perhaps as required to store in a version control system.

Summary

That is, it for now guys. I hope this first level has given you a taste of what SMO can do! We've introduced the namespaces and the SMO Object model and taken a 'first look' at the sort of tasks we can perform with the Server and Database classes.

Of course, we've only just scratched the surface. The next article will expand on the Server and Database classes, and show to create a complete inventory of a database. Stay Tuned. I will be back.