Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Log shipping secondary not showing new results from primary Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
Hi all,

Just set up log shipping. I get no errors from the agent jobs and can see the files in the secondary restore location. But when i do query i dont see any new info.. Been running for 4 hours (Log shipping hourly). Ive seen it, where it updates show up an from the 1st log file when you do the second etc. but the no results is puzzling to say the least.. any ideas?

Post #1350919
Posted Tuesday, August 28, 2012 3:43 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
Carefully check the history of the copy and restore jobs, my guess is you'll have some error messages on the restore job.

Do you have any trans log backups runnin on the primary database as part of a maint job?


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1351306
Posted Wednesday, August 29, 2012 3:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
Hi,

Yes had a brain melt..

realized that i still had my ola scripts still doing backup logs... Ive removed and started again and seems to be working now.

It did give me some questions though.

What would happen if the WAN went down and a log file was skipped. Would log shipping still work? and it just skip that 1 hour of activity? or does it stop.

If it stops. How do you get it unto date (Manual restore of log files?)

If it just skips (which i wouldn't have thought it would) how do you put in the old log files.

Thanks for the reply.
Post #1351486
Posted Wednesday, August 29, 2012 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
n00bDBA (8/29/2012)
Hi,

Yes had a brain melt..

realized that i still had my ola scripts still doing backup logs...

That's pretty much what i was expecting



n00bDBA (8/29/2012)
What would happen if the WAN went down and a log file was skipped. Would log shipping still work? and it just skip that 1 hour of activity? or does it stop.

This depends on how long it was down for and the file retention period set on the primary.
If the WAN was down for 4 hours but the log backups are removed on the primary after 2 hours then yes, when the WAN came back the restore job would complain because the log backups its recently collected and trying to restore have a gap.



n00bDBA (8/29/2012)
If it stops. How do you get it unto date (Manual restore of log files?)

If it just skips (which i wouldn't have thought it would) how do you put in the old log files.

Thanks for the reply.

If you get to this point and there are log backups missing due to the scenario mentioned above do the following;

Disable all LS jobs for this database (i.e. backup, copy and restore)

take a differential backup on the primary

restore the differential to the secondary

clear all log backups from the primary and secondary locations

Re enable LS jobs (backup, then copy, then restore)


If you get to this point and all the log backups are in place on the primary then just run the copy job followed by the restore job.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1351642
Posted Thursday, August 30, 2012 4:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
Hi Perry thanks!

Can i just confirm im understanding it right.

CASE 1 - logs retained on Primary and secondary for 24 hours - 1 hour LINK down

10:00 - Log backup of Primary
10:01 - Copy log to Secondary
10:05 - restore log to Secondary
10:10 --- WAN LINK DIES...
10:30 - Log backup of Primary
11:00 - Log backup of Primary
11:10 --- WAN LINK RESTORED --
11:30 Log backup Primary
11:31 - copy of 10:30, 11:00 and 11:30 to Secondary
11:35 - Restore of 10:30, 11:00 and 11:30 to Secondary

CASE 2 - logs retained on Primary and secondary for 24 hours - Multi day LINK down

Fri 10:00 - Log backup of Primary
Fri 10:01 - Copy log to Secondary
Fri 10:05 - restore log to Secondary
Fri 10:10 --- WAN LINK DIES ---
Tue 9:00 ---- WAN LINK RESTORED ---
Tue 9:01 - Disable all Log Shipping Jobs
Tue 9:05 - Differential Backup of Primary
Tue 9:10 - Restore Differential backup to Secondary
Tue 9:15 - Move all Log files from Primary backup location and Secondary
Tue 9:20 - Enable all Log shipping Jobs
Tue 9:30 - Log backup of Primary
Tue 9:31 - Copy log to Secondary
Tue 9:35 - Restore log to Secondary
Tue 9:40 - Return log file to backup locations for Primary and Secondary


Thanks for the help!
Post #1352106
Posted Thursday, August 30, 2012 5:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
n00bDBA (8/30/2012)
Hi Perry thanks!

Can i just confirm im understanding it right.

CASE 1 - logs retained on Primary and secondary for 24 hours - 1 hour LINK down

10:00 - Log backup of Primary
10:01 - Copy log to Secondary
10:05 - restore log to Secondary
10:10 --- WAN LINK DIES...
10:30 - Log backup of Primary
11:00 - Log backup of Primary
11:10 --- WAN LINK RESTORED --
11:30 Log backup Primary
11:31 - copy of 10:30, 11:00 and 11:30 to Secondary
11:35 - Restore of 10:30, 11:00 and 11:30 to Secondary

CASE 2 - logs retained on Primary and secondary for 24 hours - Multi day LINK down

Fri 10:00 - Log backup of Primary
Fri 10:01 - Copy log to Secondary
Fri 10:05 - restore log to Secondary
Fri 10:10 --- WAN LINK DIES ---
Tue 9:00 ---- WAN LINK RESTORED ---
Tue 9:01 - Disable all Log Shipping Jobs
Tue 9:05 - Differential Backup of Primary
Tue 9:10 - Restore Differential backup to Secondary
Tue 9:15 - Move all Log files from Primary backup location and Secondary
Tue 9:20 - Enable all Log shipping Jobs
Tue 9:30 - Log backup of Primary
Tue 9:31 - Copy log to Secondary
Tue 9:35 - Restore log to Secondary
Tue 9:40 - Return log file to backup locations for Primary and Secondary


Thanks for the help!

Before looking at any of this wee need to know the thresholds you have set on the LS config for the database in question, can you supply these?


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1352122
Posted Thursday, August 30, 2012 6:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
hey,

the delete old log backups on primary and secondary is set to 72 hours.. Is that what you mean? I don't remember any other thresholds..

thanks :)

EDIT...

think before you type really needs to be drilled into my head! used the log shipping SP and saw a few others that makes more sense..

backup_retention_period - How Long the Log backup is kept on the Primary Server (I picked 72 hours)
backup_threshold - 120 - Will trigger an alert if a log backup isnt performed within this time period? (I picked 1 hour 15 mins if ive set backups to 1 hour.. Would this effect things..

On Secondary I can see
restore threshold - set an alert if no restore is done within this time frame (for my 1 hour backups i set it to 75 mins again
file retention_period which i again set to 72 hours.


Another question this brings up (Keep questioning if im looking at this right!) Ive set the Log backups on the Primary to be every hour from 00:30
The Copy ive set to be 10 mins after this (just to make sure the file has been created)
The restore ive left as default which is 15 mins.. since i didnt know how long the copy would take and having it keep trying seems a safer bet.

Does that seem right or is it foolish!?

Thanks for the help (And patience!)
Post #1352158
Posted Thursday, August 30, 2012 7:28 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
n00bDBA (8/30/2012)
Hi Perry thanks!

Can i just confirm im understanding it right.

CASE 1 - logs retained on Primary and secondary for 24 hours - 1 hour LINK down

10:00 - Log backup of Primary
10:01 - Copy log to Secondary
10:05 - restore log to Secondary
10:10 --- WAN LINK DIES...
10:30 - Log backup of Primary
11:00 - Log backup of Primary
11:10 --- WAN LINK RESTORED --
11:30 Log backup Primary
11:31 - copy of 10:30, 11:00 and 11:30 to Secondary
11:35 - Restore of 10:30, 11:00 and 11:30 to Secondary

CASE 2 - logs retained on Primary and secondary for 24 hours - Multi day LINK down

Fri 10:00 - Log backup of Primary
Fri 10:01 - Copy log to Secondary
Fri 10:05 - restore log to Secondary
Fri 10:10 --- WAN LINK DIES ---
Tue 9:00 ---- WAN LINK RESTORED ---
Tue 9:01 - Disable all Log Shipping Jobs
Tue 9:05 - Differential Backup of Primary
Tue 9:10 - Restore Differential backup to Secondary
Tue 9:15 - Move all Log files from Primary backup location and Secondary
Tue 9:20 - Enable all Log shipping Jobs
Tue 9:30 - Log backup of Primary
Tue 9:31 - Copy log to Secondary
Tue 9:35 - Restore log to Secondary
Tue 9:40 - Return log file to backup locations for Primary and Secondary


Thanks for the help!

72 hours, so

in case 1 the copy and restore jobs would not have any affect as the copy job would not be able to access the primary backup share (you'd probably get an error). After an hour when the link is back up, the copy job would see the new files on the primary backup share and copy them across to the secondary. The restore job will restore any new backups that it finds.

in case 2 the same thing would happen in that the copy job would not be able to access the primary backup share. Now, if it's a failure that you are aware of you would stop the 2 jobs on the secondary or just leave them to run expecting no action and manually retain the log backups by adjusting the purge threshold. If you're not aware then the jobs will just run as usual and do nothing anyway.
Assuming you aren't aware of the failure do this

Tue 9:00 ---- WAN LINK RESTORED ---
Tue 9:01 - Disable all Log Shipping Jobs
Tue 9:05 - Differential Backup of Primary
Tue 9:10 - Restore Differential backup to Secondary
Tue 9:15 - Move all Log backup files from Primary backup location and Secondary
Tue 9:20 - Enable all Log shipping Jobs
Tue 9:30 - execute LS Log backup job on Primary
Tue 9:31 - execute LS Copy job on Secondary
Tue 9:35 - execute LS Restore job on Secondary

LS should carry on as usual


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1352208
Posted Thursday, August 30, 2012 8:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 4:29 AM
Points: 119, Visits: 451
Thanks sir

clears up a lot of questions i had.. and not really related to the starting question so thanks for sticking with me!



Post #1352242
Posted Thursday, August 30, 2012 8:15 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:55 AM
Points: 6,752, Visits: 14,397
it all depends on whether the failure is known or not. Try it on a test system you'll see how it works

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1352253
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse