PowerShell script to Deploy Ola Hallengren for Multiple servers

Using PowerShell, deploy the ola hallegren Maintenance Solution Script. When we run the script below, we receive the following results.

-> Jobs in SQL will be created.

-> SQL Job Schedules will be created as well.

-> Some stored procedures will also get created ( SP’s are from Brentozar site )

Above all, we must do some manual work in order to be produced.

Requirements: We’ll need Sysadmin access to all of the machines where we’ll be running our PS script.

Step 1     : Ready a script to create DBA database for storing helpful stored procedures    
             and maintenance logging
Step 2     : Download and save the latest scripts to Create the below stored procedures in         
             the DBA database which are very useful
                - 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     : Get ready with the scripts to Create the maintenance jobs for backups, DBCC 
             CheckDB, 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.

Downloaded all the latest scripts and kept in my local c:\temp\ola folder

Please notice the snippet below, which shows how all the scripts are organized into folders:

If the Instance Names are entered into the CSV file as described in Step 6, we can begin deploying the ola hallegren Maintenance Solution script by running the PowerShell script ‘Deploy-SQLMaintenance.ps1’.

I’ve deployed this to a single instance in my system, and the result is as follows:

All stored procedures are deployed to the MASTER Database in the instance (you can alter the database name to deploy the SP’s depending on your setup).

I ran Maintenance jobs and checked the ‘command Log’ Table by querying this table, we can see all of the job details such as job start and end times, etc.

If you wish to update the Job Schedules, go to the Schedules Folder and look for three files with the titles prod, dev, and test environment. These files include schedule information for all three environments.

You can access the file and make the necessary adjustments to the job schedules to suit your needs. Once you’ve finished making changes, click Save and close the file :

You can also alter the Database Name by opening the file ” Database Creation “, where everything will be saved.

Please see the following excerpt to make changes to the Job creation file:

You may download the whole script from the following Google Drive link:

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

Export SSRS Report Inventory to CSV files

Today, I’m preparing documentation for pulling DB Instance reports to CSV files for SQL server Instance Migration since we have a change scheduled for tonight, which has reporting services installed, and there’s a request from customer to pull all SSRS reports to CSV files with Fields as SSRSServerName, ReportName, Connection details, Datasource details, DataSourcepath details.

To do this, we can use a different approaches, but I prefer to use a simple PowerShell script that requires only the parameters of the Reporting Services Server Name and the file path to the CSV file where the Reports information will be saved.

I recently went through and searched my old script repository and noticed that I already had this PowerShell script saved. We must appreciate the author for this script.

The PowerShell script can be found below:

CLS
$SSRS2012Server = "DESKTOP-02JIB76" #  Here Provide your SSRS Server Name
$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

The following is the output of the aforesaid script:

All 31 reports are then exported and saved to a CSV file, as shown below:

AlwaysON Lease Timeout or Expired Error

By creating new Windows Server 2016 operating systems and configuring Alwayson( 2 replicas) for SQL 2016 servers with multiple Availability Groups, we just completed a side-by-side migration from SQL Server 2008R2 Standalone databases (30 DB’s) to SQL Server 2016.

The application team changed their connection string to connect with’ListnerName’and checked the app’s functionality. The app team confirmed that everything is working as intended and that everything is fine.

After one week, I received a call from the application team (in the middle of the night) stating that they were experiencing connectivity issues when connecting to databases.

The Primary Availability Groups alwayson Dashboard seems HEATHY and GREEN when I signed in and verified the database server. ALL DATABASES ARE SYNCHRONIZED AND ARE AVAILABLE ONLINE.

Using sys.sysprocesses, I checked if any connections were being made to databases, and I noticed that application connections were being made to the database server.

As a result, I informed the application team, who responded that they had restarted their application services and that database connectivity was now working properly.

I checked the SQL Server Error Log and found the following 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 the Windows Server Failover Cluster and availability group ‘AVG1’ has come to an end. There was a problem with communication between the SQL Server instance and the Windows Server Failover Cluster. Check the associated availability group resource in the Windows Server Failover Cluster to see if the availability group is failing over correctly.

Message

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

Message

The ‘PRIMARY NORMAL’ state of the local availability replica in availability group ‘AVG1’ has changed to ‘RESOLVING NORMAL’. The lease between the local availability replica and Windows Server Failover Clustering (WSFC) has expired, hence the state has changed. See the SQL Server error log, the Windows Server Failover Clustering (WSFC) administrative panel, or the WSFC log for further details.

Message

Always On Availability Groups connection to secondary database ‘DAB1’ on availability replica ‘Nod1’ with Replica ID: 95d2878a-3fe0-48bf-aa2a-a41805fda786 has been terminated. This is a purely informative message. The user is not required to take any action.

