In the first level of this series I outlined just enough of the basic concepts of SMO to get you started with using it. I then described how to perform some simple operations in order to provide an example of how SMO might be used. In this level we will go deeper into the detail of some essential operations that underline a script that automates a process via SMO, such as connecting to a SQL Server Instance, listing out the databases, checking errors, killing processes, and finding out the current settings of a server.

Connecting to the server

It is very likely that the first thing you will need to do with SMO is to connect to a SQL Server instance. This will involve making sure that SMO is loaded, and then using it to connect to the SQL Server Instance. In the level 1, we saw that the Server class has some constructors and that we can use one of these to connect using Windows Authentication. This may be all you need, but it could be that you would require to do it other ways, or to check that the connection is working properly. We’ll start with simple Windows Authentication and work our way through some examples.

Connecting using Windows Authentication

Let’s first see what databases there are on our DeathStar instance. We’ll simply connect to the SQL Server instance called DeathStar and list out the databases

import-module 'sqlps' -DisableNameChecking #load all of SMO
#now connect to the server 
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server')  'DeathStar'

$Server.databases.name #and list out all the databases

Listing 1

If we were lucky, then we’ll see the list of databases. However, if we aren’t lucky, then we see an error.

The following exception occurred while trying to enumerate the collection: "Failed to connect to server IDoNotExist.".
At line:1 char:1
+ $server.Databases.name
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException
    + FullyQualifiedErrorId : ExceptionInGetEnumerator

Listing 2

Let’s check that. Imagine that we actually want to connect to a whole lot of servers just to see if they are running. We might decide that we want to do so by determining their name, which is a property of the server object.

import-module 'sqlps' -DisableNameChecking #load all of SMO 
#now connect to the server
$theServerList = ('DeathStar', 'DoesntExist', 'NeitherDoI')
foreach ($servername in $theServerList)
{
    $Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername
    $Server.name #and give the name
} 

Listing 3

Which results in:

DeathStar
DoesntExist
NeitherDoI 

No errors. Well, ‘DeathStar’ exists but neither 'DoesntExist' nor 'NeitherDoI' do. SMO hasn’t bothered to make the connection because it already knows the name. It only makes a connection when it really has to. It has created that $server object without actually connecting it to a real server, which is sensible just as long as you’re aware of that.

In short, just because your server constructor you supply doesn't cause any errors, it doesn't mean you are connected to the instance. This could cause problems if, for example, you had a list of servers to contact but you only wished to get the list of databases from the ones that were up, running and accessible. You’d need to check whether a connection succeeded.

To verify the connection succeeded, without attempting to access any databases, we can use the Server.Connectioncontext property, which returns a ServerConnection object containing details of the current connection to the SQL Server instance, and then call the Connect method of that object to attempt to establish a connection to, say, the IDoNotExist instance. Here is a simple test of our solution.

$ErrorActionPreference = "Stop"
import-module 'sqlps' -DisableNameChecking #load all of SMO 
 $ServerName = 'IDoNotExist'
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
try {
    $Server.ConnectionContext.connect()
    $Server.Name
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Listing 4

Now we see a connection error:

Exception calling "Connect" with "0" argument(s): "Failed to connect to server IDoNotExist."
At line:9 char:5
+     Write-Error $_.Exception.Message
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Write-Error], WriteErrorException
    + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException 

So now we can use this error-handling system in a routine that will list the databases if you can contact the server and otherwise just gives you a warning that it can’t connect to the server. It doesn’t produce an error.

import-module 'sqlps' -DisableNameChecking #load all of SMO 
#now connect to the server
$theServerList = ('DeathStar', 'DoesntExist', 'NeitherDoI')
foreach ($servername in $theServerList)
{
    $Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $servername
    try
    {
        $Server.databases.name
    }
    catch
    {
        $WarningPreference='continue'
        Write-warning "$server is not contactable"
    }
    finally
    {
        $server.ConnectionContext.Disconnect()
    }
    
}

Listing 5

Which produces something like this:

AdventureWorks
master
model
msdb
Northwind
tempdb 
WidgetDev
WidgetLive
WidgetTest
WARNING: [DoesntExist] is not contactable
WARNING: [NeitherDoI] is not contactable 

