Sitecore Azure IaaS Dynamic Environments: Pt - 3 Creating an Elastic SQL Pool

Azure SQL PaaS offers users the ability to harness a pool of resources on a fully managed MSSQL server. Multiple databases can be created and placed into the pool for no additional charge. Resource utilization is measured by eDTUs. eDTUs are some what opaque in terms of quantifying what a eDTU is in traditional, but Grant Killian has a great post on benchmarking eDTU utilization with Sitecore. Some of the advantages of utilizing Elastic Pools include:

  • No patching or maintenance of your SQL servers
  • Scalable on fly
  • Copy existing databases on the fly

Starting with Sitecore 8.2 Elastic Pools are officially supported. From experience, I would NOT recommend using Elastic Pools pre Sitecore 8.2. For our model architecture we will be creating an Elastic Pool for our Dev/Test environments. We will be using the Standard 100 pool which offers 100 eDTUs for the pool, 100GB of storage, up to 200 databases, and a maximum of 100 eDTUs utilization per database.

The major advantage to using Elastic Database Pools with our architectural pattern is the fact that we can isolate each of our environments on the fly without incurring additional cost. Also, since our Production environment will also use an Elastic Pool we can easily restore fresh backups of production content to our Dev/Test environments with little to no effort.

Creating Our Elastic Pool

First up we need to create a new resource group for our feature test environments. Let's fire up pppppPOOOWWEEERRRSHELLLLL to take care of this.

###Creating A SQL Server An Elastic SQL pool requires a SQL server to run on. This can be easily created via PowerShell (surprise!). For our SQL password we are going to leverage Azure Key Vault. In part 2 we created our key vault, now we are going to add our first secret. The Key Vault is a secure way to store and manage credentials in Azure. In the next part of this series when we create our local development instance we will discuss how to access Secrets from our Key Vault. Now that our SQL server has been created, we need to create the Elastic Pool. First we need to create a firewall rule for the SQL server to allow us to communicate with it. In our example we have set the rule to allow traffic to 0.0.0.0, this allows all Azure traffic to our SQL server. As we progress we will remove this rule and create more specific rules based on environments. After our rule is in place we can then create our Elastic Pool. ### Importing the DACPACs using Visual Studio Team Services Sitecore 8.2 ships with DACPAC files for the databases. In this step we will be using Visual Studio Team Services to create a task that will allow us to easily deploy clean Sitecore databases over and over again with a click of a button. The first thing we need to do is create a new Visual Studio solution. In this solution create a project and copy the DACPAC files from a clean Sitecore install into the project(note: these don't need to be part of a project, they can simply be checked into version control, however I like having them in a project). Check this into your Visual Studio Team Services Git repo. Next go into your Visual Studio Team Services project and navigate to the Build & Release Tab. Click on the +New button to open the build definition tab. Select the Empty template. Next select your repository, default branch, and create a new folder named Database Tasks. Select Add build step, Deploy, Azure SQL Database Deployment For each database: Master, Web, Core, and Analytics add an Azure SQL Database Deployment step. I recommend using variables for most of the parameters so that you can easily reuse these tasks between environments. These tasks will create Azure SQL databases, but these databases won't be part of our elastic pool just yet. We need to create a final task that will move our newly created databases into the elastic pool. To do this we will create a PowerShell script and add an Azure PowerShell build task. Add the following Powershell Script to your Visual Studio solution and check it. Now that our tasks have been configured, we need to convert this group of tasks into a custom Task Group. This will allow us to reuse these steps in other build and release plans. Simply CTRL + Click each database and right click. Then click Create Task Group Finally run this build task, it is going to take a few minutes for the DACPACs to be imported and then imported into the Elastic Pool, however at the end of this, you will have a fresh set of Sitecore databases in your Elastic Pool. Anytime you need another fresh set, or to seed a new pool, you can simply update your parameters and run this task.