SQL Server Database Cloning for Test Data Management
OVERVIEW
Accelerate multi-cloud application test and release cycles with rapid cloning of multi-TB SQL Server databases to test environments.
Every enterprise wants to roll out new application features, security patches, and perform analytics faster than ever. To be successful and cost effective, these activities all need high fidelity copies of production SQL server databases.
Actifio software enables enterprises to instantly provision clones of SQL databases to test environments in a self-service manner.
CHALLENGES
Enterprises using legacy test data management tools create subsets of production data for testers.
Testing software against data subsets leads to high defect levels and poor software quality releases.
To overcome this problem, enterprises create physical copies of large SQL production databases. The result is high storage costs and long wait cycles with too much time wasted creating and refreshing physical copies.
To overcome this problem, some enterprises try using storage snapshots, but these lack self-service automation and lead to weak security models with no automated data masking or role-based access control.
In many environments, this reduces testing efficiency since users can’t perform simultaneous functionality and performance testing. Lastly, most of these point tools are not cloud-ready.
1 / 4
←
→
HOW Actifio HELPS
Actifio helps enterprises accelerate application test and release cycles by enabling users to instantly provision multi-TB SQL database clones in a self-service manner.
Database clones are rewritable and thin and thus don’t consume extra disk capacity and help reduce storage costs.
Actifio’s ability to integrate via APIs to CI/CD tools and provision dozens of SQL database clones to test environments enables quality assurance (QA) testers to iterate through testing and refresh cycles rapidly while substantially improving software quality.
For enterprises with sensitive data in SQL databases, Actifio increases security by automating the sensitive data masking process and providing integrated role-based access controls.
Enterprises can enjoy all these advantages while strategically employing Actifio on-premises or in any public cloud.
Media
Rapid database provisioning in minutes for testing multi-TB SQL databases with Actifio.
Frequently Asked Questions.
Actifio Sky supports all three configurations in on-premises and any public cloud: Standalone, Always On Availability Groups, and Always On Failover Clusters.
Actifio’s pricing model is very simple and easy to understand. It’s based on the amount of source data protected. For example: If an enterprise wants to use Actifio to provision 20 clones of a 10 TB SQL database, the enterprise would need Actifio license for 10 TB of source data. Enterprises can create as many clones as they want and can retain any amount of point-in-time history. Enterprises can also use this license anywhere in the public cloud or on-premises.
Actifio VDP is the patented core Virtual Data Pipeline technology that can be consumed in multiple form factors.
Actifio Sky is a VM appliance that runs Actifio VDP engine and can be provisioned in a public cloud VM or on-premises in VMware, Hyper-V VMs.
Actifio CDS/CDX is a physical appliance cluster that runs Actifio VDP engine with High Availability and is typically used on-premises.
Yes. Actifio supports all major public cloud providers including Azure, AWS, Google, and IBM. It’s also available in the leading public cloud marketplaces.
Following are the key comparisons:
- Staging Servers: High vs No Costs: The biggest architectural challenge with Delphix is that it needs a staging server between the production SQL server and the Delphix appliance. The staging server needs a Windows license and a SQL license. Higher change rates, large databases or more SQL instances could require the provisioning multiple staging servers. This increases the infrastructure cost, SQL license costs, and operational burden. Compare this with Actifio which does not need a staging server. Actifio software has the ability to ingest data directly from SQL server database to Actifio in an application consistent, incremental forever manner. This efficient architecture reduces costs and operational burden.
- Refresh Cycles: Long vs Short Time: Delphix relies on an architecture of shipping logs from production SQL database to staging server, where the logs are replayed. Once the logs are replayed, Delphix takes a snapshot and marks it as a point-in-time copy that is available for virtual DB (VDB) clone provisioning to test environments. Thus if 1 Million updates, delete, or write transactions occur in production, 1 Million transactions have to be replayed in the staging server. This could take a long time, thus delaying the refreshes of changes from production to Delphix appliance. Compare this with Actifio which leverages its proprietary Change Block Tracking (CBT) technology in production SQL databases to determine just the changed blocks and copy the changed blocks to Actifio Sky instance, thus leading to an application consistent incremental forever data ingest. Thus in the above example,1 Million transactions with an average of 8KB size would amount to 8GB changed blocks, and Actifio would copy 8 GB within just minutes. This architecture lends itself to very fast refreshes of changed data from production SQL databases to Actifio Sky instance.
- Low vs High Performance: Many enterprises have investments in Fibre Channel and want to provision virtual DB (VDB) clones over their high-performance Fibre Channel network for better performance. Delphix only supports NFS and so customers cannot leverage Fibre Channel to provision virtual database (VDB) clones. Compare this with Actifio which has the ability to provision VDB clones over Fibre Channel, NFS, and iSCSI, thus delivering not only high performance, but also the flexibility to pick and choose any protocol.
- High Availability: Many enterprises expect high availability when there are 100s of testers using virtual DB clones because any downtime means delays in testing and application release cycles. Delphix does not offer HA capability. Compare this with Actifio which offers the option for enterprises to buy its software in a 2-node HA cluster that ensures that testers don’t have any downtime in the event of losing one of the nodes.
- Total Cost of Ownership: High vs Low: Delphix requires a very large amount of compute and memory to run their software as compared to Actifio Sky software. Unlike Delphix, Actifio offers a global manager to manage one or more Actifio software instances running in multiple data centers or public cloud. Actifio also offers an enterprise-grade backup, disaster recovery, term data retention solution with the same license. Such rich functionality with simple software licensing, the high amount of data managed per software instance, and global management drives down the total cost of ownership for enterprises significantly as compared to Delphix.
There are many more differentiated features. For more details, download this Actifio vs Delphix comparison.
None of these vendors deliver true test data management functionality with the ability to provision dozens of database clones with at scale and with high performance. Actifio also offers automated data masking and provides flexibility to enable users to pick any storage and any cloud.
Yes, Actifio leverages VSS snapshot APIs to perform application consistent copies. It then leverages its proprietary Change Block Tracking (CBT) technology to capture just the changed blocks in an incremental forever manner. After incremental copy, it synthesizes a point-in-time virtual full copy by manipulating its metadata.
Learn more about how Actifio works for SQL server.
Yes. SLAs be set in such a way that transaction logs can be copied to Actifio Sky instance every X minutes or hours in between the incremental data ingestion. For example, a user can setup incremental data ingestion every 1 hour and log copies every 15 minutes.
While provisioning virtual database clones, a user can specify any point-in-time. Actifio automatically identifies the nearest incremental point-in-time, mounts a synthetic virtual full copy instantly as of that point-in-time, and applies the transaction logs to recover the SQL database to the specified point-in-time. All of this is fully automated.
Actifio stores the SQL copy in native SQL format. This ensures that after instant mount and provisioning a database clone, there is no performance overhead because of format conversion.
The other factor to consider is the storage on which the SQL copy is stored by Actifio.
Depending on the performance requirements, enterprises can specify the right storage tier to use with Actifio.
And lastly, Actifio also offers the flexibility to instantly mount and provision database clones over Fibre Channel, iSCSI or NFS depending upon the user preference.
Thus the performance can be as good as the underlying storage and the protocol the user wants to use.
Yes. Actifio integrates with SQL AAG. It can discover the primary and secondary databases in the SQL AAG environment. Users have the flexibility to ingest SQL database data and logs from the primary or secondary databases. Users can provision multi-TB database clones instantly at the primary or secondary sites depending on where virtual DB clones are needed.
Yes. Actifio supports this design. Actifio has the ability to mask sensitive data and replicate the masked data to another Actifio Sky instance in any public cloud such as AWS, Azure, Google, and IBM cloud. Within the cloud, Dev, QA testers can provision virtual DB clones on-demand. This architecture ensures that sensitive data is masked on-premises before getting replicated to the cloud.
Yes. This is possible with two approaches.
In the first approach, in SQL AAG environments, the enterprise can use Commvault/Netbackup to backup the primary SQL database, and Actifio can ingest data from the secondary SQL database or vice versa.
In the second approach where Commvault/Netbackup and Actifio are forced to protect from the same SQL database, Actifio can still ingest data for Test Data Management in an incremental forever manner using its own proprietary Change Block Tracking (CBT) technology, without impacting Commvault/Veritas backups. But only one product can manage the transaction log backups.
Yes. In fact, almost all the customers who use Actifio for Test Data Management replace their other backup product with Actifio either on day one or over time. This helps them not only reduce their overall costs, but also simplifies operations with a single platform for backup, recovery, test data management i.e. Copy Data Management for SQL server databases.