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:
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
- Run the setup.exe program from the SQL media and if the User Account Control prompts up click "Yes" to continue
- 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"
- Leave Microsoft Update box unchecked and click "Next"
- Ensure all of them are either "passed" or "warning" status and click "Next"
- Enter your product key and click "Next"
- Check box "I accept the license terms" and click "Next"
- I chose the "SQL Server Feature Installation" so that I could select what I wanted to be installed and click "Next"
- 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"
- Decide if you would like to name the instance or just leave as default. I selected leaving it as "default" and clicked "Next"
- 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
- On the "Collation" tab make any changed if you are not happy with the default one. Once the selection has been made click "Next"
- 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
- 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"
- 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
- Locate the configuration ini file you have just done and open it using your favourite editor ( I use Notepad++)
- 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
- 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.
No comments:
Post a Comment