Cloning SQL Server Databases Using FlashArray

Last updated
Save as PDF

Overview

The role of today’s database administrator continues to evolve. As the amount of data increases from a variety of sources, SQL Server database administrators have less time available to perform tasks that require quick copying of data to be consumed by a variety of end users within an organization. Pure Storage® FlashArray™ helps reduce the time for cloning SQL Server database operations with providing efficient snapshots.

Goals and Objectives

Define Crash-Consistent Backup with regard to SQL Server data consistency.
Provide example snapshot creation steps using the Pure Storage PowerShell SDK for a given test configuration.
Provide example snapshot deletion steps using the Pure Storage PowerShell SDK for a given test configuration.

Audience

This document is for database, system, and storage administrators responsible for maintaining SQL Server, storage environments.

Pure Storage Introduction

FlashArray Product Offerings

The Pure Storage FlashArray family delivers software-defined all-flash power and reliability for every need and every budget, from the entry-level FlashArray//M10 to the new FlashArray//X – the first mainstream, 100% NVMe, enterprise-class all-flash array.

With our Purity Operating Environment software, every FlashArray model enables organizations to achieve the highest levels of business continuity with ActiveCluster while enjoying proven 99.9999% availability, completely non-disruptive operations, and support that’s above and beyond.

Whether it’s accelerating a single database, powering VMs and desktops, or the foundation of an all-flash cloud, the rich data services and effortless operations of FlashArray will make your enterprise storage something you simply don’t worry about anymore.

Purity Protect

Combines Purity ActiveCluster with space-saving snapshots, replication, and protection policies into an end-to-end data protection and recovery solution that protects data against loss locally and globally. All Purity Protect services are fully-integrated in FlashArray and leverage native data reduction capabilities.

Zero-Compromise Data Services

Consolidating on FlashArray is simple – there’s nothing else to buy or install, and all data services are built-in and included. Our industry-leading 5:1 average data reduction (across the entire FlashArray install base) is inline and always-on, which means you’ll save on storage, power, cooling, and space. And you can keep that space savings with data reduction-aware snapshots and replication.

Enjoy the protection of always-on encryption and our zero-configuration, Always-On QoS while maintaining consistent mixed workload performance even through component failures and upgrades.

Accelerate

Latency sensitive applications
Databases
Virtual machines and desktops

Pure Storage PowerShell SDK

The Pure Storage PowerShell SDK provides integration with the Purity Operating Environment and FlashArray. It provides functionalities of Purity's REST API as PowerShell cmdlets.

Snapshot Use Cases

Development Databases

Database administrators can quickly copy databases with FlashArray snapshots and present data stores to a host for development group usage.

Reporting Databases

Different organization groups often require their own data stores outside of production database environments. FlashArray snapshots can quickly copy databases for these purposes.

Alternate Database Recovery

SQL Server native database backups, and the testing of these backups are always recommended first for organizations to meet their data restore strategy requirements (amount of acceptable downtime, and data loss). In addition to this recommendation FlashArray snapshots can also be implemented as an additional means to help mitigate outages when quick access to data is required.

Snapshot Architecture

FlashArray snapshots are crash consistent snapshots. SQL Server data and log files are preserved however uncommitted transactional data is lost.
Cloned SQL Server database files from FlashArray snapshots can be attached to a SQL Server instance creating a database copy. During the attach process SQL Server automatically will perform its own crash recovery to either persist committed transactions, or not persist uncommitted transactions to storage.
FlashArray snapshots rely on copying the metadata mapping of data location information managed by Purity operating environment. These snapshots are immutable there are no parent/child relationships.
Any subsequent writes that occur on the source volume (Volume 1) in the example below are redirected for efficiency.
A new volume (Volume 2) can be activated with no hierarchical relationship to the snapshot or parent volume offering flexible deployment. See picture below.

FlashArray snapshots are immutable. The source volume can be deleted, and eradicated without affecting any subsequent created snapshots, or activated volumes.
In the example below the blocks that were associated with the deleted source volume are now marked for reuse.

Test Configuration

To ensure configuration, performance, and compatibility are optimal, please reference and apply the following Pure Storage and Microsoft Best Practice Guides:

