AlwaysON Issues

Quarantined nodes in Windows Failover Clusters:

I’ve observed this issue on Windows Server 2016, where one of the nodes were quarantined after many node failover attempts in an hour. For the following few hours, the WSFC prevents the node from rejoining the cluster. This could be due to a network issue that I’ve noticed in the current environment. Quarantined receives a ping response from one node. Intermittently, the ping response from one node to the quarantined issue node receives a Timeout response.

For more details go through below link it helped me a lot :

https://www.mssqltips.com/sqlservertip/6714/windows-failover-cluster-node-quarantined/

2. You will encounter an error, The primary replica is not Active .The command cannot be executed.

This is due to an issue with the endpoint port not listening on the correct port or the tcp endpoint being stopped for some reason.

Go through below link for more :

https://techcommunity.microsoft.com/t5/sql-server-support/create-availability-group-fails-with-error-35250-failed-to-join/ba-p/317987

3. Unexpectedly, an availability group was missed, dropped, or removed.

This was caused by the fact that SQL was not being transmitted to WSFC. The SQL will then delete the Availability group.

Please go through below link for more details:

https://techcommunity.microsoft.com/t5/sql-server-support/issue-replica-unexpectedly-dropped-in-availability-group/ba-p/318175

4. Availability group is in Resolving State :

Issue : For a few minutes, there was a cluster failure that affected the availability group, and the replicas went into a resolving state. The replicas returned to their regular primary and secondary states once the cluster was brought back online, however several databases were still not synchronising. Furthermore, the databases on the primary were unavailable.

Resolution : The only remedy we found was to restart the primary replica’s SQL instance. Even a simple restart of the SQL service through the config manager, however, was stuck on “stopping service.” We had to use the TSQL command “SHUTDOWN WITH NOWAIT” to make SQL to stop. The databases were available again after SQL was brought back up, and AG was in sync and healthy.

For further details go through below link :

Issue : The secondary replica does not correctly transition to the primary role if an automated failover event fails. As a result, the availability replica will indicate that this replica is in the process of resolving. Furthermore, the availability databases state that they are not synchronising, and apps are unable to access them.

Reasons to occur :

Case 1 : The value of “Maximum Failures in the Specified Period” has been reached.
The clustered resource will fail three times in a six-hour period if the default behaviour is followed. RESOLVING STATE is applied to the AG replica.

Case 2 : Local SQL Server NT AUTHORITY\SYSTEM login account has insufficient NT Authority SYSTEM account rights. The following permissions are provided by default to this local login account:

Alter Any Availability Group
Connect SQL
View server state

Case 3 : If one of the availability databases in the availability group is in the synchronizing or not synchronized state, automatic failover will not be able to successfully transition the secondary replica into the primary role.

To get more details check below link :

https://docs.microsoft.com/en-us/troubleshoot/sql/availability-groups/troubleshooting-automatic-failover-problems

5 . Diagnose Unexpected Failover or Availability Group in RESOLVING State

Lease Timeout : A lease timeout can be triggered if SQL Server does not react within the normal 20-second lease timeout period.

Lease Timeout CAUSE – 100% CPU Utilization: A lease timeout might occur if CPU utilization is extremely high for an extended length of time. Using Performance Monitor, keep an eye on your CPU usage.

Sync Issues :

how to troubleshoot Always On synchronization issue?
Multiple reasons for the database status changing to not in sync

  1. Network Issue
  2. Huge transactions
  3. Space Issues

Troubleshoot Missing Of MSI or MSP files using FixMissingMSI tool

If you’ve ever had troubles with missing cached MSI/MSP files during SQL server setup, you might be familiar with the following article:
How to fix difficulties that occur during a SQL Server update by restoring the missing Windows Installer cache files.

https://docs.microsoft.com/en-US/troubleshoot/sql/install/restore-missing-windows-installer-cache-files

The above link has a VB script that will assist you in locating lost MSI/MSP files in the Windows installer folder, as well as recovering these files if the setup source is restored. The VB script works fine, however if there are a lot of missing MSI/MSP files, copying them back one by one can be tedious.

FixMissingMSI Tool :

The FixMissingMSI software will be used in this approach to locate MSI and MSP files that are missing from the Windows Installer cache. You can also point the tool to the original media locations and have it recache the missing files as an extreme measure.

The FixMissingMSI software is available for Free download from the below link :

https://github.com/suyouquan/SQLSetupTools/releases/

