Controlling Logins, Activity Monitoring and Standalone Scripts

My previous article, PowerSMO at Work I, demonstrated how to create and deploy secure, signed DBA scripts and then showed how to use PowerSMO functions to manage the extended properties of SQL Server objects.

In this article we will look how to select the identity that is used to logon to SQL Server and how to build an activity monitor. We will also look at how to create standalone PowerSMO scripts, i.e. scripts that do not require initPowerSMO.ps1 to be run before they are used. Standalone PowerSMO scripts are much more practical for use in production environments.

We will also be looking at some more useful features of PowerShell such as using PowerShell to write powershell scripts, regular expressions, formatting output, default arguments, and local variables.

Login

In the previous articles we ignored the fact that you do not get a free ride when you use SMO; you still have to login to SQL Server. The Get-SMO_Server function, as we have been using it in the previous articles, logs in to SQL Server using the credentials of the person who started PowerShell. This means that you must be running with a Windows identity that has a login on SQL Server that will allow you to access whatever you need to manage. In practice, this means that you make this login a member of the sysadmin server role.

I have a Windows identity, named SqlAdmin, which also has a SQL login that is a member of the sysadmin role. The Windows SqlAdmin identity is an ordinary Windows user with no special Windows privileges; in fact this SqlAdmin is not even a member of the Windows Users group.

SqlAdmin does not have the Windows privileges I need to do my daily work on Windows, and I never use it to logon to Windows. Instead I use the ability of SMO to impersonate a Windows identity. The loginServer.ps1 script, shown below, is the one I use to connect to a SQL Server instance when I need to do some management operations.

param($server, $user, $password)
$conn = SMO_ServerConnection
$conn.ServerInstance = $server
$conn.ConnectAsUserName = $user
$conn.ConnectAsUserPassword = $password
$conn.ConnectAsUser = $true
$conn.ApplicationName =”Ad hoc Management”
$conn.WorkstationID = $ENV:ComputerName
$conn.MinPoolSize=1
$conn.PooledConnectionLifetime=3600
$conn.AutoDisconnectMode=’NoAutoDisconnect’
$server = SMO_Server $conn

I run this script as follows (be sure to run initPowerSMO.ps1 before trying this):

PS C:\SimpleTalk>. .\loginServer.ps1 CANOPUS5 ‘SqlAdmin’ ‘P@ssw0rd’

Be sure to include the “. ” at the beginning of the command line. This script adds a $conn and a $server variable to the context in which you run it. The script uses SMO_ServerConnection make the $conn variable, which specifies how the connection is to be made, and then passes $conn to SMO_Server to create the $server variable.

NOTE:
This script seems to be using some new functions; SMO_ServerConnection and SMO_Server. These are not really new functions that are just making use of a feature of PowerShell that can save you some typing. You can skip typing the “get-” prefix for a command as long as it does not produce a conflict with some other command, so SMO_Server is the equivalent of get-SMO_Server.

The loginServer.ps1 script starts by creating an empty ServerConnection and follows this by filling out the pertinent information for the connection. It gets the server instance name, user name, and password from the command line. It fills out the ApplicationName and WorkstationID to make it easier to identify this connection; we will make use of this shortly.

The $conn.ConnectAsUser = $true part of the script is what is making SMO impersonate a Windows user, using ConnectAsUserName and ConnectAsUserPassword when connecting to SQL Server. The $true variable is a built-in variable that returns a Boolean true value; likewise $false returns a Boolean false value.

The $ENV:ComputerName is a special built-in variable of PowerShell. Any variable that starts with a $ENV: prefix accesses the Windows environment variable whose name makes up the suffix of the variable name. In this case the name of the computer the script is running on is accessed.

When performing management operations, my preference is to make sure there is a ready-to-go connection in the pool while I am working, which is why the minimum connection pool size is set to one.

The end of the loginServer.ps1 creates a $server using $conn to initialize it.

