CentOS7 install and use SQL Server

SQL Server Installation Guide on Linux##

Reference: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-setup

The author is based on CentOS7 for installation

Download the Microsoft SQL Server Red Hat repository configuration file

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

Run the following command to install SQL Server

sudo yum update -y
sudo yum install -y mssql-server

After the installation is complete, execute the following command, set the SA password according to the prompt, and select the corresponding version

sudo /opt/mssql/bin/mssql-conf setup

![ Picture description][1]

Ensure that the SA account password slightly meets the default requirements (at least 8 characters, including uppercase and lowercase letters, decimal numbers, and/or non-alphanumeric symbols)

After the configuration is complete, verify that the service is running

systemctl status mssql-server

![ Picture description][2]

Open the SQL Server port on the firewall, the default is TCP 1433

sudo firewall-cmd --zone=public--add-port=1433/tcp --permanent
sudo firewall-cmd --reload
# firewall-cmd is not enabled, no need to do this setting

So far, SQL Server has been running normally on CentOS7

Install SQL Server command line tools###

To create a database, you need to use a tool that can run Transact-SQL statements on SQL Server to connect. The following steps install SQL Server command line tools: sqlcmd and bcp

Download the Microsoft Red Hat repository configuration file

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

If the server has previously installed mssql, you need to remove any older unixODBC packages

sudo yum update
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel

Run the following command to install mssql tool and unixODBC developer package

sudo yum update
sudo yum install -y mssql-tools unixODBC-devel

Add /opt/mssql-tools/bin/ path to environment variable

echo 'export PATH="$PATH:/opt/mssql-tools/bin"'>>~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"'>>~/.bashrc
source ~/.bashrc

Local connection configuration###

The following steps use sqlcmd to connect locally to a new SQL Server instance

Run sqlcmd with the parameters of SQL Server name (-S), user name (-U) and password (-P). In this tutorial, the user makes a local connection, so the server name is localhost. The user name is SA, and the password is the password provided for the SA account during the installation process.

sqlcmd -S localhost -U SA -P '<YourPassword>'

Password input can be interactive

If the connection is successful, the following figure is displayed: the sqlcmd command prompt should be displayed: 1>

![ Picture description][3]

Use SQL Server to create and query data###

Create a new database, create a database named AniuDB

In the sqlcmd command prompt, paste the following Transact-SQL command to create a test database:

CREATE DATABASE AniuDB

In the next line, write a query to return the names of all databases on the server

SELECT Name from sys.Databases

The first two commands were not executed immediately. You must type GO in a new line to execute the previous command:

GO

![ Picture description][4]

Next create a new table itdevops, and then insert two new rows

In the sqlcmd command prompt, switch the context to the new AniuDB database:

USE AniuDB

Create a new table called itdevops:

CREATE TABLE itdevops(id INT, name NVARCHAR(50), quantity INT)

Insert data into the table

INSERT INTO itdevops VALUES(1,'banana',150); INSERT INTO itdevops VALUES(2,'orange',100);

Execute GO

GO

Select data, run query to return data from itdevops

Enter a query through the sqlcmd command prompt to return the rows in the itdevops table with a number greater than 100

SELECT * FROM itdevops WHERE quantity >100;
# Execute GO

Exit the sqlcmd command prompt

quit

Connect from Windows###

The SQL Server tool on Windows connects to the SQL Server instance on Linux in the same way as connecting to any remote SQL Server instance

Other tools that run on Windows and connect to SQL Server on Linux:

SSMS: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-develop-use-ssms
WP: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-manage-powershell
SSDT: https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-develop-use-ssdt