-> 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.
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)
-> 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.
-> We had an audit on the database servers. An auditor advise us that “Remote access” feature should be disabled due to below reasons,
[$] It is an obscure SQL Server to SQL Server communication feature that is deprecated feature. Please refer article for more details.
-> It was decided that we should disable it on our SQL Server instances. We disabled it using below code,
EXEC sp_configure 'remote access', 0 ;
GO
RECONFIGURE ;
GO
OR
-> Right Click SQL Server Instance -> Properties -> Connections -> Uncheck “Allow remote connections to this server”,
-> Please note that a restart of SQL Services is required for the changes to take effect.
-> We faced below issues after disabling sp_configure ‘remote access’ on our SQL Server instances.
Log Shipping
-> Logshipping main functionality as a whole such as backup, copy and restore works fine without issues. But the reporting part utilizing SQL Server Management Studio “Transaction Log Shipping Status” report displays incorrect information.
-> LSAlert job on Monitor servers fails as it is not able to get upto date information from Primary and Standby Instances.
-> The issues related to SSMS “Transaction Log Shipping Status” report and LSAlert job seems to be due to the fact that it is not able to get proper data from Primary and Standby server as the linked server fails with below error,
Could not execute procedure on remote server ‘LOGSHIPLINK_JBSAG4_200666876’ because SQL Server is not configured for remote access. Ask your system administrator to reconfigure SQL Server to allow remote access.
-> ‘LOGSHIPLINK_JBSAG4_200666876’ is a System Linked server that is created as part of Logshipping. I dropped this linked server and recreated using sp_add_linkedserver and was able to see same behaviour. I tried this step on a test server.
-> I checked the trace further and understood that the linked server errors out when trying to run below command remotely,
-> This is when I realized that the linked server utilizes RPC Out that is “Remote Procedure call” to execute above function remotely on Primary and DR to fetch the details. Linked servers with “RPC out” true will require Remote access to be turned on and this seems to be the root cause of our issue.
-> Linked Servers with RPC out set to false doesn’t have any issues and will function normally. But in our case, SSMS “Transaction Log Shipping Status” report and LSAlert job needs “RPC Out” set to true on Linked Servers to get the data remotely and display it as appropriate.
Linked Servers
-> The lab utilizes 2 database servers JBSAG1 and JBSAG2. Remote access option is disabled on both servers. A linked server for JBSAG2 is created on SQL Server Instance JBSAG1,
Remote Access
Linked server property
-> Querying a remote object Table1 using Linked server. It works fine,
-> Querying a remote View vw_table1 using Linked server. It works fine,
-> Querying a remote Stored procedure sp_table1 using Linked server. It returns an error that remote access option is required,
-> As a workaround, I will utilize “Execute AT” to execute remote Stored procedure sp_table1 using Linked server. It works,
Always On
I have setup a lab with 3 Always on replicas (2 in East US and 1 in Korea Central), testing was performed on the lab with Remote Access turned off,
[$] Create an Availability group. [$] Drop an Availability group. [$] Suspend and resume availability databases. [$] Load the availability database and create issues such as HADR_SYNC_COMMIT, Redo latency, high log send queue etc. [$] Check Always On dashboard several times. [$] Failover and failback manually. [$] Failover and failback automatically.
I did not see any issues on above scenario with remote access turned off. All above tests with remote access off were exhibiting same behavior as remote access on.
Summary
Discuss above points with your team and decide if things that will break as mentioned in this blog is required for your team and decide further.
In my case, many of our database servers has Logshipping and Linked servers configured. We had issues atleast on Logshipping as we make use of LSAlert and SSMS “Transaction Log Shipping Status” report day in and out. Hence, we provided this justification and left remote access on.
If there are any other features that make use of Remote Access which I have missed in my post. Please advise those in comment section.
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.
