At this point in my career, I’m not managing massive environments, so I don’t feel the need (nor have the expertise) to use a large scale solution like DSC or SCCM. But I’ve had to install SQL Server a few times, so I figured it’s worth at least scripting out a standard pre-configured installation, so that A) I don’t need click through a GUI ‘wizard’ hearkening back to the ’90s, and B) the SysAdmins can “fire and forget” (read: stop bugging me about it).
Thus, I’m attempting to K.I.S.S., while making it configurable & repeatable. There are some limitations of this approach, as alluded above. It’s not “massively scalable” (scaleable? scale-able?) because:
PoShscript still needs to be deployed locally to the server in question
- The installer config (
.ini) also lives locally (though it probably could be a UNC path, it’s just a file after all)
- The script prompts you for the service account (SQL engine, Agent) credentials and the
sapassword using the
methodcmdlet, so some meatbag still has to type those in. This is because we don’t have an enterprise pwd/secret-management system where I could, say, ask it for a service account credential set and tell it to embed that securely in a script without it actually being visible to me. So, while yes, they’re kept in a “vault”, it’s not query-able by anything else, so an admin still needs to copy & paste them into whatever configuration screen he’s working with at the time. Not ideal, I know, but we work with what we’ve got.
PS: Yeah, yeah, “don’t use
sa, rename it or disable it; or use Windows Auth only!”. Rage, howl, fire & brimstone. I’m not going to argue about it; we can save that for another post. This environment dictates that its used during setup and then disabled later, so that’s beyond the scope of the installer config.
So yes, while there are likely even better ways to do this in the long-run, this quick & easy approach was sufficient to save me time and effort for the occasions when a new SQL box/VM needs to be spun-up.
- A primer on SQL cmd-prompt installation & its arguments
- A couple community articles on the subject (the latter about slipstreaming updates)
- A technet article & couple Q&A threads (technet, stackoverflow) that helped me figure out how to securely get & put the credentials
- An example for mounting an ISO in PowerShell
- And finally, two things that I attempted to understand but ultimately failed to implement, because (apparently, at least to me), PowerShell remote-ing is a P.I.T.A.
First we need an
.ini file to work with. You could either create it from scratch, or take it from an existing SQL box’s “Setup Bootstrap” folder. Example path
C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20170801_073414\ConfigurationFile.ini — indicating this was an install done on 8/1/2017 at 7:34am. Right above that, at simply
C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\, you’ll see a
Summary.txt file, which can actually come in handy while you’re testing these unattended installs and wanting to see why it failed.
The first link above, from MSFT Docs, does a pretty nice job of telling you all the things that make up this config file. You get to bypass the TOS prompt, enter service account details, specify drive letters (paths) for default data/log file locations & tempdb, slipstream update packages (
UpdateSource), and even more advanced stuff like AG settings and whatnot. My example will be a simple standalone instance using the default name, so I’ll be sticking with the basics.
We can then use this file in the
ConfigurationFile argument of
setup.exe from the SQL Server install media. To put a little more color on that: the
.ini file is really just a collection of command-line arguments to
setup.exe; you could also list them all out in-line, but that would be tedious and silly. Here’s a couple major selling points of creating your own config file:
- Slipstream updates (SP’s, CU’s), instead of having it go out to MSFT update servers (or *aghast* sticking with the original RTM bits, you heathen you!)
- Specify drive letters / default file locations: sure, this may be considered old-hat if you’re running super slick storage, but I still find it makes management a bit easier if I know where my MDFs, LDFs, TempDB, & backups will always be.
- Take advantage of 2016’s better TempDB setup options (# files, size & growth)
We will, however, keep a couple arguments out of the
.ini file and instead throw them into the
ArgumentList from the calling PowerShell script. Speaking of, here’s what the PowerShell script needs to do:
- Prompt the operator (SysAdmin or DBA) for the SQL & Agent service account credentials, and (optionally) the
sapwd (if using it).
- Fetch our install media from the central network share where we store such things (server & office
ISOs, for example).
- Mount said ISO to our virtual disc drive.
- Run its setup.exe with the following arguments:
- The config
- The service &
- The config
- After it’s done, un-mount (dismount) the ISO.
Then the DBA can connect to the brand-spankin’-new running SQL instance and do other post-setup configurations as desired (i.e. set max-memory, maxDOP/CTFP, etc). And sure, those could also be done in PowerShell (thanks in no small part to the awesome team at DbaTools), I chose not to do so in this case.
As the bloggers say, “that’s left as an exercise to the reader”.
Plus, they’re never quite as deterministic as we’d like them to be — they depend on the server’s compute resources, i.e. memory size & CPU cores, as well as estimated workload & environment tier, so it’s often a gamble in “how correct” your initial settings will be anyway. Still, anything is better than the defaults, so configure-away!
Here are the Gists I’ve created to go along with this post. If I’ve made a mistake, or if you, dear reader, have a suggestion, we can incorporate them into the gist without me having to go back and edit the blog post!
I’d love to get feedback on how you would improve this, what you might do differently, etc. Drop me a comment or a tweet!
|; AUTHOR: Nate Johnson, @njohnson9402/@natethedba, natethedba.wordpress.com|
|; LICENSE: https://choosealicense.com/licenses/unlicense/|
|; TYPE: SQL Server 2016 Configuration File (for command-line installation)|
|; Inline comments are mostly copied from existing file generated by install wizard, with clarification where necessary.|
|; I use drive D:\ for data files (MDF), L:\ for transaction logs (LDF), T:\ for TempDB, and X:\ for backups.|
|; Most other options are "normal", i.e. I don't deal with clustering, Availability Groups, or other exotic things.|
|; I am only installing the database engine and replication components; see FEATURES option for more.|
|; Read the corresponding blog post at https://natethedba.wordpress.com/automating-sql-installation/|
|; There are THREE static placeholders you need to replace/type when you use this:|
|; 1. <YOUR UPDATES/PATCHES FOLDER>, which is for slipstreaming whatever service-pack(s) and cumulative-update(s).|
|; 2-3. <YOUR DOMAIN> and <YOUR DBA GROUP>: for specifying domain account(s) which are given SQL sysadmin ('sa') rights,|
|; hence, usually your DBA group. You could move that part out to the calling script instead, if you wanted,|
|; but I chose to leave it here because it's not likely to change much within an environment.|
|; required first line|
|; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.|
|; Specifies that SQL Server Setup should not display the privacy statement when ran from the command line.|
|; Use the /ENU parameter to install the English version of SQL Server on your localized Windows operating system.|
|; Setup will run silently, logging to files in the setup-boostrap directory|
|; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and False or 1 and 0. By default SQL Server Setup will include updates that are found.|
|; If this parameter is provided, then this computer will use Microsoft Update to check for updates.|
|; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality Services (DQS) server. The Tools feature will install shared components.|
|; Specify the location where SQL Server Setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default SQL Server Setup will search Microsoft Update or a Windows Update service through the Window Server Update Services.|
|UpdateSource="<YOUR UPDATES/PATCHES FOLDER>"|
|; Displays the command line parameters usage|
|; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system.|
|; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine (SQL), Analysis Services (AS), or Reporting Services (RS).|
|; Specify the root installation directory for shared components. This directory remains unchanged after shared components are already installed.|
|INSTALLSHAREDDIR="C:\Program Files\Microsoft SQL Server"|
|; Specify the root installation directory for the WOW64 shared components. This directory remains unchanged after WOW64 shared components are already installed.|
|INSTALLSHAREDWOWDIR="C:\Program Files (x86)\Microsoft SQL Server"|
|; Specify the installation directory.|
|INSTANCEDIR="C:\Program Files\Microsoft SQL Server"|
|; SQL Agent account startup type|
|; Level to enable FILESTREAM feature at (0, 1, 2 or 3).|
|; Specifies a Windows collation or an SQL collation to use for the Database Engine.|
|; Set to "True" to enable instant file initialization for SQL Server service. If enabled, Setup will grant Perform Volume Maintenance Task privilege to the Database Engine Service SID. This may lead to information disclosure as it could allow deleted content to be accessed by an unauthorized principal.|
|; Windows account(s) to provision as SQL Server system administrators.|
|SQLSYSADMINACCOUNTS="<YOUR DOMAIN>\<YOUR DBA GROUP>"|
|; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication.|
|; The number of Database Engine TempDB files.|
|; Specifies the initial size of a Database Engine TempDB data file in MB.|
|; Specifies the automatic growth increment of each Database Engine TempDB data file in MB.|
|; Specifies the initial size of the Database Engine TempDB log file in MB.|
|; Specifies the automatic growth increment of the Database Engine TempDB log file in MB.|
|; The Database Engine root data directory.|
|; Default directory for the Database Engine backup files.|
|; Default directory for the Database Engine user databases.|
|; Default directory for the Database Engine user database log files.|
|; Directories for Database Engine TempDB files.|
|; Directories for Database Engine TempDB log files.|
|; PS: yes, there is some debate as to whether your TempDB logs should go with your user DB logs, or with your TempDB data;|
|; I'm inclined to say the latter, because I like to use locally attached NVMe/M.2 flash storage specifically for it. But, YMMV.|
PowerShell install script:
|# AUTHOR: Nate Johnson, @njohnson9402/@natethedba, natethedba.wordpress.com|
|# LICENSE: https://choosealicense.com/licenses/unlicense/|
|# TYPE: PowerShell script|
|# There are TWO static placeholders that you need to change/type-in when you want to use this,|
|# they both start with <PATH TO …>. I also added a #CHANGE THIS! comment to the end of their lines.|
|# The first one is for your installer config .ini file|
|# (see other gist at https://gist.github.com/NJohnson9402/a3c13429a055771efd26eefa66c69d62).|
|# The second is for the location of your SQL server installation media (ISO).|
|# See corresponding blog post at https://natethedba.wordpress.com/automating-sql-installation/|
|# get account credentials to configure SQL|
|$sqlsvc = Get-Credential –Message "SQL Service account?"|
|$agtsvc = Get-Credential –Message "SQL Agent account?"|
|$sapwd = $(Read-Host –Prompt "SQL 'sa' login pwd" –AsSecureString)|
|# multi-line string concatenation|
|$arglist = '/ConfigurationFile="<PATH TO your config file>.ini"' ` #CHANGE THIS!|
|+ ' /AGTSVCACCOUNT="' + $agtsvc.UserName + '"' `|
|+ ' /AGTSVCPASSWORD="' + $agtsvc.GetNetworkCredential().Password + '"' `|
|+ ' /SQLSVCACCOUNT="' + $sqlsvc.UserName + '"' `|
|+ ' /SQLSVCPASSWORD="' + $sqlsvc.GetNetworkCredential().Password + '"' `|
|+ ' /SAPWD="' + [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($sapwd)) + '"'|
|# this last crazy tidbit converts the SecureString to a plain string that is still "secure"|
|# in the sense that it can't be sniffed or dumped from memory by another process. At least, that's my understanding.|
|# mount the install image|
|$iso = Get-ChildItem –Path "\\<PATH TO your SQL Server 2016 Standard x64 iso folder>\" #CHANGE THIS!|
|# get the drive letter of the mounted image to reference setup.exe|
|$setup = $(Get-DiskImage –ImagePath $iso.FullName | Get-Volume).DriveLetter + ":\setup.exe"|
|# run installer with arg-list built above, including config file and service/SA accounts|
|Start-Process –Verb runas –FilePath $setup –ArgumentList $arglist –Wait|
|# un-mount the install image when done after waiting 1 second (just for kicks)|
|Start-Sleep –Seconds 1|