locked
SQL 2008 SP1 - Mirroring and log shipping problem. RRS feed

  • Question

  • Hello All !

    This is the context (in production):

    3 servers for a DB mirroring solution (S1 for "principal", S2 for "mirror", S3 for "Witness"): they run ok (good tests when failovers planned and unplanned)

    A DRP log shipping was added for this site: S1 (which is the principal for mirroring) is the "primary" server for log shipping and at the other side S4 is the "standby" and S5 is the "monitor log shipping": ok . Log shipping had been tested: ok.

    But, I have a problem:

    When failover occurs on the mirroring : S2 becomes the "principal" but it stops the log shipping, because the log shipping is set to the DB on S1.

    So how can i do ? When mirror occurs from S1 to S2, I want log shipping to continue working from S2 and when S1 is coming back, log shipping continue to run.

    Is it possible to do that ?

    If YES:

    - create a log shipping solution with S2 as "primary" when S2 (secondary) has failover

    - disable logshipping jobs on S2 and S4 respectively

    When S2 has failovered, S1 will become the principal, all log shipping jobs for S1 runs.

    When S1 has failovered, S2 will become the principal, disable all log shippng jobs for S1, enable log shipping job for S2.

    Is it possible to do this ? I know that is a bit complex .....

    So how to start / stop automatically a job when failover occurs ?

     

     

     


    Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2
    Sunday, October 17, 2010 9:43 PM

Answers

All replies

  • refer http://msdn.microsoft.com/en-us/library/ms187016.aspx
    Balmukund Lakhani | Please mark solved if I've answered your question
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Sunday, October 17, 2010 10:03 PM
  • Hello Balmukund !

    Thanks for your response. I read this link, but I does not run.

    1) - I failover on the secondary server in order to have access to the DB

    2) - I run the Logshipping wizard and I configure the logshipping

    3) - When the copy job is running, I have this error message:

    ----------------------------------------------------------------
    Date  03/11/2010 11:52:53
    Log  Job History (LSCopy_ServerM011_MgmtEur)
     
    Step ID  1
    Server  ServerM001
    Job Name  LSCopy_
    ServerM011_MgmtEur r
    Step Name  Log shipping copy job step.
    Duration  00:00:16
    Sql Severity  0
    Sql Message ID  0
    Operator Emailed  
    Operator Net sent  
    Operator Paged  
    Retries Attempted  0
     
    Message
    2010-11-03 11:53:08.94 *** Error: Could not retrieve copy settings for secondary ID '36726713-8c46-48e8-b819-bb6118dcb612'.(Microsoft.SqlServer.Management.LogShipping) ***
    2010-11-03 11:53:08.94 *** Error: The specified agent_id 36726713-8C46-48E8-B819-BB6118DCB612 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
    2010-11-03 11:53:08.95 *** Error: Could not log history/error message.(Microsoft.SqlServer.Management.LogShipping) ***
    2010-11-03 11:53:08.95 *** Error: The specified agent_id 36726713-8C46-48E8-B819-BB6118DCB612 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
    2010-11-03 11:53:08.95 *** Error: Could not cleanup history.(Microsoft.SqlServer.Management.LogShipping) ***
    2010-11-03 11:53:08.95 *** Error: The specified agent_id 36726713-8C46-48E8-B819-BB6118DCB612 or agent_type 1 do not form a valid pair for log shipping monitoring processing.(.Net SqlClient Data Provider) ***
    2010-11-03 11:53:08.95 ----- END OF TRANSACTION LOG COPY     -----
     
    Exit Status: 1 (Error)
    ------------------------------------------------

    After investigation on the Web, I don't find a solution. Most of the articles talk about cluster instances, adding 2 new standby servers, modifying rows into system msdb tables....

    Privileges are ok, mirroring is good, first logshipping with the principal is all right. I have this only if I want to use the secondary server when I failover the solution.


    Thanks for advance for your ideas / help - Regards - Have a nice day ! RHUM2
    Wednesday, November 3, 2010 1:59 PM