Log Shipping Copy Fails

Question

 

I am trying to implement Log Shipping across several SQL Server 2005 systems.  My goal is to have one primary and probably four secondaries.  I set up the primary and the 1st secondary and everything worked as promised.  However, with the 2nd secondary, the transaction log copy to the secondary fails with the following:

 

Message
2008-07-21 16:00:02.85        *** Error: Could not retrieve copy settings for secondary ID 'b135549e-76d8-4297-a5b3-b043a01267ac'.(Microsoft.SqlServer.Management.LogShipping) ***
2008-07-21 16:00:02.85        *** Error: The specified agent_id B135549E-76D8-4297-A5B3-B043A01267AC or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2008-07-21 16:00:02.87        *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2008-07-21 16:00:02.87        *** Error: The specified agent_id B135549E-76D8-4297-A5B3-B043A01267AC or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2008-07-21 16:00:02.87        *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2008-07-21 16:00:02.87        *** Error: The specified agent_id B135549E-76D8-4297-A5B3-B043A01267AC or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***

 

Is there something different for configuring subsequent secondaries?  I set it up exactly the same as the first, including the agent accounts.

 

Any thoughts?  Thank you.

Answer 1

Found the problem.  You cannot add a 2nd secondary without deleting the 1st secondary and the primary . . . and starting all over again.  You must do them all at once.  Therefore, if I want to add a 3rd secondary, I will have to blow everything away, configure the primary and then all 3 secondaries.  Oh well.

 

Answer 2

I'm getting the same error message, but starting-over doesn't help.
The logs are being generated on my secondary server.  All I need to do is for agent to pick them up and restore.  The instance "allows remote connections", and the agent account has access to the share.  What am I missing?

Answer 3

Found what was the issue.  When setting up Log Shipping on a cluster, even when you specify the proper instance Alias, when the jobs are generated on the secondary server, the actual primary Node Name\Instance name is entered automatically instead of the alias.  Hence, the log shipping may fail.  I opened both "copy" and "restore" jobs and changed the instance name manually in the parameters passed to the .exe.  This worked.

Answer 4

Hi LoveDanger,

We are planning to implement Log shipping in Active\Passive cluster setup, sql server 2005. We a SQL Group (which has D, E, F drives) and a Backup Group (Z drive for Backups) in Cluster Administration. As you already configured Log shipping in Clustered SQL Server 2005, could you please answer the following:

1. I have created a share "Tlogs" & given full control to the service account under which SQLAgent is running on Z drive as and given the backp settings option as  \\node1 name\Tlogs  in Logshipping wizard. Is this right or do I need to mention the virtual server name i.e \\virtualservername\Tlogs ?

--for me backup failed if I use \\node1 name\Tlogs & it's working if I use \\virtualservername\Tlogs. So what backup settings (backup path) you have used??

2. What happens when the SQL group moves to Passive node because of fail over? does the log shipping breaks?

-- for me log shipping braked. i.e the copy and restore jobs failing.

3. What happens when the Backup Group group moves to Passive node because of fail over? does the log shipping breaks?
-- for me log shipping braked. i.e the copy and restore jobs failing

4. If both SQL Group & Backup group moves to node2, then log shipping working fine.
 
I'm NOT using a network share on a separate server, created the share on node1 Z drive. and a share folder on Secondary server to store the copied tlog files.

how exactly Log shipping works in clustered sql server? Please provide me some documentation if you already prepared?

thank you very much

Answer 5

Found what was the issue.  When setting up Log Shipping on a cluster, even when you specify the proper instance Alias, when the jobs are generated on the secondary server, the actual primary Node Name\Instance name is entered automatically instead of the alias.  Hence, the log shipping may fail.  I opened both "copy" and "restore" jobs and changed the instance name manually in the parameters passed to the .exe.  This worked.

Hi,

has anybody had thi issue in SQL Server 2008 or have they found a bug number or KNB articel?

Thanks Chidd

Answer 6

Hi,

We are experiencing this issue too

Primary: sql2005 running on Windows 2003

Secondary1: sql2005 running on Windows 2003

Secondary2: sql2005 running on Windows 2008R2

