写在前面
自从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 Dependencies Resolved =============================================================================================================================================================================================== Transaction Summary Total download size: 222 M +--------------------------------------------------------------+ Verifying : mssql-server-15.0.1200.24-2.x86_64 1/1 Installed: Complete! |
-
程序包安装完成后，请运行 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 Feb 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?). [root@localhost local]# sudo /opt/mssql/bin/mssql-conf setup Details about editions can be found at Use of PAID editions of this software requires separate licensing through a Enter your edition(1-8): 1 The privacy statement can be viewed at: Enter the SQL Server system administrator password: sqlservr: This program requires a machine with at least 2000 megabytes of memory. Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG 如果出现以上问题说明服务器内存不足2G。建议添加内存即可。如下是正常设置： [root@localhost local]# sudo /opt/mssql/bin/mssql-conf setup Details about editions can be found at Use of PAID editions of this software requires separate licensing through a Enter your edition(1-8): 1 The privacy statement can be viewed at: Enter the SQL Server system administrator password: This is an evaluation version. There are [100] days left in the evaluation period. |
安装 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 Dependencies Resolved =============================================================================================================================================================================================== Transaction Summary Total download size: 1.2 M Do you accept the license terms? (Enter YES or NO) Do you accept the license terms? (Enter YES or NO) Installed: Dependency Installed: 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 帐户提供的密码。[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> 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
参考网址： https://docs.microsoft.com/zh-cn/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017
1621
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