SQL Login

You can also use a ServerConnection to connect using a SQL Server login. The loginSeverSql.ps1 script, shown below, does this.

param($server, $user, $password)
$conn = SMO_ServerConnection
$conn.ServerInstance = $server
$conn.LoginSecure = $false;
$conn.Login = $user
$conn.Password = $password
$conn.ConnectAsUser = $false
$conn.ApplicationName =”Ad hoc Management”
$conn.WorkstationID = $ENV:ComputerName
$conn.MinPoolSize=1
$conn.PooledConnectionLifetime=3600
$conn.AutoDisconnectMode=’NoAutoDisconnect’
$server = SMO_Server $conn

The $conn.LoginSecure = $false and $conn.ConnectAsUser=$false part of the script is what is making SMO use a SQL login instead of a Windows login.

Checking activities

A fairly common management task is to find out which activities are running on a server. In fact SSMS (SQL Server Management Studio) has a Management->Activity Monitor for just this purpose. You can get equivalent functionality from PowerSMO using a SMO_Server to enumerate the processing running on a SQL Server instance, as shown below:

PS C:\SimpleTalk >$server = SMO_Server EARTH5
PS C:\SimpleTalk >$server.EnumProcesses()
| format-table -property Spid,Program,Host,Command -autosize
 
Spid Program                                Host   Command
—- ——-                                —-   ——-
   1                                               RESOURCE MONITOR
   2                                               LAZY WRITER
   3
   4                                               LOCK MONITOR
   5                                               SIGNAL HANDLER
   6                                               TASK MANAGER
   7                                               TRACE QUEUE TASK
   8
   9                                               BRKR TASK
  10                                               TASK MANAGER
  11
  12                                               TASK MANAGER
  13                                               TASK MANAGER
  14                                               TASK MANAGER
  15                                               TASK MANAGER
  16                                               TASK MANAGER
  17                                               TASK MANAGER
  18                                               TASK MANAGER
  19                                               TASK MANAGER
  20                                               BRKR EVENT HNDLR
  21                                               BRKR TASK
  22                                               BRKR TASK
  23                                               TASK MANAGER
  24                                               TASK MANAGER
  51 Microsoft SQL Server Management Studio EARTH5
  52 Ad hoc Management                      EARTH5 SELECT

A SMO_Server has a function, EnumProcesses, which lists all of the processes running on its instance of SQL Server. We are piping those results to the built-in cmdlet, format-table. This outputs a row for each process passed into it so that the results look like at table with column per property of the process. We didn’t want to see all of the properties, so we used the –property parameter of format-table to restrict the results to just the Spid, Program, Host, and Command properties of the process. The –autosize makes each column as narrow as possible.

NOTE:
The Format-table cmdlet, and its companion cmdlet format-list, are very useful. You can type help format-list -full to see all of their capabilities.

We can see that there are quite a few system processes running along with two connections from my machine, EARTH5. SSMS is running on SPID 51 and our “Ad hoc Management” PowerSMO is running on SPID 52. The ApplicationName we set in the loginSever.ps1 script appears as the Program in the properties of a process. The WorkstationID appears as the Host.

Well, now that we have PowerSMO we won’t need SSMS anymore so let’s kill its connection to the server. That is shown next.

PS C:\SimpleTalk >$server.KillProcess(51)

Now let’s check to see if SSMS still has a connection, as shown below

PS C:\SimpleTalk >$server.EnumProcesses($true)
| format-table -property Spid,Program,Host,Command -autosize
LogonUser succedded

Spid Program           Host   Command
—- ——-           —-   ——-
  51 Ad hoc Management EARTH5 SELECT

We didn’t need to list all of the system processes to check the status of the SSMS connection. Passing a $true into the EnumProcesses function limits it to user processes. Here, we can see that Spid 52 is no longer running.

Monitoring activities

