Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Install and Configure MS SQL (Beta) on Ubuntu 16.04
In this article, we will learn how to Install and Configure MS SQL on Ubuntu, Microsoft has recently announced that they are planning to release the MS SQL for Linux. Specially beta versions of Red Hat Enterprises Linux, Centos &, Ubuntu but only with 64-bit editions.
Prerequisites
- A Machine with a min of 4 GB RAM and 30 GB Hard disk space.
- A Machine installed with Ubuntu 16.04.
- A user with root privileges or root user.
MS SQL Server installation on Ubuntu
As the MS SQL is not available in the default Ubuntu repository we needed to add the MS SSQL repository details to the local machine repository.
We needed to add the GPG Key to the local machine
$ sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add – % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 983 100 983 0 0 253 0 0:00:03 0:00:03 --:--:-- 253 OK
Then we will add the Microsoft repository to the local Ubuntu machine
$ sudo curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list | sudo tee /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 95 0 --:--:-- --:--:-- --:--:-- 95 deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial main
We needed to run the update to get the repository to the local machine
$ sudo apt-get update
Once the repository is updated, we will run the below command to install the MS-SQL server
$ sudo apt-get install -y mssql-server 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 libc6-dbg libcc1-0 libcurl3 libjemalloc1 libpython-stdlib libpython2.7-minimal libpython2.7-stdlib libstdc++6 libunwind8 python python-minimal python2.7 python2.7-minimal Suggested packages: .. .. Need to get 158 MB of archives. After this operation, 734 MB of additional disk space will be used. Get:1 http://in.archive.ubuntu.com/ubuntu xenial-updates/main amd64 libpython2.7-minimal amd64 2.7.12-1ubuntu0~16.04.1 [339 kB] Get:2 https://packages.microsoft.com/ubuntu/16.04/mssql-server xenial/main amd64 mssql-server amd64 14.0.1.246-6 [144 MB] .. .. .. 14 not fully installed or removed. Need to get 0 B/144 MB of archives. After this operation, 681 MB of additional disk space will be used. Preconfiguring packages ... (Reading database ... 60605 files and directories currently installed.) 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) ... … .. Setting up libc++1:amd64 (3.7.0-1) ... Setting up libc6-dbg:amd64 (2.23-0ubuntu4) ... 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) ...
Configuring the MS SQL SA Account
Once the installation is completed, we will run sqlservr-setup script to setup password of the SA; we needed to give a strong password for the SA account (with a min of 8 characters, lower and upper case letters with numbers from 1 to 10 or any non-alpha numeric characters)
# 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: Please confirm the password for the system administrator (SA) account: The passwords specified do not match. Please try again. Please enter a password for the system administrator (SA) account: Please confirm the password for the system administrator (SA) account: 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
Once the configuration is done, we will restart the MS SQL services and will see the status of the services with the below command
Restart the services
# systemctl start mssql-server
See the status of the services
# 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 Tue 2016-12-06 23:13:01 IST; 52s ago Main PID: 6173 (sqlservr) CGroup: /system.slice/mssql-server.service ├─6173 /opt/mssql/bin/sqlservr └─6190 /opt/mssql/bin/sqlservr Dec 06 23:13:28 ubuntu16 sqlservr[6173]: [45B blob data] .. .. Dec 06 23:13:28 ubuntu16 sqlservr[6173]: [124B blob data] Dec 06 23:13:36 ubuntu16 systemd[1]: Started Microsoft(R) SQL Server(R) Database Engine.
Installing MS SQL Client Tools
Install and update the GPG key and we will update the repository to the local machine.
# curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add - % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 983 100 983 0 0 282 0 0:00:03 0:00:03 --:--:-- 282 OK
Once GPG key is updated we will update the local repository with the Microsoft repository.
$ sudo curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 0 0 0 0 0 0 0 0 --:--:-- 0:00:01 --:--:-- 0 100 79 100 79 0 0 67 0 0:00:01 0:00:01 --:--:-- 67 deb [arch=amd64] https://packages.microsoft.com/ubuntu/16.04/prod xenial main$
Once the repository is installed on the local machine, we can use the below command to install the MS SQL tools with the below command
# sudo apt-get install mssql-tools Reading package lists... Done Building dependency tree Reading state information... Done .. .. Selecting previously unselected package mssql-tools. (Reading database ... 66292 files and directories currently installed.) Preparing to unpack .../mssql-tools_14.0.1.246-1_amd64.deb ... Unpacking mssql-tools (14.0.1.246-1) ... Setting up mssql-tools (14.0.1.246-1) ...
Connecting to SQL Server on Linux
Once the tools installed on the machine, we will test, below is the command and general syntax to connect to the MS SQL server
Syntax:
# sqlcmd –S IP-ADDRESS –U SA –P ‘<YOUR SYSTEM PASSWORD’ # sqlcmd sqlcmd -S localhost -U SA -P Test@1234 1>CREATE DATABASE TEST1; 2>GO; 3>quit
For example, we will execute some of these query returns the name of all of the databases.
SELECT Name from sys.Databases; GO
Creating a database using the SQL Server default settings.
CREATE DATABASE inventdb; GO
In the above article we have learned how to install MS SQL Server on Ubuntu 16.04, MS SQL command line tools to connect to the server also we have learned how to change or setup the SA account password for the MS SQL and install the client tools and execute the quries.
137 Views
