SQL Scripts Manager with PowerShell

SQL Scripts Manager was released as a Christmas present to Simple-Talk subscribers. William Brewer then wrote an appreciation of the tool. Now, he reveals a secret:: It also runs PowerShell scripts, and hence, SMO. He has the scripts to prove it, though hopefully, you'll soon be running your own PowerShell from SQL Scripts Manager.

The free tool SQL Scripts Manager (SSM) does much more than just run SQL Scripts. It will also run IronPython and PowerShell scripts. By using PowerShell, you’ll be able to run SMO for a wide range of tasks for which PowerShell scripts are provided, but now you’ll have a standard front-end for the parameters, and help text, and to see both the progress and results of the script.  For me, it is a great advantage to have a single way of running scripts,  whether they’re SQL, PowerShell or Python, within a single interface. SSM allows you some very nice facilities for getting the user-interface right.  When you need to provide scripts that other people may be required to run, or might have to be run by remoting in via a tablet, then the user interface requires a new level of attention.

Why?

I’ve been to a lot of IT ‘shops’ where the various essential scripts for maintaining the servers are in a rather mixed state of development, and it isn’t always clear as to what they do and what parameters you give to them. They come in a plethora of scripting languages, and you have to look in a number of directories to find them. A muddle.

 The user of SQL Scripts Manager (SSM)  is no longer forced to have three or more different places on the PC to have to navigate to in order to run scripts. This is particularly useful where scripts must be done in a particular order, and if they come in different flavours of scripting language. For the users, there is no real difference as far as they care. It all works as expected.

From an IronPython or PowerShell script, it is easy to drive SMO in order to perform a huge range of administrative tasks for SQL Server. Actually, it seems to run any PowerShell script I throw at it.  As PowerShell is the means of automating administrative functions across the range of Microsoft servers,  you can us SSM for doing them. It is more convenient too, since you have a potential means of running, from one application,  a large number of scripts, all of which get their parameters from a User Interface in a similar way, and all of which can display their results or report on progress likewise.

How?

Just to recap from my first article, SSM works by looking at a directory of XML-based files, each of which contains a script of some sort, and all the ancillary information that is required in order to run the script. It uses what is in this directory to create a menu of scripts to run. It will poll the directory occasionally to detect changes within the directory: this means that, if you add, alter or delete scripts, it will read in the configuration file to check it, and if it likes what it sees, it then displays it. Otherwise, it highlights the error.  Scripts are stamped with a key, to distinguish authenticated from non-authenticated scripts.  For the general run-of-the-mill script, the task of embedding the script into the XML wrapper is pretty simple, and there are standard templates provided to help with this, here and here)

Running PowerShell from SQL Scripts Manager.

Hello world!

To be run in SSM, The PowerShell “Hello World” morphs into the XML document (file-type rgtool) …

There are two things you’ll notice. These involve the $Progress object that is exposed to PowerShell by SSM. The first is $progress.Success = $true which reports to the UI that the script executed OK. ( for Python, the syntax is RedGate.Progress.Success = True; ). Even if a script produces no errors, it may have failed to do what the user wanted, which is why it has to be set to ‘True’. The second is $progress.Message to which is assigned every message you want to send to the progress log. We’ve chosen just to have the progress log in the final results window, and have used just the ForceLog value in the ‘displaytype‘ attribute of the UI output, in order to open it out rather than collapse it.

1242-img45.jpg1242-img43.jpg

This script is unusually innocent of an input user-interface. Here is a rather more useful script that lists your server downtime (or Uptime if you look at life that way) and starts by eliciting the name of the server. It creates a grid and writes information into it.

Uptime events

1242-img4B.jpgYou’ll see here that we are just getting the user to specify the name of the computer and passing it to the PowerShell function. The interest here is in getting the data from PowerShell to the grid. Here, you have a $progress object exposed that allows you to write to the grid, specifying the columns and notifying the start of a new row.

The line that pasts the description of the event from the Event Log is this…
 $progress.RowValue('Event', $line.EventCode);

and the date is done likewise with…
 $progress.RowValue('Date', $line.TimeGenerated);
Where the ‘Date’ is the key for the column and the second parameter is the object value to be displayed in the current row for that column

When a row is complete, the call to the function …
 $progress.RowFinish()   
...
ensures that the next data is written to the following row of the grid. It bumps the ‘current row to the next.

The function …
 $progress.TableFinish()
...
completes the population of the grid. (for IronPython, you’d use …
 RedGate.Progress.Success = True;

The progress object is at the heart of integrating a script with the Output UI. There are a number of values and methods that are worth knowing about. These are the main ones.

The Public interface of $progress

This is probably the best place to list the attributes and methods of the $progress object, (RedGate.Progress in IronPython)

Attributes
  $progress.PercentageComplete
# gets, or sets the percentage completion of the progress bar.
  $progress.Message  # places or retrieves, a message in the message widget
  $progress.Finished  #  Set to $true when the script is finished
  $progress.Success  # Flag$true or $false  to inform the output UI whether the script was successful

Methods
  $progress.Log(“log message”);
#writes the string to the log widget
  $progress.PrintError(“Error message”); #writes the string as an error (in red)
  $progress.PrintError(“fmt”, params string[] args); #writes the sPrintf  result as an error
  $progress.RowFinish(); #move on to the next row, making it the current one
  $progress.RowFinish(“rowKey”); #
  $progress.RowValue(“colKey”, value); #Write the value into the “colKey” column of current row
  $progress.ClearRows(); #clear the rows of the current grid
 

Continuous monitoring with PowerShell and SSM

There is a great deal of power hidden away in SQL Scripts Manager.  Here is a PowerShell script that continuously monitors a process. You can do this in the command line but it will always work more neatly  with a GUI

The Output window of the UI

For a start, we’ve used some trickery to specify the UI display. There are several options you can use for the display and you can combine them together like this script does.

  • None  (Not advised, but there for the minimalists. it is better  to always include at least OutputLog or ForceLog so that there is something that the application can print error messages to)
  • ProgressBar (Yes, you can specify a progress bar, and call a function to specify % completion. It is really essential for long-running scripts)
  • Message (Traditional message area)
  •  Grid  (this is a traditional results table which will be most useful for multi-column results.)
  • OutputLog (this is a scrolling ‘log’ display that, by default, is closed or folded. This is the best type  where the predominent display must be the grid)
  • ForceLog (this is a scrolling ‘log’ display that is fully open, a good substitute for command line! ForceLog is better in most cases since it’s not so obvious to the user how OutputLog is unfolded.)

and there are some short-cut UI specifications in case you don’t like using the OR (|)

  • All = ProgressBar | Message | Grid | OutputLog,
  • Simple = Message | ForceLog
  • ProgressOnly = ProgressBar | Message | OutputLog
  • NeverEnding = Grid | OutputLog

 In this script, we’ll use the NeverEnding value ;

1242-img66.jpg

SSM and SMO: Scripting a database!

When you specify a database within SSM, you get passed an object that gives you a great deal of information. You need to choose the right control for the job in hand. The details are here . The relevant controls are

  • createdatabase which includes a means of specifying a database name and SQL Server instance and the connection credentials. (as, for example, when the database will be created when the script is run).
  • createtable which allows the  SQL Server instance and the connection credentials to be specified. It includes a combo box for selecting an existing database and a text box for specifying a table (for example, if you are using the script to import data into a new table).
  • server which allows the  SQL Server instance and the connection credentials to be specified.
  • database which allows the  SQL Server instance and the connection credentials to be specified. It includes a combo box for selecting a database
  • table which allows the  SQL Server instance and the connection credentials to be specified and includes combo boxes for selecting a database and a table.

If you merely want to attach to a server, you would specify this in the UI by means of the server control. You specify the ID that you want to use for the object. If you assume that you’ve specified the id of ‘connection‘ to the server control, you can, if you want, access the  $connection.ConnectionTimeout, $connection.Datasource, $connection.NetworkLibrary, $connection.PortID, $connection.ExecutionTimeOut, $connection.Encrypt (Boolean),  $connection.Credentials  and $connection.InitialCatalog  which are more than enough for database work. You’ll rarely need more than the Datasource and Credentials.

You can access all the necessary attributes of the credentials for login via the Credentials object. These values can be accessed by means of $connection.Credentials.Instance, $connection.Credentials.IntegratedSecurity, $connection.Credentials.UserID, and $connection.Credentials.Password  This will be necessary in order  to use SMO, or SQLClient. if you are supporting SQL Server Security. The User/Password combination will be needed if you aren’t using  Windows security, as when you are attaching to servers outside the domain.

The Database object has ConnectionTimeout, Datasource, Database, NetworkLibrary, PortID, ExecutionTimeOut, Encrypt (Boolean),  Credentials  and InitialCatalog

With this article, I’ve provided an example that uses the database control and SMO, taken originally from Allen White’s PowerShell code. It is too long to put in the article itself, so it is attached. I’ve also provided a tool for scripting out the data for any table you want into insert statements. That is also attached in the downloads at the bottom of the article. This latter script uses the table display and I show you how to parse the table name to separate out the schema.

The part of the code that does the business of connecting to the server is here. Because we’ve used the database control, and assigned it the ‘connection’ ID we use the $connection.Datasource and  $connection.Database attributes. This script demonstrates how to cater for both integrated security and SQL Server security, in order to access servers outside the domain

Conclusions

Most routine administration scripts for Sysadmin and DBA work require a flexible user-interface that does just a few things well. As far as the input screens go, the standard widgets and specialised database-access widgets are fine. It might be nice to have common filters to validate input but one can get along without them. The output screens aren’t going to be wild or wacky, and are generally little more than a place to display messages, or result tables.

By putting the basics in place, making TSQL scripting easy, and allowing PowerShell scripting for server administration and SMO, along with IronPython for the more complex procedures involving the local file system, SQL Scripts Manager seems to provide a great way of sorting out the mass of existing scripts and providing a simple means of developing new ones so that, if a server needs attention when they’re off-duty, the DBA or SysAdmin can be sure that the essential maintenance scripts can be done by whoever is on-site, or can be easily accessed by remoting in from an iPad.

This seems a much better approach than trying to predict all the common routine tasks that the DBA finds scripts for and producing a huge set of apps. With SSM, you can customise scripts to your exact requirement.