I am constantly installing new SQL servers where I work and I wanted to make the process easier and faster than clicking through all the screens on a manual install of SQL Server. About a year ago I started using this with the rare SQL 2005 installed and quickly adapted the process to SQL 2008 and then 08 R2.This post is specific to SQL 2008R2.
Where to Start
The quickest and easiest way to get all the parameters set is to run through the screens, choosing the components to install, choosing the data, log, tempdb, and backup drive locations (hopefully you have a standard drive configuration), after you have got all the configuration set and just before you click the “Install” button on the screen it tells you the location of the INI file you just created and will be used for installation, is located. So, cancel the installation at this point and go to that location and copy the file.
Reviewing the Configuration file
Now that we have our Configuration.INI file let’s take a look at some of the details and keep in mind all of the parameters can be changed. You can comment out parameters as well by placing a semicolon at the beginning of the line. First off we will look at “Action”. This file has been set to “INSTALL” but you can also UNINSTALL, and UPGRADE with a configuration file.
Next up is “Features” this is where you specify the components to be installed or uninstalled. I use this one file, copy it off and change the features according to the project requirements, and the reason I have all the components commented out on the line above.
One of the reasons I love working from Configuration files is this next parameter “QUIETSIMPLE”. This parameter allows the setup to display the progress without user interaction. If you don’t want to see anything you can set the parameter to run without display as well but, what can I say? I like to watch.
The “INSTALLSHAREDDIR” and the “INSTALLSHAREDWOWDIR” should be left with the default information. Some shared files need to be on the C:\
This next parameter “INSTANCEDIR” is where your binary files for SQL Server get installed. We standardize all of our stand alone servers (physical and VM) and install them on the D:\ . Again, this folder can be named what you want. I usually reduce it down to something simple.
You should use domain accounts with “least privileges” as possible to run SQL Engine and SQL Agent services. You should have these accounts ahead of install even though you have the ability to change them later in the Configuration Manager. There have been cases where issues were found when changing the accounts and the SPIDs getting out of sync.
Another best practice is the use of Domain Groups within SQL Server. Our DBA group uses a group and adding that group is as simple as putting the name here. We even add the SA Domain group even though I really don’t like to do it.
Backup, Data, and Tempdb
Hopefully you have standards setup where you work if that is the case these parameters can be set once and reused as long as your standards are followed. There will always be exceptions to every rule. Remember this is a configuration for a stand-alone server, Clustered servers will be different.
Calling the Configuration file
Even though when we created the Configuration file by running through the installation wizard we supplied the password for the SA account, a security feature does not allow the saving of the password in the Configuration file. As a result we pass in the SA password when invoking the install process via a command file and store it in that file. Our standards are to store this password in a password safe as well and once we use this file we delete it or change the password to something generic.
The next parameter allows the acceptance of the license agreement and continuing the installation process.
The last parameter is the name and location of the Configuration.INI file. This can be named anything you want. I usually name it what I am installing. As shown below, just the SQL Engine.
Put all three of the previous commands in a text file (.txt) in the root folder of where the install files are located. I generally have these files temporarily stored n the following forlder C:\_SQLServer200R2
Then rename the file to a “.cmd” file.
Now, open a command prompt and navigate to the directory where the installation files, the configuration file, and the install_SQL2008R2.cmd file is located.
Type in the command above and the install will kick-off automatically.
This all seems like a lot of work but compared to clicking through the many different pages in the installation wizard, installing SQL Server using a Configuration file it is a breeze once you do it a few times and get your own process going. The longest part of any of the installations I do now is copying the setup files over the network to the server.
Let me know if you run into any issues, but try it a few times first. You may find you need to tweak the files a few times before you get it right.
The link is also a link to a Microsoft page that explains all the parameters of the Configuration file.
Install SQL Server from Command Prompt Installation Parameters