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

Restore All Databases from Backup Path Using DBATOOLS

I had a circumstance where I needed to restore 100’s of databases from a source server to a destination server, and we had to do it manually one by one, or manually build restore scripts for all databases using T-SQL scripts, or automate the process using PowerShell. I’m planned to use PowerShell to move the databases, however while searching, I came across DBATOOLS, which appears to be a quick and straightforward operation. To accomplish this, we must first install the DBATOOLS Module, and then update it if it is already installed. We used to get Database Refresh requests from Production to DEV/TEST on a regular basis as Database Administrators.

I installed and upgraded DBATOOLS on my machine, then I developed 3 databases to test migration from one instance to another. I already have database backup files in a folder to conduct our work, and now I’m testing to start restoring all 3 databases using DBATOOLS in PowerShell. To restore a database using DBATOOLS, we need to know the Restore command in PowerShell. We can check this in PowerShell using the Get-help *restore* command, which will display all of the available restore commands as well as a synopsis to help us figure out which restore command we can use to complete our task.

I use the command Restore-DbaDatabase from the DBATOOLS commands listed above. I’m only using this command since my backup files are already in a directory with Databases Full Backups, and I need to restore databases from these backup files.

In the above screenshot you can see 3 backup full files for test purpose, I use above backup path in our restore command to restore databases in my destination instance. Also I need to specify some parameters in Restore command like Destination Instance Name, backup Path, destination DATA Directory and Destination LOG Directory

Below is the Complete Restore command used in PowerShell :

You can see in the above Snippet that I ran the Restore command with the parameters passed in, and at the top you can see the restore database operation is in progress, as well as the percent completion of the restore is showing in  a Progress bar. Once the Database Restore is complete, it generates an Output that contains the information details. In the Output, we can see the Script Field, which contains the T-sql script used by DBATOOLS to restore databases. We may copy that T-sql script and use it for future restorations.

All 3 databases were successfully restored to my destination instance and the databases are ONLINE.

We may generate Restore Script for all Databases in a Backup path without restoring them to the Destination Instance by using the same command. We just need to add one parameter to the Restore command: -OutputScriptonly. Below is the Full Restore command :

Restore-DbaDatabase -SqlInstance DESKTOP-02JIB76\INS17 -Path 'E:\Backup\INS17' -DestinationDataDirectory 'D:\INS17\DATA' -DestinationLogDirectory 'D:\INS17\LOG' -WithReplace -OutputScriptOnly

When you execute above command it generates the Restore commands for all 3 databases and provides like below :


You can copy the Restore script to your SSMS and will looks like below :

Testing looks Good , I will use this in my current organization Environment .

Automate Migrating Databases From Source to Destination USing PowerShell

Using a combination of PowerShell and T-SQL scripting, create a simple solution for automating database migration across SQL Server versions. In my instance, I need to locate multiple databases that can be migrated using a standard procedure or at the very least a set of standard activities before proceeding to a set of customized actions. In these situations, you may want to create generic migration scripts and create a system to automate their execution. If you’re going to iterate through numerous test sessions and want to migrate a new copy before each one, a general technique like this can be extremely useful.

The activities that can be taken to make the migration process easier are as follows:

1) The backup database from the Source Instance will be transferred.
2) Restore the database that will be moved to the new instance.
3) Refresh the allocation usage.
4) Data purity can be used to check database consistency.
5) Set the level of page verification.
6) Set the database compatibility level to match the version of the destination instance (e.g. 120).
7) Statistics should be updated.
8) Remove any orphaned schemas (schemas automatically created for each database user to maintain compatibility but with no objects belonging to them).
9) Fix Orphaned Users if any exists.
10) Run Rebuild or re-organize all databases based on Migration Change window and the databases size.

Today, I’m automating the databases backup and restore of the first two Point. I still need to work on automating the other points, which are currently in process. I’m hoping that this backup and restore PowerShell script may be useful to some of the DBAs, and that I can share it now.

When you issue backup and restore commands in T-SQL, you’ll use handlers that are immediately called back by SMO, and you’ll provide a simple progress notification like the one provided by SSMS or sqlcmd. Please keep in mind that the completion handler just displays the error message.
Get-SQLInstance, DatabaseFullBackup, and RestoreDatabaseFromFullBackup are only a few of the functions in the Powershell script.

Below is the PowerShell script that will implement backup/restore procedures for each database which we want to migrate to complete the migration :

#region Load Assemblies and Global Error Handling
[System.Reflection.Assembly]::LoadWithPartialName( `
	"Microsoft.SqlServer.SMO") | Out-Null;
[System.Reflection.Assembly]::LoadWithPartialName( `
	"Microsoft.SqlServer.SMOExtended") | Out-Null;

# Simple global exception handling to see SQL Server errors
trap {
		$Exc = $_.Exception;
		while ( $Exc.InnerException )
		{
			$Exc = $Exc.InnerException;
			Write-Warning ("(generic trap) " + $Exc.Message);
		};
		break;
};
#endregion

#region Event Handlers
$PercentCompleteHandler = `
	[Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] `
	{ 
		Write-Host ([string]$_.Percent + " percent processed.");
	};
$CompleteHandler = `
	[Microsoft.SqlServer.Management.Common.ServerMessageEventHandler] `
	{
		Write-Host $_.Error.Message;
	};
#endregion