(Pure Storage Documentation) Microsoft Windows Server Best Practices Guide
(Pure Storage Documentation) FlashArray User Guide
(Microsoft Documentation) Installing Windows PowerShell for current version
(Pure Storage Documentation) SQL Server Best Practices Guide

Operating System	Information/Parameter
Windows Server 2016	Build 14393
Database Source Storage	Information/Parameter
Model	//M 20
Operating Environment	Purity 4.10.4
Software	Version
PowerShell	5.1
Software	Version
Pure Storage PowerShell SDK	1.7.4.0
Release Compatibility Requirements	PowerShell 3.0 or higher. .NET 4.5. This release is compatible with Purity FlashArrays that support Pure Storage REST API 1.0, 1.1, 1.2, 1.3, 1.4, 1.5, 1.6, 1.7. 64-bit operating system.
Database Application	Information/Parameter
Microsoft SQL Server Enterprise	2016 13.0.4446 (X64)
Database Size	1.4TB
Data Size (realistic data)	814GB

Implementation Steps

Snapshot Creation Example Overview

A snapshot (4-SQL-Data-Log.SNAP-1), and new volume copy (Demo-4-SQL-Data-Log-Copy-Vol-1) will be created
The new copy volume (Demo-4-SQL-Data-Log-Copy-Vol-1) will be connected to a host (MARK-HOST-04)
A Windows Volume copy will be renamed (4-SQL-Data-Log-Copy-1 )
The SQL Server database clone will be created, named (tpce-copy-1), and attached to a SQL Server instance

STEP 1: Install Pure Storage PowerShell SDK

Ensure PowerShell is installed with a PSversion value of 3.0 or higher using this command for verification.

$PSVersionTable

Locate the Pure Storage PowerShell SDK by searching at Microsoft’s PowerShell Gallery site for installation.
Install the Pure Storage PowerShell SDK module per the instructions listed on the PowerShell Gallery site.

STEP 2: Setup FlashArray Connectivity

Import the Pure Storage PowerShell SDK module
Create credentials, connect to the FlashArray, and enter required credentials
PowerShell Cmdlets used

#    Import Modules
Import-Module PureStoragePowerShellSDK

#    Create Credentials
$Creds = Get-Credential

#    Create FlashArray object, endpoint, and use credentials
#    Connect to the FlashArray
$FlashArray = New-PfaArray -Endpoint 10.21.88.234 -Credentials $Creds -IgnoreCertificateError

STEP 3: Reference Host Disk Number and Volume Letter

Retrieve current Windows Disk number and volume letter for reference
Import the Pure Storage PowerShell SDK module
Create credentials, connect to the FlashArray, and enter required credentials
PowerShell Cmdlets used

#    Retrieve current Pure FlashArray Disk Number
Get-Disk | Where-Object FriendlyName -like "PURE*"

#    Retrieve current Pure FlashArray Volume Letters
Get-Volume | Select-Object DriveLetter, FileSystemLabel, FileSystem

STEP 4: Create Snapshot and Connect to Host

Take Snapshot of the source FlashArray volume
Create a new FlashArray volume from source snapshot
Connect the new FlashArray volume to the host
PowerShell cmdlets used

#    Take snapshot of the specific volume
New-PfaVolumeSnapshots -Array $FlashArray -Sources '4-SQL-Data-Log' -suffix 'SNAP-1'

#    Create a new volume from the snapshot
New-PfaVolume -Array $FlashArray -Source '4-SQL-Data-Log.SNAP-1' -VolumeName 'Demo-4-SQL-Data-Log-Copy-Vol-1'

#    Connect new volume copy to host
New-PfaHostVolumeConnection -HostName 'MARK-HOST-04' -Array $FlashArray -VolumeName 'Demo-4-SQL-Data-Log-Copy-Vol-1'

STEP 5: Bring Disk, Windows Volume Copy Online at Host

Scan and online disks
Check driver letter Windows used for new Windows volume copy for reference
Using the Windows drive letter (example D), rename the new Windows volume copy label
PowerShell Cmdlets used

#    Rescan Disks to ensure all are available in Windows 
Update-HostStorageCache

