Difference between HotFix,GDR,Cummulative Update and ServicePack

When ever new version of a product is being developed, it will be available to some selected community members and cutomers to do testing. This is called ALPHA builds for the product.

If the development goes smooth and progresses, the product becomes more reliable then it will be given to more customers. This is called BETA RELEASES ( eXAMPLE BETA1,BETA2…etc.,)

Now Microsoft has changed the terminology for pre-releases of SQL SERVER and they are referred to as Community Technology Previews( CTP ). This will be available to download in Microsoft site.

Once the product has been entered to its final stages of testing before release, it is called as Release Candidate (RC).

After this the product will go to significant testing , here confirms there will be no more changes needed for the product before release, it is called General Availability (GA).

Once all testing completed , the product will be handed over to company to produce media files like cd’s, dvd’s, . This is called Release to Manufacturing (RTM)

once the final product is being used by customers, they may face some specific issues with the product . To fix those issues MS releases Hotfixes which were developed by DEV Team.

Sometimes these issues are encountered to more and more customers, this time MS releases General Distribution Release (GDR) which are Security updates by this all customers will get these updates.

Both these hotfixes and GDR’s are created to address specific problems for customers immediately. The increase in number of hotfixes and GDRs makes impractical for many customers to apply So, MS created Cummulative updates (CU) which contains all the hotfixes . This is easier for all customers to install.

By gathering above all fixes MS has released a ServicePack (SP) which contains all the hotfixes, GDRs and cummulative Updates along with some new features which were not included at the time of General Availability.

Transaction log for Availability database is full due to AVAILABILITY_REPLICA

Error message :

The transaction log for database ‘Database’ is full due to ‘AVAILABILITY_REPLICA’

Error: 9002, Severity: 17, State: 9.

Cause : This occurs whatever the transaction changes happened at primary replica are not yet hardened to the secondary replica.

Troubleshoot the log file full Issue

Below are the scenarios when the log file is to grow and shows the log reuse wait desc column as AVAILABILITY_REPLICA

Some of the Possible Latencies when delivering logged changes to secondary replicas

Whatever the transaction changes happening at primary replica those will be encapsulated to log record blocks and these logged blocks are delivered and hardened to database log file at secondary replica. The Primary replica cannot overwrite these log blocks into its own log file untill the blocks have been delivered and hardened to corresponded database log file at secondary replica.
If there is any delay in hardened of these blocks to any secondary replica in Availability group will prevent truncation of those logged changes in the database at primary replica and will cause the log file to grow

Redo Latency

Once logged blocks have been hardened to the secondary database log file, a committed redo thread in the secondary replica instance applies those contained log records to corresponded data file.The primary replica cannot overwrite these log blocks into its own log file untill all redo thread on each secondary replicas have applied the contained log records

if the redo operation on any secondary replica is not able to keep up with the speed at which log blocks are hardened at the secondary replica , this will cause log file to grow at the primary replica. The primary replica can only truncate and reuse its own transaction log up to the point at which that all secondary replica redo threads have applied.
If there is more than one secondary replica we can compare the column Transaction_lsn from the DMV sys.dm_hadr_database_replica_states to get on which secondary replica database log truncation is delaying.

WorkAround

After identifying the secondary database which is causing this issue, we can try the following methods to work around this issue temporarily:

  1. Take out the Availability group database from secondary and join back again.
  2. If the redo thread is frequently blocked, set the Readable Secondary option to NO from the properties of Availability Group. Once the redo queue has reduced to an considerable size, change the Readable Secondary option to YES
  3. Enable the Autogrow setting if it is disabled and make sure to have available disk space.

PowerShell script to Deploy Ola Hallengren for Multiple servers

Deploy ola hallegren Maintenance Solution Script using powershell. when we execute this script below are get created

-> Jobs will get created

-> Job Schedules will also get created

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

To get created above all we have to do some manual work

Dependencies : Need Sysadmin Access on all the instances where we are executing this 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 see below snippet where all the scripts are kept in folders :

As mentioned in Step 6 if Instance Names are entered into the CSV file , we can start deploying this ola hallegren Maintenance Solution script by executing a PowerShell script ‘ Deploy-SQLMaintenance.ps1

I have deployed this to one Instance in my system and the output looks like below :

All stored procedures are deployed to MASTER Database in the Instance ( As per your environment you can change the database Name to deploy the SP’s ).

I executed Maintenance jobs and checked the ‘command Log ‘Table , by Querying this table we can see all the information of jobs like job start time and end time ..e.t.c.,

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

You can open the file and do the necessary changes for job schedules as per your environment. Once changes are done Click Save and close the file :

You can also change the Database Name by opening the file ‘ Database Creation ‘ where all will get stored into that Database.

To do changes in Job creation file , please see below snippet:

You can get 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., for pulling DB Instance reports which includes Databases Info, , Configurations, security, linked servers, settings ..e.t.c., to CSV files for SQL server Instance Migration as we have a change 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 of CSV file to save the Reports information.

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” # 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

Output from above script will get like below :

And the above all 31 Reports are exported and saved to a CSV file 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 when 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 ‘AVG1’ 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 ‘AVG1’ 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 ‘DAB1’ on the availability replica ‘Nod1’ 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

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: Explicitly transitioning the state of the Windows Server Failover Clustering (WSFC) resource that corresponds to availability group ‘AVG1’ 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 ‘AVG1’ 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 ‘DAB1’ 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

A connection for availability group ‘AVG1’ from availability replica ‘Nod1’ with id [3DB08082-C6D9-47E6-B53A-CCC8B15C21D2] to ‘Nod2’ 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 .
This may 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 timeout or expired.

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

After creating Data collection and enable by clicking 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 value to see if CPU utilization is detected high during the time of the lease timeout error recorded in errorlog.

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.