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