Public preview of the next release of SQL Server on Linux

SQL Server for Linux

当微软宣布即将发布SQL Server for Linux版本的时候,有些人觉得很兴奋,有些人觉得然并卵,但是既然Gartner在2016年的数据库管理系统魔力象限图中将微软列在了第一位,超过了一直以来的霸主Oracle,那么无论如何这个SQL Server for Linux版本的发布都是值得关注的,微软将这个版本称为SQL Server vNext on Linux。vNext,好直白的期望。

Gartner-OP-DBMS-MQ-2016
Gartner-OP-DBMS-MQ-2016

微软在2016年11月中旬正式发布了SQL Server for Linux的第一个公众预览版,这条产品线将支持所有的企业级Linux平台,在第一个预览版中支持Red Hat Enterprise Linux 7.2和Ubuntu Linux 16.04,并且支持在macOS和Linux中的Docker容器,后续还会支持Suse Linux Enterprise Server,另外,微软承诺Linux上的SQL Server绝对不会是“SQL Server Lite”这样的阉割版数据库,而会是一个具备SQL Server 2016完整功能集的真正的企业级数据库,比如 in-memory OLTP,还有always-on encryption和row-level security这样的企业级安全功能。在现在的预览版中以下这些功能还不支持,但是后续会逐渐支持。

SQL Server Unsupported features and services

本文中会对SQL Server for Linux的安装、配置、使用做简单的测试,说实话,如果Oracle数据库的安装使用也能这样简单就太好了。

本文使用的测试环境是AWS的一个EC2实例,Red Hat Enterprise Linux 7.2,整个安装过程,从开始下载一直到数据库启动结束,不超过20分钟,这其中还包括了下载RPM包的15分钟。

第一步:用root用户下载安装镜像库的repo文件

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo

第二步:通过yum安装

sudo yum install -y mssql-server

第三步:设置数据库并启动
Update@2017-05-11
与之前不同,设置数据库的命令整合到mssql-conf命令中,而不再有sqlservr-setup命令。

[root@opreative-server-3 ~]# /opt/mssql/bin/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]:Y
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 from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup completed successfully.

[11:24:34:794][ec2-user@ip-172-31-11-228 ~]$ sudo /opt/mssql/bin/sqlservr-setup
[11:24:34:876]Microsoft(R) SQL Server(R) Setup
[11:24:34:876]
[11:24:34:876]You can abort setup at anytime by pressing Ctrl-C. Start this program
[11:24:34:876]with the –help option for information about running it in unattended
[11:24:34:876]mode.
[11:24:34:876]
[11:24:34:876]The license terms for this product can be downloaded from
[11:24:34:877]http://go.microsoft.com/fwlink/?LinkId=746388 and found
[11:24:34:877]in /usr/share/doc/mssql-server/LICENSE.TXT.
[11:24:34:880]
[11:24:45:937]Do you accept the license terms? If so, please type “YES”: YES
[11:24:45:938]
[11:24:55:008]Please enter a password for the system administrator (SA) account:
[11:24:59:810]Please confirm the password for the system administrator (SA) account:
[11:24:59:810]
[11:24:59:811]Setting system administrator (SA) account password…
[11:25:03:006]
[11:25:07:728]Do you wish to start the SQL Server service now? [y/n]: y
[11:25:11:927]Do you wish to enable SQL Server to start on boot? [y/n]: y
[11:25:12:022]Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
[11:25:12:058]Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /usr/lib/systemd/system/mssql-server-telemetry.service.
[11:25:12:087]
[11:25:12:088]Setup completed successfully.

That’s it! SQL Server for Linux数据库就安装完毕并正常启动了。

可以通过systemctl来检查mssql-server的服务的启动状态。

[root@opreative-server-3 ~]# 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 Thu 2017-05-11 17:00:17 CST; 34s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 16147 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ├─16147 /opt/mssql/bin/sqlservr
           └─16166 /opt/mssql/bin/sqlservr

May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.52 spid18s     Server is listening on [ 0.0.0.0  1433].
May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.53 Server      Server is listening on [ 127.0.0.1  1434].
May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.53 Server      Dedicated admin connection support was established for listening locally on port 1434.
May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.53 spid18s     SQL Server is now ready for client connections. This is an informational message... required.
May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.86 spid11s     Starting up database 'tempdb'.
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.03 spid11s     The tempdb database has 1 data file(s).
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.03 spid22s     The Service Broker endpoint is in disabled or stopped state.
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.03 spid22s     The Database Mirroring endpoint is in disabled or stopped state.
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.05 spid22s     Service Broker manager has started.
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.08 spid7s      Recovery is complete. This is an informational message only. No user action is required.
Hint: Some lines were ellipsized, use -l to show in full.

[ec2-user@ip-172-31-11-228 ~]$ systemctl status mssql-server
● mssql-server.service – Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2016-12-04 22:25:08 EST; 1min 59s ago
Main PID: 16558 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─16558 /opt/mssql/bin/sqlservr
└─16571 /opt/mssql/bin/sqlservr

安装完毕以后可以通过自带的mssql-conf命令进行一些简单的配置,可配置项不多,主要是监听端口,默认的数据文件所在目录,日志所在目录,备份所在目录等。

[ec2-user@ip-172-31-11-228 ~]$ sudo /opt/mssql/bin/mssql-conf list
Supported settings using the 'set' option: 
tcpport
defaultbackupdir
defaultdumpdir
defaultlogdir
defaultdatadir
For all command line options use -h or --help

如果希望在服务器上直接通过sqlcmd命令行登入数据库,还需要额外安装一个mssql-tools的RPM包,这个安装包里包括sqlcmd和bcp(Bulk import-export utility)。

第一步:用root用户下载安装mssql-tools的repo文件

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

第二步:通过yum安装

sudo yum install -y mssql-tools

然后就可以通过sqlcmd来登入数据库,并使用Transact-SQL (T-SQL)语言来进行各种操作和管理了。我并非专业的SQL Server DBA,只是浅尝辄止而已。登录以后,创建了一个新数据库testdb,然后进入testdb,创建了一张新表inventory,然后在inventory表中插入了2条记录。

[ec2-user@ip-172-31-11-228 ~]$ sqlcmd -S localhost -U SA -P 'PASSWORD OF SA'
1> SELECT Name from sys.Databases;
2> GO
Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            

(4 rows affected)
1> create database testdb;
2> go
1> SELECT Name from sys.Databases;
2> go
Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
testdb                                                                                                                          

(5 rows affected)
1> use testdb
2> go
Changed database context to 'testdb'.
1> CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT);
2> GO
1> INSERT INTO inventory VALUES (1, 'banana', 150);
2> INSERT INTO inventory VALUES (2, 'orange', 154);
3> GO

(1 rows affected)

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

(1 rows affected)

除了sqlcmd命令行之外,在Windows操作系统下有全套的SQL Server Management Studio (SSMS) ,可以直接使用这个具有丰富功能的图形化管理工具来直接管理SQL Server vNext on Linux。多说一句,由于是使用1433端口连接服务器上的数据库,因此需要在AWS EC2中将该服务器实例所属的Security Group中的Inbound策略中开放TCP 1433端口。

SQL Server Management Studio

【结论】SQL Server vNext on Linux预览版在安装、操作过程中非常顺畅,在后续有更多功能加入以后,应该是企业级数据库非常优秀的选择。先不说是否会有Oracle用户迁移到SQL Server上,这至少给了现在正在使用SQL Server的客户们更广阔的选择空间,现在他们运行在Windows Server上的SQL Server数据库也可以移植到Linux中了,无论如何,这是一个很大的进步,微软这步要是能早一些迈出可能就更好了。