Skip to main content
Pure1 Support Portal

Cloning SQL Server Databases using FlashArray

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.

image.png

Test Configuration

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

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.

image.png

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

image.png

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

image.png

image.png

image.png

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

image.png

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

image.png

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

image.png

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