Log Shipping for DR


  • Good morning,

    I'm just looking for some opinions really and would love to hear anybodies anecdotes or ideas.

    We currently have a couple of servers which have discovered do not have an adequate DR plan around them and are considering our options.

    The issue?  Firstly, we are on SQL 2005/2008 R2 for these boxes (and cannot upgrade them) and secondly, not all of our tables have primary keys (and we cannot alter the table structures as they run third party products).

    The only real options I can see are backup/restore and log shipping.  What I'm not sure about is the limitations of log shipping with regard to the number of databases on a system.  Some of our servers have 20+ databases on.  Is there some guidelines saying "Well, you should only really be log shipping up to 5 databases off any server?" or would it be fine (apart from the management overhead) to just log ship the lot?  (I am deliberately ignoring transaction log sizes here as I think that is a different discussion).

    I've considered transactional replication, but again, I dont know if the volume of changed data would cause an issue.  Are there limitations on the number of publications/tables which might be "too many".  I'm not sure that replicating 921 tables with quite volatile data is such a great idea.  (and that's just one database).  Transactional replication also requires primary keys on tables, and, as mentioned, not all tables have this, nor can we alter the underlying structure to have this.

    I've also considered mirroring, but am worried that, due to the size of the databases that any indexing tasks would simply flood the transaction logs to being massively out of kilter, so I'm sort of eliminating that option

    Anyway, any advice or thoughts would be greatly appreciated.


    Wednesday, April 25, 2018 8:51 AM

All replies

  • Let me address step by step

    Logshipping: I am not aware about any such limitation that LS will work fine only with 5 databases or so this is plain absurd. I have had 25 databases in LS on server supported by good hardware and network and they all worked just fine. The advantage with LS is that is very easy to manage and troubleshoot, you also have read only secondary server which can be used for running read only queries. 

    Mirroring: This is also a good solution but is resource intensive as compared to LS, I am not saying that you cannot use but level of expertise need to handle LS is much less than mirroring and going further it will be deprecated. Mirroring is bit complex when it comes to maintaining it also you do not have read only mirror servers. There are inherent issues with mirroring which I am aware about but cannot actually write them down. Its more advanced for Availability group is now running very good.

    Replication: Yes replicating 900+ table is bit of task but this should also work fine again you know the restriction of PK so you might be able to only replicate table with PK. The biggest advantage is you can have both servers read write and can use various replication topology to suit your need. 

    My personal opinion I just love LS, if you need a warm DR where you just want to run few select queries LS is best.



    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles


    Wednesday, April 25, 2018 9:15 AM
  • I need this solution.thanks

    Wednesday, April 25, 2018 9:17 AM