HPE Community read-only access December 15, 2018
This is a maintenance upgrade. You will be able to read articles and posts, but not post or reply.
Hours:
Dec 15, 4:00 am to 10:00 am UTC
Dec 14, 10:00 pm CST to Dec 15, 4:00 am CST
Dec 14, 8:00 pm PST to Dec 15, 2:00 am PST
HPE Storage Tech Insiders
cancel
Showing results for 
Search instead for 
Did you mean: 

NOS4 Use Case: Leveraging NWT4 cmdlets for SQL Server Reporting or Dev/Test Workflows

dgonzalez59

Nimble OS4 brings an exciting set of enhancements for Windows application data management, including snapshot application metadata for Exchange and SQL Server, host level cloning automation provided by the Nimble Windows Toolkit version 4 (NWT4), and the Hyper-V VSS Requestor. In this blog, I am going to cover using NWT4 cmdlets, as well as our REST API, to perform two of the most common Proof of Concept requests for SQL Server cloning that I receive:

(1) Cloning a SQL Server Database to a Reporting Host;

(2) Cloning a SQL Server Database for Development and Test

The new clone workflow automation included with the NWT4 cmdlets greatly simplifies and accelerates SQL Server database cloning, especially when clones are required to occur at frequent intervals (ie. Development and Test or Reporting). Leveraging these cmdlets also allows for more self service capabilities, where workflows can be completed without logging into the array GUI, or knowing a detailed amount of storage level infrastructure. For example, application metadata allows for the quick identification of snapshot and volume information, and all cmdlets may be issued from the source or destination SQL Server host.

 

So let's begin....

Cloning a SQL Server Database to a Reporting Host:

This workflow is broken down into four parts:

(1) Get the most recent snapshot information for the volumes backing the SQL Server database (not a necessary step if using the latest snapshot collection, though I included the output for show and tell);

(2) Clone the volumes;

(3) Attach the volumes to the appropriate reporting host.

(4) Mount the database to SQL Server.

The reporting host does not have to be the source SQL Server, and more often than not, the desire is to offload reporting to another host altogether. I will be running these cmdlets from the reporting host itself.

Note that you have the option to display a list of available snapshot collections by changing the "-MaxObjects" parameter. A value of one uses the latest snapshot collection. A value greater than one displays the recent number of collections, in order from most recent. Also, since cloned volumes inherit the ACL from the parent, I will be using the "-InitiatorGroup" parameter to specify the initiator of the reporting host. Access paths are assigned in alphabetical order.

Cloning Production Server Data to Reporting Host with NWT4 cmdlets

# Find the appropriate SnapshotCollection for the SQL Server database, change the MaxObjects variable to show more snapshot collections. #

$> Get-NimSnapshotCollection -AppObject "\SQL03\virtdb1" -MaxObjects 5

 

GroupMgmtIP          : 192.168.35.26

Name                 : sql03udb-hourly-2017-05-11::16:01:26.480

VolumeCollectionName : sql03udb

CreationTime         : 5/11/2017 4:01:26 PM

Snapshots            : {sql03udb-hourly-2017-05-11::16:01:26.480, sql03udb-hourly-2017-05-11::16:01:26.480}

GroupMgmtIP          : 192.168.35.26

Name                 : sql03udb-hourly-2017-05-11::15:01:27.660

VolumeCollectionName : sql03udb

CreationTime         : 5/11/2017 3:01:27 PM

Snapshots            : {sql03udb-hourly-2017-05-11::15:01:27.660, sql03udb-hourly-2017-05-11::15:01:27.660}

GroupMgmtIP          : 192.168.35.26

Name                 : sql03udb-hourly-2017-05-11::14:01:25.649

VolumeCollectionName : sql03udb

CreationTime         : 5/11/2017 2:01:25 PM

Snapshots            : {sql03udb-hourly-2017-05-11::14:01:25.649, sql03udb-hourly-2017-05-11::14:01:25.649}

GroupMgmtIP          : 192.168.35.26

Name                 : sql03udb-hourly-2017-05-11::13:01:26.935

VolumeCollectionName : sql03udb

CreationTime         : 5/11/2017 1:01:26 PM

