SQL Server on Linux

Hello,

Back in Nov 2016, Microsoft has announced SQL Server on Linux and being a Database enthusiast I would like to hands on and see how it works, but I am been busy with some other projects and could not look into it. Last week I had a conversation with one of my peer in lift and he asked me a question how it works that reminds me this topic again. My first answer to him was , there must be some component or a process  which converts a windows calls from SQL platform to Linux abstraction layer which is must, the second in the list was compatibility workarounds. Without looking into any document I was sure about it since Microsoft (or anyone) would never do a overhaul or rewrite SQL Server Platform into Linux. Well, they found themselves with internal projects which offers the bridge between Windows and Linux called "DrawBridge" and evolved something called "SQLPAL" to make sqlserver platform work on linux.

You can read more about SQLPAL here from the Microsoft technet blog: https://blogs.technet.microsoft.com/dataplatforminsider/2016/12/16/sql-server-on-linux-how-introduction/

And after reading it, I decided to give a go with installation and see compatibility for features especially with clients and other integration tools we use.

I have spinned up an ec2 instance (RHEL 7.4, doc says 7.3) with atleast 2cpu and 7gb of memory with RHEL 7.3 and the script here does the following list of things if your server can connect to internet.  I do not want to reinvent the wheel to create my own scripts etc to save sometime, so I have decided to use this script as is.

1. Install sql server package
2. Configure sqlserver using msql-conf utiliaty
3. Create login user
4. Create Database

And Script worked like charm, It took just less than 4 mins (the size of package was 175 m) to install and configure the SQL Server and SQL Server Agent. (contrary to Windows installation of dependencies , .net framework etc etc)

Log Here and excerpt of log below Full log : sqlserver_on_linux_install

Complete!
Configuring firewall to allow traffic on port 1433...
sudo: firewall-cmd: command not found
sudo: firewall-cmd: command not found
Restarting SQL Server...
Waiting for SQL Server to start...
                                                                                                                                                                                 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RC2) - 14.0.900.75 (X64)
        Jul 27 2017 08:53:49
        Copyright (C) 2017 Microsoft Corporation
        Enterprise Evaluation Edition (64-bit) on Linux (Red Hat Enterprise Linux Server 7.4 (Maipo))                                                                            

(1 rows affected)
Done!

And my first look with processes

[root@ip-******** ~]# ps -eaf | grep sql
mssql      778     1  0 11:32 ?        00:00:00 /opt/mssql/bin/sqlservr
mssql      781   778  3 11:32 ?        00:00:07 /opt/mssql/bin/sqlservr
root       984 10394  0 11:36 pts/1    00:00:00 grep --color=auto sql

Second, connecting database through sqlcmd tool, Add the /opt/msssql-tools/bin to environment

[root@ip-****** ~]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
[root@ip-****** ~]# source .bash_profile
[root@ip-****** ~]# sqlcmd
Microsoft (R) SQL Server Command Line Tool
Version 13.1.0007.0 Linux
Copyright (c) 2012 Microsoft. All rights reserved.

usage: sqlcmd            [-U login id]          [-P password]
  [-S server or Dsn if -D is provided]
  [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-K application intent]
  [-M multisubnet failover]
  [-b On error batch abort]
  [-D Dsn flag, indicate -S is Dsn]
  [-X[1] disable commands, startup script, environment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]

Next , stop and start and status of  service.

[root@ip-***** ~]# sudo systemctl restart mssql-server
[root@ip-***** ~]# sudo 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 2017-08-27 11:53:23 UTC; 10s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 1502 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ├─1502 /opt/mssql/bin/sqlservr
           └─1504 /opt/mssql/bin/sqlservr

Next, I forgot my sa login password, hence reset needed, run a script mssql-conf with set-sa-password it will ask the password.

[root@ip-***** ~]# /opt/mssql/bin/mssql-conf set-sa-password
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

This is an evaluation version.  There are [149] days left in the evaluation period.
The system administrator password has been changed.
Please run 'sudo systemctl start mssql-server' to start SQL Server.

Finally, log into database using sqlcmd

[root@ip-****** ~]# sqlcmd -S localhost -U sa -P ***** -d master -Q "select name, database_id from sys.databases"
name                                                                                                                             database_id
-------------------------------------------------------------------------------------------------------------------------------- -----------
master                                                                                                                                     1
tempdb                                                                                                                                     2
model                                                                                                                                      3
msdb                                                                                                                                       4
geekdb                                                                                                                                     5

(5 rows affected)

And also logged through SSMS as well.

You can set some configuration using mssql-conf tool. The following configuration can be done

My Final go to install SSIS package,

[root@********* ~]# curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   220  100   220    0     0    100      0  0:00:02  0:00:02 --:--:--   100
[root@ip-******** ~]# sudo yum install -y mssql-server-is
Loaded plugins: amazon-id, rhui-lb, search-disabled-repos
packages-microsoft-com-mssql-server                                                                                                                       | 2.9 kB  00:00:00
Resolving Dependencies


Installed:
  mssql-server-is.x86_64 0:14.0.900.75-1

Complete!

Run setup,

[root@******* ~]# sudo /opt/ssis/bin/ssis-conf setup
The license terms for this product can be downloaded from:
https://go.microsoft.com/fwlink/?LinkId=852741&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:yes

Choose an edition of SSIS:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID)
  7) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-7): 1
Only user in 'ssis' group can run 'dtexec' on Linux. Do you want to add current user into 'ssis' group? [Yes/No]:yes
Please logout to reload the group information.
SSIS telemetry service is now running.
Setup has completed successfully.

SSIS Telemetry services running

[root@****** ~]# ps -eaf | grep tele
root      2958     1  1 12:26 ?        00:00:00 /opt/ssis/bin/ssis-telemetry
root      2977  2958 20 12:26 ?        00:00:06 /opt/ssis/bin/ssis-telemetry
root      3106  3086  0 12:26 pts/1    00:00:00 grep --color=auto tele

And we can run SSIS package using , will explore that later more.

dtexec /F <package name> /DE <protection password>

Bingo, everything is transparent , the bridge between SQL Platform and Underlying OS. You will not know what you are running behind.

Enjoy..

-Thanks

Suresh