2017年9月28日 星期四

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

[研究] SQL Server 2017 RC2 安裝、試用 (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@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    516      0 --:--:-- --:--:-- --:--:--   516
[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 ~]# /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 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 ~]#

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


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


再次設定資料庫
[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

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 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...

This is an evaluation version.  There are [117] 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,不論您選取的版本為何會出現下列訊息:
This is an evaluation version.
此訊息並不會反映您所選取的版本。

檢視有關 mssql 的 process
[root@localhost ~]# ps aux | grep mssql
mssql      2826  0.4  0.4 198160 18264 ?        Ssl  13:55   0:00 /opt/mssql/bin/sqlservr
mssql      2865  8.3 15.2 1923168 590928 ?      Sl   13:55   0:04 /opt/mssql/bin/sqlservr
root       3010  0.0  0.0 112660   972 pts/1    R+   13:56   0:00 grep --color=auto mssql
[root@localhost ~]#

可執行程式列表
[root@localhost ~]# ls /opt/mssql/bin -al
total 23432
drwxrwxr-x. 2 root root      166 Sep 28 13:46 .
drwxr-xr-x. 4 root root       28 Sep 28 13:46 ..
-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
[root@localhost ~]#

測試 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-09-28 13:55:22 CST; 5min ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 2826 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ├─2826 /opt/mssql/bin/sqlservr
           └─2865 /opt/mssql/bin/sqlservr

Sep 28 13:55:27 localhost.localdomain sqlservr[2826]: 2017-09-28 13:55:27.21 ...
Sep 28 13:55:27 localhost.localdomain sqlservr[2826]: 2017-09-28 13:55:27.21 ...
Sep 28 13:55:27 localhost.localdomain sqlservr[2826]: 2017-09-28 13:55:27.60 ...
Sep 28 13:55:27 localhost.localdomain sqlservr[2826]: 2017-09-28 13:55:27.92 ...
Sep 28 13:55:27 localhost.localdomain sqlservr[2826]: 2017-09-28 13:55:27.96 ...
Sep 28 13:55:27 localhost.localdomain sqlservr[2826]: 2017-09-28 13:55:27.96 ...
Sep 28 13:55:28 localhost.localdomain sqlservr[2826]: 2017-09-28 13:55:28.09 ...
Sep 28 13:55:28 localhost.localdomain sqlservr[2826]: 2017-09-28 13:55:28.20 ...
Sep 28 14:00:30 localhost.localdomain sqlservr[2826]: 2017-09-28 14:00:30.85 ...
Sep 28 14:00:30 localhost.localdomain sqlservr[2826]: 2017-09-28 14:00:30.90 ...
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    529      0 --:--:-- --:--:-- --:--:--   530
[root@localhost bin]# yum install -y mssql-tools unixODBC-devel
Loaded plugins: fastestmirror, langpacks
packages-microsoft-com-prod                              | 2.9 kB     00:00
packages-microsoft-com-prod/primary_db                     |  37 kB   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
Package unixODBC-devel-2.3.1-11.el7.x86_64 already installed and latest version
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
--> Running transaction check
---> Package msodbcsql.x86_64 0:13.1.9.1-1 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
Installing for dependencies:
 msodbcsql      x86_64    13.1.9.1-1       packages-microsoft-com-prod    4.0 M

Transaction Summary
================================================================================
Install  1 Package (+1 Dependent package)

Total download size: 4.2 M
Installed size: 4.2 M
Downloading packages:
(1/2): mssql-tools-14.0.6.0-1.x86_64.rpm                   | 249 kB   00:00
(2/2): msodbcsql-13.1.9.1-1.x86_64.rpm                     | 4.0 MB   00:01
--------------------------------------------------------------------------------
Total                                              4.0 MB/s | 4.2 MB  00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
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                                  1/2
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                                2/2
  Verifying  : msodbcsql-13.1.9.1-1.x86_64                                  1/2
  Verifying  : mssql-tools-14.0.6.0-1.x86_64                                2/2

Installed:
  mssql-tools.x86_64 0:14.0.6.0-1

Dependency Installed:
  msodbcsql.x86_64 0:13.1.9.1-1

Complete!
[root@localhost bin]#


使用sqlcmd連線測試
[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]#

(待續)

相關

[研究] 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

沒有留言:

張貼留言