Snapshots            : {sql03udb-hourly-2017-05-11::13:01:26.935, sql03udb-hourly-2017-05-11::13:01:26.935}

 

GroupMgmtIP          : 192.168.35.26

Name                 : sql03udb-hourly-2017-05-11::12:01:25.996

VolumeCollectionName : sql03udb

CreationTime         : 5/11/2017 12:01:26 PM

Snapshots            : {sql03udb-hourly-2017-05-11::12:01:25.996, sql03udb-hourly-2017-05-11::12:01:25.996}

 

GroupMgmtIP          : 192.168.35.26

Name                 : sql03udb-hourly-2017-05-11::16:01:26.480

VolumeCollectionName : sql03udb

CreationTime         : 5/11/2017 4:01:26 PM

Snapshots            : {sql03udb-hourly-2017-05-11::16:01:26.480, sql03udb-hourly-2017-05-11::16:01:26.480}

# Use the latest SnapshotCollection to create clones of the database volumes, and then mount cloned volumes to reporting host. Assigning mount points as part of the mount process.#

$> Get-NimSnapshotCollection -AppObject "SQL03\virtdb1" -MaxObjects 1 | InvokeCloneNimVolumeCollection -Suffix "-clone" -InitiatorGroup "sql04-fci-n1" -AccessPath "c:\sqlclone-virtdb1\data","c:\sqlclone-virtdb1\log"

 

DeviceName        : \\.\physicaldrive9

SerialNumber      : 8d164eae19e8766d6c9ce9008b7f40ca

GroupManagementIP : 192.168.35.26

NimbleVolumeName  : sql03-tl-clone

DiskSize          : 20964825

BusType           : iScsi

WindowsVolumes    : {C:\sqlclone-virtdb1\data\}

FCTargetMappings  : {}

Clone             : True

Snapshot          : False

BaseSnapshotName  : sql03udb-hourly-2017-05-11::16:01:26.480

ParentVolumeName  : sql03-tl

DeviceName        : \\.\physicaldrive10

SerialNumber      : bc8185b178dc434f6c9ce9008b7f40ca

GroupManagementIP : 192.168.35.26

NimbleVolumeName  : sql03-udb-clone

DiskSize          : 52428127.5

BusType           : iScsi

WindowsVolumes    : {C:\sqlclone-virtdb1\log\}

FCTargetMappings  : {}

Clone             : True

Snapshot          : False

BaseSnapshotName  : sql03udb-hourly-2017-05-11::16:01:26.480

ParentVolumeName  : sql03-udb

 

# Attach the database to the SQL Server #

$> $attachSQLCMD = @"

>>USE [master]

>>GO

>>CREATE DATABASE [virtdb1-clone] ON (FILENAME = 'C:\sqlclone-virtdb1\data\virtdb1.mdf'),(FILENAME = 'C:\sqlclone-virtdb1\log\virtdb1.log') for ATTACH

>>GO

>>"@

>>  Invoke-Sqlcmd $attachSQLCMD -QueryTimeout 3600 -ServerInstance "sql04-fci-n1"

Cloning a SQL Server Database for Development and Test:

 

Much like the reporting workflow, requested POC's for development and test usually have clones mounted on separate hosts. The difference I often see between these two workflows is that for development and test, virtual machines are commonly used as the destination systems. This workflow has a couple of extra pieces, and it is a little more complicated, as we need to interact with vCenter in order to attach volumes to the guest, but it is still worth showing, as it is high on my list of requests. I will also be making use of our REST API to modify the ACLs on the cloned volumes. Check out Julian Cates most recent post about API enhancements in NOS4:

Enhanced REST API in Nimble OS 4.

Don't be intimidated by the REST API functions defined at the start of the script block. The real work starts when the clones are created. The REST functions are completely portable, and can be used with any PowerShell scripting against Nimble Arrays running NOS3 and NOS4.

For instances where the guest is running iSCSI, the previous workflow would be used to attach the cloned volumes. The following workflow focuses on attaching cloned volumes as RDMs to the guest machine. It is broken down into five parts:

(1) Get the most recent snapshot information for the volumes backing the SQL Server database;

(2) Clone the volumes and assign ACL;

(3) Connect to vCenter via PowerCli, attach volumes to ESXi hosts and add the volumes as RDMs to the guest;

