April 7, 2009 at 11:52 pm
Hi,
We are planning to Configure logshipping for all the databases in an instance. In this we already have log backup schedule every 1 hr, differential at every 6hrs, full backup at 1:00am daily.
Now if we configure log shipping for all databases and transaction log backups taking place at every 15 mins. then do we need to disable the transaction log backups already scheduled for every 1hr?
how you guys do?
how actually we need to configure?
thanks for your help
April 8, 2009 at 4:42 am
kotlas7 (4/7/2009)
Now if we configure log shipping for all databases and transaction log backups taking place at every 15 mins. then do we need to disable the transaction log backups already scheduled for every 1hr?
You should take log backups from the same shared folder that you use for log shipping backups.
April 8, 2009 at 11:54 pm
So No need to take the regular Transaction log backups using the backup job for the databases involved in Logshipping right?
So I will disable the transaction log backup for the databases involved in Logshipping and continue with the Full backup ,differential and Logshipping transactional backup.
In my case, we have 16 databases and decided to use logshipping for all 16 databaes.
in this case,if I configure logshipping, there will be 48 jobs(3 jobs for each logshipping database) in primary instance and secondary instance right?
is this the way all DBA has to do or is there any way to reduce the no.of jobs created?
thanks for your inputs
April 9, 2009 at 1:44 am
Hi,
Correct, once you have configured log shipping to provide transaction log backups for all of your databases you will no longer need your other transaction log backup jobs i.e. those that may have been created by maintenance plans etc.
The reason why a seperate job is created for each transaction log backup is most likely for logical seperation and ease of maintenance. Each job belongs to a completely seperate log shipping configuration after all (i.e. there is a seperate log shipping configuration defined for each database).
Hope this makes sense but please feel free to post further questions.
Cheers,
April 9, 2009 at 2:46 am
kotlas7 (4/8/2009)
So No need to take the regular Transaction log backups using the backup job for the databases involved in Logshipping right?
Hi,
what jhon advised is true.
My two cents:
No just log shipping doesnt make your DR strategy safe. You should still have your backups schedule. You should take Tlog backups from the same shared folder that you have created for your Log shipping backup jobs that means you should create one more job to copy Tlog backup files from that shared folder to your local folder and then offload it to tape.
April 9, 2009 at 1:06 pm
thank you,
I have configured the log shipping as below
1.Take the full backup of the database,copy to secondary server and restored with NORECOVERY.
2.Schedules tlogs for every 15 mins on primary server.
3.While creating the restore transaction log backup(in the wizard), I choose delay restoring backup atleast as 30 mins.
4.But this restore job scheduled to run every 15 mins and its restoring the t-logs every 15 mins.
My Question is if choose the delay restoring backup atleast as 30 mins, do we need to change Restore schedule to run every 30 mins?
how exactly it works? Because by giving this delay Iam trying to do the fail over and role switch.
please advise me
thanks
April 9, 2009 at 2:10 pm
Once you have created the logshipping for a pair of Databases: DB1(on Primary Server [PS]) and DB2(on Secondary Server [SS]), there will be 1 T-log backup job created on PS, 1 Tlog copy job and 1Tlog Restore Job created on SS.
Logshipping, as its name suggests, is basically shipping the t-log derived from the PS to SS and get them restored and reflecting the changes to DB1 on the PS to the DB2 on the SS.
How does this work? You make changes to DB1-->DB1 generated T-log--> Your T-log backup job runs (at the time/frequency you want) and the T-Log is backedup-->> Your T-log Copy job runs and picked up the Logs backedup to SS-->>T-log restore Job runs and the t-log is restored to DB2 on your SS and the Changes are now reflected on your DB2.
On the Job Schedules:
With those three Jobs created, you can change their Schedules as you want, just like you would schedule any other job. After observing several rounds of running of those jobs, you can check the job history and the log-backup/copy created dates to see whether it is doing what you want. If not, you can adjust the job schedules accordingly.
Note: if you are configuring the secondary DB as Non-recovery, you will not be able to access it. As I am not exactly clear How you would do the fail-over and role switch, logshipping is not particularly designed for a failover solution.
April 9, 2009 at 4:18 pm
kotlas7 (4/9/2009)
thank you,My Question is if choose the delay restoring backup atleast as 30 mins, do we need to change Restore schedule to run every 30 mins?
Test and see what is acceptable according to your business model. Basically you are telling sql server not to restore jobs for 30 mins from the time they received the backup files.
I would advise you to test it and get authorized by your manager. Any problems gives us a shout.
April 9, 2009 at 4:26 pm
do you really need to log ship all 16 databases??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2009 at 5:50 pm
Yes, because its a share point application databases. If do not configure logshipping for all the databases and if there is a disaster, then we can not make the application to work properly, as we are thinking.
Please give me your suggestions.
March 25, 2010 at 11:29 pm
How do you make failure in primary server instance and make secondary server instance databases online
please provide your suggestions..
March 26, 2010 at 3:38 am
starterm (3/25/2010)
How do you make failure in primary server instance
Pull the network cable out!!!!
starterm (3/25/2010)
and make secondary server instance databases onlineplease provide your suggestions..
To bring secondary databases online, restore any remaining transaction logs and issue a WITH RECOVERY to bring the database(s) online
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 26, 2010 at 9:29 am
Yes, as Perry said, to make the secondary database online, it is a manual process. It is not an automatic one like the failover cluster.
And after the restore WITH RECOVERY, you may need to fix the logins on the secondary instance and reconfigure your applications to point to the secondary server/DB as well if that is the database for.
March 26, 2010 at 3:47 pm
scripting the login accounts from the primary to the secondary should take care of any SID mismatches. Or just use the SP_Change_Users_Login stored procedure
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply