SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Setup of High avilability DB Mirroring + Log shipping


Setup of High avilability DB Mirroring + Log shipping

Author
Message
SQL Galaxy
SQL Galaxy
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17567 Visits: 3517

Hi
In Local IDC High availability Database mirroring already configured since 5 years back with manual failover with no witness server. Now management decided to configure this DR setup with existing database mirroring setup.

TLOG backup configured every 15 min in principal database for controlling LDF file size growth and point in time recovery

we can 't extent one more 3rd node due to DB mirroring limit and support only tow nodes. And 3rd party vendor application restrictions I can't upgrade higher version SQL 2012 or 2016 for choosing alwayson HA new feature

I need to choose option Log shipping only for fulfill this DR requirement. So I want to know what is the time interval to be setting in TLOG backup setting in Log shipping scenario due to already TLOG backup configured every 15 min in principal database.

So, Can we choose two different timing custom TLOG backup and TLOG backup in log shipping setup
1. Custom TLOG backup at every 30 min
2. Log shipping TLOG backup at every 15 min
Will it cause of LSN chain brake in log shipping if above two backup times? Please suggest.

Thanks


anthony.green
anthony.green
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63396 Visits: 8598
Yes, if you have two processes backing up the transaction log it will cause a break in the LSN sequence and thus impact logshipping.

Setup logshipping to meet your companies RTO and RPO schedules, then disable anything else that could be doing TX log backups.

Note - If your using Ola Hallengren's backup solution it will automatically not backup a database that is configured for logshipping.



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138127 Visits: 18218
Please this Microsoft knowledge base article for more on mirroring and log shipping combined together

https://technet.microsoft.com/en-us/library/ms187016(v=sql.110).aspx

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
SQL Galaxy
SQL Galaxy
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17567 Visits: 3517
Thank you Mr. Perry and Mr. Anthony. for yours valuable comments
I will disable custom TLOG backup in Principal database server during configuration of log shipping.
Does Log shipping technique will take of LDF growth size? So normal TLOG backup will truncate unused log space in LDF file and reuse it again, that will be managed log growth rate. Please suggest.

RPO and RTO are 4 hours defined and approved by business side. for recovery server and database both.
My assumption data bit loss duration of 15 min because log shipping TLOG backup schedule at every 15 min.
If primary principal database server failure, then data available on both the location Mirror server as well as DR setup server with 15 min gap.

Thanks
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138127 Visits: 18218
SQL Galaxy - Friday, May 5, 2017 7:16 AM
I will disable custom TLOG backup in Principal database server during configuration of log shipping.

No!
Do not disable the backup job created by the log shipping configuration, remove the database backup from any other jobs that may run apart from the configured job.


SQL Galaxy - Friday, May 5, 2017 7:16 AM

Does Log shipping technique will take of LDF growth size? So normal TLOG backup will truncate unused log space in LDF file and reuse it again, that will be managed log growth rate. Please suggest.

Log truncation on the Log shipping Primary will be handled by the Log shipping configured log backup job.





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

"Ya can't make an omelette without breaking just a few eggs" ;-)
SQL Galaxy
SQL Galaxy
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17567 Visits: 3517
Thanks Mr. Perry.
I have gone through Microsoft Link and documents which is suggested by you.
In that architecture backup share server introduced and this backup share folder storing TLOG backup files which is access restoring TLOG files in stand by server.
Feature of backup share folder Log shipping will continue work if primary server down or planned downtime.
Please suggest as below points:
1. Should be backup share folder should be access on SQL service account?
2. where I have to create backup share folder in Primary location or remote location?
Thanks
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)SSC Guru (138K reputation)

Group: General Forum Members
Points: 138127 Visits: 18218
SQL Galaxy - Monday, May 8, 2017 10:41 PM
1. Should be backup share folder should be access on SQL service account?

The sql server service accounts should have access to the fileshare, as the jobs typically run under the agent service account



SQL Galaxy - Monday, May 8, 2017 10:41 PM2. where I have to create backup share folder in Primary location or remote location?
Thanks

It depends on your preference, a network fileshare accessible to all nodes makes sense too though


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

"Ya can't make an omelette without breaking just a few eggs" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search