在 openSuSE 15.5 上安装 SQL Server 2022

不能复制文字，会被认为是抄袭。纯手敲

昨天有个任务， 迁移 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 openSuSE 安装

环境: openSuSE 15.5 最小化安装。 配置了国内 源。

先决条件

必须拥有 SLES v15 (SP1 - SP4) 计算机（内存至少为 2 GB）。 文件系统必须是 XFS 或 EXT4 。 其他文件系统（如 BTRFS）均不受支持 。

我的环境 是 openSuSE 15.5 ， btrfs 文件系统， 经测试，也是可以的

安装 SQL Server

#配置源
zypper addrepo -fc https://packages.microsoft.com/config/sles/15/mssql-server-2022.repo
zypper --gpg-auto-import-keys refresh
rpm --import https://packages.microsoft.com/keys/microsoft.asc

#安装包
zypper 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 命令行工具

#配置源
zypper addrepo -fc https://packages.microsoft.com/config/sles/15/prod.repo
zypper --gpg-auto-import-keys refresh

#安装 mssql-tools
zypper 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

以下是实际操练过程（带输出)，供参考

localhost:~ # zypper addrepo -fc https://packages.microsoft.com/config/sles/15/mssql-server-2022.repo
Adding repository 'packages-microsoft-com-mssql-server-2022' ................................................................................................[done]
Repository 'packages-microsoft-com-mssql-server-2022' successfully added

URI         : https://packages.microsoft.com/sles/15/mssql-server-2022/
Enabled     : Yes
GPG Check   : Yes
Autorefresh : Yes
Priority    : 99 (default priority)

Repository priorities are without effect. All enabled repositories share the same priority.
localhost:~ # zypper --gpg-auto-import-keys refresh
Repository 'Multilingualization Project (15.5)' is up to date.                                                                                                     
Repository 'openSUSE-Aliyun-NON-OSS' is up to date.                                                                                                                
Repository 'openSUSE-Aliyun-OSS' is up to date.                                                                                                                    
Repository 'openSUSE-Aliyun-Packman' is up to date.                                                                                                                
Repository 'openSUSE-Aliyun-UPDATE-NON-OSS' is up to date.                                                                                                         
Repository 'openSUSE-Aliyun-UPDATE-OSS' is up to date.                                                                                                             

Automatically importing the following key:

  Repository:       packages-microsoft-com-mssql-server-2022
  Key Fingerprint:  BC52 8686 B50D 79E3 39D3 721C EB3E 94AD BE12 29CF
  Key Name:         Microsoft (Release signing) <gpgsecurity@microsoft.com>
  Key Algorithm:    RSA 2048
  Key Created:      Thu Oct 29 07:21:48 2015
  Key Expires:      (does not expire)
  Rpm Name:         gpg-pubkey-be1229cf-5631588c



    Note: A GPG pubkey is clearly identified by its fingerprint. Do not rely on the key's name. If
    you are not sure whether the presented key is authentic, ask the repository provider or check
    their web site. Many providers maintain a web page showing the fingerprints of the GPG keys they
    are using.
Retrieving repository 'packages-microsoft-com-mssql-server-2022' metadata ...................................................................................[done]
Building repository 'packages-microsoft-com-mssql-server-2022' cache ........................................................................................[done]
All repositories have been refreshed.
localhost:~ # rpm --import https://packages.microsoft.com/keys/microsoft.asc
localhost:~ # zypper install -y mssql-server
Loading repository data...
Reading installed packages...
Resolving package dependencies...

The following recommended package was automatically selected:
  pmdk

The following 14 NEW packages are going to be installed:
  babeltrace ctags gdb libatomic1 libboost_regex1_66_0 libdebuginfod1 libmpfr6 libndctl6 libpmem1 libsource-highlight4 libsss_nss_idmap0 libstdc++6-pp mssql-server
  pmdk

