By Rohit Khadka, Sr. Database Administrator
As a Production DBA, I have had the opportunity to do an upgrade/migration from SQL Server 2012 to SQL Server 2016. During the process, I realized that installing a new SQL Server on 20-30 servers in a short time was going to be challenging and monotonous. Going through the setup wizard and clicking next every time was just not going to be efficient. So, I almost always question myself before I do anything that requires a lot of effort – what is the opportunity cost to this? I guess my microeconomics class got in my head more than I wanted it to. 😊
I remember one of the things that one of my ex co-workers used to repeat the phrase ‘Is the juice worth the squeeze?’. I suppose the answer really depends on the concept of opportunity cost. Is it worth spending 1-2 hours to build some scripts to automate the process? Or is it a waste of time if you only have 2 servers and you can do it in less than a couple of hours? For me at the time, the obvious answer was to build an automated script rather than spending hours, if not days doing the same thing repeatedly. So, I chose to find some ways to automate the SQL Server installation. Luckily, Microsoft was kind enough to allow installation using command prompt and provide us with a configuration file (.ini) file that stores defined installation parameters in the file during setup.
But, how do you really automate the installation? That question remained. There were several ways to achieve this:
- Batch File
I chose to go with the Batch File and here is how I did it:
Step 1: Create a configuration file (.ini) using the setup wizard:
Starting with one of the servers that requires installation, create a configuration file going through the setup wizard until you get to the screen below ‘Ready to install’. On the screen, you will see the location where the configuration file is being saved under the configuration file path.
Step 2: Configure settings in the Configuration File
Open the configuration file in an editor app like Notepad or Notepad++. We will need to change the following parameters in the file as per our requirements:
Note: There maybe other parameters such as install directories that may need to be changed as per your need.
Step 3: Create a batch file to run the unattended install If you are familiar with batch scripting, this step may be quite easy. Otherwise, there are many online resources out there if you need help with writing simple batch scripts. For me, this was a bit of a learning curve. Anyway, here is what I ended up with:
@echo off set /p Env=Environment(Enter 1 for Prod or 2 for NonProd): set /p SQLServiceAccount=SQL Service Account: set /p SQLServiceAccountPwd=SQL Service Account password: set /p SAPwd=SA password: set sqlcmdpath="C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn" set pshellpath="C:\Windows\system32\WindowsPowerShell\v1.0" echo. rem ----------- SQL Server 2016 Installation & Configuration ---------------- echo Mounting Disk Image.... IF %Env%==1 ( %pshellpath%\powershell.exe -Command Mount-DiskImage -ImagePath "C:\temp\2016\en_sql_server_2016_enterprise_with_service_pack_2_x64_dvd_12124051.iso" ) IF %Env%==2 ( %pshellpath%\powershell.exe -Command Mount-DiskImage -ImagePath "C:\temp\DeveloperEdition\en_sql_server_2016_developer_with_service_pack_2_x64_dvd_12194995.iso" ) IF ERRORLEVEL 1 goto :FAILED echo Mount successful! echo Installing SQL Server Database Engine and Other Components....... start /wait E:\setup.exe /SQLSVCACCOUNT=%SQLServiceAccount% /SQLSVCPASSWORD=%SQLServiceAccountPwd% /AGTSVCACCOUNT=%SQLServiceAccount% /AGTSVCPASSWORD=%SQLServiceAccountPwd% /SAPWD=%SAPwd% /ConfigurationFile="C:\temp\Install\ConfigurationFile.ini" echo SQL Server Installation successful! echo Installing SQL Server Management Studio(SSMS)....... start /wait C:\temp\SSMS-Setup-ENU.exe /install /passive /norestart /log C:\temp\SSMSLogs\SSMS.log IF ERRORLEVEL 1 goto :FAILED echo SSMS Installation successful! @echo off echo. echo. echo. echo ___________________________________________ echo * * echo * Database Server Build is complete * echo *___________________________________________* echo. pause goto :EOF :FAILED @echo off echo. echo. echo !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! echo An error occurred! SQL Server installation did not finish. echo. pause
You will need to copy this code and make necessary adjustments and save it as a .bat file. To run this file without permission issues, it is important to right click and ‘run as administrator’.
As you can see when we are setting variables, you will be prompted to provide values for the following:
Env: Enter 1 or 2 depending on the environment (Prod, Non-Prod) this will need to be installed.
SQLServiceAccount: Enter username for the service account which the services will run under.
SQLServiceAccountPwd: Enter username for the service account which the services will run under.
SAPwd: Enter password for the sys admin account.
Also, we can define these directly in the setup step below if we do not want to enter the values manually.
start /wait E:\setup.exe /SQLSVCACCOUNT=%SQLServiceAccount% /SQLSVCPASSWORD=%SQLServiceAccountPwd% /AGTSVCACCOUNT=%SQLServiceAccount% /AGTSVCPASSWORD=%SQLServiceAccountPwd% /SAPWD=%SAPwd% /ConfigurationFile="C:\temp\Install\ConfigurationFile.ini"
Step 4: Run the batch file
Lastly, we will need to copy the configuration file and batch file we just created on the servers we will want to setup SQL Server. If you would like to automate the copy process, you could add a step to copy the files to the servers as well. To run the job, you could either schedule a SQL Server agent job or a windows task scheduler. Of course, it will depend on if you need to pass in the parameter values mention above.
In a matter of less than an hour, you could have SQL Server running on multiple servers at the same time. I was happy with the results and I was able to use the “saved” time to do other DBA tasks. Everyone is happy!
Until next time…😊
Note: Below is a zip containing sample configuration file and batch script file to help you get started.
Automating SQL Server Installation sample files