在Azure虚拟机上安装SQL server
Azure虽然向用户提供SQL paas服务，但是大多数用户还是习惯在用虚拟机自己搭建SQL server，这样的好处是便于后期最大化的扩展，所以鉴于这些情况，所以觉得有必要写这篇博客。首先，我们要建立一台虚拟机，鉴SQL server对虚拟机内存的要求最低为3GB，所以笔者建议虚拟机选择A3或者A4，当然也可以选择A1的虚拟机，然后再升为A3或者A4，因为本次环境只是测试，这里我就直接建立A3虚拟机，OS为centos7.1
第一步，建立虚拟机，过程省略，有不会的读者自行百度，结果如下
第二步，登陆虚拟机，切换到root用户
$ 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.
password for yangyang:
Sorry, try again.
password for yangyang:
#
第三步，获得yum源，yum的repo文件地址
下载到本地
# 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
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
#
接下来把文件复制到/etc/yum.repos.d目录
# cp mssql-server.repo /etc/yum.repos.d/
更新yum信息
# 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的相关信息
# 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应该是做高可用性的版本，所以我们选择安装第一个
# 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
================================================================================
Install1 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
# cd /opt/mssql/bin
# pwd
/opt/mssql/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
Do you wish to enable SQL Server to start on boot? : 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.
#
这样就配置成功了
第六步，检查状态
# 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: 2017-01-05 06:24:34.22 spid6s ....
Jan 05 06:24:34 sqlserver1 sqlservr: 2017-01-05 06:24:34.86 spid6s ....
Jan 05 06:24:34 sqlserver1 sqlservr: 2017-01-05 06:24:34.87 spid20s ....
Jan 05 06:24:34 sqlserver1 sqlservr: 2017-01-05 06:24:34.87 spid20s ....
Jan 05 06:24:34 sqlserver1 sqlservr: 2017-01-05 06:24:34.92 spid20s ....
Jan 05 06:24:35 sqlserver1 sqlservr: 2017-01-05 06:24:35.01 spid5s ....
Jan 05 06:24:36 sqlserver1 sqlservr: 2017-01-05 06:24:35.99 spid38s ...
Jan 05 06:24:37 sqlserver1 systemd: [/usr/lib/systemd/system/mssql-serve...e'
Jan 05 06:24:37 sqlserver1 systemd: [/usr/lib/systemd/system/mssql-serve...e'
Jan 05 06:24:37 sqlserver1 systemd: [/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
# cd /root/
#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
Saving to: 鈖rod.repo?
100%[======================================>] 193 --.-K/s in 0s
2017-01-05 06:38:12 (31.7 MB/s) - 鈖rod.repo?saved
# mv prod.repo /etc/yum.repos.d/
# 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
接下来进行安装
#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
================================================================================
Install1 Package (+3 Dependent packages)
Total download size: 4.5 M
Installed size: 4.5 M
Is this ok : 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 MB00: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
# 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也可以使用了
页:
[1]