做了很多年的微软SQL Server数据库的管理工作,从SQL Server 6.5一路走来,也离开SQL Server管理转到开源数据库MySQL/MariaDB很多年。目前已经将工作平台转到Fedora 25,家里也主要用Fedora 25/MacOS。目前打算在公司内部做一些数据库方面的知识分享,由于需要说明一些数据库方面的基础,而MySQL对此有着不同的支持,所以需要安装SQL Server,同时也不想运行在VirtualBox虚拟机中,且听说微软SQL Server已经开始支持Linux。于是搜索了一些资料,开始折腾SQL Server on Fedora 25。
这里仅做一些简要的记录,理论上说安装设置过程应该非常顺利。
第一步:获取yum源,有两个源
$ sudo wget https://packages.microsoft.com/config/rhel/7/mssql-server.repo -O /etc/yum.repos.d/mssql-server.repo
--2017-03-21 10:02:04-- https://packages.microsoft.com/config/rhel/7/mssql-server.repo
Resolving packages.microsoft.com (packages.microsoft.com)... 13.75.127.55
Connecting to packages.microsoft.com (packages.microsoft.com)|13.75.127.55|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 220 [application/octet-stream]
Saving to: ‘/etc/yum.repos.d/mssql-server.repo’
/etc/yum.repos.d/mssql-server.r 100%[==================================================>] 220 --.-KB/s in 0s
2017-03-21 10:02:04 (22.9 MB/s) - ‘/etc/yum.repos.d/mssql-server.repo’ saved [220/220]
$ sudo wget wget https://packages.microsoft.com/config/rhel/7/prod.repo -O /etc/yum.repos.d/prod.repo
--2017-03-21 10:03:12-- https://packages.microsoft.com/config/rhel/7/prod.repo
Resolving packages.microsoft.com (packages.microsoft.com)... 13.75.127.55
Connecting to packages.microsoft.com (packages.microsoft.com)|13.75.127.55|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 193 [application/octet-stream]
Saving to: ‘/etc/yum.repos.d/prod.repo’
/etc/yum.repos.d/prod.repo 100%[======================================================>] 193 --.-KB/s in 0s
2017-03-21 10:03:12 (40.9 MB/s) - ‘/etc/yum.repos.d/prod.repo’ saved [193/193]
第二步:安装SQL Server和命令行管理工具
$ sudo dnf search mssql
packages-microsoft-com-mssql-server 36 kB/s | 3.8 kB 00:00
packages-microsoft-com-prod 170 kB/s | 18 kB 00:00
===================================================== N/S Matched: mssql ======================================================
opendbx-mssql.x86_64 : MSSQL backend - provides mssql support in opendbx
mssql-tools.x86_64 : Tools for Microsoft(R) SQL Server (R)
mssql-server.x86_64 : Microsoft(R) SQL Server(R) Relational Database Engine
mssql-server-ha.x86_64 : High Availability support for Microsoft(R) SQL Server(R) Relational Database Engine
mssql-server-fts.x86_64 : Microsoft(R) SQL Server(R) Full Text Search
mssql-server-agent.x86_64 : Microsoft SQL Server Agent
php-ZendFramework-Db-Adapter-Pdo-Mssql.noarch : Zend Framework database adapter for MS SQL PDO
$ sudo dnf install mssql-server mssql-tools
Last metadata expiration check: 0:00:35 ago on Tue Mar 21 10:03:34 2017.
Dependencies resolved.
===============================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================
Installing:
msodbcsql x86_64 13.1.4.0-1 packages-microsoft-com-prod 3.9 M
mssql-server x86_64 14.0.405.200-1 packages-microsoft-com-mssql-server 142 M
mssql-tools x86_64 14.0.4.0-1 packages-microsoft-com-prod 249 k
Transaction Summary
===============================================================================================================================
Install 3 Packages
Total download size: 146 M
Installed size: 146 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): mssql-tools-14.0.4.0-1.x86_64.rpm 95 kB/s | 249 kB 00:02
(2/3): msodbcsql-13.1.4.0-1.x86_64.rpm 42 kB/s | 3.9 MB 01:35
(3/3): mssql-server-14.0.405.200-1.x86_64.rpm 122 kB/s | 142 MB 19:51
-------------------------------------------------------------------------------------------------------------------------------
Total 125 kB/s | 146 MB 19:51
warning: /var/cache/dnf/packages-microsoft-com-mssql-server-6ec86e9fa6acaff2/packages/mssql-server-14.0.405.200-1.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY
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
Is this ok [y/N]: y
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
The license terms for this product can be downloaded from
https://aka.ms/odbc131eula and found in
/usr/share/doc/msodbcsql/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 : msodbcsql-13.1.4.0-1.x86_64 1/3
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-14.0.4.0-1.x86_64 2/3
Installing : mssql-server-14.0.405.200-1.x86_64 3/3
+--------------------------------------------------------------------------+
| Please run sudo /opt/mssql/bin/mssql-conf setup to complete the setup of |
| Microsoft(R) SQL Server(R). |
+--------------------------------------------------------------------------+
Verifying : mssql-server-14.0.405.200-1.x86_64 1/3
Verifying : mssql-tools-14.0.4.0-1.x86_64 2/3
Verifying : msodbcsql-13.1.4.0-1.x86_64 3/3
Installed:
msodbcsql.x86_64 13.1.4.0-1 mssql-server.x86_64 14.0.405.200-1 mssql-tools.x86_64 14.0.4.0-1
Complete!
过程中如果有包依赖应该可以自动解决,因为我机器本身安装有不少开发包,所以本次并没有安装第三方包。
同时,正式安装前会需要导入GPG签名并且键入YES两次来同意微软的许可协议。
第三步:初始化SQL Server并启动服务
$ cd /opt/mssql/bin/
$ sudo ./mssql-conf setup
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746388
and found in /usr/share/doc/mssql-server/LICENSE.TXT.
Do you accept the license terms? [Yes/No]:yes
Setting up Microsoft SQL Server
Enter the new SQL Server system administrator password:
Confirm the new SQL Server system administrator password:
Starting Microsoft SQL Server...
Enabling Microsoft SQL Server to run at boot...
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /usr/lib/systemd/system/mssql-server.service.
Setup completed successfully.
使用命令行初始化向导,仅需同意许可协议及提供sa的密码即可。初始化完成后服务自动启动。
查看服务运行状态
$ systemctl status mssql-server
● 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 Tue 2017-03-21 10:35:54 CST; 11min ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 17354 (sqlservr)
Tasks: 154
CGroup: /system.slice/mssql-server.service
├─17354 /opt/mssql/bin/sqlservr
└─17420 /opt/mssql/bin/sqlservr
Mar 21 10:36:00 rx-wj39106 sqlservr[17354]: [66B blob data]
Mar 21 10:36:00 rx-wj39106 sqlservr[17354]: [75B blob data]
Mar 21 10:36:00 rx-wj39106 sqlservr[17354]: [96B blob data]
Mar 21 10:36:00 rx-wj39106 sqlservr[17354]: [100B blob data]
Mar 21 10:36:00 rx-wj39106 sqlservr[17354]: [71B blob data]
Mar 21 10:36:00 rx-wj39106 sqlservr[17354]: [124B blob data]
Mar 21 10:41:01 rx-wj39106 sqlservr[17354]: [71B blob data]
Mar 21 10:43:03 rx-wj39106 sqlservr[17354]: [66B blob data]
Mar 21 10:43:03 rx-wj39106 sqlservr[17354]: [109B blob data]
Mar 21 10:43:03 rx-wj39106 sqlservr[17354]: [110B blob data]
查看本地端口,发现1433/1434已经在监听当中了
$ ss -nat
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 5 127.0.0.1:631 *:*
LISTEN 0 128 *:1433 *:*
LISTEN 0 128 127.0.0.1:1434 *:*
LISTEN 0 128 *:8000 *:*
LISTEN 0 128 *:8800 *:*
LISTEN 0 128 127.0.0.1:9000 *:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 *:3307 *:*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:80 *:*
第四步:连接SQL Server
$ cd /opt/mssql-tools/bin/
$ ./sqlcmd -S localhost -U sa
Password:
1> CREATE DATABASE sbtest;
2> GO
1> USE sbtest;
2> GO
Changed database context to 'sbtest'.
1> CREATE TABLE sbtest1 (id INT NOT NULL PRIMARY KEY, cdate DATETIME NOT NULL DEFAULT GETDATE());
2> GO
1> INSERT INTO sbtest1 (id) VALUES (1);
2> INSERT INTO sbtest1 (id) VALUES (2);
3> INSERT INTO sbtest1 (id) VALUES (3);
4> INSERT INTO sbtest1 (id) VALUES (4);
5> INSERT INTO sbtest1 (id) VALUES (5);
6> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> SELECT id,cdate FROM sbtest1;
2> GO
id cdate
----------- -----------------------
1 2017-03-21 10:44:44.010
2 2017-03-21 10:44:44.010
3 2017-03-21 10:44:44.010
4 2017-03-21 10:44:44.010
5 2017-03-21 10:44:44.010
(5 rows affected)
连接SQL Server,创建数据库sbtest,创建表sbtest1,添加/查询数据,一切跟预期一致,至此SQL Server在Fedora 25上安装成功。