Category Archives: AlwaysOn + Log Shipping

Standard

Posted by

Posted on

March 9, 2022

Posted under

AlwaysON, AlwaysOn + Log Shipping, Azure, Core

Comments

Leave a comment

Disabling Deprecated feature sp_configure Remote access

-> 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,

select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N’.msdb.sys.sp_processlogshippingmonitorhistory’
exec @retcode = @linkcmd
                    @mode = @mode
                    ,@agent_id = @agent_id
                    ,@agent_type = @agent_type
                    ,@session_id = @session_id
                    ,@session_status = @session_status
                    ,@monitor_server = @monitor_server
                    ,@monitor_server_security_mode = 1
                    ,@database = @database
                    ,@log_time = @log_time
                    ,@log_time_utc = @log_time_utc
                    ,@message = @message

-> 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.

Standard

Posted by

Posted on

March 3, 2022

Posted under

AlwaysON, AlwaysOn + Log Shipping

Comments

Leave a comment

Failed to bring availability group ‘AG’ online. The operation timed out. If this is a Windows Server Failover Clustering (WSFC) availability group, verify that the local 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. (Microsoft SQL Server, Error: 41131)

Environment

-> JBSAG1 and JBSAG2 are Azure IaaS virtual machines provisioned on East US region. Azure IaaS virtual machine JBSAG3 is provisioned on West US region.

-> Azure virtual machine JBSAG1, JBSAG2 and JBSAG3 are part of Windows cluster JBSWikiClust without shared storage. Azure virtual machine JBSAG1 and JBSAG2 will be configured using Synchronous commit with automatic failover. JBSAG3 will be configured using Asynchronous commit with Manual failover.

-> I was testing the failover of above setup, changed the commit mode to JBSAG3 to Synchronous and tried a failover from JBSAG1 to JBSAG3. It failed with below error,

TITLE: Microsoft SQL Server Management Studio
Manual Failover failed (Microsoft.SqlServer.Management.HadrTasks)
ADDITIONAL INFORMATION:

Failed to perform a manual failover of the availability group ‘JBSAG’ to server instance ‘JBSAG3’. (Microsoft.SqlServer.Management.HadrModel)
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&LinkId=20476

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

Failed to bring availability group ‘JBSAG’ online. The operation timed out. If this is a Windows Server Failover Clustering (WSFC) availability group, verify that the local 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. (Microsoft SQL Server, Error: 41131)

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

-> This issue occurs when [NT AUTHORITY\SYSTEM] account is not available or if the account lacks the necessary permissions on the SQL Server Instance.

-> If [NT AUTHORITY\SYSTEM] account is not available. Create it using below command,

USE [master]
GO
CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

-> Provide appropriate permission to [NT AUTHORITY\SYSTEM] using below command,

GRANT ALTER ANY AVAILABILITY GROUP TO [NT AUTHORITY\SYSTEM]
GO
GRANT CONNECT SQL TO [NT AUTHORITY\SYSTEM]
GO
GRANT VIEW SERVER STATE TO [NT AUTHORITY\SYSTEM]
GO

-> Once above permission was provided, I was able to failover to JBSAG3 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.

Standard

Posted by

Posted on

January 10, 2021

Posted under

AlwaysOn + Log Shipping

Comments

2 Comments

Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing

AlwaysOn + Log shipping related blogs
Below blog post are all related to AlwaysOn and log shipping configured together,
-> Configuring Log Shipping with AlwaysOn
-> Removing Log shipping on a database with AlwaysOn configured

Environment

-> JBSAG1, JBSAG2 and JBSAG3 are part of a failover cluster without shared storage. Alwayson Availability group is configured between JBSAG1, JBSAG2 and JBSAG3. JBSAG1 is the current primary, JBSAG2 is the synchronous secondary and JBSAG3 is Asynchronous secondary. The database(s) that is part of Always on Availability group has log shipping configured and its secondary is on JBSAG4.

-> LSCopy and LSRestore jobs were failing.

-> LSCopy job Error,

