Multi-Server Scripting – Powershell, SQLCMD and Scripts that write Scripts
I received a request for help today from a friend (let’s call him “Dave”). Dave was looking for a better way to deploy projects across a large number of Databases spread over multiple Servers.
My initial response suggested looking at scripting – either using SQLCMD or Powershell. Alternatively, there are tools such as Redgate’s “MultiScript” that have this capability. There is also the option of using some form of Deployment tool, perhaps Octopus Deploy, although I haven’t had much experience in that area.
All options have a cost – for SQLCMD or Powershell, there’s the time investment in learning a new language or way of doing things. For Redgate MultiScript or Octopus Deploy, there’s a hard financial cost. Often it can be cheaper to buy an “off the shelf” package than to spend to money on resourcing the creation of something bespoke.
With a bit of time available, I’d probably go with Powershell. It’s really flexible, especially if you use it in conjunction with “dbatools”, the free and open source extension for use with SQL Server. There are all sorts of useful commands that would facilitate the copying of Databases or running of SQL Scripts on multiple Servers / SQL Instances. Plenty of online training available and the “dbatools” team love to help.
An alternative that I proposed is SQLCMD, perhaps showing my age. SQLCMD doesn’t seem to get much love these days, but I’ve used it a lot “in a previous life”. Perhaps everyone went on to Powershell but bear with me as your options may be limited – this may be through a lack of knowledge or you may be blocked from using Powershell by security policies. SQLCMD extends SQL Scripts to allow you to perform tasks such as running other SQL Scripts, changing Databases and connecting to other Servers. Variables can be used within scripts for further flexibility.
Scripting Scripts One of the methods that I use frequently is to write SQL Scripts to build SQL Scripts.
For example, if I wanted to write a script to review the contents of all Tables within a Database, I could connect to the Database and run the following command:
SELECT 'SELECT * FROM [' + [NAME] + '];' FROM sys.tables ORDER BY [name];
The Output (from running the above on [master] looks like this:
SELECT * FROM [MSreplication_options]; SELECT * FROM [spt_fallback_db]; SELECT * FROM [spt_fallback_dev]; SELECT * FROM [spt_fallback_usg]; SELECT * FROM [spt_monitor];
The Output script can be copied / pasted into another query window and then… this bit is really important… it needs to be checked. Thoroughly.
If you are using quotes then you’ll need to “escape” the single quote – e.g.
SELECT 'SELECT * FROM [' + [NAME] + '] WHERE [install_failures] = ''0'';' FROM sys.tables WHERE [name] = 'MSreplication_options'
Apologies for the poor example – I will update it, but this should give you an idea of how to use quotes. Be prepared to fight with SQL Server Management Studio or SQL Server Operations Studio as they try to auto-create single quotes.
One of the advantages of using the Scripting method instead of just writing code before copy and pasting it elsewhere is that you only need to correct bad code in one place. Just fix it in the Scripting code, re-run to generate your script again and you’re done. Equally, if you have written something truly awful or dangerous then the same applies – your script generator is going to write a massive pile of rubbish.
You can go one further and automate the execution of your dynamically generated script, but personally I like to review it in the Query window before running or scheduling.
SQLCMD Basics
There’s a really nice post on Redgate’s “SimpleTalk” WebSite that walks through SQLCMD and the various ways that you can use it.
Going back to Dave’s request, this method of writing script can be used to generate . I’m not going to build it, but I’ll describe the building blocks that can be used – teach a man to fish, so to speak.
Metadata
Create a Table containing the SQL Server Instance and Database names where you wish to deploy scripts.
Create a second Table with the details of Scripts (inc full path and filename) for deployment.
Script
Create a Script that will use the Metadata to do the following:
• Connect to a SQL Server / Instance :Connect <ServerName>
• Change to the required Database USE <DatabaseName>
• Execute a Script -I <scriptFileName>
• Provide a location for the Output :Out <OutputFileName>
This can all be tested in SQL Server Management Studio (SSMS) before setting it up to be run in Command Mode and scheduling using SQL Server Agent.
Hopefully this will help someone, perhaps even Dave!
Nigel.
