Thought Leadership
February 23, 2017
Database Clone
Vijayakumar K02
Tags: database

A database clone is a complete and separate copy of a database system that includes the business data, the DBMS software and any other application tiers that make up the environment. Cloning is a different kind of operation to replication and backups in that the cloned environment is both fully functional and separate in its own right. Additionally the cloned environment may be modified at its inception due to configuration changes or data sub setting. The cloning refers to the replication of the server in order to have a backup, to upgrade the environment.

There are a multiple of ways you could create a database clone. Standard Backup and restore is one method. Export/Import is another. Third party tools are also available for setting up Database Cloning. However, each of these methods have drawbacks. It’s moving all the data twice. Moving the data when it is exported and moving it again when it is imported (same with backup and restore method). This results in slowing down for larger databases. How a database clone be configured without moving the data multiple times?

Create Database Clone without Moving Data

A New DBCC command with SQL Server 2016, expanded in SP1, Enhancement in SQL Server 2014 SP2 is DBCC CLONEDATABASE. This make things easier to do a database clone through extremely simple method:

DBCC CLONEDATABASE(Movie Management, Clone DB);

This creates new database on the server with full set of schema plus all the statistics and Meta data. Meta data like Query Data Store information is moved as part of method. The database is created read only and the file size is the same size of the Model database.

As a showcase, Movie Management database is currently sized with 1 GB data. The new Clone DB that has created is 200 MB in size. If you are running with SQL Server 2016 SP1, movement of statistics or Query Store data can be controlled by unchecking it as an option.

Why Database Clone?

Why to just create an empty database with statistics and Query Store information? What is the advantage of it?

Obviously, without data, there won’t be troubleshooting data issues. However, the advantage is that the database can have performance tests executed against it, as the statistics and the Meta data are copied from original database, this cloned database will project as if it had data inside of it in terms of generating execution plans. All kinds of queries can be executed and see how they would operate, without moving data.

This creates an excitement now, right.

The database is read only by default and this avoids a statistics update. If the database is taken out of read only and then update the statistics, there’s no data. Therefore it would end up with statistics showing that there is no data. Any execution plans generated would not be replica of the original database.

There are cautions in using it. Database clone might be created on production, but it is not suggested leaving it there or using it in tests there. Microsoft suggests caution of Cloning database and keeping it in production environment. Below is the output of a clone operation:

Database cloning for ‘Document Version’ has started with target as ‘QDS Clone’. Database cloning for ‘Document Version’ has finished. Cloned database is ‘QDS Clone’. Database ‘QDS Clone’ is a cloned database. A cloned database should be used for diagnostic purposes only and is not supported for use in a production environment. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

High recommended is to back up the above clone and restore it to a non-production server for any testing.

Data is moved only once

So, what if the requirement is to have real time data? Is there a speedy method to move it twice? Not really. However, what if the data only moved once? Redgate SQL Clone is the software that does it.

What happens is a copy is made of given database. It’s about the size of a backup and the time taken is based on it. The virtual drives are mounted to the server instantly. In this method, the data doesn’t move at all. Data is read from the copy of the database, not from the local storage. In addition, multiple servers can attach to the copy, so many of servers can have a clone.

The main advantage of this method would be for troubleshooting production data, but offline in other environment and not from the production server. If the data is cleaned and made a clone of that, then it has another primary use as development database. A clean copy of production can be set that only takes up the space of a full back up on multiple development servers. It is the catch of it.

Again, it is not recommended using this in a production environment. The initial backup can be from production, but the mounted clones should not be on production.

This is a best way to get a clone of a database only having the data moved once.

Conclusion

Database Clone makes possibility of database copy off your production server without having to move the data twice. If the focus is only on performance tuning, check out DBCC CLONEDATABASE. If there is need of data, go for SQL Clone from Redgate. Both the way, it is possible to get a copy of your database faster and easier than moving the data two times.

References:

http://www.scarydba.com

Comments
MORE ARTICLES BY THE AUTHOR
RECENT ARTICLES
RELATED ARTICLES