November 3, 2009 at 4:24 pm
Hi,
I have configured Log shipping in SQL Server 2005 as below:
Primary insatnce : Server A
Primary database: Mydb (online)
Secondary instance: ServerB
Secondary database: Mydb (restore mode)
We have NO Monitor Server. Backup share is on Primary Server called "LS_Backups" and for Copy, we have a one more share on Secondary called "Copy_LS_Backups"
Now, I'm testing failove and role switch scenarios.
For failover performed the below steps:
1. Disabled the Backup job on Primary & Copy and Restore job on secondary including alerts jobs on Primary & Secondary
2. Performed the Tail Log backup mydb_Tail.trn for Mydb and it is in the share LS_Backups on Primary.
3. Copied backup files from the backup share (from primary server) to the copy destination folder on secondary server.
4. Applied the unapplied transaction log backups in sequence to the secondary database Mydb (Except the tail log backup, mydb_Tail.trn , because I did NOT understand whether I need to apply tail log to the Mydb or not. Please advice here.. ) and brought the secondary database online
5. Transferred the logins & jobs
Now, for role switch I performed the below steps:
1.The Primary database is already in NORECOVRY mode
2. From the secondary(after bringing it to onlne), I have configured log shipping. Now the Backup share is on Secondary called "Copy_LS_Backups" and for copying to "LS_Backups" share on Primary
3. At this step I can Say the Role swithc has happened right? I can say The original primary as seconday & Original secondary as Primary
4. On new Primary, the backup job is working fine.On new Secondary, Copy job is working But the Restore job failing with the below error message:
Skipped log backup file. Secondary DB: 'Mydb', File: '\\serverA\LS_Backups\Mydb_20091103221500.trn'
Could not find a log backup file that could be applied to secondary database 'Mydb.
2009-11-03 14:20:01.67The restore operation was successful. Secondary Database: 'Mydb, Number of log backup files restored: 0
and the LSAlert jobs on Primary & Secondary giving the below error messages about the other databases has exceeded thresholds(I have role switched only Mydb database here but there are other databases aslo configured in log shipping and in process of failover, I have disabled the LSAlert on Primary & Secondary and once Failove is done, I have enabled them again after configuring Log shipping back from Secondary to Primary for Mydb Database.But I did NOT configure Log shipping agin from Secondary to Primary for the rest of the database but the LSAlert on both Primary & Secondary giving the errors about other database threshold. )
And when I see the Log shipping report "Transaction log shipping status" from the Primary or Secondary, it showing an error like below:
Error:
Violation of PRIMARY KEY constraints 'PK_#log_shipping_mo_25869641'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'.
The statement has been terminated.
Questions:
Is that mean, we must have a separate Monitoring server, when we want to perform role switch? How do deal with these LSAlert jobs(what to do with them), after failover & role swich?
Applied the unapplied transaction log backups in sequence to the secondary database Mydb (Except the tail log backup, mydb_Tail.trn , because I did NOT understand whether I need to apply tail log to the Mydb or not. Please advice here.. ) and brought the secondary database online ?
If we do not have a separate Backup share on Network and have the backup share on Primary server, then when performing role switch, how to deal with backup share?
From BOL,
3.On your secondary database (the database you want to be the new primary), configure log shipping using SQL Server Management Studio, Include the following steps:
oUse the same share for creating backups that you created for the original primary server.
Here I'm NOT using the same share for creating backups that you created for the original primary server.Is that a MUST rule?? because we do NOT have a backup share on Separate computer. We have Backup share on Primary server itself.
How to create (using Management studio) a new LSAlert job on Primary & Secondary after the failover/role switch is done and when configuring Log shipping back from the secondary to Primary?? Is that required? because, LSAlert giving alert about the Backup threshold & Restore threshold exceeded error. We already know that we disabled that backup job on primary, and Copy & Restore jobs on Secondary after making Primary as secondary & Secondary as primary right? Ideally LSAlert should NOT give these error messages right?
thanks for your inputs
November 4, 2009 at 2:16 am
4. Applied the unapplied transaction log backups in sequence to the secondary database Mydb (Except the tail log backup, mydb_Tail.trn , because I did NOT understand whether I need to apply tail log to the Mydb or not. Please advice here.. ) and brought the secondary database online
That bit was vital... it contains the last lot of changes to the original Primary.
Not restoring it now means that the log chain has been broken. The new secondary is trying to restore from a transaction log backup and isn't finding the LSN it is expecting.
By way of further explanation...
Suppose the tail backup from your primary contained LSN 10. You have now switched the primary into NORECOVERY, so it is expecting the next restore to be from LSN 11 onwards.
By not restoring the tail on the secondary, it only has LSN 9 as the last one it restored. After switching roles, and making it the primary, the first log backup you produce will be LSN 10... which is not what the new secondary is expecting.
That's what this message is telling you
Skipped log backup file. Secondary DB: 'Mydb', File: '\\serverA\LS_Backups\Mydb_20091103221500.trn'
Could not find a log backup file that could be applied to secondary database 'Mydb.
2009-11-03 14:20:01.67 The restore operation was successful. Secondary Database: 'Mydb, Number of log backup files restored: 0
I don't use the built-in monitors, but at a guess, this error message
Error:
Violation of PRIMARY KEY constraints 'PK_#log_shipping_mo_25869641'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'.
The statement has been terminated.
may also be something to do with not restoring the tail log.
Because the log chain has been broken, you are going to have to remove and reinstall log shipping for that database.
Have a look at the system stored procedures sp_change_primary_role and sp_change_secondary_role... you're better off using these to do the role swirching.
November 4, 2009 at 11:25 am
Thanks Ian,
I'm able to bring the Secondary online by applying the tail backup of primary(Primary is in Revovery mode now) then again configured log shipping from Secondary to Primary. Its working fine.
Now I want to have the The Original Primary as Primary & Original secondary as Secondary(before the start of DR test) and continue log shipping from Primary to Secondary as usal.
Because there are many backup, copy restore jobs were created while failover\roleswitch and I want cleanup everything except the jobs we have before DR test.
Is that Possible?
I don't use the built-in monitors, but at a guess, this error message
Error:
Violation of PRIMARY KEY constraints 'PK_#log_shipping_mo_25869641'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'.
The statement has been terminated.
When I initially configured log shipping, I did NOT select any Monitor server for Log shipping and aslo during failover/role switch, I did NOT select any Monitor server.
Please advice me how to to get rid of this below error:(This error is coming when I open the Transaction log shipping status report from object explorer)
Error:
Violation of PRIMARY KEY constraints 'PK_#log_shipping_mo_25869641'. Cannot insert duplicate key in object 'dbo.#log_shipping_monitor'.
The statement has been terminated.
November 4, 2009 at 3:11 pm
I ran into the same exact issue with the PK error message when testing DR. What apparently happens is that since you did not gracefully uncheck log shipping from that instance at some point, there is now invalid data in some of the log shipping tables. in my case in one of the log_shipping_ tables (sorry I cannot give the list of them at the moment) there were 2 entries for the secondary server one valid one and one invalid one. I had to delete the invalid rows from these log_shipping_ tables and that resolved my problem. Ah it is now disturbing that I can't find my notes on the exact ones I deleted from as it took me awhile to figure this problem out. There are only about 10 tables starting with log_shipping_ and these are located in the msdb database. Have a look into some of those and you will eventually find the duplicate information. check both sides of the DR as well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply