Database cloning tools have become increasingly popular in the SQL Server community. These tools, such as Delphix Dynamic Data platform and Redgate’s SQL Clone, offer the ability to create virtual copies of databases, known as clones, for various purposes including development and testing. In this article, we will explore the benefits and considerations of using SQL Server cloning tools.

The Power of Cloning

One of the key advantages of SQL Server cloning tools is the ability to quickly and easily create multiple copies of a database. This is particularly useful for developers who need to test their code without impacting the production environment. With a clone, developers can freely experiment and make changes without the fear of breaking anything.

Furthermore, cloning tools can dramatically reduce storage space requirements. A clone database only contains a small amount of data that maps to the original database, along with the differences between the two. This means that a clone can be initially as small as a few megabytes, even if the original database is several terabytes in size.

Considerations for Cloning

While SQL Server cloning tools offer great benefits, there are some important considerations to keep in mind:

Compliance

If your database contains sensitive data that falls under regulatory requirements such as HIPAA, SOX, or PCI, you need to ensure that the cloned databases are compliant as well. This may involve masking or scrambling the data in the clones to protect sensitive information.

Security

Some data in the original database may need to be protected from unauthorized access, even within the development environment. For example, executive compensation or trade secrets should not be visible to all developers. In such cases, it is important to implement proper permissions management and data masking techniques to ensure data security.

Refreshing Clones

One challenge with cloning tools is keeping the clones up to date with the latest data from the production environment. If the original image database is outdated and developers require current data for debugging or testing, a clone alone won’t help. There are several approaches to address this issue:

• Create a refreshed database in a lower environment, which is quick but doesn’t save any storage space.
• Create a new image to clone while retaining the old image, allowing for a new point of reference but potentially losing the space savings.
• Replace the image with a new one, preserving space savings but necessitating the recreation of all existing clones.
• Create a clone and use a data comparison tool to synchronize it with the refreshing database, providing an emergency solution that preserves some space savings.

It is important to carefully plan and choose the most appropriate approach based on your specific requirements and constraints.

Cloning a Clone

In some cases, developers may need to work on different branches of a clone simultaneously. This can be challenging, as creating a new clone may not be feasible due to ongoing work on an existing clone. One solution is to clone the existing clone and use data comparison tools to synchronize the two. While this may require extra work, it still offers space savings and is faster than a full backup and restore.

Conclusion

SQL Server cloning tools, such as SQL Clone, are powerful tools that can greatly enhance the development process and save storage space. By allowing developers to work with virtual copies of databases, these tools enable faster and more efficient development and testing. However, it is important to consider compliance, security, and data refresh strategies when adopting cloning tools. With careful planning and implementation, SQL Server cloning can be a game-changer for your development team.

Author: John F. Tamburo

Chief Database Administrator at Landauer, Inc.

Twitter: @SQLBlimp

Blog: www.sqlblimp.com