(4) Modify attributes of the cloned volumes on the Dev/Test guest, assign mount points;

(5) Mount the SQL database.

Like the previous workflow, I will be running all commands from the Dev/Test guest. PowerCli is also required for the workflow. An alternative to using the REST API is to leverage our PowerShell toolkit to assign ACLs.

Cloning Production Data to Dev/Test Host with NWT4 cmdlets and REST API

# Function Definitions for REST API. DO NOT EDIT THESE. Port as they are written.#

# Function to get token #

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }

function get-token

{

  param

  (

  [string]$array,

  [string]$uid,

  [string]$password

  )

  $data = @{

  username = $uid

  password = $password

  }

  $body = convertto-json (@{ data = $data })

  $uri = "https://" + $array + ":5392/v1/tokens"

  $token = Invoke-RestMethod -Uri $uri -Method Post -Body $body

  $token = $token.data.session_token

  return $token

}

# Function to create new ACL on volume #

function Create-ACL

{

  param

  (

  [string]$array,

  [string]$token,

  [string]$apply_to,

  [string]$vol_id,

  [string]$igroup_id

  )

  $data = @{

  apply_to = $apply_to

  initiator_group_id = $igroup_id

  vol_id = $vol_id

  }

  $body = convertto-json (@{ data = $data })

  $header = @{ "X-Auth-Token" = $token }

  $uri = "https://" + $array + ":5392/v1/access_control_records"

  $result = Invoke-RestMethod -Uri $uri -Method Post -Body $body -Header $header

  return $result.data

}

# Function to get the volume information for specific volume, so we can use the ID for other purposes, ie. adding an ACL #

function Get-volID

{

  param

  (

  [string]$token,

  [string]$array,

  [string]$volume

  )

  $header = @{ "X-Auth-Token" = $token }

  $uri = "https://" + $array + ":5392/v1/volumes/detail"

  $volume_list = Invoke-RestMethod -Uri $uri -Method Get -Header $header

  $vollist = $volume_list.data

  foreach ($vol in $vollist)

  {

  if ($vol.name -eq $volume)

  {

  $volid = $vol.id

  $volserial = $vol.serial_number       

  $volinf = $vol | select @{ Name = "Name"; Expression = { $volume } }, @{ Name = "VolID"; Expression = { $volid } }, @{ Name = "Serial_Number"; Expression = { $volserial } }

  $volinfo += $volinf

  break

  }

  }

  Write-Output $volinfo

}

# Function to get the igroup id for a specific igroup, so we can use the id for other purposes, ie. adding an ACL to a volume. #

function Get-igroupID

{

  param

  (

  [string]$token,

  [string]$array,

  [string]$name

  )

  $header = @{ "X-Auth-Token" = $token }

  $uri = "https://" + $array + ":5392/v1/initiator_groups?name=" + $name

  $igroup_list = Invoke-RestMethod -Uri $uri -Method Get -Header $header

  Write-Output $igroup_list.data.id

}

# Use the latest SnapshotCollection to create clones of the database volumes, and then assign ACLs with the functions listed above. #

$> Get-NimSnapshotCollection -AppObject "SQL03\virtdb1" -MaxObjects 1 | InvokeCloneNimVolumeCollection -Suffix "-cloneRDM" -DoNotConnect

DeviceName        : Unknown

SerialNumber      : 73e2b0c22991a2236c9ce9008b7f40ca

GroupManagementIP : 192.168.35.26

NimbleVolumeName  : sql03-tl-cloneRDM

DiskSize          : 20480

BusType           : Unknown

WindowsVolumes    : {}

FCTargetMappings  : {}

Clone             : True

Snapshot          : False

BaseSnapshotName  : sql03udb-hourly-2017-05-12::09:01:25.745

ParentVolumeName  : sql03-tl

DeviceName        : Unknown

SerialNumber      : a28d3b90d274efc36c9ce9008b7f40ca

GroupManagementIP : 192.168.35.26

NimbleVolumeName  : sql03-udb-cloneRDM

DiskSize          : 51200

BusType           : Unknown

WindowsVolumes    : {}

FCTargetMappings  : {}

Clone             : True

