Maq Guru

Cannot Create Always On Availability Group on Azure Virtual Machines

July 28, 2020Muhammad QadeerAlways On Availability GroupsNo Comments

I have encountered the following error whilst creating Availability Group on Azure Virtual Machines. Tried again with TSQL and got the same error:

Msg 41131, Level 16, State 0, Line 3
Failed to bring availability group 'agMAQ' online.  The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.
Msg 41152, Level 16, State 2, Line 3
Failed to create availability group 'agMAQ'.  The operation encountered SQL Server error 41131 and has been rolled back.  Check the SQL Server error log for more details.  When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

1

2

3

4

Msg 41131, Level 16, State 0, Line 3

Failed to bring availability group 'agMAQ' online. The operation timed out. Verify that the local Windows Server Failover Clustering (WSFC) node is online. Then verify that the availability group resource exists in the WSFC cluster. If the problem persists, you might need to drop the availability group and create it again.

Msg 41152, Level 16, State 2, Line 3

Failed to create availability group 'agMAQ'. The operation encountered SQL Server error 41131 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command.

Checked SQL Server error log and found the following:

The state of the local availability replica in availability group 'agMAQ' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'.  The state changed because the local availability replica is joining the availability group.  For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
The state of the local availability replica in availability group 'agMAQ' has changed from 'RESOLVING_NORMAL' to 'NOT_AVAILABLE'.  The state changed because either the associated availability group has been deleted, or the local availability replica has been removed from another SQL Server instance.  For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.
Error: 19435, Severity: 16, State: 1.
Always On: WSFC AG integrity check failed for AG 'agMAQ' with error 41044, severity 16, state 1.

1

2

3

4

The state of the local availability replica in availability group 'agMAQ' has changed from 'NOT_AVAILABLE' to 'RESOLVING_NORMAL'. The state changed because the local availability replica is joining the availability group. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

The state of the local availability replica in availability group 'agMAQ' has changed from 'RESOLVING_NORMAL' to 'NOT_AVAILABLE'. The state changed because either the associated availability group has been deleted, or the local availability replica has been removed from another SQL Server instance. For more information, see the SQL Server error log, Windows Server Failover Clustering (WSFC) management console, or WSFC log.

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

Always On: WSFC AG integrity check failed for AG 'agMAQ' with error 41044, severity 16, state 1.

Decided to google the error and found this link. It’s a known issue to Microsoft. I have executed the following statement…

PolyBase External Tables and Always On Availability Group – Part 2

July 27, 2020Muhammad QadeerAlways On Availability GroupsNo Comments

In this post we will see how to add a database that hosts PolyBase external tables to an Always On Availability Group. In the previous post we have extracted data from external files stored in Azure Data Lake Gen 2 storage using PolyBase. Scenario A database “MAQDB” that hosts external tables. Database is encrypted by…

PolyBase External Tables in SQL Server 2019 – Part 1

July 21, 2020Muhammad QadeerSQL ServerNo Comments

In this post we will find out how to use PolyBase in SQL Server 2019 to extract data from text files stored in Azure Data Lake Gen2 storage. In the second part of this post we will see how to add databases that hosts PolyBase external tables into an Always On Availability Group. Scenario Text…

Change the dump directory for SQL Server or SQL Agent

July 7, 2018Muhammad QadeerSQL Server1

If you want to change the dump directory of SQL Server dump files (.mdmp extension) from the default location. You can do this using “SQL Server Configuration Manager” Start SQL Server Configuration Manager Right Click on SQL Server Instance and select Properties Go to Advanced Tab and change Dump Directory If you want to do…

Remote Desktop Connection: An authentication error has occurred. The function requested is not supported

June 18, 2018Muhammad QadeerInfrastructureNo Comments

Microsoft Security patch (CredSSP updates for CVE-2018-0886) issued on Tuesday, May 8th 2018 triggered the following RDP problem. “An authentication error has occurred. The function requested is not supported Remote computer: <computer name> This could be due to CredSSP encryption oracle remediation. For more information, see https:/go.microsoft.com/fwlink/?linkid=866660” It changed the default setting from Vulnerable to Mitigated….

Microsoft SQL-based technologies to plan your journey to GDPR compliance

June 10, 2018Muhammad QadeerSQL ServerNo Comments

The General Data Protection Regulation (GDPR) is fundamentally about protecting and enabling the privacy of individuals. The GDPR establishes strict global privacy requirements governing how personal data is managed and protected, while respecting individual’s choice. How this can be applied to Microsoft SQL Server environments? There are several technological solutions built-in to Microsoft SQL-based technologies…

Cluster network name resource failed registration of one or more associated DNS names

May 21, 2018Muhammad QadeerInfrastructureNo Comments

Here is another tricky issue you might encounter when working with Windows Server Failover Cluster. If you are creating a cluster and do not have domain admin rights, you may get the following error even if someone pre-staged objects for you and you have successfully created the cluster. “Cluster network name resource failed registration of…

sp_Blitz to collect information from SQL Server estate in a centralized server

May 11, 2018Muhammad QadeerSQL Server2

If you are taking over SQL Server(s) from someone or your client requested a quick health check of a SQL Server then the first thing that comes to your mind is “sp_Blitz”. Thanks to Brent Ozar. I don’t need to explain anything about sp_Blitz as it is industry standard. You can either use it on…

Joys of Multi-Subnet AlwaysOn Availability Group

April 28, 2018Muhammad QadeerAlways On Availability GroupsNo Comments

You have Multi-Subnet Availability Group environment with the following configuration: Availability Group Listener with one IP address for each defined subnet and have an “OR” dependency on each of the IP addresses SQL Server 2014 with .Net 4.5 MultiSubnetFailover parameter set to “TRUE” in the connection string Primary replica -> Read/Write Secondary replicas -> HADR…

Log Shipping-Restore Log with Standby Mode on an Advanced Format disk may cause a 9004 error

April 18, 2018Muhammad QadeerLog ShippingNo Comments

I have successfully configured Microsoft SQL Server log shipping between two servers; keeping the secondary database in Standby mode. Restore job failed on the very first run with the following error: Error: 9004, Severity: 16, State: 6. An error occurred while processing the log for database <databasename> . If possible, restore from backup. If a…