clone for dev?

sand

Hi! Thinking about SQL Clone for dev teams.. they now use copies of anonymized prod DBs.
Is this still doable with SQL Clone?
How would SQLClone perform on databases of size around 50-200GB, when deploying changes from a db project to its clone?
For example when (and after) deploying a substantial schema change to a dev instance (a clone) (forcing table re-creation) for a table with millions of rows, will the developers notice the overhead when accessing it?

AlexYates

Put a timeout on your deploy process and treat this as an early warning that whatever change you just made might deserve special attention.

Rebuilding a table and accidentally blowing away a clone is annoying, but relatively easy to fix. The main point is that it might one day save you from unexpectedly doing it on production.

AlexYates

Whatever bits you are changing on disk will be saved on the clone differencing disk. Hence, expect table recreations on big tables to have a proportional affect on the size of the clone. If a table is bigger than the available harddrive space, don't expect it to work.

However, as long as you are not doing table rebuilds etc it should work fine. And as long as you treat source control as god and your clone as a disposable playground for dev/test. you shouldn't need to worry too much about the occasional clone breaking. If it does, respawn and try again.

As for the anonymisation - that should work fine. You can either use the pre-anonymised version to create your source image or, if you have either a Redgate Masking Set or a plain T-SQL script to anonymise the data, you can use the production database/backup (backup recommended obvs) and run your masking script as part of the image creation.

Don't try to run data anonymisation scripts on your clones. If you are masking a large amount of data your clones will grow proportionally large. Run your masking scripts on the source database or during image creation.

sand

Thank you for answering. My fear is that when there is a change in the table design that forces a table recreate when deploying to a clone, that this would be too slow if the table has millions of rows.. how would such a scenario best be handled when using clones in dev?