在CentOS 8/RHEL 8上安装Microsoft SQL Server 2019

时间:2020-02-23 14:30:47  来源:igfitidea点击:

本指南将指导我们完成在RHEL/CentOS 8上的Microsoft SQL Server(MS SQL)安装。MS SQL是Microsoft的关系数据库系统,于2015年开源。从SQL Server 2016开始,可用于生产。 RHEL和其他Linux发行版上的工作负载。

可用于生产工作负载的MS SQL Server版本是MS SQL 2016和MS SQL2019. 请按照下一部分中的步骤在RHEL/CentOS 8 Linux服务器上安装和运行Microsoft SQL Server(MS SQL)2019.

以下是在CentOS 8/RHEL 8 Linux上运行MS SQL Server 2019的最低系统要求:

最小内存为3 GBCPU处理器,最小速度为1.4 GHz。但建议>> 2 GHz SQL Server至少需要10 GB的可用硬盘空间

添加Microsoft SQL Server 2019存储库

Microsoft SQL数据库服务器软件包可在Red Hat存储库中找到,需要手动添加。

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

在RHEL 8/CentOS 8上安装MS SQL Server

添加的存储库中提供了Microsoft SQL Server 2019所需的所有软件包。只需执行以下命令,即可在CentOS 8/RHEL 8 Linux上安装MS SQL Server。

sudo dnf -y install mssql-server

依赖树:

Dependencies resolved.
========================================================================================================================================================
 Package                          Arch           Version                                         Repository                                        Size
========================================================================================================================================================
Installing:
 mssql-tools                      x86_64         17.5.2.1-1                                      packages-microsoft-com-prod                      223 k
 unixODBC-devel                   x86_64         2.3.7-1.rh                                      packages-microsoft-com-prod                       42 k
 mssql-server                     x86_64         15.0.4033.1-2                                   packages-microsoft-com-mssql-server-2019         209 M
Installing dependencies:
 gc                               x86_64         7.6.4-3.el8                                     AppStream                                        109 k
 gdb                              x86_64         8.2-6.el8                                       AppStream                                        296 k
 gdb-headless                     x86_64         8.2-6.el8                                       AppStream                                        3.7 M
 guile                            x86_64         5:2.0.14-7.el8                                  AppStream                                        3.5 M
 libatomic_ops                    x86_64         7.6.2-3.el8                                     AppStream                                         38 k
 libbabeltrace                    x86_64         1.5.4-2.el8                                     AppStream                                        201 k
 libipt                           x86_64         1.6.1-8.el8                                     AppStream                                         50 k
 python2                          x86_64         2.7.16-12.module_el8.1.0+219+cf9e6ac9           AppStream                                        109 k
 python2-libs                     x86_64         2.7.16-12.module_el8.1.0+219+cf9e6ac9           AppStream                                        6.0 M
 python2-pip-wheel                noarch         9.0.3-14.module_el8.1.0+219+cf9e6ac9            AppStream                                        1.2 M
 python2-setuptools-wheel         noarch         39.0.1-11.module_el8.1.0+219+cf9e6ac9           AppStream                                        289 k
 msodbcsql17                      x86_64         17.5.2.1-1                                      packages-microsoft-com-prod                      802 k
 unixODBC                         x86_64         2.3.7-1.rh                                      packages-microsoft-com-prod                      213 k
Installing weak dependencies:
 python2-pip                      noarch         9.0.3-14.module_el8.1.0+219+cf9e6ac9            AppStream                                        2.0 M
 python2-setuptools               noarch         39.0.1-11.module_el8.1.0+219+cf9e6ac9           AppStream                                        643 k
Enabling module streams:
 python27                                        2.7                                                                                                   

Transaction Summary
========================================================================================================================================================
Install  18 Packages

看到提示时,请接受许可协议:

The license terms for this product can be downloaded from
https://aka.ms/odbc17eula and found in
/usr/share/doc/msodbcsql17/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES

安装SQL Server命令行工具

然后使用unixODBC开发人员软件包安装mssql-tools。

sudo yum -y install mssql-tools unixODBC-devel

接受许可:

Do you accept the license terms? (Enter YES or NO)
YES
  Installing       : msodbcsql17-17.5.2.1-1.x86_64                                                                                                 2/4 
  Running scriptlet: msodbcsql17-17.5.2.1-1.x86_64                                                                                                 2/4 
  Running scriptlet: mssql-tools-17.5.2.1-1.x86_64                                                                                                 3/4 
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746949 and found in
/usr/share/doc/mssql-tools/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES

见下文。

确认安装。

$rpm -qi mssql-server
Name        : mssql-server
Version     : 15.0.4033.1
Release     : 2
Architecture: x86_64
Install Date: Sun 19 Apr 2017 08:44:02 PM EAT
Group       : Unspecified
Size        : 1095014063
License     : Commercial
Signature   : RSA/SHA256, Mon 30 Mar 2017 08:05:33 PM EAT, Key ID eb3e94adbe1229cf
Source RPM  : mssql-server-15.0.4033.1-2.src.rpm
Build Date  : Fri 27 Mar 2017 02:16:54 AM EAT
Build Host  : hls-rhel8-1-prod-build-rhel8-01
Relocations : (not relocatable)
Summary     : Microsoft SQL Server Relational Database Engine
....

$rpm -qi mssql-tools 
Name        : mssql-tools
Version     : 17.5.2.1
Release     : 1
Architecture: x86_64
Install Date: Sun 19 Apr 2017 08:45:46 PM EAT
Group       : Applications/Driver
Size        : 726935
License     : http://go.microsoft.com/fwlink/?LinkId=746949
Signature   : RSA/SHA256, Tue 03 Mar 2017 10:25:59 PM EAT, Key ID eb3e94adbe1229cf
Source RPM  : mssql-tools-17.5.2.1-1.src.rpm
Build Date  : Tue 03 Mar 2017 09:10:21 PM EAT
Build Host  : ODBC-029-CentOS68-1.galaxy.ad
Relocations : (not relocatable)
Packager    : Microsoft SQL Tools Team <theitroad@localhost>
Summary     : Tools for Microsoft(R) SQL Server (R)
....

初始化MS SQL数据库引擎

软件包安装完成后,运行runmssql-conf并按照提示设置SA密码并选择版本。

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

1.选择我们想要使用的版本

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): 2

为了我。不适用于2开发人员(免费,没有生产使用权)。

2.接受许可条款

The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&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

3.设置SQL Server系统管理员密码:

Enter the SQL Server system administrator password: <Password>
Confirm the SQL Server system administrator password: <Confirm Password> Configuring SQL Server... 

ForceFlush is enabled for this instance. 
ForceFlush feature is enabled for log durability.
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

该服务应已启动,并设置为在启动时启动。

$systemctl status mssql-server.service 
● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2017-04-19 21:05:26 EAT; 1h 52min ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 8699 (sqlservr)
Tasks: 157
Memory: 727.2M
CGroup: /system.slice/mssql-server.service
├─8699 /opt/mssql/bin/sqlservr
└─8727 /opt/mssql/bin/sqlservr
Apr 19 21:05:30 cent8.novalocal sqlservr[8699]: [61B blob data]
Apr 19 21:05:30 cent8.novalocal sqlservr[8699]: [96B blob data]
Apr 19 21:05:30 cent8.novalocal sqlservr[8699]: [66B blob data]

$systemctl is-enabled mssql-server.service 
enabled

/opt/mssql/bin /添加到$PATH变量中:

echo 'export PATH=$PATH:/opt/mssql/bin:/opt/mssql-tools/bin' | sudo tee /etc/profile.d/mssql.sh

源文件以在当前的shell会话中开始使用MS SQL可执行二进制文件:

source /etc/profile.d/mssql.sh

如果我们有活动的防火墙服务,请允许远程主机的SQL Server端口进行连接:

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

测试SQL Server

连接到SQL Server并验证其是否正常运行。

$sqlcmd -S localhost -U SA

使用在中设置的密码进行身份验证。

显示数据库用户:

1> select name from sysusers;
2> go
name
MS_AgentSigningCertificate##
MS_PolicyEventProcessingLogin##
db_accessadmin
db_backupoperator
db_datareader
db_datawriter
db_ddladmin
db_denydatareader
db_denydatawriter
db_owner
db_securityadmin
dbo
guest
INFORMATION_SCHEMA
public
sys
(16 rows affected)
1>

创建一个测试数据库:

CREATE DATABASE mytestDB
SELECT Name from sys.Databases
GO
USE mytestDB
CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT)
INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154);
GO
SELECT * FROM Inventory LIMIT 1;

在SQL Server上显示数据库。

1> select name,database_id from sys.databases; 
2> go
name          database_id
------------- ----------
master                  1
tempdb                  2
model                   3
msdb                    4
testDB                  5
(5 rows affected)

删除数据库:

1> DROP DATABASE testDB;
2> GO