|
|
|
SSC Veteran
 
Group: General Forum Members
Last Login: Wednesday, November 18, 2009 12:05 AM
Points: 270,
Visits: 821
|
|
Hi,
From BOL,
Performing the initial role change
To set up a failover to the secondary database, perform the following steps:
Disable the log shipping backup job on the original primary server.
Disable the copy and restore jobs on the original secondary server.
Perform a manual failover from the primary database to the secondary database.
Use SQL Server Management Studio to configure log shipping on your new primary server and log ship to the remaining secondary servers. When doing so, you must ensure the following:
Use the same share for creating backups as that of the original primary server.
Use the original primary database name when adding the secondary database in the Secondary Database Settings dialog box.
Select the No, The Secondary Database Is Initialized option in the Secondary Database Settings dialog box
In above, what happens if we do NOT have the same share for creating backups as that of the original primary server? Is it MANDATORY for a role change?
Because, we have the back share local to the primary server..and I cannot use the same backup share while initialize Log shipping from Secondary to Primary in process of role change. Instead I can use another backup share on secondary & configure log shipping But I want to know is that supported or not?
Changing server roles
You can change the roles of the primary database and the secondary database by performing the following steps:
1)Bring the secondary database online after making a backup of the transaction log on the primary
server by using the NORECOVERY option.
2)Disable the log shipping backup job on the original primary server.
3)Disable the copy and restore jobs on the original secondary server.
4)Restore the backup on the secondary server by using the RECOVERY option.
5)Enable the log shipping backup job on the new primary server.
5)Enable the copy and restore jobs on the new secondary server.
In above, the backup taken at step1(tail backup of primary) should be applied at step4 right?
thanks
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
First question: yes that's perfectly fine, though you'll need to manually copy the tail-log backup from the old share to the new share. That assumes that log shipping was up-to-date at the time of the role change. If not, you'd also need to copy any unapplied log backups as well. Going forward, you'd need an automated process to copy logs from one share to the other. All this is only if you decide to use the SSMS integration to control your log shipping. It is quite straightforward to 'roll your own' custom log shipping - there's no magic involved in copying logs from one server to another and applying them.
Second question: Yes, that's right.
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, November 18, 2009 12:05 AM
Points: 270,
Visits: 821
|
|
Thank You,
One more question:
After performing role change, second set of backup job on New Primary(previously Secondary) & copy and restore jobs will be created on New Secondary (previously Primary). Now the DR test is done & I want go back, I mean I want original Primary as Primary & original secondary as secondary and continue the log shipping as it used to run before performing the DR test. In this case, what to do with the second set of jobs that were created while role change?
thanks
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 4:15 AM
Points: 2,069,
Visits: 2,040
|
|
Only one set of jobs will be enabled, depending on which server is primary.
I would encourage you to try this out in practice (you can log ship from and to the same test server!) to make sure you fully understand what is going on.
Once you have mastered how SQL Server handles log shipping for you, it should be easy for you to see how to control the whole process yourself - and which option suits your needs better. If you can make the SSMS integration work for you, it is generally less work. We only wrote our own because we use a proprietary backup compression tool.
Paul
The quality of the answers is directly proportional to the quality of the question.
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, November 18, 2009 12:05 AM
Points: 270,
Visits: 821
|
|
Only one set of jobs will be enabled, depending on which server is primary.
I have performed role change and the new backup, copy restore jobs were created & enabled by default after role change using Management studio.
But do I need to Disable the LSAlert job before role change & enable after role change? How exactly we need to deal with these LSAlert jobs on Primary & Secondary after role change? It should be enabled or disabled?
I would encourage you to try this out in practice (you can log ship from and to the same test server!) to make sure you fully understand what is going on.
Once you have mastered how SQL Server handles log shipping for you, it should be easy for you to see how to control the whole process yourself - and which option suits your needs better. If you can make the SSMS integration work for you, it is generally less work. We only wrote our own because we use a proprietary backup compression tool.
In my test machine, I have used Management studio to perform role change as I'm not familiar with Log shipping stored procedures. Everything is working fine after role change but I,m getting the below error when I see the Transaction Logshipping status report from Management studio(InstanceName->Right click->Standard reports->Transaction Logshipping status):
Error
Violation of Primary Key constraint 'PK_#logship_mo_1EA29017. cannot insert duplicate key in object db0.#log_shipping_monitor
I did NOT configure any separate monitoring server(I did not select the Monitor server instance option while configuring log shipping)
so please tell me from which table, what exactly I need to delete to remove this duplicate key in msdb
thanks so much
|
|
|
|
|
SSC Veteran
Group: General Forum Members
Last Login: Wednesday, November 18, 2009 12:05 AM
Points: 270,
Visits: 821
|
|
Could you help me on above issue?
thanks
|
|
|
|
|
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 9:24 PM
Points: 1,337,
Visits: 6,589
|
|
There are few tables named '%logshipping_monitor%' tables in msdb tht you need to check for duplicate entry.
MJ
|
|
|
|
