浮雲雅築: [研究] SQL Server 2017 RTM 安裝、試用 (CentOS 7.4 x64)

[研究] SQL Server 2017 安裝、試用 (CentOS 7.4 x64)

2017-09-28

參考

安裝 SQL Server，並在 Red Hat 上建立資料庫
https://docs.microsoft.com/zh-tw/sql/linux/quickstart-install-connect-red-hat

安裝方法重點彙整

# install
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo
sudo yum install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup

# open port on firewall
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload

# install developer toolkit
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
sudo yum install -y mssql-tools unixODBC-devel
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

實際安裝過程畫面

安裝 mssql-server.repo，準備用 yum 安裝 SQL Server

root@192.168.128.128's password:
Last login: Thu Oct 5 09:02:35 2017
[root@localhost ~]# sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 220 100 220 0 0 618 0 --:--:-- --:--:-- --:--:-- 617
[root@localhost ~]#

yum 中有關 mssql 的套件列表

[root@localhost ~]# yum list | grep mssql

mssql-server.x86_64 14.0.900.75-1 @packages-microsoft-com-mssql-server
dbfs.x86_64 0.1.5-0 packages-microsoft-com-mssql-server
mssql-server-agent.x86_64 14.0.900.75-1 packages-microsoft-com-mssql-server
mssql-server-fts.x86_64 14.0.900.75-1 packages-microsoft-com-mssql-server
mssql-server-ha.x86_64 14.0.900.75-1 packages-microsoft-com-mssql-server
mssql-server-is.x86_64 14.0.900.75-1 packages-microsoft-com-mssql-server
[root@localhost ~]#

設定資料庫

[root@localhost ~]# sudo /opt/mssql/bin/mssql-conf setup
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=852741&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes

Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-7): 2
Enter the SQL Server system administrator password: (設定密碼)
The specified password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters
Enter the SQL Server system administrator password: (設定密碼)
The specified password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, numbers, and symbols.
Enter the SQL Server system administrator password: (設定密碼)
Confirm the SQL Server system administrator password: (設定密碼)
Configuring SQL Server...

sqlservr: This program requires a machine with at least 3250 megabytes of memory.
Initial setup of Microsoft SQL Server failed. Please consult the ERRORLOG
in /var/opt/mssql/log for more information.
[root@localhost ~]#

檢查目前安裝 SQL Server 2017 RTM 這台 VM 只有 2GB RAM

[root@localhost ~]# free -m
total used free shared buff/cache available
Mem: 1823 681 329 9 812 923
Swap: 2047 0 2047
[root@localhost ~]#

(下圖) 小弟用的是 VMware Workstation Pro 14.0.0 for Windows (2017-09-26) 架設的 VM，在 VM 開機狀態下，只能從 2GB 設定到 3072 MB。

(下圖) 只好 Power Off 關機後，設定為 4GB，再 Power On 開機繼續測試

[root@localhost ~]# poweroff

再次設定資料庫

login as: root
root@192.168.128.128's password:
Last login: Thu Oct 5 09:20:39 2017

[root@localhost ~]# /opt/mssql/bin/mssql-conf setup
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=852741&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-7): 2
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

This is an evaluation version. There are [110] days left in the evaluation period.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.
[root@localhost ~]#

當安裝 RC2 或 RTM 版，不論您選取的版本為何會出現下列訊息：
This is an evaluation version.
此訊息並不會反映您所選取的版本。

檢視有關 mssql 的 process

[root@localhost ~]# ps aux | grep mssql
mssql 2940 0.3 0.4 198156 18264 ? Ssl 09:21 0:00 /opt/mssql/bin/sqlservr
mssql 2979 6.4 15.4 1927124 596308 ? Sl 09:21 0:04 /opt/mssql/bin/sqlservr
root 3126 0.0 0.0 112660 972 pts/1 R+ 09:22 0:00 grep --color=auto mssql
[root@localhost ~]#

可執行程式列表

[root@localhost ~]# ls /opt/mssql/bin -al

total 23432
drwxrwxr-x. 2 root root 166 Oct 5 09:05 .
drwxr-xr-x. 4 root root 28 Oct 5 09:05 ..
-rwxr-xr-x. 1 root root 409 Jul 29 02:31 compress-dump.sh
-rwxr-xr-x. 1 root root 13621 Jul 29 02:31 crash-support-functions.sh
-rwxr-xr-x. 1 root root 1266 Jul 29 02:31 generate-sql-dump.sh
-rwxr-xr-x. 1 root root 1652 Jul 29 02:31 handle-crash.sh
-rwxrwxr-x. 1 root root 983 Jul 29 02:30 mssql-conf
-rwxr-xr-x. 1 root root 23032936 Jul 29 02:31 paldumper
-rwxr-xr-x. 1 root root 921832 Jul 29 02:31 sqlservr

測試 mssql-server 啟動狀態

[root@localhost ~]# 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-10-05 09:21:45 CST; 1min 53s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 2940 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─2940 /opt/mssql/bin/sqlservr
└─2979 /opt/mssql/bin/sqlservr

Oct 05 09:21:49 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:49.60 spid6s 0 transactions rolled back in database 'msdb' (4:0). This is an informational message only. No user ac... is required.
Oct 05 09:21:49 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:49.76 spid9s Polybase feature disabled.
Oct 05 09:21:49 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:49.76 spid9s Clearing tempdb database.
Oct 05 09:21:50 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:50.27 spid9s Starting up database 'tempdb'.
Oct 05 09:21:50 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:50.58 spid9s The tempdb database has 1 data file(s).
Oct 05 09:21:50 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:50.60 spid22s The Service Broker endpoint is in disabled or stopped state.
Oct 05 09:21:50 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:50.60 spid22s The Database Mirroring endpoint is in disabled or stopped state.
Oct 05 09:21:50 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:50.88 spid22s Service Broker manager has started.
Oct 05 09:21:50 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:50.96 spid6s Recovery is complete. This is an informational message only. No user action is required.
Oct 05 09:21:53 localhost.localdomain sqlservr[2940]: 2017-10-05 09:21:53.15 spid31s The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output th...
Hint: Some lines were ellipsized, use -l to show in full.
[root@localhost ~]#

