Advanced Microsoft SQL Database Cloning for Dev/Test Workflows

By Andrew McCumiskey • August 25, 2017

Many Cohesity customers have been using Cohesity’s advanced Microsoft SQL Server data protection tools to safeguard their data. Many customers have expressed interest in leveraging the data protection copies for test and dev workflows to minimize the sprawl of data copies while keeping developers working on latest versions of production data. Additionally, customers have requested the ability to clone SQL databases to development-only environments where the target VM or physical server has a very restricted environment and storage.

Cohesity solves the SQL server clone and replication sprawl by integrating the ability to not just protect SQL databases via native Microsoft APIs, but also allow full cloning of the database to any SQL Server or SQL instance within a customer environment using the same APIs. The cloned databases are directly served from the Cohesity cluster to any SQL server. Unlike other ‘backup first’ products, Cohesity presents the cloned database on a fully distributed, highly available, high performance volume. By serving the database from Cohesity, test/dev SQL servers can be configured with minimum primary storage capacity and still run multi-terabyte SQL servers without the need for additional primary storage. Additionally, because the target SQL server and cloned databases are separate from the primary SQL server and storage, there is no performance impact to the production environment. Finally, the clones are created and torn down via a completely automated workflow within the Cohesity UI.

The isolation of the storage environments between primary compute and storage resources and clone/secondary storage is shown in the diagram above. Using Cohesity’s test/dev workflow enables instantaneous cloning of databases directly from the Cohesity cluster to another SQL server with zero impact on the primary SQL servers or primary storage controllers. Put another way, the target SQL server only needs enough storage to host a version of Windows and SQL server that is compatible with the cloned database and the rest is taken care of by the Cohesity cluster.

Here’s a brief walkthrough of this advanced clone functionality with screenshots. We have assumed that the SQL database that needs to be cloned has already been captured by a Cohesity Database protection job. More information on protection jobs can be found in this blog.

1. Identify the server, instance and specific database that needs to be cloned

2. Log in to the Cohesity UI and select the Test/Dev Workflow

3. Choose the SQL Server Workflow

4. Enter the database name you wish to clone from step #1 and select it from the query results

5. Next choose the appropriate SQL host and instance and optionally provide the cloned database a new name

6. Choose the restore point of the database, save the selection and continue the workflow

7. The Cohesity cluster will immediately recall the appropriate snapshot that contains the SQL database and log files and being restoring them to the target SQL server and instance. In this case, a 10TB SQL database is cloned and brought online in a minute and a half!

8. Once all work is done on the cloned database, the user can tear down the clone and clean up all resources by clicking on the “Tear Down Clone” button

9. Finally, review the database list on the SQL server to ensure it was removed