One of the nice things about the SSMS Activity Monitor is that you can configure it to poll the status of the server every few seconds to get a sort of real time display. The script, ActivityMonitor.ps1, shown next, works in a similar way.

param($server, $user, $password)
$local:conn = SMO_ServerConnection
$local:conn.ServerInstance = $server
$local:conn.ConnectAsUserName = $user
$local:conn.ConnectAsUserPassword = $password
$local:conn.ConnectAsUser = $true
$local:conn.ApplicationName =”Ad hoc Management”
$local:conn.WorkstationID = $ENV:ComputerName
$local:conn.NonPooledConnection = $true
$local:server = SMO_Server $conn
while($true)
{
      $local:server.EnumProcesses($true) |
      format-table -property Spid,Program,Host,Command -autosize;
      [System.Threading.Thread]::Sleep(10000)
      if([System.Console]::KeyAvailable)
      {
            break;
      }
}

The results of running it are shown below. About every ten seconds the script polls the CANOPUS5 server to see what non-system processes it is running. Between the second and third pass I started up SSMS.

PS C:\Simpletalk > .\ActivityMonitor.ps1 EARTH5 SqlAdmin P@ssw0rd

Spid Program           Host   Command
—- ——-           —-   ——-
  51 Ad hoc Management EARTH5 SELECT

Spid Program           Host   Command
—- ——-           —-   ——-
  51 Ad hoc Management EARTH5 SELECT

Spid Program                                Host   Command
—- ——-                                —-   ——-
  51 Ad hoc Management                      EARTH5 SELECT
  52 Microsoft SQL Server Management Studio EARTH5

The ActivityMonitor.ps1 script starts in way similar to the way that loginServer.ps1 does, it just makes a Server that is connected to the SQL instance you want to monitor. However, there are a couple of differences. Firstly, it is creating $local:conn and $local:server variables. When a variable has a “$local:” prefix it is local to the PowerShell context in which it is run, which prevents it from interfering with the variables in the PowerShell that invoked it. Secondly, $local:conn.NonPooledConnection=$true, which means it gets its own connection rather than using one from the pool.

After the $local:server variable has been created the script creates an infinite loop that is broken after you press a key on the keyboard. PowerShell can use any capability provided by .NET, so we make use of a static property of the System.Console class to see if a key has been typed. In the previous PowerSMO article we learned that when PowerShell sees text inside of square brackets it interprets it as a type definition. A type definition followed by “::” means “access a static member of the type”. So [System.Console]::KeyAvailable accesses the static KeyAvailable property of the System.Console class in .NET.

Inside this loop, PowerSMO runs the same command we used earlier to enumerate the processes running on SQL Server. It follows the command by a ten second delay. The static Sleep method of the System.Threading.Thread class provides a delay specified in milliseconds.

Choosing process properties

The Spid, Program, Host, and Command properties of a SQL Server process are fairly common things to monitor, but you might want to choose a different set. The script, ActivityMonitorSel.ps1, shown below, is an enhancement to ActivityMonitor.ps1 that allows the properties to be specified on the command line.

param($server, $user, $password, $properties)
$local:conn = SMO_ServerConnection
$local:conn.ServerInstance = $server
$local:conn.ConnectAsUserName = $user
$local:conn.ConnectAsUserPassword = $password
$local:conn.ConnectAsUser = $true
$local:conn.ApplicationName =”Ad hoc Management”
$local:conn.WorkstationID = $ENV:ComputerName
$local:conn.NonPooledConnection = $true
$local:server = SMO_Server $conn
$local:conn.Connect()
while($true)
{
$local:server.EnumProcesses($true) |
 format-table -property $properties -autosize
[System.Threading.Thread]::Sleep(10000)
if([System.Console]::KeyAvailable)
{
break;
}
}

This script has modified ActivityMonitor.ps1 in two ways; it adds the $properties argument to the script and it uses that argument in the format-table cmdlet.

We use this script as follows (the first two lines are actually a single line):