I extracted the file from the latest version 2.2.1 software that I downloaded from the URL above.

One of the prerequisites for using this tool is that you have.NET Framework 4.5 installed.

After you’ve extracted the downloaded software, navigate to the Manual folder to learn how to use this tool.

When I was installing SQL 2014 Service Pack 3 and Cumulative Update today, I ran into an issue with MSI and MSP files missing. I tried this FixMissingMSI software to see if I had several files missing, and we were able to fix them using this tool.

When I double-clicked FixMissingMSI.exe, a wizard appeared, asking if I wanted to set up the scan menu.
If you select “Product name contains” and enter “SQL,” the program will search for all products whose product names contain the key word and also you need to provide Media + SP + CU location path

If you specify this setup source folder, this program will search all of the *.MSI and *.MSP files inside to see if any missing or mismatched MSI/MSPs can be identified.
If you don’t specify this folder, the tool may be unable to locate the MSI/MSP for those missing/mismatched files, leaving you unable to repair them. Even if you don’t specify this path, this program may still be able to locate the missing MSI/MSP in the installed product’s LastUsedSource folder.
For example, for the SQL product, the best practice is to place the SQL server RTM setup media and all subsequent service packs, CUs in the same folder, for example:

Please remember to extract the package using the ‘X‘ switch to the folder if you download service packs or cumulative updates from the Microsoft site. For instance, the command below extracts SP1 to the SP1 subfolder:


SQLServer2014SP3-KB4022619-x64-ENU.exe /X


Even though the package is in the same folder, it is useless if you do not extract it.

I’ve created a media path that includes RTM, SP, and CU. Clicked SCAN NOW button

If you have any missing files, the scanning results will show them like below :

You will see all files in the All option, which signifies the status of Missing, Mismatched, and OK.
Only those will be listed if you pick Missing or Mismatched only.

When I selected Fix It, a pop-up window appeared, instructing me to click OK to copy the missing file.

It was fixed by copying the file from Install Source to Installer Folder after pressing the Fix It button, and that line was marked as GREEN.

We can fix the missing msi files separately, as shown above, however fixing the number of multiple files individually is a time-consuming operation. So, we can accomplish it by going to the Fix TAB and clicking on the Fix All button.

As you can see, I have 60 missing files that need to be restored to the Installer folder on Drive C. When I clicked the Fix All option, a pop window appeared asking me to click OK to immediately fix all missing files. I pressed the OK button.

It repaired 30 missing files and attempted to install SQL 2014 SP3 and CU, which it did successfully.

There are still 30 files that need to be repaired. Here’s what I noticed from the tool, This FixmissingMSI tool has a column called ‘LastUsedSource,’ which is nothing but the location where we installed the most recent SP or CU. If the setup folder exists on the server, this tool will automatically gather information from that LastUsedSource, which is nothing more than the Last Installed Source location of the media, service pack, or cumulative update.

It will not do anything if the recently installed SP or CU folders do not exist on the server, and the column ‘Fixcommand’ will not be generated with any commands to repair it.

I‘m not sure if my assumption is correct.

As a report, we can also export the data from the missing files to a CSV and text file.

When you are exporting the data out from File TAB and clicking on Export just provide the Filename and click ok
Two Files with format .csv and .txt are automatically created on the physical location where you have given path to store with Filename which you have given .

Note :

The SQL Media\Service Pack \ Security Update \ Cumulative Update We must manually extract any patch or ISO file to a specific directory or folder before running the installation by double-clicking the “SETUP.exe” file, which is Best Practice for Installation.
If we launch the package without first manually extracting it, the software will extract it to an unknown location and begin the setup process. When the server is REBOOTED, that UN-known location may no longer exist on the server, and the next time we run any New SP\CU\SU, it will check for previously extracted locations, but if that place is not present on the server, an error will be thrown, and the installation will fail.

Capture Blockings Information Using SP_WHOISACTIVE

Hope everyone knows about Adam Mechanic SP_whoisactive stored procedure which is very good for DBA’s to use for finding out more information what is going on Sql Server.

Below is the new link where it is moved to github

https://github.com/amachanic/sp_whoisactive

From the above link you can download the Stored Procedure and execute it on MASTER database or you can create it in a DBA owned USER DATABASE , I have created in MASTER Database

From the above screen shot you can see the details of SP_WHOISACTIVE stored procedure where we have 24 rows and we are going to pass parameters for fetching the required data using the Parameter_name column as it will be a Lightweight to execute the SP instead of executing without any parameters as it will put some pressure on Sql to get the requested output .

