Just a quick post here after the PASS Marathon Webinar during which I talked about the GDPR effects around the world. In the talk, I demo’d SQL Provision, which is SQL Clone + Data Masker for SQL Server. Someone asked for the PoSh, so here it is.

# Connect to SQL Clone Server
$mycredential = Get-Credential
#$password = Get-Content .\socratescredential.txt | ConvertTo-SecureString
#$mycredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "home\sjones", $password
Connect-SqlClone -ServerUrl 'http://SOCRATES:14145' -Credential $mycredential
# Set variables for Image and Clone Location
$SqlServerInstance = Get-SqlCloneSqlServerInstance -MachineName 'PLATO' -InstanceName 'SQL2016'
$SqlServerDevInstance = Get-SqlCloneSqlServerInstance -MachineName 'PLATO' -InstanceName 'SQL2016_qa'
$ImageDestination = Get-SqlCloneImageLocation -Path 'E:\SQLCloneImages'
$ImageScript = 'e:\Documents\Data Masker(SqlServer)\Masking Sets\datamaskdemo.DMSMaskSet'
# connect and create new image
$NewImage = New-SqlCloneImage -Name 'GDPRImage2' -SqlServerInstance $SqlServerInstance -DatabaseName DataMaskerDemo -Destination $ImageDestination -Modifications @(New-SqlCloneMask -Path $ImageScript) | Wait-SqlCloneOperation
#Demo pause
Start-Sleep -Seconds 2
$DevImage = Get-SqlCloneImage -Name 'GDPRImage2'
# Create New Masked Image from Clone
$DevImage | New-SqlClone -Name "GDPR2" -Location $SqlServerDevInstance | Wait-SqlCloneOperation

This uses SQL Clone, and I’ve coded in my server here, just for simplicity. I assume you could use your own variable or parameter, but if not, you need to learn a bit more PoSh.

This script works as follows: First, I get credentials for my server. I have a lab domain, but my primary desktop isn’t on the domain for various reasons. As a result, I need to authenticate. The Connect-SqlClone cmdlet is used to do this.

Once I connect, I need to get the SQL Server instance used for cloning. In this case, I need two. One  ($SqlServerInstance) is production and one is development ($SqlServerDevInstance).

I set the location for the image, which is a folder here, but this should be a share in your environment as you typically have images used for multiple developers. If you watched the webinar, that was the warning that popped up since I used a local path and not a share.

The data masking is done with Data Masker for SQL Server. I’ve written a few pieces on this, but essentially the GUI creates a file that describes the masking rules. In this case, I set a variable to the file.

The creation of the image is with the New-SqlCloneImage cmdlet. This needs an instance to use for copying the data and then the parameters for the db and the masking script. The key here is the script needs to be a New-SqlCloneMask object. Hence the @(). If you use a T-SQL script, and you can, you need to use the New-SqlCloneSqlScript object.

From there I use New-SqlClone to cerate the actual database for the developers. I could automate this and create multiple ones if I wanted.

One last note, Wait-SqlCloneOperation is good in scripts as a few things take time to complete. I added the Start-Sleep for demos since things take longer at times, and in a demo seconds matter. I need the consistency. In an overnight script, I’d likely leave this out.