SQL Server Performance Forum – Threads Archive

Problem on Log Shipping role change

Hello all, There are two sql 2000 servers on one domain. The primary server (Srv1) hosts a db1 and the secondary server(Srv2) copies and restores transaction logs on itself. All work fine. When I decide to change server roles I do it with MS scripts. Then my Srv2 becomes new primary server and Srv1 new secondary. Now I need to add the Srv1 as destination server on the new primary server Database Maintenance plan and when I click the add button the problem begins. I choose Srv1 as destination server, I can set other options, but when I click OK button the following error rises: "A connection could not be made to any Log Shipping destination servers. You must register at least one destination server before continuing"
Any ideas? Thanks

Take help of this KBAhttp://support.microsoft.com/default.aspx?scid=kb;en-us;314515 about log shipping FAQ and pay attention on the role changes section. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks satya for your reply. I’ve read this FAQ before my post there and I did it once more after yours recomendation, but it didn’t help me. All work fine if I delete and recreate DB Maintenance Plan newly after the first change of roles instead of adding the new secondary server to the existing DB Maintenance plan on the new primary server. I just wanted to see how does it work when the "Allow database to assume Primary role" check box was selected, but unfortunately I allways see the same error.
Maybye it happens because I run sp_change_primary_role with a @final_state of 3 not 2 as it was shown in an example? I do so because when I put 2 in this parameter, my new secondary DB comes into the loading state. And the second thing I don’t resolve logins because they are always the same and with the same SIDs Thanks again

Try adding primary server as the Linked server in Secondary server and vice-versa, I guess there could be a network naming resolution issue to connect while performing the role swapping. With regard to transferring and exporting logins, I suggest review BOL which recommends building a SQL Server 2000 DTS package to transfer logins from the primary server to the secondary server and to resolve login SIDs across distinct servers. (The DTS Transfer Logins task, which you use to transfer the logins, is available only in SQL Server 2000 DTS.) The package execution transfers the logins from one server to the other, but it doesn’t resolve their login SIDs. To export the logins to the secondary server, the BOL article recommends that you create a two-stage SQL Server Agent job: bcp out and copy. In the first step, you export the logins to a file by using bcp in native mode. In the second step, you copy the logins to a file on the secondary server that you can use later for resolving logins during the role change. At that point, you use the sp_resolve_logins stored procedure to resolve login SIDs on the secondary server. After you create the job, you can run it at regular intervals (e.g., nightly) to keep an up-to-date exported file of logins on the secondary server in case you need to make a log shipping role change. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya, it really helped me. Now it works fine without any errors Thanks again

What step actually fixed your problem? I am having this exact same issue and I have tried the above steps and nothing is working. I need help!

Refr to the steps defined above, by adding the servers are linked servers and check if any issue exists on the network when the log shipping is working. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>