The Scripted PowerShell Automated Deployment Engine (SPADE) is a powerful, PowerShell-driven remote install and configuration tool for SQL Server that streamlines remote deployment and simplifies complex installation processes.
SPADE's developer, Michael Wells, is a SQL Server community volunteer and enthusiast. When asked what SPADE offers compared to SQL Server’s native remote installation features, Wells pointed out that SPADE installs and configures aspects of the OS, such as the Microsoft Distributed Transaction Coordinator (MS DTC), local security policies, database schema objects, SQL Server Agent jobs, operators, and tasks.
In a nutshell, SPADE lets you set up and configure a new SQL Server instance, including the OS and services running on the OS, in less than 20 minutes. SPADE does this through one simple PowerShell script. I will show you that using SPADE is as simple as setting up a configuration file and passing a few variables to a command-line script.
SPADE uses PowerShell to deploy, install, and configure SQL Server and any elements of the installation that you might require, such as Windows Services and security policies. SPADE can be easily configured to your organization's specific needs—it basically mimics the process you’d follow if you were performing a manual installation by walking through a series of pre- and post-installation steps, as well as applying the unattended installation file for you at the appropriate time. You can define a standard build with all of the configuration options you want through a simple XML configuration file. Non-standard and one-off builds are also supported without needing to change the configuration file.
Configuring SPADE is a two-step process. First, you’ll want to make adjustments to the RUN-INSTALL.CONFIG file. Next, you’ll want to use the SqlSpadeHelper.exe to build the folder structure and populate the folders with the installation files used to get SQL Server up and running.
To specify your own configuration, edit the RUN-INSTALL.CONFIG file, while paying special attention to the following:
- The <AppSettings>
section.This section lets you accept or overwrite the default settings for the WindowsScriptsFolder, which defines the location on the target server to hold any scripts you’ll be using from the PowerShellScripts folder. This section also lets you do the same for FilePath, which defines the path and folder on the destination server to hold all the setup files.
<ScriptConfigs> section.This section defines the name and parameters for each pre- or post-installation script needed for your environment. They’re blank by default and need to be configured, otherwise they'll be skipped.
<DataCenters> section.This section lets you specify separate file locations local to each data center in your organization, in case you don’t want to copy large files across the corporate WAN.
<SqlVersions> section.This section lets you specify the configuration details of a SQL Server standard build, including the versions and editions you want to support, as well any other configuration settings.
Your final configuration step is to edit the Start.bat file and set values for each major variable, such as SqlVersion, SqlEdition, and InstanceName. (You can also override any configuration setting by using the $overrides hashtable if needed). You can also set a variety of switches, such as –PreOnly (to run the pre-installation processes) or –PostOnly (to run the post-installation processes), to meet your needs. There are more than a couple of variables and switches, so be sure to read the documentation about runtime variables and switches. After the configuration process, you’re ready to run the SPADE script by kicking of the Start-SqlSpade.ps1 PowerShell script.
SPADE runs on SQL Server 2005 or later, including SQL Server 2012. SPADE also runs on evaluation editions of SQL Server. (As an aside, SPADE will deploy Ola Hallengren’s excellent database maintenance scripts, which I first wrote about in the article "More SQL Server Automation Scripts". Read more about Hallengren’s scripts at ola.hallengren.com). SPADE doesn't currently support clustered SQL Server installations.