January 29, 2013 at 12:42 pm
Hello all,
I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional logs every 1 hour. Both the databases are running on SQL Server 2008 R2.
According to my understanding every changes that are commited on the Primary Server should reflect on the Secondary Server. Today I ran Redgate SQL Compare to compare the two databases and discovered 38 objects which are inconsistent between the two servers. So I waited for 1 hour for the next restore to be completed on the secondary server. Even after all the log backups have been restored to the Secondary server, the two databases are not identical.
I created a test table on the primary database to see whether it will be created by the restores on the Secondary server, but it is not created even after many restores..
Hourly backup is taken without any errors, and None of the transactional logs on the Secondary server is failing.
Is there any fault on my Log shipping setup ? Where to start troubleshooting ?
What might be the case ? Please suggest..
January 31, 2013 at 9:11 am
sdpages (1/29/2013)
Hello all,I have a SQL log-shipping High availability setup from a Primary Server to a Secondary Server which ships transactional logs every 1 hour. Both the databases are running on SQL Server 2008 R2.
According to my understanding every changes that are commited on the Primary Server should reflect on the Secondary Server. Today I ran Redgate SQL Compare to compare the two databases and discovered 38 objects which are inconsistent between the two servers. So I waited for 1 hour for the next restore to be completed on the secondary server. Even after all the log backups have been restored to the Secondary server, the two databases are not identical.
I created a test table on the primary database to see whether it will be created by the restores on the Secondary server, but it is not created even after many restores..
Hourly backup is taken without any errors, and None of the transactional logs on the Secondary server is failing.
Is there any fault on my Log shipping setup ? Where to start troubleshooting ?
What might be the case ? Please suggest..
Check the jobs carefully and inspect the output of each step, i'm betting that the restore job is showing errors restoring files. Post back what you find.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 31, 2013 at 9:52 am
also the restore job will report success even if it did not actually restore any logs because it could not find any with correct LSNs.
It sounds as if your logs are not restoring. Check your sql errorlog for evidence of logs restoring and run this to tell you last log copied and restored
select * from msdb.dbo.log_shipping_monitor_secondary
---------------------------------------------------------------------
February 6, 2013 at 9:15 am
Hey, ya'll are right guys. Looks like the Restore jobs on the Secondary database is not restored successfully.
( Eventhough it shows that job run successfully )
The sql event log shows that the trn files are skipped while the trying to Restore. I am pretty positive that it has to do something with the transactional log sequence number.
The day when I did this log shipping setup there was a log backup job ( our original log backup from the maintenance plans ) still running parallel to our new log shipping transactional log backup . So as soon as I realized that there were two transactional log backups running side by side and interfering with each other's lsn's I disable/deleted the original log backup. But I guess I was too late.
Now, I am pretty positive that deleting all transactional log backups and setting up the log shipping from the beginning will solve this problem.
I will keep it posted...
thanks for all the help.
February 6, 2013 at 4:01 pm
Have you taken any full backups since LS broke, if not you may restart LS with a differential backup
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 7, 2013 at 8:39 am
Yes , that's what i thought . But I take Full backups every 3 days and diffs everyday. And the LS is out of sync for like a week now. It may be easier for me to reconfigure the whole log-shipping setup. Doesn't take much time though.
Is there anyway i can restore the last full backup + last diff backup + all the trans log backups to the Secondary log shipped database and put it back to sync.
Right now the database is in Standby/Read only mode.
Appreciate the help...
February 7, 2013 at 8:59 am
BTW, you don't need to reconfigure logshipping you can use your existing jobs and set up, just reinitialise the backup.
You would be best pausing log jobs, taking a full backup (in copy_only mode if you are worried about your diffs) and just restore that to the secondary in norecovery mode, then re-enable your log shipping jobs. Clear down the log directory on the secondary first if you want.
---------------------------------------------------------------------
February 7, 2013 at 9:06 am
george sibbald (2/7/2013)
BTW, you don't need to reconfigure logshipping you can use your existing jobs and set up, just reinitialise the backup.You would be best pausing log jobs, taking a full backup (in copy_only mode if you are worried about your diffs) and just restore that to the secondary in norecovery mode, then re-enable your log shipping jobs. Clear down the log directory on the secondary first if you want.
+1
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 7, 2013 at 9:56 am
Very much appreciate George and Perry.
February 7, 2013 at 10:13 am
Guys,
Is the full restore possible on the Secondary Database while it is being Replicated ?
My secondary database for this log shipping is replicated. So do i have to disable the replication before I restore the Full Backup on this database ?
February 7, 2013 at 10:22 am
well thats out of left field.
I don't do replication but I would have thought you would have to reinitialise it from snapshot.
---------------------------------------------------------------------
February 7, 2013 at 12:06 pm
Ok, Guys the The Restore was successful and the Log shipping is back on sync. I can confirm it by looking at the restore log on the Log File Viewer.
It says that the Restore Operation was successful.
I have a question though. The database is now on "Restoring Mode" since we used "NO RECOVERY"
Any idea how to bring it back to Standby Mode since we want to use this for Read purposes ?
thanks.
February 7, 2013 at 12:49 pm
I have restored the last transactional Log backup with the option of STANDBY on SSMS. The database is now back to ReadOnly / STANDBY. Now I users will be able to read from this database and the Log shipping is IN-SYNC.
thanks for all the help guys.
Appreciate it. I will put detailed steps here shortly in case anyone needs it later.
February 7, 2013 at 1:37 pm
thanks
---------------------------------------------------------------------
February 8, 2013 at 3:02 pm
Hi again,
Recently I found two of the databases (Primary and Secondary) on my log shipping were Out Of Sync.
Below are the steps I performed to bring it back to Sync:
1. Identify the SQL Agent Job on the Primary Database server which is taking Transactional Log Backups of your Database and Disable it. ( This is a very critical step to take in a 24X7 environment , So I would recommend this process to be performed on a non-busy period ; like midnight or so. So that you loose minimum data.)
2. Identify the SQL Agent Jobs on the Secondary Database server that are Copying and Restoring Log Backups. Disable them.
3. After Disabling the Agent jobs, Take a Full Backup of the Primary database and Restore it on the Secondary Database, option WITH NO RECOVERY mode. You won't be able to do this in SSMS GUI since the Secondary database is on a STANDBY/READ-ONLY mode and the RESTORE DATABASE options are disabled on the tasks>Restore . USE the T-SQL code instead to perform this step .
( Even a recent Differential backups is enough to perform this Restore if a Full backup has not been taken after the LS OFF-Sync )
4. After a fresh Restore has been performed on the Secondary Database, Execute sp_who2 and see if there are any users that are connected to the Secondary Database. Kill the SPIDs connecting to the Database if there are any.
Don't have to do this if you have selected "Disconnect users in the database when restoring backups" while you setup the Log Shipping.
5. Re-Enable the SQL Agent Job on the Primary Server that was taking Transaction Log Backup. Right click and click "Start Job At Step".
6. Clear all transactional log files (trn) from the Secondary server's Restore Folder.
7. Re-Enable SQL Agent Jobs on the Secondary Database server for Copying and Restoring Log Backups. Right click on the Copy job and click "Start job at Step".
Wait for the .trn files to be copied to the Restore Folder.
After that, Right click on the Restore Job and click "Start Job at Step".
8. You may want to manually restore the last transactional backup file to the Secondary Database with the option (RESTORE WITH STANDBY) on SSMS.
Log shipping should now be back IN-SYNC and ready to READ by users. Use Log File Viewer on the Secondary Server to confirm that Restores are Not Failing.
Look on the "Message" on the Log file summary , It should say something like this; "The restore operation was successful. Secondary Database ....... Number of log backup files restored: ##some number "
You may see some latency between the Live Database and the Secondary Database.
May not apply to all environments.
==============================
Some Info:
Primary: SQL SERVER 2008 R2 Enterprise SP2
Secondary: SQL SERVER 2008 R2 Enterprise SP2
Secondary Database mode: STANDBY/READ-ONLY
Transactional Log Backup: Every 30 mins interval . This is the only log backup taken for the whole database. Running two parallel log backups at same time , BAD IDEA !!
Database size: 400GB
Total Time taken : 1 hour.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply