SQL Server 2014 Prod/Dev Refresh with VMware PowerCLI and Pure Storage PowerShell Toolkit

One of the demonstrations that I created for VMworld 2014 was to create a simple orchestration for refreshing a SQL Server 2014 database volume and re-connecting  to a VMware Guest rapidly and then querying the database. In the demonstration VMware PowerCLI, Pure Storage PowerShell Toolkit (REST API) and Windows PowerShell Extensions for SQL Server 2014 are used.

Here are two videos that shows how the orchestration works, first step-by-step and second one shows this running in real time.

How it works (step-by-step)

How it works (real time – 8 seconds)

Sample PowerShell

There are improvements that can be made with the above PowerShell but this was created as a demonstration to envision what can be accomplished using Pure Storage FlashRecover capabilities and our REST API integration with PowerShell.

The example above assumes a few things.

  1. A SQL_MASTER volume existing with the AdventureWorks2014 database. Download a copy of this database from https://msftdbprodsamples.codeplex.com/releases/view/125550.
  2. A new volume created named SQL_TEST based on SQL_MASTER source.
  3. You have SQL Server PowerShell installed, if you need it download the following Microsoft SQL Server 2014 Feature  Pack files (ENU\x64) and install in this order:
    • Microsoft System CLR Types for Microsoft SQL Server 2014 (SQLSysClrTypes.msi)
    • Microsoft SQL Server 2014 Shared Management Objects (SharedManagementObjects.msi)
    • Microsoft Windows PowerShell Extensions for Microsoft SQL Server 2014 (PowerShellTools.msi)

This demonstration example uses one of the new cmdlet functions Refresh-PfaVolume to refresh a volume, see my post “Pure Storge PowerShell Toolkit Enhancements” for more details.

barkz

  • Pingback: Top 5 – Pure Storage Technical Blog Posts 2014 | 2vcps and a Truck()

  • Does this script require the VM to have a direct connection (NPIV) to the LUN(s) on the Pure array? I’m guessing the disks are not VMDKs inside a datastore on a LUN attached to a VMware Host.

    • The demonstration here is using Raw Device Maps (RDMs) that can either be manually or programmatically connected using PowerCLI.