Cloning SQL Server Instances with Containers

  • pauls 72822

    Ten Centuries

    Points: 1041

    Comments posted to this topic are about the item Cloning SQL Server Instances with Containers

  • Jeff Moden

    SSC Guru

    Points: 994266

    Great introduction to containers.  Thanks for taking the time to post it.

    The thing that I don't understand is, considering that it starts out with a restore of the database and is then followed by the application of SQL scripts to customize and obfuscate, how is this any better than just doing a restore and then running those same scripts?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • pauls 72822

    Ten Centuries

    Points: 1041

    When working with database clones the backup restore process is a one-time event to build the image (>docker build -t <imagename> c:\path\to\backups.   Once the image is built, the subsequent clones are delivered in seconds, with minimal storage required (<40 MB).   The advantage of a one-time restore followed by "copies on demand" is where cloning is valuable.   The typical use-case is for supporting isolated environments for development and test.

  • Jeff Moden

    SSC Guru

    Points: 994266

    pauls 72822 - Monday, December 31, 2018 9:45 AM

    When working with database clones the backup restore process is a one-time event to build the image (>docker build -t <imagename> c:\path\to\backups.   Once the image is built, the subsequent clones are delivered in seconds, with minimal storage required (<40 MB).   The advantage of a one-time restore followed by "copies on demand" is where cloning is valuable.   The typical use-case is for supporting isolated environments for development and test.

    Ah... got it.  Thanks for the amplification. 

    So, to be sure, the data for the database isn't actually materialized at the cloned instance.  Does that make these cloned databases "read only" or can they actually be Inserted/Updated/Deleted and only those changes are materialized and only on the cloned instance?  Is the latter a correct understanding on my part?

    Also, since you said the clone would occupy <40MB, where is the data in the database read from when data is read?  Obviously I'm concerned about the possibility of a "bottle neck" and I'm also concerned with the unplanned materialization of data on the cloned instance.

    I'm also concerned about "backups" of development code.  Having such a thing has saved our hinnies more than once (we treat backups on Dev boxes as seriously as we do prod).

    And, to say the least, your good article has piqued more than just a bit of interest on my part.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • pauls 72822

    Ten Centuries

    Points: 1041

    While the source image is "read only," each database clone supports full read/write, with a Copy on Write behavior.  So, backups can be implemented on the Dev machines if you choose.   The deployment architecture will also influence how dev work is protected.    In the case of VHDs, a backup can be a copy of the clone itself (generally much smaller than the source image, and effectively capturing any changes made), or the clone can be used to generate a new Full backup of the source databases.  Another approach to cloning uses Storage Arrays, and in that case the backup approach would be to snapshot each developers clone rather than running a backup.  But, you could also create a backup here as well.

  • Jeff Moden

    SSC Guru

    Points: 994266

    Awesome.  I've been so far away from the hardware and VM stuff, I feel a bit idiotic about it all.  Thanks to your article and your posts. You've inspired me to learn more about that end of the business and have given me enough information to at least know what to look for.  Thanks for your time, Paul.  I really appreciate it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • pauls 72822

    Ten Centuries

    Points: 1041

    Happy to help, and thanks for the positive feedback.  -- Paul

  • This was removed by the editor as SPAM

Viewing 8 posts - 1 through 8 (of 8 total)

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