none
Database Mirroring and Logshipping which one is best in high availability? which one we preferred?

Réponses

  • In  Logshipping and Mirroring which one is best and which one we preferred.

    Thanks,

    Nag.


    Running SQL server under Hyper-V with configured failover clustering between guest VMs and keeping all data live on CSV put on HA shared storage. That's the way to go with a minimum downtime. Everything else is a compromise between downtime and how much money / human resources you're willing to spend on uptime.

    -nismo

    lundi 5 mars 2012 11:22
  • It depends  on your business requirements (for example with Log Shipping there is no automatic fail over)

    http://msdn.microsoft.com/en-us/library/ms190202.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    lundi 5 mars 2012 06:42
  • Mirroring
    Database mirroring is limited to only two servers.
    Mirroring with a Witness Server allows for High Availability and automatic fail over.
    You can configure your DSN string to have both mirrored servers in it so that when they switch you notice nothing.
    While mirrored, your Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode. 

    Log Shipping

    You can log ship to multiple servers.
    Log shipping is only as current as how often the job runs. If you ship logs every 15 minutes, the secondary server could be as far as 15 minutes. Making it more of a Warm Standby.
    You can leave the database in read only mode while it is being updated. Good for reporting servers.
    Good for disaster recovery

    Ref: http://stackoverflow.com/questions/317438/mirroring-vs-log-shipping-in-sql-server-2005

    Which is the best? It depends upon your business need as also suggested by Uri.


    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    mardi 6 mars 2012 03:42
  • Hi Nagaraju,

    A given database can be mirrored or log shipped; it can also be simultaneously mirrored and log shipped. To choose what approach to use, consider the following:

    How many destination servers do you require?

    If you require only a single destination database, database mirroring is the recommended solution.

    If you require more than one destination database, you need to use log shipping, either alone or with database mirroring. Combining these approaches gives you the benefits of database mirroring along with the support for multiple destinations provided by log shipping.

    If you need to delay restoring log on the destination database (typically, to protect against logical errors), use log shipping, alone or with database mirroring.

    Reference:
    Database Mirroring: http://msdn.microsoft.com/en-us/library/ms189852.aspx.
    Log Shipping: http://msdn.microsoft.com/en-us/library/ms187103.aspx.

    Thanks,
    Maggie
     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    mardi 6 mars 2012 09:55
    Modérateur

