PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

SQL Scripts Manager and IronPython

As well as running TSQL and PowerShell, the free SQL Scripts Manager tool can also run Python scripts. This allows even more sophisticated scripting possibilities for managing automated database tasks.

Last year, Red Gate released the free tool SQL Scripts Manager (SSM) to provide a convenient way to organize scripts for SQL Server. Most scripting that DBAs do require a small number of forms, such as keying in credentials, and these are already-made within SSM and therefore consistent between scripts. SSM makes it easy to create a graphical front end to T-SQL Scripts, but it will also support PowerShell and IronPython scripts.

Why use IronPython with SSM?

IronPython is a .Net implementation of Python, which means it comes with Python’s ease of readability and ease of learning, but adds the advantages of access to the .Net libraries, including the SQL Management Objects (SMO). By using IronPython with SSM, a DBA can easily create and organize a series of administrative scripts with a simple, consistent user interface that will automate many routine tasks. IronPython has an amazing range of libraries and modules, and so it is sometimes the quickest way of getting some of the more unusual functionality into a script.

Creating an IronPython Script for SSM

In order to integrate with SSM, a script must be wrapped in XML which provides extra information to be displayed to the user, as well as a description of the interface that SSM will provide. As with many things, the best way to get a feeling for the XML format is to look at examples.

SSM comes with a library of scripts written by experts that are useful by themselves, but also provide examples of what can be done with SSM and how it can be done. Some of the scripts that come with the package are written in IronPython and provide a good template for new utilities. For instance, the entire Export series by Phil Factor is written in IronPython, as is the Ping Multiple Machines script. To examine the complete source code for one of these scripts, select the “Show Scripts Folder” Icon in SSM and then open the corresponding .rgtools file in a text editor.

1483-image001.png

There is an option within SSM to view the details of a script and the details include the source code. This provides an opportunity to review exactly what a script would do before executing it. But it is not the best place to find templates for creating new scripts since it only shows the code that will be executed and strips out the XML.

1483-image002.png

The complete reference for the XML Schema used by SSM is detailed in The SQL Scripts Manager XML schema. Along with providing metadata about the script, the XML template describes the user interface and provides a variety of controls that allow the user to provide inputs for the script. The script itself can interact with the controls through the RedGate.control_identifier after importing the RedGate module. Once the XML file, including the IronPython script, is completed it can be added to SSM just by saving it in the scripts folder (which can be set in File – Application Options) with the .rgtools extensions.

Example

As an example, here is a simple script that will identify Orphan Users with SMO:

When this is saved in the scripts folder with a .rgtool extension, it will automatically appear in the SSM menu under the title, tags, and author given in the XML document itself.

1483-image003.png

It shows up in red to show that there is no valid signature in the XML document, which also makes it stand out from the Red Gate provided scripts, which can be convenient at times.

Some Technical Details

Generally, Python is known for its readability and the XML formatting used by SSM is generally self-explanatory, but there are a few things worth noting about the interactions.

SSM comes with its own IronPython interpreter, currently at version 2.6. This means that it can run IronPython scripts whether the user has another IronPython interpreter or not. But it also means that a script for SSM cannot use the new features of later versions of IronPython. The SSM interpreter also maintains its own path. So, if a script needs to import libraries not included in the SSM IronPython path, then either those libraries need to be copied into the SSM folder or the folder they are in needs to be explicitly added to sys.path.

In some cases, it may be necessary to specifically insert a new folder at the beginning of sys.path instead of appending it to the end. For instance, if there is more than one version of SQL Server installed, the SSM IronPython may put all of their assembly directories in the default path. The script can be forced to use a specific version of the assembly by inserting the folder with the right version at the beginning of the list, like:

Also, the signature block at the bottom is there to authenticate scripts and there is currently no way for the end user to sign their own scripts. Scripts with a blank signature block will appear red in the SSM interface, but that will not affect their use in any other way.

SSM generally does not run with elevated operating system permissions unless specifically opened with elevated permissions. It can still get elevated permissions inside of SQL Server, so most scripts which interact primarily with SQL Server will be unaffected with this. However, this may cause issues for scripts which make calls to the operating system or reach out to other programs. One way to ensure that SSM has full permissions is to change the compatibility settings it runs with:

1483-image004.png

Naturally, this does permit scripts run through SSM to cause more problems than they would otherwise if they are malicious or poorly written, so this should be used with care.

Using SSM with other tools

IronPython is well adapted to interacting with other programs which expose a command line interface. For instance, Red Gate’s SQL Backup Pro can be run from the command line on the server. A simple SSM script to make a backup with SQL Backup would look like:

This script is only intended to illustrate how easy it is to call command-line applications. It must be run on the server on which the backup is to be made, it routes around the user-friendly GUI built in to SQL Backup Pro, and it requires that SSM be run with administrator permissions. It could however serve as a foundation for something larger which is useful for repeated series of operations which require only slight user-interaction. For instance, it would be fairly simple to write a script which invoked additional programs to take a backup and transfer it by secure FTP to a server on a different network. Or, if PsExec was available, it would be simple to write a script to make a fresh backup on one server, transfer it to another, and restore it there.

Additional References

  1. Using SMO to manage a MS SQL database
  2. The SQL Scripts Manager XML Schema
  3. The SQL Server Central SSM Repository