SQL Server and SSIS : Installation on Ubuntu
https://msdnshared.blob.core.windows.net/media/2016/05/0640_NinjaAwardTinyGold.png Gold Award Winner
System Requirements
SQL Server 2017 has the following system requirements for Linux:
| Resource | Value |
| 2 GB | |
| XFS or EXT4 (other file systems, such as BTRFS, are unsupported) | |
| 6 GB | |
| 2 GHz | |
| 2 cores | |
| x64-compatible only |
If we use Network File System (NFS) remote shares in production, note to the following support requirements:
- Use NFS version 4.2 or higher. Older versions of NFS do not support required features, such as fallocate and sparse file creation, common to modern file systems.
- Locate only the /var/opt/mssql directories on the NFS mount. Other files, such as the SQL Server system binaries, are not supported.
- Ensure that NFS clients use the 'nolock' option when mounting the remote share.
Check OS
We should execute the following command to Check the OS (Operating System)
$ cat /etc/os-release
NAME="Ubuntu"
VERSION="16.04.5 LTS (Xenial Xerus)"
ID=ubuntu
ID_LIKE=debian
PRETTY_NAME="Ubuntu 16.04.5 LTS"
VERSION_ID="16.04"
HOME_URL="http://www.ubuntu.com/"
SUPPORT_URL="http://help.ubuntu.com/"
BUG_REPORT_URL="http://bugs.launchpad.net/ubuntu/"
VERSION_CODENAME=xenial
UBUNTU_CODENAME=xenial
Public Repository GPG Keys
With the following command we are able to get the GPG keys.
$ wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add
$ sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"
$ sudo apt-get update
ASC files can contain textual representations of binary information, they may be larger in size than the original pre-encoded file.
Note
wget utility which retrieves files from World Wide Web (WWW) using widely used protocols like HTTP, HTTPS and FTP
Installing mssql using bash command
Apt-get is so easy because it not only keeps track of what packages are installed, but also what other packages are available. It will even download them from the Internet for you (if properly configured).
$ sudo apt-get install -y mssql-server
Hit:1 http://us.archive.ubuntu.com/ubuntu xenial InRelease
Get:2 http://security.ubuntu.com/ubuntu xenial-security InRelease [107 kB]
Get:3 https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial InRelease [3,218 B]
Get:4 http://us.archive.ubuntu.com/ubuntu xenial-updates InRelease [109 kB]
Get:5 https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial/main md64 Packages [9,545 B]
Get:6 http://security.ubuntu.com/ubuntu xenial-security/main amd64 Packages [589kB]
Get:7 http://us.archive.ubuntu.com/ubuntu xenial-backports InRelease [107 kB]
Get:8 http://security.ubuntu.com/ubuntu xenial-security/main i386 Packages [502kB]
Get:9 http://security.ubuntu.com/ubuntu xenial-security/main Translation-en [245kB]
Get:10 http://security.ubuntu.com/ubuntu xenial-security/universe amd64 Packages [408 kB]
Get:11 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 Packages [892 kB]
Get:12 http://security.ubuntu.com/ubuntu xenial-security/universe i386 Packages[354 kB]
Get:13 http://security.ubuntu.com/ubuntu xenial-security/universe Translation-en [159 kB]
Get:14 http://security.ubuntu.com/ubuntu xenial-security/multiverse amd64 Packag es [3,724 B]
Get:15 http://security.ubuntu.com/ubuntu xenial-security/multiverse i386 Packages [3,888 B]
Get:16 http://security.ubuntu.com/ubuntu xenial-security/multiverse Translation- en [1,844 B]
Get:17 http://us.archive.ubuntu.com/ubuntu xenial-updates/main i386 Packages [79 0 kB]
Get:18 http://us.archive.ubuntu.com/ubuntu xenial-updates/main Translation-en [361 kB]
Get:19 http://us.archive.ubuntu.com/ubuntu xenial-updates/universe amd64 Package s [715 kB]
Get:20 http://us.archive.ubuntu.com/ubuntu xenial-updates/universe i386 Packages [655 kB]
Get:21 http://us.archive.ubuntu.com/ubuntu xenial-updates/universe Translation-e n [292 kB]
Get:22 http://us.archive.ubuntu.com/ubuntu xenial-updates/multiverse amd64 Packages [16.6 kB]
Get:23 http://us.archive.ubuntu.com/ubuntu xenial-updates/multiverse i386 Packag es [15.7 kB]
Get:24 http://us.archive.ubuntu.com/ubuntu xenial-updates/multiverse Translation-en [8,440 B]
Get:25 http://us.archive.ubuntu.com/ubuntu xenial-backports/main amd64 Packages[7,288 B]
Get:26 http://us.archive.ubuntu.com/ubuntu xenial-backports/main i386 Packages [ 7,292 B]
Get:27 http://us.archive.ubuntu.com/ubuntu xenial-backports/main Translation-en [4,456 B]
Get:28 http://us.archive.ubuntu.com/ubuntu xenial-backports/universe amd64ges [7,804 B]
Get:29 http://us.archive.ubuntu.com/ubuntu xenial-backports/universe i386 Packages [7,488 B]
Get:30 http://us.archive.ubuntu.com/ubuntu xenial-backports/universe Ten [4,184 B]
Fetched 6,388 kB in 9s (672 kB/s)
Reading package lists... Done
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
gcc-5-base gdb gdbserver libbabeltrace-ctf1 libbabeltrace1 libc++1 libc6-dbg
libcc1-0 libjemalloc1 libpython-stdlib libpython2.7-minimal
libpython2.7-stdlib libsasl2-modules libsasl2-modules-gssapi-mit
libsss-nss-idmap0 libstdc++6 libunwind8 python python-minimal python2.7
python2.7-minimal
Suggested packages:
gdb-doc clang libsasl2-modules-otp libsasl2-modules-ldap
libsasl2-modules-sql python-doc python-tk python2.7-doc binutils
binfmt-support
The following NEW packages will be installed:
gdb gdbserver libbabeltrace-ctf1 libbabeltrace1 libc++1 libc6-dbg libcc1-0
libjemalloc1 libpython-stdlib libpython2.7-minimal libpython2.7-stdlib
libsasl2-modules-gssapi-mit libsss-nss-idmap0 libunwind8 mssql-server python
python-minimal python2.7 python2.7-minimal
The following packages will be upgraded:
gcc-5-base libsasl2-modules libstdc++6
3 upgraded, 19 newly installed, 0 to remove and 88 not upgraded.
Need to get 188 MB of archives.
After this operation, 983 MB of additional disk space will be used.
Get:1 https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial/main amd64 mssql-server amd6414.0.3048.4-1 [176 MB]
Get:2 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libpython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.4 [339 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 python2.7-minimal amd64 2.7.12-1ubuntu0~16.04.4 [1,261 kB]
Get:4 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 python-minimal amd64 2.7.12-1~16.04[28.1 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libpython2.7-stdlib amd64 2.7.12-1ubuntu0~16.04.4 [1,880 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 python2.7 am d64 2.7.12-1ubuntu0~16.04.4 [224 kB]
Get:7 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libpython-st dlib amd64 2.7.12-1~16.04[7,768 B]
Get:8 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 python amd64 2.7.12-1~16.04 [137 kB]
Get:9 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 gcc-5-base a md64 5.4.0-6ubuntu1~16.04.11 [17.3 kB]
Get:10 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libstdc++6 amd64 5.4.0-6ubuntu1~16.04.11 [393 kB]
Get:11 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libsasl2-modules amd64 2.1.26.dfsg1-14ubuntu0.1 [47.5 kB]
Get:12 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 libbabeltrace1 amd64 1.3.2-1 [34.7 kB]
Get:13 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 libbabeltrace-ctf1 amd64 1.3.2-1 [88.3 kB]
Get:14 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 gdb amd64 7.11.1-0ubuntu1~16.5 [2,526 kB]
Get:15 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 gdbserver amd64 7.11.1-0ubuntu1~16.5 [172 kB]
Get:16 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libcc1-0 amd64 5.4.0-6ubuntu1~16.04.11 [38.8 kB]
Get:17 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libsasl2-modules-gssapi-mit amd642.1.26.dfsg1-14ubuntu0.1 [34.3 kB]
Get:18 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 libunwind8 amd64 1.1-4.1 [46.5 kB]
Get:19 http://us.archive.ubuntu.com/ubuntu xenial-updates/universe amd64 libc++1amd64 3.7.0-1ubuntu0.1[225 kB]
Get:20 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libc6-dbg amd64 2.23-0ubuntu10 [3,683kB]
Get:21 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libjemalloc1 amd64 3.6.0-9ubuntu1 [78.9kB]
Get:22 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libsss-nss-idmap0 amd64 1.131ubuntu1.12 [12.2 kB]
Fetched 188 MB in 19s (9,599 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libpython2.7-minimal:amd64.
(Reading database ... 59897 files and directories currently installed.)
Preparing to unpack .../libpython2.7-minimal_2.7.12-1ubuntu0~16.04.4_amd64.deb .
Unpacking libpython2.7-minimal:amd64 (2.7.12-1ubuntu0~16.04.4) ...
Selecting previously unselected package python2.7-minimal.
Preparing to unpack .../python2.7-minimal_2.7.12-1ubuntu0~16.04.4_amd64.deb ...
Unpacking python2.7-minimal (2.7.12-1ubuntu0~16.04.4) ...
Selecting previously unselected package python-minimal.
Preparing to unpack .../python-minimal_2.7.12-1~16.04_amd64.deb ...
Unpacking python-minimal (2.7.12-1~16.04) ...
Selecting previously unselected package libpython2.7-stdlib:amd64.
Preparing to unpack .../libpython2.7-stdlib_2.7.12-1ubuntu0~16.04.4_amd64.deb ..
Unpacking libpython2.7-stdlib:amd64 (2.7.12-1ubuntu0~16.04.4) ...
Selecting previously unselected package python2.7.
Preparing to unpack .../python2.7_2.7.12-1ubuntu0~16.04.4_amd64.deb ...
Unpacking python2.7 (2.7.12-1ubuntu0~16.04.4) ...
Selecting previously unselected package libpython-stdlib:amd64.
Preparing to unpack .../libpython-stdlib_2.7.12-1~16.04_amd64.deb ...
Unpacking libpython-stdlib:amd64 (2.7.12-1~16.04) ...
Processing triggers for man-db (2.7.5-1) ...
Processing triggers for mime-support (3.59ubuntu1) ...
Setting up libpython2.7-minimal:amd64 (2.7.12-1ubuntu0~16.04.4) ...
Setting up python2.7-minimal (2.7.12-1ubuntu0~16.04.4) ...
Linking and byte-compiling packages for runtime python2.7...
Setting up python-minimal (2.7.12-1~16.04) ...
Selecting previously unselected package python.
(Reading database ... 60643 files and directories currently installed.)
Preparing to unpack .../python_2.7.12-1~16.04_amd64.deb ...
Unpacking python (2.7.12-1~16.04) ...
Preparing to unpack .../gcc-5-base_5.4.0-6ubuntu1~16.04.11_amd64.deb ...
Unpacking gcc-5-base:amd64 (5.4.0-6ubuntu1~16.04.11) over (5.4.0-6ubuntu1~16.04.10) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up gcc-5-base:amd64 (5.4.0-6ubuntu1~16.04.11) ...
(Reading database ... 60698 files and directories currently installed.)
Preparing to unpack .../libstdc++6_5.4.0-6ubuntu1~16.04.11_amd64.deb ...
Unpacking libstdc++6:amd64 (5.4.0-6ubuntu1~16.04.11) over (5.4.0-6ubuntu1~16.04.10) ...
Processing triggers for libc-bin (2.23-0ubuntu10) ...
Setting up libstdc++6:amd64 (5.4.0-6ubuntu1~16.04.11) ...
Processing triggers for libc-bin (2.23-0ubuntu10) ...
(Reading database ... 60698 files and directories currently installed.)
Preparing to unpack .../libsasl2-modules_2.1.26.dfsg1-14ubuntu0.1_amd64.deb ...
Unpacking libsasl2-modules:amd64 (2.1.26.dfsg1-14ubuntu0.1) over (2.1.26.dfsg1-14build1) ...
Selecting previously unselected package libbabeltrace1:amd64.
Preparing to unpack .../libbabeltrace1_1.3.2-1_amd64.deb ...
Unpacking libbabeltrace1:amd64 (1.3.2-1) ...
Selecting previously unselected package libbabeltrace-ctf1:amd64.
Preparing to unpack .../libbabeltrace-ctf1_1.3.2-1_amd64.deb ...
Unpacking libbabeltrace-ctf1:amd64 (1.3.2-1) ...
Selecting previously unselected package gdb.
Preparing to unpack .../gdb_7.11.1-0ubuntu1~16.5_amd64.deb ...
Unpacking gdb (7.11.1-0ubuntu1~16.5) ...
Selecting previously unselected package gdbserver.
Preparing to unpack .../gdbserver_7.11.1-0ubuntu1~16.5_amd64.deb ...
Unpacking gdbserver (7.11.1-0ubuntu1~16.5) ...
Selecting previously unselected package libcc1-0:amd64.
Preparing to unpack .../libcc1-0_5.4.0-6ubuntu1~16.04.11_amd64.deb ...
Unpacking libcc1-0:amd64 (5.4.0-6ubuntu1~16.04.11) ...
Selecting previously unselected package libsasl2-modules-gssapi-mit:amd64.
Preparing to unpack .../libsasl2-modules-gssapi-mit_2.1.26.dfsg1-14ubuntu0.1_amd 64.deb ...
Unpacking libsasl2-modules-gssapi-mit:amd64 (2.1.26.dfsg1-14ubuntu0.1) ...
Selecting previously unselected package libunwind8.
Preparing to unpack .../libunwind8_1.1-4.1_amd64.deb ...
Unpacking libunwind8 (1.1-4.1) ...
Selecting previously unselected package libc++1:amd64.
Preparing to unpack .../libc++1_3.7.0-1ubuntu0.1_amd64.deb ...
Unpacking libc++1:amd64 (3.7.0-1ubuntu0.1) ...
Selecting previously unselected package libc6-dbg:amd64.
Preparing to unpack .../libc6-dbg_2.23-0ubuntu10_amd64.deb ...
Unpacking libc6-dbg:amd64 (2.23-0ubuntu10) ...
Selecting previously unselected package libjemalloc1.
Preparing to unpack .../libjemalloc1_3.6.0-9ubuntu1_amd64.deb ...
Unpacking libjemalloc1 (3.6.0-9ubuntu1) ...
Selecting previously unselected package libsss-nss-idmap0.
Preparing to unpack .../libsss-nss-idmap0_1.13.4-1ubuntu1.12_amd64.deb ...
Unpacking libsss-nss-idmap0 (1.13.4-1ubuntu1.12) ...
Selecting previously unselected package mssql-server.
Preparing to unpack .../mssql-server_14.0.3048.4-1_amd64.deb ...
Unpacking mssql-server (14.0.3048.4-1) ...
Processing triggers for libc-bin (2.23-0ubuntu10) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libpython2.7-stdlib:amd64 (2.7.12-1ubuntu0~16.04.4) ...
Setting up python2.7 (2.7.12-1ubuntu0~16.04.4) ...
Setting up libpython-stdlib:amd64 (2.7.12-1~16.04) ...
Setting up python (2.7.12-1~16.04) ...
Setting up libsasl2-modules:amd64 (2.1.26.dfsg1-14ubuntu0.1) ...
Setting up libbabeltrace1:amd64 (1.3.2-1) ...
Setting up libbabeltrace-ctf1:amd64 (1.3.2-1) ...
Setting up gdb (7.11.1-0ubuntu1~16.5) ...
Setting up gdbserver (7.11.1-0ubuntu1~16.5) ...
Setting up libcc1-0:amd64 (5.4.0-6ubuntu1~16.04.11) ...
Setting up libsasl2-modules-gssapi-mit:amd64 (2.1.26.dfsg1-14ubuntu0.1) ...
Setting up libunwind8 (1.1-4.1) ...
Setting up libc++1:amd64 (3.7.0-1ubuntu0.1) ...
Setting up libc6-dbg:amd64 (2.23-0ubuntu10) ...
Setting up libjemalloc1 (3.6.0-9ubuntu1) ...
Setting up libsss-nss-idmap0 (1.13.4-1ubuntu1.12) ...
Setting up mssql-server (14.0.3048.4-1) ...
+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+
SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
Processing triggers for libc-bin (2.23-0ubuntu10) ...
Note
Standard edition require **983 MB **
Check mssql service status
#systemctl restart mssql-server.service
Change mssql sa password and configuration
Choose an edition of SQL Server:
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) Enterprise Core (PAID)
8) I bought a license through a retail sales channel and have a proenter.
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-8):5 The license terms for this product can be found in/usr/share/doc/mssql-server or downloaded from:https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409The privacy statement can be viewed at:https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409Do you accept the license terms? [Yes/No]:yesEnter the SQL Server system administrator password:******Confirm the SQL Server system administrator password:******Configuring SQL Server...The licensing PID was successfully processed. The new edition is [Standard Edition].ForceFlush is enabled for this instance.ForceFlush feature is enabled for log durability.Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.se rvice to /lib/systemd/system/mssql-server.service.Setup has completed successfully. SQL Server is now starting.
update repository
curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
Note
CURL command can be used to download multiple files as the same time
Installation command with the unixODBC developer package.
$ sudo apt-get update
$ sudo apt-get install mssql-tools unixodbc-dev
Mssql-tools run the following commands
$ sudo apt-get update
$ sudo apt-get install mssql-tools
Creating symlink
We can access mssql tools from any location like link.
$ sudo ls /opt/mssql-tools/bin/sqlcmd*
$ sudo ln -sfn /opt/mssql-tools/bin/sqlcmd /usr/bin/sqlcmd
Login mssql using sqlcmd command
$ sqlcmd -S 192.168.1.35 -U sa -P 'sqldba@1'
1> select @@version
2> go
Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64)
Nov 30 2018 12:57:58
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)
How to uninstall SQL Server
$ sudo apt-get remove mssql-server
Removing the package does not delete the generated database files. If you want to delete the database files, use the following command:
$ sudo rm -rf /var/opt/mssql/
Note: Make sure that you have kept backup of user database which are useful.
Install SSIS
Make sure copy microsoft.asc then install configuration IS and set configuration path step by step.
1. Import the public repository GPG keys.
$ curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
% Total % Received % Xferd Average Speed Time Time Time Current load Total Spent Left Speed
100 983 100 983 0 0 1003 0 --:--:-- --:--:-- --:--:-- 1003
OK
2. Register the Microsoft SQL Server Ubuntu repository.
$ sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent
100 91 100 91 0 0 96 0 --:--:-- --:--:-- --:--:-- 96
3. Run the following commands to install SQL Server Integration Services.
$ sudo apt-get update
$ sudo apt-get install -y mssql-server-is
Hit:1 http://us.archive.ubuntu.com/ubuntu xenial InRelease
Hit:2 https://packages.microsoft.com/ubuntu/16.04/mssql-server-2017 xenial InRelease
Hit:3 https://packages.microsoft.com/ubuntu/16.04/prod xenial InRelease
Get:4 http://security.ubuntu.com/ubuntu xenial-security InRelease [107 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu xenial-updates InRelease [109 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu xenial-backports InRelease [107 kB]
Fetched 323 kB in 1s (176 kB/s)
Reading package lists... Done
4. Installing Integration Services
$ sudo /opt/ssis/bin/ssis-conf setup
Reading package lists... Done
5. Configuration is done, set the path.
$ export PATH=/opt/ssis/bin:$PATH