Questions about SQL Clone

Erik.HT

I have a few questions that I was hoping could be answered. I'd like to evaluate SQL Clone for use in our enterprise, but need to understand if it can do certain things.

• How does SQL Clone handle cloning a database that has multiple filegroups spread across different drives?
• How large, typically, would the differencing disk need to be if adding a column to a table with 1 million records? (Obviously, I understand this will "depend" on various factors, just trying to figure out what actually gets written to the differencing disk)
• Would it essentially have to rewrite the entire table to the differencing disk?
• When updating a row, does it need to write the entire row to the disk, or just the changed column?
• What kind of performance penalty can we expect from the use of differencing disks? I'm not talking about running production with it, but more how will it affect testing performance.
• Does the "clone" need to be on the same server as the image?
• Is the image typically the same size as the "live" database?

NeilSmart

hi,

I can answer a few of the question from my testing of SQL Clone.

Does the "clone" need to be on the same server as the image? - No can be any where you like
Is the image typically the same size as the "live" database? - Yes the image is about the same size of the live database

Neil

Erik.HT

Thanks. I guess i'm a bit confused as to how SQL Clone saves "99%" of the disk space if you need 100% for the image. I get the other benefits, but it seems like this is just over stated. Yeah, if you're going to make 100 clones from the same image, sure, it saves a lot of disk space. But if you are making one.. not so much.

Mark R

Erik.HT wrote:

• How does SQL Clone handle cloning a database that has multiple filegroups spread across different drives?

You can create an image from a database with filegroups across different drives, they will all be copied to the image. An image is a single file, though, so the image itself can only be stored on a single drive.

Erik.HT wrote:

• How large, typically, would the differencing disk need to be if adding a column to a table with 1 million records? (Obviously, I understand this will "depend" on various factors, just trying to figure out what actually gets written to the differencing disk)
• Would it essentially have to rewrite the entire table to the differencing disk?
• When updating a row, does it need to write the entire row to the disk, or just the changed column?

The differencing disk contains any pages that SQL Server has written to. If SQL Server has to rewrite the table, or an entire row, then that will be stored in the differencing disk.

Erik.HT wrote:

• What kind of performance penalty can we expect from the use of differencing disks? I'm not talking about running production with it, but more how will it affect testing performance.

I don't think the differencing disks themselves impose any significant performance penalty - the main thing will be the underlying storage & network. If your storage & network setup for the images is similar to production, then I would expect the performance to be similar. If significant changes are made to the clone (such as adding a column to a large table, as you mention above), then the situation will get more complicated.

Erik.HT wrote:

Thanks. I guess i'm a bit confused as to how SQL Clone saves "99%" of the disk space if you need 100% for the image. I get the other benefits, but it seems like this is just over stated. Yeah, if you're going to make 100 clones from the same image, sure, it saves a lot of disk space. But if you are making one.. not so much.

That's fair - if you're only ever creating a single clone, then you don't save disk space overall. Although, if you have space on a network share for an image, but not on a local machine you want to clone to, then SQL Clone could still be advantageous from a disk space point of view by changing where the data is stored.

Erik.HT

Can SQL Clone be automated, such as part of a DLM automated deployment?

EDIT: Nevermind, I found the Powershell reference in the documentation...

GrahamH

For reference for others: https://documentation.red-gate.com/display/CLONE1/Automation

SkreebyDBA

Can SQL Compare be run against a SQL Clone?

Mark R

Yes - a SQL Clone functions just as a normal database.

PMass

I know this is an old thread, but I have a followup to the original poster's question.

Erik.HT asked about imaging databases with multiple filegroups and files spread across multiple disks.  My followup question is, is there a way to exclude specific filegroups or files before the image is actually created?  I have found that any database with a defunct file prevents me from cloning that database.

Thank you.