First Experience of SQL On Linux

The version of SQL On Linux will be officially released soon. This article has been installed and operated in common use. It feels good. Say less nonsense, please see.

Installation: Installing SQL Services and Related Components

Install the SQL Service On Linux

First, install the operating system. This paper uses CentOS 7.2 to install. The installation process is not table. After the installation is completed, access the Internet, use network installation, use root user to log in. If not root user, sudo is needed to run. Now let's install the most important engine of SQL, the SQL service, according to the following steps:

1. Download the configuration file of SQL, which can be accessed if installed offline. https://packages.microsoft.com Download the file.

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

2. Install SQL. Although it is downloaded, it will be installed soon. This is completely different from the experience of installing under windows. Too fast to believe!!!

sudo yum install -y mssql-server

3. After installation, run mssql-conf setup to set SA's password, preferably more than 8 bits, with capitals and special characters.

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

4. Run the command after the installation, you can see how the SQL runs.

systemctl status mssql-server

5. External applications are required to connect to this service, and 1433 ports need to be opened. The following commands can be used:

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

6. Upgrade SQL after installation

sudo yum update mssql-server

SQL On Linux Tool Installation

1. Similarly, you need to use super administrator to download the corresponding configuration file.

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

2. Install mssql-tools using unixODBC development kit, check the update first.

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

3. Determine download y

4. Accept license terms


5. Add PATH path / opt/mssql-tools/bin/

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

Install the SQL agent mssql-server-agent

​

1. Installing SQL Agent

sudo yum install mssql-server-agent

2. Restart SQL Service

 sudo systemctl restart mssql-server

Install SQL Server Full-Text Search

1. Install full-text search with the name mssql-server-fts

sudo yum install -y mssql-server-fts

Use: Connect SQL Server on Linux and run Transact-SQL queries

Connecting SQL Server On Linux with SSMS

1. There is no gap between using SSMS to connect SQL Server on Linux and windows. Open SSMS, point to database engine, enter server name or IP, user name and password, connect

2. After connection


3. Use SQL statements to operate as usual

4. Create database test and file group and memory table optimization group

Create tables and insert data

use test

CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT);
go
 INSERT INTO inventory VALUES (1, 'banana', 150);
INSERT INTO inventory VALUES (2, 'orange', 154);
GO
SELECT * FROM inventory WHERE quantity > 152;
GO

Create memory tables

1. The following code creates a memory table

--Upgrade to snapshot
ALTER DATABASE CURRENT  
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;  


---Creating Optimized File Groups

ALTER DATABASE test ADD FILE (name='test_mod1', filename='/var/opt/mssql/data/testmod') TO FILEGROUP memogroup  

---Creating memory optimization tables

CREATE TABLE dbo.SalesOrder  
(  
    SalesOrderId   integer        not null  IDENTITY  
        PRIMARY KEY NONCLUSTERED,  
    CustomerId     integer        not null,  
    OrderDate      datetime       not null  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
        DURABILITY = SCHEMA_AND_DATA);  

2. Create a native compilation process

---Create Native Compiled Storage Procedures (Native Procedures)
------------------------------------------------------------------------------
---The key words are NATIVE_COMPILATION


CREATE PROCEDURE ncspRetrieveLatestSalesOrderIdForCustomerId  
    @_CustomerId   INT  
    WITH  
        NATIVE_COMPILATION,  
        SCHEMABINDING  
AS  
BEGIN ATOMIC  
    WITH  
        (TRANSACTION ISOLATION LEVEL = SNAPSHOT,  
        LANGUAGE = N'us_english')  

    DECLARE @SalesOrderId int, @OrderDate datetime;  

    SELECT TOP 1  
            @SalesOrderId = s.SalesOrderId,  
            @OrderDate    = s.OrderDate  
        FROM dbo.SalesOrder AS s  
        WHERE s.CustomerId = @_CustomerId  
        ORDER BY s.OrderDate DESC;  

    RETURN @SalesOrderId;  
END;  

3. Insertion of data and execution process

---Execute native processes

INSERT into dbo.SalesOrder  
        ( CustomerId, OrderDate )  
    VALUES  
        ( 42, '2013-01-13 03:35:59' ),  
        ( 42, '2015-01-15 15:35:59' );

---Calls to locally compiled stored procedures EXECUTE.     

DECLARE @LatestSalesOrderId int, @mesg nvarchar(128);  

EXECUTE @LatestSalesOrderId =  
    ncspRetrieveLatestSalesOrderIdForCustomerId 42;  

SET @mesg = CONCAT(@LatestSalesOrderId,  
    ' = Latest SalesOrderId, for CustomerId = ', 42);  
PRINT @mesg;  

4. Successfully completed, which proves that not only ordinary tables support, but also memory tables support. That is to say, SQL On Linux will provide almost identical functionality with windows Linux.

Connecting SQL with SQLCMD On Linux

Connect and use sqlcmd

1. The sqlcmd tool has been installed before. When you enter sqlcmd in the terminal window of linux, you can see the contents of sqlcmd command.

2. Connect sql

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

3. After the connection, execute the result of select * from sys.sysfiles to view the file address of master database and other information. The sqlcmd command is the same as the sqlcmd command in windows.

select * from sys.sysfiles

Creating databases and inserting data

1. Creating a database

CREATE DATABASE testdb;
go

2. Creating tables and inserting data

CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT);
go
 INSERT INTO inventory VALUES (1, 'banana', 150);
INSERT INTO inventory VALUES (2, 'orange', 154);
GO
SELECT * FROM inventory WHERE quantity > 152;
GO

Create memory tables

1. Create a memory table using the following script

use testdb

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;  

---Creating Optimized File Groups
 ALTER DATABASE testdb  ADD FILEGROUP testdb_mod CONTAINS MEMORY_OPTIMIZED_DATA
 ALTER DATABASE testdb ADD FILE (name='test_mod1', filename='/var/opt/mssql/data/testmod') TO FILEGROUP testdb_mod

----

---Creating memory optimization tables

CREATE TABLE dbo.SalesOrder  
(  
    SalesOrderId   integer        not null  IDENTITY  
        PRIMARY KEY NONCLUSTERED,  
    CustomerId     integer        not null,  
    OrderDate      datetime       not null  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
        DURABILITY = SCHEMA_AND_DATA);  

2. Specific use of sqlcmd can refer to the following description, as a lot of operation or use SSMS is more convenient.

sqlcmd utility

Post order

From simple installation to simple use, you can find that many functions will be different due to the difference between Linux operating system and windows.

My biggest feeling is that online installation is very convenient and very fast. It's totally different from the installation experience under windows.

The first problem in using only SQL On Linux is to understand the file structure, which is different from the organization of windows. File paths are also different. Linux exists in config file form in system configuration, and there are many differences. This article has not done the most important content yet.

• Database Operation and Maintenance Management
• Database Performance Detection
• Database tuning

These contents cover a wide range and cannot be completed in a relatively short time. The latter has the opportunity to experience it slowly.

End