Quick PoSh SQL Provision Databases for New Developers

As part of some presentation work, I wanted to demonstrate some onboarding of new developers. To that end, as part of a demo, I wanted to build a script that would take a few parameters and generate some SQL Provision databases for development. The idea is that an admin can have a script here to set up a developer with a database for a project with SQL Provision. I imagine this would be part of a script that also cloned a repo, set up a project, etc.

The Process

My thought here was that I needed a script that uses some pattern to build databases. The one I decided on was that the basic image name would be used with the developer name to prevent collisions. This would work if an admin used login names or even nicknames, as long as there is some uniqueness on the instances. Of course, if you’re provisioning to a local instance, this doesn’t matter.

In any case, the images I make are often date driven, with a format like:


Using formats like this, I can easily strip off the beginning part of the name (SimpleTalk) and then append my _developer to the end. For me, this means I’d get a database named: SimpleTalk_Steve

This manifests itself as a script:

2018-10-03 20_09_05-NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

That produces a new database.

2018-10-03 20_08_57-SQLQuery6.sql - Plato_SQL2016.DataMaskerDemo (PLATO_Steve (61)) - Microsoft SQL

I also see this in the SQL Provision dashboard as a new item.

2018-10-03 20_10_14-Microsoft Edge

The Script

I started this with some simple PowerShell. The first part of this script is a few parameters and a variable. Note that I’m splitting the instance name here.

2018-10-03 20_11_48-● NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

From here, I connect to the SQL Provision server and then get the instance and image objects.

2018-10-03 20_12_01-● NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

Lastly, I create the clone, making a new name from the image, and if the word “base” is included (I do this often), I strip it out.

2018-10-03 20_12_06-● NewDeveloper.ps1 - Minimize impact Dev Test Environments - Visual Studio Code

Quick and easy, but this allows me to demo how to onboard a new developer.

There are lots of enhancements, and I need to add some error checking if there isn’t an instance, and if the connection fails, but for now, PoSh lets me quickly start getting some useful scripts that I can use for demonstrating some functionality. This certainly would work in an environment where I knew the Clone server was there and I had control over imaging. If not, I’d be writing more PowerShell.

SQL Provision is pretty amazing and lets you really leverage technology to provide developers with copies of databases in seconds. Download an evaluation and see what you think.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.