Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to deploy SQL Server 2017 under Ubuntu. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

SQL Server 2017 has recently been officially released. This is the first time in SQL Server history that Windows and Linux have been released at the same time. In addition, Microsoft has released a container version that can be deployed using Docker. For SQL Server, this is a landmark step in its history, as it is a step out of the * version of Windows, marking the availability of SQL Server on the Linux platform.

The new version of SQL Server 2017 has become a cloud-based version across different operating systems, including Linux and Docker. SQL Server 2017 currently supports Linux distributions such as Red Hat Enterprise Linux (RHEL), SUSE Linux Enterprise Server and Ubuntu. SQL Server 2017 supports three major container platforms: Docker Enterprise Edition, Kubernetes and OpenShift.

What's New in SQL Server 2017

SQL Server 2017 supports the use of R and Python analysis methods for machine learning in the database, which means that there is no need to migrate data and save a lot of time.

The graph data analysis feature will enable customers to use native graphical query syntax using graphical data storage and query language extensions to discover new relationships in highly interconnected data.

Adaptive query processing can bring a more intelligent experience to the database. For example, Adaptive Memory Grants in SQL Server tracks and understands how much memory is used for a given query to adjust memory usage.

Automatic Plan Correction ensures continuous performance by finding and correcting the regression of performance.

The core functions of SQL Server 2017 are consistent on Windows and Linux, but a small number of features that depend on Windows capabilities are not available to Linux (such as clustering support and integrated Windows authentication).

This article describes how to deploy SQL Server 2017 under Ubuntu.

Install SQL Server 2017

Prerequisites for installing SQL Server 2017 on Linux

Device type device requires memory 3.25 GB and above file system XFS or EXT4 (other file systems, such as BTRFS, not supported) disk space 6 GB processor speed 2 GHz processor core 2 core processor type x64 compatible only

Install SQL Server 2017 server

Import the public repository GPG key

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

Add Microsoft SQL Server Ubuntu Warehouse

$add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server-2017.list)"

Install the SQL Server server

$apt-get update $apt-get install-y mssql-server

Set the SA password and select the version to install

$/ opt/mssql/bin/mssql-conf setup Choose an edition of SQL Server: 1) Evaluation (free, no production use rights, 180-day limit) 2) Developer (free, no production use rights) 3) Express (free) 4) Web (PAID) 5) Standard (PAID) 6) Enterprise (PAID) 7) Enterprise Core (PAID) 8) I bought a license through a retail sales channel and have a product key to enter. Details about editions can be found at https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409 Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program. By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software. Enter your edition (1-8): 1 The license terms for this product can be found in / usr/share/doc/mssql-server or downloaded from: https://go.microsoft.com/fwlink/?LinkId=855864&clcid=0x409 The privacy statement can be viewed at: https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409 Do you accept the license terms? [Yes/No]: yes Enter the SQL Server system administrator password: Confirm the SQL Server system administrator password: Configuring SQL Server... The licensing PID was successfully processed. The new edition is [Enterprise Evaluation Edition]. Created symlink from / etc/systemd/system/multi-user.target.wants/mssql-server.service to / lib/systemd/system/mssql-server.service. Setup has completed successfully. SQL Server is now starting.

A total of 8 versions are available, of which freely licensed versions are: evaluation, developer, and Quick.

The SA account must be a strong password (at least 8 characters, including uppercase and lowercase letters, decimal numbers, and / or non-alphanumeric symbols).

Verify that the service is running

$systemctl status mssql-server ● mssql-server.service-Microsoft SQL Server Database Engine Loaded: loaded (/ lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled) Active: active (running) since Thu 2017-10-12 11:50:29 CST; 1min 22s ago Docs: https://docs.microsoft.com/en-us/sql/linux Main PID: 20776 (sqlservr) CGroup: / system.slice/mssql-server.service ├─ 20776 / opt/mssql/bin/sqlservr └─ 20796 / opt/mssql/bin/sqlservr

Install SQL Server 2017 command line tools

If you want to create a database, you need to use the client tools sqlcmd and bcp.

Import the public repository GPG key

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

Add Microsoft Ubuntu Warehouse

$add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list)"

Install SQL Server command line tools and unixODBC developer kits

$apt-get update $apt-get install-y mssql-tools unixodbc-dev

The Sqlcmd tool is installed in / opt/mssql-tools/bin/ by default, and / opt/mssql-tools/bin/ is added to the environment variable for ease of use.

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

Sqlcmd is a command-line tool used to connect to SQL Server to run queries and perform management and development. If you want to use a more powerful graphics tool, you can use the mssql plug-in for SQL Server Management Studio or Visual Studio Code.

Use Sqlcmd to establish a local connection

Sqlcmd connects to the local SQL Server instance. The password is the SA account password that was configured during installation.

$sqlcmd-S localhost-U SA-P''

Parameter description

-the name of the machine to which the SQL Server is connected

-U user name of the connection SQL Server

-P password for connecting to SQL Server

If the connection is successful, the Sqlcmd command prompt: 1 > should be displayed, similar to the following

$sqlcmd-S localhost-U SA Password: 1 >

Create databases and query data

Create a new database

Create a new database called TestDB

From the sqlcmd command prompt, execute the Transact-SQL command to create the test database.

1 > CREATE DATABASE TestDB

The command is not executed immediately in SQL Server, and you must type GO on the new line to execute the command.

2 > GO

Returns the names of all databases on the server

1 > SELECT Name from sys.Databases 2 > GO Name-- master tempdb model msdb TestDB (5 rows affected)

Insert data

Create a new table Inventory, and then insert two new rows.

From the sqlcmd command prompt, switch to the new TestDB database.

1 > USE TestDB

Create a new table named Inventory

2 > CREATE TABLE Inventory (id INT, name NVARCHAR (50), quantity INT)

Insert data into a new table

3 > INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2,' orange', 154)

Execute the above commands in batch

4 > GO

The whole execution process is as follows

1 > USE TestDB 2 > CREATE TABLE Inventory (id INT, name NVARCHAR (50), quantity INT) 3 > INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2,' orange', 154); 4 > GO Changed database context to 'TestDB'. (1 rows affected) (1 rows affected)

Query data

Query the rows in the Inventory table with a quantity greater than 152via the sqlcmd command

1 > SELECT * FROM Inventory WHERE quantity > 152; 2 > GO id name quantity-2 orange 154( 1 rows affected)

Exit sqlcmd

To end the sqlcmd session, type QUIT.

1 > QUIT

Uninstall SQL Server 2017

To delete SQL Server 2017, use the following command

$apt-get remove mssql-server

Deleting a package does not delete the generated database file. If you want to delete the database file, use the following command

$sudo rm-rf / var/opt/mssql/

* Microsoft conscience's Visual Studio Code editor is recommended, which is extremely powerful, cross-platform and open source. The most important thing is that it is faster than Atom and rich in plug-ins. I have transferred from Atom to VSCode, thanks to the snail god for his recommendation!

The following picture shows the effect of the VSCode+MSSQL plug-in. Are there any great ones?

This is the end of this article on "how to deploy SQL Server 2017 under Ubuntu". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.