Message

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

Message

Stopped listening on virtual network name ‘LISTN1’. 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: Changing the state of the Windows Server Failover Clustering (WSFC) resource associated with availability group ‘AVG1’ to Failed. In the instance of SQL Server, the resource state does not match the availability group state. The local availability replica is the primary replica, according to the WSFC resource status, but it is not in the primary role. This is a purely informative message. There is no need for the user to take any action.

Message

Always On: In response to a request from the Windows Server Failover Clustering (WSFC) cluster, the local replica of availability group ‘AVG1’ is prepared to shift to the resolving role. This is a purely informative message. There is no need for the user to take any action.

Message

Not Unable to access availability database ‘DAB1’ because the database replica is not in the PRIMARY or SECONDARY role. Only when the database replica is in the PRIMARY or SECONDARY role is it possible to connect to an availability database. Later, try the operation again.

Message

A connection for availability group ‘AVG1’ has been successfully created between availability replica ‘Nod1’ with id [3DB08082-C6D9-47E6-B53A-CCC8B15C21D2] and ‘Nod2’ with id [95D2878A-3FE0-48BF-AA2A-A41805FDA786]. This is a purely informative message. There is no need for the user to take any action.

According to the SQL Server Error Log, the problem was caused by a Lease Time Out that occurred on the Primary replica.
Due to intense resource use on the database server (100 percent CPU usage), SQL Server may become stalled or unresponsive for a period of time. This could have occurred as a result of a Lease timeout or expiration.

Follow the instructions below to find out how to set CPU Usage in Perfmon.

After you’ve created a Data collection and enabled it with the Start button, let it run until you get a lease timeout error or the problem reappears. Stop the data collector now, open the log in Performance Monitor, and check the values of the Processor / percent Processor Time counter value to see if CPU utilisation was reported high during the lease timeout error recorded in the errorlog.

I can see that an Optimization task was triggered at that time in SQL Server Agent jobs, however when the job started executing after a few minutes, it encountered a lease timeout issue, and the optimization operation was not completed successfully.

By this, I can say that when the Optimization job was initiated as per the schedule, the CPU utilization spiked to 100%, and there were blockings on the Database server, as seen by SPID being destroyed by SQL Server in an errrolog message, resulting in a Lease Timeout or Lease Expired issue.

The Lease Timeout Value is set to 20000 by default, however if the problem persists, we can increase the Lease Timeout Value from the 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 .

However, adjusting the Lease Timeout Value will not solve your problem; it is only a band-aid solution. Need to figure out why there are blockings; additionally, try adjusting the Optimization job schedule and see what happens.

Alwayson Logins Sync SQL Agent Job

There are lot of SQL Server Instances configured with Alwayson Availability Groups HADR in our environment.
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 on which node it should execute.
On Primary replica When a new login creation or password update or changing\providing permissions occurs, as per the schedule when this job triggered the script will replicate all the changes or modifications done on Primary replica 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 the Job on Primary replica 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

Availability Group Database Not Synchronizing / Recovery Pending State

Availability Group Database Not Synchronizing / Recovery Pending State:


When SQL Server is unable to access the database log files of your Availability Group Database/Databases, this error message appears. This problem can occur on both the Primary and Secondary Replicas.

Issue happened on Primary Replica:

You can check the SQL Server error log on Primary Replicas to see if the database log file is available or not.
If the log file is inaccessible, the error number 9001 is also reported.

We can fix the problem on Primary Replicas by doing the following:

  1. Ascertain that the database files are accessible.
  2. Attempt to SUSPEND and RESUME the Availability Group database; however, this may not resolve the issue.
  3. Set the database ONLINE using the ALTER command below ALTER database DBNAME set ONLINE
  4. Wait a few minutes and verify the database status; it should now be GREEN and HEALTHY.

If the Issue Reappears on the Secondary Replica:

If the database log file is available or inaccessible, look for error messages in the SQL Server error log. If the log file is inaccessible, the error number 9001 is logged.

To fix the problem on secondary replicas, follow these steps:

  1. Ascertain that the database files are accessible.
  2. Attempt to SUSPEND and RESUME the Availability Group database; however, this may not resolve the issue.
  3. Since the secondary replica is in the process of being restored, we can’t use the Alter command to bring the Availability Group database online. However, the db status on the sys.databases reports is ONLINE.
  4. We need to RESTART the secondary replica SQL Instance since it will start the recovery process for all databases. It will also assist in successfully recovering the faulty Availability Group database and synchronizing the database with the primary replica.