Toutes les réponses

  • It depends  on your business requirements (for example with Log Shipping there is no automatic fail over)

    http://msdn.microsoft.com/en-us/library/ms190202.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    lundi 5 mars 2012 06:42
  • In  Logshipping and Mirroring which one is best and which one we preferred.

    Thanks,

    Nag.


    Running SQL server under Hyper-V with configured failover clustering between guest VMs and keeping all data live on CSV put on HA shared storage. That's the way to go with a minimum downtime. Everything else is a compromise between downtime and how much money / human resources you're willing to spend on uptime.

    -nismo

    lundi 5 mars 2012 11:22
  • Hello,

    Consider Database Mirroring for Disaster Recovery purposes http://msdn.microsoft.com/en-us/library/cc917680.aspx 

    Or SQL 2012 AlwaysOn Feature http://www.microsoft.com/sqlserver/en/us/future-editions/mission-critical/SQL-Server-2012-high-availability.aspx


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    lundi 5 mars 2012 13:16
  • Mirroring
    Database mirroring is limited to only two servers.
    Mirroring with a Witness Server allows for High Availability and automatic fail over.
    You can configure your DSN string to have both mirrored servers in it so that when they switch you notice nothing.
    While mirrored, your Mirrored Database cannot be accessed. It is in Synchronizing/Restoring mode. 

    Log Shipping

    You can log ship to multiple servers.
    Log shipping is only as current as how often the job runs. If you ship logs every 15 minutes, the secondary server could be as far as 15 minutes. Making it more of a Warm Standby.
    You can leave the database in read only mode while it is being updated. Good for reporting servers.
    Good for disaster recovery

    Ref: http://stackoverflow.com/questions/317438/mirroring-vs-log-shipping-in-sql-server-2005

    Which is the best? It depends upon your business need as also suggested by Uri.


    Read my blog (Blog)
    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    mardi 6 mars 2012 03:42
  • Hi Nagaraju,

    A given database can be mirrored or log shipped; it can also be simultaneously mirrored and log shipped. To choose what approach to use, consider the following:

    How many destination servers do you require?

    If you require only a single destination database, database mirroring is the recommended solution.

    If you require more than one destination database, you need to use log shipping, either alone or with database mirroring. Combining these approaches gives you the benefits of database mirroring along with the support for multiple destinations provided by log shipping.

    If you need to delay restoring log on the destination database (typically, to protect against logical errors), use log shipping, alone or with database mirroring.

    Reference:
    Database Mirroring: http://msdn.microsoft.com/en-us/library/ms189852.aspx.
    Log Shipping: http://msdn.microsoft.com/en-us/library/ms187103.aspx.

    Thanks,
    Maggie
     

     


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.

    mardi 6 mars 2012 09:55
    Modérateur
  • Hello,

    Consider Database Mirroring for Disaster Recovery purposes http://msdn.microsoft.com/en-us/library/cc917680.aspx 

    Or SQL 2012 AlwaysOn Feature http://www.microsoft.com/sqlserver/en/us/future-editions/mission-critical/SQL-Server-2012-high-availability.aspx


    Javier Villegas | @javier_villhttp://sql-javier-villegas.blogspot.com/

    Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    The biggest issue with database mirroring is performance. Having all syncs offloaded to "smart" shared storage increases number of transactions SQL server can handle. We've seen 30-50% better response time on a real heavy patterns. So on OP place I'd give a try to a very different configs referenced here to pick up the one working for him in the best way (pretty obvious).

    -nismo

    mardi 6 mars 2012 15:46
  • Seriously nismo? Wherever I use Mirroring and Clustering in tests Mirroring always is by far faster... And that has a pretty good reason: When you use clustering every single write needs to be commited by a SAN storage, so you have to go accross a very thin wire for every write. With mirroring only the transaction commit needs to be confirmed from the sync secondary. everything else runs async.

    Comparing Logshipping to mirroring... It's all a matter of how much data you are willing to loose, what infrastructure you have and how many copies you need. Comparing those two I would always opt for mirroring, IF you only need one copy. When comparing Async Mirrors to LogShipping there really is not much advantage in LS, other than the fact that Mirroring only supports one copy.

    jeudi 8 mars 2012 23:38
  • Seriously nismo? Wherever I use Mirroring and Clustering in tests Mirroring always is by far faster... And that has a pretty good reason: When you use clustering every single write needs to be commited by a SAN storage, so you have to go accross a very thin wire for every write. With mirroring only the transaction commit needs to be confirmed from the sync secondary. everything else runs async.

    Comparing Logshipping to mirroring... It's all a matter of how much data you are willing to loose, what infrastructure you have and how many copies you need. Comparing those two I would always opt for mirroring, IF you only need one copy. When comparing Async Mirrors to LogShipping there really is not much advantage in LS, other than the fact that Mirroring only supports one copy.

    You're comparing apples to oranges. Go run proper SAN software on both clustered nodes and you'll have the same network load and ACK reported immediately after transaction is written to cache on your second node. What's the point? I've been assuming OP keeps actual database on the same storage and you're basically comparing raw DAS Vs. SAN speeds (sick!). Of couse DAS is a winner... Thank you very much for sharing with us :)

    -nismo

    vendredi 9 mars 2012 00:36
  • Sorry if you didn't like my comment Nismo, but the discussion was around logshipping and mirroring so it was only prudent to point out the advantage of mirroring compared to failover clustering. And I might point out that you started the discussion around bad performance of mirroring, so it would only be fair to treat this statement as abusive then too, wouldn't it?

    Lucifer

    vendredi 9 mars 2012 18:51
  • Sorry if you didn't like my comment Nismo, but the discussion was around logshipping and mirroring so it was only prudent to point out the advantage of mirroring compared to failover clustering. And I might point out that you started the discussion around bad performance of mirroring, so it would only be fair to treat this statement as abusive then too, wouldn't it?

    Lucifer

    1) I'm not sure if somebody wants a solution and proposing A and B options as a possible answer himself we cannot offer him C (unless he specially told he's locked in A or B).

    2) I'd prefer to leave ability to choose to OP rather then you.

    2) I'd prefer to talk technically on technical forums rather then going philosophy.

    -nismo

    P.S. You're doing self-moderation in a forum you're not moderator in.

    vendredi 9 mars 2012 19:32
  • Then let us hope that the answer to the original question is in the comments provided so far. (And I'm sorry, but I think I provided that answer in my first post anyway, but that's certainly not mine to decide.)

    And I am certainly not trying to moderate, just trying to keep the discussion down to a technical level, that's what we are here for after all... But I very much hope that a moderator will step into this discussion at some point and decide what is in fact abusive to the topic and what not... Which is not mine to decide either, but so it isn't yours...

    vendredi 9 mars 2012 19:56