Solved

Adding a Second Log Shipping Secondary Instance

Posted on 2014-02-28
7
521 Views
Last Modified: 2014-02-28
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.
0
Comment
Question by:Primedius
  • 4
  • 3
7 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 39895003
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
 

Author Comment

by:Primedius
ID: 39895067
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
 
LVL 39

Expert Comment

by:lcohan
ID: 39895234
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 39895329
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
 

Author Comment

by:Primedius
ID: 39895347
Wow, thanks for the validation.  Will try this afternoon.
0
 

Author Comment

by:Primedius
ID: 39896339
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
 

Author Comment

by:Primedius
ID: 39896407
No provision for backing up.  Hmm take offline and move files?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Minus first query 1 36
My Query is not giving correct result. Please help 5 30
SQL Server Question 5 28
Changing the datatype of a column from nvarchar to date 47 13
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now