#    Online Disks
Get-Disk | ? IsOffline | Set-Disk -IsOffline:$false

#    Rescan Disks to ensure all are available in Windows
Update-HostStorageCache

#    Rename volume copies
Set-Volume -DriveLetter D -NewFileSystemLabel '4-SQL-Data-Log-Copy-1'

STEP 6: Verify Disk, Windows Volume Copy

Verify the Windows volume copy
PowerShell and Windows Disk Manager Examples
Windows volume D: has been added

STEP 7: Attach Database Clone Files to SQL Server Instance

Locate the database clone files on the new Windows volume copy

Attach the clone database to the SQL Server instance with a new name
SQL Server T-SQL example using the attach feature

In some cases when attaching a SQL Server database to a different host SQL Server instance, the SQL Server engine service account permissions may need to be modified (Windows ACL) on the corresponding database Windows volume and file directory for the service account accessibility.

STEP 8: Verify SQL Server Database Copy

SQL Server Management Studio example to verify the database copy exists

Availability Groups databases cannot be used as a FlashArray snapshot source, and you cannot seed Availability Group Replicas from a database created by a FlashArray snapshot. FlashArray snapshots are crash consistent snapshots. SQL Server data and log files are preserved however uncommitted transactional data is lost.

Cloned SQL Server database files from FlashArray snapshots can be attached to a SQL Server instance creating a database copy. During the attach process SQL Server automatically will perform its own crash recovery to either persist committed transactions, or not persist uncommitted transactions to storage.

Snapshot Deletion Example Overview

The SQL Server database clone (tpce-copy-1) will be dropped and the Windows volume copy (4-SQL-Data-Log-Copy-1) will be disconnected from the host.
The FlashArray volume (Demo-4-SQL-Data-Log-Copy-Vol-1) will be removed and eradicated from the FlashArray. Eradication is optional.
The FlashArray snapshot (4-SQL-Data-Log.SNAP-1) will be removed and eradicated from the FlashArray. Eradication is optional.

STEP 1: Drop SQL Server Database Clone

In SQL Server Management Studio drop database clone copy

USE MASTER
go
ALTER DATABASE "tpce-copy-1"
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
DROP DATABASE "tpce-copy-1"
GO

STEP 2: Setup FlashArray Connectivity

Import the Pure Storage PowerShell SDK module
Create credentials, connect to the FlashArray, and enter required credentials
PowerShell Cmdlets used

#    Import Modules
Import-Module PureStoragePowerShellSDK

#    Create Credentials
$Creds = Get-Credential

#    Create FlashArray object, endpoint, and use credentials
#    Connect to the FlashArray
$FlashArray = New-PfaArray -Endpoint 10.21.88.234 -Credentials $Creds -IgnoreCertificateError

STEP 3: Remove Host Connection and Delete Eradication Volume and Snapshot

Remove FlashArray connection from host
Remove FlashArray Volume
Eradicate FlashArray Volume (Eradicate is optional)
Remove Snapshot
Eradicate Snapshot (Eradicate is optional)

#    Cleanup
#    Remove volume connections from the host
Remove-PfaHostVolumeConnection -HostName 'MARK-HOST-04' -Array $FlashArray -VolumeName 'Demo-4-SQL-Data-Log-Copy-Vol-1'

#    Remove Volume
Remove-PfaVolumeOrSnapshot -Array $FlashArray -Name 'Demo-4-SQL-Data-Log-Copy-Vol-1'
Remove-PfaVolumeOrSnapshot -Array $FlashArray -Name 'Demo-4-SQL-Data-Log-Copy-Vol-1' -Eradicate

#    Remove Snapshot
Remove-PfaVolumeOrSnapshot -Array $FlashArray -Name 'Demo-4-SQL-Data-Log.SNAP-1'
Remove-PfaVolumeOrSnapshot -Array $FlashArray -Name 'Demo-4-SQL-Data-Log.SNAP-1' -Eradicate

STEP 4: Rescan Windows Disks

Rescan Windows disks to ensure the Windows Volume copy is removed

#    Rescan Disks to ensure all are available in Windows 
Update-HostStorageCache

Windows Volume D: is removed

