Automate SQL Server 2017 Installation using PowerShell

Powershell script to install SQL Server 2017 and latest Cummulative update CU23 , Also you can install SQL Server 2017 Reporting Services and SQL Server Management Studio(SSMS) by using this powershell script

This powershell script helps to automate SQL Server 2017 Installation .
Here we need to change the paramters baed on your enevironment installation default or named instance
Parameters need to be edited between the lines 15 to 80 before running the Script .

As SQL Server media does not include SQL Server Management studio and Reporting Services inbuilt,
MS provided option to download it as a separate , this PowerShell script is capable to take care of it .
This script can download the SSRS and SSMS .exe files from provided download link in the script ( this link can be changed with new updated link as well )
If you have already downloaded .exe files of SSMS and SSRS it can install from the provided folder path and it will not download from internet.

Instruction to run the script :

  1. Download the file SQLServer2017install.ps1
  2. Open the downloaded file in notepad , edit the parameters between the lines from 15 to 80 before running the script.
  3. After changes copy the script, Open Windows PowerShell ISE with Run As Administrator , past the copied script and click RUN.
  4. You can also do necessary changes in the script.

PowerShell script below :

<#
# Install SQL Server 2017
# 
# This script:            Installs SQL Server 2017, CU5, SSMS and RS
# Before running:         Edit the variables as necessary (lines 15-80). Copy the SQL Service Management Studio (SSMS-Setup-ENU.exe) and Reporting Services (SQLServerReportingServices.exe) files to $folderpath\ in advance if you don't want the script to download the exe's
# Usage:                  Run this script on the ConfigMgr Primary Server as a user with local Administrative permissions on the server
#>
  If (-NOT ([Security.Principal.WindowsPrincipal] [Security.Principal.WindowsIdentity]::GetCurrent()).IsInRole(`
        [Security.Principal.WindowsBuiltInRole] “Administrator”))

    {
        Write-Warning “You do not have Administrator rights to run this script!`nPlease re-run this script as an Administrator!”
        Break
    }

# below variables are customizable
$folderpath="E:\Software\SQLServer2017\SQLServer2017-x64-ENU-Dev"
$inifile="$folderpath\ConfigurationFile.ini"
# next line sets user as a SQL sysadmin
$yourusername="DOMAIN\USERNAME"
# path to the SQL media
$SQLsource="E:\Software\SQLServer2017\SQLServer2017-x64-ENU-Dev"
$SQLInstallDrive = "D:"
# SQL memory
$SqlMemMin = 8192
$SqlMemMax = 8192
# configurationfile.ini settings https://msdn.microsoft.com/en-us/library/ms144259.aspx
$ACTION="Install"
$ASCOLLATION="Latin1_General_CI_AS"
$ErrorReporting="False"
$SUPPRESSPRIVACYSTATEMENTNOTICE="False"
$IACCEPTROPENLICENSETERMS="False"
$ENU="True"
$QUIET="True"
$QUIETSIMPLE="False"
$UpdateEnabled="True"
$USEMICROSOFTUPDATE="False"
$FEATURES="SQLENGINE,RS,CONN,IS,BC,SDK"
$UpdateSource="MU"
$HELP="False"
$INDICATEPROGRESS="False"
$X86="False"
$INSTANCENAME="MSSQLSERVER"
$INSTALLSHAREDDIR="$SQLInstallDrive\Program Files\Microsoft SQL Server"
$INSTALLSHAREDWOWDIR="$SQLInstallDrive\Program Files (x86)\Microsoft SQL Server"
$INSTANCEID="MSSQLSERVER"
$RSINSTALLMODE="DefaultNativeMode"
$SQLTELSVCACCT="NT Service\SQLTELEMETRY"
$SQLTELSVCSTARTUPTYPE="Automatic"
$ISTELSVCSTARTUPTYPE="Automatic"
$ISTELSVCACCT="NT Service\SSISTELEMETRY130"
$INSTANCEDIR="$SQLInstallDrive\Program Files\Microsoft SQL Server"
$AGTSVCACCOUNT="NT AUTHORITY\SYSTEM"
$AGTSVCSTARTUPTYPE="Automatic"
$ISSVCSTARTUPTYPE="Disabled"
$ISSVCACCOUNT="NT AUTHORITY\System"
$COMMFABRICPORT="0"
$COMMFABRICNETWORKLEVEL="0"
$COMMFABRICENCRYPTION="0"
$MATRIXCMBRICKCOMMPORT="0"
$SQLSVCSTARTUPTYPE="Automatic"
$FILESTREAMLEVEL="0"
$ENABLERANU="False"
$SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"
$SQLSVCACCOUNT="NT AUTHORITY\System"
$SQLSVCINSTANTFILEINIT="False"
$SQLSYSADMINACCOUNTS="$yourusername"
$SQLTEMPDBFILECOUNT="1"
$SQLTEMPDBFILESIZE="8"
$SQLTEMPDBFILEGROWTH="64"
$SQLTEMPDBLOGFILESIZE="8"
$SQLTEMPDBLOGFILEGROWTH="64"
$ADDCURRENTUSERASSQLADMIN="True"
$TCPENABLED="1"
$NPENABLED="1"
$BROWSERSVCSTARTUPTYPE="Disabled"
$RSSVCACCOUNT="NT AUTHORITY\System"
$RSSVCSTARTUPTYPE="Automatic"
$IAcceptSQLServerLicenseTerms="True"

# do not edit below this line

$conffile= @"
[OPTIONS]
Action="$ACTION"
ErrorReporting="$ERRORREPORTING"
Quiet="$Quiet"
Features="$FEATURES"
InstanceName="$INSTANCENAME"
InstanceDir="$INSTANCEDIR"
SQLSVCAccount="$SQLSVCACCOUNT"
SQLSysAdminAccounts="$SQLSYSADMINACCOUNTS"
SQLSVCStartupType="$SQLSVCSTARTUPTYPE"
AGTSVCACCOUNT="$AGTSVCACCOUNT"
AGTSVCSTARTUPTYPE="$AGTSVCSTARTUPTYPE"
RSSVCACCOUNT="$RSSVCACCOUNT"
RSSVCSTARTUPTYPE="$RSSVCSTARTUPTYPE"
ISSVCACCOUNT="$ISSVCACCOUNT" 
ISSVCSTARTUPTYPE="$ISSVCSTARTUPTYPE"
ASCOLLATION="$ASCOLLATION"
SQLCOLLATION="$SQLCOLLATION"
TCPENABLED="$TCPENABLED"
NPENABLED="$NPENABLED"
IAcceptSQLServerLicenseTerms="$IAcceptSQLServerLicenseTerms"
"@


# Check for Script Directory & file
if (Test-Path "$folderpath"){
 write-host "The folder '$folderpath' already exists, will not recreate it."
 } else {
mkdir "$folderpath"
}
if (Test-Path "$folderpath\ConfigurationFile.ini"){
 write-host "The file '$folderpath\ConfigurationFile.ini' already exists, removing..."
 Remove-Item -Path "$folderpath\ConfigurationFile.ini" -Force
 } else {

}
# Create file:
write-host "Creating '$folderpath\ConfigurationFile.ini'..."
New-Item -Path "$folderpath\ConfigurationFile.ini" -ItemType File -Value $Conffile

# Configure Firewall settings for SQL

write-host "Configuring SQL Server 2017 Firewall settings..."

#Enable SQL Server Ports

New-NetFirewallRule -DisplayName "SQL Server" -Direction Inbound –Protocol TCP –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName "SQL Admin Connection" -Direction Inbound –Protocol TCP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName "SQL Database Management" -Direction Inbound –Protocol UDP –LocalPort 1434 -Action allow
New-NetFirewallRule -DisplayName "SQL Service Broker" -Direction Inbound –Protocol TCP –LocalPort 4022 -Action allow
New-NetFirewallRule -DisplayName "SQL Debugger/RPC" -Direction Inbound –Protocol TCP –LocalPort 135 -Action allow

#Enable SQL Analysis Ports

New-NetFirewallRule -DisplayName "SQL Analysis Services" -Direction Inbound –Protocol TCP –LocalPort 2383 -Action allow
New-NetFirewallRule -DisplayName "SQL Browser" -Direction Inbound –Protocol TCP –LocalPort 2382 -Action allow

#Enabling related Applications

New-NetFirewallRule -DisplayName "HTTP" -Direction Inbound –Protocol TCP –LocalPort 80 -Action allow
New-NetFirewallRule -DisplayName "SQL Server Browse Button Service" -Direction Inbound –Protocol UDP –LocalPort 1433 -Action allow
New-NetFirewallRule -DisplayName "SSL" -Direction Inbound –Protocol TCP –LocalPort 443 -Action allow

#Enable Windows Firewall
Set-NetFirewallProfile -DefaultInboundAction Block -DefaultOutboundAction Allow -NotifyOnListen True -AllowUnicastResponseToMulticast True

Write-Host "done!" -ForegroundColor Green

# start the SQL installer
Try
{
if (Test-Path $SQLsource){
 write-host "about to install SQL Server 2017..." -nonewline
$fileExe =  "$SQLsource\setup.exe"
$CONFIGURATIONFILE = "$folderpath\ConfigurationFile.ini"
& $fileExe  /CONFIGURATIONFILE=$CONFIGURATIONFILE
Write-Host "done!" -ForegroundColor Green
 } else {
write-host "Could not find the media for SQL Server 2017..."
break
}}
catch
{write-host "Something went wrong with the installation of SQL Server 2017, aborting."
break}

# start the SQL Server 2017 CU5 downloader
$filepath="$folderpath\SQLServer2017-KB5000685-x64.exe"
if (!(Test-Path $filepath)){
write-host "Downloading SQL Server 2017 CU5..." -nonewline
$URL = "https://download.microsoft.com/download/C/4/F/C4F908C9-98ED-4E5F-88D5-7D6A5004AEBD/SQLServer2017-KB5000685-x64.exe"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "done!" -ForegroundColor Green
}
 else {
write-host "found the SQL Server 2017 CU5 Installer, no need to download it..."
}
# start the SQL Server 2017 CU5 installer
write-host "about to install SQL Server 2017 CU5..." -nonewline
$Parms = " /quiet /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances"
$Prms = $Parms.Split(" ")
& "$filepath" $Prms | Out-Null
Write-Host "done!" -ForegroundColor Green

# start the SQL SSMS downloader
$filepath="$folderpath\SSMS-Setup-ENU.exe"
if (!(Test-Path $filepath)){
write-host "Downloading SQL Server 2017 SSMS..." -nonewline
$URL = "https://go.microsoft.com/fwlink/?linkid=870039"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "done!" -ForegroundColor Green
}
 else {
write-host "found the SQL SSMS Installer, no need to download it..."
}
# start the SQL SSMS installer
write-host "about to install SQL Server 2017 SSMS..." -nonewline
$Parms = " /Install /Quiet /Norestart /Logs SQLServerSSMSlog.txt"
$Prms = $Parms.Split(" ")
& "$filepath" $Prms | Out-Null
Write-Host "done!" -ForegroundColor Green


# start the SQL RS downloader
$filepath="$folderpath\SQLServerReportingServices.exe"
if (!(Test-Path $filepath)){
write-host "Downloading SQL Server 2017 Reporting Services..." -nonewline
$URL = "https://download.microsoft.com/download/E/6/4/E6477A2A-9B58-40F7-8AD6-62BB8491EA78/SQLServerReportingServices.exe"
$clnt = New-Object System.Net.WebClient
$clnt.DownloadFile($url,$filepath)
Write-Host "done!" -ForegroundColor Green
}
 else {
write-host "found the SQL RS Installer, no need to download it..."
}
# start the SQL RS installer
write-host "about to install SQL Server 2017 Reporting Services..." -nonewline
$Parms = "  /IAcceptLicenseTerms True /Quiet /Norestart /Log SQLServerReportingServiceslog.txt"
$Prms = $Parms.Split(" ")
& "$filepath" $Prms | Out-Null
Write-Host "done!" -ForegroundColor Green

# Configure SQL memory
write-host "Configuring SQL memory..." -nonewline

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.VisualBasic') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null
$SQLMemory = New-Object ('Microsoft.SqlServer.Management.Smo.Server') ("(local)")
$SQLMemory.Configuration.MinServerMemory.ConfigValue = $SQLMemMin
$SQLMemory.Configuration.MaxServerMemory.ConfigValue = $SQLMemMax
$SQLMemory.Configuration.Alter()
Write-Host "done!" -ForegroundColor Green
write-host ""

# exit script
write-host "Exiting script, goodbye."

Above script output is like below :

PowerShell script to Deploy Ola Hallengren for Multiple servers

Powershell script to deploy ola hallegren Maintenance Solution Script,jobs creation,job schedules and the reQuired brentozar store procedures with single click.

But before doing that we need to do some manual work as follows:

Dependendencies : Sysadmin access on the instances you wish to apply the solution to

Step 1     : Prepare a script for creating DBA database to store helpful stored procedures    
             and maintenance logging
Step 2     : Download latest scripts to Create the following stored procedures in the DBA 
             database which are very helpful
                - Sp_whoisactive: https://github.com/amachanic/sp_whoisactive/
                - Sp_Blitz: http://www.brentozar.com/blitz/
                - Sp_BlitzIndex: http://www.brentozar.com/blitzindex/
                - Sp_BlitzCache: http://www.brentozar.com/blitzcache/
                - Sp_AskBrent: http://www.brentozar.com/askbrent/
Step 3     : Download Latest script to Create Ola Hallengren Maintenance procedures: 
             https://ola.hallengren.com/
Step 4     : Prepare to Create the maintenance jobs for backups, DBCC Check DBs, Index  
             Maintenance and maintenance cleanup 
Step 5     : Schedule the maintenance jobs created in step 2
Step 6     : Create CSV file and enter your Instance Names on each line,save the file and 
             close.

I have collected all the scripts and kept in my local c:\temp\ola folder

I have downloaded all the scripts and kept in folders like below :

Once Instance Names are enter into the CSV file , we can deploy this ola hallegren Maintenance Solution script with stored procs, jobs and job schedules to all the servers with singe click by executing a PowerShell script ‘ Deploy-SQLMaintenance.ps1

I deployed to one Instance by executing the script and the output looks like below :

I have created stored procedures in a MASTER Database

I initiated running Maintenance jobs and checked ‘ command Log ‘Table , by Querying this table we can see all the job data where it stores the job information like the job start time and end time ..e.t.c.,

To change the Job Schedules , go to Schedules Folder and you can see here 3 files with names prod, dev, test environment which has schedule information for all 3 environments.

You can open the file and change the schedules of jobs as per your environment :

Also you can change the Database Name where these all needs to get stored by opening the file ‘ Database Creation

To change in Job creation file , pls see below :

Find the entire script in below Drive link :

https://drive.google.com/drive/u/3/folders/1bGHvfW0KutOtmNLznL8xWEzJtF7Z4pp_

Export SSRS Report Inventory to CSV files

Today when I am preparing a documentation i.e., pulling DB Instance reports which includes Databases Info, , Configurations, security, linked servers, settings ..e.t.c., to CSV files for SQL server Instance Migration which is scheduled for tonight ,which has reporting services installed and there is a request from customer to pull all SSRS reports to CSV files with Fields as SSRSServerName, ReportName, Connection details, Datasource details, DataSourcepath details

To achieve this we have methods to do but I wanted to do this using PowerShell script which is simple where we need to provide parameters of Reporting Services Server Name and provide file path where CSV file has to be saved.

I have just gone through and searched my old script repository which I already has this PowerShell script saved , We need to thank the Author for this script .

Please find the PowerShell script below :

CLS
$SSRS2012Server = “DESKTOP-02JIB76” # Provide your SSRS Server
$ReportPathMatch = “*” # you can also specify certain reports, * for all
$file = “C:\temp\SSRS_Inventory.csv” # Provide the path to create CSV file

#URL to the web service
$SSRSurl = “http://{0}/ReportServer/ReportService2010.asmx” -f $SSRS2012Server
# Get a handle on the service
$SSRS = New-WebServiceProxy -uri $SSRSurl -UseDefaultCredential;
# Get a collection of the reports on the server
$itemlist = $SSRS.ListChildren(“/”, $true) | Where-Object {$_.TypeName -eq “Report” -and $_.Path -like $ReportPathMatch -and $_.Path -notlike “/Users Folders*”} ;
Write-Host “Reports to review” $itemlist.Count
#Creating a report tracker
$Reports =@()
#Creates a report tracking object
function Add-Reports
{
param([string]$SSRSServer, [string]$reportPath, [string]$connString, [string]$dataSourceType , [string]$dataSourcePath)
$report = New-Object System.Object
$report | Add-Member -type NoteProperty -name “SSRSServer” -value $SSRSServer
$report | Add-Member -type NoteProperty -name “Report” -value $reportPath
$report | Add-Member -type NoteProperty -name “Connection” -value $connString
$report | Add-Member -type NoteProperty -name “DataSourceType” -value $dataSourceType
$report | Add-Member -type NoteProperty -name “DataSourcePath” -value $dataSourcePath
return $report
}

$SSRSServer = $SSRS2012Server

#Loop Through Each Report
Foreach($item in $itemlist)
{
Write-Host “Checking ” $item.Path
try
{
$dsList = $null #clearing it out explicitly
#Any given report may have multiple data sources, grab the collection
$dsList = $ssrs.GetItemDataSources($item.Path)
#Loop through each DS in the report
for($i=0;$i -le $dsList.Length-1;$i++)
{
$ds = $dsList[$i];
#If the DS has a reference it would appear to be a shared datasource
if($ds.Item.Reference -ne $null)
{
$dataSource = $ssrs.GetDataSourceContents($ds.Item.Reference);
$Reports += Add-Reports -SSRSServer $SSRSServer -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType “Shared” -dataSourcePath $ds.Item.Reference
}
else #embedded datasource
{
$dataSource = $ds.Item
$Reports += Add-Reports -SSRSServer $SSRSServer -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType “Embedded” -dataSourcePath “”
}
} #for each datasource
}
catch{
Write-Host “Error on ” $item.Path $Error[0]
$Error.Clear();
}
} # Foreach Report
$Reports | Export-Csv $file -Force

Output from above script will get like below :

And the above all 31 Reports will be exported to CSV file where File path has given like below :

AlwaysON Lease Timeout or Expired Error

Recently performed side-by-side migration from SQL Server 2008R2 Standalone databases(30 DB’s) to SQL Server 2016 by building new Windows Serever 2016 operating systems and also configured Alwayson( 2 replicas) for SQL 2016 servers with multiple Availability Groups.

Application team has modified their connection string to connect with ‘ ListnerName ‘ and tested the application connectivity. App team confirmed everything is working as expected and all good.

After 1 week got a call from application team(middle of the night) that they are having connectivity Issues and trouble connecting to databases.

When I logged in and checked the database server that the Primary Availability Groups alwayson Dashboard looks HEATHY and GREEN . All Databases are ONLINE and SYNCHRONIZED.

Checked if any connections are hitting to Databases using sys.sysprocesses and seen Application coonection are coming to database server.

So, informed the same to application team and they replied saying they have restarted their application services and then database connectivity is working fine.

I opened SQL Server Error Log and seen below error messages :

Message

SQL Server hosting availability group ‘XXX’ did not receive a process event signal from the Windows Server Failover Cluster within the lease timeout period.

Message

Error: 19421, Severity: 16, State: 1.

Message

The lease between availability group ‘XXX’ and the Windows Server Failover Cluster has expired. A connectivity issue occurred between the instance of SQL Server and the Windows Server Failover Cluster. To determine whether the availability group is failing over correctly, check the corresponding availability group resource in the Windows Server Failover Cluster.

Message

Error: 19407, Severity: 16, State: 1.

Message

The state of the local availability replica in availability group ‘XXX’ has changed from ‘PRIMARY_NORMAL’ to ‘RESOLVING_NORMAL’. The state changed because the lease between the local availability replica and Windows Server Failover Clustering (WSFC) has expired. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

Message

Always On Availability Groups connection with secondary database terminated for primary database ‘XXX’ on the availability replica ‘XXXXX’ with Replica ID: {95d2878a-3fe0-48bf-aa2a-a41805fda786}. This is an informational message only. No user action is required.

Message

Remote harden of transaction ‘user_transaction’ (ID 0x000000002af6b5b4 0001:99ea0a50) started at Apr 17 2021 1:46AM in database ‘XXX’ at LSN (7544210:90297:16) failed.

Message

The availability group database “XXX” is changing roles from “PRIMARY” to “RESOLVING” because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.

Message

State information for database ‘XXX’ – Hardened Lsn: ‘(7544210:90297:1)’ Commit LSN: ‘(7544210:89962:3)’ Commit Time: ‘Apr 17 2021 1:45AM’

Message

Stopped listening on virtual network name ‘ListnerNAme’. No user action is required.

Message

Process ID 250 was killed by an ABORT_AFTER_WAIT = BLOCKERS DDL statement on database_id = 16, object_id = 0.

Message

Always On: Explicitly transitioning the state of the Windows Server Failover Clustering (WSFC) resource that corresponds to availability group ‘XXX’ to Failed. The resource state is not consistent with the availability group state in the instance of SQL Server. The WSFC resource state indicates that the local availability replica is the primary replica, but the local replica is not in the primary role. This is an informational message only. No user action is required.

Message

Always On: The local replica of availability group ‘XXXX’ is preparing to transition to the resolving role in response to a request from the Windows Server Failover Clustering (WSFC) cluster. This is an informational message only. No user action is required.

Message

Unable to access availability database ‘DBNAME’ because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later.

Message

Always On: The local replica of availability group ‘XXXX’ is preparing to transition to the primary role in response to a request from the Windows Server Failover Clustering (WSFC) cluster. This is an informational message only. No user action is required.

Message

The state of the local availability replica in availability group ‘XXXX’ has changed from ‘RESOLVING_NORMAL’ to ‘PRIMARY_PENDING’. The state changed because the availability group is coming online. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

Message

Started listening on virtual network name ‘ListnerName’. No user action is required.

Message

The lease worker of availability group ‘XXXXX’ is now sleeping the excess lease time (164797 ms) supplied during online. This is an informational message only. No user action is required.

Message

The state of the local availability replica in availability group ‘XXXX’ has changed from ‘PRIMARY_PENDING’ to ‘PRIMARY_NORMAL’. The state changed because the local replica has completed processing Online command from Windows Server Failover Clustering (WSFC). For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

Message

A connection for availability group ‘XXXX’ from availability replica ‘InstanceName’ with id [3DB08082-C6D9-47E6-B53A-CCC8B15C21D2] to ‘AU2106CP0644\WDCP01’ with id [95D2878A-3FE0-48BF-AA2A-A41805FDA786] has been successfully established. This is an informational message only. No user action is required.

From SQL Server Error log , I could see the issue occurred because of Lease Time Out which was happened on Primary replica as it controls the mechanism of leae timeout .
This could occur due to high resource utilization on Database server( CPU 100% Usage), at this time SQL Server may go to hung or not responding state for sometime. This could have been resulted to Lease timout or expired.

To find out CPU Usage follow below steps to set it in Perfmon

After creating Data collection and clicked Start , leave it for running until you get lease timeout error or the issue re-Occurs. Once the issue happened again now stop the data collector, open the log in Performance Monitor and review the values of Processor / % Processor Time counter to see if sustained CPU utilization is detected during the time of the lease timeout.

In SQL Server Agent jobs I can see Optimization job was triggered at that time , when the job started running after few minutes the lease timeout error was occurred, also the optimization job was not completed successfully.

BY this I can say When Optimization job was triggered as per the schedule the CPU usage went high at 100% ,also there are Blockings happened on the Database server we can see SPID killed by SQL Server in errrolog message due to /this the Lease Timeout or Lease Expired error occurred .

By default the Lease Timeout Value is ‘ 20000 ‘ if the issue re surfacing again and again we can Increase the Lease Timout Value from Windows Server Failover Cluster( WSFC ).

Open ‘ cluadim ‘ –> click on Roles in Left Pane–>Now On Right Pane click Availability Group–>Down you will seeing Resource TAB,click on it —>Right click on ListnerName –> click Properties–>Again on Properties TAB—> change the Lease Timeout value here —> then click OK .

But the changing Lease Timeout Value will not resolve your issue , it is only temporary Issue. Need to find out why blockings had occurred , also try changing the Optimization job schedule and will see.

Alwayson Logins Sync SQL Agent Job

In our environment we have lot of SQL Server Instances configured with Alwayson Availability Groups HADR
When we create a login on Primary Replica of an Availability Group it will not be automatically moved or get sync with all secondary replicas
This will cause an issue(Failed login error messages) when a Failover occurs as in current Primary replica the login does not exist .

To avoid such issues I have googled for script and found one PowerShell script which suits ours requirement ( I have automated this long back ago , didn’t remember the site or link to provide here – thanks to the original Author who created this script ) we automated by creating a copy logins job on all replicas, here I am using a PowerShell script to perform our task
Creating an SQL Agent job which executes PowerShell script on all replicas and this job is smart enough to identify which node it should execute.
On Primary replica When a new login creation or password update or changing\providing permissions occurs, this job script will replicate to all the secondary replicas.

Pre-requisites:

Need to have stored procedure ‘Sp_hexadecimal’ Script on all replicas.
This script will not do anything until there are any modifications done on Primary replica.
This script does not copy existing logins until you make any changes to them e.g. password change, grant/revoke privileges etc.

Schedule this job on all replica based on your environment and need.

Before implementing in Production environment please do testing

Created new  SQL Agent job Copy\Sync-Logins and added a Job Step Sync as shown below :

In Job Step given Step Name as ‘ Sync Logins’ and Type as ‘ Operating System (CmdExec) ‘ and command as ‘powershell -file “C:\temp\Sync-Logins.ps1”

click OK–>Schedule job–>OK–>OK, then the job gets created.

After creating Job Script out and execute it on all secondary replicas.

Whenever there is a modification done on Primary replicas those will get replicated to all secondary when the scheduled job runs.

Please find the Full Script in below link:

https://drive.google.com/file/d/1BhbtBrqBwk_D1jdhHAw9A_idTMA83PFt/view?usp=sharing