Date 1/8/2021 1:04:00 PM
Log Job History (LSCopy_JBSAG1_JBDB)
Step ID 1
Server JBSAG4
Job Name LSCopy_JBSAG1_JBDB
Step Name Log shipping copy job step.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2021-01-08 13:04:00.30 *** Error: Could not retrieve copy settings for secondary ID ‘1d58dd23-142c-498f-83ab-5077791b5781’.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:04:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:04:00.30 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:04:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:04:00.30 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:04:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:04:00.30 —– END OF TRANSACTION LOG COPY —–
Exit Status: 1 (Error)

-> LSRestore job error,

Date 1/8/2021 1:10:00 PM
Log Job History (LSRestore_JBSAG1_JBDB)
Step ID 1
Server JBSAG4
Job Name LSRestore_JBSAG1_JBDB
Step Name Log shipping restore log job step.
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
2021-01-08 13:10:00.28 *** Error: Could not retrieve restore settings.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:10:00.28 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:10:00.30 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:10:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:10:00.30 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2021-01-08 13:10:00.30 *** Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2021-01-08 13:10:00.30 —– END OF TRANSACTION LOG RESTORE —–
Exit Status: 1 (Error)

-> I searched through the internet and found same/similar errors in LSCopy and LSRestore jobs, if the server mentioned on these jobs are not the log shipping secondary server. Let us look what I have in my database server.

-> LSCopy job step contains below command,

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Copy 1D58DD23-142C-498F-83AB-5077791B5781 -server JBSAG4

-> LSRestore job step contains below command,

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Restore 1D58DD23-142C-498F-83AB-5077791B5781 -server JBSAG4

-> In our setup, LSCopy and LSRestore jobs contains the correct log shipping secondary server as above marked in green. You need to ensure that LSCopy and LSRestore jobs should contain the log shipping secondary servers only.

-> I started a profiler trace on Log shipping secondary server and started LSCopy job and found below,

-> From the above screenshot, function sys.fn_MSvalidatelogshipagentidreturns value 1 or 0 depending on below query,

return case
when ((@agent_type = 0) and
exists (select * from msdb.dbo.log_shipping_monitor_primary
where primary_id = @agent_id)) then 1
when ((@agent_type in (1,2)) and
exists (select * from msdb.dbo.log_shipping_monitor_secondary
where secondary_id = @agent_id)) then 1
else 0 end

-> Lets execute the below query on Log shipping secondary JBSAG4 and verify the output,

select secondary_id, primary_server, primary_database from msdb.[dbo].[log_shipping_monitor_secondary]
where secondary_id = '1D58DD23-142C-498F-83AB-5077791B5781'

-> We dont see any data in object msdb.dbo.log_shipping_monitor_secondary for agent_id specified in LSCopy job, so this means it is going to return always 0 and you will see below error,

Error: The specified agent_id 1D58DD23-142C-498F-83AB-5077791B5781 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider)

-> Lets execute below query and see what valid id we have in object log_shipping_monitor_secondary,

select secondary_id, primary_server, primary_database from msdb.[dbo].[log_shipping_monitor_secondary]
where primary_database = ‘JBDB’

-> Secondary_id is 1A0BAD73-8C71-4445-96AC-61BC5AC2FD23. Lets try replacing the LSCopy and LSRestore job as below,

LSCopy job,

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Copy 1A0BAD73-8C71-4445-96AC-61BC5AC2FD23 -server JBSAG4

LSRestore job,

“C:\Program Files\Microsoft SQL Server\150\Tools\Binn\sqllogship.exe” -Restore 1A0BAD73-8C71-4445-96AC-61BC5AC2FD23 -server JBSAG4

-> Once I have replaced the jobs using above query. The job completed fine.

-> It seems Always On failover happened from JBSAG1 to JBSAG2, once after the failover someone have opened the log shipping settings and configured secondary server on JBSAG2 as below,

-> Above action created 1 more LSCopy and LSRestore job on secondary server JBSAG4. It seems like the job created as part of JBSAG2 configuration was later removed and this left the other job with wrong secondary_id value. This is the reason for failure.

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.