Database snapshots are widely used to re-create realistic workloads for database testing and development (test/dev). Almost all enterprise-class storage vendors support snapshot functionality that can create space-efficient point-in-time copies. However, using general-purpose snapshots for database snapshots requires compromises for test/dev activities and incurs extra process and management complexity as compared to database-aware snapshots. In this post we examine the requirements for database snapshots and contrast general-purpose snapshots with Exadata’s database-aware snapshots.
Database testing and development with general-purpose snapshots
Typical database test/dev requires re-creating the production environment as efficiently and simply as possible, often for multiple concurrent activities, with minimal disruption to production, and subject to data protection requirements (e.g., personal information, credit card numbers, etc., cannot percolate outside the production environment).
Most general-purpose snapshot implementations migrate data from their primary (production) storage array to a secondary platform. Because a single production environment often requires multiple test/dev environments, secondary storage arrays and platforms typically can't afford to have the same performance or availability characteristics as the production environment. Also, any off-database redaction process introduces additional users and software into the set of entities that must be trusted. These compromises and additional procedures add complexity and reduce the quality of test/dev activities.
Besides these snapshot issues, general-purpose storage arrays require ongoing, specialized administration. Database Administrators (DBAs) manage database snapshots with tools provided by the storage array vendor, third party data management tools, or Oracle’s own Enterprise Manager (OEM). While OEM simplifies snapshot management, general-purpose storage arrays also require significant non-database-related (storage) management, including creation of volumes or LUNs, capacity management, backup of the array, software upgrades, hardware maintenance tasks, and storage-level user control and access.
In sum, while general-purpose snapshots are useful, they entail significant compromises that limit the quality and value of test/dev activities.
Do all-flash arrays enable first-class database testing and development?
Some all-flash array vendors propose to host test and development databases and production databases in the same primary (production) all-flash storage array. While this approach addresses the performance and availability compromises discussed in the previous section, it introduces a new set of issues. Combining production and test/dev workloads on the same storage array requires careful planning and active management of the database systems and the storage array, as test/dev databases can easily command a large portion of the array’s resources and degrade the performance of the productiondatabase.
One problem is that most all-flash arrays have low throughput, single-digits GB/s at best, which can be saturated by a handful of test databases running data warehousing queries in the shared storage array.
More broadly, general-purpose storage arrays are unaware of the context in which read and write requests are issues by the database, and are thus limited in their ability to combine workloads intelligently. For example, if a production database submits a log-write while a test/dev database is issuing a long batch of writes, an all-flash array is unable to identify and prioritize the log-write over the batch writes.
Combining test/dev with production workloads on the same all-flash storage arrays trades improvements in dev/test environments for risks to the production workloads. And all-flash storage arrays are still inherently limited in their throughput.
The solution is to make snapshots as close to the database -in functionality and location- as possible. Exadata provides a database-aware snapshot solution that is simple, space efficient, and secure, and it doesn’t compromise on the availability and the performance of the production system.
Simple: An Exadata Snapshot is based on a test master, which is a full clone of the source database. From a single test master you can create multiple Exadata Snapshots with minimal additional storage and minimal effort. It takes one SQL command to create a snapshot database and it is created in minimal time.
SQL> create pluggable database PDB1S1 from PDB1TM1 tempfile reuse create_file_dest='+SPARSE' snapshot copy;
Each snapshot represents a complete Oracle database and can take advantage of all Exadata features – Smart Scan, Smart Flash Cache, Smart Flash Logging to name a few. Exadata documentation explains the process in great detail. Exadata Snapshots are integrated with Enterprise Manager as well and customers can use EM to create snapshots in a few clicks. This demo gives a sneak peak on integration between Enterprise Manager and Exadata Snapshots. Exadata Cloud offerings also allow users to create a snapshot with just a few clicks (more on this in a later post).
Space Efficient: Exadata Snapshots are built on sparse disk groups and use physical storage in proportion to new changes made to the database. In addition, hierarchical snapshots enable you to create snapshots from snapshots and any of these snapshots can act a sparse test master. And you can take a sparse backup of the snapshot, preserving only the new changes introduced in that database. In fact, Rent-A-Center, an Exadata customer, achieved ~30X space savings by implementing Exadata snapshots.
Secure: Each Exadata snapshot inherits the same security model as the primary database. In addition, the test master database owner can delete or mask sensitive data in the test master before making it available to non-privileged users in the snapshot database.
Exadata's fully comprehensive and database-aware resource management ensures that snapshots do not interfere with the performance of production workloads, and test/dev environments also benefit from Exadata's high availability.
In later blog posts we will discuss the best practices on creating and managing snapshots, sizing the system to take advantage of them as well how snapshots are deployed in Exadata Cloud. Next: Snapshots in the Exadata Cloud.