在 openSuSE 15.5 上安装 SQL Server 2022

╰半橙微兮° 2024-03-17 19:22 101阅读 0赞

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

昨天有个任务， 迁移 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'.