Today I used this SP_WHOISACTIVE stored procedure to capture Blocking information, to do that I followed below steps :

1 Downloaded SP_WHOISACTIVE from github site ( which already there in my local )
2 Executed SP in MASTER Database ( You can create in any Database)
3 Also I created One Table by passing the parameter @return_schema =1 ( This table is to Capture the Info)
4 After creating table we need to Load the Data by running the Stored Procedure with required Parameters
5 After completing above all steps now we are ready to Schedule the Agent job to Capture our Information .

You can get information of each parameter by executing the stored with parameter @help=1

With the help of below script you can get all information of what is going on in sql server instance , you can also remove some parameters which is not required for you and make your custom script .

EXEC sp_WhoIsActive
    @filter = '',
    @filter_type = 'session',
    @not_filter = '',
    @not_filter_type = 'session',
    @show_own_spid = 0,
    @show_system_spids = 0,
    @show_sleeping_spids = 1,
    @get_full_inner_text = 0,
    @get_plans = 0,
    @get_outer_command = 0,
    @get_transaction_info = 0,
    @get_task_info = 1,
    @get_locks = 0,
    @get_avg_time = 0,
    @get_additional_info = 0,
    @find_block_leaders = 0,
    @delta_interval = 0,
    @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%][cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]',
    @sort_order = '[start_time] ASC',
    @format_output = 1,
    @destination_table = '',
    @return_schema = 0,
    @schema = NULL,
    @help = 0

The script will give you output like below :

Now to capture information in a table to see whenever it is required for us we need to first create table which is a destination table to get all data loaded into it by executing the Store procedure SP_WHOISACTIVE.

Use master -- you can change Database Name
go

DECLARE @script_table VARCHAR(MAX)

EXEC sp_WhoIsActive

@get_plans = 1,@get_task_info = 2,@get_locks = 1   -- you can add more parameters here
,@return_schema = 1,@format_output = 1,

@schema = @script_table OUTPUT

SET @script_table = REPLACE(@script_table, '<table_name>', 'dbo.SPWHOI')--Change Table Name

PRINT (@script_table)

Output of above script you will get it in result pane, copy it and past in new window to execute the script .I have executed in MASTER database and the table was created :