PS C:\SimpleTalk> .\ActivityMonitorSel.ps1 CANOPUS5
SqlAdmin P@ssw0rd spid, cpu, status, memusage

Spid Cpu Status  MemUsage
—- — ——  ——–
  51   0                1
  52 187                1
  53   0 running        1

Spid Cpu Status  MemUsage
—- — ——  ——–
  52 187                1
  53   0 running        1

After the password on the command line, we passed in the properties that we wished to monitor, as a comma-separated list. Up until now we have been careful to separate arguments by spaces, not commas. PowerShell treats a sequence of values separated by commas as a single argument. The argument itself is an array with an entry for each of the comma separated values.

The format-table cmdlet interprets an array after -property as the list of properties it should display. In fact, if you look at how we specified the properties when we first used format-table they were in the form of a comma separated list. PowerShell turned this into an array and then passed it to the format-table cmdlet.

Choosing user processes

Let’s make one more change to our activity monitor script so that we can use another feature of PowerShell, namely default arguments. Below are the changes we need to make in order to turn ActivityMonitorSel.ps1 into ActivityMonitorOpt.ps1, which allows us to select whether or not we want to monitor system processes.

param($server, $user, $password, $properties, $userOnly = $true)
…
$local:server.EnumProcesses($userOnly) |
format-table -property $properties -autosize

We have added a $userOnly argument and specified its default value as $true. If we don’t include this value on the command line then ActivityMonitorOpt.ps1 will produce the same output as ActivityMonitorSel.ps1. We can see this below (the first two lines are a single line):

PS C:\SimpleTalk> .\ActivityMonitorOpt.ps1 CANOPUS5
SqlAdmin P@ssw0rd spid, cpu, status, memusage
Spid Cpu Status  MemUsage
—- — ——  ——–
  51   0 running        1

However if we include the $false value on the command line we will get all the processes, including the system processes (the first two lines are a single line):

PS C:\SimpleTalk> .\ActivityMonitorOpt.ps1 CANOPUS5

SqlAdmin P@ssw0rd spid, cpu, status, memusage $false
Spid Cpu Status     MemUsage
—- — ——     ——–
   1   0 background        0
   2   0 background        0
   3   0                   0
…
  20   0 background        0
  21   0 background        0
  22   0 background        0
  23   0 sleeping          0
  51   0                   1
  52   0                   1
  53   0 running           1

Standalone scripts

So far we have been running our scripts inside of a PowerShell that has run the initPowerSMO.ps1 script. There are a couple of issues with this. One is that initPowerSMO.ps1 is really just meant to be used for tutorial purposes and has not been put through the kind of testing that something used in a production environment requires. Another is that it takes quite a long time to run… if all you want to do is a quick activity check you wouldn’t want to wait for the 20 or more seconds for it to start.

To see how to make our scripts standalone, i.e. run without first running initPowerSMO.ps1, we have to look at how a command like get-SMO_Server works. Look at the line below:

$server = get-SMO_Server CANOPUS5

In effect, when the script is run this line is converted to:

$server = new-object
“Microsoft.SqlServer.Management.Smo.Server” CANOPUS5

The new-object command makes a new instance of a Microsoft.SqlServer.Management.Smo.Server object and passes the string “CANOPUS5” to the constructor it uses to do this. To convert the ActivityMonitorOpt.ps1 script to a standalone version, named ActivityMonitorSA.ps1, the first thing we have to do is to replace the SMO_ServerConnection and SMO_Server as is shown below.

$local:conn = new-object
“Microsoft.SqlServer.Management.Common.ServerConnection”
…
$local:server = new-object
“Microsoft.SqlServer.Management.Smo.Server” $conn

It turns out that is not enough. By default, PowerShell only loads a few .NET assemblies, the most common ones. In order for our standalone script to work we must load the SMO assemblies. If you look at the initPowerSMO.ps1 script you will see that it starts by loading the SMO assemblies:

