SQLSERVER单机安装配置

安装配置

SQL server2017版本软件包

mssql-server-14.0.3238.1-19.x86_64.rpm
mssql-tools-17.4.1.1-1.x86_64.rpm
msodbcsql-13.1.9.2-1.x86_64.rpm
msodbcsql17-17.4.2.1-1.x86_64.rpm

配置本地yum源，之后使用yum安装rpm包

yum install msodbcsql-13.1.9.2-1.x86_64.rpm
yum install msodbcsql17-17.4.2.1-1.x86_64.rpm
yum install mssql-server-14.0.3238.1-19.x86_64.rpm
yum install mssql-tools-17.4.1.1-1.x86_64.rpm

配置mssql

# /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)
  7) Enterprise Core (PAID)
  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=852748&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): 3
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&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:
The specified password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters
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 [Enterprise Edition: Core-based Licensing].
ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

sqlcmd配置

sqlcmd登陆时报如下错误

Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746

因系统进行安全加固，对ssl进行了升级，Sql Server和SSMS依赖的SSL是1.0版本的，由于版本不一致造成无法链接
解决方案如下：

# 先停止MSSQL
sudo systemctl stop mssql-server

# 编辑服务配置
sudo systemctl edit mssql-server

# 在打开的编辑器中输入
[Service]
Environment="LD_LIBRARY_PATH=/opt/mssql/lib"
#输入完保存

# 给MSSQL创建指向SSL1.0的链接
sudo ln -s /usr/lib64/libssl.so.1.0.2k /opt/mssql/lib/libssl.so
sudo ln -s /usr/lib64/libcrypto.so.1.0.2k /opt/mssql/lib/libcrypto.so

数据目录配置

修改数据库数据文件及备份文件位置

mkdir -p /mssql/data
mkdir -p /backup
chown -R mssql:mssql /mssql/
chown -R mssql:mssql /backup/
/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /mssql/data
/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /mssql/data
/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /backup
/opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /backup

设置排序规则

修改数据库排序规则

/opt/mssql/bin/mssql-conf set-collation 
Latin1_General_CI_AS

备份恢复

数据备份

创建备份脚本

#!/bin/bash
source /etc/profile

# 以下是配置信息
user=sa
passwd=Password01
back_path=/backup
db_list="[Test-clear] [Freem]"
back_time=`date +%Y%m%d`
for db_name in $db_list
do
    echo "==============开始备份数据库[$db_name]=============" >> log$back_time.log
    back_filename=$back_path/$db_name$back_time
    sqlcmd -S localhost -U $user -P $passwd -d master -Q "BACKUP DATABASE $db_name to disk='$back_filename.bak'"
    if [ -e $back_filename.bak ];then
        echo "==============数据库[$db_name] 备份成功!=============" >> log$back_time.log
    else
        echo "==============数据库[$db_name] 备份失败!=============" >> log$back_time.log
    fi
done

设置定时任务

crontab -e
0 23 * * * /root/scripts/back.sh        # 每天晚上23点执行

备份还原

获取数据库逻辑名

1> restore  filelistonly from disk = '/tmp/Test-clear.bak'
2> go

数据库还原，库名包含特殊字符，需要使用中括号包围

RESTORE DATABASE [TaskProcess-clear] FROM DISK = '/tmp/TaskProcess-clear.bak'
WITH MOVE 'TaskProcess-clear' TO '/mssql/data/TaskProcess-clear.mdf',
MOVE 'TaskProcess-clear_log' TO '/mssql/data/TaskProcess-clear_log.ldf'
GO

权限分配

登录名只有登进数据库的功能 ，登录名可以映射用户名， 这样就有了相应的权限

本方案将创建两个用户并赋予数据库权限，将两个用户名映射到登录名，登录后可以操作两个数据库

创建登录名

create login aps with password='Aps2022'
go

创建用户名Test-clear

use [Test-clear]
go
create user Test-clear for login aps 
go
# 赋予数据库权限
exec sp_addrolemember 'db_owner', 'Test-clear'
go
# 设置用户默认schema
alter user Freemwith default_schema=Test-clear;
go

创建用户名Freem

use Freem
go
create user Freem for login aps 
go
# 赋予数据库权限
exec sp_addrolemember 'db_owner', 'Freem'
go
设置用户默认schema
alter user Freemwith default_schema=Freem;
go

日常操作

sqlcmd登录

# sqlcmd -U sa -P Test2016

查询数据库版本

1> select @@version
2> go                                                                                                                       
-----------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64)
        Sep 13 2019 15:49:57
        Copyright (C) 2017 Microsoft Corporation
        Enterprise Edition: Core-based Licensing (64-bit) on Linux (Red Hat Enterprise Linux Server 7.6 (Maipo))

(1 rows affected)

查看表结构，如表名前指定schema，需要用单引号包围，否则报错

sp_help table_name;
sp_columns table_name;
#表名指定schema示例
sp_help 'sys.schemas'
#扩展
sp_helpdb
sp_helpindex
sp_helpfile

查看数据库信息

select * from sysdatabases
go

查看schema信息

use Freem
go
select * from sys.schemas
go

查看对象信息

select * from sysobjects
go