14 new packages to install.
Overall download size: 257.4 MiB. Already cached: 0 B. After the operation, additional 1.2 GiB will be used.
Continue? [y/n/v/...? shows all options] (y): y
Retrieving: babeltrace-1.5.8-1.30.x86_64 (openSUSE-Aliyun-OSS)                                                                                (1/14), 203.7 KiB    
Retrieving: babeltrace-1.5.8-1.30.x86_64.rpm ...................................................................................................[done (15.3 KiB/s)]
Retrieving: ctags-5.8-150000.3.3.1.x86_64 (openSUSE-Aliyun-OSS)                                                                               (2/14), 141.6 KiB    
Retrieving: ctags-5.8-150000.3.3.1.x86_64.rpm ...............................................................................................................[done]
Retrieving: libatomic1-12.2.1+git416-150000.1.7.1.x86_64 (openSUSE-Aliyun-OSS)                                                                (3/14),  22.5 KiB    
Retrieving: libatomic1-12.2.1+git416-150000.1.7.1.x86_64.rpm ................................................................................................[done]
Retrieving: libboost_regex1_66_0-1.66.0-12.3.1.x86_64 (openSUSE-Aliyun-OSS)                                                                   (4/14), 266.3 KiB    
Retrieving: libboost_regex1_66_0-1.66.0-12.3.1.x86_64.rpm ...................................................................................................[done]
Retrieving: libdebuginfod1-0.185-150400.5.3.1.x86_64 (openSUSE-Aliyun-OSS)                                                                    (5/14),  24.5 KiB    
Retrieving: libdebuginfod1-0.185-150400.5.3.1.x86_64.rpm ....................................................................................................[done]
Retrieving: libmpfr6-4.0.2-3.3.1.x86_64 (openSUSE-Aliyun-OSS)                                                                                 (6/14), 216.1 KiB    
Retrieving: libmpfr6-4.0.2-3.3.1.x86_64.rpm .................................................................................................................[done]
Retrieving: libndctl6-75-150500.2.2.x86_64 (openSUSE-Aliyun-OSS)                                                                              (7/14), 149.1 KiB    
Retrieving: libndctl6-75-150500.2.2.x86_64.rpm .................................................................................................[done (95.4 KiB/s)]
Retrieving: libsss_nss_idmap0-2.5.2-150500.8.3.x86_64 (openSUSE-Aliyun-OSS)                                                                   (8/14),  69.1 KiB    
Retrieving: libsss_nss_idmap0-2.5.2-150500.8.3.x86_64.rpm ...................................................................................................[done]
Retrieving: libstdc++6-pp-12.2.1+git416-150000.1.7.1.x86_64 (openSUSE-Aliyun-OSS)                                                             (9/14),  35.2 KiB    
Retrieving: libstdc++6-pp-12.2.1+git416-150000.1.7.1.x86_64.rpm .............................................................................................[done]
Retrieving: pmdk-1.11.1-150400.1.10.x86_64 (openSUSE-Aliyun-OSS)                                                                             (10/14),  37.3 KiB    
Retrieving: pmdk-1.11.1-150400.1.10.x86_64.rpm ..............................................................................................................[done]
Retrieving: libsource-highlight4-3.1.8-150000.3.4.1.x86_64 (openSUSE-Aliyun-OSS)                                                             (11/14), 237.1 KiB    
Retrieving: libsource-highlight4-3.1.8-150000.3.4.1.x86_64.rpm ..............................................................................................[done]
Retrieving: libpmem1-1.11.1-150400.1.10.x86_64 (openSUSE-Aliyun-OSS)                                                                         (12/14),  88.1 KiB    
Retrieving: libpmem1-1.11.1-150400.1.10.x86_64.rpm ..........................................................................................................[done]
Retrieving: gdb-12.1-150400.15.6.1.x86_64 (openSUSE-Aliyun-OSS)                                                                              (13/14),   4.3 MiB    
Retrieving: gdb-12.1-150400.15.6.1.x86_64.rpm ...................................................................................................[done (3.9 MiB/s)]
Retrieving: mssql-server-16.0.4045.3-1.x86_64 (packages-microsoft-com-mssql-server-2022)                                                     (14/14), 251.6 MiB    
Retrieving: mssql-server-16.0.4045.3-1.x86_64.rpm .............................................................................................[done (494.3 KiB/s)]

Checking for file conflicts: ................................................................................................................................[done]
( 1/14) Installing: babeltrace-1.5.8-1.30.x86_64 ............................................................................................................[done]
update-alternatives: using /usr/bin/ctags-exuberant to provide /usr/bin/ctags (ctags) in auto mode
( 2/14) Installing: ctags-5.8-150000.3.3.1.x86_64 ...........................................................................................................[done]
( 3/14) Installing: libatomic1-12.2.1+git416-150000.1.7.1.x86_64 ............................................................................................[done]
( 4/14) Installing: libboost_regex1_66_0-1.66.0-12.3.1.x86_64 ...............................................................................................[done]
( 5/14) Installing: libdebuginfod1-0.185-150400.5.3.1.x86_64 ................................................................................................[done]
( 6/14) Installing: libmpfr6-4.0.2-3.3.1.x86_64 .............................................................................................................[done]
( 7/14) Installing: libndctl6-75-150500.2.2.x86_64 ..........................................................................................................[done]
( 8/14) Installing: libsss_nss_idmap0-2.5.2-150500.8.3.x86_64 ...............................................................................................[done]
( 9/14) Installing: libstdc++6-pp-12.2.1+git416-150000.1.7.1.x86_64 .........................................................................................[done]
(10/14) Installing: pmdk-1.11.1-150400.1.10.x86_64 ..........................................................................................................[done]
(11/14) Installing: libsource-highlight4-3.1.8-150000.3.4.1.x86_64 ..........................................................................................[done]
(12/14) Installing: libpmem1-1.11.1-150400.1.10.x86_64 ......................................................................................................[done]
(13/14) Installing: gdb-12.1-150400.15.6.1.x86_64 ...........................................................................................................[done]
+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+
(14/14) Installing: mssql-server-16.0.4045.3-1.x86_64 .......................................................................................................[done]

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.
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 11:08:08 CST; 38s ago
       Docs: https://docs.microsoft.com/en-us/sql/linux
   Main PID: 14007 (sqlservr)
      Tasks: 42
     CGroup: /system.slice/mssql-server.service
             ├─ 14007 /opt/mssql/bin/sqlservr
             └─ 14026 /opt/mssql/bin/sqlservr
