{"id":12615,"date":"2019-07-02T17:08:04","date_gmt":"2019-07-02T15:08:04","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/deploying-sql-server-on-azure-virtual-machine-with-terraform\/"},"modified":"2023-07-17T14:46:11","modified_gmt":"2023-07-17T12:46:11","slug":"deploying-sql-server-on-azure-virtual-machine-with-terraform","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/deploying-sql-server-on-azure-virtual-machine-with-terraform\/","title":{"rendered":"Deploying SQL Server on Azure virtual machine with Terraform"},"content":{"rendered":"

We are entering now to the infrastructure as code world and provisioning a SQL Server infrastructure is not excluded from the equation. This is especially true when it comes the Cloud regardless we are using IaaS or PaaS.<\/p>\n

\"\"<\/p>\n

One great tool to use in such scenario is certainly terraform and I introduced it during the last PowerSaturday<\/a> pre-conference named \u201cDBA modern competencies\u201d. Installation paradigms in the cloud differ from what we usually do on-premises either we are GUI-oriented or scripting-enthusiastic DBAs. In on-premises scenarios, building and delivering a software require both to deal often with a lot of hardware including servers, racks, network, cooling and so on. So, it makes sense to have one team for managing the hardware stuff (Ops) and another one to develop software (Devs). But nowadays, a shift is taking place and instead of managing their own data centers for some of their system components, many customers are moving to the cloud, taking advantage of services such as Azure, AWS and Google Cloud. This is a least what I begin to notice with some of my customers for some times now including some of their database environments. Instead of investing heavily in hardware, many Ops teams are spending all their time working on software, using tools such as Chef, Puppet, Terraform or Docker. In other words, instead of racking servers and plugging in network cables, many sysadmins are writing code. From a database administrator standpoint, I believe this is a good news because it will remove this boring part of the work without any real adding values.<\/p>\n

The question that may rise probably is why to use Terraform (or equivalent) rather than a script? I mean script as ad-hoc script here \u2026 Personally, I use PowerShell based ah-hoc scripts to install and configure SQL Server for a while now at customer shops and PowerShell is part of IAC (Infrastructure As Code) tooling category. But using ad-hoc scripts come with some drawbacks. First of all, using a programming language like PowerShell implies you have to write completely custom code for every task and it is not a big deal as long as you manage few components in the infrastructure. But what if you’re\u00a0 dealing with hundred or thousand of servers including databases, network, load balancers and so on? Do you really want to maintain a big and unmaintainable ad-hoc script repository especially if you work in a collaborative way? In this case you have to rely on a tool designed for such job. Furthermore, writing an ad hoc script that works once isn\u2019t often too difficult but writing one that works correctly even if you run it over and over again is a lot harder and impotent code is an important part of modern infrastructure.<\/p>\n

In my case, we also investigated on different configuration management tools like Ansible but they are mostly designed to install and manage software on existing servers. In the context of the cloud, in most cases servers do not exist and you have to provide one (or many) including the related infrastructure like virtual network, virtual IP, virtual machine, disks and so forth \u2026\u00a0 So here comes the big interest of using Terraform in such scenario. Terraform<\/a> is an Hashicop product and comes with open source vs Enterprise version. I’m using the open source one with 01.2 version at the moment of writing this blog post as shown below:<\/p>\n

$ terraform version\nTerraform v0.12.0\n+ provider.azurerm v1.31.0\n+ provider.template v2.1.2<\/pre>\n
In my case, it may be very helpful to provision servers on Azure. Microsoft already invested a lot to introduce Terraform as provisioning tool for different Azure services including SQL Azure DBs with azurerm_sql_database<\/a> or azurerm_sql_server<\/a> providers. But the story is not the same when it comes SQL Server virtual machines on Azure. The only provider available to provision an Azure VM is azurerm_virtual_machine<\/a> but it doesn\u2019t include the SQL Server configuration part. In this blog post I want to expose some challenges I faced to make it possible and you probably want to be aware of \ud83d\ude42<\/p>\n
Let’s say that in fact, we have to rely on the azurerm_template_deployment<\/a> provider in this case but at the cost of some complexity. Indeed, you have to introduce the SQL Server template Microsoft.SqlVirtualMachine\/SqlVirtualMachines<\/a> to the Terraform files with all the difficulties that is implies. First, from my opinion adding a SQL Server template file leads to additional complexity that is at the opposite of what we expect from Terraform. We lose some code simplification and maintainability somewhere and this is by far my main disappointment. Anyway, in addition let’s say that debugging a Terraform deployment with Azure templates may be time-consuming task especially if you have to deal with syntax error issues. You often have to take a look at the Azure event logs directly to know what is happening. Second, the parameters are all string type and we need to convert them to the right type with Azure template variables. Finally, as far as I know, it is not possible to use arrays as input parameters within the Azure template and refactoring from the initial server template is requested accordingly. This is the case of the SQL Server dataDisks configuration parameter for instance. I don\u2019t know if Microsoft is planning something on this topic but my expectation is to get something more \u201cTerraform-integrated\u201d in the future.<\/p>\n
Let\u2019s finish this blog post with my Terraform files used in my SQL Server VM Azure provisioning workflow that is as follows:<\/p>\n
Create a resource group <\/strong>=><\/strong> Create a virtual network + subnet <\/strong>=><\/strong> Create SQL Server Azure VM with custom configuration<\/strong><\/p>\n