Home > Storage > PowerFlex > White Papers > Microsoft SQL Server Data Protection using Dell PowerFlex Snapshots > Use Case 1: Database refresh using PowerFlex Snapshot
A database refresh involves overwriting an existing database environment from production such as for purposes of creating development, testing (QA), and reporting database environments. An administrator typically performs this task on demand or automates it by job scheduling. Database refresh is required:
The following figure shows the details of production cluster SQL CLUSTER.BR.SQL. Three databases TPCC1, TPCC2, and TPCC3 are running on the production environment (Site 1). All the database files are stored on the data volume and the transaction log files are stored on the log volume. These volumes provide cluster shared volume functionality for the SQL FCI cluster (TPCC_data_volume and TPCC_log_volume below).
Figure 6. Production environment details
Microsoft SQL Server FCI role is configured with three SQL nodes running as shown in Figure 7. As a result, if any node/role failure happens, the database cluster role automatically gets failed over to the healthy nodes.
Figure 7. Cluster details
The database can be refreshed using PowerFlex snapshot by following the below documented steps.
Figure 8. Database and row count on production environment
Snapshots can be created manually or using snapshot policies. The following steps show an example of creating manual PowerFlex snapshots of the database volumes using the PowerFlex Manager UI:
Figure 9. Volume snapshot steps
CSV_data-snap-1 and CSV_log-snap-1 are the snapshots of data and log volumes as shown in the following figure:
Figure 10. Storage snapshot of database and log volume
Note: PowerFlex snapshots in a consistency group are guaranteed to be from precisely the same point in time. Hence, it is important to take the snapshot of the data and log volume together to restore and recover the database in a consistent state.
Figure 11. Map snapshot volumes to the SDC hosts
Figure 12. Attach snapshot volumes to the dev/test VMs.
Figure 13. Attaching the disk to the secondary cluster
As seen in the above figure, the secondary cluster that is, dev/test environment SQLSECSVR.MuM.SQL with two nodes is up and running. The data and log volume snapshots of the production site is configured as cluster shared volumes in the secondary cluster.
Figure 14. Production environment database attached to dev/test environment
Figure 15. Database restored and verified on Site2
As seen in the above figure, Dev/Stage_Site2 of the Microsoft SQL Server FCI cluster has been successfully refreshed with the production data. The object counts and row count match with the production database for all the three databases.