不能复制文字,会被认为是抄袭。纯手敲
昨天有个任务, 迁移 SQL Server 2008 到 2022. 本来以为版本跨度太大, 会不支持, 还准备 2017 想做个过渡, 没想到 SQL Server 2008 的备份也可以被 SQL Server 2022 识别, 备份恢复很丝滑。 顺利。
但 SQL Server 后面已经不再继承管理工具 SSMS 了, SSMS 已经是一个独立的组件,单独开发发布。 于是去官网下载了一份,在这过程中发现官网对 SQL Server on Linux 已经更新了。
以前安装过 SQL Server 2017 on Linux , SQL Server 2019 on Linux , SQL Server 14.0 on Linux, 有空再贴上来, 今天先贴下 SQL Server 2022 on Linux 安装过程。
SQL Server 2022 on RHEL8.8 安装
环境: RHEL 8.8 最小化安装。 配置了本地yum 源。
先决条件
必须拥有 RHEL 8.0 - 8.6 计算机(内存至少为 2 GB)。经测试, 我的环境是 RHEL 8.8 也可以安装。
安装 SQL Server
#配置源
curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2022.repo
#安装包
yum install -y mssql-server
#初始化配置等 按照提示设置 SA 密码并选择版本。我选的是 Express
/opt/mssql/bin/mssql-conf setup
#查看状态
systemctl status mssql-server
#防火墙允许通过端口
firewall-cmd --zone=public --add-port=1433/tcp --permanent
firewall-cmd --reload
安装 SQL Server 命令行工具
#配置源
curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
#如果安装了早期版本的 mssql-tools,请删除所有旧的 unixODBC 包
yum remove unixODBC-utf16 unixODBC-utf16-devel
#安装 mssql-tools
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
测试本地连接
#本地连接测试
sqlcmd -S localhost -U sa -P '<YourPassword>'
-- 新建数据库
SELECT @@VERSION
CREATE DATABASE TestDB;
SELECT Name from sys.databases;
-- 插入数据
USE TestDB;
CREATE TABLE dbo.Inventory (
id INT,
name NVARCHAR(50),
quantity INT,
PRIMARY KEY (id)
);
INSERT INTO dbo.Inventory VALUES (1, 'banana', 150);
INSERT INTO dbo.Inventory VALUES (2, 'orange', 154);
GO
SELECT * FROM dbo.Inventory WHERE quantity > 152;
GO
-- 附加数据库 将windows上的数据库deattch 后拿过来可以顺利attach
-- 注意上传文件后别忘了改属主, 否则打开是只读的哦
sp_attach_db 'psdata','/var/opt/mssql/data/psdata.mdf','/var/opt/mssql/data/psdata.ldf'
go
以下是实际操练过程(带输出),供参考
[root@localhost ~]# curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2022.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 232 100 232 0 0 299 0 --:--:-- --:--:-- --:--:-- 298
[root@localhost ~]# yum install -y mssql-server
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered with an entitlement server. You can use subscription-manager to register.
base 89 MB/s | 2.4 MB 00:00
appstream 93 MB/s | 8.1 MB 00:00
packages-microsoft-com-mssql-server-2022 8.2 kB/s | 12 kB 00:01
Dependencies resolved.
============================================================================================================================================
Package Architecture Version Repository Size
============================================================================================================================================
Installing:
mssql-server x86_64 16.0.4045.3-1 packages-microsoft-com-mssql-server-2022 252 M
Installing dependencies:
binutils x86_64 2.30-119.el8 base 5.8 M
bzip2 x86_64 1.0.6-26.el8 base 60 k
cpp x86_64 8.5.0-18.el8 appstream 10 M
cyrus-sasl x86_64 2.1.27-6.el8_5 base 96 k
cyrus-sasl-gssapi x86_64 2.1.27-6.el8_5 base 50 k
gc x86_64 7.6.4-3.el8 appstream 109 k
gcc x86_64 8.5.0-18.el8 appstream 23 M
gdb x86_64 8.2-19.el8 appstream 299 k
gdb-headless x86_64 8.2-19.el8 appstream 3.7 M
glibc-devel x86_64 2.28-225.el8 base 83 k
glibc-headers x86_64 2.28-225.el8 base 488 k
guile x86_64 5:2.0.14-7.el8 appstream 3.5 M
isl x86_64 0.16.1-6.el8 appstream 841 k
kernel-headers x86_64 4.18.0-477.10.1.el8_8 base 11 M
libatomic x86_64 8.5.0-18.el8 base 25 k
libatomic_ops x86_64 7.6.2-3.el8 appstream 38 k
libbabeltrace x86_64 1.5.4-4.el8 base 200 k
libipt x86_64 1.6.1-8.el8 appstream 50 k
libmpc x86_64 1.1.0-9.1.el8 appstream 61 k
libxcrypt-devel x86_64 4.1.1-6.el8 base 25 k
python3-pip noarch 9.0.3-22.el8 appstream 20 k
python3-setuptools noarch 39.2.0-7.el8 base 163 k
python36 x86_64 3.6.8-38.module+el8.5.0+12207+5c5719bc appstream 19 k
Installing weak dependencies:
gcc-gdb-plugin x86_64 8.5.0-18.el8 appstream 120 k
Enabling module streams:
python36 3.6
Transaction Summary
============================================================================================================================================
Install 25 Packages
Total size: 312 M
Total download size: 252 M
Installed size: 1.3 G
Downloading Packages:
mssql-server-16.0.4045.3-1.x86_64.rpm 524 kB/s | 252 MB 08:11
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 524 kB/s | 252 MB 08:11
packages-microsoft-com-mssql-server-2022 2.0 kB/s | 983 B 00:00
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
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : libmpc-1.1.0-9.1.el8.x86_64 1/25
Installing : cpp-8.5.0-18.el8.x86_64 2/25
Running scriptlet: cpp-8.5.0-18.el8.x86_64 2/25
Installing : libipt-1.6.1-8.el8.x86_64 3/25
Installing : libatomic_ops-7.6.2-3.el8.x86_64 4/25
Installing : gc-7.6.4-3.el8.x86_64 5/25
Installing : guile-5:2.0.14-7.el8.x86_64 6/25
Running scriptlet: guile-5:2.0.14-7.el8.x86_64 6/25
Installing : isl-0.16.1-6.el8.x86_64 7/25
Running scriptlet: isl-0.16.1-6.el8.x86_64 7/25
Installing : python3-setuptools-39.2.0-7.el8.noarch 8/25
Installing : python3-pip-9.0.3-22.el8.noarch 9/25
Installing : python36-3.6.8-38.module+el8.5.0+12207+5c5719bc.x86_64 10/25
Running scriptlet: python36-3.6.8-38.module+el8.5.0+12207+5c5719bc.x86_64 10/25
Installing : libbabeltrace-1.5.4-4.el8.x86_64 11/25
Running scriptlet: libbabeltrace-1.5.4-4.el8.x86_64 11/25
Installing : gdb-headless-8.2-19.el8.x86_64 12/25
Installing : libatomic-8.5.0-18.el8.x86_64 13/25
Running scriptlet: libatomic-8.5.0-18.el8.x86_64 13/25
Installing : kernel-headers-4.18.0-477.10.1.el8_8.x86_64 14/25
Running scriptlet: glibc-headers-2.28-225.el8.x86_64 15/25
Installing : glibc-headers-2.28-225.el8.x86_64 15/25
Installing : libxcrypt-devel-4.1.1-6.el8.x86_64 16/25
Installing : glibc-devel-2.28-225.el8.x86_64 17/25
Running scriptlet: glibc-devel-2.28-225.el8.x86_64 17/25
Installing : cyrus-sasl-gssapi-2.1.27-6.el8_5.x86_64 18/25
Running scriptlet: cyrus-sasl-2.1.27-6.el8_5.x86_64 19/25
Installing : cyrus-sasl-2.1.27-6.el8_5.x86_64 19/25
Running scriptlet: cyrus-sasl-2.1.27-6.el8_5.x86_64 19/25
Installing : bzip2-1.0.6-26.el8.x86_64 20/25
Installing : binutils-2.30-119.el8.x86_64 21/25
Running scriptlet: binutils-2.30-119.el8.x86_64 21/25
Installing : gcc-8.5.0-18.el8.x86_64 22/25
Running scriptlet: gcc-8.5.0-18.el8.x86_64 22/25
Installing : gcc-gdb-plugin-8.5.0-18.el8.x86_64 23/25
Running scriptlet: gcc-gdb-plugin-8.5.0-18.el8.x86_64 23/25
Installing : gdb-8.2-19.el8.x86_64 24/25
Running scriptlet: mssql-server-16.0.4045.3-1.x86_64 25/25
Installing : mssql-server-16.0.4045.3-1.x86_64 25/25
Running scriptlet: mssql-server-16.0.4045.3-1.x86_64 25/25
+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+
Running scriptlet: guile-5:2.0.14-7.el8.x86_64 25/25
Running scriptlet: mssql-server-16.0.4045.3-1.x86_64 25/25
Verifying : binutils-2.30-119.el8.x86_64 1/25
Verifying : bzip2-1.0.6-26.el8.x86_64 2/25
Verifying : cyrus-sasl-2.1.27-6.el8_5.x86_64 3/25
Verifying : cyrus-sasl-gssapi-2.1.27-6.el8_5.x86_64 4/25
Verifying : glibc-devel-2.28-225.el8.x86_64 5/25
Verifying : glibc-headers-2.28-225.el8.x86_64 6/25
Verifying : kernel-headers-4.18.0-477.10.1.el8_8.x86_64 7/25
Verifying : libatomic-8.5.0-18.el8.x86_64 8/25
Verifying : libbabeltrace-1.5.4-4.el8.x86_64 9/25
Verifying : libxcrypt-devel-4.1.1-6.el8.x86_64 10/25
Verifying : python3-setuptools-39.2.0-7.el8.noarch 11/25
Verifying : cpp-8.5.0-18.el8.x86_64 12/25
Verifying : gc-7.6.4-3.el8.x86_64 13/25
Verifying : gcc-8.5.0-18.el8.x86_64 14/25
Verifying : gcc-gdb-plugin-8.5.0-18.el8.x86_64 15/25
Verifying : gdb-8.2-19.el8.x86_64 16/25
Verifying : gdb-headless-8.2-19.el8.x86_64 17/25
Verifying : guile-5:2.0.14-7.el8.x86_64 18/25
Verifying : isl-0.16.1-6.el8.x86_64 19/25
Verifying : libatomic_ops-7.6.2-3.el8.x86_64 20/25
Verifying : libipt-1.6.1-8.el8.x86_64 21/25
Verifying : libmpc-1.1.0-9.1.el8.x86_64 22/25
Verifying : python3-pip-9.0.3-22.el8.noarch 23/25
Verifying : python36-3.6.8-38.module+el8.5.0+12207+5c5719bc.x86_64 24/25
Verifying : mssql-server-16.0.4045.3-1.x86_64 25/25
Installed products updated.
Installed:
binutils-2.30-119.el8.x86_64 bzip2-1.0.6-26.el8.x86_64 cpp-8.5.0-18.el8.x86_64
cyrus-sasl-2.1.27-6.el8_5.x86_64 cyrus-sasl-gssapi-2.1.27-6.el8_5.x86_64 gc-7.6.4-3.el8.x86_64
gcc-8.5.0-18.el8.x86_64 gcc-gdb-plugin-8.5.0-18.el8.x86_64 gdb-8.2-19.el8.x86_64
gdb-headless-8.2-19.el8.x86_64 glibc-devel-2.28-225.el8.x86_64 glibc-headers-2.28-225.el8.x86_64
guile-5:2.0.14-7.el8.x86_64 isl-0.16.1-6.el8.x86_64 kernel-headers-4.18.0-477.10.1.el8_8.x86_64
libatomic-8.5.0-18.el8.x86_64 libatomic_ops-7.6.2-3.el8.x86_64 libbabeltrace-1.5.4-4.el8.x86_64
libipt-1.6.1-8.el8.x86_64 libmpc-1.1.0-9.1.el8.x86_64 libxcrypt-devel-4.1.1-6.el8.x86_64
mssql-server-16.0.4045.3-1.x86_64 python3-pip-9.0.3-22.el8.noarch python3-setuptools-39.2.0-7.el8.noarch
python36-3.6.8-38.module+el8.5.0+12207+5c5719bc.x86_64
Complete!
[root@localhost ~]# /opt/mssql/bin/mssql-conf setup
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) - CPU core utilization restricted to 20 physical/40 hyperthreaded
7) Enterprise Core (PAID) - CPU core utilization up to Operating System Maximum
8) I bought a license through a retail sales channel and have a product key to enter.
9) Standard (Billed through Azure) - Use pay-as-you-go billing through Azure.
10) Enterprise Core (Billed through Azure) - Use pay-as-you-go billing through Azure.
Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=2109348&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.
By choosing an edition billed Pay-As-You-Go through Azure, you are verifying
that the server and SQL Server will be connected to Azure by installing the
management agent and Azure extension for SQL Server.
Enter your edition(1-10): 3
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from: https://aka.ms/useterms
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
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...
The licensing PID was successfully processed. The new edition is [Express Edition].
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink /etc/systemd/system/multi-user.target.wants/mssql-server.service → /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.
[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 Fri 2023-06-16 10:59:50 CST; 46s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 10956 (sqlservr)
Tasks: 144
Memory: 591.2M
CGroup: /system.slice/mssql-server.service
├─10956 /opt/mssql/bin/sqlservr
└─10977 /opt/mssql/bin/sqlservr
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [145B blob data]
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [158B blob data]
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [155B blob data]
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [61B blob data]
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [193B blob data]
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [66B blob data]
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [96B blob data]
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [100B blob data]
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [71B blob data]
Jun 16 10:59:56 localhost.localdomain sqlservr[10977]: [124B blob data]
[root@localhost ~]# firewall-cmd --zone=public --add-port=1433/tcp --permanent
success
[root@localhost ~]# firewall-cmd --reload
success
[root@localhost ~]# curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 192 100 192 0 0 311 0 --:--:-- --:--:-- --:--:-- 312
[root@localhost ~]# yum remove unixODBC-utf16 unixODBC-utf16-devel
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered with an entitlement server. You can use subscription-manager to register.
No match for argument: unixODBC-utf16
No match for argument: unixODBC-utf16-devel
No packages marked for removal.
Dependencies resolved.
Nothing to do.
Complete!
[root@localhost ~]# yum install -y mssql-tools unixODBC-devel
Updating Subscription Management repositories.
Unable to read consumer identity
This system is not registered with an entitlement server. You can use subscription-manager to register.
packages-microsoft-com-prod 257 kB/s | 7.3 MB 00:28
Last metadata expiration check: 0:00:25 ago on Fri 16 Jun 2023 11:01:03 AM CST.
Dependencies resolved.
===================================================================================================================================================================
Package Architecture Version Repository Size
===================================================================================================================================================================
Installing:
mssql-tools x86_64 17.10.1.1-1 packages-microsoft-com-prod 291 k
unixODBC-devel x86_64 2.3.11-1.rh packages-microsoft-com-prod 53 k
Installing dependencies:
msodbcsql17 x86_64 17.10.4.1-1 packages-microsoft-com-prod 908 k
unixODBC x86_64 2.3.11-1.rh packages-microsoft-com-prod 274 k
Transaction Summary
===================================================================================================================================================================
Install 4 Packages
Total download size: 1.5 M
Installed size: 3.8 M
Downloading Packages:
(1/4): unixODBC-2.3.11-1.rh.x86_64.rpm 162 kB/s | 274 kB 00:01
(2/4): msodbcsql17-17.10.4.1-1.x86_64.rpm 260 kB/s | 908 kB 00:03
(3/4): unixODBC-devel-2.3.11-1.rh.x86_64.rpm 26 kB/s | 53 kB 00:02
(4/4): mssql-tools-17.10.1.1-1.x86_64.rpm 35 kB/s | 291 kB 00:08
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 182 kB/s | 1.5 MB 00:08
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : unixODBC-2.3.11-1.rh.x86_64 1/4
Running scriptlet: unixODBC-2.3.11-1.rh.x86_64 1/4
Running scriptlet: msodbcsql17-17.10.4.1-1.x86_64 2/4
The license terms for this product can be downloaded from
https://aka.ms/odbc17eula and found in
/usr/share/doc/msodbcsql17/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 : msodbcsql17-17.10.4.1-1.x86_64 2/4
Running scriptlet: msodbcsql17-17.10.4.1-1.x86_64 2/4
odbcinst: Driver installed. Usage count increased to 1.
Target directory is /etc
Running scriptlet: mssql-tools-17.10.1.1-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-17.10.1.1-1.x86_64 3/4
Installing : unixODBC-devel-2.3.11-1.rh.x86_64 4/4
Running scriptlet: msodbcsql17-17.10.4.1-1.x86_64 4/4
Running scriptlet: unixODBC-devel-2.3.11-1.rh.x86_64 4/4
Verifying : msodbcsql17-17.10.4.1-1.x86_64 1/4
Verifying : mssql-tools-17.10.1.1-1.x86_64 2/4
Verifying : unixODBC-2.3.11-1.rh.x86_64 3/4
Verifying : unixODBC-devel-2.3.11-1.rh.x86_64 4/4
Installed products updated.
Installed:
msodbcsql17-17.10.4.1-1.x86_64 mssql-tools-17.10.1.1-1.x86_64 unixODBC-2.3.11-1.rh.x86_64 unixODBC-devel-2.3.11-1.rh.x86_64
Complete!
[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 -S localhost -U sa -P ********
1> SELECT @@VERSION
2> go
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2022 (RTM-CU5) (KB5026806) - 16.0.4045.3 (X64)
May 26 2023 12:52:08
Copyright (C) 2022 Microsoft Corporation
Express Edition (64-bit) on Linux (Red Hat Enterprise Linux 8.8 (Ootpa)) <X64>
(1 rows affected)
1> CREATE DATABASE TestDB
2> go
1> SELECT Name from sys.databases
2> go
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
TestDB
(5 rows affected)
1> USE TestDB
2>
3> go
Changed database context to 'TestDB'.
2> CREATE TABLE dbo.Inventory (id INT, name NVARCHAR(50), quantity INT, PRIMARY KEY (id));
3> go
1> INSERT INTO dbo.Inventory VALUES (1, 'banana', 150);
2>
3> INSERT INTO dbo.Inventory VALUES (2, 'orange', 154);
4> go
(1 rows affected)
(1 rows affected)
1> SELECT * FROM dbo.Inventory;
2> go
id name quantity
----------- -------------------------------------------------- -----------
1 banana 150
2 orange 154
(2 rows affected)
1> sp_attach_db 'psdata','/var/opt/mssql/data/psdata.mdf','/var/opt/mssql/data/psdata.ldf'
2> go
1> use psdata
2> go
Changed database context to 'psdata'.