{"id":207,"date":"2013-04-22T17:31:47","date_gmt":"2013-04-22T15:31:47","guid":{"rendered":"http:\/\/www.ludovicocaldara.net\/dba\/?p=207"},"modified":"2013-11-11T11:07:53","modified_gmt":"2013-11-11T09:07:53","slug":"sqlserver-2008r2-unattended-installation-and-configuration-via-powershell","status":"publish","type":"post","link":"https:\/\/www.ludovicocaldara.net\/dba\/sqlserver-2008r2-unattended-installation-and-configuration-via-powershell\/","title":{"rendered":"SQLServer 2008R2 unattended installation and configuration via powershell"},"content":{"rendered":"

My first steps on Powershell<\/strong><\/p>\n

Ok, Ok, as an “Oracle on Linux Certified Expert”, I’ve never been a great fan of SQLServer (I shouldn’t say this, I’m working on SQLServer since release 6.5…) and I’ve always hated the DOS command prompt.
\nHowever, things are changing fast after Microsoft released the Powershell some years ago. It’s surprising, now Windows powershell support new best of breed features like aliases and pipelines. \ud83d\ude00<\/p>\n

Today Microsoft itself recommends Windows Core installations instead of the full ones, and also SQLServer 2012 comes with a lot of new Commandlets to manage your server.<\/p>\n

So I’ve decided to move my first steps in the Powershell world and I’ve created a script for a customer that installs and configure a SQL2008 with a single Powershell script.<\/p>\n

It has been quite painful to complete, you can download the complete script HERE<\/a>.<\/p>\n

Taking parameters<\/strong><\/p>\n

The very first line accepts named parameters. I’ve tried to reduce the number but I’ve preferred to take, as an example, different disks for different tasks.<\/p>\n

param ([string] $instance, [string] $collation, $ServiceAccountPassword, $saPassword, $sourceDir, $servicePackExec, $instDrive, $userDbDrive, $userLogDrive, $tempDbDrive,  $tempLogDrive, $backupDrive , $port )<\/pre>\n
Then I’ve put a little of interaction if some parameters are missing.\u00a0In facts, I can launch my scripts without inline parameters and specify everything when prompted by the script.<\/p>\n
######################\r\n# Getting Parameters #\r\n######################\r\nif ( -not $sourceDir) { $sourceDir = Read-Host 'Source Path containing SQL2008R2 installation ? ' }\r\nif ( -not $servicePackExec) { $servicePackExec = Read-Host 'Full Path to service pack executable [Empty for None]? ' }\r\nif ( -not $instance) { $instance = Read-Host 'Instance Name? ' }\r\nif ( -not $collation ) {$collation = Read-Host 'Collation [Latin1_General_CI_AS]? ' }\r\nif ( -not $collation ) { $collation = \"Latin1_General_CI_AS\" }\r\n\r\nif ( -not $port ) { $port = Read-Host 'TCP Port [50000]? ' }\r\nif ( -not $port ) { $port = \"50000\" }\r\n\r\nif ( -not $ServiceAccountPassword ) {\r\n    [System.Security.SecureString]$ServiceAccountPassword = Read-Host \"Enter the service account password: \" -AsSecureString;\r\n    [String]$syncSvcAccountPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($ServiceAccountPassword));\r\n} else { [String]$syncSvcAccountPassword = $ServiceAccountPassword; }\r\n\r\nif ( -not $saPassword ) {\r\n    [System.Security.SecureString]$saPasswordSec = Read-Host \"Enter the sa password: \" -AsSecureString;\r\n    [String]$saPassword = [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($saPasswordSec));\r\n} else { [String]$saPassword = $ServiceAccountPassword; }\r\n\r\n$instance = $instance.ToUpper()<\/pre>\n
Before I prompt for the drive letters for the installation paths, I display a little table with the available local disks:<\/p>\n
function displayDrives () {\r\n    Get-WmiObject -class \"Win32_LogicalDisk\" | ?{ @(2, 3) -contains $_.DriveType } | where {$_.Freespace} | select Name, VolumeName, Size, FreeSpace\r\n}\r\n\r\n########################\r\n# Getting Disk Letters #\r\n########################\r\n\r\n$driveDisplayed=0\r\n\r\nif ( -not $instDrive ) {\r\n    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }\r\n    $instDrive = Read-Host 'Drive letter (without colon) for Instance Installation? '\r\n}\r\n\r\nif ( -not $userDbDrive ) {\r\n    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }\r\n    $userDbDrive = Read-Host 'Drive letter (without colon) for User Databases? '\r\n}\r\n\r\nif ( -not $userLogDrive ) {\r\n    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }\r\n    $userLogDrive = Read-Host 'Drive letter (without colon) for Transaction Logs ? '\r\n}\r\n\r\nif ( -not $tempDbDrive ) {\r\n    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }\r\n    $tempDbDrive = Read-Host 'Drive letter (without colon) for Temp Database ? '\r\n}\r\n\r\nif ( -not $tempLogDrive ) {\r\n    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }\r\n    $tempLogDrive = Read-Host 'Drive letter (without colon) for Temp Logs ? '\r\n}\r\n\r\nif ( -not $backupDrive ) {\r\n    if ( $driveDisplayed -eq 0 ) { displayDrives ; $driveDisplayed=1 }\r\n    $backupDrive = Read-Host 'Drive letter (without colon) for Backups ? '\r\n}<\/pre>\n
 <\/p>\n
\u00a0Installing prerequisites<\/strong><\/p>\n
###############################\r\n# install prerequisites for SQL2008R2\r\nfunction installPrereqs () {\r\n    Import-Module ServerManager\r\n    Add-WindowsFeature Application-Server,AS-NET-Framework,NET-Framework,NET-Framework-Core,WAS,WAS-Process-Model,WAS-NET-Environment,WAS-Config-APIs\r\n    # get-windowsfeature | Where {$_.Installed} | Sort FeatureType,Parent,Name | Select Name,Displayname,FeatureType,Parent\r\n}\r\n\r\n######################\r\n# Installing Prereqs #\r\n######################\r\n\"Installing Prerequisites (.Net, etc) ...\"\r\ninstallPrereqs<\/pre>\n
The commented command is to get the installed features after the installation. No really need to display it, it works really well.<\/p>\n
\u00a0Dynamically prepare a configuration file<\/strong><\/p>\n
The unattended installation needs some parameters prepared in a configuration file.
\nThis is likely where you will change most of your stuff depending on your standards:
\nComponents, paths, service accounts, you can change everything or modify the script to accept also this variables as parameters.<\/p>\n
The full documentation about filling the configuration file is on the MSDN:<\/p>\n
http:\/\/msdn.microsoft.com\/en-us\/library\/ms144259%28v=sql.105%29.aspx<\/a><\/p>\n
#########################################\r\n# prepare the standard configuration file\r\nfunction prepareConfigFile ([String]$instance, [String]$collation, $instDrive, $userDbDrive, $userLogDrive, $tempDbDrive, $tempLogDrive, $backupDrive ) {\r\n$config = \"[SQLSERVER2008]\r\nACTION=\"\"Install\"\"\r\nFEATURES=SQLENGINE,REPLICATION,FULLTEXT,BIDS,CONN,IS,BC,BOL,SSMS,ADV_SSMS,SNAC_SDK\r\nINSTANCENAME=\"\"$instance\"\"\r\nINSTANCEID=\"\"$instance\"\"\r\nINSTALLSHAREDDIR=\"\"C:\\Program Files\\Microsoft SQL Server\"\"\r\nINSTALLSHAREDWOWDIR=\"\"C:\\Program Files (x86)\\Microsoft SQL Server\"\"\r\nINSTANCEDIR=\"\"C:\\Program Files\\Microsoft SQL Server\"\"\r\nINSTALLSQLDATADIR=\"\"\"+$instDrive+\":\\MSSQL\\$instance\"\"\r\nSQLUSERDBDIR=\"\"\"+$userDbDrive+\":\\MSSQL\\$instance\\MSSQL10_50.\"+$instance+\"\\MSSQL\\Data\"\"\r\nSQLUSERDBLOGDIR=\"\"\"+$userLogDrive+\":\\MSSQL\\$instance\\MSSQL10_50.\"+$instance+\"\\MSSQL\\Tlog\"\"\r\nSQLTEMPDBDIR=\"\"\"+$tempDbDrive+\":\\MSSQL\\$instance\\MSSQL10_50.\"+$instance+\"\\MSSQL\\Data\"\"\r\nSQLTEMPDBLOGDIR=\"\"\"+$tempLogDrive+\":\\MSSQL\\$instance\\MSSQL10_50.\"+$instance+\"\\MSSQL\\Tlog\"\"\r\nSQLBACKUPDIR=\"\"\"+$backupDrive+\":\\MSSQL\\$instance\\MSSQL10_50.\"+$instance+\"\\MSSQL\\Backup\"\"\r\nFILESTREAMLEVEL=\"\"0\"\"\r\nTCPENABLED=\"\"1\"\"\r\nNPENABLED=\"\"1\"\"\r\nSQLCOLLATION=\"\"$collation\"\"\r\nSQLSVCACCOUNT=\"\"MYDOM\\sqlsrvc\"\"\r\nSQLSVCSTARTUPTYPE=\"\"Automatic\"\"\r\nAGTSVCACCOUNT=\"\"MYDOM\\sqlsrvc\"\"\r\nAGTSVCSTARTUPTYPE=\"\"Automatic\"\"\r\nISSVCACCOUNT=\"\"NT AUTHORITY\\NetworkService\"\"\r\nISSVCSTARTUPTYPE=\"\"Automatic\"\"\r\nBROWSERSVCSTARTUPTYPE=\"\"Automatic\"\"\r\nSQLSYSADMINACCOUNTS=\"\"MYDOM\\sqlsrvc\"\"\r\nSECURITYMODE=\"\"SQL\"\"\r\nSQMREPORTING=\"\"False\"\"\r\nIACCEPTSQLSERVERLICENSETERMS=\"\"True\"\"\"\r\n\r\n$config\r\n}\r\n\r\n#####################\r\n# Creating Ini File #\r\n#####################\r\n$workDir = pwd\r\n\r\n\"Creating Ini File for Installation...\"\r\n$configFile = \"$workDir\\sql2008_\"+$instance+\"_install.ini\"\r\n\r\nprepareConfigFile $instance $collation $instDrive $userDbDrive $userLogDrive $tempDbDrive $tempLogDrive $backupDrive | Out-File $configFile\r\n\r\n\"Configuration File written to: \"+$configFile<\/pre>\n
Starting the SQL Server 2008 R2 installation<\/strong><\/p>\n
Off course you’ll need an installation media downloaded from the Microsoft customers site with the correct License Keys and mounted somewhere.\u00a0 (remember the $sourceDir parameter?) I’ve decided to change the path in the directory containing the media and then change it back.<\/p>\n
#######################################\r\n# Starting SQL 2008 Base Installation #\r\n#######################################\r\n\r\nset-location $sourceDir\r\n\r\n\"Starting SQL 2008 Base Installation...\"\r\n$installCmd = \".\\setup.exe \/qs \/SQLSVCPASSWORD=\"\"$syncSvcAccountPassword\"\" \/AGTSVCPASSWORD=\"\"$syncSvcAccountPassword\"\" \/SAPWD=\"\"$saPassword\"\" \/ConfigurationFile=\"\"$configFile\"\"\"\r\n$installCmd\r\n\r\nInvoke-Expression $installCmd\r\n\r\nset-location $workDir<\/pre>\n
 <\/p>\n
Launching the Service Pack installation<\/strong><\/p>\n
The Service Pack installation has been a little more painful, normally would be simple but actually the powershell prompt is returned immediately after firing the command. So, to wait it, I’ve had to figure out the name of the process (is the executable name without the file extension .exe), get its process id and wait for that process:<\/p>\n
#######################################\r\n# Starting SQL 2008 SP Installation #\r\n#######################################\r\nif ( $servicePackExec) {\r\n    \"Starting Service Pack Installation...\"\r\n    $patchCmd = \"$servicePackExec \/Action=Patch \/Quiet \/IAcceptSQLServerLicenseTerms \/Instancename=\"\"$Instance\"\"\"\r\n    $patchCmd\r\n    Invoke-Expression $patchCmd\r\n\r\n    ## have to take the name of the process and wait for the completion of the pid because service packs\r\n    ## return prompt immediately and then run in background\r\n    $process=[System.IO.Path]::GetFileNameWithoutExtension($servicePackExec)\r\n    $nid = (Get-Process $process).id\r\n    Wait-Process -id $nid\r\n}<\/pre>\n
\u00a0Changing the TCP port<\/strong><\/p>\n
By default SQLServer starts listening on a dynamic port. If you have a default and you want to configure it without opening the configuration manager, you can do it with this snipplet that I’ve copied from sirSql (thank you for sharing this)<\/a>.<\/p>\n
 ###############################\r\n# change the TCP port at the end of the installation\r\nfunction changePort ($SQLName , $Instance, $port) {\r\n    Try\r\n        {\r\n\r\n    $SQLName\r\n    $Instance\r\n\r\n    # Load SMO Wmi.ManagedComputer assembly\r\n    [System.Reflection.Assembly]::LoadWithPartialName(\"Microsoft.SqlServer.SqlWmiManagement\") | out-null\r\n\r\n    Trap {\r\n        $err = $_.Exception\r\n        while ( $err.InnerException ) {\r\n            $err = $err.InnerException\r\n            write-output $err.Message\r\n        }\r\n        continue\r\n    }\r\n\r\n    # Connect to the instance using SMO\r\n    $m = New-Object ('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer') $SQLName\r\n    $urn = \"ManagedComputer[@Name='$SQLName']\/ServerInstance[@Name='$Instance']\/ServerProtocol[@Name='Tcp']\"\r\n    $Tcp = $m.GetSmoObject($urn)\r\n    $Enabled = $Tcp.IsEnabled\r\n    #Enable TCP\/IP if not enabled\r\n    IF (!$Enabled)\r\n        {$Tcp.IsEnabled = $true }\r\n\r\n    #Set to listen on 50000 and disable dynamic ports\r\n    $m.GetSmoObject($urn + \"\/IPAddress[@Name='IPAll']\").IPAddressProperties[1].Value = $port\r\n    $m.GetSmoObject($urn + \"\/IPAddress[@Name='IPAll']\").IPAddressProperties['TcpDynamicPorts'].Value = ''\r\n    $TCP.alter()\r\n\r\n        \"Success: SQL set to listen on TCP\/IP port $port. Please restart the SQL service for changes to take effect.\"\r\n    }\r\n    Catch { Write-Warning \"Unable to enable TCP\/IP & set SQL to listen on port $port\" }\r\n}\r\n\r\n####################\r\n# Changing TCPport #\r\n####################\r\n\r\n\"Changing TCP port to $port...\"\r\nchangePort $hostName $instance $port<\/pre>\n
Adding your stuff at the end<\/strong><\/p>\n
Having the installation completed is in midstream. After the installation you may want to add tempfiles to your installation, modify your model database, add default accounts.<\/p>\n
That’s up to you. If your scripts are identical you can execute them with sqlcmd.If you want to take benefit of the variables already set in the script you can execute them directly:<\/p>\n
###############################\r\n# Resizing \/ Adding Tempfiles #\r\n###############################\r\n\r\n$Connection = New-Object System.Data.SQLClient.SQLConnection\r\n$hostName = get-content env:computername\r\n\r\n$Connection.ConnectionString =\"Server=$hostName\\$instance;Database=master;uid=sa;Pwd=$saPassword;\"\r\n$Connection.Open()\r\n\r\n$Command = New-Object System.Data.SQLClient.SQLCommand\r\n$Command.Connection = $Connection\r\n\r\n$Command.CommandText = \"ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 1024MB, filegrowth = 64MB, maxsize=unlimited);\"\r\n$Command.ExecuteNonQuery() | out-null\r\n$Command.CommandText = \"ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 512MB, filegrowth = 64MB, maxsize=unlimited);\"\r\n$Command.ExecuteNonQuery() | out-null\r\n\r\n$Command.CommandText = \"ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = '\"+$tempDrive+\":\\MSSQL\\$instance\\MSSQL10_50.$instance\\MSSQL\\Data\\tempdb2.ndf', SIZE = 1024MB, filegrowth = 64MB, maxsize=unlimited);\"\r\n$Command.ExecuteNonQuery() | out-null\r\n$Command.CommandText = \"ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = '\"+$tempDrive+\":\\MSSQL\\$instance\\MSSQL10_50.$instance\\MSSQL\\Data\\tempdb3.ndf', SIZE = 1024MB, filegrowth = 64MB, maxsize=unlimited);\"\r\n$Command.ExecuteNonQuery() | out-null\r\n$Command.CommandText = \"ALTER DATABASE tempdb ADD FILE (NAME = tempdev4, FILENAME = '\"+$tempDrive+\":\\MSSQL\\$instance\\MSSQL10_50.$instance\\MSSQL\\Data\\tempdb4.ndf', SIZE = 1024MB, filegrowth = 64MB, maxsize=unlimited);\"\r\n$Command.ExecuteNonQuery() | out-null\r\n\r\n$Connection.Close()<\/pre>\n
Putting all together…
\n<\/strong><\/p>\n
Well, I’ll never paste again all the content here, you can download the script HERE<\/a>. Just change the file extension from .txt to .ps1.<\/p>\n
I know it’s not a rock-solid procedure but it works well for my purposes, feel free to comment or review my script. Just, if you do some improvement on it, please share it and back-link this post!<\/p>\n
Cheers<\/p>\n","protected":false},"excerpt":{"rendered":"
My first steps on Powershell Ok, Ok, as an “Oracle on Linux Certified Expert”, I’ve never been a great fan of SQLServer (I shouldn’t say this, I’m working on SQLServer since release 6.5…) and I’ve always hated the DOS command … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,132],"tags":[64,63,59,61,65,62,58,60,57],"class_list":["post-207","post","type-post","status-publish","format-standard","hentry","category-sqlserver","category-triblog","tag-64","tag-2008r2","tag-install","tag-powershell","tag-script","tag-service-pack","tag-silent","tag-sqlserver-2","tag-unattended"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/comments?post=207"}],"version-history":[{"count":19,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/207\/revisions"}],"predecessor-version":[{"id":227,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/posts\/207\/revisions\/227"}],"wp:attachment":[{"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/media?parent=207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/categories?post=207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ludovicocaldara.net\/dba\/wp-json\/wp\/v2\/tags?post=207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}