CREATE TABLE dbo.SPWHOI ( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,
[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[tasks] varchar(30) NULL,[CPU] varchar(30) NULL,
[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,
[reads] varchar(30) NULL,[writes] varchar(30) NULL,[context_switches] varchar(30) NULL,[physical_io] varchar(30) NULL,
[physical_reads] varchar(30) NULL,[query_plan] xml NULL,[locks] xml NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,
[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,
[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,
[collection_time] datetime NOT NULL)

After creating Table (SPWHOI) now manually try loading the data and retrieve it to see by using below script :

EXEC sp_WhoIsActive
@get_plans = 1,@get_task_info = 2,@get_locks = 1
,@format_output = 1
,@destination_table = 'master.dbo.SPWHOI'
--truncate table master.dbo.SPWHOI
select * from master.dbo.SPWHOI

Now you can schedule Agent job to get instance information loaded into a table for every 5 minutes ( this can be changed based on your need ).when job runs data will be populated into the table and the size of the table will increase so, make sure you have enough space on the disk . Below is the code I used in Agent Jobto capture the information .

EXEC sp_WhoIsActive
@get_plans = 1,@get_task_info = 2,@get_locks = 1 --Add more parameters as per your need
,@format_output = 1
,@destination_table = 'master.dbo.SPWHOI'

Job ran and it loaded data into table SPWHOI

If you see above screenshot there was a blocking SPID recorded in our custom Table SPWHOI and also we can see the execution plan ,sql text, [dd hh:mm:ss.mss] which is Runtime of query, wait_info, blocking_session_id, login_name, hostname…e.t.c.,

You can see only blocking info with runtime of the SPID i.e., how much time it took to execute that script from the custom Table SPWHOI

select start_time ST,collection_time as CT,[dd hh:mm:ss.mss] as Running_time,* from master.dbo.SPWHOI

where blocking_session_id <>0

order by  Running_time desc 

SQL Server 2019 Requirements for Server Side and SQL Side

SQL Server 2019 Requirements:

The minimum requirements to install SQL Server 2019 on windows server operating system.

Requirements for Hardware:

SQL Server needs a minimum of 6GB available on the disk where SQL is being installed and the disk requirements will vary based on the Components you selects while installing

It requires a minimum of 1GB RAM but microsoft recommends to have minimum 4GB RAM and it also depends on the database size we may need to increase the RAM for better performance.

Note : SQL Server installation supports on x64 processor only.x86 is no longer supported by MS.

Requirements for Software :

SQL Server 2019 requires a minimum operating system of Windows Server 2016/ Windows Server 2019
It requires a .NET Framework 4.6.1 which will come pre-installed on Windows Server 2016/ Windows Server 2019
Also better to have .NET Framework 3.5 installed as Database Mail requires this.

Supported Operating systems for SQL Server 2019 :

SQL Server 2019 Supported Editions

EntDevStanWebExpress
Windows Server 2019 EnterpriseYESYESYESYESYES
Windows Server 2019 StandardYESYESYESYESYES
Windows Server 2019 DatacenterYESYESYESYESYES
Windows Server 2016 EnterpriseYESYESYESYESYES
Windows Server 2016 StandardYESYESYESYESYES
Windows Server 2016 DatacenterYESYESYESYESYES

Support on Server Core Operating System:

SQL Server 2019 installing on Server Core operating system is supported by below Windows Servers :

Windows Server 2016 Core Edition

Windows Server 2019 Core Edition

SQL Server 2019 Installation Media file can be downloaded from below link :

https://www.microsoft.com/en-in/evalcenter/evaluate-sql-server-2019

Page File Configuration :

To get a memory dump for future analysis , servers are configured by setting a pagefile with 1.5 times of Memory (1.5*Physical Memory ) .

Disk Volumes Details:

For high I/O behavior of SQL and some application we need to consider disk performance. Data and Log volumes needs to be in separate volumes or separate disks.

Example:

VolumeDrive Mount PointSize in GBDisk Speed
SQL DATAG:\SQLDATA250 GBFAST
SQL LOGSG:\SQLLOGS300GBVERY FAST
SQL TEMP LOGG:\SQLTEMPLOG50GB VERY FAST
SQL TEMP DATAG:\SQLTEMPDATA50GBFAST
SQL BACKG:\SQLBACK500 GBSTANDARD

Note : Mount point root Permissions does not inherit to the mount points . We need to explicitly copy those root permissions and set the same to the mount points.

Firewall Security :

TCP/UDPPORTDescription
TCP1433SQL Server/Availability Group listener ( Default Port can be changed )
TCP5022SQL Server DBM/AG endpoint ( Default port can be changed )
UDP1434SQL Server Browser
UDP2382SQL Server Analysis Services Browser
UDP2383SQL Server Analysis Services Listener
UDP49152-65535Dynamic TCP\UDP ( Defined Policy or by Company )

How to troubleshoot Slow-running queries on SQL Server

Steps to troubleshoot SQL Server queries that are taking too long to execute :

When a user approached you and said his query was taking too long to complete, As a DBA, we perform all of the basic checks, such as checking CPU usage, memory usage, any blockings, deadlocks, locks, fragmentation, statistics update, and so on. If the user still experiences slowness after performing all of these checks, we must follow the steps below to troubleshoot or fix slow-running queries on SQL Server.

1 Check for Index Analysis :That means we need to check if correct indexes are exist. When a user requests that a DBA study a slow-running query with which he is having problems, we can utilise Database Tunning Advisor to analyse the query and provide recommendations for indexes that will help us improve query performance. We may check if right indexes are placed in a database this manner.

2 We need to search for join hints: HASH,LOOP,MERGE, and REMOTE are the four forms of join hints. I believe SQL Server mostly use these operators: LOOP, HASH, and MERGE. This hint will override SQL Server query optimization, preventing the query optimizer from selecting a fast execution plan. Because the optimizer changes in the current SQL Server version or builds may have a negative impact on performance or may have no impact at all.

So, if the query we’re looking at has any hints, try to decrease or delete them before rerunning the query to see how it performs.

3 Check the Execution Plan: SQL query Analyser may be used to check the query execution plan. We may achieve this by selecting Display Estimated Execution Plan from the query option in the query window.

4 Check the ShowPlan Output: The ShowPlan Output provides information about the query execution plan used by SQL Server. The basic checks we must observe from the execution plan to establish if it is using the correct plan are listed below :

–> Index Usage
–> Join Order Usage
–> Join Types
–> Parallel Execution