2017年10月5日 星期四

[研究] 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
login as: root
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]#


安裝前後,VM 在真實硬碟額外佔用了約 1.12 GB 空間。

(完)

相關

[研究] SQL Server 2017 RTM 安裝、試用 (CentOS 7.4 x64)
http://shaurong.blogspot.com/2017/10/sql-server-2017-rtm-centos-74-x64.html

[研究] SQL Server 2017 Enterprise Core 企業核心版安裝
http://shaurong.blogspot.com/2017/10/sql-server-2017-enterprise-core.html

[研究] SQL Server 2017 Developer 開發版安裝
http://shaurong.blogspot.com/2017/10/sql-server-2017-developer.html

[研究] SQL Server 2017 Enterprise 企業版安裝
http://shaurong.blogspot.com/2017/10/sql-server-2017-enterprise.html

[研究] SQL Server 2017 Evaluation 評估版安裝
http://shaurong.blogspot.com/2017/10/sql-server-2017-evaluation.html

[研究] SQL Server 2017 Express Advanced 安裝
http://shaurong.blogspot.com/2017/10/sql-server-2017-express-advanced.html

[研究] SQL Server 2017 Express Core 安裝
http://shaurong.blogspot.com/2017/10/sql-server-2017-express-core.html

[研究] SQL Server 2017 Express - LocalDB 安裝
http://shaurong.blogspot.com/2017/10/sql-server-2017-express-localdb.html

[研究] SQL Server 2017 繁體中文版下載
http://shaurong.blogspot.com/2017/10/sql-server-2017_4.html

[研究] SQL Server 2017 繁體中文標準版安裝 (失敗)
http://shaurong.blogspot.com/2017/10/sql-server-2017.html

[研究] SQL Server 2017 繁體中文標準版安裝 (成功)
http://shaurong.blogspot.com/2017/10/sql-server-2017_3.html

[研究] SQL Server Management Studio (SSMS) 17.2 安裝 (Windows Server 2016)
http://shaurong.blogspot.com/2017/09/sql-server-management-studio-ssms-172.html

Oracle JAVA JDK JRE 下載
http://www.oracle.com/technetwork/java/javase/downloads/index-jsp-138363.html#javasejdk

[研究] SQL Server 2017 RC2 安裝、試用 (CentOS 7.4 x64)
http://shaurong.blogspot.com/2017/09/sql-server-2017-rc2-centos-74-x64.html

[研究] SQL Server 2017 RC2 安裝 (Windows Server 2016)
http://shaurong.blogspot.com/2017/09/sql-server-2017-rc2-windows-server-2016.html

[研究] SQL Server 2017 RC2 安裝 (Windows 7 x64)
http://shaurong.blogspot.com/2017/09/sql-server-2017-rc2-windows-7-x64.html



沒有留言:

張貼留言