この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
こんにちは、藤本です。
11月16日の「Connect();// 2016」で SQL Server on Linux のパブリックプレビュー版リリースが発表されました。
SQL Server v.Next Public Preview
早速試してみました。
サポートプラットフォーム
- Red Hat Enterprise Linux : 7.2
- Ubuntu : 16.04
- Docker Engine : 1.8+
SQL Server on Linuxのサポートしない機能
Release notes for SQL Server on Linux
SQL Server on Linux ではまだまだサポートされない機能がたくさんあります。
- Database engine
- Full-text Search
- Replication
- Stretch DB
- Polybase
- Distributed Query
- System extended stored procedures (XP_CMDSHELL, etc.)
- Filetable
- High Availability
- Always On Availability Groups
- Database mirroring
- Security
- Active Directory authentication
- Windows Authentication
- Extensible Key Management
- Use of user-provided certificate for SSL or TLS
- Sevices
- SQL Server Agent
- SQL Server Browser
- SQL Server R services
- StreamInsight
- Analysis Services
- Reporting Services
- Integration Services
- Data Quality Services
- Master Data Services
Replication、Always On は GA までにはリリースされるのだろうか。。
試してみた
環境
試してみた OS は CentOS 7.2 です
# cat /etc/redhat-release
CentOS Linux release 7.2.1511 (Core)インストール
RedHat系OSの場合、Microsoft が提供する Yum リポジトリからインストールできます。インストール手順はInstall SQL Server on Red Hat Enterprise Linuxをご参照ください。
まずは Yum リポジトリの定義をダウンロードします。/etc/yum.repos.d/に配置します。
# curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 220 100 220 0 0 494 0 --:--:-- --:--:-- --:--:-- 494
# cat /etc/yum.repos.d/mssql-server.repo
[packages-microsoft-com-mssql-server]
name=packages-microsoft-com-mssql-server
baseurl=https://packages.microsoft.com/rhel/7/mssql-server/
enabled=1
gpgcheck=1
gpgkey=https://packages.microsoft.com/keys/microsoft.ascyum コマンドでインストールします。
# yum install -y mssql-server
Loaded plugins: fastestmirror
packages-microsoft-com-mssql-server | 2.9 kB 00:00:00
packages-microsoft-com-mssql-server/primary_db | 2.4 kB 00:00:00
Loading mirror speeds from cached hostfile
* base: ftp.riken.jp
* extras: ftp.riken.jp
* updates: ftp.riken.jp
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.1.246-6 will be installed
--> Processing Dependency: gdb for package: mssql-server-14.0.1.246-6.x86_64
--> Running transaction check
---> Package gdb.x86_64 0:7.6.1-80.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
========================================================================================================================================================================================================
Package Arch Version Repository Size
========================================================================================================================================================================================================
Installing:
mssql-server x86_64 14.0.1.246-6 packages-microsoft-com-mssql-server 138 M
Installing for dependencies:
gdb x86_64 7.6.1-80.el7 base 2.4 M
Transaction Summary
========================================================================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 140 M
Installed size: 145 M
Downloading packages:
(1/2): gdb-7.6.1-80.el7.x86_64.rpm | 2.4 MB 00:00:00
warning: /var/cache/yum/x86_64/7/packages-microsoft-com-mssql-server/packages/mssql-server-14.0.1.246-6.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY0 MB/s | 140 MB 00:00:00 ETA
Public key for mssql-server-14.0.1.246-6.x86_64.rpm is not installed
(2/2): mssql-server-14.0.1.246-6.x86_64.rpm | 138 MB 00:00:13
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 10 MB/s | 140 MB 00:00:13
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 : gdb-7.6.1-80.el7.x86_64 1/2
Installing : mssql-server-14.0.1.246-6.x86_64 2/2
+-------------------------------------------------------------------+
| Please run /opt/mssql/bin/sqlservr-setup to complete the setup of |
| Microsoft(R) SQL Server(R). |
+-------------------------------------------------------------------+
Verifying : mssql-server-14.0.1.246-6.x86_64 1/2
Verifying : gdb-7.6.1-80.el7.x86_64 2/2
Installed:
mssql-server.x86_64 0:14.0.1.246-6
Dependency Installed:
gdb.x86_64 0:7.6.1-80.el7
Complete!初期セットアップコマンドを実行します。セットアップは対話形式となり、ライセンスの承諾（YES）、管理者アカウントパスワード、サービス起動、自動起動を設定します。
# /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:
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 five sets: uppercase
letters, lowercase letters, numbers, non-alphanumeric characters, or unicode
characters that are categorized as alphabetic characters, but are not
uppercase or lowercase.
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
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /usr/lib/systemd/system/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; vendor preset: disabled)
Active: active (running) since Sat 2016-11-19 01:18:03 UTC; 5min ago
Main PID: 16793 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─16793 /opt/mssql/bin/sqlservr
└─16806 /opt/mssql/bin/sqlservr
Nov 19 01:18:07 localhost.localdomain sqlservr[16793]: 2016-11-19 01:18:07.27 spid6s Clearing tempdb database.
Nov 19 01:18:07 localhost.localdomain sqlservr[16793]: 2016-11-19 01:18:07.62 spid6s Starting up database 'tempdb'.
:起動していますね。
必要に応じて、firewalld のポートを開放します。SQL Server はデフォルト 1433/tcp でアクセスを受けます。
# firewall-cmd --zone=public --add-port=1433/tcp --permanent
success
# firewall-cmd --reload
success
# firewall-cmd --list-all
public (default, active)
interfaces: enp0s3
sources:
services: dhcpv6-client ssh
ports: 1433/tcp
masquerade: no
forward-ports:
icmp-blocks:
rich rules:インストールは以上です。機能が絞られているからか Windows Server へのインストールに比べ非常に早くインストール〜起動までを行うことができます。
接続・操作
SQL Server の操作は Management Studio で操作する印象が強いですが、別途CLIツールも提供しています。こちらも Yum リポジトリからインストールできます。
まず、Yum リポジトリの定義ファイルをダウンロードします。
# curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 193 100 193 0 0 230 0 --:--:-- --:--:-- --:--:-- 230
# cat /etc/yum.repos.d/msprod.repo
[packages-microsoft-com-prod]
name=packages-microsoft-com-prod
baseurl=https://packages.microsoft.com/rhel/7/prod/
enabled=1
gpgcheck=1
gpgkey=https://packages.microsoft.com/keys/microsoft.ascyum コマンドでインストールします。2回ライセンスの承諾でYESを入力する必要があります。
# yum install -y mssql-tools
Loaded plugins: fastestmirror
packages-microsoft-com-prod | 2.9 kB 00:00:00
packages-microsoft-com-prod/primary_db | 4.6 kB 00:00:00
:
========================================================================================================================================================================================================
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 updates 49 k
msodbcsql x86_64 13.0.1.0-1 packages-microsoft-com-prod 3.8 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
Downloading packages:
(1/4): libtool-ltdl-2.4.2-21.el7_2.x86_64.rpm | 49 kB 00:00:00
(2/4): mssql-tools-14.0.1.246-1.x86_64.rpm | 249 kB 00:00:00
(3/4): unixODBC-utf16-2.3.1-1.x86_64.rpm | 329 kB 00:00:00
(4/4): msodbcsql-13.0.1.0-1.x86_64.rpm | 3.8 MB 00:00:01
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 2.8 MB/s | 4.5 MB 00: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.0.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.0.1.0-1.x86_64 2/4
Verifying : unixODBC-utf16-2.3.1-1.x86_64 3/4
Verifying : mssql-tools-14.0.1.246-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.0.1.0-1 unixODBC-utf16.x86_64 0:2.3.1-1
Complete!sqlcmdコマンドを利用して、接続することができます。
# sqlcmd -S localhost -U SA -P '<YourPassword>'
1>接続できました。
いくつかクエリを試してみます。
データベースの確認
1> select name from sys.Databases;
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
(4 rows affected)データベースの作成
1> CREATE DATABASE db;
2> go
1> select name from sys.Databases
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
testdb
(5 rows affected)テーブル作成
1> use testdb
2> go
Changed database context to 'testdb'.
1> CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT)
2> go
1> select name from sys.tables;
2> go
name
--------------------------------------------------------------------------------------------------------------------------------
inventory
(1 rows affected)データ挿入
1> INSERT INTO inventory VALUES (1, 'banana', 150);
2> INSERT INTO inventory VALUES (2, 'orange', 154);
3> go
(1 rows affected)
(1 rows affected)
1> select * from inventory
2> go
id name quantity
----------- -------------------------------------------------- -----------
1 banana 150
2 orange 154
(2 rows affected)もちろん Management Studio からも接続できます。
まとめ
いかがでしたでしょうか？
正直まだ SQL Server on Linux の使い処が見えていませんが、社会人一年目に初めて SQL Server をインストールした時は設定項目の多さに大変苦労しましたが、このインストールの容易さは SQL Server の利用の敷居は下がったように感じました。今回の「Connect();// 2016」では The Linux Foundation への加入や、Visual Studio for Mac のリリースと今までのMicrosoftにない試みがたくさん発表されたように感じました。
3
