Using Ansible to deploy Microsoft SQL Server 2019 on Red Hat Enterprise Linux 8
![Using Ansible to install MSSQL 2019 on RHEL](/sysadmin/sites/default/files/styles/full/public/2020-10/Using%20Ansible%20to%20install%20MSSQL%20on%20Linux.jpg?itok=9UB8DoWw)
I have been asked multiple times by colleagues and customers alike about the easiest way to test/play with Microsoft SQL Server 2019 on Linux. So I thought, "I need to find a way to show this to them because I am sure they are not the only ones asking this question." In my mind, the answer is, of course, using Ansible. But where to start?
One fine weekend while watching a rugby match between the mighty All Blacks and the Wallabies, I sat in front of my computer and started to look for existing playbooks/roles available. I found a couple of playbooks, but I was not satisfied. Someone new to both Ansible and Linux would still require a bit of guidance. So my mission now is to make sure that one can just clone a repo and run a playbook to get Microsoft SQL Server 2019 (MSSQL) server running on Red Hat Enterprise Linux 8 (RHEL 8).
Hence, this article was written to provide the easiest way to deploy SQL Server 2019 (MSSQL) on Linux (RHEL 8).
But before we jump into the installation, one very important question that we also need to answer is, "Why deploy MSSQL on Linux?" Let me share some of my thoughts.
One should consider installing MSSQL on Linux because of the additional flexibility, security, performance, and innovation it inherits by just running on a Linux server.
[ You might also like: Getting started with Ansible ]
For example, just turning on SQL Server trace flag 3979, which is only supported with XFS on RHEL 8, can reduce IO contention to 50% for enterprise-class storage. There is also improved support for multi-queue scheduling, TCP stack enhancement that benefits replication, and supports persistent memory in enlightened mode, delivering SQL Server Enterprise Edition performance at a SQL Server Standard Edition price. In short, it's much faster.
Additional links that talk about performance improvements are here:
- Optimizing performance with Microsoft SQL Server 2019 on RHEL 8
- Cisco Dominates Microsoft SQL server…again
-
HPE ProLiant DL325 Gen10 Plus Wins 1P WORLD Records on TPC-H Benchmark @ 3000GB (Non-Clustered)
In addition to this, Microsoft provides the top six reasons companies should move MSSQL Server to Linux.
If you are curious and want to try MSSQL 2019 on RHEL 8, let's start the installation.
Prerequisites
Before beginning the installation, let's make sure you meet the following prerequisites:
- Installed RHEL 8
- The OS is updated to the latest version
- Valid subscription for RHEL 8
- The latest Ansible version is installed
- Git is installed on your systems
- Internet access
MSSQL 2019 Server installation
Before diving straight into it, let's review the playbook and the variables you will run to install and configure your MSSQL 2019 server.
Playbook:
- hosts: localhost ⇨ Define your target hosts
become: yes ⇨ We need root access to deploy MSSQL Server
roles:
- pre-reqs ⇨ The roles that takes care of the prereqs
- ansible-role-mssql ⇨ The role that install/configure MSSQL Server
tasks:
- name: Wait up to 60 seconds for server to become available after creation
wait_for:
port: 1433
timeout: 60
- name: Create new db
Include_role: ⇨ Additional role to create the DB
name: ansible-role-mssql
tasks_from: new_db
Variables:
# These are required for database installation
end_user_license_aggreement_consent_server: Y # Must be Y or N
end_user_license_aggreement_consent_cli: "YES" # Must be YES or NO in all caps within quotes
edition: evaluation
# For use when creating, importing, or deleting databases
db_name: testDB
db_host: 127.0.0.1
db_port: 1433
db_user: sa
db_password: P@ssWORD!
Now, let's deploy
1. Clone the repo here:
$ git clone https://github.com/mikecali/mssql2019-roles-RHEL8
2. Change directory to mssql2019-roles-RHEL8
:
$ cd mssql2019-roles-RHEL8
3. Execute the playbook to install and configure MSSQL 2019 and then create a new database called testDB:
$ ansible-playbook site.yaml -e @vars.yaml -vvv
If successful, you should expect an output similar to below:
PLAY RECAP ***********************************************************************************************************************************************************************************************************************************
localhost : ok=18 changed=15 unreachable=0 failed=0 skipped=1 rescued=0 ignored=0
4. Verify the installation:
$ systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2020-10-18 14:58:10 EDT; 6min ago
5. Log in to MSSQL and verify the version:
$sqlcmd -S localhost -U SA -P 'P@ssWORD!'
Then execute the version check:
> SELECT @@version
2> GO
----------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)
Sep 23 2020 16:03:08
Copyright (C) 2019 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Linux (Red Hat Enterprise Linux 8.2 (Ootpa)) <X64>
(1 rows affected)
Then you can also verify if the database was created:
1> EXEC sp_databases
2> GO
At this point, you will have a successful deployment of MSSQL 2019 Server on RHEL 8.
8. To clean up, run this playbook:
$ ansible-playbook site-del.yaml -e @vars.yaml -vvv
And you're done.
[ Looking for more on system automation? Get started with The Automated Enterprise, a free book from Red Hat. ]
Wrap up
My goal was to provide a simple, straightforward way to help Linux users get going with Microsoft SQL Server 2019. Managing the deployment through Ansible and RHEL 8 was a natural approach for me, and one that I think has worked well. Microsoft SQL Server 2019 is an important tool, and now you're ready to start exploring it.
References:
![Broken nails](/sysadmin/sites/default/files/styles/card/public/2019-08/broken-builder-carpenter-nails.jpg?itok=Z8UaoA9p)
![Factory](/sysadmin/sites/default/files/styles/card/public/2019-07/factory-conveyor-belt.jpg?itok=ACSE4z2d)
![Gears](/sysadmin/sites/default/files/styles/card/public/2019-08/art-clockwork-cogs-gears.jpg?itok=HNX8UtbL)
![Author’s photo](/sysadmin/sites/default/files/styles/user_picture_square/public/pictures/2020-10/MCalizo.jpg?itok=8z5xKrap)
Michael Calizo
Mike Caliizo is a Red Hat Solutions Architect based in New Zealand. His technology focus areas are OpenShift, RHEL, Satellite, Ansible. More about me