Using Ansible to deploy Microsoft SQL Server 2019 on Red Hat Enterprise Linux 8

Installing Microsoft SQL Server 2019 on Red Hat Enterprise Linux using a single playbook.

Posted: November 2, 2020 by Michael Calizo (Red Hat)

Using Ansible to install MSSQL 2019 on RHEL — "database schema" by gnizr is licensed under CC BY 2.0

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:

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: