Thursday, November 17, 2016

Install SQL Server (vNext) on Ubuntu



donghua@ubuntu:~$ lsb_release -d
Description:    Ubuntu 16.04.1 LTS

# Registery GPG key and SQL Server Ubuntu repository:

root@ubuntu:~# curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   983  100   983    0     0   1952      0 --:--:-- --:--:-- --:--:--  1954
OK
root@ubuntu:~# curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list > /etc/apt/sources.list.d/mssql-server.list
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    87  100    87    0     0    545      0 --:--:-- --:--:-- --:--:--   547
root@ubuntu:~# curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/msprod.list
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100    79  100    79    0     0    109      0 --:--:-- --:--:-- --:--:--   109

donghua@ubuntu:~$ sudo apt-get update

# Install SQL Server
donghua@ubuntu:~$ sudo apt-get install -y mssql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  linux-headers-4.4.0-31 linux-headers-4.4.0-31-generic linux-image-4.4.0-31-generic linux-image-extra-4.4.0-31-generic
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libc++1 libjemalloc1
Suggested packages:
  clang
The following NEW packages will be installed:
  libc++1 libjemalloc1 mssql-server
0 upgraded, 3 newly installed, 0 to remove and 55 not upgraded.
Need to get 144 MB of archives.
After this operation, 682 MB of additional disk space will be used.
Get:1 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd64 mssql-server amd64 14.0.1.246-6 [144 MB]
Get:2 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libc++1 amd64 3.7.0-1 [226 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libjemalloc1 amd64 3.6.0-9ubuntu1 [78.9 kB]
Fetched 144 MB in 32s (4,453 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libc++1:amd64.
(Reading database ... 242891 files and directories currently installed.)
Preparing to unpack .../libc++1_3.7.0-1_amd64.deb ...
Unpacking libc++1:amd64 (3.7.0-1) ...
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 mssql-server.
Preparing to unpack .../mssql-server_14.0.1.246-6_amd64.deb ...
Unpacking mssql-server (14.0.1.246-6) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libc++1:amd64 (3.7.0-1) ...
Setting up libjemalloc1 (3.6.0-9ubuntu1) ...
Setting up mssql-server (14.0.1.246-6) ...

+-------------------------------------------------------------------+
| Please run /opt/mssql/bin/sqlservr-setup to complete the setup of |
|                  Microsoft(R) SQL Server(R).                      |
+-------------------------------------------------------------------+

Processing triggers for libc-bin (2.23-0ubuntu3) ...


# Configure SQL Server
donghua@ubuntu:~$ sudo /opt/mssql/bin/sqlservr-setup
Microsoft(R) SQL Server(R) Setup

You can abort setup at anytime by pressing Ctrl-C. Start this program
with the --help option for information about running it in unattended
mode.

The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746388 and found
in /usr/share/doc/mssql-server/LICENSE.TXT.

Do you accept the license terms? If so, please type "YES": YES

Please enter a password for the system administrator (SA) account:myPassowrd
Please confirm the password for the system administrator (SA) account: myPassowrd

Setting system administrator (SA) account password...

Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /lib/systemd/system/mssql-server-telemetry.service.

Setup completed successfully.

# Check the SQL Server status

donghua@ubuntu:~$ systemctl status mssql-server
● mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2016-11-17 23:33:57 SGT; 18s ago
 Main PID: 26336 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ├─26336 /opt/mssql/bin/sqlservr
           └─26350 /opt/mssql/bin/sqlservr

Nov 17 23:34:00 ubuntu sqlservr[26336]: [62B blob data]
Nov 17 23:34:00 ubuntu sqlservr[26336]: [61B blob data]
Nov 17 23:34:00 ubuntu sqlservr[26336]: [122B blob data]
Nov 17 23:34:00 ubuntu sqlservr[26336]: [145B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [66B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [75B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [96B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [100B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [71B blob data]
Nov 17 23:34:01 ubuntu sqlservr[26336]: [124B blob data]

donghua@ubuntu:~$ ps -ef|grep mssql
mssql     26336      1  0 23:33 ?        00:00:00 /opt/mssql/bin/sqlservr
mssql     26341      1  0 23:33 ?        00:00:00 /opt/mssql/bin/sqlservr-telemetry /var/opt/mssql/.system
mssql     26350  26336  6 23:33 ?        00:00:04 /opt/mssql/bin/sqlservr


# Install SQL Server Client Tools

donghua@ubuntu:~$ sudo apt-get install mssql-tools
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
  linux-headers-4.4.0-31 linux-headers-4.4.0-31-generic linux-image-4.4.0-31-generic linux-image-extra-4.4.0-31-generic
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
  libgmp-dev libgmpxx4ldbl libgnutls-dev libgnutls28-dev libgnutlsxx28 libgss3 libidn11-dev libodbc1-utf16 libp11-kit-dev libshishi-dev libshishi0 libtasn1-6-dev libtasn1-doc msodbcsql nettle-dev
  odbcinst-utf16 odbcinst1debian2-utf16 shishi-common unixodbc-utf16 zlib1g-dev
Suggested packages:
  gmp-doc libgmp10-doc libmpfr-dev gnutls-doc gnutls-bin guile-gnutls shishi libmyodbc odbc-postgresql tdsodbc
The following NEW packages will be installed:
  libgmp-dev libgmpxx4ldbl libgnutls-dev libgnutls28-dev libgnutlsxx28 libgss3 libidn11-dev libodbc1-utf16 libp11-kit-dev libshishi-dev libshishi0 libtasn1-6-dev libtasn1-doc msodbcsql mssql-tools
  nettle-dev odbcinst-utf16 odbcinst1debian2-utf16 shishi-common unixodbc-utf16 zlib1g-dev
0 upgraded, 21 newly installed, 0 to remove and 55 not upgraded.
Need to get 8,431 kB of archives.
After this operation, 12.7 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 libodbc1-utf16 amd64 2.3.1-2 [159 kB]
Get:2 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 odbcinst1debian2-utf16 amd64 2.3.1-2 [40.8 kB]
Get:3 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 odbcinst-utf16 amd64 2.3.1-2 [20.7 kB]
Get:4 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 unixodbc-utf16 amd64 2.3.1-2 [31.3 kB]
Get:5 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 msodbcsql amd64 13.0.1.0-1 [3,510 kB]
Get:6 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 libgmpxx4ldbl amd64 2:6.1.0+dfsg-2 [8,948 B]
Get:7 https://packages.microsoft.com/ubuntu/16.04/prod xenial/main amd64 mssql-tools amd64 14.0.1.246-1 [1,263 kB]
Get:8 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 libgmp-dev amd64 2:6.1.0+dfsg-2 [314 kB]
Get:9 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libgnutlsxx28 amd64 3.4.10-4ubuntu1.1 [13.7 kB]
Get:10 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 nettle-dev amd64 3.2-1 [937 kB]
Get:11 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 zlib1g-dev amd64 1:1.2.8.dfsg-2ubuntu4 [168 kB]
Get:12 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libtasn1-6-dev amd64 4.7-3ubuntu0.16.04.1 [85.8 kB]
Get:13 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libp11-kit-dev amd64 0.23.2-5~ubuntu16.04.1 [56.5 kB]
Get:14 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libidn11-dev amd64 1.32-3ubuntu1.1 [519 kB]
Get:15 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libgnutls-dev amd64 3.4.10-4ubuntu1.1 [553 kB]
Get:16 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libgnutls28-dev amd64 3.4.10-4ubuntu1.1 [8,164 B]
Get:17 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 shishi-common all 1.0.2-6build1 [60.6 kB]
Get:18 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libshishi0 amd64 1.0.2-6build1 [159 kB]
Get:19 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libshishi-dev amd64 1.0.2-6build1 [157 kB]
Get:20 http://us.archive.ubuntu.com/ubuntu xenial/universe amd64 libgss3 amd64 1.0.3-2 [65.2 kB]
Get:21 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libtasn1-doc all 4.7-3ubuntu0.16.04.1 [299 kB]
Fetched 8,431 kB in 29s (287 kB/s)
Preconfiguring packages ...
Selecting previously unselected package libgmpxx4ldbl:amd64.
(Reading database ... 242940 files and directories currently installed.)
Preparing to unpack .../libgmpxx4ldbl_2%3a6.1.0+dfsg-2_amd64.deb ...
Unpacking libgmpxx4ldbl:amd64 (2:6.1.0+dfsg-2) ...
Selecting previously unselected package libgmp-dev:amd64.
Preparing to unpack .../libgmp-dev_2%3a6.1.0+dfsg-2_amd64.deb ...
Unpacking libgmp-dev:amd64 (2:6.1.0+dfsg-2) ...
Selecting previously unselected package libgnutlsxx28:amd64.
Preparing to unpack .../libgnutlsxx28_3.4.10-4ubuntu1.1_amd64.deb ...
Unpacking libgnutlsxx28:amd64 (3.4.10-4ubuntu1.1) ...
Selecting previously unselected package nettle-dev.
Preparing to unpack .../nettle-dev_3.2-1_amd64.deb ...
Unpacking nettle-dev (3.2-1) ...
Selecting previously unselected package zlib1g-dev:amd64.
Preparing to unpack .../zlib1g-dev_1%3a1.2.8.dfsg-2ubuntu4_amd64.deb ...
Unpacking zlib1g-dev:amd64 (1:1.2.8.dfsg-2ubuntu4) ...
Selecting previously unselected package libtasn1-6-dev:amd64.
Preparing to unpack .../libtasn1-6-dev_4.7-3ubuntu0.16.04.1_amd64.deb ...
Unpacking libtasn1-6-dev:amd64 (4.7-3ubuntu0.16.04.1) ...
Selecting previously unselected package libp11-kit-dev:amd64.
Preparing to unpack .../libp11-kit-dev_0.23.2-5~ubuntu16.04.1_amd64.deb ...
Unpacking libp11-kit-dev:amd64 (0.23.2-5~ubuntu16.04.1) ...
Selecting previously unselected package libidn11-dev.
Preparing to unpack .../libidn11-dev_1.32-3ubuntu1.1_amd64.deb ...
Unpacking libidn11-dev (1.32-3ubuntu1.1) ...
Selecting previously unselected package libgnutls-dev:amd64.
Preparing to unpack .../libgnutls-dev_3.4.10-4ubuntu1.1_amd64.deb ...
Unpacking libgnutls-dev:amd64 (3.4.10-4ubuntu1.1) ...
Selecting previously unselected package libgnutls28-dev:amd64.
Preparing to unpack .../libgnutls28-dev_3.4.10-4ubuntu1.1_amd64.deb ...
Unpacking libgnutls28-dev:amd64 (3.4.10-4ubuntu1.1) ...
Selecting previously unselected package libodbc1-utf16:amd64.
Preparing to unpack .../libodbc1-utf16_2.3.1-2_amd64.deb ...
Unpacking libodbc1-utf16:amd64 (2.3.1-2) ...
Selecting previously unselected package odbcinst1debian2-utf16:amd64.
Preparing to unpack .../odbcinst1debian2-utf16_2.3.1-2_amd64.deb ...
Unpacking odbcinst1debian2-utf16:amd64 (2.3.1-2) ...
Selecting previously unselected package odbcinst-utf16.
Preparing to unpack .../odbcinst-utf16_2.3.1-2_amd64.deb ...
Unpacking odbcinst-utf16 (2.3.1-2) ...
Selecting previously unselected package unixodbc-utf16.
Preparing to unpack .../unixodbc-utf16_2.3.1-2_amd64.deb ...
Unpacking unixodbc-utf16 (2.3.1-2) ...
Selecting previously unselected package shishi-common.
Preparing to unpack .../shishi-common_1.0.2-6build1_all.deb ...
Unpacking shishi-common (1.0.2-6build1) ...
Selecting previously unselected package libshishi0.
Preparing to unpack .../libshishi0_1.0.2-6build1_amd64.deb ...
Unpacking libshishi0 (1.0.2-6build1) ...
Selecting previously unselected package libshishi-dev.
Preparing to unpack .../libshishi-dev_1.0.2-6build1_amd64.deb ...
Unpacking libshishi-dev (1.0.2-6build1) ...
Selecting previously unselected package libgss3.
Preparing to unpack .../libgss3_1.0.3-2_amd64.deb ...
Unpacking libgss3 (1.0.3-2) ...
Selecting previously unselected package libtasn1-doc.
Preparing to unpack .../libtasn1-doc_4.7-3ubuntu0.16.04.1_all.deb ...
Unpacking libtasn1-doc (4.7-3ubuntu0.16.04.1) ...
Selecting previously unselected package msodbcsql.
Preparing to unpack .../msodbcsql_13.0.1.0-1_amd64.deb ...
Unpacking msodbcsql (13.0.1.0-1) ...
Selecting previously unselected package mssql-tools.
Preparing to unpack .../mssql-tools_14.0.1.246-1_amd64.deb ...
Unpacking mssql-tools (14.0.1.246-1) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...
Processing triggers for doc-base (0.10.7) ...
Processing 3 added doc-base files...
Processing triggers for install-info (6.1.0.dfsg.1-5) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libgmpxx4ldbl:amd64 (2:6.1.0+dfsg-2) ...
Setting up libgmp-dev:amd64 (2:6.1.0+dfsg-2) ...
Setting up libgnutlsxx28:amd64 (3.4.10-4ubuntu1.1) ...
Setting up nettle-dev (3.2-1) ...
Setting up zlib1g-dev:amd64 (1:1.2.8.dfsg-2ubuntu4) ...
Setting up libtasn1-6-dev:amd64 (4.7-3ubuntu0.16.04.1) ...
Setting up libp11-kit-dev:amd64 (0.23.2-5~ubuntu16.04.1) ...
Setting up libidn11-dev (1.32-3ubuntu1.1) ...
Setting up libgnutls-dev:amd64 (3.4.10-4ubuntu1.1) ...
Setting up libgnutls28-dev:amd64 (3.4.10-4ubuntu1.1) ...
Setting up libodbc1-utf16:amd64 (2.3.1-2) ...
Setting up shishi-common (1.0.2-6build1) ...
Setting up libshishi0 (1.0.2-6build1) ...
Setting up libshishi-dev (1.0.2-6build1) ...
Setting up libgss3 (1.0.3-2) ...
Setting up libtasn1-doc (4.7-3ubuntu0.16.04.1) ...
Setting up odbcinst1debian2-utf16:amd64 (2.3.1-2) ...
Setting up odbcinst-utf16 (2.3.1-2) ...
Setting up unixodbc-utf16 (2.3.1-2) ...
Setting up msodbcsql (13.0.1.0-1) ...
Setting up mssql-tools (14.0.1.246-1) ...
Processing triggers for libc-bin (2.23-0ubuntu3) ...

donghua@ubuntu:~$ sqlcmd -S localhost -U SA -P myPassword
1>  SELECT Name,state_desc from sys.Databases
2> go
Name                state_desc
------------------- --------------
master              ONLINE
tempdb              ONLINE
model               ONLINE
msdb                ONLINE

(4 rows affected)
1> create database test
2> go
1> use TEST
2> go
Changed database context to 'test'.
1> CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT)
2> go
1> INSERT INTO inventory VALUES (1, 'banana', 150);
2> INSERT INTO inventory VALUES (2, 'orange', 154);
3> go

(1 rows affected)

(1 rows affected)
1> select * from inventory;
2> go
id          name                                               quantity
----------- -------------------------------------------------- -----------
          1 banana                                                     150
          2 orange                                                     154

(2 rows affected)
1> select @@version;
2> go
                                                                                                                                                                                                              
-----------------------------------------------------
Microsoft SQL Server vNext (CTP1) - 14.0.1.246 (X64)
        Nov  1 2016 23:24:39
        Copyright (c) Microsoft Corporation
        on Linux (Ubuntu 16.04.1 LTS)

(1 rows affected)

root@ubuntu:/opt/mssql/bin# ls -l /opt/mssql/bin /opt/mssql-tools/bin
/opt/mssql/bin:
total 1756
-rwxr-xr-x 1 root root    801 Nov 15 07:03 compress-dump.sh
-rwxr-xr-x 1 root root   5197 Nov 15 07:03 generate-core.sh
-rwxr-xr-x 1 root root     73 Nov 15 07:02 mssql-conf
-rwxr-xr-x 1 root root 590360 Nov 15 07:03 sqlpackage
-rwxr-xr-x 1 root root 591224 Nov 15 07:03 sqlservr
-rwxr-xr-x 1 root root   9615 Nov 15 07:02 sqlservr-setup
-rwxr-xr-x 1 root root 581560 Nov 15 07:03 sqlservr-telemetry

/opt/mssql-tools/bin:
total 5620
-rwxrwxr-x 1 root root 1114264 Nov  9 12:23 bcp-14.0.1.246
-rwxrwxr-x 1 root root 4635376 Nov  9 12:23 sqlcmd-14.0.1.246


root@ubuntu:/var/opt/mssql/data# ls -l /var/opt/mssql/data
total 69440
-rw-r----- 1 mssql mssql  4194304 Nov 17 23:39 master.mdf
-rw-r----- 1 mssql mssql  2097152 Nov 17 23:41 mastlog.ldf
-rw-r----- 1 mssql mssql  8388608 Nov 17 23:41 modellog.ldf
-rw-r----- 1 mssql mssql  8388608 Nov 17 23:41 model.mdf
-rw-r----- 1 mssql mssql 13959168 Nov 17 23:34 msdbdata.mdf
-rw-r----- 1 mssql mssql   524288 Nov 17 23:34 msdblog.ldf
-rw-rw---- 1 mssql mssql  8388608 Nov 17 23:34 tempdb.mdf
-rw-rw---- 1 mssql mssql  8388608 Nov 17 23:39 templog.ldf
-rw-rw---- 1 mssql mssql  8388608 Nov 17 23:42 test_log.ldf
-rw-rw---- 1 mssql mssql  8388608 Nov 17 23:41 test.mdf

root@ubuntu:/var/opt/mssql/data# ls -l /var/opt/mssql/log/
total 2508
-rw-rw---- 1 mssql mssql   14696 Nov 17 23:41 errorlog
-rw-r----- 1 mssql mssql   11780 Nov 17 23:33 errorlog.1
-rw-r----- 1 mssql mssql       0 Nov 17 23:33 errorlog.2
-rw-r----- 1 mssql mssql   77824 Nov 17 23:33 HkEngineEventFile_0_131238704318540000.xel
-rw-rw---- 1 mssql mssql   77824 Nov 17 23:33 HkEngineEventFile_0_131238704394410000.xel
-rw-rw---- 1 mssql mssql 1048576 Nov 17 23:39 log_1.trc
-rw-rw---- 1 mssql mssql   12288 Nov 17 23:41 log_2.trc
-rw-r----- 1 mssql mssql 1048576 Nov 17 23:33 log.trc
-rw-r--r-- 1 mssql mssql    6902 Nov 17 23:33 setup-20161117-233347.log
-rw-r----- 1 mssql mssql  102400 Nov 17 23:33 system_health_0_131238704326830000.xel
-rw-rw---- 1 mssql mssql  163840 Nov 17 23:47 system_health_0_131238704398150000.xel

Reference URL: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup