Deploy SQL Server – The Ansible way!

Today, we'll look at how to automate SQL Server deployment and configuration on Linux. To automate our deployment, we will use the Ansible system role, which is available here.

Note: The Ansible system role that I use in this blog is a sample system role that is provided as is and for reference only. Microsoft and Red Hat do not support this. However, I invite you to provide feedback and suggestions for improving the system role here: Issues linux-system-roles/mssql (github.com).

Update as of July 2022: Please note that SQL Server system roles are now available as default and more information is available here: Configuring Microsoft SQL Server using Microsoft SQL Server Ansible role Red Hat Enterprise Linux 8 | Red Hat Customer Portal

To begin, we must first prepare the environment. Here are the steps we will follow in this blog.

1. Configure a RHEL 8-based host machine to serve as the controller node.
2. We will install the SQL Server system role on this host.
3. We then configure passwordless ssh access between the controller node and all managed servers where SQL Server is to be installed. You can skip this step if you already have passwordless ssh access configured for a service account with sudo access between the controller node and all the other managed nodes.

Solution Diagram for reference:

Let's get started; I'll use three Azure VMs to set up this environment:

• A RHEL-8 based Azure VM to serve as the controller. On this node, I'll install Ansible, followed by the SQL Server system role.
• One Ubuntu-based Azure VM and one more RHEL-based Azure VM will be our managed/target machines for deploying and configuring SQL Server via the system role.

Setting up the controller

• First, I'll install Ansible on my RHEL 8 Azure VM. Please see Installing Ansible — Ansible Documentation for more information. On my  VM, I already had python3 and pip3 installed, so I ran the following commands to install ansible.

  #Confirm pip3 is installed
  pip3 –help
  #Now install ansible using the command pip3
  pip3 install ansible ​

• You should now be able to view the ansible version with the following command: ansible --version

• Following ansible installation, a hosts file is created in /etc/ansible.  You can edit this hosts file using your favorite editor and add the managed/target node details as a group entry or as ungrouped entries. Here is a sample for your reference I have added the IP address 10.0.0.12 and 10.0.0.14 which are my managed nodes.

Setup passwordless ssh access between the Control node and the managed nodes.

Please follow the steps below only if you do not already have passwordless ssh access configured between the controller and the target/managed nodes.

Note: In this example, I am using the root user, in a production environment this is not recommended. Instead of the all-powerful root, use a regular user account with sudo access.

• On the RHEL-based controller node, use the command ssh-keygen to generate SSH keys. When you run the command, accept the defaults, and when the command is finished, you should have a private and public key.
• Now, on the managed/target servers, copy the public key. Run the following commands to copy them to the targets 10.0.0.12 and 10.0.0.14 : ssh-copy-id root@10.0.0.12 and ssh-copy-id root@10.0.0.14. The -i option of the ssh-copy-id command allows you to specify the path to the public key. When you run the above commands, you will be prompted to enter the root password for the hosts to which you are connecting, and once you do, the public key is copied to the host.
• Run the following commands to see if passwordless root ssh is working: ssh root@10.0.0.12 and ssh root@10.0.0.14. Both of these commands should work and you should be logged in to the hosts.

Install the SQL System role & configure the playbook.yaml:

• On the RHEL 8-based controller node, run the following command to install the SQL Server system role:

  ansible-galaxy collection install microsoft.sql​

• This installs the role in the following directory: /root/.ansible/collections/ansible collections/microsoft/sql, with the files shown below:

• Next, we'll create the SQL Server playbook yaml file. To understand the various role variables, refer the documentation or the README.md included with the SQL system role. Here is a yaml playbook sample with role variables defined to configure SQL Server and enable additional functionality:

  - hosts: all
    vars:
      mssql_accept_microsoft_odbc_driver_17_for_sql_server_eula: true
      mssql_accept_microsoft_cli_utilities_for_sql_server_eula: true
      mssql_accept_microsoft_sql_server_standard_eula: true
      mssql_password: "YourP@ssw0rd"
      mssql_edition: Evaluation
      mssql_enable_sql_agent: true
      mssql_install_fts: true
      mssql_install_powershell: true
      mssql_tune_for_fua_storage: true
    roles:
      - microsoft.sql.server​

  

   

Deploy SQL Server on managed/target machines using the system role:

To deploy SQL Server on managed servers, execute the following command from the controller node:

ansible-playbook -u root playbook.yaml

This will begin the deployment, and at the end, you should see a summary of the play, with the title "play recap," that looks like this:

PLAY RECAP *******

10.0.0.12                  : ok=31   changed=42   unreachable=0    failed=0    skipped=0   rescued=1    ignored=0

10.0.0.14                  : ok=31   changed=42   unreachable=0    failed=0    skipped=0   rescued=1    ignored=0

The figures in your case may differ. For a hands-on experience, try the Red Hat Enterprise Linux Interactive Lab Portal lab, which is provided by our friends at Red Hat, Inc. I hope this helps you get started, and please share your thoughts and suggestions about the system role here: Issues linux-system-roles/mssql (github.com).