Automating SQL Installs

Here’s how I do a basic silent install of SQL Server.

Configuration File

You can’t start this unless you have an installation configuration file from a previous install. Your Configuration File (ConfigurationFile.ini) can be found in the following location:

C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log\

Within this location there will be a timestamped folder and this will correspond with the time of the initial install. 120 relates to SQL Server 2014, for 2012 use 110 or 2016 130 (You can work out the rest yourself).

Please note that you don’t have to install the whole of SQL Server first to generate your Configuration File. You simply go through the wizard until you reach the  ‘Ready to Install’ tab and then you can cancel your install.

This should already include your build information such as your components to install and locations for your install/data/log/backup/tempdb folders. I can’t stress how having a standard build across your estate can make administration a hell of a lot easier which is why using a configuration file makes more sense than manually completing an install. There are lots of other configurations I use for my standard build but this is out of scope for this blog post (although I will follow this up with a separate blog about the best practices I use with new installations).

For this process to work you need to also change or add the following parameters to your configuration file:

QUIET=”True”

QUIETSIMPLE=”False”

IACCEPTSQLSERVERLICENSETERMS=”True”

The ‘QUIET’ parameter is normally set to False so you must change this to true as well as QUIETSIMPLE being set to false. You need to add IACCEPTSQLSERERLICENSETERMS otherwise your quiet installation won’t be able to complete as it would be waiting for your input.

You can run the quiet install from the command line but I run mine using a PowerShell script as I want to make my configuration file slightly more interactive so if I want to install a named instance and change account details, I can do this within PowerShell and it will change the configuration file for me rather than me having to change the configuration file each time.

The following parameters hold the Service Accounts.

SQLSVCACCOUNT=”$SQLAccount$”

AGTSVCACCOUNT=”$SQLAGENT$”

Encompassing these parameters in a pair of ‘$’ symbols my PowerShell script can replace this value with a new value I set in my command, this way I don’t have to amend the configuration file every time I run a  new install.

I want my PowerShell script to do a number of things before and after to installing SQL Server. Here’s the  list of the functions I want it to perform.

  • Copy my ISO image and configuration file for the install to the server I’m installing SQL server on.
  • Find and Replace the $SQLAccount$ and $SQLAGENT$ strings with the new values and also replace MSSQLSERVER if I’m installing a named instance
  • Check that the instance name isn’t already installed on the Server
  • Mount the ISO
  • Install SQL Server
  • Unmount the ISO
  • Delete the ISO and Configuration file

The Following script does all this:

Function Install-SQLServer
{
    param (
        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [String]
        $ComputerName = $env:ComputerName,
        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [String]
        $PackagePath="\\UNCPATH\SQLSERVER\INSTALLS\2014",
        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [String]
        $PackageName="en_sql_server_2014_developer_edition_with_service_pack_1_x64_dvd_6668542.iso",
        [Parameter(Mandatory=$false)]
        [ValidateNotNullOrEmpty()]
        [String]
        $InstanceName = 'MSSQLSERVER',
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $SQLAccount,
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $SQLAgent,
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $SQLPW,
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $AGTPW

    )


 process
  {
    try
    {
 
        copy-item -Path "$PackagePath\$PackageName" -Destination "c:\temp\SQLServer.iso" -erroraction Stop

        copy-item -Path "$PackagePath\ConfigurationFile.ini" -Destination "C:\temp" -ErrorAction Stop

        ##Add Service Accounts to Configuration File

        $NewConfigFile = FindReplace-String -InputFile "C:\temp\ConfigurationFile.ini" -FindString '$SQLAccount$' -replaceString $SQLAccount
        
        $NewConfigFile | out-file  "C:\temp\ConfigurationFile.ini" -Force

        $NewConfigFile2 = FindReplace-String -InputFile "C:\temp\ConfigurationFile.ini" -FindString '$SQLAGENT$' -replaceString $SQLAgent 
        $NewConfigFile2 |out-file "C:\temp\ConfigurationFile.ini" -Force

        ##Add Instance Name to Configuration File If it is not the Default Instance
        IF ($InstanceName -ne 'MSSQLSERVER')
        {
        $NewConfigFile3 = FindReplace-String -InputFile "C:\temp\ConfigurationFile.ini" -FindString 'MSSQLSERVER' -replaceString $InstanceName
        $NewConfigFile3 |out-file "C:\temp\ConfigurationFile.ini" -Force
        }

        $TestExists = Test-SQLServerExists -InstanceName $InstanceName -ComputerName $ComputerName
        If ($TestExists -eq $true)
        {
        write-host "$instanceName is already in use. please choose a different SQL Instance Name"
        return
        }
        else
        {
        write-host "About to start the install of SQL Server"

        $IsoDrive = Mount-SQLIso

  

        If ($IsoDrive -ne $null)
        {
        write-host $IsoDrive
        start-SQLInstall -ISODrive $IsoDrive
        sleep -Seconds 5
        }


        }

        
        }

        Catch 

        {
         $ErrorMessage = $_.Exception.Message
         Write-Error -Message $ErrorMessage
        }

        finally
        {
        ##clean up files
        Dismount-DiskImage -ImagePath "c:\temp\SQLServer.iso"
        remove-item -Path "C:\temp\ConfigurationFile.ini"
        remove-item -Path "C:\temp\SQLServer.iso"
        }


     }
       
       
 }


 Function FindReplace-String

 {
  param (
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $InputFile,
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $FindString,
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $replaceString
    )

 process
  {
    try
    {

        (Get-Content $inputFile) | foreach {$_.replace($findString,$replaceString)}

        }

         Catch 

        {
         $ErrorMessage = $_.Exception.Message
         Write-Error -Message $ErrorMessage
        }

     }
       
       
 }


 Function Test-SQLServerExists

 {
  param (
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $InstanceName,
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $ComputerName
    )

 process
  {
        $InstanceExists = $false
    try
    {
            $sqlInstances = gwmi win32_service -computerName $ComputerName | ? { $_.Name -match "$InstanceName *" -and $_.PathName -match "sqlservr.exe" } | % { $_.Caption }
            $res = $sqlInstances -ne $null
            if ($res) {
                  Write-Verbose "SQL Server Instance $InstanceName is already installed"
                  $InstanceExists = $true
                  } else {
                  Write-Verbose "SQL Server Instance $instanceName is not installed"
                  $instanceExists = $false
                  }
        return $InstanceExists
        }
        Catch
        {
         $ErrorMessage = $_.Exception.Message
         Write-Error -Message $ErrorMessage
        }

    }

}