[System.Reflection.Assembly]::Load(“Microsoft.SqlServer.Smo,
Culture=Neutral,Version=9.0.242.0,
PublicKeyToken=89845dcd8080cc91″)
| out-null
[System.Reflection.Assembly]::Load(“Microsoft.SqlServer.ConnectionInfo,
Culture=Neutral,Version=9.0.242.0,
PublicKeyToken=89845dcd8080cc91″)
| out-null
[System.Reflection.Assembly]::Load(“System.Data,Culture=Neutral,
Version=2.0.0.0,
PublicKeyToken=b77a5c561934e089″)
| out-null
[System.Reflection.Assembly]::Load(“Microsoft.SqlServer.SqlEnum,
Culture=Neutral,Version=9.0.242.0,
PublicKeyToken=89845dcd8080cc91″)
  | out-null

We will save the details of how this loads the needed assemblies for a later article; for now all we will do is make sure that this is part of any standalone assembly. The beginning of our ActivityMonitorSA.ps1 script now looks like:

With this modification, ActivityMonitorSA.ps1 will run without needing to first run initPowerSmo.ps1. One of the nice things about a standalone PowerSMO assembly like this is that it can be run directly from a Windows command shell command line, as shown below. You will also notice that this starts much faster than running initPowerSMO.ps1.

C:\SimpleTalk>PowerShell .\ActivityMonitorSA.ps1 CANOPUS5
SqlAdmin P@ssw0rd spid
,program

Spid Program
—- ——-
  51 Ad hoc Management
  52 Ad hoc Management
  53 Ad hoc Management
  54 Ad hoc Management

Converting to standalone scripts

So, the way to create your standalone scripts is first to get them working using all the PowerSMO functions like get-SMO_Server, and then to convert the script to using new-object and the class names that correspond to the PowerSMO functions. To do this you will have to know the full name of the classes that SMO functions are using. You can look these up in BOL, but you’ll find it is easier to just ask PowerShell to tell you what the full names are. For every PowerSMO function like get-SMO_Server there is a corresponding function named get-SMOT_Server that returns the type definition the function uses. This can be used to get the full name of the type, as shown in the example below.

PS C:\SimpleTalk> (get-SMOT_Server).FullName
Microsoft.SqlServer.Management.Smo.Server
PS C:\SimpleTalk>

Automatic conversion to standalone scripts

PowerShell is a full-fledged scripting engine so we might as well just write a script that will automatically convert an initPowerSMO.ps1 based script to a standalone one.

To get started we will need a script that can convert something like:

get-SMO_Server or SMO_Server

into:

new-object “Microsoft.SqlServer.Management.Smo.Server”

The following script, in replace.ps1, can do that:

param ([String]$smo_fcn)
$t = $smo_fcn.Insert($smo_fcn.IndexOf(‘SMO’)+3, ‘T’)
$c = (&$t).FullName
“new-object “”$c”””

This script inserts a “T” after the SMO in the input string, which makes the function name that returns the type definition for the function. You can execute a string in PowerShell by prefacing it with a “&” character; think dynamic SQL… you’ve done this before. This returns the type definition from which we extract the full name. Lastly we preface the full name of the class with new-object and return the result. Some examples of its usage shown below:

PS C:\SimpleTalk>  .\replace.ps1 SMO_Server
new-object “Microsoft.SqlServer.Management.Smo.Server”
PS C:\SimpleTalk>  .\replace.ps1 get-SMO_Server
new-object “Microsoft.SqlServer.Management.Smo.Server”

To do an automatic conversion we must find all of the SMO functions in a script and replace them with an equivalent new-object. We can use the regular expression support built into PowerShell to do this. The –match operator in PowerShell uses a regular expression to find patterns in strings, as shown below.

PS C:\SimpleTalk> ‘$local:conn=SMO_ServerConnection’ | %{$_ -match “SMO_\w*”}
True
PS C:\SimpleTalk> $matches.Values
SMO_ServerConnection

