Provisioning Microsoft SQL Server 2022 with VMware Aria Automation

In this blog, we will discuss setting up a Windows Server 2022 virtual machine template, installing and configuring Cloudbase-init, configuring Aria Automation and downloading the MSSQL Server 2022 IaC YAML Aria Automation template from VMwarelab GitHub Repo.

I wanted to provision a Microsoft SQL Server 2022 by requesting it from the VMware Service Broker Self-service catalog within VMware Aria Automation. Since I am unfamiliar with MSSQL, it took a lot of trial and error and hours of testing and research so my beloved readers don’t have to. The result is an astonishing great time-to-value provisioning of MSSQL Server 2022 in minutes. I hope you find it useful and informative, opening the door to other use cases.

There are other ways to do this post-provisioning, for example, Aria Automation integration with Ansible(Opensource and Tower), Puppet, Tanzu Config (Saltstack), and/or Extensibility ( ABX and Orchestrator ). Personally, Cloud-init for Linux and Cloudbase-init for Windows is the simplest and lightest, despite a few challenges that you may encounter.

Note Disclaimer: The provisioned virtual machine uses network DHCP IP assignment without vSphere Customization Specification in my setup. Since I have not tested these configurations, the result may vary if you configure the template to use static IP assignment and/or custom specs.

Let’s get started!

• vSphere Windows Server 2022 Template Setup:
  • Create a Virtual machine using the vSphere Client to install/build a Windows Server 2022 that will later convert to a template.
  • Update Windows by installing the required updates or if updates are available.
  • Download the SQL Server 2022 ISO image from Microsoft, mount the downloaded ISO in File Explore of the virtual machine and copy its content to a folder called. c:mssql-install.
  • Don’t forget to Unmount the SQL Server 2022 ISO once the copy is completed.
  • Download the SQL Server Management Studio (SSMS). Download Link and copy or move the file SSMS-Setup-ENU.exe to c:mssql-install as well.
  • In Server Manager – > Local Server. Make sure that you configure the following:
    • Remote Desktop: Enabled
    • Defender Firewall: Public: Off
    • Defender Antivirus: Real-Time Protection: Off
    • IE Enhanced Security Configuration: Off

• Set the PowerShell Execution Policy to Unrestricted by opening the PowerShell command line and executing. Set-ExecutionPolicy Unrestricted

• Install Cloudbase-init

• Aria Automation Configuration Setup:
  • Create a secret called mssql-productkey and provide your MSSQL Server 2022 product key as the value.
  • Create a secret called windows-administrator-password and provide the built-in Administrator password for the template we set up earlier.
  • Create a Flavor Mapping called medium Configured for your vCenter cloud account/region.
  • Sync Images of the vCenter cloud account where the template was created in Aria Automation by navigating to Assembler -> Infrastructure -> Connections -> Cloud Accounts -> vCenter Cloud Account -> SYNC IMAGES.
  • Create Image Mappings called win2k22-mssql-cloudbase-init and select the template we configured.
  • Download and Upload the Microsoft SQL Server 2022 Template to Aria Automation
  • Download the template from GitHub Download Link.
  • Create a new template by Uploading the downloaded template or creating a new one, copying and pasting the YAML code, and then assigning it to a project by navigating to Assembler -> Design -> NEW FROM -> Upload / Blank canvas.
    • Now that you have seen the template content and probably have it open, here is an explanation breakdown of what we are doing within #cloud-config code: (Click to enlarge)
      1. The cloud-config starts with setting the built-in Administrator password since once the VM is provisioned, the cloud base sysprep forces you to change the template’s original password, having to use the vSphere VM Console to do that, and until that is done, users can’t remote desktop right away to the provisioned VM, so they would have to know the original Administrator password which is unlikely. If you don’t want the user to use the administrator account, you can remove lines #31-33, including the comment on line #28.
      2. Starting with line #34, we create a new user admin account that the user provides.
      3. In line #39, we create a PowerShell script with the commands we want to execute.
      4. In line #46, we have our MSSQL setup parameters, which was the hardest part for me to figure out through hours of testing. As you can see, I’m also using template bind expressions to pass the product key and the SA password. You can get creative here and add more of the supported installation parameters and expose them to the user using input bind expressions so they can set some of these parameters.
      5. In line #49, we execute the MSSQL command with all the defined parameters.
      6. In lines #51-57, we install the SQL Server Management Studio (SSMS), which we can use to access the database with the administrator and/or the admin user provided by the requester when using Windows Authentication, and the database’s built-in SA account when using SQL Server Authentication.
      7. In line #59, we set the hostname to match the generated VM name based on the Custom Naming Policy setup in Aria Automation.
      8. Finally, on line #61, we run the actual PowerShell script with all the above.
  • Within the template, YAML IaC, update lines #21 and #69 with your own cloud zone capability tag.
  • Create a version for the template and release it to the Service Broker so it’s available as content.
  • [Optional] Configure the item catalog with a customs form and MSSQL Server 2022 icon for a professional look
  • Add the content to the appropriate content-sharing policy depending on which project you want to entitle it to.
  • Finally, request MSSQL Server 2022 from the Service Broker catalog.

Please note that when logging in for the first time, you may need to follow the Microsoft instructions here since the computer name was renamed from the original source template after installing MSSQL Server. So, in our case, since we renamed a computer hosting a default instance of SQL Server, we execute the following query within SQL Server Management Studio, followed by an MSSQL Service restart.

EXEC sp_dropserver '<old_name>';
GO
EXEC sp_addserver '<new_name>', local;
GO

Now, to confirm that the server Name within the Master Database has been updated run the following query:

SELECT @@SERVERNAME AS 'Server Name';

You should see that the computer name referenced in the MSSSQL Server Master database matches the newly generated VM/MSSQLServer Name.

Conclusion

VMware Aria Automation, within VMware Cloud Foundation, stands out as a comprehensive automation platform that significantly enhances the delivery and management of IT services. Leveraging features such as Infrastructure as Code, a self-service portal, policy-based governance, multi-cloud support, and robust automation and orchestration capabilities ensures a more efficient, consistent, and secure approach to IT operations.

If you liked this content, please like and subscribe to my Blog and YouTube channel so you don’t miss any of my future posts.

Stay tuned, and happy reading!