10 PowerShell scripts to configure SQL Server.

Previously in my blog I described following technics of administering SQL Server using PowerShell:
"How to Install and prepare Windows Core for SQL Server installation"
"Five Steps of Remote SQL Server installation using PowerShell"

In my most recent post I've started "Configuration" topic, by providing very important PowerShell script, which I want to use in this post post too: "Collect SQL Server Registry information using PowerShell"

In this post I want to talk about the biggest problem for SQL Server DBA, who works on Windows Core machine: Configuring the SQL Server.

If you ever touched SQL Server installed on Windows Core you probably faced the issue of not being able to change default port or add startup parameter because Microsoft did not provide you a tool to do that.
The tool everybody is using: "SQL Server Configuration Manager" does not work in Windows Core environment!!! Moreover, although it is designed as console application it does not easily allow you to connect to another instance. So, as SQL DBA you are doomed.


Now I want to change the thing. I'll give you the set of scripts to configure SQL Server in Core environment.

Script #0. Connect remotely to SQL Server.

At first, you have to run PowerShell as an administrator and run following command to connect to remotely to our Windows Core server:
PS> Enter-PSSession -ComputerName <ServerName> -Credential <Account Name>

Script #1. Get List of SQL Server Services.

The very first step on the server is to check for SQL Server services. Unless somebody completely renamed them you can get them by running following:

#Get SQL Server Services:
Get-WMIObject win32_service | Where-Object { $_.DisplayName+$_.Name -like "*SQL*" } | Sort-Object state,displayname | Format-Table -Autosize -Property State, Name, DisplayName, StartMode, PathName 

As the result you'll get something like this:

In my case I have two SQL Server Services:
Default instance: MSSQLSERVER - SQL Server (MSSQLSERVER)
Named Instance: MSSQL$MSI2016 - SQL Server (MSI2016)

Both instances are stopped because they are set for "Manual" Start Mode

Script #2. Stop/Start/Restart the service.

That is the most important script during the SQL Server configuration.
Stop-Start or Restart must be performed to apply any configured changes:

#Stop/Start/Restart SQL Server Service:
Stop-Service -displayname "SQL Server (MSI2016)"
Start-Service -displayname "SQL Server (MSI2016)"

Restart-Service -displayname "SQL Server (MSI2016)"

In this particular example I purposely used parameter "DisplayName" instead of "Name", which would be easier and more logical.
As you can see, the name of "named instance" is "MSSQL$MSI2016", which contains special PowerShell symbol "$". That special symbol requires extra handling. So, to make life easier I go with "DisplayName".

Script #3. Change Service Start Mode.

As you can see, my SQL Services are stopped after my server rebooted.
To make these services starting automatically after the server starts I have to change the StartMode to "Auto". The other options are "Manual" and "Disabled".

#Change SQL Server Startup mode:
$SQLService = "SQL Server (MSI2016)"
$NewMode = "Auto" # Options: Auto/Manual/Disabled
Set-Service -StartupType $NewMode -Name (Get-WMIObject win32_service | Where-Object { $_.DisplayName -eq $SQLService }).Name

Script #4. Get SQL Server Registry Instance Path.

That is the second most important configuration script. It searches for specific sub-folder in the registry, where SQL Server Instance information is located and stores it inside of "$SQLInstancePath" variable.