The –match returns a Boolean true if any patterns were found in the input it tested. It also fills the built-in $matches variable with an array of all of the patterns that matched it. As is shown above, the Values property of the $matches variable contains the SMO functions from the input string.

The get-content cmdlet will parse a text file into individual lines. To use it to do this, the path to the file is passed to the -path property of get-content. The makeSA.ps1 script, shown below, uses get-content to parse a file into lines, and then replaces each SMO functions with its new-object equivalent, i.e. it creates a standalone version of the input script.

param ($file)
$assemblies =
‘
[System.Reflection.Assembly]::Load(“Microsoft.SqlServer.Smo,
Culture=Neutral,Version=9.0.242.0,
PublicKeyToken=89845dcd8080cc91″)
| out-null
[System.Reflection.Assembly]::Load(“Microsoft.SqlServer.ConnectionInfo,
Culture=Neutral,Version=9.0.242.0,
PublicKeyToken=89845dcd8080cc91″)
| out-null
[System.Reflection.Assembly]::Load(“System.Data,Culture=Neutral,
Version=2.0.0.0,
PublicKeyToken=b77a5c561934e089″)
| out-null
[System.Reflection.Assembly]::Load(“Microsoft.SqlServer.SqlEnum,
Culture=Neutral,Version=9.0.242.0,
PublicKeyToken=89845dcd8080cc91″) 
| out-null
‘
$assembliesIncluded = $false;
get-content -path $file |
%{
if($_ -match “SMO_\w*”)
{
if(!$assembliesIncluded)
{
$assemblies
$assembliesIncluded = $true
}
foreach($m in $matches.Values)
{

$t = $m.Insert($m.IndexOf(‘SMO’)+3, ‘T’)
$c = (&$t).FullName
$r = “new-object “”$c”””
$_ = $_.Replace($m, $r)
}
}
$_
}

The file path is passed into the script in the $file argument. The $assemblies variable holds the instructions that will load the assemblies that SMO requires. The $assembliesIncluded variable is a marker used so that the assemblies are loaded only once.

Next the get-content cmdlet is used to pipe the lines of the input file into a script block. The script block uses –match find all the SMO functions in the line. If the line does include any SMO functions they are replaced using the script we looked at earlier, replace.ps1. Lastly the input line, with any SMO function replaced, is output.

Below is an example of using makeSA.ps1 to convert ActivityMonitorOpt.ps1 into a standalone script named AM_Standalone.ps1:

PS C:\SimpleTalk> .\makeSA.ps1 .\ActivityMonitorOpt.ps1
| out-file AM_Standalone.ps1

The out-file cmdlet is used to pipe the result of the makeSA.ps1 to a file. We can test things out by running AM_Standalone.ps1 in a new instance of PowerShell that has not had intiPowerSMO.ps1 run in it, as shown below.

PS C:\SimpleTalk> .\AM_Standalone.ps1 CANOPUS5
SqlAdmin P@ssw0rd spid, program

Spid Program
—- ——-
  51 Ad hoc Management
  52 Ad hoc Management

Here we see that AM_Standalone.ps1 worked without first running initPowerSMO.ps1 and ran immediately.

Conclusion

We started off by looking at how we can control the identity that SMO uses to login to SQL Server. Next, we built an activity monitor that gave us capabilities similar to those provided by the SSMS Activity Monitor.

The initPowerSMO.ps1 script creates a shell in which we can easily interact with and exploit all the features of SMO, but it really isn’t really practical for production management scripts. However, we can automatically convert any script we write that uses the PowerSMO functions to an equivalent one that does not depend on the initPowerSMO.ps1 script.

So, we can use a PowerShell initialized with initPowerSMO.ps1 in an ad hoc and development environment, but we make standalone scripts for production use.

Some of the topics in this article are covered in the Pluralsight.com Applied SQL Server 2005 course.