How to create SQL Server as a Service in vRA8

I was checking out the Azure catalog and came across their SQL server offering. I decided to create my own on-prem version of SQL Server as a Service using VMware vRealize Automation.

I decided to leverage the Cloud Template from my previous blog on adding multiple disks. It has been modified to have the inputs specific to the SQL server. 

The catalog item will let the user specify the service accounts for the SQL server and the SQL server agent, size of the database directories, collation and instance name.

All the fields shown above are user inputs in the Cloud Assembly template and are also passed through as host variables to Ansible.

For the installation of the SQL server, I am using Ansible. Ansible integrates natively with vRealize Automation so I am making the use of it. I have used ansible.windows.win_dsc module to do the installation of the server. My playbook has some tasks that perform some preliminary checks on the vm such as checks for the presence of required powershell modules and .NET framework. The installation media is copied from a network share to the temp folder on the VM. I do not have access to iscsi so I am not using LUNs. Instead I have vmDisks attached to the VM. The main task for the actual install is below.

- name: Extract the installation media
    win_shell: "{{ temp_path }}\SQLServer2017-DEV-x64-ENU.exe /X:{{ temp_path }}\Media /Q"
  - name : Install SQL Server
    win_dsc:
      resource_name: SQLSetup
      Action: Install
      UpdateEnabled: True
      SourcePath: "{{ temp_path }}\Media"
      InstanceName: "{{ instanceName }}"
      InstallSharedDir: "{{ sharedFeatureDir }}"
      InstallSharedwowDir: "{{ sharedFeatureWowDir }}"
      SQLCollation: "{{ mssql_collation }}"
      SQLUserDBDir: "{{ sqlDataDriveLetter }}:\DatabaseFiles"      
      SQLUserDBLogDir: "{{ sqlLogDriveLetter }}:\Log"
      SQLTempDBDir: "{{ tempdbDriveLetter }}:\TempDB"
      SQLTempDBLogDir: "{{ templogDriveLetter }}:\TempLog"
      Features: "{{ mssql_features }}"
      BrowserSvcStartupType: "{{ mssql_browsersvc_mode }}"
      SuppressReboot: "{{ mssql_suppress_reboot }}"
      SQLSvcAccount_username: "{{ mssql_sqlsvc_account }}"
      SQLSvcAccount_password: "{{ mssql_sqlsvc_account_password }}"
      AgtSvcAccount_username: "{{ mssql_agentsvc_account }}"
      AgtSvcAccount_password: "{{ mssql_agentsvc_account_password }}"
      SQLSysAdminAccounts: "{{ mssql_sysadmin_group }}"
    tags: install_mssql

Once the Deployment is completed, I can see that my named instance of SQL server has been created and the correct directory structure is there. The short video below shows the demo.

I wish to improve on it and make it more “Production Ready” by utilising iscsi LUNs. I will also be attempting to leverage the above Template to create a “Database as a Service” offering.

TAGS: DESIGN, VRA CLOUD, ANSIBLE, SQL, SQLSERVER,VRA CLOUD ASSEMBLY, AUTOMATION,HYBRIDCLOUD,VRA8.X