Adding a Second Log Shipping Secondary Instance

Hi All,

I already log ship my main databases to another local server and now I need to also log ship to another physical DR location via a slower WAN connection.

My plan was to bring over the full backup of these databases ( which are quite large) and restore with standby in preparation to start log shipping to the DR site.

My question is this: how do I handle the incremental transaction log changes?  Normally when doing this for the first secondary instance I restore a full backup and then a current trans log backup before starting log shipping, I imagine doing that for a second instance will create an LSN problem for the existing log shipping instance?  Do I do a differential instead?

Thanks for the help.
PrimediusAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
I would think to use the Secondary log shipped database as source and set that one to Log ship to " another physical DR location via a slower WAN connection."
Not sure what the purpose is for the secondary database in your existing Log Shipping but I think by creating a chain rather than log ship from one single source would alleviate the problems may be caused on the primary source database.
0
PrimediusAuthor Commented:
Hmm, never thought of that. Is that something which is commonly done?  You can log ship from a database which is in standby as these will be?
0
lcohanDatabase AnalystCommented:
I'll test right now the copy only backup on 2008 R2 Standby database.
Please see more details about different ways to "log ship" as highlighted in the picture/notes at the end of the article - I know is slightly different chain with Transactional Replication first but that one is just another Log Shipping method.

http://sqlmag.com/database-high-availability/3-log-shipping-techniques

Other options would be to use Transactional Replication with Pull Subscribers so the slow one will not affect the Publisher and the fast Subscriber which is not the StandBy Log Shipoped database(s).

Also check out the last solution just before last posting in this article and see if that helps.


http://www.sql-server-performance.com/forum/threads/backing-up-a-log-shipping-destination-database.17746/
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

lcohanDatabase AnalystCommented:
Actually I just set it all up in my 2008 R2 chained exactly as mentioned SQLDEV1 my OLTP database as Primary to Secondary in SQLDEV2 as standby and that one I used as Primary to log ship to SQLDEV3 Secondary in stand by mode as well.

I just wasn't 100% if what I did a while ago is still valid but yes, it is all good so far and working well to setup your stand by database as Primary to another T-log Shipping Secondary. I know I did this a while ago to offload the prod OLTP that was log shipped to a warm standby before clustering and from that to the reporting server which was in different data center from different location and with slower network connection.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PrimediusAuthor Commented:
Wow, thanks for the validation.  Will try this afternoon.
0
PrimediusAuthor Commented:
A little confused. On the secondary do I do a full backup and then a trans backup to restore on the third server to get the database in place to start log shipping.
0
PrimediusAuthor Commented:
No provision for backing up.  Hmm take offline and move files?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.