Shortcut to Consistent SQL Server Installs Using Configuration Files

If you support a large environment for SQL Server, then consistency between SQL instances and simplicity of installations are both key to making your life (at the office at least) easier in both the short- and long- runs. 

Install From Configuration File

The process I'm about to show you is one of those little-known features of the SQL Server installation. Typically, we jump right in and start up a new install as soon as the disk or .iso file is mounted. However, by selecting Advanced from the main menu and then Install based on configuration file, you've taken the next step in consistency and simplicity of one of the core responibilities as a SQL Server database administrator (DBA).

You may notice that I'm using screen shots from Microsoft SQL Server 2014, above. However, whether it's obvious or not, I'm sprinkling bits of screen images from processes I've documented for this going all the way back to SQL Server 2008. This is not just a new and shiny SQL Server 2014 feature; you've actually been oblivious to this shortcut for YEARS. I bet you feel a bit awkward now, don't you? Don't worry though, this will be our little secret, right?

Once you select to install from a configuration file, you'll be prompted with the standard Microsoft Windows file selection dialog box. We can draw a rough image of that in our sleep, so I don't think we need to take up space on the Internet showing you what that looks like now, do we? Select the configuration.ini file you wish to use and then you'll proceed through the install wizard as you would normally do, but this time around you don't need to input any information except for the single form that requests you enter passwords for the service accounts you intend to use.

What's that you say? Where is this configuration file I've spoke of? Oh, well perhaps I did get just a little ahead of myself here in all the excitement. Let's talk about that for a second and we'll come back to where we were. Think of this as a bit of a flashback save the wavy lines on your movie screen and the Theremin music cuing our trip back in time to the creation of the initial configuration.ini file. . .

The Configuration File

No doubt, if you're a SQL Server DBA, you've done at least one install in your career through the installation wizard. If so, you've been greeted with this last screen (or a fascimile of) before hitting that final Install button:

See that Configuration file path directory? That stores all of the values for each configuration setting you completed throughout the wizard. It's saved conveniently in that ConfigurationFile.ini file for you to easily modify for re-use or to use as is if you so choose. The path changes slightly based upon your environment, so only use this as a reference for where it exists on your installs. This means that you can step though an install one time and then save off the configuration.ini file for re-use later on.

As for editing the .ini file, it's as simple as opening a text editor should you want to make changes (or perhaps obfuscate the only real security risk in the file, the service account settings). The passwords for the service accounts are not stored in the file—as mentioned earlier, it's the only information you need to complete when stepping through the wizard when using a configuration.ini file—but as a database administrator, I don't even want to expose the service accounts I use either. Let's take a look at what the .ini file looks at before we step back into our current time stream:

;SQL Server 2014 Configuration File[OPTIONS]; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. ACTION="Install"; Detailed help for command line argument ENU has not been defined yet. ENU="True"; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block. UIMODE="Normal"; Setup will not display any user interface. QUIET="False"; Setup will display progress only, without any user interaction. QUIETSIMPLE="False"; 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. UpdateEnabled="True"; 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 Management Tools, Books online components, SQL Server Data Tools, and other shared components. FEATURES=SQLENGINE; 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="MU"; Displays the command line parameters usage HELP="False"; Specifies that the detailed Setup log should be piped to the console. INDICATEPROGRESS="False"; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. X86="False"; Specify the root installation directory for shared components.  This directory remains unchanged after shared components are already installed. INSTALLSHAREDDIR="C:Program FilesMicrosoft 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 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). INSTANCENAME="FOO"; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will incorporate the instance ID of the SQL Server instance. INSTANCEID="FOO"; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to enable and 0 or False to disable this feature. SQMREPORTING="False"; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to enable and 0 or False to disable this feature. ERRORREPORTING="False"; Specify the installation directory. INSTANCEDIR="C:Program FilesMicrosoft SQL Server"; Agent account name AGTSVCACCOUNT="DOMAIN_NAMEservice_sql_agent"; Auto-start service after installation.  AGTSVCSTARTUPTYPE="Automatic"; CM brick TCP communication port COMMFABRICPORT="0"; How matrix will use private networks COMMFABRICNETWORKLEVEL="0"; How inter brick communication will be protected COMMFABRICENCRYPTION="0"; TCP port used by the CM brick MATRIXCMBRICKCOMMPORT="0"; Startup type for the SQL Server service. SQLSVCSTARTUPTYPE="Automatic"; Level to enable FILESTREAM feature at (0, 1, 2 or 3). FILESTREAMLEVEL="0"; Set to "1" to enable RANU for SQL Server Express. ENABLERANU="False"; Specifies a Windows collation or an SQL collation to use for the Database Engine. SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"; Account for SQL Server service: DomainUser or system account. SQLSVCACCOUNT="DOMAIN_NAMEservice_sql_server"; Windows account(s) to provision as SQL Server system administrators. SQLSYSADMINACCOUNTS="DOMAIN_NAMEdatabase_administrators"; The Database Engine root data directory. INSTALLSQLDATADIR="D:"; Default directory for the Database Engine backup files. SQLBACKUPDIR="F:Backup"; Default directory for the Database Engine user databases. SQLUSERDBDIR="D:MSSQL"; Default directory for the Database Engine user database logs. SQLUSERDBLOGDIR="L:MSSQL"; Directory for Database Engine TempDB files. SQLTEMPDBDIR="T:MSSQL11.FOOMSSQLData"; Provision current user as a Database Engine system administrator for SQL Server 2012 Express. ADDCURRENTUSERASSQLADMIN="False"; Specify 0 to disable or 1 to enable the TCP/IP protocol. TCPENABLED="1"; Specify 0 to disable or 1 to enable the Named Pipes protocol. NPENABLED="0"; Startup type for Browser Service. BROWSERSVCSTARTUPTYPE="Automatic"

Truth be told, the service account names you see in this configuration.ini file have already been modified in a text editor. Even though I'm open for consulting gigs, I do love my day job and the concept of it providing a decent wage and health care benefits. I'm not up for writing something here that would place that into jeopardy after all. Okay, let's get back to where we started; I don't know about you, but time travel always makes me thirsty and I want to wrap this up and have a nice cold drink.

Finishing of An Install Based Upon a Configuration File

Let's summarize where we're at in this process:

Now, it's simply the matter of stepping through the install wizard and entering the service account passwords when prompted (or if you've chose to obfuscate the service account names, enter both the service account names and passwords when prompted). Finally, finish off the wizard, select Install when you can go no further, and kick back and wait until the install completes.

Once done, you have an instance of SQL Server consistent with all others based off the same configuration file primed and ready for a standardized post-install configuration. For suggestions on that process and what you may want to include in it, you'll need to stay tuned here for future tips, however.

Recommendations for Using Configuration Files in the Real World

It's my suggestion that you create separate configuration files for the following types of installations for both default and named instances:

It's my standard operating procedure to have a library of confiiguration files that I can draw on for installing all versions of Microsoft SQL Server configured to be consistent for drive mappings, security standards, service management, and so forth, for my entire SQL Server environment. I think this is a direct result of being the sole DBA for the first eight years of my career in supporting an environment that went from 1 to over 100 managed instances during that timeframe. It worked then, out of survival, and it works now that I have a team of three DBAs I lead for different reasons—we all know where to go to look for components or what to script to work in our servers without having to second-guess how a particular server is set up.  It makes our life easier—easy as pie. 

It can do the same for you as well.

Related: Scripting a Custom Database Installation