Centos安装配置SqlServer

安装配置

• 下载SQL Server2019镜像

curl https://packages.microsoft.com/config/rhel/7/mssql-server-2019.repo > /etc/yum.repos.d/mssql-server.repo

• 安装SqlServer程序包

yum install -y mssql-server

• 按照提示安装SqlServer

[root@hecs-166225 ~]# /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.

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.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=2104294&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

Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Setup has completed successfully. SQL Server is now starting.

• 查看运行状态

[root@hecs-166225 ~]# 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 Mon 2024-08-19 17:49:13 CST; 1h 17min ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 21569 (sqlservr)
    Tasks: 162
   Memory: 722.9M
   CGroup: /system.slice/mssql-server.service
           ├─21569 /opt/mssql/bin/sqlservr
           └─21594 /opt/mssql/bin/sqlservr

远程连接

连接前需放行相应端口，默认为1433

• navicat连接

• 初次连接可能会报[IM002] [Microsoft][ODBC驱动程序管理器]未发现数据源名称并且未指定默认驱动程序(0)

可以根据navicat版本的不同在navicat的安装目录运行sqlncli_x64.msi或者msodbcsql_64.msi

安装mssql相关工具包（非必须）

• 下载镜像

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

• 安装

yum install -y mssql-tools unixODBC-devel

• 添加环境变量

echo 'export PATH=$PATH:/opt/mssql-tools/bin' >> /etc/profile

• 重新加载环境变量

source /etc/profile

• 本地连接测试

[root@hecs-166225 ~]# sqlcmd -S localhost -U sa -p
Password:
1>

sprigboot项目连接

• pom

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <!--      根据项目情况确定版本号      -->
    <version>10.2.3.jre8</version>
</dependency>

• application.yml

driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver
#数据库test，模式名dbo
url: ${SPRING_DATASOURCE_URL:jdbc:sqlserver://****:1433;databaseName=test;encrypt=false;schema=dbo}
username: ${SPRING_DATASOURCE_USERNAME:sa}
password: ${SPRING_DATASOURCE_PASSWORD:***}

常见问题

• SpringBoot项目启动报com.microsoft.sqlserver.jdbc.SQLServerException: 驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target”。 ClientConnectionId:ffa8e271-af4f-4189-b477-6e5fb2818e52

application.yml中的url添加encrypt=false;

参考资料

[1].centos安装sqlserver

[2].IM002报错解决方案