Database Mirroring or Log shipping on different server

  • Paresh: Your cut and paste was in reference to High Avail style mirroring

    Perry: You were talking about automatic failover.

    Don't do either one with a normal internet type connection!

    My posts were concerning actual experience with High Performance (no wait on read commit) style mirroring with manual failover; compared to actual experience with Log Shipping (always manual failover).

    There is zero concern about spurious failovers with HP Mirroring with no witness server, because just like log shipping it's done manually when you want it. It works amazingly well over a distance and has great tools for you to monitor status with. There is little to no performance penalty on either server.


    Student of SQL and Golf, Master of Neither

  • My Producation databasae is huge and if it stop for few minuteds, it can create problem with us and alos users.

    so i am confused for if i have to keep performanance then i have to lost failover time and

    if i have to reduce failover time then i have to loss perfomanance.

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • I confuse to choose high availability or high performamance...?

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Resolve my confusion please..it's important for me.

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Can Transactional Replication solve problem?

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • My dbsize is 10 GB.In replication all tables(around 1000 tables ) of database will be replicated , it is viable for performaence?

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • With regards to mirroring:

    High Availability is "synched writes". The user's change is written to the primary server. The primary notifies the secondary. The secondary also does the write. The secondary notifies the primary. Finally, the user's query is considered complete. I would only use this mode if the two servers are on the same high speed network AND you feel that you NEED to make certain that every single change is duplicated.

    High Performance in "delayed secondary writes". Not delayed much though. The user's change is written to the primary server. The change is immediately considered complete (from the user's point of view). Then the copy to secondary server takes place.

    HA = true duplication at the expense of performance. HP = true duplication after a few seconds or milliseconds, or minutes if the change is large and the connection is slow. If you have a failure after the user "leaves" and before the changes are transfers, then the user believes all is well though the secondary does not have 100% of all changes. For my purposes, for a disaster recovery site this is perfectly acceptable.


    Student of SQL and Golf, Master of Neither

  • Thanks guy,

    Can u tell me , my database is fully utilized, around 500 users connected to databases .when transaction occur then the changes write to primary server and how long it take time to write on secondary server.

    I it take long time then is it better then log shipping , bec in shipping performennce is not degrade and in disaster we can recover database with small amount of data lose witch is same as recover time and data loss of mirroring with async mode.

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • I can't tell you "how long it will take" to write to the secondary server without a LOT of details. Nobody can. But I CAN tell you this: Under any circumstances of load, under any method of setting up Log shipping, the delay in doing writes will be less with HP mirroring than with log shipping for one very simple reason. With LS, the data is only copied to the secondary every 15 minutes (by default). Of course you can set it for less or more, but not less than once a minute. With Mirroring, the log files are sent to the secondary immediately with no artificial delay.


    Student of SQL and Golf, Master of Neither

  • SO,as per your side, Mirroring with Sync mode is best..

    For my large DBs and Long different location, Mirroring with sync mode is viable?

    Yes, we can get high avaibality, but what about High performanence..are they comared to best with Log shipping for HP or HA?

    Which option are suit or satisfied?

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Reading this thread one thought comes to mind: Paresh, you REALLY need to hire an experienced person to help you both evaluate what form of High Availability will meet your company's requirements (perhaps even help you define what those requirements really are) and also help implement and test the plan that is developed. You are truly setting yourself and your company up for failure since you have a lack of knowledge and experience. That isn't a personal condemnation, just an observation based on this thread. I note as well that there are some really bad pieces of advice contained within this thread too and I hope you don't base your company's HA/DR plan on a few forum posts from entities of unknown skills and training.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • hi,

    I know that both HA and HP requirement may not be satisfied..

    But i want to say that my application is working on individual instances.

    But as per disucussion with company i have decided implemente

    active - passive cluster on those instance with automatic failure for disater recovery.

    Means each machime have two instances, one is default and another is virtual instace..

    and those are cross move when failure occuer..

    suppose machine 1 has occure failure, it will transfer to machine 2 's virtual instace. and vise versa

    is it ok?

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Hi i can get both HA and HP from Active/Passive clustring .

    But this very costly in comparision to Mirroring/Shipping.

    in this ,When one instance is fail it is auto tranfer to virtual instance of other machine.

    like senario

    machine1 machine2

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

    Def instance def ins..

    virtual ins... virtual.

    when def ins. fail on machine1 then inst is tranfer to virtual ins of machine2

    and vice-versa.

    But what about when both instance def ins of machine1 and virtual ins of machine2 fail?

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Can you please explain me the difference between Active/Active clustering and Active/Passive clustering.

    I am very confuse.

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Paresh,

    High availability is very important I agree, but High performance is more important.

    To be honest, what are the chances that you will ever use your DR server. ( 0.0000000 what ever )

    Why you want to kill Performance of application by choosing high availability mode in Database Mirroring.

    Microsoft guys are very intelligent, they have provided High Availability mode of Database Mirroring in Standard Edition, but they did not provide High performance mode in Standard Edition, High performance is only available with Enterprise/Developer Edition.

    Microsoft is no fool, to sell very important feature for low price ... it does means that Performance is what every company looks at.

    I would go with Log shipping in the battle of database mirroring and log shipping because it is more robust. Less overhead on the server comparing to Database Mirroring.

    I am not sure how database mirroring will provide High Availability for web application, for client server I agree because of .NET technology it might be possible to have a high availability solution using database mirroring if your application uses only one database.

    If you are really planning for High availability mode then go for clustering.

    This is my view...might be correct, chances are more to be wrong !!!

    Regards

    IM.

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply