What are the challenges you face when working across database platforms? Take the survey

SQL Clone for static databases

rmg1rmg1 Posts: 11 Bronze 1
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?

Best Answers

  • Options
    PlantBasedSQLPlantBasedSQL Posts: 187 Gold 4
    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!
  • Options
    PlantBasedSQLPlantBasedSQL Posts: 187 Gold 4
    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!


  • Options
    rmg1rmg1 Posts: 11 Bronze 1

    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.



  • Options
    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. 
  • Options
    rmg1rmg1 Posts: 11 Bronze 1

    Thanks for that.

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

  • Options
    rmg1rmg1 Posts: 11 Bronze 1

    Brilliant, thanks for that.

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

  • Options
    Yes absolutely :smile:

    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!
  • Options
    rmg1rmg1 Posts: 11 Bronze 1

    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.

  • Options
    Sounds great @rmg1 !
    Let me know if you have any problems, happy Cloning!
  • Options
    rmg1rmg1 Posts: 11 Bronze 1

    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.

  • Options
    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!
Sign In or Register to comment.