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).
To begin, we must first prepare the environment. Here are the steps we will follow in this blog.
Configure a RHEL 8-based host machine to serve as the controller node.
We will install the SQL Server system role on this host.
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
#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 email@example.com and ssh-copy-id firstname.lastname@example.org. 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 email@example.com and ssh firstname.lastname@example.org. 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: