Azure虽然向用户提供SQL paas服务，但是大多数用户还是习惯在用虚拟机自己搭建SQL server，这样的好处是便于后期最大化的扩展，所以鉴于这些情况，所以觉得有必要写这篇博客。
首先，我们要建立一台虚拟机，鉴SQL server对虚拟机内存的要求最低为3GB，所以笔者建议虚拟机选择A3或者A4，当然也可以选择A1的虚拟机，然后再升为A3或者A4，因为本次环境只是测试，这里我就直接建立A3虚拟机，OS为centos7.1
第一步，建立虚拟机，过程省略，有不会的读者自行百度，结果如下
第二步，登陆虚拟机，切换到root用户
[yangyang@sqlserver1 ~]$ sudo su - root We trust you have received the usual lecture from the local System Administrator. It usually boils down to these three things: #1) Respect the privacy of others. #2) Think before you type. #3) With great power comes great responsibility. [sudo] password for yangyang: Sorry, try again. [sudo] password for yangyang: [root@sqlserver1 ~]#
第三步，获得yum源，yum的repo文件地址
下载到本地
[root@sqlserver1 ~]# wget https://packages.microsoft.com/config/rhel/7/mssql-server.repo --2017-01-05 06:08:01-- 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: 鈓ssql-server.repo? 100%[======================================>] 220 --.-K/s in 0s 2017-01-05 06:08:01 (13.7 MB/s) - 鈓ssql-server.repo?saved [220/220] [root@sqlserver1 ~]#
接下来把文件复制到/etc/yum.repos.d目录
[root@sqlserver1 ~]# cp mssql-server.repo /etc/yum.repos.d/
更新yum信息
[root@sqlserver1 ~]# yum makecache Loaded plugins: fastestmirror, langpacks base | 3.6 kB 00:00 extras | 3.4 kB 00:00 openlogic | 1.3 kB 00:00 packages-microsoft-com-mssql-server | 2.9 kB 00:00 updates | 3.4 kB 00:00 (1/18): base/7/x86_64/group_gz | 155 kB 00:00 (2/18): base/7/x86_64/primary_db | 5.6 MB 00:01 (3/18): base/7/x86_64/other_db | 2.4 MB 00:00 (4/18): base/7/x86_64/filelists_db | 6.6 MB 00:02 (5/18): extras/7/x86_64/prestodelta | 88 kB 00:00 (6/18): extras/7/x86_64/filelists_db | 516 kB 00:00 (7/18): extras/7/x86_64/primary_db | 183 kB 00:00 (8/18): extras/7/x86_64/other_db | 813 kB 00:00 (9/18): openlogic/7/x86_64/filelists | 8.8 kB 00:00 (10/18): openlogic/7/x86_64/primary | 12 kB 00:00 (11/18): openlogic/7/x86_64/other | 4.0 kB 00:00 (12/18): packages-microsoft-com-mssql-server/primary_db | 3.2 kB 00:00 (13/18): packages-microsoft-com-mssql-server/other_db | 860 B 00:00 (14/18): packages-microsoft-com-mssql-server/filelists_db | 1.8 kB 00:00 (15/18): updates/7/x86_64/prestodelta | 135 kB 00:00 (16/18): updates/7/x86_64/filelists_db | 899 kB 00:00 (17/18): updates/7/x86_64/primary_db | 1.2 MB 00:00 (18/18): updates/7/x86_64/other_db | 13 MB 00:01 Determining fastest mirrors openlogic 60/60 openlogic 60/60 openlogic 60/60 Metadata Cache Created
第四步，通过yum安装sql server
yum查找mssql的相关信息
[root@sqlserver1 ~]# yum search mssql Loaded plugins: fastestmirror, langpacks 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应该是做高可用性的版本，所以我们选择安装第一个
[root@sqlserver1 ~]# yum install mssql-server -y Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile Resolving Dependencies --> Running transaction check ---> Package mssql-server.x86_64 0:14.0.100.187-1 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: mssql-server x86_64 14.0.100.187-1 packages-microsoft-com-mssql-server 139 M Transaction Summary ================================================================================ Install 1 Package Total download size: 139 M Installed size: 139 M Downloading packages: warning: /var/cache/yum/x86_64/7/packages-microsoft-com-mssql-server/packages/mssql-server-14.0.100.187-1.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY Public key for mssql-server-14.0.100.187-1.x86_64.rpm is not installed mssql-server-14.0.100.187-1.x86_64.rpm | 139 MB 00:07 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-14.0.100.187-1.x86_64 1/1 +-------------------------------------------------------------------+ | Please run /opt/mssql/bin/sqlservr-setup to complete the setup of | | Microsoft(R) SQL Server(R). | +-------------------------------------------------------------------+ Verifying : mssql-server-14.0.100.187-1.x86_64 1/1 Installed: mssql-server.x86_64 0:14.0.100.187-1 Complete!
看到上面的结果表示安装完成
第五步，配置SQL server
[root@sqlserver1 ~]# cd /opt/mssql/bin [root@sqlserver1 bin]# pwd /opt/mssql/bin [root@sqlserver1 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 ln -s '/usr/lib/systemd/system/mssql-server.service' '/etc/systemd/system/multi-user.target.wants/mssql-server.service' ln -s '/usr/lib/systemd/system/mssql-server-telemetry.service' '/etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service' Setup completed successfully. [root@sqlserver1 bin]#
这样就配置成功了
第六步，检查状态
[root@sqlserver1 bin]# systemctl status mssql-server mssql-server.service - Microsoft(R) SQL Server(R) Database Engine Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled) Active: active (running) since Thu 2017-01-05 06:24:24 UTC; 5min ago Main PID: 1629 (sqlservr) CGroup: /system.slice/mssql-server.service 忖1629 /opt/mssql/bin/sqlservr 忖1639 /opt/mssql/bin/sqlservr Jan 05 06:24:34 sqlserver1 sqlservr[1629]: 2017-01-05 06:24:34.22 spid6s .... Jan 05 06:24:34 sqlserver1 sqlservr[1629]: 2017-01-05 06:24:34.86 spid6s .... Jan 05 06:24:34 sqlserver1 sqlservr[1629]: 2017-01-05 06:24:34.87 spid20s .... Jan 05 06:24:34 sqlserver1 sqlservr[1629]: 2017-01-05 06:24:34.87 spid20s .... Jan 05 06:24:34 sqlserver1 sqlservr[1629]: 2017-01-05 06:24:34.92 spid20s .... Jan 05 06:24:35 sqlserver1 sqlservr[1629]: 2017-01-05 06:24:35.01 spid5s .... Jan 05 06:24:36 sqlserver1 sqlservr[1629]: 2017-01-05 06:24:35.99 spid38s ... Jan 05 06:24:37 sqlserver1 systemd[1]: [/usr/lib/systemd/system/mssql-serve...e' Jan 05 06:24:37 sqlserver1 systemd[1]: [/usr/lib/systemd/system/mssql-serve...e' Jan 05 06:24:37 sqlserver1 systemd[1]: [/usr/lib/systemd/system/mssql-serve...e' Hint: Some lines were ellipsized, use -l to show in full.
可以从上图看出SQL server已经在运行，这一步说明SQL server已经完全安装成功了
但是接下来我们还要做一个工作，就是检查，我们需要使用SQL ToolTs，sqlcmd是mssql ODBC的一个工具，可以通过yum的repo直接yum安装
首先下载yum的repo
[root@sqlserver1 bin]# cd /root/ [root@sqlserver1 ~]# wget https://packages.microsoft.com/config/rhel/7/prod.repo --2017-01-05 06:38:11-- 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: 鈖rod.repo? 100%[======================================>] 193 --.-K/s in 0s 2017-01-05 06:38:12 (31.7 MB/s) - 鈖rod.repo?saved [193/193] [root@sqlserver1 ~]# mv prod.repo /etc/yum.repos.d/ [root@sqlserver1 ~]# yum makecache Loaded plugins: fastestmirror, langpacks base | 3.6 kB 00:00 extras | 3.4 kB 00:00 openlogic | 1.3 kB 00:00 packages-microsoft-com-mssql-server | 2.9 kB 00:00 packages-microsoft-com-prod | 2.9 kB 00:00 updates | 3.4 kB 00:00 (1/3): packages-microsoft-com-prod/filelists_db | 3.2 kB 00:00 (2/3): packages-microsoft-com-prod/other_db | 10 kB 00:00 (3/3): packages-microsoft-com-prod/primary_db | 6.7 kB 00:00 Loading mirror speeds from cached hostfile Metadata Cache Created
接下来进行安装
[root@sqlserver1 ~]# yum install mssql-tools Loaded plugins: fastestmirror, langpacks Loading mirror speeds from cached hostfile Resolving Dependencies --> Running transaction check ---> Package mssql-tools.x86_64 0:14.0.1.246-1 will be installed --> Processing Dependency: msodbcsql for package: mssql-tools-14.0.1.246-1.x86_64 --> Running transaction check ---> Package msodbcsql.x86_64 0:13.1.1.0-1 will be installed --> Processing Dependency: unixODBC-utf16 for package: msodbcsql-13.1.1.0-1.x86_64 --> Processing Dependency: libodbcinst.so.2()(64bit) for package: msodbcsql-13.1.1.0-1.x86_64 --> Running transaction check ---> Package unixODBC-utf16.x86_64 0:2.3.1-1 will be installed --> Processing Dependency: libltdl.so.7()(64bit) for package: unixODBC-utf16-2.3.1-1.x86_64 --> Running transaction check ---> Package libtool-ltdl.x86_64 0:2.4.2-21.el7_2 will be installed --> Finished Dependency Resolution Dependencies Resolved ================================================================================ Package Arch Version Repository Size ================================================================================ Installing: mssql-tools x86_64 14.0.1.246-1 packages-microsoft-com-prod 249 k Installing for dependencies: libtool-ltdl x86_64 2.4.2-21.el7_2 base 49 k msodbcsql x86_64 13.1.1.0-1 packages-microsoft-com-prod 3.9 M unixODBC-utf16 x86_64 2.3.1-1 packages-microsoft-com-prod 329 k Transaction Summary ================================================================================ Install 1 Package (+3 Dependent packages) Total download size: 4.5 M Installed size: 4.5 M Is this ok [y/d/N]: y Downloading packages: (1/4): libtool-ltdl-2.4.2-21.el7_2.x86_64.rpm | 49 kB 00:00 (2/4): mssql-tools-14.0.1.246-1.x86_64.rpm | 249 kB 00:00 (3/4): unixODBC-utf16-2.3.1-1.x86_64.rpm | 329 kB 00:00 (4/4): msodbcsql-13.1.1.0-1.x86_64.rpm | 3.9 MB 00:01 -------------------------------------------------------------------------------- Total 3.0 MB/s | 4.5 MB 00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : libtool-ltdl-2.4.2-21.el7_2.x86_64 1/4 Installing : unixODBC-utf16-2.3.1-1.x86_64 2/4 The license terms for this product can be downloaded from http://go.microsoft.com/fwlink/?LinkId=746838 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.1.0-1.x86_64 3/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-14.0.1.246-1.x86_64 4/4 Verifying : libtool-ltdl-2.4.2-21.el7_2.x86_64 1/4 Verifying : msodbcsql-13.1.1.0-1.x86_64 2/4 Verifying : mssql-tools-14.0.1.246-1.x86_64 3/4 Verifying : unixODBC-utf16-2.3.1-1.x86_64 4/4 Installed: mssql-tools.x86_64 0:14.0.1.246-1 Dependency Installed: libtool-ltdl.x86_64 0:2.4.2-21.el7_2 msodbcsql.x86_64 0:13.1.1.0-1 unixODBC-utf16.x86_64 0:2.3.1-1 Complete!
安装成功，接下来测试连接
登陆，输入刚刚的密码，并创建数据库test
[root@sqlserver1 ~]# sqlcmd -S localhost -U SA Password: 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 (5 rows affected)
创建表yytable，并插入数据，最后进行查询
1> create table yytable ( id int, name varchar(20), gender varchar(20), age int) 2> go 1> insert into yytable values (1, 'yangyang','male',20); 2> go (1 rows affected) 1> select * from yytable; 2> go id name gender age ----------- -------------------- -------------------- ----------- 1 yangyang male 20 (1 rows affected) 1>
说明该步骤成功，且SQL server也可以使用了