Its maybe not so interesting or worthwhile now with DSC about, but in my last job i had a standard SQL server installation PowerShell script which i thought i would share. All the server team would need to do is create a domained OS 2008+ box with a few standard SQL Server drives, then we could run the below script which would create throw away AD Service accounts, create folders for SQL Server Data, Log, TempDB etc, Add the SQL Server Service account to the Instant File Initialization and run some standard config scripts at the end (Note you would need permissions in AD to create and update passwords, and local admin on the install box).

To use the script it should just be a case of updating the variables at the top for locations of the SQL install media, AD paths etc.. Its not a completed script as i would have liked it to be, with that at present its set up that should any part of it error the script will stop, just to save further errors mainly, The main catch is if the SQL Installs fails it doesn’t error (Just writes the error to the install log) so the following section to run the SQL Server configuration script would then error as sqlps doesn’t exist!

One of the more interesting parts of the script is getting SQLPS to run straight after an install of SQL. if you try to load the sqlps module it will fail unable to find it. I got around this by doing a search for the sqlps.exe and calling that. Another thing to note is that I didnt write the code which adds the SQL Service Account to the local security policy for Instant File initialization (Sadly i cant find where I did get this code from, if anyone reconizes it please let me know so I update with the details!) Anyway for those not using DSC and looking for a template to base there SQL installs on hopefully this is useful to you.


<# # SQL SERVER INSTALL Steps to complete: Create folders Create Service Accounts Add SQL Server Service Account to Local security group for volume maintenance tasks Add SQL Server Service Account to AD Group for Backup Security, etc Install pre-reqs for SQL Server (.net) Install SQL Server (slipstream patches) Run SQL Standardization Scripts # #>
## SCRIPT LEVEL VARIABLES 
## Servers Name
$ServerName = $env:COMPUTERNAME
## Domain instance being installed to
$SQLDomain = $env:userdnsdomain 
## Root Installation Folder
$SQLRoot = "\\172.29.224.135"
## Location of SQL Server Setup.exe
$SQLSetup = "$SQLRoot\SQL2014Dev\setup.exe"
## Location of SQL Server SP / CUs
$SQLPatch = "$SQLRoot\SQL2014Updates"
## Location of Windows SXS folder for .NET install
$WindowsSXSFolder = "$SQLRoot\sxs"
## SQL Server Standardization Script Location
$Scriptsfolder = "$SQLRoot\Scripts"
## Configuration File
$SQLConfig = "$SQLRoot\Config\StandardInstall.ini"
## Groups for Service Account Memembership
$SQLServerServiceGroup = "SQL_Server_ServiceAccounts"
$SQLAgentServiceGroup = "SQL_Server_Agent_Accounts"
## SQL Admin Group
$SQLAdminGroup = "$SQLDomain\DBA Admin Group"
## Stop script running when error occures.
$ErrorActionPreference = "Stop"
## Configure Service Account names
$SVC = "SVC-"
$ServerName2 = $ServerName.Replace("WIN-","")
## SQL Server Service Account
$SVCSQL = $SVC + $ServerName2
## SQL Server Agent Service Account
$SVCAgt = $SVC + $ServerName2 + "_A"
## Path to OU for SQL Server Service accounts
$OUPath = "OU=SQL, OU=Service Accounts,DC=MYDOMAIN,DC=COM"


<# Create folders #>
$SQLDataFolder = "F:\SQL_Data"
$SQLLogFolder = "G:\SQL_Log"
$SQLTempDBFolder = "T:\SQL_TempDB"
$SQLFolder = "E:\SQL_Server"

IF (!(Test-Path $SQLDataFolder))
    {
       New-Item -ItemType directory -Path $SQLDataFolder
    } 

IF (!(Test-Path $SQLLogFolder))
    {
       New-Item -ItemType directory -Path $SQLLogFolder
    } 

IF (!(Test-Path $SQLTempDBFolder))
    {
       New-Item -ItemType directory -Path $SQLTempDBFolder
    } 
IF (!(Test-Path $SQLFolder))
    {
       New-Item -ItemType directory -Path $SQLFolder
    } 

## turn off firewall
netsh advfirewall set allprofiles state off


<# Create Service Accounts #>
## Load AD PowerShell modules
IF ((Get-WindowsFeature RSAT-AD-PowerShell).InstallState -ne 'Installed') 
	{
		Add-WindowsFeature RSAT-AD-PowerShell
	}


## Password generator
Function Get-TempPassword() 
	{
		Param
			(
				[int]$length=10,
				[string[]]$sourcedata
			)
		
		For ($loop=1; $loop –le $length; $loop++) 
			{
				$TempPassword+=($sourcedata | GET-RANDOM)
			}
		return $TempPassword
	}

$ascii=$NULL
For ($a=33;$a –le 126;$a++) 
    {
        $ascii+=,[char][byte]$a 
    }
## Passwords for Accounts
$SVCSQLPsw = Get-TempPassword -length 15 -sourcedata $ascii
$SVCAgtPsw = Get-TempPassword -length 15 -sourcedata $ascii
$SAPsw = Get-TempPassword -length 15 -sourcedata $ascii

## Create SQL Server Service Account 
IF (!(Get-ADUser -LDAPFilter "(sAMAccountName=$SVCSQL)"))
	{
		Write-Host "User Account $SVCSQL being created" -ForegroundColor Green
        New-ADUser  $SVCSQL -AccountPassword (ConvertTo-SecureString $SVCSQLPsw -AsPlainText -force) -Enabled $True -PasswordNeverExpires $True -Path $OUPath
	}
