SQL Clone for static databases

rmg1

Hi all

We have a server (call it server A) where we store our main databases.
On there are four databases A1, A2, A3, A4.
Databases A1 and A2 are production databases, databases A3 and A4 are test databases.

What we would like to do is take a image/clone of each of the databases and server it to a different server.  For example:-
A1 and A2 would go to server B
A3 and A4 would go top server C

The databases on server A are being continuously written to.

We would not want the image/clone to grow at any point until we need to take a new image/clone.

Is this possible?

PlantBasedSQL

Hi @rmg1 thank you for your post,

Once the Image has been taken from the respective databases they are immutable - meaning they are completely removed from the workflow of Production/Test and do not grow with additional information.

The process would be: Images of the A databases would go onto Windows File Share and then Clones would go onto Server B and C. If you did no work on or made no changes to the Clones then these would not grow (only the differences are stored locally) - it wouldn't be until you created new Images and Clones that they captured any new data from the databases on A. How it works is described here: https://documentation.red-gate.com/clone3/getting-started/what-is-sql-clone 

Optimally, we would recommend you used backups of the databases on server A to create the Images so that the Databases on Server A never have any additional overhead on them from the Imaging process.

I hope this makes sense - let me know if you have any further questions - thank you very much!

PlantBasedSQL

Not a problem @rmg1
The Clones are created as empty differencing disks (as per my post above).
Fortunately, this means that on creation (assuming you have not altered them in any way) they should be around 50mb in size.
Let me know if you have any additional questions!

rmg1

Thanks for that, I'll pass it to our technical server team.

Just out of curiosity, can you tell me how much room the following would take up when cloned:-

Database A - 580GB

Database B - 651GB

Database C - 38GB

Database D - 22GB

We would need a single image of each with one clone of each as per my initial question.

I'm not sure if it makes a difference, but we would be compressing the backups (SQLServer 2012 Enterprise) using the built-in compression algorithm.

Regards

Richard

PlantBasedSQL

Thank you for coming back on this @rmg1
We would expect the images to be, assuming you weren't making any changes to the Databases (.sql scripts or Data Masking .dmsmasksets) to be roughly the same size as the uncompressed backups / original databases).
Let me know if you have any further questions. 

rmg1

Thanks for that.

Can you advise on the size of the clones as well please?

rmg1

Brilliant, thanks for that.

So, as we will only be reading from the clones, they should stay at 50MB each.

PlantBasedSQL

Yes absolutely

1 word of caution, that can catch some of our users out, if you have any maintenance - jobs on these servers that might also affect the Clones then this might cause them to grow (as it would technically be applying differences) - the chances are minimal, but I wanted to let you know just in case!

rmg1

Thanks for the heads-up.

As far as I know, we have a maintenance job on the original server which takes care of index rebuilds, etc.

As we currently use mirroring/snapshotting to transfer the data to a single server, I don't think that will be an issue.

From what's been put on here, I think the plan will be:-

Create backup of original database(s)

Make image(s) from backup

Clone image(s) to appropriate server(s) as per my original post.

PlantBasedSQL

Sounds great @rmg1 !
Let me know if you have any problems, happy Cloning!

rmg1

One final question.....

Can we put the backup/image files anywhere or would we need to keep them on the same server as the SQL instance?

I'm thinking of putting them onto one our fileservers (there's more room) in a secure folder but not sure that's possible.

PlantBasedSQL

Hi @rmg1
Thanks for coming back on this - absolutely you can put the backup & image files elsewhere on the network, they don't have to be on the same server as the instance creating the image. 
Geographic location can play a part in latency connecting to the image though so that's just something to be aware of (more on that in our best practices document here: https://www.red-gate.com/products/dba/sql-provision/resources/best-practices-sql-provision) but ultimately putting them on the file server should be fine, so long as the SQL Clone agent running on the nominated SQL Server has read/write access to network locations where images will be stored!
Let me know if you have any other questions - thanks!