Install and use SQL Server on Linux

Microsoft SQL Server is a database administrator is very popular in the world, it is where the data for large enterprise software, many web sites as well as the production system .

Previously SQL Server only on Windows (Server), now also has the Linux side for many other software such as PowerShell, Visual Studio Code or the browser’s JavaScript source Edge …

In this article, I will guide you how to install and use SQL Server on the popular Linux distro.

1. Installing SQL Server on Linux

SQL Server requires minimal configuration 3250 MB Ram.

You need to use the superuser rights # sudo su to install.

1.1. Installing SQL Server on CentOS / RHEL

a. Download Microsoft SQL Server Red Hat repository

 # curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo> /etc/yum.repos.d/mssql-server.repo

b. Installing SQL Server

 # sudo yum -y install MSSQL-server

Installed:
  MSSQL-server.x86_64 0: 14.0.1.246-6
Installed Dependency:
  bzip2.x86_64 0: 1.0.6-13.el7 gdb.x86_64 0: 7.6.1-80.el7

c. Configuring SQL Server

After completing the installation packages, you need to run the configuration script.

Note : YES to accept the license and satisfactory password of 8 characters or more including uppercase letters, lowercase, numbers, special characters.

 # sudo / opt / MSSQL / bin / sqlservr-setup
Microsoft (R) SQL Server (R) Setup

You can abort the 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
/usr/share/doc/mssql-server/LICENSE.TXT print.

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

Please enter a password for the system administrator (SA) account:  password 
Please confirm the password for the system administrator (SA) account:  password 
Setting the 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 /usr/lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /usr/lib/systemd/system/mssql-server-telemetry.service.
Setup completed successfully.

Once installed, check if the SQL Server service is running

 # systemctl MSSQL-server status -l
 MSSQL-server.service - Microsoft (R) SQL Server (R) Database Engine
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor presets: disabled)
   Active:  active (running)  2016-11-17 03:43:32 UTC since Autumn; 7min ago

1.2. Installing SQL Server on Ubuntu

a. GPG keys enter public repository

 # curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

b. Registration Microsoft SQL Server Ubuntu repository

 # curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list> /etc/apt/sources.list.d/mssql-server.list

c. Installing SQL Server

 # sudo apt-get update
# Sudo apt-get install -y MSSQL-server

d. Configuring SQL Server

Configuration like instructions for CentOS

 sudo / opt / MSSQL / bin / sqlservr-setup

Once installed, check if the SQL Server service is running

 systemctl status MSSQL-server

After a successful installation, you can connect to SQL Server vNext CTP1 on Linux via MSSQL Tool, including Sqlcmd utility, Transact-SQL queries to create, query the database and import-export data for bcp bulk material.

Note:

  • In the majority of cases, requirements and process different connection between Linux distributions
  • Sqlcmd just one tool to connect to SQL server management and query the data. Besides, you can use SQL Server Management Studio and Visual Code.

2.1. MSSQL Install Tool

To install, you need to use the superuser rights # sudo su

a. For CentOS / RHEL

Download Microsoft Red Hat configuration file repository

 # curl https://packages.microsoft.com/config/rhel/7/prod.repo> /etc/yum.repos.d/msprod.repo

MSSQL Install Tool (YES to accept the license term)

 # sudo yum install MSSQL-tools
Installed:
  MSSQL-tools.x86_64 0: 14.0.1.246-1

Installed Dependency:
  libtool-ltdl.x86_64 0: 2.4.2-21.el7_2 msodbcsql.x86_64 0: 13.0.1.0-1 unixODBC-utf16.x86_64 0: 2.3.1-1

b. For Ubuntu

Enter the repository GPG keys

 # curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

Register Microsoft Ubuntu repository

 # curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list> /etc/apt/sources.list.d/msprod.list

Update system and installed MSSQL Tool

 # sudo apt-get update
# Sudo apt-get install MSSQL-tools

2.2. Connectivity and data access SQL Server

a. Connect SQL Server database

To connect to SQL Server on Linux, you need to use SQL Authentication. With the remote connection, you need to open the SQL Server port (default is 1433 via tcp port) on the firewall.

If you set up a firewall using iptables:

 # iptables -A INPUT -p tcp -m tcp -j ACCEPT --dport 1433

If you set the firewall by FirewallD:

 sudo firewall-cmd = public --zone --add-port = 1433 / tcp --permanent
sudo firewall-cmd --reload

Connect to SQL Server through sqlcmd with the parameters of the SQL Server name (-S), username (U) and password (-P)

 # sqlcmd -S localhost -U SA -P 'yourpassword'

To connect remotely, specify the hostname or IP -S parameters, eg

 # sqlcmd -S localhost -U SA -P 'yourpassword'

b. Query SQL Server database

After a successful connection SQL Server, you can run the query returns information problem or create a database. Here are some basic applications

Query database list

 SELECT Name from sys.databases;
GO

The results of

 Name
-------------------------------------------------- -------------------------------------------------- ----------------------------
master
tempdb
model
msdb
(4 rows affected)

Create and use a new database

 TestDB CREATE DATABASE;
GO

Using the newly created database TestDB

 USE TestDB;
GO

Create and insert data into one database table in the new

Create 1 sheet of 3 fields:

  1. Where the order ID number (key), data types INT
  2. School Name names, data types NVARCHAR
  3. Where the number of data types INT Quantity
 CREATE TABLE inventory (id INT, name NVARCHAR (50), quantity INT);
WOOD

Enter data, each data 1 line, the field separated by brackets and data protection by text qualifier ''

  • 1, Banana, number 150
  • 2, Orange, number 154
 INSERT INTO inventory VALUES (1, 'banana', 150);
INSERT INTO inventory VALUES (2, 'orange', 154);
WOOD

Query data sheet

Search the records with the number of> 152. Results returned orange

 SELECT * FROM inventory WHERE quantity> 152;
 WOOD
id name quantity
--------------------------------------- ----------- ----------- -----------
          2 orange 154

(1 rows affected)

Exit session Sqlcmd, type QUIT

Install and use SQL Server on Linux
5 (100%) 10 vote