ELSE 
	{
		Write-Host "$SVCSQL found in AD updating password" -ForegroundColor Yellow
        ##Remove-ADUser $SVCAgt -Confirm:$false
        Set-ADAccountPassword $SVCSQL -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $SVCSQLPsw -Force)
	}

## Create SQL Agent Service Account 
IF (!(Get-ADUser -LDAPFilter "(sAMAccountName=$SVCAgt)"))
	{
		Write-Host "User Account $SVCAgt being created" -ForegroundColor Green
        New-ADUser $SVCAgt -AccountPassword (ConvertTo-SecureString $SVCAgtPsw -AsPlainText -force) -Enabled $True -PasswordNeverExpires $True -Path $OUPath
	}
ELSE 
	{
		Write-Host "$SVCAgt found in AD updating password" -ForegroundColor Yellow
        ##Remove-ADUser $SVCAgt -Confirm:$false
        Set-ADAccountPassword $SVCAgt -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $SVCAgtPsw -Force)
	}
 


## Create Install Vars containing Domain
$SQLService = $SQLDomain + "\" + $SVCSQL
$SQLAgentService = $SQLDomain + "\" + $SVCAgt



<# Add SQL Server Service Account to Local security group for volume mantinenace tasks #>
secedit /export /areas USER_RIGHTS /cfg C:\templocalsec.cfg
$privline = Get-Content C:\templocalsec.cfg | Select-String 'SeManageVolumePrivilege'
(Get-Content C:\templocalsec.cfg).Replace($privline,"$privline,$SVCSQL") | Out-File C:\newlocalsec.cfg
secedit /configure /db secedit.sdb /cfg C:\newlocalsec.cfg

<# Add SQL Server Service to AD group #>
$SQLServerServiceGroupMembers = Get-ADGroupMember -Identity $SQLServerServiceGroup -Recursive

IF (!($SQLServerServiceGroupMembers.SamAccountName –contains $SVCSQL))
    {
        Write-host "The $SVCSQL account has been added to the group: $SQLServerServiceGroup" -ForegroundColor Green
        Add-ADGroupMember $SQLServerServiceGroup $SVCSQL
    }
Else
    {
        Write-host "The $SVCSQL account is already in to group: $SQLServerServiceGroup" -ForegroundColor Yellow
    }


<# Add SQL Server Agent Service to AD group #>
$SQLAgentServiceGroupMembers = Get-ADGroupMember -Identity $SQLAgentServiceGroup -Recursive

IF (!($SQLAgentServiceGroupMembers.SamAccountName –contains $SVCAgt))
    {
        Write-host "The $SVCAgt account has been added to the group: $SQLAgentServiceGroup" -ForegroundColor Green
        Add-ADGroupMember $SQLAgentServiceGroup $SVCAgt
    }
Else
    {
        Write-host "The $SVCAgt account is already in to group: $SQLAgentServiceGroup" -ForegroundColor Yellow
    }


<# Install pre-reqs for SQL Server #>
IF ((Get-WindowsFeature Net-Framework-Core).InstallState -ne 'Installed') 
	{
		Install-WindowsFeature Net-Framework-Core -Source $WindowsSXSFolder
	}


<# Install SQL Server #>
Write-Host "Install SQL Server -------------------------------" -BackgroundColor DarkGreen -ForegroundColor White

IF(!(Get-Service -Name MSSQLSERVER))
    {
& $SQLSetup /SQLSVCACCOUNT=$SQLService /INSTANCEDIR=$SQLFolder /SQLSVCPASSWORD=$SVCSQLPsw /AGTSVCACCOUNT=$SQLAgentService /AGTSVCPASSWORD=$SVCAgtPsw `
/SAPWD=$SAPsw /UpdateSource=$SQLPatch /SQLUSERDBDIR=$SQLDataFolder /SQLUSERDBLOGDIR=$SQLLogFolder `
/SQLTEMPDBDIR=$SQLTempDBFolder /SQLSYSADMINACCOUNTS=$SQLAdminGroup  `
/IACCEPTSQLSERVERLICENSETERMS /ConfigurationFile=$SQLConfig 
    }
ELSE
    {
        Write-Host "There is already a SQL Server service on this instance!" -ForegroundColor White -BackgroundColor DarkRed
    }
 

<# Run SQL Standardization Scripts #>
$SqlPs = Get-ChildItem -path "C:\Program Files (x86)" -filter sqlps.exe -Recurse -Force
$scripts = Get-ChildItem $Scriptsfolder | Where-Object {$_.Extension -eq ".sql"}

foreach ($s in $scripts)
    {
        Write-Host "Running Script : " $s.Name -BackgroundColor DarkGreen -ForegroundColor White
        $script = $s.FullName
        & $SqlPs.FullName -Command "Invoke-Sqlcmd -ServerInstance $ServerName -InputFile $script"
    }
 

<# Restart the SQL Server Agent Service to update Mail settings #>
Write-Host "Restarting SQL Server Agent Service after updating mail configuration" -ForegroundColor Green
Get-Service -computer . SQLSERVERAGENT | Restart-Service
Write-Host "SQL Server Agent Server account is currently in:" -ForegroundColor Green
Get-Service -computer . SQLSERVERAGENT | Select name, status  




Advertisements