Function Mount-SQLIso

{
  param (

    )

 process
 	{
    try
        {
        $setupDriveLetter = (Mount-DiskImage -ImagePath c:\temp\SQLServer.iso -PassThru | Get-Volume).DriveLetter + ":"
        if ($setupDriveLetter -eq $null) {
                throw "Could not mount SQL install iso"
                }
        Write-Host "Drive letter for iso is: $setupDriveLetter"

        }
        Catch
        {
         $ErrorMessage = $_.Exception.Message
         Write-Error -Message $ErrorMessage

        }
        return $setupDriveLetter
    }

}


Function start-SQLInstall

{
  param (
        [Parameter(Mandatory=$True)]
        [ValidateNotNullOrEmpty()]
        [String]
        $ISODrive

    )

 process
 	{
    try
        {
        # run the installer using the ini file
        $cmd = "$IsoDrive\Setup.exe /ConfigurationFile=c:\temp\ConfigurationFile.ini /SQLSVCPASSWORD=$SQLPW /AGTSVCPASSWORD=qtkkKq>CN[9U2FSb /SAPWD=$AGTPW"
        Write-Host "Running SQL Install - check %programfiles%\Microsoft SQL Server\120\Setup Bootstrap\Log\ for logs..."
        Invoke-Expression $cmd | Write-Verbose
       

        }
        Catch
        {
         $ErrorMessage = $_.Exception.Message
         Write-Error -Message $ErrorMessage
         $Success = $false
        }
        $success=$true
    }
    
}

 

I save the script as ‘Install-SQLServer.ps1’. To run this you need to first log onto the server where you are installing SQL Server and open the script in PowerShell ISE.

There are default parameter values I use so that that the actual PowerShell command doesn’t become too long but it’s up to you if you enter these or not:

-ComputerName This will default to your local computer. As I generally run this script on the Server, I do not need to use this when I call the script.

-PackagePath – This is where your SQL package lives. I prefer to enter a default parameter for each version of SQL I install so I don’t have to enter this every time.

-PackageName – This is the full name of the ISO image. I normally set this beforehand. This script will install all editions of SQL Server.

-InstanceName – This will be the Default Instance but you can change this to a named instance if you require (If so, make sure  your SQL Browser is set to automatic).

-SQLAccount – This is one parameter I don’t use a default value for. You must enter your SQL account here.

-SQLAgent – The SQL agent account goes here

-SQLPW – Another non-default parameter. This will hold your SQL Service account password

-AGTPW – This will hold your Agent account password.

SAPW – This will hold your SA password (if you are using mixed mode)

The following command will start the basic install providing that you’ve entered default parameters

Install-SQLServer -SQLAccount ‘DOMAIN\SVC_SERVERNAME_SQL' -SQLAgent 'DOMAIN\SVC_SERVERNAME_AGT' -SQLPW 'ServiceP4ssword!' -AGTPW 'AgentP4ssword!' -SAPW 'SAP4ssword!'

And that’s it! Although there are a number of other things I include with this script to complete my standard build as part of my best practices but I appreciate these might not be part of your best practices:

Ultimately what I do is I create another PowerShell function that runs the basic install but also configures all of my best practices (tempdb size, filegrowths, database mail, fail-safes, maintenance jobs etc).

And I promise to cover my SQL Build best practices in another blog post.

Further Reading

Install SQL Server using a Configuration File

Install SQL Server from the Command Prompt

Standardize SQL Server Installations with Configuration Files