In my previous blog post I generally described how we deploy SharePoint within our organization. The first step is to install SQL Server. Because we install everything using unattended PowerShell scripts, we also install SQL Server from this script. This post will show how we do it.
Create settings file
As described in this post, we need a settings.xml file, describing what to install and where to find the binaries. This looks like this (My server is called STTO-SQL):
<?xml version="1.0" ?>
<SP2010Config>
<Binaries>
<SQLServerR2 displayname="Sql Server 2008 R2"
location="E:\setup.exe" />
</Binaries>
<Topology>
<!--DataBaseServer-->
<Server Name="STTO-SQL" >
<Install_SQL2008R2 />
</Server>
</Topology>
<General>
<PasswordFile>.\STTO-passwords.xml</PasswordFile>
</General>
<Domain>
<DomainName>STTO</DomainName>
<DNSDomainName>stto.local</DNSDomainName>
</Domain>
<SQL2008R2>
<SQLEngineServiceAccount>STTO\svcSQL-SPDB</SQLEngineServiceAccount>
<SQLSysAdminAccounts>Builtin\Administrators</SQLSysAdminAccounts>
<INSTALLSHAREDDIR>C:\Program Files\Microsoft SQL Server</INSTALLSHAREDDIR>
<INSTALLSHAREDWOWDIR>C:\Program Files (x86)\Microsoft SQL Server</INSTALLSHAREDWOWDIR>
<INSTANCEDIR>C:\Program Files\Microsoft SQL Server</INSTANCEDIR>
<SQLTEMPDBDIR>D:\SQLData</SQLTEMPDBDIR>
<SQLTEMPDBLOGDIR>D:\SQLTransactionLog</SQLTEMPDBLOGDIR>
<SQLUSERDBDIR>D:\SQLData</SQLUSERDBDIR>
<SQLUSERDBLOGDIR>D:\SQLTransactionLog</SQLUSERDBLOGDIR>
</SQL2008R2>
</SP2010Config>
The settings file tells the installer to install SQL Server 2008R2, where to find the password file and it has a configuration section (SQL2008R2) for SQL setup settings. The ZIP file contains the password file in the same folder. It is a better idea to store that in a network share where just the installer account has the permissions to get to it.
Create SQL Configuration file
Next thing to prepare is the configuration file for the unattended SQL installation. This article shows you how to do that. The easiest way is to used the SQL setup wizard up to the “Ready to Install” step and taking the ini file the setup program created. If you are installing R2, please note this before you start the installation.
The config file in the zip file contains this sample configuration:
[SQLSERVER2008]
IACCEPTSQLSERVERLICENSETERMS="True"
INSTANCEID="MSSQLSERVER"
ACTION="Install"
FEATURES=SQLENGINE,BIDS,SSMS,ADV_SSMS
HELP="False"
INDICATEPROGRESS="True"
QUIET="False"
QUIETSIMPLE="True"
X86="False"
ENU="True"
ERRORREPORTING="False"
INSTALLSHAREDDIR=
INSTALLSHAREDWOWDIR=
INSTANCEDIR=
SQMREPORTING="False"
INSTANCENAME="MSSQLSERVER"
AGTSVCACCOUNT=
AGTSVCPASSWORD=
AGTSVCSTARTUPTYPE="Automatic"
ISSVCSTARTUPTYPE="Automatic"
ISSVCACCOUNT="NT AUTHORITY\NetworkService"
ASSVCSTARTUPTYPE="Automatic"
ASCOLLATION="Latin1_General_CI_AS"
ASDATADIR="Data"
ASLOGDIR="Log"
ASBACKUPDIR="Backup"
ASTEMPDIR="Temp"
ASCONFIGDIR="Config"
ASPROVIDERMSOLAP="1"
FARMADMINPORT="0"
SQLSVCSTARTUPTYPE="Automatic"
FILESTREAMLEVEL="0"
ENABLERANU="0"
SQLCOLLATION="Latin1_General_CI_AS"
SQLSVCACCOUNT=
SQLSVCPASSWORD=
SQLSYSADMINACCOUNTS=
TCPENABLED="1"
NPENABLED="1"
BROWSERSVCSTARTUPTYPE="Disabled"
RSSVCACCOUNT=
RSSVCPASSWORD=
RSSVCSTARTUPTYPE="Automatic"
RSINSTALLMODE="DefaultSharePointMode"
SQLTEMPDBDIR=
SQLTEMPDBLOGDIR=
SQLUSERDBDIR=
SQLUSERDBLOGDIR=
You can find this file in the ZIP file in the .\scripts\DB folder. It is called ‘SQL2008R2_Unattended.ini’. A number of settings are not specified in this file, these are replaced while installing by the values provided in the settings file. The ini file provided is used as a template file. The installer adds the values from the settings file and saves it to a copy of this ini file. That copy is the file that will be used by the SQL installer. The advantage of doing this is that we now have 1 central XML where we can control the settings of our SQL installation.
And we also don’t need to put password in the ini file. The installer does that for us.
Run the installer
After logging on to the server and starting a PowerShell window (using Run as Administrator!) we can start the script by starting Install.ps1. This script file calls 2 other powershell files from the .scripts\DB folder:
$CurrentFolder = Get-Location
#Clear all previous errors
$Error.Clear()
## Run all scripts intended for database servers in the farm
& $CurrentFolder\Scripts\DB\1.PrePareSql2k8R2Unattended.ps1
& $CurrentFolder\Scripts\DB\2.InstallSQL-2008R2.ps1
## SQL Setup has failed.
if($LastExitCode -eq 123)
{
Exit
}
Write-Host "Finished"
Both ps1 files first load the Utils.ps1 file from the support folder. This utils file contains some general functions (like getting a password from the password file, logging, error handling, etc.) It also gets all general settings from the Settings.xml file.
Preparing the ini file
The first PS1 file prepares the ini file required by the SQL installation process. It takes the template and adds to the configuration from the settings.xml file to that ini file.
#Include library from $RootScriptFolder\Support folder
$CurrentFolder = Split-Path $myInvocation.MyCommand.Definition -Parent
$ParentFolder = Split-Path $CurrentFolder -Parent
$RootScriptFolder = Split-Path $ParentFolder -Parent
. $RootScriptFolder\Support\Utils.ps1
Function ReplaceInUnattendedFile
{ param ($Find, $Replace)
$Path = Resolve-Path $UnattendedFile
$Text = [String]::join([Environment]::newline, (Get-Content -Path $Path))
$NewText = $Text.Replace($Find, $Replace)
Set-Content $Path -value $NewText
}
Function CreateDir
{
param ($Path)
try
{
If(-not (Test-Path $Path))
{
LogMessage "Creating $Path"
New-Item -path $Path -type Directory
}
}
catch
{
LogError "Error creating directory $Path"
}
}
Function PrepareSQL2008R2UnattendedInstall
{
if (-not ((Test-Path "$env:ProgramFiles\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlagent.exe") -or `
(Test-Path "$env:ProgramFiles\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlagent.exe") -or `
(Test-Path "$env:ProgramFiles(x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlagent.exe") -or `
(Test-Path "$env:ProgramFiles(x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlagent.exe") ))
{
## Read required parameters from settings
$SQLSource = [System.IO.Path]::GetDirectoryName($Binaries.SQLServerR2.location)
$SQLEngineServiceAccount = $SQL2008R2.SQLEngineServiceAccount
$SQLEngineServiceAccountPassword = GetPassword $SQL2008R2.SQLEngineServiceAccount
$SQLSysAdminAccounts = $SQL2008R2.SQLSysAdminAccounts
$INSTALLSHAREDDIR = $SQL2008R2.INSTALLSHAREDDIR
$INSTALLSHAREDWOWDIR = $SQL2008R2.INSTALLSHAREDWOWDIR
$INSTANCEDIR = $SQL2008R2.INSTANCEDIR
$SQLTEMPDBDIR = $SQL2008R2.SQLTEMPDBDIR
$SQLTEMPDBLOGDIR = $SQL2008R2.SQLTEMPDBLOGDIR
$SQLUSERDBDIR = $SQL2008R2.SQLUSERDBDIR
$SQLUSERDBLOGDIR = $SQL2008R2.SQLUSERDBLOGDIR
CreateDir $INSTALLSHAREDDIR
CreateDir $INSTALLSHAREDWOWDIR
CreateDir $INSTANCEDIR
CreateDir $SQLTEMPDBDIR
CreateDir $SQLTEMPDBLOGDIR
CreateDir $SQLUSERDBDIR
CreateDir $SQLUSERDBLOGDIR
$UnattendedFile = "$RootScriptFolder\Scripts\DB\sql-2008R2.ini"
$UnattendedSourceFile = "$RootScriptFolder\Scripts\DB\SQL2008R2_Unattended.ini"
## Overwrite unattended file of previous runs
LogMessage "- Preparing unattend SQL setup file"
Copy-Item $UnattendedSourceFile $UnattendedFile -Force
Get-Item $UnattendedFile | % { $_.IsReadOnly = $false }
## Store specified variables in the unattended file
ReplaceInUnattendedFile "SQLSVCACCOUNT=" "SQLSVCACCOUNT=`"$SQLEngineServiceAccount`""
ReplaceInUnattendedFile "SQLSVCPASSWORD=" "SQLSVCPASSWORD=`"$SQLEngineServiceAccountPassword`""
ReplaceInUnattendedFile "AGTSVCACCOUNT=" "AGTSVCACCOUNT=`"$SQLEngineServiceAccount`""
ReplaceInUnattendedFile "AGTSVCPASSWORD=" "AGTSVCPASSWORD=`"$SQLEngineServiceAccountPassword`""
ReplaceInUnattendedFile "RSSVCACCOUNT=" "RSSVCACCOUNT=`"$SQLEngineServiceAccount`""
ReplaceInUnattendedFile "RSSVCPASSWORD=" "RSSVCPASSWORD=`"$SQLEngineServiceAccountPassword`""
ReplaceInUnattendedFile "SQLSYSADMINACCOUNTS=" "SQLSYSADMINACCOUNTS=`"$SQLSysAdminAccounts`""
ReplaceInUnattendedFile "INSTALLSHAREDDIR=" "INSTALLSHAREDDIR=`"$INSTALLSHAREDDIR`""
ReplaceInUnattendedFile "INSTALLSHAREDWOWDIR=" "INSTALLSHAREDWOWDIR=`"$INSTALLSHAREDWOWDIR`""
ReplaceInUnattendedFile "INSTANCEDIR=" "INSTANCEDIR=`"$INSTANCEDIR`""
ReplaceInUnattendedFile "SQLTEMPDBDIR=" "SQLTEMPDBDIR=`"$SQLTEMPDBDIR`""
ReplaceInUnattendedFile "SQLTEMPDBLOGDIR=" "SQLTEMPDBLOGDIR=`"$SQLTEMPDBLOGDIR`""
ReplaceInUnattendedFile "SQLUSERDBDIR=" "SQLUSERDBDIR=`"$SQLUSERDBDIR`""
ReplaceInUnattendedFile "SQLUSERDBLOGDIR=" "SQLUSERDBLOGDIR=`"$SQLUSERDBLOGDIR`""
}
}
if ((NeedsInstall "Install_SQL2008R2") -eq $true) { PrepareSQL2008R2UnattendedInstall }
The configuration file is now saved as sql-2008R2.ini an it is ready to be used by the SQL installer.
Installing SQL Server
The second PS1 file, InstallSQL-2008R2.ps1 installs SQL Server. But only if SQL is needed on the server we are currently running the installer on. Remember this is (going to be) a general installer that we run on all servers in the farm. It checks the settings.xml file to see if it can find the current server name in the Topology section.
<Server Name="STTO-SQL" >
<Install_SQL2008R2 />
</Server>
If so, it checks (using the function NeedsInstall at the bottom of the ps1 file) if the current server needs to have SQL installed. If so, it will install the database server.
#Include library from $RootScriptFolder\Support folder
$CurrentFolder = Split-Path $myInvocation.MyCommand.Definition -Parent
$ParentFolder = Split-Path $CurrentFolder -Parent
$RootScriptFolder = Split-Path $ParentFolder -Parent
. $RootScriptFolder\Support\Utils.ps1
$BinaryName = $Binaries.SQLServerR2.displayname
$BinaryLocation = $Binaries.SQLServerR2.location
$UnattendedFile = "$RootScriptFolder\Scripts\DB\sql-2008R2.ini"
Function InstallSQL2008R2
{
if (-not ((Test-Path "$env:ProgramFiles\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlagent.exe") -or `
(Test-Path "$env:ProgramFiles\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlagent.exe") -or `
(Test-Path "$env:ProgramFiles(x86)\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlagent.exe") -or `
(Test-Path "$env:ProgramFiles(x86)\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlagent.exe") ))
{
if (Test-Path $BinaryLocation)
{
LogMessage "- Installing '$BinaryName'"
$currentlocation = Get-Location
LogMessage $currentLocation
$newlocation = [System.IO.Path]::GetDirectoryName($Binaries.SQLServerR2.location)
Set-Location $newlocation
Start-Process -FilePath "$BinaryLocation" -ArgumentList "/CONFIGURATIONFILE=`"$UnattendedFile`" /INDICATEPROGRESS /QS" -Wait
If ((Get-Service | ? { $_.Name -eq "MSSQLSERVER" }) -eq $Null )
{
Set-Location $currentlocation
LogError "Failed to install SQL Server, check logfiles at %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log"
Exit 123
}
Set-Location $currentlocation
LogMessage "- Installation of '$BinaryName' finished"
}
else
{
LogError "- Install path $BinaryLocation not found"
}
}
}
if ((NeedsInstall "Install_SQL2008R2") -eq $true)
{
InstallSQL2008R2
}
At this point our database server is ready. Next setup is to install SharePoint to the application server in our farm. That will be the subject of the next blogpost!
The scripts above are also available in this ZIP file.
