Fixing Failed to Create SQL Server Availability Group Resource

Have you ever encountered the error message “Failed to create the Windows Server Failover Clustering (WSFC) resource with name and type ‘SQL Server Availability Group'” while creating an availability group in SQL Server? If so, don’t worry, you’re not alone. In this blog post, we will explore the cause of this error and discuss two possible solutions to fix it.

The Error Message

When attempting to create an availability group, you may receive the following error message:

Msg 41105, Level 16, State 0, Line 3
Failed to create the Windows Server Failover Clustering (WSFC) resource with name 'SQLAUTHORITY_AG' and type 'SQL Server Availability Group'. The resource type is not registered in the WSFC cluster. The WSFC cluster may have been destroyed and created again. To register the resource type in the WSFC cluster, disable and then enable Always On in the SQL Server Configuration Manager.
Msg 41152, Level 16, State 2, Line 3
Failed to create availability group 'SQLAUTHORITY_AG'. The operation encountered SQL Server error 41105 and has been rolled back. Check the SQL Server error log for more details.

This error message indicates that the resource type ‘SQL Server Availability Group’ is not registered in the WSFC cluster. It suggests disabling and then enabling the Always On feature in the SQL Server Configuration Manager to register the resource type.

The Cause of the Error

Upon further investigation, it was found that the PowerShell command Get-ClusterResourceType | where name -like "SQL Server Availability Group" returned no results, confirming that the resource type was indeed not registered in the WSFC cluster.

Possible Solutions

There are two ways to fix this issue:

1. Register the Resource Type Manually

You can manually register the resource type using PowerShell. Execute the following command:

Add-ClusterResourceType -Name "SQL Server Availability Group" -DisplayName "SQL Server Availability Group" -Dll "C:\Windows\System32\hadrres.dll"

This command registers the ‘SQL Server Availability Group’ resource type in the WSFC cluster.

2. Disable and Enable Always On Feature

The recommended solution is to disable and then enable the Always On feature using the SQL Server Configuration Manager. This can be done by following these steps:

Open the SQL Server Configuration Manager.
Locate the Always On feature and disable it.
Enable the Always On feature again.

By disabling and enabling the feature, the resource type will be registered in the WSFC cluster, resolving the error.

Conclusion

If you encounter the error message “Failed to create the Windows Server Failover Clustering (WSFC) resource with name and type ‘SQL Server Availability Group'” while creating an availability group in SQL Server, you now have two solutions to fix it. You can either register the resource type manually using PowerShell or disable and enable the Always On feature in the SQL Server Configuration Manager. Choose the solution that works best for you and enjoy a seamless availability group creation process.

Have you encountered this error before? What was the cause in your case? Share your experiences in the comments below!

Click to rate this post!

[Total: 0 Average: 0]

Comprehensive 360 Degree Assessment

Data Replication

Performance Optimization

Data Security

Database Migration

Expert Consultation

Cloud Migration Made Easy

Considering a move to the cloud? Axial SQL brings you proven migration strategies to streamline your transition. Our expert team ensures a smooth, efficient shift, keeping your data safe and accessible. Start your journey to the cloud with confidence!

SQL Performance Optimization

Is your SQL running slower than expected? Don't let sluggish performance hinder your business. Our optimization experts at Axial SQL specialize in tuning your databases for peak performance. Speed up your SQL and supercharge your data processing today!

Database Stability Solutions

Tired of frequent database outages? Discover stability with Axial SQL! Our comprehensive analysis identifies and resolves your database vulnerabilities. Enhance reliability, reduce downtime, and keep your operations running smoothly with our expert guidance.

Expert Database Team Evaluation

Questioning your database team's efficiency? Let Axial SQL provide an expert, unbiased analysis. We assess your team's strategies and workflows, offering insights and improvements to boost productivity. Elevate your database management to new heights!

Data Security Assurance

Concerned about your database security? Axial SQL is here to fortify your data defenses. Our specialized security assessments identify potential risks and implement robust protections. Keep your sensitive data secure and your peace of mind intact with our expert services.

Published on