install-sql2k14-on-s12r2.ps1

# install-sql2k14-on-s12r2.ps1 
#
# Windows Azure Pack runbook to automate the install and initial
# setup of SQL Server 2014 on Windows Server 2012R2
#
# sjnovick 4/11/2016
#

workflow Install-SQL2K14-On-S12R2
{
    param
    (
        [Parameter(Mandatory=$true)]
        [string] $ComputerName,
        [Parameter(Mandatory=$false)]
        [string] $SQLInstanceName
    )
   
    Write-Output "* Runbook Install-SQL2K14-On-S12R2 started *"
    
    #VARIABLES
    $ADCred = Get-AutomationPSCredential -Name "adcred1"   
    $SQLCred = Get-AutomationPSCredential -Name "sqlcred1"
    $tcpipPort = 3470
    
    if ($SQLInstanceName) {$InstanceName = $SQLInstanceName}
    else {$InstanceName = "MSSQLSERVER"}
    Write-Output "SQL Server instance name: $InstanceName"
    
    InlineScript {
       
        #INSTALL SQL
        Write-Output "Installing SQL Server..."
        try {
            #Write-Output ""
            $sb = {
                param($InstanceName)
                $configfile="F:\SQL2K14\ConfigurationFile.INI"
                $hostname = hostname
                $acct="$hostname\0-sjno"
                $pwd='redacted'
                $sapwd='redacted'
                F:\SQL2K14\setup.exe /SQLSVCACCOUNT=$acct /SQLSVCPASSWORD=$pwd /AGTSVCACCOUNT=$acct /AGTSVCPASSWORD=$pwd /ISSVCACCOUNT=$acct /ISSVCPASSWORD=$pwd /SAPWD=$sapwd /INSTANCENAME=$InstanceName /CONFIGURATIONFILE=$configfile
            }
            Invoke-Command -ComputerName $Using:ComputerName -Credential $Using:ADCred -ScriptBlock $sb -Args $Using:InstanceName
        }
        catch { Write-Output "!!! SQL SERVER INSTALLATION FAILED !!!" }
    
        #CHANGE SQL SERVICE LOGON USER
        Write-Output "Changing logon user on SQL services..."
        try {
            $sb2 = {
                param($InstanceName)
                $user='SJNO\1-sql_nonprod'
                $pw='redacted'
                if ($InstanceName -ne "MSSQLSERVER"){
                    $agentsvc = "SQLAgent"+"$"+"$InstanceName"
                    $serversvc = "MSSQL"+"$"+"$InstanceName"}
                else{
                    $agentsvc = "SQLSERVERAGENT"
                    $serversvc = "MSSQLSERVER"}
                Write-Output "Agent Service Name: $agentsvc"
                Write-Output "Server Service Name: $serversvc"
                sc.exe config "$agentsvc" obj= "$user" password= "$pw"
                sc.exe config "$serversvc" obj= "$user" password= "$pw"
                sc.exe config "MsDtsServer120" obj= "$user" password= "$pw"
            }
            Invoke-Command -ComputerName $Using:ComputerName -Credential $Using:ADCred -ScriptBlock $sb2 -Args $Using:InstanceName
        }
        catch { Write-Output "!!! CHANGING SQL SERVICE LOGON USERS FAILED !!!" }
        
        #CHANGE SQL DEFAULT BACKUP LOCATION
        Write-Output "Changing SQL default backup location to F:\SQLBACKUPS..."
        try {
            $sb3= {
                $path = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQLServer"
                $name = "BackupDirectory"
                $value = "F:\SQLBACKUPS"
                New-ItemProperty -Path $path -Name $name -Value $value -PropertyType String -Force | Out-Null
            }
            Invoke-Command -ComputerName $Using:ComputerName -Credential $Using:ADCred -ScriptBlock $sb3
        }
        catch { Write-Output "!!! CHANGING SQL DEFAULT BACKUP LOCATION FAILED !!!" }
      
        #CHANGE SQL SERVER TEMPDB CONFIG
        try {
            Write-Output "Altering SQL Server tempdb config..."
            $sb5= {
                param($ComputerName,$InstanceName)
                if ($InstanceName -ne "MSSQLSERVER"){
                    $ServerInstance = "$ComputerName\$InstanceName"}
                else{
                    $ServerInstance = "$ComputerName"}
                ipmo sqlps
                Invoke-Sqlcmd -Query "USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\SQLTEMPDB\tempdb.mdf', SIZE = 1024, FILEGROWTH = 0);
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLTEMPDB\templog.ldf', SIZE = 4096, FILEGROWTH = 1024, MAXSIZE = 5120);
GO
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'F:\SQLTEMPDB\tempdb2.mdf', SIZE = 1024, FILEGROWTH = 0);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev3, FILENAME = 'F:\SQLTEMPDB\tempdb3.mdf', SIZE = 1024, FILEGROWTH = 0);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev4, FILENAME = 'F:\SQLTEMPDB\tempdb4.mdf', SIZE = 1024, FILEGROWTH = 0);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev5, FILENAME = 'F:\SQLTEMPDB\tempdb5.mdf', SIZE = 1024, FILEGROWTH = 0);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev6, FILENAME = 'F:\SQLTEMPDB\tempdb6.mdf', SIZE = 1024, FILEGROWTH = 0);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev7, FILENAME = 'F:\SQLTEMPDB\tempdb7.mdf', SIZE = 1024, FILEGROWTH = 0);
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev8, FILENAME = 'F:\SQLTEMPDB\tempdb8.mdf', SIZE = 1024, FILEGROWTH = 0);
GO" -ServerInstance "$ServerInstance"   
            }
            Invoke-Command -ComputerName $Using:ComputerName -Credential $Using:SQLCred -ScriptBlock $sb5 -Args $Using:ComputerName,$Using:InstanceName
        }
        catch { Write-Output "!!! SQL SERVER TEMPDB CONFIG FAILED !!!" }

        #CHANGE SQL SERVER MAX MEMORY
        try {
            Write-Output "Setting SQL Server max memory..."
            $sb6= {
                param($ComputerName,$InstanceName)
                if ($InstanceName -ne "MSSQLSERVER"){
                    $ServerInstance = "$ComputerName\$InstanceName"}
                else{
                    $ServerInstance = "$ComputerName"}
                $ram = Get-WMIObject -class Win32_PhysicalMemory | Measure-Object -Property capacity -Sum
                $ram = $ram.sum
                $ram = $ram/1MB
                Write-Output "Server RAM: $ram MB"
                $sqlram = $ram-2048
                Write-Output "Setting SQL Server max memory to: $sqlram MB"
                ipmo sqlps
                Invoke-Sqlcmd -Query "sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', $sqlram;
GO
RECONFIGURE;
GO" -ServerInstance "$ServerInstance"   
            }
            Invoke-Command -ComputerName $Using:ComputerName -Credential $Using:SQLCred -ErrorAction Stop -ScriptBlock $sb6 -Args $Using:ComputerName,$Using:InstanceName
        }
        catch { Write-Output "!!! SETTING SQL SERVER MAX MEMORY FAILED !!!" }
    
        #CHANGE STATIC TCPIP PORT
        try {
            Write-Output "Changing static TCP/IP port to 3470..."
            $sb7= {
                param($ComputerName,$InstanceName,$tcpipPort)
                ipmo sqlps
                $smo = 'Microsoft.SqlServer.Management.Smo.'
                $wmi = New-Object ($smo + 'Wmi.ManagedComputer')
                $uri = "ManagedComputer[@Name='$ComputerName']/ ServerInstance[@Name='$InstanceName']/ServerProtocol[@Name='Tcp']"
                $Tcp = $wmi.GetSmoObject($uri)
                foreach ($ipAddress in $Tcp.IPAddresses){
                    $ipAddress.IPAddressProperties["TcpDynamicPorts"].Value = ""
                    $ipAddress.IPAddressProperties["TcpPort"].Value = "$tcpipPort"
                    }
                $Tcp.Alter()
                }
            Invoke-Command -ComputerName $Using:ComputerName -Credential $Using:SQLCred -ScriptBlock $sb7 -Args $Using:ComputerName,$Using:InstanceName,$Using:tcpipPort
        }
        catch { Write-Output "!!! SETTING SQL SERVER STATIC TCPIP PORT FAILED !!!" }
  
        #UPDATE COMMENT AND DIVISION ATTRIBUTES IN AD
        try {
            Write-Output "Updating Comment and Division attributes in AD..."
            $sb8= {
                param($ComputerName,$tcpipPort)
                $division = "DB"
                Set-ADComputer $ComputerName -Replace @{Comment=$tcpipPort;Division=$division}
            }
            Invoke-Command -ComputerName dc1.sjno.net -Credential $Using:ADCred -ScriptBlock $sb8 -Args $Using:ComputerName,$Using:tcpipPort
        }
        catch { Write-Output "!!! UPDATING COMMENT AND DIVISION ATTRIBUTES IN AD FAILED !!!" }
   
        #REBOOT
        try {
            Write-Output "Rebooting..."
            $sb4= {
                param($ComputerName)
                ipmo VirtualMachineManager
                $vmm = "vmm.sjno.net"
                $vm = Get-SCVirtualMachine -vmmserver $vmm -Name $ComputerName
                Stop-SCVirtualMachine -VM $vm -Shutdown -ErrorAction Stop | Out-Null
                Start-SCVirtualMachine -VM $vm -ErrorAction Stop | Out-Null    
            }
            Invoke-Command -ComputerName vmm.sjno.net -Credential $Using:ADCred -ScriptBlock $sb4 -Args $Using:ComputerName
        }
        catch { Write-Output "!!! REBOOT FAILED !!!" }

        #PAUSE FOR REBOOT
        Write-Output "Pausing to let VM come back up..."
        Start-Sleep -s 15

    Write-Output "* Runbook Install-SQL2K14-On-S12R2 finished *" 
    }
}