Change Sql Service Account Password with no Restart

On SINGLE INSTANCE (apparently this does not work correctly with clusters…will test and update…) Sql Servers use the following code to update the password for the service account. This will not require a restart of the service. Curiously…the call to $service.ChangePassword is supposed to take the old password as the first parameter and the new password as the second parameter…but in my haste I didn’t notice this at first and was just passing in the new password for both parameters, yet everything seems to work just fine this way. I bingled around to try to see if anyone else had noticed this with no luck at all…

  $password = 'P@55s0rd!' 

 icm -ComputerName ComputerName -ScriptBlock{
    
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
    try{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $env:COMPUTERNAME
     
        $service = $srv.Services | Where-Object{$_.name -eq 'sqlserveragent'}
        $service.ChangePassword($using:password, $using:password)
        $service.Alter()
 
        $service = $srv.Services | Where-Object{$_.name -eq 'mssqlserver'}
        $service.ChangePassword($using:password, $using:password)
        $service.Alter()
    }
    catch{
        Write-Warning ($_ | fl -Force | Out-String)
        throw $_ 
    }


 }

 

Set Sql Server\Agent Service Account

This uses a CIMMethod to change the service accounts. The Wmi.ManagedComputer method is also included in the comments below…but I’ve found this to be not reliable at times.

 cls


[securestring]$mssqlpwd = ConvertTo-SecureString "yomamma" -AsPlainText -Force
[PSCredential]$mssqlCred = New-Object System.Management.Automation.PSCredential ("$env:userdomain\sqlserver", $mssqlpwd)

[securestring]$agentPwd = ConvertTo-SecureString "wearsarmyboots" -AsPlainText -Force
[PSCredential]$agentCred = New-Object System.Management.Automation.PSCredential ("$env:userdomain\sqlagent", $agentPwd)

Invoke-Command -ComputerName 'ComputerName' -ScriptBlock{

    $mssqlCred = $using:mssqlCred 
    $agentCred = $using:agentCred

    try{

        $ss = Get-CimInstance Win32_Service -Filter 'Name="sqlserveragent"'
        $ss | Invoke-CimMethod -MethodName Change -Arguments @{StartName=$agentCred.UserName;StartPassword=$agentCred.GetNetworkCredential().Password}

        $ss = Get-CimInstance Win32_Service -Filter 'Name="mssqlserver"'
        $ss | Invoke-CimMethod -MethodName Change -Arguments @{StartName=$mssqlCred.UserName;StartPassword=$mssqlCred.GetNetworkCredential().Password}

        <#
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement") | Out-Null
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer $env:COMPUTERNAME
    
        $service = $srv.Services | Where-Object{$_.name -eq 'sqlserveragent'}
        $service.SetServiceAccount($agentCred.UserName, $agentCred.GetNetworkCredential().Password);
        Restart-Service -name sqlserveragent -Force

        $service = $srv.Services | Where-Object{$_.name -eq 'mssqlserver'}
        $service.SetServiceAccount($mssqlCred.UserName, $mssqlCred.GetNetworkCredential().Password);
        Restart-Service -name mssqlserver -Force
        #>
    }
    catch{
        throw $_ | fl -Force | Out-String
    }

    Get-Service mssqlserver | Start-Service 
    Get-Service sqlserveragent | Start-Service 


} 

Enable Sql AlwaysOn

This always seems to give me trouble, and I always forget the manner in which I run this that works correctly, so, viola!

Invoke-Command -ComputerName $name -ScriptBlock{
        Import-Module SqlPS -DisableNameChecking

        $srv = New-Object Microsoft.SqlServer.Management.smo.Server "."
        Enable-SqlAlwaysOn -InputObject $srv -Force
        $srv.ConnectionContext.Disconnect()
    }

Restore Database with SMO

Probably 3 or 4 versions of this already on here.  So here is one more.  

function Invoke-Restore{
    [cmdletbinding()]
    param(
        [Parameter(Mandatory)]
        [string]$ServerName,
        [Parameter(Mandatory)]
        [int]$Port,
        [string]$DatabaseName = $null,
        [string]$BackupFile = "c:\PathToBackupFile.bak",
        [string]$dataDir = "d:\mssql\data",
        [string]$logDir = "d:\mssql\tranlog",
        [switch]$Force
    )

    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")
    [void][reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended")
 

    try{
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server "$ServerName,$Port"
        $backup = New-Object Microsoft.SqlServer.Management.Smo.BackupDeviceItem ($BackupFile, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
         
        $restore = New-Object Microsoft.SqlServer.Management.Smo.Restore
        $restore.Devices.Add($backup);
        $restore.NoRecovery = $false;
        $restore.ReplaceDatabase = $true
 
        $dbFiles = $restore.ReadFileList($srv);
        $header = $restore.ReadBackupHeader($srv)
        if([System.String]::IsNullOrWhiteSpace($DatabaseName)){
            $DatabaseName = $header.DatabaseName
        }
        $restore.Database = $DatabaseName
        if($Force -and $srv.Databases[$DatabaseName]){
            $srv.KillDatabase($DatabaseName);
        }

        foreach($dbFile in $dbFiles){
            $fileName = [System.IO.Path]::GetFileName($dbFile.PhysicalName);
            switch($dbFile.Type){
                'D'{
                    [void]$restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "$dataDir\$fileName")))
                }
                'L'{
                    [void]$restore.RelocateFiles.Add((New-Object Microsoft.SqlServer.Management.Smo.RelocateFile($dbFile.LogicalName, "$logDir\$fileName")))
                }
            }
        }
    
        $srv.ConnectionContext.StatementTimeout = 0;
        $restore.SqlRestore($srv);
        $srv.ConnectionContext.Disconnect();

    }
    catch{
        $_ | fl -Force
    }

}

