Auto close is enabled. Only databases with auto close disabled can be added to an availability group. To turn off auto close, set the Auto Close database property to False.

-> I was trying to add a database onto existing Always On Availability group. The database that I wanted to add was grayed out,

-> I was able to see the status for that database as “Does not meet prerequisites”. I got below message when I clicked on it,

TITLE: Microsoft SQL Server Management Studio

Auto close is enabled. Only databases with auto close disabled can be added to an availability group. To turn off auto close, set the Auto Close database property to False.

This database lacks a full database backup. Before you can add this database to an availability group, you must perform a full database backup.

-> I checked the database properties and was able to see that “Auto close” option was enabled for the database.

-> Auto close option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.

-> Due to above reason we decided to disable Auto close option.

-> You can use below query if you dont want the GUI method. I just scripted below query from GUI “Script” option.

USE [master]
GO
ALTER DATABASE [JBSDB] SET AUTO_CLOSE OFF WITH NO_WAIT
GO

-> Once the database JBSDB auto close option was disabled, I was able to add the database onto Availability group without any issues,

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Create failed for Availability Group Listener ‘JBApplication’. The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.

-> I tried creating an Always On Listener on an existing 4 node Always on Availability Group with below details,

-> It failed with below error,

TITLE: Microsoft SQL Server Management Studio
Create failed for Availability Group Listener ‘JBApplication’. (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.46521.71+(SMO-master-A)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+AvailabilityGroupListener&LinkId=20476

ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057. If this is a WSFC availability group, the WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. Otherwise, contact your primary support provider. For information about this error code, see “System Error Codes” in the Windows Development documentation.
The attempt to create the network name and IP address for the listener failed. If this is a WSFC availability group, the WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. Otherwise, contact your primary support provider.
The attempt to create the network name and IP address for the listener failed. If this is a WSFC availability group, the WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. Otherwise, contact your primary support provider. (Microsoft SQL Server, Error: 41009)

For help, click: https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-41009-database-engine-error

-> As per the error message, I checked what is Error code 5057 in “System Error Codes” by executing below command in Command prompt.

NET HELPMSG 5057

-> Error code 5057 refers to “The cluster IP address is already in use.”

-> This means that the IP address is already taken by some other host name.

-> Checking cluster.log for more details. Please use this article to generate cluster.log.

-> Below entries found in cluster.log which is same as what we got from Helpmsg,

000015f4.00000544::2022/05/30-11:58:35.110 ERR [RES] IP Address : IpaValidatePrivateResProperties: IP address 172.20.1.20 was detected on the network.
000015f4.00000544::2022/05/30-11:58:35.110 WARN [RHS] Resource JBAG1234_172.20.1.20 called SetResourceStatus, but OnlineOrOfflineCall call is NULL: Offline, 1.
000015f4.00000544::2022/05/30-11:58:35.110 WARN [RHS] returning ResourceExitStateTerminate.
000015f4.00000544::2022/05/30-11:58:35.110 WARN [RHS] Error 5057 from ResourceControl 20971654 for resource JBAG1234_172.20.1.20.

-> Lets find out the hostname that is using this IP Address by executing below command,

ping -a 172.20.1.20

-> I see that hostname JBSAPP.JBSWIKI.com is utilizing this IPAddress. I advised the same to my platforms team and got a new IP Address. I was able to create the listener after getting the new IP Address.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)

-> I was trying to configure Transactional replication on one of the SQL Server Instance and got below error,

TITLE: Configure Distribution Wizard
Microsoft SQL Server Management Studio is unable to access replication components because replication is not installed on this instance of SQL Server. For information about installing replication, see the topic Installing Replication in SQL Server Books Online.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.0.18369.0&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=ReplicationNotInstalled&LinkId=20476
ADDITIONAL INFORMATION:
Replication components are not installed on this server. Run SQL Server Setup again and select the option to install replication. (Microsoft SQL Server, Error: 21028)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.2000&EvtSrc=MSSQLServer&EvtID=21028&LinkId=20476

-> This is when I realized that replication features were not selected for this SQL Server Instance during Installation.

-> I opened the SQL Server setup files and clicked on “New SQL Server stand-alone installation or add features to an existing installation”.

-> Select “Add feature to an existing instance of SQL Server 2019” and select appropriate instance,

-> Select “SQL Server Replication” as shown in below screenshot,

-> Once the add feature is done. I was able to move ahead and configure Transactional replication.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.