Cloning SQL Server Instances with Containers

Question

Cloning SQL Server Instances with Containers

pauls 72822

Ten Centuries

Points: 1049
More actions
December 31, 2018 at 12:03 am

#376707

Comments posted to this topic are about the item Cloning SQL Server Instances with Containers
January 2, 2019 at 4:53 am

This was removed by the editor as SPAM

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

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

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.

Change is inevitable... Change for the better is not.

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

pauls 72822 Ten Centuries Points: 1049 More actions · Answer 2

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: 1004406 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

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

pauls 72822 Ten Centuries Points: 1049 More actions · Answer 4

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: 1004406 More actions · Answer 5

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.

Change is inevitable... Change for the better is not.

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

pauls 72822 Ten Centuries Points: 1049 More actions · Answer 6

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