Add an AvailabilityDatabase

function Add-AvailabilityGroupDatabase{
    param(
        $ServerName,
        $Port = 1433,
        $DatabaseName,
        $AvailabilityGroup
    )
    begin{

    }
    process{

        if(!$ServerName.Contains('.')){
            $ServerName = "$ServerName.$env:USERDNSDOMAIN"
        }
        $srv = new-object Microsoft.SqlServer.Management.smo.Server "$ServerName,$Port"
        $ServerName = $srv.AvailabilityGroups[$AvailabilityGroup].PrimaryReplicaServerName
        $srv.ConnectionContext.Disconnect()
        $srv = new-object Microsoft.SqlServer.Management.smo.Server "$($ServerName).$env:USERDNSDOMAIN,$Port"

        if(!$srv.Databases[$DatabaseName]){
            throw "Could not find a database $DatabaseName on primary replica $ServerName"
        }

        if(!$srv.AvailabilityGroups[$AvailabilityGroup].AvailabilityDatabases.Contains($DatabaseName)){
            [void]$srv.ConnectionContext.ExecuteNonQuery("RESTORE DATABASE [$DatabaseName] WITH RECOVERY;");
            $ADB = New-Object Microsoft.SqlServer.Management.smo.AvailabilityDatabase($srv.AvailabilityGroups[$AvailabilityGroup], $DatabaseName);
            $ADB.Create();
        }
        else{
            Write-Warning "Database $DatabaseName is already an availbility database on server $ServerName."
        }
        
        
        $srv.AvailabilityGroups[$AvailabilityGroup].AvailabilityReplicas | ?{$_.Name -ne $ServerName} | select -expandproperty Name | %{
            $name = $_ 
            try{
                $sec = new-object Microsoft.SqlServer.Management.smo.Server "$($name).$env:USERDNSDOMAIN,$Port"
                $ag = $sec.AvailabilityGroups[$AvailabilityGroup]
                if(!$ag.AvailabilityDatabases[$DatabaseName].IsJoined){
                    $ag.AvailabilityDatabases[$DatabaseName].JoinAvailablityGroup();
                }
                else{
                    Write-Warning "Database $DatabaseName is already an availbility database on server $name."
                }
            }
            catch{
                Write-Error $_ | fl -Force
            }
            finally{
                $sec.ConnectionContext.Disconnect();
            }
        }


        $srv.ConnectionContext.Disconnect()

    }
    end{

    }
}

Capture Sql Server Information Messages in Powershell

Sometimes you have a need to capture the informational messages generated in sql server, for example sp_help_revlogin only prints the user information to the messages tab.

Import-Module SqlServer -DisableNameChecking
cls
try {

$users = @('user1', 'user2')

$users | % {
$user = $_

$sqlConn = New-Object System.Data.SqlClient.SqlConnection "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=servername"
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {
param(
$sender,
$event
)

Write-host $event.Message

};
$sqlConn.add_InfoMessage($handler);
$sqlConn.FireInfoMessageEventOnUserErrors = $true;
$sqlConn.Open();
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand("EXEC sp_help_revlogin '$user', NULL", $sqlConn);
$sqlcmd.CommandType = [System.Data.CommandType]::Text
[void]$sqlCmd.ExecuteNonQuery();
}

}
catch {
$_ | fl -Force
}
finally {
$sqlConn.Close();
}

Get Index Key Length via Powershell

Yep.

function Get-IndexKeyWidth{
    param(
        [string]$ServerName,
        [int]$Port = 2080,
        [string]$DatabaseName,
        [string]$TableName
    )
    begin{

    }
    process{
        $objects = New-Object System.Collections.Generic.List[object]
        $indexes = Invoke-Sqlcmd -ServerInstance "$ServerName,$Port" -Database $DatabaseName -Query "EXEC sp_helpindex [$TableName]" 
        $indexes | %{
            $sb = New-Object System.Text.StringBuilder
            $indexName = $_.index_name
            $_.index_keys -split ',' | %{
                [void]$sb.Append("'$($_.ToString().Trim())',");
            }
            $cmd = "SELECT SUM(max_length)AS TotalIndexKeySize
                    FROM sys.columns
                    WHERE name IN ($($sb.ToString().Substring(0, $sb.ToString().Length-1)))
                    AND object_id = OBJECT_ID(N'$TableName');"
                    $cmd
            Invoke-Sqlcmd -ServerInstance "$ServerName,$Port" -Database $DatabaseName -Query $cmd | %{
                [void]$objects.Add([PSCustomObject]@{
                    ServerName = $ServerName
                    DatabaseName = $DatabaseName 
                    TableName = $TableName 
                    IndexName = $indexName
                    SizeBytes = $_.TotalIndexKeySize 
                });
            }
        }
        $objects 
    }
    end{

    }
}