#region Functions definition
function Get-SQLInstance($InstanceName, $Login, $Password)
{
	$SQLInstance = New-Object "Microsoft.SqlServer.Management.Smo.Server" `
							  $InstanceName;
	if ($Login -eq $null) {
		$SQLInstance.ConnectionContext.LoginSecure = $true;
	}
	else {
		$SQLInstance.ConnectionContext.LoginSecure = $false;
		$SQLInstance.ConnectionContext.Login = $Login;
		$SQLInstance.ConnectionContext.Password = $Password;
	};
	# Force connection to get an early error message
	$SQLInstance.ConnectionContext.Connect();
	return $SQLInstance;
};
function DatabaseFullBackup ($SQLInstance, $DatabaseName, $BackupPath)
{
	$Backup = New-Object "Microsoft.SqlServer.Management.Smo.Backup";
	$Backup.Action = "Database";
	$Backup.Database = $DatabaseName;
	$Backup.Initialize = $true;
	$Backup.CopyOnly = $true;
	$Backup.Devices.AddDevice($BackupPath + "\" + $DatabaseName + ".bak" `
								, "File");

	$Backup.add_PercentComplete($PercentCompleteHandler);
	$Backup.add_Complete($CompleteHandler);

	$Backup.SqlBackup($SQLInstance)
};
function RestoreDatabaseFromFullBackup ($DestinationSQLInstance, $DatabaseName `
								, $BackupPath, $DataFilesPath, $LogFilesPath)
{
	$Restore = New-Object "Microsoft.SqlServer.Management.Smo.Restore";
	$Restore.FileNumber = 1;
	$Restore.Devices.AddDevice($BackupPath + "\" + $DatabaseName + ".bak"`
								, "File");

	foreach ($File in $Restore.ReadFileList($DestinationSQLInstance))
	{
		$NewFile = New-Object "Microsoft.SqlServer.Management.Smo.relocatefile";
		$NewFile.LogicalFileName = $File.LogicalName;
		
		#Primary Data File
		if	($File.FileID -eq 1 -and $DataFilesPath -ne "")
		{	
			$NewFile.PhysicalFileName = ($DataFilesPath + "\" + $DatabaseName `
											+ "_" + $File.LogicalName + ".mdf");
		}
		#Secondary Data File
		elseif	($File.Type -eq "D" -and $DataFilesPath -ne "")
		{	   
			$NewFile.PhysicalFileName = ($DataFilesPath + "\" + $DatabaseName`
											+ "_" + $File.LogicalName + ".ndf");
		}
		#Log File
		elseif	($File.Type -eq "L" -and $LogFilesPath -ne "")
		{
			$NewFile.PhysicalFileName = ($LogFilesPath + "\" + $DatabaseName `
											+ "_" + $File.LogicalName + ".ldf");
		};

		if ($NewFile.PhysicalFileName -ne $null) {
			[void]$Restore.RelocateFiles.add($Newfile);
		};
	};
	
	$Restore.Database = $DatabaseName;
	$Restore.ReplaceDatabase = $true;
	$Restore.NoRecovery = $false;
	$Restore.Action = "Database";

	$Restore.add_PercentComplete($PercentCompleteHandler);
	$Restore.add_Complete($CompleteHandler);

	$Restore.SqlRestore($DestinationSQLInstance); 
};
#endregion

#region Main
#Parameters
$SourceSQLInstanceName = "DESKTOP-02JIB76";                    # Mention your Source Instance Name 
$DestinationSQLInstanceName = "DESKTOP-02JIB76\INS17";         # Mention your Destination InstanceName

$BackupPath = "E:\Backup\INS17\";                              # Mention your Backup Path which can be your Local Path or Shared Path

$DataFilesPath = "D:\INS17\DATA";                              # Mention your Destination DATA File Path
$LogFilesPath = "D:\INS17\LOG";                                # Mention your Destination LOG File Path

$DatabaseNames = "BLOB","panda","dbareports";                  # Mention your Databases Names which are needed to Migrate

#Main
$SourceSQLInstance = Get-SQLInstance $SourceSQLInstanceName;
$DestinationSQLInstance = Get-SQLInstance $DestinationSQLInstanceName;

foreach ($DatabaseName in $DatabaseNames)
{
	Write-Host ("`nBackup database [" + $DatabaseName + "]");
	DatabaseFullBackup 	$SourceSQLInstance $DatabaseName $BackupPath;

	Write-Host ("`nRestore database [" + $DatabaseName + "]");
	RestoreDatabaseFromFullBackup 	$DestinationSQLInstance $DatabaseName `
									$BackupPath $DataFilesPath $LogFilesPath;

};
#endregion

You’ll need to update a few parameters in the script above, such as SourceInstanceName, Destination Instance Name, Backup Path, DATA File Path, LOG File Path and the Databases Names .

Once all of the parameters have been adjusted to your environment, the script will begin taking database backups from the source instance and restoring them to destination instance.

You can see below snippet where I don;t have any backup files , any Data and Log files:

Also I don’t have any Databases in my Destination Instance.

Now I’m backing up and restoring my “BLOB”,”panda”,”dbareports” databases with the PowerShell Script above. When I run the script, it will back up the first database, restore it to the Destination Instance after the backup is complete, and then take backups for the second database, It will begin restoring after the backup has been completed.

You can see in above Screenshot when Panda Database backup completed immediately it start s to Restore Panda Database in Destination , once the restore completed it will start backup for next Database (dbareports database) and will restore

The script completed migration of databases which I mentioned the DatabasesField

All three databases have been successfully migrated from the Source Instance to the Destination Instance. Now all you have to do is run the script in PowerShell ISE and it will take care of migrating your provided databases.