SQL Server Replication–Of the Duplication Kind

Have you ever found yourself in the situation where you need to have multiple SQL Server environments that are exactly the same that you have the ability to spin them up and tear them down when you need them very quickly and knowing that they are exactly the same???? Well in this short post we will take you through how you can create your own environment that provides you this exact requirement and not have it cost you a lot of time, effort or money.

What do we need to start? A couple of things in preparation for being able to put this into practice:

1. An Azure subscription
2. A working knowledge or understanding of PowerShell
3. An environment that you have preconfigured that you want to duplicate

With our points above in place we are ready to start our journey of being able to quickly and easily create and tear down 2,5, 100 servers in a non-production environment to provide you all of the working copies of your SQL Server environment to meet your development or testing requirements. What we are going to take you through in this post can easily assist you if you are starting out on your Database DevOPs journey.

Now I am not going to take you through how to have preconfigured your SQL Server environment that you are wanting to duplicate. I am assuming that you are proficient enough to already have that VM in Azure and have it configured with all of settings and databases that you are going to be using.

Now you should also be considering that the data that is in your databases on the VM you are wishing to replicate is appropriate for the environments that you are going to be duplicating. Do you have sensitive data that will be made available in a non-production environment to users who should not be accessing that data? Have you thought about how you are able to protect the data? Why not have a look at Redgate’s SQL Data Privacy Suite ?

This is your starting point, and if you are not ready with that environment yet, you can continue on and understand the process and come back to this when you are ready to undertake this implementation in your environment.

Why would we want to do something like this? Well we may have multiple dev teams who all need to be working on exactly the same copy of the environment to ensure the changes they are working on are valid, We may be wanting to deliver some training and ensure that all of our team members are able to have the same experience and be able to quickly spin up a number of exact duplicate servers for all to be working on.

Like all things we do with technology, the preparation we undertake and put into this provides us with the ability to complete our tasks quicker and with less wasted time. With our Azure subscription we can look at creating our PowerShell scripts to connect and undertake all of the task covered in this post.

At a high level we are going to undertake the following steps with PowerShell:

1. Create a Snapshot of the operating disk of the server we wish to replicate
2. Create a new Resource Group in Azure to store our new resources per VM we wish to spin up
3. Create a virtual server using the snapshot copy of the operating system disk

Now we have our environment we are going to be replicating, and the high level tasks we are going to be following we can get started and create some PowerShell to allow for this process to be repeatable and easy to use and only having to change 5 parameter configurations to allow our PowerShell script the ability to cater for different situations. Opening up the PowerShell IDE or VS Code we are able to start.

Login to Azure Subscription

Given we are replicating or duplicating servers in Azure we need to connect to our Azure Subscription. This is an interactive activity every time you wish to run this process. You can create your script to not require a manual login but for this post and script we are undertaking this on an interactive login as it allows you to easily change users or subscriptions.

Parameter Setting

To allow for our script to be re-usable we are going to set some common parameters that will vary based on how many different environments we wish to be able to use the single script for. In this post and this script we are setting the following 5 parameters:

• Snapshot Disk Name
• Server Name to snapshot and create
• Snapshot Name
• No. of VMs to spin up
• Location

The Snapshot Name is the name of the Master Server Snapshot that is to be created and stored in the existing resource group that the master virtual server resides in.

The Server Name to snapshot has dual purposes. The replicated servers, we want them to be named the same, so that we are not needing to make any modifications to the newly created virtual server. From a training perspective, this makes it very simple with all attendees having the exact same server as the trainer and there is no confusion.

The Snapshot disk name is the name given to the snapshotted disk that is copied to the appropriate newly created resource group.

The No. of VMs to spin up, as it’s name indicates is the number from 1 to x VM’s we wish to create. By setting this one (1) value has the advantage of us being able to create 1 or multiple VM’s of the exact duplicate that we are wishing to and given we are using an imported function allowing us to undertake tasks in parallel, the time it will take to complete 1 or 100 VM’s is exactly the same amount of time and that is the beauty of this process.

Create Snapshot

The beauty of this process is that to create the snapshot of the virtual server is a single line of powershell code. There is some prep work but we are able to create the snapshot using the powershell cmdlet – New-AzureRMSnapShot

The next steps in the script are grouped together inside of a scriptblock that is able to be used with the imported Invoke-Parallel function to allow us to easily create multiple copies of the same server and all we need to adjust is the number we wish to create. This is the beauty of automating and scripting this process, it does not matter if we are spinning up 2 copies or 10 copies, changing 1 parameter and the process just runs. Because of this we group the creation of the new resource group and new virtual server inside of the script block.

When you are reviewing the code, you will see that we have added some try..catch statements to ensure that if 1 process fails the following processes will not be run.

Enjoy the start of your journey into the world of Database DevOPs in Azure and Powershell. You can download the powershell script MultipleVMs here.

I would like to thank my good friend and colleague Rob Sewell (B | T) for the review and his assistance with the powershell script.