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.
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
<?xml version="1.0" encoding="utf-8"?> <tool version="1"> <description name="Find Orphan Users" icon="data_next" version="1"><![CDATA[ <p><b>Provides a list of all orphan users.</b></p> <p>Uses IronPython and SMO to provide a list of all databases on an instance and any users which do not have a login associated with them but have access to the DB.</p> ]]></description> <author name="Tim Wiseman" url="http://www.simple-talk.com/author/timothy-wiseman/" img="" /> <tags> <tag>Diagnostic</tag> <tag>users</tag> </tags> <ui> <output displaytype=" Message | ForceLog" /> <block> <control type="header" label="Server"> <font name="Arial" size="12" /> </control> <control type="server" id="Connection" /> </block> </ui> <script type="python"><![CDATA[ import RedGate #allows interaction with the UI import sys #to add to the Python Path to find the assemblies import clr #To bring in .NET libraries #May or may not be needed depending on configuration, adjust path as needed sys.path.insert(0, r"C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies") #bring in the SMO assemblies and import clr.AddReferenceToFile('Microsoft.SqlServer.Smo.dll') clr.AddReferenceToFile('Microsoft.SqlServer.SqlEnum.dll') clr.AddReferenceToFile('Microsoft.SqlServer.ConnectionInfo.dll') import Microsoft.SqlServer.Management.Smo as SMO import Microsoft.SqlServer.Management.Common as Common #connect with SMO, one of several methods if RedGate.Connection.Credentials.IntegratedSecurity: sqlServer = SMO.Server(RedGate.Connection.DataSource) else: srvConn = Common.ServerConnection(RedGate.Connection.DataSource) srvConn.LoginSecure = 0 srvConn.Login = RedGate.Connection.Credentials.UserID srvConn.Password = RedGate.Connection.Credentials.Password sqlServer = SMO.Server(srvConn) for db in sqlServer.Databases: RedGate.Progress.Message = 'DB: {0}'.format(db.Name) for user in db.Users: #Orphan user if it is not a system account (like guest) #and it has no Login if user.Login == '' and user.IsSystemObject == False: RedGate.Progress.Message = '\t{0}'.format(user.Name) RedGate.Progress.Message = ' ' #Insert a blank line between DBs RedGate.Progress.Success = True ]]></script> <signature></signature> </tool> |
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.
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:
|
1 |
sys.path.insert(0, r"C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies") |
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:
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
<?xml version="1.0" encoding="utf-8"?> <tool version="1"> <description name="Sql Backup Pro Backup" icon="data_floppy_disk" version="1"> <![CDATA[ <p> <b>Sql Backup Pro Database Backup</b> </p> <p>Uses the CLI for Red Gate Sql Backup Pro to take a database backup.</p> <p>Must be run on the server where SQL Backup Pro is installed.</p> <p>Using SQLBackupC this way requires elevated permissions and this script will fail if SQL Scripts Manager is run without the "Run as Administrator" option.</p> ]]></description> <author name="Tim Wiseman" url="http://www.simple-talk.com/author/timothy-wiseman/" img="" /> <tags> <tag>Backup</tag> </tags> <ui> <output displaytype="Message|ForceLog" /> <block> <control type="header" label="Database"> <font name="Arial" size="12" /> </control> <control type="database" id = "dbConn" /> <control type="header" label="Backup to:" /> <control type="savefile" id = "sfile"> <filter>SQB Files (*.sqb)|*.sqb|All files(*.*)|*.*</filter> <title>Backup File...</title> </control> </block> </ui> <script type="python"><![CDATA[ import RedGate #allows interaction with the UI from os import popen #subprocess.check_output is a better way #but it is not a default part of the SSM python interpreter #Set this to the directory containing Sql Backup Pro rgDir = r'C:\Program Files (x86)\Red Gate\SQL Backup 6\(LOCAL)' rgCMD = '"{0}\SQLBackupC" -SQL "BACKUP DATABASE {1} TO DISK=\'{2}\'"'.format( rgDir, RedGate.dbConn.Database, RedGate.sfile.File) RedGate.Progress.Message = rgCMD +'\n' #This command may fail silently if run without administrator #permissions. results = popen(rgCMD).read() RedGate.Progress.Message = results RedGate.Progress.Success = True ]]></script> <signature></signature> </tool> |
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.
Load comments