Last time we covered the basics of unattended installs in my post, Unattended (Silent) SQL Server Installations. Here I will be providing and explaining my PowerShell script for facilitating these installations.
UPDATED: Click here for the QS-Config product page. Where you can download the latest version for free.
As the help context describes, this script is intended to perform unattended installations (not upgrades) of stand-a-lone or clustered SQL Server instances. It will create the configuration.ini file necessary and it will produce the Add Node.ini file if you are installing on a Windows Cluster, in addition to providing you the command line execution string and/or run the install itself.
- SSRS and SSAS are not supported in this script.
- Upgrades are not supported.
- It assumes that your Product Key is stored with your installation media. This would be found at \\Default Setup.ini and would look like this PID=”AAAAA-12345-BBBBB-67890-CCCCC”.
- It assumes that your clustered network name is default for your IP address (Cluster Network 1).
- The “UseDefaults” feature is available but commented out because I removed all of my company’s standard settings so that you can fill in your own. Since the options are not set to any real settings I hard coded $Script:DefaultChoice = “NO” in the main section.
When writing this script I had modular coding in mind. My script has four main sections, Help Context which is used to make this script compatible with PowerShell’s native Get-Help cmdlet. Includes which is a Windows Form Assembly, a COM object and a Clipboard Alias. Functions which is the bulk of the code to run the interview, file writing, and installation. Finally, main, a term many C programmers are familiar with. The main section is simply where I placed all of the logic for traveling through the functions. I attempted to minimize the amount of code in the main function and maximize segregation of the functions so that they could be easily re-ordered or modified with as little code change as necessary, a feature that many script writers omit from their designs.
I chose to use variable pass ins for the function parameters but I used $Script: scoped variables for setting variables from within the functions. I imagine arguments could be made as to whether this was the best choice or not but for this post’s purposes it’s only important that you understand the logic flow.
High-Level Work Flow Explanation:
The script begins by loading the Help Context, the Includes and Functions.
It then transitions into the main section where the first five function calls are not specific to this installation. You are prompted to choice if you’d like to use defaults or not (commented out so you can setup your own defaults with the commented code shell), there is a welcome message in the form of a Windows dialog pop-up, you are prompted to select an environment if you are using defaults, then you select your installation type (clustered, stand-alone, or add node) before setting your path for the configuration file(s).
NOTE: In the SetFilePath function there is a nice feature that allows you to navigate to your configuration file location using a dialog box. I did not use this dialog feature for selecting the directories for the database files because in a cluster your available storage is not always on the node that you are running this script, therefore you will not be capable of selecting the drive(s). As noted above, this code exists right here in the SetFilePath function so if you wanted you could take this and incorporate this feature into the Stand-Alone installations very easily. I did not do this myself because I may install 1 stand-alone instance for every 30 clustered instances and the inconvenience was not large.
Next you will hit the primary switch of the script where it will select different function logic based on your installation type. Both stand-alone and clustered installations have a similar flow even though what is written to the configuration file(s) is different so I will speak to them both interchangeably.
Once into the meat of the script you will either begin answering a bunch of questions or your DefaultChoice option will be nicely filling these answers in for you. The function WriteNonConfigurableOptions will initialize and output to your configuration file settings which are either mandatory or things that you shouldn’t need to change from installation to installation.
NOTE: All file writes are Appends so if you ever exit the script or make a mistake you haven’t lost your work. The script is not designed to pick-up where it left off if you accidentally exited but your config file will be partially created so you can use it as a reference or just manually expand from there. Also, it is useful if you make a typo. The lines are appended so if a typo occurs you can open you config and just edit the last line that was written, save, and move on with the script.
Once the non-configurable options are written, we move on to the interview. You will be asked for network name, instance name, feature selection, sysadmin accounts, service accounts, and file directories. All of these are in a text based prompting format and where you are free-form typing there are examples for syntax.
NOTE: If you see an example like this: (Do not include the trailing ‘\’) eg. J: or J:\SQLServer, please don’t input J:\ or J:\SQLServer\.
Upon completion of the interview you will be provided an exit message and then asked if you would like to execute now. The alternative is to simply print out the command line executable and copy it to your clipboard. If you choose to execute now then you must be running this script locally from the host that you are intending upon installing it on. Running it from the PowerShell console on the host using “\\workstation\PSscripts\InstallSQL_Silent.ps1” will not work, nor will it work if you run it from your workstation’s PS console.
In most cases I simply select “NO” so that it will print the command line and copy it to my clipboard. I will usually then paste it into a text file along with the configuration files for later use. This way I can always reuse the command line and files without having to do much thinking.
Add Node Section:
The last part that has not been discussed is the add node section. You will notice in the main switch if you have $InstallChoice -eq “INSTALLCLUSTER” there is a function called WriteAddNodeFile. This function works because all of your variables that are needed have already been set from the full interview. Since these variables are not set already when the ADDNODE selection is made the script runs through a truncated version of the interview and gets you finished rather quickly using the same work-flow as the other types.
Please play around with my script and let me know what you think of it. I HIGHLY ENCOURAGE suggestions, complaints or anything of the sort. If you work in a situation which I didn’t not account for, let me know and I’ll provide updates. My goal is to be able to provide a script that is 100% universal. This script is not there yet but with your help it can be.