Malathi Mahadevan works as a Senior DBA with Kindred Healthcare – a healthcare services company based out of Louisville, KY and ranked first in Fortune magazine's Most Admired Companies "Health Care: Medical Facilities" category. She is the founder-lead of the local chapter of Professional Assn of SQL Server and have 12 years of experience using SQL Server.
This is a review of a small but significant tool that is inexpensive, has few competitive products and has made a significant difference to our work environment.
The environment I work on comprises of approximately 150 SQL servers, 50 each on Dev/QA and Production. Majority of these servers are on SQL Server 2005, some are on SQL Server 2000 and two of them are on SQL Server 2008. Being a healthcare company we have very sensitive data and high expectations on system uptime.
We also support several development teams through their SDLC and by offering reviews and feedback as necessary. A critical part of our work involves review and promotion of scripts in an accurate manner. We also have an auditing process that requires an audit script to be run before any script on every promotion.
As a DBA team at Kindred we do a lot of running of scripts across servers, also called promotions. The volume is on an average upto 10 promotions a day some of which may include hundreds of scripts across multiple databases/servers. These include DDL/DML scripts, code changes and one time data updates.
It is very time consuming for us to log on to each server using Query Analyzer and run the scripts repeatedly. It also gives room for error, a script file may be missed on one database and run on another. We were looking for a simple handy tool that will help us run scripts across multiple databases/servers with a click of a button and configure various environments (that is lists of databases/servers that serve one application and typically go together).
How RedGate SQL Multi Script helped us
Red Gate SQL Multi Script is a handy tool that is part of their SQL Toolbelt set of tools.
It allows for configuring list of servers that can be reused, and executing scripts across all servers at single click of button.
SQL Multi script by default stops execution when an error is encountered (this is configurable to continue execution on each database or one database). It also allows for parsing/validating scripts, saving results of execution to CSV file or text file, and also configure for serial or parallel execution on multiple servers (default is serial depending on order servers are selected). SQL Multi script has cut down our promotion time by more than 70% for large promotions and 20-30% for smaller promotions. We have also not had any errors happen when code is promoted on one database and not on another.
There are some small improvements that could be done to the product – it only allows selection of 10 databases at a time maximum, and query results have to be saved one by one and not all together, there are no command line options and no way to schedule script execution. But these are small improvements and overall we have greatly enjoyed using this nifty tool.
Other interesting features
SQL Multi Script allows for options to run scripts serially (one after another on each server/database depending on order of databases selected) or parellely, every script runs same time on each selected server/database.
SQL Multi Script also typically keeps the last script you ran when you open it again – in case you want to run it again. This is particularly useful for audit type scripts which have to be run repeatedly.
I highly recommend this tool for environments where code promotion forms an important part of DBA Tasks and there are several servers to attend to.