If SQL Server database files are located across multiple FlashArray volumes a protection group can be created to snapshot multiple FlashArray volumes simultaneously (atomic snapshot). Protection groups can be implemented using the Pure Storage PowerShell SDK PfaProtectionGroupSnapshot cmdlet. See Pure Storage PowerShell SDK documentation for its usage.

Summary

FlashArray snapshots offer a fast and efficient method to clone SQL Server databases for a variety of use cases. As data store sizes increase administrators now have a flexible tool to reduce the complexity of copying large amounts of data and representing it to different consumers.

Code Samples

Create Snapshot

#  Check PowerShell Version
$PSVersionTable

#  Import Moodules
Import-Module PureStoragePowerShellSDK

# Create credentials
$Creds = Get-Credential

#  Create FlashArray object, endpoint, and use credentials
#  Connect to the FlashArray
$FlashArray = New-PfaArray -Endpoint 10.21.88.234 -Credentials $Creds -IgnoreCertificateError

#  Retrieve current Pure FlashArray disk numbers
Get-Disk | Where-Object FriendlyName -like "PURE*"

#  Retrieve current Pure FlashArray volume letters
Get-Volume | Select-Object DriveLetter, FileSystemLabel, Filesystem

#  Take Snapshot of specific volume
New-PfaVolumeSnapshots -Array $FlashArray -Sources '4-SQL-Data-Log' -suffix 'SNAP-1'

#  Create a new volumes from the snapshot
New-PfaVolume -Array $FlashArray -Source '4-SQL-Data-Log.SNAP-1' -VolumeName 'Demo-4-SQL-Data-Log-Copy-Vol-1'

#  Connect new volume copies to host
New-PfaHostVolumeConnection -HostName 'MARK-HOST-04' -Array $FlashArray -VolumeName 'Demo-4-SQL-Data-Log-Copy-Vol-1'

# Rescan Disks to ensure all are available in Windows
Update-HostStorageCache

#  Online Disks
Get-Disk | ? IsOffline | Set-Disk -IsOffline:$false

# Rescan Disks to ensure all are available in Windows
Update-HostStorageCache

# Rename Volume Copies
Set-Volume -DriveLetter D -NewFileSystemLabel '4-SQL-Data-Log-Copy-1'

Attach Database

USE [master]
GO
CREATE DATABASE [tpce-copy-1] ON
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\MSSQL_tpce_root.mdf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\TPCE_Log.ldf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Fixed_1.ndf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Fixed_2.ndf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Fixed_3.ndf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Scaling_1.ndf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Scaling_2.ndf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Scaling_3.ndf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Growing_1.ndf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Growing_2.ndf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data\Growing_3.ndf' )
 FOR ATTACHGO

Drop Database

USE MASTER
go
ALTER DATABASE "tpce-copy-1"
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
DROP DATABASE "tpce-copy-1"GO

Delete Snapshot

#  Import Moodules
Import-Module PureStoragePowerShellSDK

# Create credentials
$Creds = Get-Credential

#  Create FlashArray object, endpoint, and use credentials
#  Connect to the FlashArray
$FlashArray = New-PfaArray -Endpoint 10.21.88.234 -Credentials $Creds -IgnoreCertificateError

#  Cleanup
#  Remove volume connections from the host
Remove-PfaHostVolumeConnection -HostName 'MARK-HOST-04' -Array $FlashArray -VolumeName 'Demo-4-SQL-Data-Log-Copy-Vol-1'

#  Remove Volume
Remove-PfaVolumeOrSnapshot -Array $FlashArray -Name 'Demo-4-SQL-Data-Log-Copy-Vol-1'
Remove-PfaVolumeOrSnapshot -Array $FlashArray -Name 'Demo-4-SQL-Data-Log-Copy-Vol-1' -Eradicate

#  Remove Snapshot
Remove-PfaVolumeOrSnapshot -Array $FlashArray -Name '4-SQL-Data-Log.SNAP-1'
Remove-PfaVolumeOrSnapshot -Array $FlashArray -Name '4-SQL-Data-Log.SNAP-1' -Eradicate

# Rescan Disks to ensure all are available in Windows
Update-HostStorageCache