Restarting Remote SQL server
# This will be executed as the following
# ECHO "BASE64STRING==" | .\script.ps1; Clear-History
# Note1: Avoid using net commands to stop and start again the server
# net stop MSSQLSERVER && net start MSSQLSERVER
# sc stop MSSQLSERVER && sc start MSSQLSERVER
# These will not automatically start dependencies like
# SQL Server Agent service and SQL Server Browser service
# Note2: Powershell Restart-Service cmdlet handles all dependencies but runs on Windows only
# powershell -command "Restart-Service MSSQLSERVER -Force"
# After MSSQLSERVER starts - cmdlet starts all previously stopped dependencies.
# On Linux SQL Server comes with systemd unit files and can be controlled through systemctl
# sudo systemctl restart mssql-server
# sudo systemctl status mssql-server
# Note3: Make sure you are running commands as admin
# Make sure to restart all SQL Server named instances if required
# When running on a cluster, the SQL Server Database Engine service
# is best managed by using Cluster Administrator
begin {}
process {
# Stop if error. If not set, result value will be True and assumed there
# was no problems.
$ErrorActionPreference = "Stop"
# Executes once for each pipeline object
$JSON = [System.Text.Encoding]::UTF8.GetString([System.Convert]::FromBase64String($_))
$Params=($JSON | ConvertFrom-Json)
Write-Output "providerRecordUid=$($Params.providerRecordUid)"
Write-Output "resourceRecordUid=$($Params.resourceRecordUid)"
Write-Output "userRecordUid=$($Params.userRecordUid)"
Write-Output "newPassword=$($Params.newPassword)"
Write-Output "oldPassword=$($Params.oldPassword)"
Write-Output "user=$($Params.user)"
# SQL Server Instances that will be restarted (in list order)
[string[]]$Instances = 'MSSQLSERVER', 'MSSQL$instance1', 'MSSQL$instance2'
# Restart service(s) on remote machine
# To connect to a remote computer, you must be a member of the Administrators group
# on the remote computer. To start an interactive session on the local computer,
# you must start PowerShell with the Run as administrator option.
# Note: In newer versions of PowerShell -ComputerName was removed from *-Service cmdlets
# ex. Get-Service MSSQLSERVER -ComputerName MyServer | Restart-Service - no longer works
# Note: Enter-PSSession shouldn't be used in scripts (runs script on localhost)
# Prepare PSCredential parameter
[string]$userName = 'MyUserName'
[string]$userPass = 'MySuperSecureToken'
[securestring]$secStringPass = ConvertTo-SecureString $userPass -AsPlainText -Force
[pscredential]$credObject = New-Object System.Management.Automation.PSCredential ($userName, $secStringPass)
[string]$hostname = 'Server01'
foreach ($Instance in $Instances) {
Invoke-Command -ComputerName $hostname -Credential $credObject -ScriptBlock { Restart-Service -Name $Using:Instance -Force }
}
}
end {}
Last updated