locked
Database snapshots or SAN snapshots RRS feed

  • Question

  • I would like to take multiple snapshots of my OLTP databases to be used for different purposes.. for reporting, HA, setting up non-prod environments,etc..

    I am thinking SAN based snapshots because I believe you can do writeable snapshots and cannot do so with database snapshots.

    I want to setup local HA.. but want to preserve against human error..i.e. accidentally deleting some data.. I dont want it to immediately propogate to the secondary server. I believe only Log shipping can give you that.. or maybe replication with some timed distribution agent... Looking at some options to use my standby server for multiple purposes..

     

     

    Sunday, November 20, 2011 2:40 AM

Answers

  • So database snapshots are an excellent way to gain great read concurrency for long running reports as discussed in my presentation READPAST & Furious (see around 50 minutes), you can use a technique called rolling snapshots which I also discuss to create (and destroy them) on demand. You could even use a mirrored database to roll your snapshots against meaning you are scaling away your reads and writes.

    Snapshots could also be used as a fast recovery mechanism to a point in time but ARE NOT a protection against media failure.

    The functionality of SAN snapshot can vary upon your SAN - but obviously should you have writeable snapshots then you will still have to hook this up to a SQL instance. Not a big deal but is a little more manual effort -and as you have correctly stated you will not protect yourself from Human error.

    For the scenario you propose, Replication would be a very good choice since it would meet all your requirements and could (if you so wish) provide the ability to read/write using PEER-PEER replication or Up-datable subscribers.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Proposed as answer by Janos BerkeMVP Sunday, November 20, 2011 9:45 AM
    • Marked as answer by Peja Tao Friday, November 25, 2011 1:43 AM
    Sunday, November 20, 2011 8:35 AM
  •  

    A couple additional thoughts.. of course this all really depends on exactly what san snapshot technology you are referring to but....

    Keep in mind when taking SAN snapshots of a production database, depending on the san vendor/technology io is frozen on the db for a point in time, this can affect application performance of a live system. For safety reasons, I have only performed this against a log ship destination db, not live production.  It depends on the nature of your application, though

    SAN snapshots take extra disk space as you are creating a mirror of your SQL data and log drives and then breaking the mirror (basically)

    The SAN snapshot drives will need to go offline to resync the drives when you want to refresh, this can take some time while db snapshots are fairly instanteneous

    With SAN snapshots being writable, this is a GREAT option if you want to test data fixes against "near real time" production data and validate them before running datda updates against your production system.....

    There is not really a single right or wrong answer here, but my vote would be to use a  delayed log shipping approach. It is fairly low tech and simple to manage, and you can log ship in standby mode to allow for some reporting off of the destination db as well (but NO data writes).

    • Marked as answer by Peja Tao Friday, November 25, 2011 1:43 AM
    Monday, November 21, 2011 1:04 PM

All replies

  • So database snapshots are an excellent way to gain great read concurrency for long running reports as discussed in my presentation READPAST & Furious (see around 50 minutes), you can use a technique called rolling snapshots which I also discuss to create (and destroy them) on demand. You could even use a mirrored database to roll your snapshots against meaning you are scaling away your reads and writes.

    Snapshots could also be used as a fast recovery mechanism to a point in time but ARE NOT a protection against media failure.

    The functionality of SAN snapshot can vary upon your SAN - but obviously should you have writeable snapshots then you will still have to hook this up to a SQL instance. Not a big deal but is a little more manual effort -and as you have correctly stated you will not protect yourself from Human error.

    For the scenario you propose, Replication would be a very good choice since it would meet all your requirements and could (if you so wish) provide the ability to read/write using PEER-PEER replication or Up-datable subscribers.


    Regards,
    Mark Broadbent.

    Contact me through (twitter|blog)

    Please click "Propose As Answer" if a post solves your problem
    or "Vote As Helpful" if a post has been useful to you
    • Proposed as answer by Janos BerkeMVP Sunday, November 20, 2011 9:45 AM
    • Marked as answer by Peja Tao Friday, November 25, 2011 1:43 AM
    Sunday, November 20, 2011 8:35 AM
  •  

    A couple additional thoughts.. of course this all really depends on exactly what san snapshot technology you are referring to but....

    Keep in mind when taking SAN snapshots of a production database, depending on the san vendor/technology io is frozen on the db for a point in time, this can affect application performance of a live system. For safety reasons, I have only performed this against a log ship destination db, not live production.  It depends on the nature of your application, though

    SAN snapshots take extra disk space as you are creating a mirror of your SQL data and log drives and then breaking the mirror (basically)

    The SAN snapshot drives will need to go offline to resync the drives when you want to refresh, this can take some time while db snapshots are fairly instanteneous

    With SAN snapshots being writable, this is a GREAT option if you want to test data fixes against "near real time" production data and validate them before running datda updates against your production system.....

    There is not really a single right or wrong answer here, but my vote would be to use a  delayed log shipping approach. It is fairly low tech and simple to manage, and you can log ship in standby mode to allow for some reporting off of the destination db as well (but NO data writes).

    • Marked as answer by Peja Tao Friday, November 25, 2011 1:43 AM
    Monday, November 21, 2011 1:04 PM