Skip to main content

Automated Installation of SQL 2014 - Basic Starter

I have always been told by our Database Administrators that it is very hard to automate the installation of SQL server. We already had a process to automatically deploy some software like SCCM and Anti-Virus when a new server is provisioned. The next step for me was to try and deploy applications/features such as SQL and IIS.

After much digging around Microsoft site I came across a few articles around deploying SQL Server 2014 via command line, sysprep and configuration file.

Sysprep is good if you are going to have a specific VM image that will always be for deploying SQL as default.

Command Line:
You can use command line to type in all your options, features that you would like to install but that command could be a very long one!!!

Configuration File:
A configuration file can be generated by going through the installation process selecting your options up to the stage before actually hit the "Install" button. This method captures all the options and settings that you have chosen and writes out to a .ini configuration file. You then use this configuration file to deploy across your environment with the same settings/options.

As you can see depending on your environment you have a number of ways to deploy SQL server with the same base configuration. Based on my environment I have chosen to use the configuration file option and reason for this is:
  • Command line would require me to know all the setup parameters and I could end up with a very long command. As always with long commands they are probe to errors.
    • Using sysprep means I would need to maintain another VM image. If the base build changes then I would need to update two VM images.
    Using configuration file in my environment would give me the flexibility of knowing I have all the settings in a file and I can choose to deploy on any server that I wish.

    I will show you how I captured a configuration file and how to use it

    • Ensure you logged in as administrator for the server you are working on
    • Ensure the server has access to all the installation file for SQL
    1. Run the setup.exe program from the SQL media and  if the User Account Control prompts up click "Yes" to continue
    2. You will be presented with the "SQL Server Installation Center", select "Installation" on the left hand side menu and then click on "New SQL Server stand-alone installation or add features to an existing installation" 
    3. Leave Microsoft Update box unchecked and click "Next"
    4. Ensure all of them are either "passed" or "warning" status and click "Next"
    5. Enter your product key and click "Next"
    6. Check box "I accept the license terms" and click "Next"
    7. I chose the "SQL Server Feature Installation" so that I could select what I wanted to be installed and click "Next"
    8. Select the feature you would like to install and click next. I selected "Database Engine Services, Client Tools Connectivity, Management Tools & SQL Client Connectivity SDK". Also select where you would like to install the instance if the default one is not suitable and click "Next"
    9. Decide if you would like to name the instance or just leave as default. I selected leaving it as "default" and clicked "Next"
    10. Decide for the windows services if you want specific accounts to be used. If you do use specific accounts for certain services then make your changes here. I left them as default
    11. On the "Collation" tab make any changed if you are not happy with the default one. Once the selection has been made click "Next"
    12. On the "Server Configuration" tab choose the authentication mode you would like to use. We currently use mixed mode so I have selected this option and type in the password you wish to use. You will also need to add a Windows user or group as well so use the "Add" button to perform this task
    13. On the "Data Directories" tab if the default locations for database, logs etc is not what you want then change them to your preference. I have changed them from the default to just demonstrate. Once you are happy click "Next"
    14. Now you are on the "ready to Install" screen, from here checked that you are happy with all the features and configuration that you have selected. DO NOT CLICK "INSTALL". As you can see there is a section "Configuration File Path" and you can see where the generated configuration file is. Browse to the directory and copy this file to another location so that we can use it later. Once you have done this we can cancel our install as we have captured our install parameters.

    So the above process has described how we managed to very quickly generated a configuration file that we can use for future installs of SQL server. There are a couple more changes we need to do to the configuration file to get it working. Below are the following changes I performed
    1. Locate the configuration ini file you have just done and open it using your favourite editor ( I use Notepad++) 
    2. Locate "UpdateEnabled" and change the value from "True" to "False". This will prevent it from trying to locate and update SQL installation files. I rather have a build where I know it is always going to be the same
    3. Locate "UIMODE" and comment it out like ";"UIMODE""
    If you look in the configuration file you will notice that there is no password for the account we set for "SA". For any accounts we you have defined in the configuration file we would need to add the password on the command line. You would need to look up for the correct parameters to use from the above document links I provided.

    How do we now use the Configuration File to do the silent install ?

    It is a simple command line but make sure you are running in Administrative mode for command prompt. The command line is

    %Path_To_%setup.exe /ConfigurationFile=%Location_Of_File% /SAPWD=%password% /IAcceptSQLLicenseTerms 

    So example could be
    z:\setup.exe /ConfigurationFile=e:\ConfigurationFile.ini=e:\ConfigurationFile.ini /SAPWD=Kinsql123 /IAcceptSQLServerLicenseTerms

    You will see that in the command line I now have added the password that I would like to give to the SA account. The options that I have set for my configuration.ini file would show the progress of the installation just for information. I tend to keep it this way so that I can see it happening.

    My next steps for this project is to speak to the DBAs and find out what options/features they normally use and turn those to configuration ini file. Then as we have prefixes for our server names for different roles I will use powershell to query the name. If it is suppose to be a SQL server then it will call my command to install SQL.

    So go ask the DBAs how they do the installs and modify them so that you can have them automate, streamlined and a consistent build where you can now deliver much quicker for your customers.

    Please remember you can use these installation methods to also add/remove SQL features so it is not just for installing SQL from scratch.


    Popular posts from this blog

    Rolling back a version of ESXi

    There is an option in VMware where after you have performed an major upgrade of ESXi you can roll back to your previous version. The benefit of this is that you would not need to reinstall your ESXi and its configuration if you had issues with the new software. I had to do this on one occassion in my lab where I upgraded from 6.5 to 6.7 and my VMs would not run because the CPU was not supported in 6.7. Please remember if you are using ISO method to upgrade ESXi please ensure you select "Upgrade ESXi, preserve VMFS datastore". Selecting "Install ESXi, preserve VMFS datastore" does not mean preserving datastore means retaining ESXi as it will still do a clean install of ESXi. This method does not work for vSphere 7.0 as there are changes to the partitions on the boot device. Below are the steps to roll back to a previous version which is quite straight forward. As always perform an backup of your host configuration before you upgrade or rollback ( KB2042141 ). I have

    Configuring ESXi 6 host to send logs to Syslog Server

    In my previous post I talked about configuring VMware Syslog server for Windows which is installed and enabled by default on installation of vCenter 6 for Windows. I will now describe the basic configuration that is required on an ESXi 6 host to be able to send logs out to a syslog server using my vCenter as the example. 1) Navigate to your ESXi host within vCenter. Go to "Manage" tab and select "Settings" followed by "Advanced System Settings". Look for the settings "" and highlight this settings. Click the pencil icon to edit the configuration for this setting. 2) You can now add the host name or ip address of your syslog server/s. You can enter just hostname or IP address, use udp://hostname:514 or ssl://hostname:1514 to be more specific on the port and protocol to be used. If you have multiple hosts then you use the comma (,) to separate each server i.e. udp://,udp:// 3)We n

    Custom ESXi Image - ISO using PowerCLI

    There comes a time when you have purchased a new hardware to run your ESXi software and discover that the installable base media provided by VMware does not include the drivers or the drivers are out of date. In the world of Windows (Plug and Play) it would discover the hardware and prompt you to provide the drivers so that Windows would install/update the drivers for the hardware. For ESXi if the drivers are not present during load time then the hardware will possibly not work. VMware uses VIB (vSphere Installation Bundle) as a way for vendors to distribute their drivers. To install these VIBs you can either use Update Manager or command line (esxcli). Now this is all good but it does mean you have to first install the base ESXi then use one of the steps above to install/update the drivers.   Some people might feel that it is OK to update the drivers using the above methods but what if it was the network card that was the new hardware and you needed new drivers. Without the net