Now you won’t be able to detect, just from looking at this result, the final problem that you’ve really got to fix. What happens is that the application hangs for a long time before deciding that the server is not connected, because it has to assume the worst possible connectivity. It is much better to tell it how many seconds to wait. Let’s try that out by specifying the timeout with a simple test script.

Setting the timeout before connecting

import-module 'sqlps' -DisableNameChecking #load all of SMO 
$ServerName = 'DeathStar'
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
try {
    $server.ConnectionContext.ConnectTimeout = 1
    $Server.ConnectionContext.connect()
    Write-Host 'I am connected'
    #do whatever you need to do it
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
} 

Listing 6

So we can now add the line:

     $server.ConnectionContext.ConnectTimeout = 1 #number of seconds to wait

To our script so that we don’t have to wait a long time for SMO to decide that a server can’t be reached.

What if we want to use this script with servers outside the domain? We have to then use SQL Server authentication. This makes things a bit trickier but we can get over it.

Connecting using SQL Server Login with no secure login

If we want to connect using SQL Server authentication rather than Windows authentication, we need to set the LoginSecure property to false, and then supply the SQL Server login credentials. The simplest, but unusable, way is demonstrated in Listing 6.

$ErrorActionPreference = "Stop"
import-module 'sqlps' -DisableNameChecking #load all of SMO 
 $ServerName = 'DeathStar'
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
try {
    $Server.ConnectionContext.LoginSecure = $false
    $server.ConnectionContext.Login = "laerte"
    $server.ConnectionContext.Password = "laerte"
    $Server.ConnectionContext.connect()
    Write-Host "I am connected"
    #do whatever you need to do it
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Listing 7

As you can see, we have connected to the SQL Server with explicit Login and Password, including the password, in plain text. Of course for security reasons, this is not a best practice. We need to do a lot better than that.

Using Get-Credential

We can ask the user for the password. This method is a little bit better but is not fully automated because a popup screen will appear to get the credentials from you:

import-module 'sqlps' -DisableNameChecking #load all of SMO 
 
$login = Get-Credential -Message 'Please provide your SQL Credentials'

$ServerName = 'DeathStar'
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
try {
    $Server.ConnectionContext.LoginSecure = $false
    $Server.ConnectionContext.Login = $login.UserName
    $server.ConnectionContext.set_SecurePassword($login.Password)
    $Server.ConnectionContext.connect()
    Write-Host 'I am connected'
    #do whatever you need to do it
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Listing 8

Although this isn’t ideal for a busy DBA doing a whole lot of admin chores, this is sometimes the best approach in a production setting where each administrator must use their own credentials but scripts are kept centrally.

Storing the Password in a file

Other approach, if you don’t want to keep typing in your passwords, is to set the password in an encrypted file and then use it. For that first we need to store the password:

read-host -assecurestring | 
convertfrom-securestring | 
out-file C:\temp\cred.txt

It will create a cred.txt file with your encrypted password. Then it is just a matter of loading the credentials and everyone is happy. This will normally be stored in your user area $env:USERPROFILE to ensure that other users cannot ‘borrow’ your credentials and that each use has their own credentials when they ‘hotseat’ the same desktop machine. This means that the final line will be

out-file "$env:USERPROFILE\cred.txt"

We would then use it like this.

import-module 'sqlps' -DisableNameChecking #load all of SMO 
 
$password = get-content C:\temp\cred.txt | 
            convertto-securestring
$credentials = new-object ('System.Management.Automation.PSCredential') 'laerte',$password

$ServerName = 'DeathStar'
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
try {
    $Server.ConnectionContext.LoginSecure = $false
    $Server.ConnectionContext.Login = $credentials.UserName
    $server.ConnectionContext.set_SecurePassword($credentials.Password)
    $Server.ConnectionContext.connect()
    Write-Host 'I am connected'
    #do whatever you need to do it
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Listing 9

Now there can be other difficulties in connecting. We might need to connect on a different port if security dictates that. Here is a test script to illustrate how to do it.

Connecting on a specific port

import-module 'sqlps' -DisableNameChecking #load all of SMO 
 $ServerName = 'DeathStar'
$Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$ServerName,1433"
try {
  
    $Server.ConnectionContext.connect()
    Write-Host 'I am connected'
    #do whatever you need to do it
} catch {
    Write-Error $_.Exception.Message
} finally {
    $server.ConnectionContext.Disconnect()
}

Listing 10

Connecting to all the servers that are registered in SSMS.

So, we have now worked out how to access a whole list of servers, how to authenticate via windows or via SQL Server authentication, how to check whether a server is available, and how to change other connection settings such as the server port. You may think ‘I don’t have to worry about that when I’m using SSMS. I just connect and it knows my credentials for each server’. If you are registering your servers in SSMS, or use a central management server, you can do the same thing with SMO. We just change the routine slightly and you can have the routine tell you all the databases for all your registered servers.

get-childitem 'SQLSERVER:\SQLRegistration\database Engine Server Group' -recurse|
   where {$_.GetType() -notlike '*ServerGroup*'} | #exclude groups
    Select servername, connectionstring | #select just the name and the connection string
     sort-object -property servername -unique | #and exclude possible duplicates
  foreach-object{ #for each server you've registered in SSMS
    $Server = new-object ('Microsoft.SqlServer.Management.Smo.Server')
    try #contacting the sever and listing the databases
    {    $server.ConnectionContext.ConnectTimeout = 1
         $server.ConnectionContext.ConnectionString=$_.ConnectionString
         $Server.databases.name
    }
    catch #any error
    {
        $WarningPreference='continue' #to be sure to see warnings
        Write-warning "$($_.servername) is not contactable"
    }
    finally #disconnect from the server
    {
        $server.ConnectionContext.Disconnect()
    }
    
} 
}

Listing 11

By doing it this way, you don’t have to keep credentials in files at all. This is safe because SSMS keeps the credentials encrypted in the user directory which isn’t accessible unless you are logged in. You can easily keep your lists on the Central Management Server instead, with the advantage that the whole team can share the list, but with the drawback that credentials aren’t stored for Servers such as Azure that require SQL Server Authentication. I’ll explain a lot more about registered Servers and the Central Management Server later on in this stairway series

Some cool operations using Server Class

Now that we have some reliable ways of creating a server object and connecting to a whole lot of servers, we can do a wide range of operations. Where we have so far just been boringly listing the names of the databases, in the line …

$Server.databases.name

But we can swap that out for anything you wish. For the rest of this stairway, I’ll be demonstrating a few of these things.

Read the SQL Server Error Log

One of the some interesting characteristic of OOP is to have methods in a class that they have the same name but different argument list keeping the user interface consistent, only the outputs are different. It's called overloaded methods.

To read the SQL Server Error Log we use the method ErrorLog. The overloading list is:

• ErrorLog() - Enumerates the SQL Server error log.
• ErrorLog(int32) - Enumerates the specified SQL Server error log. Int32 It's an integer addressed to the index number of the Error Log where 0 it's the current one and – to – 9 the oldest

The Last Error Log

$server.ReadErrorLog()

Listing 11

-------               ----------- ----                                                                                                                                   
02-May-16 11:16:06 PM Server      Microsoft SQL Server 2014 - 12.0.4213.0 (Intel X86) ...                                                                                
02-May-16 11:16:06 PM Server      UTC adjustment: -3:00                                                                                                                  
02-May-16 11:16:07 PM Server      (c) Microsoft Corporation.                                                                                                             
02-May-16 11:16:07 PM Server      All rights reserved.                                                                                                                   
02-May-16 11:16:07 PM Server      Server process ID is 3168.                                                                                                             
02-May-16 11:16:07 PM Server      System Manufacturer: 'System manufacturer', System Model: 'System Product Name'.                                                       
02-May-16 11:16:07 PM Server      Authentication mode is MIXED.      

Specifying the Error Log to display

$server.ReadErrorLog(3)

Listing 12

There is much more you can do, such as to filter what you want to check. This looks for any line with the word ‘error’ in it:

($server.ReadErrorLog()).where{$_.text -like '*Error*'}

Listing 13

The last 24 hours logs

We can even filter for a range of dates by accessing the logdate field of the log entries. This example filters only those entries from the past 24 hours.

($server.ReadErrorLog()).where{ $_.logdate -ge ((Get-Date).addhours(-24))}

Listing 14

Killing process

The $server object has many more methods in it. You can view them, of course, by using …

$server|get-member

Or, if you’re using the PowerShell IDE, using intellisense.

Here we are using the KillProcess() and KillAllProcesses() methods which, unsurprisingly kill SQL Server processes.

$server.KillAllProcesses('Alderaan') #Kill all process in a specifc database
$server.KillProcess(42)  #Kill specific process

Listing 15

Automating a simple task

Let’s say you need to check for the logins mapped as sysadmin in your SQL Server Instances:

(get-childitem 'SQLSERVER:\SQLRegistration\database Engine Server Group' -recurse).where{($_.gettype() -notlike '*ServerGroup´*')} | 
Select  servername, 
        connectionstring | #select just the name and the connection string
sort-object -property servername -unique -PipelineVariable ServerInstance  | #getting the SQL Server list in the Registered Servers
ForEach-Object  {
    $Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $_.ServerName
    try {
        $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
        $server.ConnectionContext.connect() #connecting
        ($Server.Logins).where{($_.listmembers() -contains 'sysadmin')}  | #checking for the SA logins
        Sort-Object -Property LoginType | #ordering by SQL and Windows Logins
        Select @{N='SQL Server Instance Name';E={$ServerInstance.ServerName}},
               Name,
               LoginType,
               CreateDate #selecting the output
    } catch {
        Write-Error $_.Exception.Message
    } finally {
        $server.ConnectionContext.Disconnect()
    } 
} 

Listing 16

Even more is possible, of course: Let’s imagine that you just want to check for the last day logins created as SA: for that we just need to add a condition in the where method:

   ($Server.Logins).where{($_.listmembers() -contains 'sysadmin' -and $_.createdate -ge (get-date).AddDays(-1))}

Then for security reasons, you need to enforce password expiration and Password expiration to every SQL Login in your systems, excluding SA accounts and those ones created automatically when install SQL Server (with ## in the name).

import-module 'sqlps' -DisableNameChecking #load all of SMO 
$ErrorActionPreference = 'Stop’
(get-childitem 'SQLSERVER:\SQLRegistration\database Engine Server Group' -recurse).where{($_.gettype() -notlike '*ServerGroup´*')} | 
Select  servername, 
        connectionstring | #select just the name and the connection string
sort-object -property servername -unique -PipelineVariable ServerInstance  | #getting the SQL Server list in the Registered Servers
ForEach-Object  {
    $Server=New-Object 'Microsoft.SqlServer.Management.Smo.Server' $_.ServerName
    try {
        $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
        $server.ConnectionContext.connect() #connecting
        ($Server.Logins).where{($_.logintype -eq 'SqlLogin' -and $_.name -notmatch 'sa|##' )}  | #getting only the SQLLogins excluding SA and ## accounts
        ForEach-Object {
            try {
                $_.PasswordPolicyEnforced = $true #enforcing  PasswordPolicy
                $_.PasswordExpirationEnabled = $true #enforcing PasswordExpiration
                $_.alter()
            } catch {
                Write-Error "Could not change properties to login $($_.name) Error : $($_.Exception.Message)"
            }
        }
    } catch {
        Write-Error $_.Exception.Message
    } finally {
        $server.ConnectionContext.Disconnect()
    } 
}

Listing 17

Remember, you can check all the properties, methods and events you can use to perform your operations and mainly, automate them using the simple PowerShell Get-Member:

$Server | Get-Member

And of course, after that, check the MSDN documentation on the class which is good for information about how to use it.

Creating a Server Inventory

One of the more common settings in a SQL Server Instance that you might need to store as a report are those of sp_configure. Without these reports it is very difficult to be certain that there has been a change. SMO in conjunction with PowerShell can perform this operation easier.

The sp_configure procedure in T-SQL corresponds to the Configuration Class -Configuration Property in SMO and you can get and set all these properties that you’re used to seeing displayed when you run sp_configure. You can see all these by running:

$serversource.Configuration | 
Get-member

Figure 3

Here is some simple code to list only those properties that you’re interested in:

@{'Max Server Memory'=$Server.Configuration.MaxServerMemory.RunValue;
  'Min Server Memory'=$Server.Configuration.MinServerMemory.RunValue;
  'Optimize Adhoc Workloads'=$Server.Configuration.OptimizeAdhocWorkloads.RunValue;
  'Agent XPs Enabled'=$Server.Configuration.AgentXPsEnabled.RunValue;
  'Default Backup Compression'=$Server.Configuration.DefaultBackupCompression.RunValue;}

Name                           Value                                                                                                                                                         
----                           -----                                                                                                                                                         
Max Server Memory              2147483647                                                                                                                                                    
Optimize Adhoc Workloads       0                                                                                                                                                             
Agent XPs Enabled              1                                                                                                                                                             
Default Backup Compression     0                                                                                                                                                             
Min Server Memory              8 

Listing 18

Of course, you can list them all out by merely iterating through the displaynames and values of the $ server object using a pipeline

$Server.Configuration.properties|select displayname,runvalue

Listing 19

The output is not so esthetically beautiful, but you can personalize as you want it. PowerShell and SMO allow you to present the data just as you wish.

Here is an example

$Server.Configuration.properties|select displayname,runvalue|
  ConvertTo-Html -Title 'Server Information' -Body 'I am the automator. Brace yourselves' |
    Set-Content "ServerConfiguration.html"

Summing up

We’ve covered the topic of creating a server object and then connecting it to a SQL Server instance. This is at the heart of most SMO operations. We’ve wanted to show how to cope with Windows Authentication, SQL Server credentials and unusual port numbers. We then extended this to show how you could connect to a list of servers, one after the other, or even to the servers that you’ve already registered in SSMS.

Using these techniques, we’ve shown just a few things you can do just with the server object. Just to end up, here is an example script that creates a single JSON document for all your registered servers, containing the configuration information you’re most interested in along with the name of the server, and anything else you need of course, if you go on to extend the example.

import-module 'sqlps' -DisableNameChecking #load all of SMO 
$TheConfigData = get-childitem 'SQLSERVER:\SQLRegistration\database Engine Server Group' -recurse |
where { $_.GetType() -notlike '*ServerGroup*' } | #exclude groups
Select servername, connectionstring | #select just the name and the connection string
sort-object -property servername -unique | #and exclude possible duplicates
foreach-object{
    #for each server you've registered in SSMS
    $Server = new-object ('Microsoft.SqlServer.Management.Smo.Server') 
    try #contacting the sever and listing the databases
    {
        $server.ConnectionContext.ConnectTimeout = 1 #set a low timeout
        $server.ConnectionContext.ConnectionString = $_.ConnectionString #and connect
        @{# create a data object for each server
                'ServerName' = $server.urn.GetAttribute('Name'); #get the full server/instance name
                #add any amount of server information you want in the same way
            'Configuration' = @{ #and create a dictionary list of the config items
                'Max Server Memory' = $Server.Configuration.MaxServerMemory.RunValue;
                'Min Server Memory' = $Server.Configuration.MinServerMemory.RunValue;
                'Optimize Adhoc Workloads' = $Server.Configuration.OptimizeAdhocWorkloads.RunValue;
                'Agent XPs Enabled' = $Server.Configuration.AgentXPsEnabled.RunValue;
                'Default Backup Compression' = $Server.Configuration.DefaultBackupCompression.RunValue;
            }
        } # you can add any interesting collections in the same way.
    }
    catch #any error
    {
        $WarningPreference = 'continue' #to be sure to see warnings
        Write-warning "$($_.servername) is not contactable"
    }
    finally #disconnect from the server
    {
        $server.ConnectionContext.Disconnect()
    }
}
ConvertTo-Json $TheConfigData #or XML if you fancy it.

Which would give you:

[
    {
        "Configuration":  {
                              "Max Server Memory":  2147483647,
                              "Optimize Adhoc Workloads":  0,
                              "Agent XPs Enabled":  0,
                              "Default Backup Compression":  0,
                              "Min Server Memory":  0
                          },
        "ServerName":  "Yoda"
    },
    {
        "Configuration":  {
                              "Max Server Memory":  2147483647,
                              "Optimize Adhoc Workloads":  0,
                              "Agent XPs Enabled":  1,
                              "Default Backup Compression":  0,
                              "Min Server Memory":  8
                          },
        "ServerName":  "ChewBacca
    },
    {
        "Configuration":  {
                              "Max Server Memory":  2147483647,
                              "Optimize Adhoc Workloads":  0,
                              "Agent XPs Enabled":  1,
                              "Default Backup Compression":  0,
                              "Min Server Memory":  8
                          },
        "ServerName":  "DarthMaul"
    },
    {
        "Configuration":  {
                              "Max Server Memory":  2147483647,
                              "Optimize Adhoc Workloads":  0,
                              "Agent XPs Enabled":  1,
                              "Default Backup Compression":  0,
                              "Min Server Memory":  16
                          },
        "ServerName":  "Clonetrooper"
    }
]