在 Linux 安裝 MSSQL 2022

1
2
3
4
5

sudo alternatives --config python
# If not configured, install python2 and openssl10 using the following commands:
sudo yum install -y python2 compat-openssl10
# Configure python2 as the default interpreter using this command:
sudo alternatives --config python

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

[root@mssql-server-2022 ~]# sudo alternatives --config python

There are 2 programs which provide 'python'.

  Selection    Command
-----------------------------------------------
*+ 1           /usr/libexec/no-python
   2           /usr/bin/python2

Enter to keep the current selection[+], or type selection number: 2

1
2

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2022.repo
sudo yum install -y mssql-server

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

[main]
summary=Optimize for Microsoft SQL Server
include=throughput-performance

[cpu]
force_latency=5

[sysctl]
vm.swappiness = 1
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.transparent_hugepages=always
# For multi-instance SQL deployments, use
# vm.transparent_hugepages=madvise
vm.max_map_count=1600000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.numa_balancing=0
kernel.sched_latency_ns = 60000000
kernel.sched_migration_cost_ns = 500000
kernel.sched_min_granularity_ns = 15000000
kernel.sched_wakeup_granularity_ns = 2000000

1

sudo tuned-adm profile mssql

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20

[root@db141-mssql2022 ~]# tuned-adm list
Available profiles:
- accelerator-performance     - Throughput performance based tuning with disabled higher latency STOP states
- aws                         - Optimize for aws ec2 instances
- balanced                    - General non-specialized tuned profile
- desktop                     - Optimize for the desktop use-case
- epyc-eda                    - Optimize for EDA compute workloads on AMD EPYC CPUs
- hpc-compute                 - Optimize for HPC compute workloads
- intel-sst                   - Configure for Intel Speed Select Base Frequency
- latency-performance         - Optimize for deterministic performance at the cost of increased power consumption
- mssql                       - Optimize for Microsoft SQL Server
- network-latency             - Optimize for deterministic performance at the cost of increased power consumption, focused on low latency network performance
- network-throughput          - Optimize for streaming network throughput, generally only necessary on older CPUs or 40G+ networks
- optimize-serial-console     - Optimize for serial console use.
- powersave                   - Optimize for low power consumption
- throughput-performance      - Broadly applicable tuning that provides excellent performance across a variety of common server workloads
- virtual-guest               - Optimize for running inside a virtual guest
- virtual-host                - Optimize for running KVM guests
Current active profile: virtual-guest
[root@db141-mssql2022 ~]# sudo tuned-adm profile mssql

1

sysctl -w vm.max_map_count=1600000

1

echo madvise > /sys/kernel/mm/transparent_hugepage/enabled

1
2
3
4

[root@db141-mssql2022 ~]# tuned-adm off
[root@db141-mssql2022 ~]# tuned-adm profile mssql
[root@db141-mssql2022 ~]# tuned-adm active
Current active profile: mssql

1
2
3

[root@db141-mssql2022 ~]# ethtool -g enp1s0
[root@db141-mssql2022 ~]# ethtool -G enp1s0 rx 4096 tx 4096
netlink error: Operation not supported

1
2
3
4
5
6
7

<interface type='bridge'>
  <mac address='52:54:00:6b:54:f7'/>
  <source bridge='public'/>
  <model type='virtio'/>
  <driver name='vhost' rx_queue_size='4096' tx_queue_size='4096' queues='4'/>
  <address type='pci' domain='0x0000' bus='0x01' slot='0x00' function='0x0'/>
</interface>

1

sudo /opt/mssql/bin/mssql-conf setup

1
2

sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload

1

Fd831554@1234

1
2

curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo
sudo yum install -y mssql-tools18 unixODBC-devel

1

su - mssql

1
2

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile
source ~/.bash_profile

1

sqlcmd -C -S localhost -U sa

1
2
3
4

CREATE DATABASE db1;
GO
SELECT Name FROM sys.databases;
GO

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

USE db1;
GO;
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;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

-- 建立伺服器登入
CREATE LOGIN pollochang WITH PASSWORD = 'P@ssw0rd', CHECK_POLICY = OFF;
GO
SELECT name, type_desc FROM sys.server_principals WHERE name = 'pollochang';

-- 可省略，預設資料庫設定
ALTER LOGIN pollochang WITH DEFAULT_DATABASE = db1;
GO
-- 在 db1 資料庫裡，為伺服器登入 pollochang 建立對應的資料庫使用者 pollochang
CREATE USER pollochang FOR LOGIN pollochang;
GO

-- 分配基本權限
EXEC sp_addrolemember 'db_datareader', 'pollochang'; -- 可讀取數據
EXEC sp_addrolemember 'db_datawriter', 'pollochang'; -- 可寫入數據
EXEC sp_addrolemember 'db_owner', 'pollochang'; -- 完全控制數據庫

1
2
3
4
5
6

USE db1;
go
CREATE USER db1user FOR LOGIN pollochang;
GO
EXEC sp_addrolemember 'db_owner', 'pollochang';
GO