Concepts

A Petabyte of SQL Server Data on-demand

In this article we look at how database cloning can scale to provide on-demand delivery of SQL Server data warehouses of 20 TB or more in size, for an effective on-demand capacity of 1 PB of SQL data.


Windocks recently worked with a Fortune 500 financial services firm to provide SQL Server database clones for a 25 TB data warehouse used for development and test. Windocks is a leader in SQL Server database cloning, supporting both Windows Virtual Hard Drive based database cloning, and storage array based volume snapshots.

The project evaluated Windows Virtual Hard Drive (VHD) database clones, with a shared SSD storage pool on Windows Storage Spaces Direct (S2D). The alternative was a existing Pure Storage array with clones in the form of fast volume snapshots. Both approaches deliver read/write environments in seconds, for use with Microsoft’s Docker SQL Server containers, and conventional SQL Server instances. Both also deliver up to 99% storage savings, with virtually no impact on storage consumption when working with cloned databases.

Goals:

The choice to use the Pure Storage array was straightforward, as it involved no new investment. The goals for the project included:

Freedom to work with any SQL Server: it was important to support a mix of SQL Server environments, including Microsoft’s Docker SQL Server containers and conventional SQL server instances, and the new SQL Server on Linux containers.

Use existing infrastructure: in addition to cost savings, the pilot was to be conducted in one month with minimal disruption to existing systems and processes.

Equip the SQL Server and DevOps teams: the solution was to be managed by SQL Server DBAs, and provide complete life-cycle management of the storage array volumes and mount points, without involvement of the storage admins.

Portability: the solution should be portable to any public cloud using VHD database cloning.

Security: the solution must meet all regulatory requirements involved in financial services, with user/group role based authentication, secure data images, secure secrets management, and data image and usage audits and reporting.

Scalability: the client needed support for up to 50 simultaneous cloned environments of 25 TB each, for a total of 1.2 Petabytes of on-demand SQL Server data.

Pilot and Results:

As one would expect, the client chose to focus on using the existing Pure Storage array to avoid the need for new systems. Following a 20 minute automated install, the first SQL Server image was built and delivered. Pilot users were given access the following day, and first week impressions were unchanged following a 30 day pilot.

1) Developers and testers quickly adapted to using isolated “sand boxed” SQL Server environments, with fresh 25 TB environments delivered in seconds, enabling destructive testing, and dramatically improved dev and test support. Test coverage expanded and improved release success is expected.

2) Flexibility was realized with database clone delivery to conventional instances in addition to SQL Server containers.

3) Storage Admins appreciated being freed from DBA and developer requests for data environments. DBAs also gain freedom from provisioning requests, as the end-users use either automated or self-provisioned environments.

4) Data security improved while data access expanded. Windocks delivers an authorized catalog of secure data images, incorporating data masking and other user/group specific preparations. For the first time the enterprise can audit and report on data images and their use.

5) Savings: costs came in at ~$0.005/GB-hr. A 90% savings over conventional cloud storage.

“Database cloning for SQL Server is a game-changer”

Whether working with a 50 GB database, or a complex image of 50 databases totaling multi-Terabytes, Windocks database cloning transforms enterprise SQL data access and use. Windocks is proven in use by leading Financial Services, Healthcare, and Technology firms worldwide, and supports any public cloud or on-premise infrastructure. Get started today with a free full featured Windocks Community Edition.

Similar posts

Get notified on new test data management insights

Be the first to know about new insights on DevOps and automation in the test data management space.