We have tried recreating log shipping to Secondary2 using a fresh full backup from the primary but have found that both the copy and the restore job on Secodary2 report the same issue:

Step Name        Log shipping copy job step.
Duration        00:00:11
Sql Severity        0
Sql Message ID        0
Operator Emailed       
Operator Net sent       
Operator Paged       
Retries Attempted        0

Message
2011-01-27 11:25:49.26    *** Error: Could not retrieve copy settings for secondary ID '723285b1-07ff-4a0c-a59b-ecca7ee58330'.(Microsoft.SqlServer.Management.LogShipping) ***
2011-01-27 11:25:49.26    *** Error: The specified agent_id 723285B1-07FF-4A0C-A59B-ECCA7EE58330 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2011-01-27 11:25:49.27    *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2011-01-27 11:25:49.27    *** Error: The specified agent_id 723285B1-07FF-4A0C-A59B-ECCA7EE58330 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2011-01-27 11:25:49.28    *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2011-01-27 11:25:49.28    *** Error: The specified agent_id 723285B1-07FF-4A0C-A59B-ECCA7EE58330 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provi

Step Name        Log shipping restore log job step.
Duration        00:00:11
Sql Severity        0
Sql Message ID        0
Operator Emailed       
Operator Net sent       
Operator Paged       
Retries Attempted        0

Message
2011-01-27 11:30:11.04    *** Error: Could not retrieve restore settings.(Microsoft.SqlServer.Management.LogShipping) ***
2011-01-27 11:30:11.04    *** Error: The specified agent_id 723285B1-07FF-4A0C-A59B-ECCA7EE58330 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2011-01-27 11:30:11.04    *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
2011-01-27 11:30:11.04    *** Error: The specified agent_id 723285B1-07FF-4A0C-A59B-ECCA7EE58330 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2011-01-27 11:30:11.04    *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
2011-01-27 11:30:11.04    *** Error: The specified agent_id 723285B1-07FF-4A0C-A59B-ECCA7EE58330 or agent_type 2 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
2011-01-27 11:30:11.04    ----- END OF TRANSAC

Security permissions have been shown to be correct in a control experiment of the same log shipping set up.

Thanks.

Answer 7

This was fixed for us by the following steps:

deleting the logshipping configuration that was not working (Primary to Secondary2) from Primary first (it gave an error about not being able to connect to the Secondar2 server, and then from Secondary2...we deleted the copy & restore jobs.

Then we cleaned out the log-shipping tables on msdb database, giving the results:

use msdb

-- log shipping selects
select * from dbo.log_shipping_monitor_alert
-- blank
select * from dbo.log_shipping_monitor_error_detail
-- blank
select * from dbo.log_shipping_monitor_history_detail
-- blank
select * from dbo.log_shipping_monitor_primary
-- blank
select * from dbo.log_shipping_monitor_secondary
-- blank
select * from dbo.log_shipping_primaries
-- blank
select * from dbo.log_shipping_primary_databases
-- blank
select * from dbo.log_shipping_primary_secondaries
-- blank
select * from dbo.log_shipping_secondaries
-- blank
select * from dbo.log_shipping_secondary
-- blank
select * from dbo.log_shipping_secondary_databases
-- blank

Thereafter, start setting up log shipping as before, but before clicking on the final [OK] after configuring the backups, the Secondary/destination and the monitor servers, click on [Script configuration].

We found the following corrections were required:

1. if named instances are used, the script only had the virtual server name. Ensure full server names: [VirtualServer\InstanceName] in the script.

2. Backup location had \\servername\driveletter$\foldername but the account could not access this. After implementing the share, we changed this to \\servername\foldername

3. Changed @Monitor_server_security_mode=1 to 0 (from sql authentication to Windows authentication)...so the sql agent job of the secondary was used to access the retrieve the transaction logs from the primary.

... then create using the script rather than the GUI.

Answer 8

Hi there, I've had trouble copying the trn logs to my secondary server. When the job runs, it fails, with the error message: the network path not found.

The linked server seems to work fine, and it starts the transaction log copy.

Any advice gratefully received.

Thanks

Chris