学习SQL Server从在Linux上安装开始

原文链接

微软已经发布了SQL Server on Linux，目前支持Redhat和Ubuntu两种发行版。

下面我们来安装体验一下。

1. 获得YUM源：

YUM的repo文件地址：

https://packages.microsoft.com/config/rhel/7/mssql-server.repo

下载到本地：

wget https://packages.microsoft.com/config/rhel/7/mssql-server.repo

把此文件复制到/etc/yum.repos.d目录：

cp mssql-server.repo /etc/yum.repos.d/

更新yum信息：

[root@hwweb0 ~]# yum makecache
Loaded plugins: fastestmirror, langpacks
base | 3.6 kB 00:00:00
extras | 3.4 kB 00:00:00
openlogic | 1.3 kB 00:00:00
packages-microsoft-com-mssql-server | 2.9 kB 00:00:00
updates | 3.4 kB 00:00:00
(1/13): extras/7/x86_64/prestodelta | 78 kB 00:00:00
(2/13): openlogic/7/x86_64/filelists | 6.7 kB 00:00:00
(3/13): openlogic/7/x86_64/other | 3.7 kB 00:00:00
(4/13): extras/7/x86_64/filelists_db | 468 kB 00:00:01
(5/13): extras/7/x86_64/other_db | 706 kB 00:00:00
(6/13): packages-microsoft-com-mssql-server/filelists_db | 1.4 kB 00:00:00
(7/13): packages-microsoft-com-mssql-server/other_db | 728 B 00:00:00
(8/13): base/7/x86_64/other_db | 2.3 MB 00:00:01
(9/13): updates/7/x86_64/prestodelta | 786 kB 00:00:00
(10/13): updates/7/x86_64/filelists_db | 5.3 MB 00:00:01
(11/13): base/7/x86_64/filelists_db | 6.2 MB 00:00:03
(12/13): updates/7/x86_64/other_db | 79 MB 00:00:04
(13/13): packages-microsoft-com-mssql-server/primary_db | 2.4 kB 00:00:06
Loading mirror speeds from cached hostfile
openlogic 54/54
openlogic 54/54
Metadata Cache Created

2. 通过yum安装sql server：

Yum查找mssql的相关信息：

[root@hwweb0 ~]# yum search mssql
Loaded plugins: fastestmirror, langpacks
Repository packages-microsoft-com-mssql-server is listed more than once in the configuration
Loading mirror speeds from cached hostfile
=========================================================================== N/S matched: mssql ============================================================================
mssql-server.x86_64 : Microsoft(R) SQL Server(R) Relational Database Engine
mssql-server-ha.x86_64 : Microsoft(R) SQL Server(R) Relational Database Engine
 
Name and summary matches only, use "search all" for everything.

可以看到有两个版本，ha版本应该是做高可用性的版本。安装mssql-server：

[root@hwweb0 ~]# yum search mssql

这样就安装好了。

3. 配置sql server

[root@hwweb0 bin]# pwd
/opt/mssql/bin
[root@hwweb0 bin]# ./sqlservr-setup
Microsoft(R) SQL Server(R) Setup
 
You can abort setup at anytime by pressing Ctrl-C. Start this program
with the --help option for information about running it in unattended
mode.
 
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? If so, please type "YES": YES
 
Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:
 
Setting system administrator (SA) account password...
sqlservr: This program requires a machine with at least 3250 megabytes of memory.
Microsoft(R) SQL Server(R) setup failed with error code 1.
Please check the setup log in /var/opt/mssql/log/setup-20161117-184346.log
for more information.

我采用的是A1的机器，配置过程提示，内存需要大于3G。把机器升级到A2：

[root@hwchefserver bin]# ./sqlservr-setup
Microsoft(R) SQL Server(R) Setup
 
You can abort setup at anytime by pressing Ctrl-C. Start this program
with the --help option for information about running it in unattended
mode.
 
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? If so, please type "YES": YES
 
Please enter a password for the system administrator (SA) account:
Please confirm the password for the system administrator (SA) account:
 
Setting system administrator (SA) account password...
 
Do you wish to start the SQL Server service now? [y/n]: y
Do you wish to enable SQL Server to start on boot? [y/n]: y
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /usr/lib/systemd/system/mssql-server-telemetry.service.
 
Setup completed successfully.

配置成功。检查状态：

[root@hwchefserver bin]# systemctl status mssql

SQL Server已经在运行。

3. 检查

安装SQL Tools。SQL Tools：sqlcmd是mssql ODBC的一个工具，可以通过yum的repo直接yum安装。

首先下载yum的repo：

wget https://packages.microsoft.com/config/rhel/7/prod.repo

安装：

yum install mssql-tools

安装成功！

测试连接：

[root@hwchefserver ~]# sqlcmd -S localhost -U SA
Password:

登陆成功。

创建数据库test：

1> create database test;
2> go
1> use test;
2> go
Changed database context to 'test'.

查看数据库：

1> select name from sys.databases;
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
test

创建表hwtable：

1> create table hwtable ( id int, name varchar(20), gender varchar(20), age int);
2> go

插入数据：

1> create table hwtable ( id int, name varchar(20), gender varchar(20), age int);
2> go

插入数据：

1> insert into hwtable values (1, 'weiheng','male',20);
2> go

查询：

1> select * from hwtable;
2> go
id name gender age
----------- -------------------- -------------------- -----------
1 weiheng male 20
 
(1 rows affected)

MSSQL Sever On Linux就这样安装完成了！