Linux Shell 实现一键部署SQL_Server2022

sql Server 前言

Microsoft SQL Server 是一个全面的数据库平台，使用集成的商业智能 (BI)工具提供了企业级的数据管理。Microsoft SQL Server 数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使您可以构建和管理用于业务的高可用和高性能的数据应用程序。

SQL 参考

• 支持 SQL Server 2017,2019,2022自动化部署

Linux 各系统下载使用参考

安装 SQL Server 2022

• 创建安装自动化脚本

• 实现在线下载sql初始化sql，配置用户名密码，远程连接开启，环境变量设置。
• 以下基于Redhat系统
• SQL Server 安装目录/opt/mssql
• 数据库用户名密码 sa/Report@123 额外的系统管理账号admin/Report@123
• MSSQL_PID （evaluation, developer, express, web, standard, enterprise）安装完成自动激活 
• Redhat 9 functions使用需要执行安装yum install initscripts -y 
• curl 'https://qyapi.weixin.qq.com/cgi-bin/webhook/send?key=XXXXX' #更改自己的企业微信机器人地址 
• curl -o /etc/yum.repos.d/redhat.repo http://mirrors.aliyun.com/repo/Centos-8.repo #阿里在线repo
• yum install figlet -y #用于将文字转换为放大艺术字（使用figlet Mysql显示）

vim /sql_server_install.sh

#!/bin/bash -e
# Author: CIASM 
# Date: 2023/04/02
#https://learn.microsoft.com/en-us/sql/linux/sample-unattended-install-redhat?view=sql-server-ver16

<<!
 ____        _   ____                           
/ ___|  __ _| | / ___|  ___ _ ____   _____ _ __ 
\___ \ / _` | | \___ \ / _ \ '__\ \ / / _ \ '__|
 ___) | (_| | |  ___) |  __/ |   \ V /  __/ |   
|____/ \__, |_| |____/ \___|_|    \_/ \___|_| 
!
# Use the following variables to control your install:

# Password for the SA user (required)
MSSQL_SA_PASSWORD='Report@123'

# Product ID of the version of SQL server you're installing
# Must be evaluation, developer, express, web, standard, enterprise, or your 25 digit product key
# Defaults to developer
MSSQL_PID='enterprise'

# Enable SQL Server Agent (recommended)
SQL_ENABLE_AGENT='y'

# Install SQL Server Full Text Search (optional)
SQL_INSTALL_FULLTEXT='y'

# Create an additional user with sysadmin privileges (optional)
SQL_INSTALL_USER='admin'
SQL_INSTALL_USER_PASSWORD='Report@123'

if [ -z $MSSQL_SA_PASSWORD ]
then
  echo Environment variable MSSQL_SA_PASSWORD must be set for unattended install
  exit 1
fi

echo Adding Microsoft repositories...
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2022.repo
sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo

echo Installing SQL Server...
sudo yum install -y mssql-server

echo Running mssql-conf setup...
sudo MSSQL_SA_PASSWORD=$MSSQL_SA_PASSWORD \
     MSSQL_PID=$MSSQL_PID \
     /opt/mssql/bin/mssql-conf -n setup accept-eula

echo Installing mssql-tools and unixODBC developer...
sudo ACCEPT_EULA=Y yum install -y mssql-tools unixODBC-devel

# Add SQL Server tools to the path by default:
echo Adding SQL Server tools to your path...
echo PATH="$PATH:/opt/mssql-tools/bin" >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

# Optional Enable SQL Server Agent:
if [ ! -z $SQL_ENABLE_AGENT ]
then
  echo Enable SQL Server Agent...
  sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true
fi

# Optional SQL Server Full Text Search installation:
if [ ! -z $SQL_INSTALL_FULLTEXT ]
then
    echo Installing SQL Server Full-Text Search...
    sudo yum install -y mssql-server-fts
fi

# Configure firewall to allow TCP port 1433:
echo Configuring firewall to allow traffic on port 1433...
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload

# Example of setting post-installation configuration options
# Set trace flags 1204 and 1222 for deadlock tracing:
#echo Setting trace flags...
#sudo /opt/mssql/bin/mssql-conf traceflag 1204 1222 on

# Restart SQL Server after making configuration changes:
echo Restarting SQL Server...
sudo systemctl restart mssql-server

echo power on SQL Server...
sudo systemctl enable --now mssql-server

echo global variable SQL Server...
ln -s /opt/mssql/bin/* /usr/local/sbin/
ln -s /opt/mssql-tools/bin/* /usr/local/sbin/

# Connect to server and get the version:
counter=1
errstatus=1
while [ $counter -le 5 ] && [ $errstatus = 1 ]
do
  echo Waiting for SQL Server to start...
  sleep 5s
  /opt/mssql-tools/bin/sqlcmd \
    -S localhost \
    -U SA \
    -P $MSSQL_SA_PASSWORD \
    -Q "SELECT @@VERSION" 2>/dev/null
  errstatus=$?
  ((counter++))
done

# Display error if connection failed:
if [ $errstatus = 1 ]
then
  echo Cannot connect to SQL Server, installation aborted
  exit $errstatus
fi

# Optional new user creation:
if [ ! -z $SQL_INSTALL_USER ] && [ ! -z $SQL_INSTALL_USER_PASSWORD ]
then
  echo Creating user $SQL_INSTALL_USER
  /opt/mssql-tools/bin/sqlcmd \
    -S localhost \
    -U SA \
    -P $MSSQL_SA_PASSWORD \
    -Q "CREATE LOGIN [$SQL_INSTALL_USER] WITH PASSWORD=N'$SQL_INSTALL_USER_PASSWORD', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON; ALTER SERVER ROLE [sysadmin] ADD MEMBER [$SQL_INSTALL_USER]"
fi

echo Done!

执行安装

sh /sql_server_install.sh

查询SQL Server数据授权问题

• 显示为“0”，表示已经激活

sqlcmd -S localhost -U SA -P Report@123

use master
go
RECONFIGURE
GO
DECLARE @daysleft int
DECLARE @instancename sysname
SELECT @instancename = CONVERT(sysname, SERVERPROPERTY('InstanceName'))
EXEC @daysleft = xp_qv '2715127595', @instancename
SELECT @daysleft '剩余使用天数'
GO

获取系统版本和数据库版本信息，上述脚本有，以下单独列出来说明

sqlcmd -S localhost -U SA -P Report@123 -Q 'select @@VERSION'