Snapshot          : False

BaseSnapshotName  : sql03udb-hourly-2017-05-12::09:01:25.745

ParentVolumeName  : sql03-udb

$> $token = Get-token -array 192.168.35.26 -uid admin -password XXXXXX

$> $db_clone_id = Get-volID -array 192.168.35.26 -token $token -volume sql03-udb-cloneRDM

$> $tl_clone_id = Get-volID -array 192.168.35.26 -token $token -volume sql03-tl-cloneRDM

$> $igroup_id = Get-igroupID -array 192.168.35.26 -token $token -name "ESX-HOSTS"

$> Create-ACL -apply_to "both" -array 192.168.35.26 -token $token -igroup_id $igroup_id -vol_id $db_clone_id.VolID

$> Create-ACL -apply_to "both" -array 192.168.35.26 -token $token -igroup_id $igroup_id -vol_id $tl_clone_id.VolID

 

# Attach cloned volumes to guest.#

$> Get-Cluster | Get-VMHost | Get-VMHostStorage -RescanAllHba -RescanVmfs

$> $snapdb = Get-VMhost | Get-ScsiLun | where { $_.CanonicalName -match "eui." + $db_clone_id.Serial_Number }

$> $snaptl = Get-VMhost | Get-ScsiLun | where { $_.CanonicalName -match "eui." + $tl_clone_id.Serial_Number }

$> New-HardDisk -VM "sql04-fci-n1" -DiskType RawPhysical -DeviceName $snapdb.ConsoleDeviceName

$> New-HardDisk -VM "sql04-fci-n1" -DiskType RawPhysical -DeviceName $snaptl.ConsoleDeviceName

# Change Nimble Volume attributes and assign mount point #

$> Set-NimVolume -SerialNumber $db_clone_id.Serial_Number -ReadOnly $false -ShadowCopy $false -Hidden $false -Online $true -Verbose

$> Set-NimVolume -SerialNumber $tl_clone_id.Serial_Number -ReadOnly $false -ShadowCopy $false -Hidden $false -Online $true -Verbose

$> Get-Disk | Where-Object -FilterScript { $_.SerialNumber -eq $db_clone_id.Serial_Number } | Get-Partition | Add-PartitionAccessPath -AccessPath "C:\sqlclone-virtdb1\db-rdm"

$> Get-Disk | Where-Object -FilterScript { $_.SerialNumber -eq $tl_clone_id.Serial_Number } | Get-Partition | Add-PartitionAccessPath -AccessPath "C:\sqlclone-virtdb1\tl-rdm"

 

# Attach the database to SQL Server #

$> $attachSQLCMD = @"

>>USE [master]

>>GO

>>CREATE DATABASE [virtdb1-clone] ON (FILENAME = 'C:\sqlclone-virtdb1\db-rdm\virtdb1.mdf'),(FILENAME = 'C:\sqlclone-virtdb1\tl-rdm\virtdb1.log') for ATTACH

>>GO

>>"@

>>  Invoke-Sqlcmd $attachSQLCMD -QueryTimeout 3600 -ServerInstance "sql04-fci-n1"

Final Note:

If you are interested in other Dev/Test workflows, be sure to check out the other SQL Server focused blog in this series:

NOS4 Use Case: Rapid Deployment of SQL Developer Containers with Nimble Storage.

In that blog post, we cover cloning production SQL Server databases to SQL Server Developer instances running in Windows Containers.

Also, check out these existing blogs in our NOS4 series that provide more information about NWT4, or cover use cases that leverage new functionality:

NWT 4: The “Power” of PowerShell cmdlets in Nimble Windows Toolkit 4.0, by Anagha Barve

Hyper-V VSS Requestor: ‌, by Jason Monger

About the Author

dgonzalez59

Comments
fgrahn80

This is by far the best documentation on how to do this...Fantastic document!

Events
Nov 27 - 29
Madrid, Spain
HPE Discover 2018 Madrid
Learn about all things HPE Discover 2018 in Madrid, Spain, 27 - 29 November, 2018.
Read more
See posts for
dates/locations
HPE at 2018 Technology Events
Learn about the technology events where Hewlett Packard Enterprise will have a presence in 2018.
Read more
View all