設定防火牆

[root@localhost ~]# firewall-cmd --zone=public --add-port=1433/tcp --permanent
success
[root@localhost ~]# firewall-cmd --reload
success
[root@localhost ~]#

安裝 mssql-tools (黃色部分是同一行)

[root@localhost bin]# sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

% Total % Received % Xferd Average Speed Time Time Time Current

Dload Upload Total Spent Left Speed
100 193 100 193 0 0 491 0 --:--:-- --:--:-- --:--:-- 492
[root@localhost ~]#

[root@localhost bin]# yum install -y mssql-tools unixODBC-devel
Loaded plugins: fastestmirror, langpacks
packages-microsoft-com-prod | 2.9 kB 00:00:00
packages-microsoft-com-prod/primary_db | 38 kB 00:00:00
Loading mirror speeds from cached hostfile
* base: linux.cs.nctu.edu.tw
* extras: linux.cs.nctu.edu.tw
* updates: linux.cs.nctu.edu.tw
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:14.0.6.0-1 will be installed
--> Processing Dependency: msodbcsql < 13.2.0.0 for package: mssql-tools-14.0.6.0-1.x86_64
--> Processing Dependency: msodbcsql >= 13.1.0.0 for package: mssql-tools-14.0.6.0-1.x86_64
---> Package unixODBC-devel.x86_64 0:2.3.1-11.el7 will be installed
--> Processing Dependency: unixODBC(x86-64) = 2.3.1-11.el7 for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libtemplate.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libtdsS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libsapdbS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: liboraodbcS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: liboplodbcS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbctxtS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcnnS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcminiS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcdrvcfg2S.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcdrvcfg1S.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbccr.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libnn.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libmimerS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libesoobS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Running transaction check
---> Package msodbcsql.x86_64 0:13.1.9.1-1 will be installed
---> Package unixODBC.x86_64 0:2.3.1-11.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================================================================================================
Package Arch Version Repository Size
===============================================================================================================================================================================================================
Installing:
mssql-tools x86_64 14.0.6.0-1 packages-microsoft-com-prod 249 k
unixODBC-devel x86_64 2.3.1-11.el7 base 55 k
Installing for dependencies:
msodbcsql x86_64 13.1.9.1-1 packages-microsoft-com-prod 4.0 M
unixODBC x86_64 2.3.1-11.el7 base 413 k

Transaction Summary
===============================================================================================================================================================================================================
Install 2 Packages (+2 Dependent packages)

Total download size: 4.7 M
Installed size: 5.6 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/base/packages/unixODBC-devel-2.3.1-11.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for unixODBC-devel-2.3.1-11.el7.x86_64.rpm is not installed
(1/4): unixODBC-devel-2.3.1-11.el7.x86_64.rpm | 55 kB 00:00:00
(2/4): unixODBC-2.3.1-11.el7.x86_64.rpm | 413 kB 00:00:00
(3/4): mssql-tools-14.0.6.0-1.x86_64.rpm | 249 kB 00:00:00
(4/4): msodbcsql-13.1.9.1-1.x86_64.rpm | 4.0 MB 00:00:01
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 3.2 MB/s | 4.7 MB 00:00:01
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Importing GPG key 0xF4A80EB5:
Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
Package : centos-release-7-4.1708.el7.centos.x86_64 (@anaconda)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : unixODBC-2.3.1-11.el7.x86_64 1/4
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.9.1-1.x86_64 2/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.6.0-1.x86_64 3/4
Installing : unixODBC-devel-2.3.1-11.el7.x86_64 4/4
Verifying : msodbcsql-13.1.9.1-1.x86_64 1/4
Verifying : unixODBC-devel-2.3.1-11.el7.x86_64 2/4
Verifying : unixODBC-2.3.1-11.el7.x86_64 3/4
Verifying : mssql-tools-14.0.6.0-1.x86_64 4/4

Installed:
mssql-tools.x86_64 0:14.0.6.0-1 unixODBC-devel.x86_64 0:2.3.1-11.el7

Dependency Installed:
msodbcsql.x86_64 0:13.1.9.1-1 unixODBC.x86_64 0:2.3.1-11.el7

Complete!
[root@localhost ~]#

設定環境變數

[root@localhost ~]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
[root@localhost ~]# echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
[root@localhost ~]# source ~/.bashrc

[root@localhost ~]#

使用sqlcmd連線測試 ( 其中 P@ssw0rd 是前面設定的資料庫密碼)

[root@localhost bin]# sqlcmd -S localhost -U SA -P 'P@ssw0rd'

1> CREATE DATABASE TestDB
2> SELECT Name from sys.Databases
3> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
TestDB

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

(1 rows affected)

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

(1 rows affected)
1> QUIT
[root@localhost bin]#

浮雲雅築

2017年10月5日星期四

[研究] SQL Server 2017 RTM 安裝、試用 (CentOS 7.4 x64)

沒有留言:

張貼留言

2017年10月5日 星期四

[研究] SQL Server 2017 RTM 安裝、試用 (CentOS 7.4 x64)

沒有留言:

張貼留言

2017年10月5日星期四