写在前面
自从SQL Server 2019数据库问世以来，一直没有时间去尝试安装并体验一下新版本及新功能，今天在这里抽出闲暇之余来搭建测试环境，并体验一下SQL Server 2019中都又那些功能兼容Linux操作系统。
安装之前首先要准备相应的环境介质：
操作系统版本为：CentOS Linux release 7.5.1804 (Core)
数据库版本为：SQL Server 2019
内存： 至少2G以上
开始安装SQL Server
若要在 Centos上配置 SQL Server，请在终端中运行以下命令，以便安装mssql server包：
下载 Microsoft SQL Server 2019预览版 ， 对于 SQL Server 2019 安装中使用以下命令：
[root@localhost local]# sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-preview.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 240 100 240 0 0 562 0 --:--:-- --:--:-- --:--:-- 562
[root@localhost local]#运行以下命令，安装 SQL Server：
[root@localhost local]# sudo yum install -y mssql-server
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.nwsuaf.edu.cn
* extras: mirrors.huaweicloud.com
* updates: mirrors.huaweicloud.com
packages-microsoft-com-mssql-server-preview | 2.9 kB 00:00:00
packages-microsoft-com-mssql-server-preview/primary_db | 53 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:15.0.1200.24-2 will be installed
--> Finished Dependency ResolutionDependencies Resolved
===============================================================================================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================================================================================
Installing:
mssql-server x86_64 15.0.1200.24-2 packages-microsoft-com-mssql-server-preview 222 MTransaction Summary
===============================================================================================================================================================================================
Install 1 PackageTotal download size: 222 M
Installed size: 222 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/packages-microsoft-com-mssql-server-preview/packages/mssql-server-15.0.1200.24-2.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY:00:00 ETA
Public key for mssql-server-15.0.1200.24-2.x86_64.rpm is not installed
mssql-server-15.0.1200.24-2.x86_64.rpm | 222 MB 00:05:29
Retrieving key from https://packages.microsoft.com/keys/microsoft.asc
Importing GPG key 0xBE1229CF:
Userid : "Microsoft (Release signing) <gpgsecurity@microsoft.com>"
Fingerprint: bc52 8686 b50d 79e3 39d3 721c eb3e 94ad be12 29cf
From : https://packages.microsoft.com/keys/microsoft.asc
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mssql-server-15.0.1200.24-2.x86_64 1/1+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+ Verifying : mssql-server-15.0.1200.24-2.x86_64 1/1
Installed:
mssql-server.x86_64 0:15.0.1200.24-2 Complete!
[root@localhost local]#- 程序包安装完成后，请运行 mssql-conf setup 命令并按提示设置 SA 密码，然后选择版
[root@localhost local]# sudo /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=0x409Use 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=0x409The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409Do 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...sqlservr: This program requires a machine with at least 2000 megabytes of memory.
/opt/mssql/bin/sqlservr: This program requires a machine with at least 2000 megabytes of memory.Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG
in /var/opt/mssql/log for more information.
[root@localhost local]#- 提示
以下 SQL Server 2017 版本自由地授予使用许可：评估、 开发人员版和 Express。
备注
请确保为 SA 帐户指定强密码（最少 8 个字符，包括大写和小写字母、十进制数字和/或非字母数字符号）。 - 配置完成后，请验证服务是否正在运行：
[root@localhost local]# systemctl status mssql-server
?.mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; disabled; vendor preset: disabled)
Active: inactive (dead)
Docs: https://docs.microsoft.com/en-us/sql/linux
[root@localhost local]# systemctl start mssql-server
[root@localhost local]#
[root@localhost local]# systemctl status mssql-server
?.mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; disabled; vendor preset: disabled)
Active: failed (Result: start-limit) since Wed 2019-02-27 10:46:17 CST; 6s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Process: 3432 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=1/FAILURE)
Main PID: 3432 (code=exited, status=1/FAILURE)Feb 27 10:46:17 localhost.localdomain systemd[1]: mssql-server.service: main process exited, code=exited, status=1/FAILURE
Feb 27 10:46:17 localhost.localdomain systemd[1]: Unit mssql-server.service entered failed state.
Feb 27 10:46:17 localhost.localdomain systemd[1]: mssql-server.service failed.
Feb 27 10:46:17 localhost.localdomain systemd[1]: mssql-server.service holdoff time over, scheduling restart.
Feb 27 10:46:17 localhost.localdomain systemd[1]: start request repeated too quickly for mssql-server.service
Feb 27 10:46:17 localhost.localdomain systemd[1]: Failed to start Microsoft SQL Server Database Engine.
Feb 27 10:46:17 localhost.localdomain systemd[1]: Unit mssql-server.service entered failed state.
Feb 27 10:46:17 localhost.localdomain systemd[1]: mssql-server.service failed.
[root@localhost local]# systemctl status firewall
Unit firewall.service could not be found.
[root@localhost local]# systemctl status firewalld
?.firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2019-02-27 10:18:28 CST; 28min ago
Docs: man:firewalld(1)
Main PID: 751 (firewalld)
Tasks: 2
CGroup: /system.slice/firewalld.service
?..751 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopidFeb 27 10:18:27 localhost.localdomain systemd[1]: Starting firewalld - dynamic firewall daemon...
Feb 27 10:18:28 localhost.localdomain systemd[1]: Started firewalld - dynamic firewall daemon.- 若要允许远程连接，请打开防火墙上的 SQL Server 端口。 默认的 SQL Server 端口为 TCP 1433。 如果对防火墙使用 FirewallD，可以使用以下命令：
cal]# sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
success
[root@localhost local]# sudo firewall-cmd --reload
success
[root@localhost local]#SQL Server 目前正在 centos系统的计算机上运行，可以使用了！
[root@localhost local]# systemctl status firewalld
?.firewalld.service - firewalld - dynamic firewall daemon
Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2019-02-27 10:18:28 CST; 35min ago
Docs: man:firewalld(1)
Main PID: 751 (firewalld)
Tasks: 2
CGroup: /system.slice/firewalld.service
?..751 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopidFeb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete FORWARD --destination 192.168.122.0/24 --out-in...at chain?).
Feb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete FORWARD --source 192.168.122.0/24 --in-interfac...at chain?).
Feb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete FORWARD --in-interface virbr0 --out-interface v...at chain?).
Feb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete FORWARD --out-interface virbr0 --jump REJECT' f... that name.
Feb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete FORWARD --in-interface virbr0 --jump REJECT' fa... that name.
Feb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete INPUT --in-interface virbr0 --protocol udp --de...at chain?).
Feb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete INPUT --in-interface virbr0 --protocol tcp --de...at chain?).
Feb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete OUTPUT --out-interface virbr0 --protocol udp --...at chain?).
Feb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete INPUT --in-interface virbr0 --protocol udp --de...at chain?).
Feb 27 10:52:49 localhost.localdomain firewalld[751]: WARNING: COMMAND_FAILED: '/usr/sbin/iptables -w2 -w --table filter --delete INPUT --in-interface virbr0 --protocol tcp --de...at chain?).
Hint: Some lines were ellipsized, use -l to show in full.
[root@localhost local]#
[root@localhost local]#[root@localhost local]# sudo /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=0x409Use 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=0x409The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
The passwords do not match. Please try again.
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...sqlservr: This program requires a machine with at least 2000 megabytes of memory.
/opt/mssql/bin/sqlservr: This program requires a machine with at least 2000 megabytes of memory.Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG
in /var/opt/mssql/log for more information.
[root@localhost local]#如果出现以上问题说明服务器内存不足2G。建议添加内存即可。如下是正常设置：
[root@localhost local]# sudo /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=0x409Use 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=0x409The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...This is an evaluation version. There are [100] days left in the evaluation period.
The licensing PID was successfully processed. The new edition is [Enterprise Evaluation Edition].
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.
[root@localhost local]#安装 SQL Server 命令行工具
若要创建数据库，需要使用一个能够在 SQL Server 上运行 Transact-SQL 语句的工具进行连接。 以下步骤安装 SQL Server 命令行工具： sqlcmd和bcp。
- 下载 Microsoft Red Hat 存储库配置文件。
[root@localhost local]# sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 193 100 193 0 0 111 0 0:00:01 0:00:01 --:--:-- 111
[root@localhost local]#
- 如果你有旧版mssql 工具安装，请删除任何较旧的 unixODBC 包。
[root@localhost local]# sudo yum remove unixODBC-utf16 unixODBC-utf16-devel Loaded plugins: fastestmirror, langpacks No Match for argument: unixODBC-utf16 No Match for argument: unixODBC-utf16-devel No Packages marked for removal[root@localhost local]# sudo yum install -y mssql-tools unixODBC-devel
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
* base: mirrors.nwsuaf.edu.cn
* extras: mirrors.huaweicloud.com
* updates: mirrors.huaweicloud.com
packages-microsoft-com-prod | 2.9 kB 00:00:00
packages-microsoft-com-prod/primary_db | 160 kB 00:00:01
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.3.0.1-1 will be installed
--> Processing Dependency: msodbcsql17 < 17.4.0.0 for package: mssql-tools-17.3.0.1-1.x86_64
--> Processing Dependency: msodbcsql17 >= 17.3.0.0 for package: mssql-tools-17.3.0.1-1.x86_64
---> Package unixODBC-devel.x86_64 0:2.3.7-1.rh will be installed
--> Processing Dependency: unixODBC = 2.3.7 for package: unixODBC-devel-2.3.7-1.rh.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.3.1.1-1 will be installed
---> Package unixODBC.x86_64 0:2.3.7-1.rh will be installed
--> Finished Dependency ResolutionDependencies Resolved
===============================================================================================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================================================================================
Installing:
mssql-tools x86_64 17.3.0.1-1 packages-microsoft-com-prod 254 k
unixODBC-devel x86_64 2.3.7-1.rh packages-microsoft-com-prod 42 k
Installing for dependencies:
msodbcsql17 x86_64 17.3.1.1-1 packages-microsoft-com-prod 769 k
unixODBC x86_64 2.3.7-1.rh packages-microsoft-com-prod 213 kTransaction Summary
===============================================================================================================================================================================================
Install 2 Packages (+2 Dependent packages)Total download size: 1.2 M
Installed size: 1.4 M
Downloading packages:
(1/4): mssql-tools-17.3.0.1-1.x86_64.rpm | 254 kB 00:00:04
(2/4): unixODBC-2.3.7-1.rh.x86_64.rpm | 213 kB 00:00:03
(3/4): msodbcsql17-17.3.1.1-1.x86_64.rpm | 769 kB 00:00:08
(4/4): unixODBC-devel-2.3.7-1.rh.x86_64.rpm | 42 kB 00:00:01
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 142 kB/s | 1.2 MB 00:00:08
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : unixODBC-2.3.7-1.rh.x86_64 1/4
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
Installing : msodbcsql17-17.3.1.1-1.x86_64 2/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
Installing : mssql-tools-17.3.0.1-1.x86_64 3/4
Installing : unixODBC-devel-2.3.7-1.rh.x86_64 4/4
Verifying : unixODBC-devel-2.3.7-1.rh.x86_64 1/4
Verifying : unixODBC-2.3.7-1.rh.x86_64 2/4
Verifying : mssql-tools-17.3.0.1-1.x86_64 3/4
Verifying : msodbcsql17-17.3.1.1-1.x86_64 4/4Installed:
mssql-tools.x86_64 0:17.3.0.1-1 unixODBC-devel.x86_64 0:2.3.7-1.rh Dependency Installed:
msodbcsql17.x86_64 0:17.3.1.1-1 unixODBC.x86_64 0:2.3.7-1.rh Complete!- 为方便起见，请将
/opt/mssql-tools/bin/添加到 PATH 环境变量。 这样就可以在运行工具时不指定完整路径。 请运行以下命令，以便修改登录会话和交互/非登录会话的 PATH：
[root@localhost local]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
[root@localhost local]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
[root@localhost local]# source ~/.bashrc
[root@localhost local]#
[root@localhost local]#
本地连接
以下步骤使用 sqlcmd 本地连接到新的 SQL Server 实例。
- 使用 SQL Server 名称 (-S)，用户名 (-U) 和密码 (-P) 的参数运行 sqlcmd。 在本教程中，用户进行本地连接，因此服务器名称为
localhost。 用户名为SA，密码是在安装过程中为 SA 帐户提供的密码。
1. [root@localhost local]# sqlcmd
Microsoft (R) SQL Server Command Line Tool
Version 17.3.0000.1 Linux
Copyright (c) 2012 Microsoft. All rights reserved.
usage: sqlcmd [-U login id] [-P password]
[-S server or Dsn if -D is provided]
[-H hostname] [-E trusted connection]
[-N Encrypt Connection][-C Trust Server Certificate]
[-d use database name] [-l login timeout] [-t query timeout]
[-h headers] [-s colseparator] [-w screen width]
[-a packetsize] [-e echo input] [-I Enable Quoted Identifiers]
[-c cmdend]
[-q "cmdline query"] [-Q "cmdline query" and exit]
[-m errorlevel] [-V severitylevel] [-W remove trailing spaces]
[-u unicode output] [-r[0|1] msgs to stderr]
[-i inputfile] [-o outputfile]
[-k[1|2] remove[replace] control characters]
[-y variable length type display width]
[-Y fixed length type display width]
[-p[1] print statistics[colon format]]
[-R use client regional setting]
[-K application intent]
[-M multisubnet failover]
[-b On error batch abort]
[-D Dsn flag, indicate -S is Dsn]
[-X[1] disable commands, startup script, environment variables [and exit]]
[-x disable variable substitution]
[-? show syntax summary]
[root@localhost local]#- [root@localhost local]# sqlcmd -S localhost -U sa -P 'sql123$%'
1. 1> select @@version
2> go
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (CTP2.2) - 15.0.1200.24 (X64)
Dec 5 2018 16:51:26
Copyright (C) 2018 Microsoft Corporation
Enterprise Evaluation Edition (64-bit) on Linux (CentOS Linux 7 (Core)) <X64>
(1 rows affected)
1>- 提示
可以在命令行上省略密码，以收到密码输入提示。
提示
如果以后决定进行远程连接，请指定 -S 参数的计算机名称或 IP 地址，并确保防火墙上的端口 1433 已打开。 - 如果成功，应会显示 sqlcmd 命令提示符：
1>。 - 如果连接失败，请首先尝试根据错误消息诊断问题。
创建和查询数据
下面各部分将逐步介绍如何使用 sqlcmd 新建数据库、添加数据并运行简单查询。
1> create database testdb
2> go
1> select Name from sys.databases
2> go
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
testdb (5 rows affected)
1>
1> use testdb
2> go
Changed database context to 'testdb'.
1> create table tb01(id int identified(1,1) primary key,name varchar(20),ddate datetime)
2> go
Msg 102, Level 15, State 1, Server localhost, Line 1
Incorrect syntax near 'identified'.
1> create table tb01(id int identity(1,1) primary key,name varchar(20),ddate datetime);
2> go
1>
1> select * from testdb.dbo.tb01
2> go
id name ddate
----------- -------------------- -----------------------(0 rows affected)
1>
1>
1>
1> insert into tb01(name,ddate) values('testname',getdate());
2> go(1 rows affected)
1> go
1>
1>
1>
1> insert into tb01(name,ddate) values('testname',getdate());
2> go(1 rows affected)
1>
1>
1>
1> select * from tb01
2> go
id name ddate
----------- -------------------- -----------------------
1 testname 2019-02-27 11:17:46.327
2 testname 2019-02-27 11:17:56.140(2 rows affected)>quit
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