Jun 16 11:08:08 localhost.localdomain systemd[1]: Started Microsoft SQL Server Database Engine.
localhost:~ # firewall-cmd --zone=public --add-port=1433/tcp --permanent 
success
localhost:~ # firewall-cmd --reload
success

localhost:~ # zypper addrepo -fc https://packages.microsoft.com/config/sles/15/prod.repo
Adding repository 'packages-microsoft-com-prod' .............................................................................................................[done]
Repository 'packages-microsoft-com-prod' successfully added

URI         : https://packages.microsoft.com/sles/15/prod/
Enabled     : Yes
GPG Check   : Yes
Autorefresh : Yes
Priority    : 99 (default priority)

Repository priorities are without effect. All enabled repositories share the same priority.
localhost:~ # zypper --gpg-auto-import-keys refresh
Repository 'Multilingualization Project (15.5)' is up to date.                                                                                                     
Repository 'openSUSE-Aliyun-NON-OSS' is up to date.                                                                                                                
Repository 'openSUSE-Aliyun-OSS' is up to date.                                                                                                                    
Repository 'openSUSE-Aliyun-Packman' is up to date.                                                                                                                
Repository 'openSUSE-Aliyun-UPDATE-NON-OSS' is up to date.                                                                                                         
Repository 'openSUSE-Aliyun-UPDATE-OSS' is up to date.                                                                                                             
Repository 'packages-microsoft-com-mssql-server-2022' is up to date.                                                                                               
Retrieving repository 'packages-microsoft-com-prod' metadata ................................................................................................[done]
Building repository 'packages-microsoft-com-prod' cache .....................................................................................................[done]
All repositories have been refreshed.
localhost:~ # zypper install -y mssql-tools unixODBC-devel
Loading repository data...
Reading installed packages...
Resolving package dependencies...

The following 4 NEW packages are going to be installed:
  msodbcsql17 mssql-tools unixODBC unixODBC-devel

4 new packages to install.
Overall download size: 1.5 MiB. Already cached: 0 B. After the operation, additional 3.8 MiB will be used.
Continue? [y/n/v/...? shows all options] (y): y
Retrieving: unixODBC-2.3.11-1.suse.x86_64 (packages-microsoft-com-prod)                                                                        (1/4), 276.0 KiB    
Retrieving: unixODBC-2.3.11-1.suse.x86_64.rpm ..................................................................................................[done (90.4 KiB/s)]
Retrieving: unixODBC-devel-2.3.11-1.suse.x86_64 (packages-microsoft-com-prod)                                                                  (2/4),  53.1 KiB    
Retrieving: unixODBC-devel-2.3.11-1.suse.x86_64.rpm ............................................................................................[done (15.6 KiB/s)]
Retrieving: msodbcsql17-17.10.4.1-1.x86_64 (packages-microsoft-com-prod)                                                                       (3/4), 909.0 KiB    
Retrieving: msodbcsql17-17.10.4.1-1.x86_64.rpm ................................................................................................[done (863.2 KiB/s)]
Retrieving: mssql-tools-17.10.1.1-1.x86_64 (packages-microsoft-com-prod)                                                                       (4/4), 292.2 KiB    
Retrieving: mssql-tools-17.10.1.1-1.x86_64.rpm .................................................................................................[done (90.4 KiB/s)]

Checking for file conflicts: ................................................................................................................................[done]
(1/4) Installing: unixODBC-2.3.11-1.suse.x86_64 .............................................................................................................[done]
(2/4) Installing: unixODBC-devel-2.3.11-1.suse.x86_64 .......................................................................................................[done]
(3/4) Installing: msodbcsql17-17.10.4.1-1.x86_64 ---------------------------------------------------------------------------------------------------------------[-]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
odbcinst: Driver installed. Usage count increased to 1. 
    Target directory is /etc/unixODBC
(3/4) Installing: msodbcsql17-17.10.4.1-1.x86_64 ............................................................................................................[done]
(4/4) Installing: mssql-tools-17.10.1.1-1.x86_64 ---------------------------------------------------------------------------------------------------------------[|]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
(4/4) Installing: mssql-tools-17.10.1.1-1.x86_64 ............................................................................................................[done]
Executing %posttrans scripts ................................................................................................................................[done]
localhost:~ # echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
localhost:~ # echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
localhost:~ # source ~/.bashrc

localhost:~ # sqlcmd -S localhost -U sa -P '<YourPassword>'
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 (openSUSE Leap 15.5) <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> go
Changed database context to 'TestDB'.
1> CREATE TABLE dbo.Inventory (id INT, name NVARCHAR(50), quantity INT, PRIMARY KEY (id));
2> INSERT INTO dbo.Inventory VALUES (1, 'banana', 150);
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'.