#Get SQL Server Instance Path:
$SQLService = "SQL Server (MSI2016)";
$SQLInstancePath = "";
$SQLServiceName = ((Get-Service | WHERE { $_.DisplayName -eq $SQLService }).Name).Trim();
If ($SQLServiceName.contains("`$")) { $SQLServiceName = $SQLServiceName.SubString($SQLServiceName.IndexOf("`$")+1,$SQLServiceName.Length-$SQLServiceName.IndexOf("`$")-1) }
foreach ($i in (get-itemproperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server").InstalledInstances)
{
  If ( ((Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$i).contains($SQLServiceName) )
  { $SQLInstancePath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\"+`
  (Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL").$i}
}
$SQLInstancePath

In my case the resulting string is:
HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSI2016
That is Registry Path to my "named instance" info.
That Path is stored inside of "$SQLInstancePath" variable. So, be careful and do not change this value during the following operations. To check its value you always can run its name as it was done in the last line of the script.

Script #5. Read SQL Server Error Log file.

That is extremely important troubleshooting script. When you start/restart the SQL Server service and it does not come up, you can run this script to see what was going on during the SQL Server startup and what was the problem (just note that value of "$SQLInstancePath" must be pre-set by previous script):

#Read SQL Server Error Log file:
$LogFile = (Get-ItemProperty "$SQLInstancePath\CPE").ErrorDumpDir + "ERRORLOG"
Get-Content "$LogFile" | more

Script #6. Enabling/Disabling Network properties:

I do not want to make life too complicated and included in this section only two basic properties: "ForceEncryption" and "HideInstance". If you going to the advanced you have the general idea:

# Enable/Disable protocol properties:
$Property = "HideInstance" # Options: ForceEncryption/HideInstance
$Enabled = "0"             # Options: "0" - Disabled / "1" - Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib").$Property
Set-ItemProperty -Path "$SQLInstancePath\MSSQLServer\SuperSocketNetLib" -Name "$Property" -Value $Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib").$Property

Script #7. Enabling/Disabling Network protocols.

In most cases you just need to have only TCP protocol enabled and all other disabled. So, use this script to control that situation and do not forget to restart SQL Server service to put the change into an effect:

#Enable/Disable network protocols:
$Protocol = "Tcp" # Options: Tcp/Np/Via/Sm
$Enabled = "1"     # Options: "0" - Disabled / "1" - Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\$Protocol").Enabled
Set-ItemProperty -Path "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\$Protocol" -Name "Enabled" -Value $Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\$Protocol").Enabled

Script #8. Changing "ListenOnAllIPs" TCP protocol property.

That script does a tiny, but very necessary change. By default "ListenOnAllIPs" option is enabled, but you might need to have it disabled on your very secured Core server:

# Enable/Disable ListenOnAllIPs TCP protocol property:
$Enabled = "0"    # Options: "0" - Disabled / "1" - Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\Tcp").ListenOnAllIPs
Set-ItemProperty -Path "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\Tcp" -Name "ListenOnAllIPs" -Value $Enabled
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\Tcp").ListenOnAllIPs

Script #9. Configuring TCP/IP protocols' properties.

That PowerShell script sets all parameters for one single TCP/IP protocol at the time. You have a choice to run it individually for all your TCP/IP protocols and for "IPALL", in case ListenOnAllIPs is enabled.
Will go through parameters in that script:
1. $IPProtocol. Possible options: "IPALL","IP1","IP2".... for all IP protocols in in your system.
2. $Enabled. That is easy: "0" - Disabled / "1" - Enabled
3. $Active. Same choice: "0" - Inactive / "1" - Active
4. $Port. Here you can specify a port. By default it 1433, but you can put there any value up to 64K. You also can put there "0"(zero) or just leave it blank. If you make a wrong call just see the ERRORLOG file for an error or SQL Server is listening to.
5. $DynamicPort. Not really sure if you can control this, but I'd suggest to keep it "0" or empty.
6. $IPAddress. The IP address that IP protocol will be listening in the system. The main rule here that IP address for each protocol MUST BE UNIQUE, otherwise you'll get duplication error during the SQL Server restart.
In this particular script I've made an example of settings for "IPALL" when ListenOnAllIPs is disabled:

# Set IP protocol's properties:
$IPProtocol="IPALL"   # Options: "IPALL"/"IP4"/"IP6"/Etc
$Enabled = "0"            # Options: "0" - Disabled / "1" - Enabled
$Active = "0"              # Options: "0" - Inactive / "1" - Active
$Port = ""                   # Options: "0"/"" (Empty)
$DynamicPort = "0"    # Options: "0"/"" (Empty)
$IPAddress="::0"        # There must not be IP Address duplication for any IP Protocol

$SQLTcpPath = "$SQLInstancePath\MSSQLServer\SuperSocketNetLib\Tcp"
Get-ChildItem $SQLTcpPath | ForEach-Object {Get-ItemProperty $_.pspath} `
| Format-Table -Autosize -Property @{N='IPProtocol';E={$_.PSChildName}}, Enabled, Active, TcpPort, TcpDynamicPorts, IpAddress

Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "Enabled" -Value $Enabled
Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "Active" -Value $Active
Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "TcpPort" -Value $Port
Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "TcpDynamicPorts" -Value $DynamicPort
Set-ItemProperty -Path "$SQLTcpPath\$IPProtocol" -Name "IPAddress" -Value $IPAddress

Get-ChildItem $SQLTcpPath | ForEach-Object {Get-ItemProperty $_.pspath} `
| Format-Table -Autosize -Property @{N='IPProtocol';E={$_.PSChildName}}, Enabled, Active, TcpPort, TcpDynamicPorts, IpAddress

Here is a typical output from this script:

Here are typical settings for configuring  "IP4":

$IPProtocol="IP4"  # Options: "IPALL"/"IP4"/"IP6"/Etc
$Enabled = "1"      # Options: "0" - Disabled / "1" - Enabled
$Active = "1"        # Options: "0" - Inactive / "1" - Active
$Port = "1433"      # Options: "0"/"" (Empty)
$DynamicPort = "0"  # Options: "0"/"" (Empty)
$IPAddress="10.10.1.10" # There must not be IP Address duplication for any IP Protocol

Script #10. Create/Read/Update/Delete SQL Server Startup Parameters

That is the most advance and dangerous script. If you do not know what you are doing do not touch it.

The general advise would be to do not touch "SQL Arguments 0-1-2":

# Read Existing SQL Server Startup Parameter
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\Parameters" | Select SQLArg*  | Format-List | Out-String ).trim() -replace "SQLArg","`tSQLArg"

#Create New SQL Server Startup Parameters
$ParamNumber = "3"
$ParamValue = "-T1117"
New-ItemProperty -Path "$SQLInstancePath\MSSQLServer\Parameters" -Name ("SQLArg$ParamNumber") -Value $ParamValue -PropertyType String -Force | Out-Null
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\Parameters" | Select SQLArg*  | Format-List | Out-String ).trim() -replace "SQLArg","`tSQLArg"

#Update SQL Server Startup Parameter
$ParamNumber = "3"
$ParamValue = "-T1118"
Set-ItemProperty -Path "$SQLInstancePath\MSSQLServer\Parameters" -Name ("SQLArg$ParamNumber") -Value $ParamValue
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\Parameters" | Select SQLArg*  | Format-List | Out-String ).trim() -replace "SQLArg","`tSQLArg"

#Delete SQL Server Startup Parameter
$ParamNumber = "3"
Remove-ItemProperty -Path "$SQLInstancePath\MSSQLServer\Parameters" -Name ("SQLArg$ParamNumber")
(Get-ItemProperty "$SQLInstancePath\MSSQLServer\Parameters" | Select SQLArg*  | Format-List | Out-String ).trim() -replace "SQLArg","`tSQLArg"

In the script I'm creating new parameter for setting trace flag "1117", then change it to a flag "1118" and then delete that parameter.


These 10 scripts do not cover ALL possible advanced features of SQL Server, but if you are smart enough to use these advanced features, I hope you will be able to modify these scripts for your task.

If you think some scripts are working incorrectly in your environment or you'd like me to cover some additional features please leave a comment.

And do not forget to use "Registry Collection Info Script" from my prior post. It will make your navigation much easier.