July 21, 2006 at 4:11 pm
Hi, I am doing some testing on role reversal for log shipping. I am able to do the role reversal with no issues, by following the steps outlined in Books Online. While I am able to make the secondary server become the primary server, I can't figure out how to make the old primary server become a secondary server. If I view the properties of the maintenance plan on the secondary server(new primary server), there are no options available to start shipping the logs anywhere. Books Online does state: You must add the former primary server as a secondary server to the new primary server to establish a log shipping pair between the two databases, but it does not tell you how to go about doing this.
Is anyone aware of a programmatic way of doing this, since the gui does not let you? Am I missing something obvious here? I want to avoid starting over every time we do a role reversal (dropping the database on the old primary server, creating a new maintenance plan on the new primary server, and letting a new maintenance plan create a new database on the old primary server). Taking this approach would be very time consuming with larger databases. I'm sure there must be a better way to handle this that I am not aware of...
Any help would be greatly appreciated...
Melissa
July 22, 2006 at 4:14 am
Hi Melissa,
When you perform the role change and you execute the procedure sp_change_primary_role you should specify a value of 2 for the @final_state parameter. This will put the primary database (which is to become the secondary) into norecovery mode. This will enable the database to accept new transaction logs to be restored. You could use an @final_state value of 3 to put it into standby mode.
Once you've done the role change, on the new primary you will need to set up a new transaction log backup plan and set up log shipping.
Because you've left the old primary database in norecovery mode you won't need to go through the process of copying or creating a new database. When setting up log shipping you get the option to log ship to an existing database - which only works if that database is in norecovery or standby mode.
Hope that helps,
July 24, 2006 at 7:04 am
Thanks for the tip Karl. I was using 2 for the @final_state parameter.
Since the log shipping wizard creates a maintenance plan on the secondary server when everything is initially set up, I was trying to find a way to add log shipping to that existing maintenance plan when I reversed the roles. As you suggested, I will just create a new maintenance plan and select the existing database. That should work out perfectly fine.
Melissa
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply