There are more than 1000 different ways to configure a SQL Server instance. Just to install a new SQL Server 2017 instance, the ConfigurationFile.ini supports over 80 parameters! Add to that all of the post-install configuration options, and PowerShell based deployment scripts (install & configure) can suffer from parameter bloat. Having a block of 50-100+ parameters at the top of a script is messy to look at, and painful to manage at execution time. Command line executions become fraught with peril after the 10th or 20th parameter.
Are you sure there aren’t any typos? Can you assign default values to some parameters?
Recently I was contemplating this problem while updating & consolidating my SQL deployment scripts for SQL Server 2017. What about creating a file that could hold ALL of the information needed to install and configure a new SQL instance?
Enter JSON
JSON (JavaScript Object Notation) is a human-readable file format intended for transmitting data (usually between a server and app interface). Better yet, it’s supported by both PowerShell and SQL Server. The JSON format works off of collections of key:value pairs that can be organized into objects and arrays.
The basic syntax (rules) for JSON are:
- Objects are contained in curly brackets {}
- Arrays are contained in square brackets []
- Key names and values are separated by a colon :
- Names and values are contained within double-quotes ” “
- Backslash \ is an escape character, so values that contain \ must be doubled \\
In the JSON example below, the Accounts object contains 2 objects (Logins and Services). The Logins object contains an array with 2 member objects. Each member has elements (attributes) of: AccountType, Username, and Role.
The next question becomes: How does PowerShell read the JSON information? The answer to which is: Very easily. PowerShell can store all of the JSON information in a single object (variable) using the ConvertFrom-Json command. The script file(s) only need to reference a path to the JSON file.
Param ( [string]$ConfigFile = "\\server\share\folder\Config.json" ) $Config = ConvertFrom-Json -InputObject (Get-Content -Path "$ConfigFile" -Raw);
Now that the key values are stored in the $Config variable, they can be accessed based on their object hierarchy.
[array]$LoginList = (($Config).Accounts).Logins; [string]$SQLSvcAcct = (((($Config).Accounts).Services).Username).Where{$_.ServiceName -eq "SQL"};
So with the help of JSON, we can have 1 file that stores all of the configuration information, and 1 or more scripts that have all of the execution logic.
P.S.As a bonus, the JSON configuration file can be saved to a (secure) repository and referenced as documentation for the initial SQL installation/deployment.