Label
PostgreSQL , ms sql , SQL Server
background
This paper introduces the simple deployment and use of MS SQL on Linux.
https://docs.microsoft.com/zh-cn/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017
Later, I will briefly introduce how to migrate MS SQL to PostgreSQL.
1 environment
CentOS 7.x x64 ECS
2. Environmental Configuration
Refer to MS SQL for the following links
Or refer to the configuration of the PostgreSQL OS section
PostgreSQL on Linux Best Deployment Manual - Collection Level
vi /etc/sysctl.conf kernel.sched_min_granularity_ns = 10000000 kernel.sched_wakeup_granularity_ns = 15000000 vm.swappiness=0 kernel.numa_balancing=0 vm.max_map_count=262144 sysctl -p
3. Install mssql database software
curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo cat /etc/yum.repos.d/mssql-server.repo yum install -y mssql-server
4. Install mssql client software
curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo yum remove unixODBC-utf16 unixODBC-utf16-devel yum install -y mssql-tools unixODBC-devel
5 View Installation Location
View the installation location of the rpm package to facilitate the configuration file settings.
rpm -ql mssql-tools rpm -ql mssql-server
6 Configuration environment variables
The configuration can be placed in the default configuration of the mssql user shell.
su - mssql vi .bash_profile export PATH="/opt/mssql-tools/bin:/opt/mssql/bin:$PATH" export LD_LIBRARY_PATH="/opt/mssql/lib:$LD_LIBRARY_PATH"
7 Configure System D Service
/opt/mssql/bin/mssql-conf setup
Enter MS SQL Super User SA Password
Password input
8 View mssql-server service status
systemctl status mssql-server
Current database not started
mssql-server.service - Microsoft SQL Server Database Engine Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; disabled; vendor preset: disabled) Active: inactive (dead) Docs: https://docs.microsoft.com/en-us/sql/linux Aug 19 10:20:18 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:18.81 spid29s The Service Broker endpoint is in disabled or stopped state. Aug 19 10:20:18 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:18.81 spid29s The Database Mirroring endpoint is in disabled or stopped state. Aug 19 10:20:18 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:18.83 spid29s Service Broker manager has started. Aug 19 10:20:18 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:18.84 spid10s Recovery is complete. This is an informational message only. No user action is required. Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z systemd[1]: Stopping Microsoft SQL Server Database Engine... Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:30.43 spid10s Always On: The availability replica manager is going offline because SQL Server is shutting down. This is an informational mess...ion is required. Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:30.43 spid10s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. ...ion is required. Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:30.53 spid29s Service Broker manager has shut down. Aug 19 10:20:30 iZbp13nu0s9j3x3op4zpd4Z sqlservr[2371]: 2018-08-19 10:20:30.63 spid10s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required. Aug 19 10:20:31 iZbp13nu0s9j3x3op4zpd4Z systemd[1]: Stopped Microsoft SQL Server Database Engine. Hint: Some lines were ellipsized, use -l to show in full.
9 View system D service configuration
cat /usr/lib/systemd/system/mssql-server.service [Unit] Description=Microsoft SQL Server Database Engine After=network.target auditd.service Documentation=https://docs.microsoft.com/en-us/sql/linux [Service] ExecStart=/opt/mssql/bin/sqlservr User=mssql WorkingDirectory=/var/opt/mssql # Kill root process KillMode=process # Wait up to 30 seconds for service to start/stop TimeoutSec=30min # Remove process, file, thread limits # LimitNPROC=infinity LimitNOFILE=infinity TasksMax=infinity UMask=007 # Restart on non-successful exits. Restart=on-failure # Don't restart if we've restarted more than 3 times in 2 minutes. StartLimitInterval=120 StartLimitBurst=3 [Install] WantedBy=multi-user.target
10 system D service configuration - default MSSQL data, log directory
cat /usr/lib/systemd/system/mssql-server.service WorkingDirectory=/var/opt/mssql
Content of working directory
ll -la /var/opt/mssql ll -la /data02/mssql/mssql total 40 drwxrwx--- 6 mssql mssql 4096 Aug 19 12:04 . drwxr-xr-x 3 mssql mssql 4096 Aug 19 12:06 .. -rw------- 1 mssql mssql 64 Aug 19 12:04 .bash_history -rw-rw-r-- 1 mssql mssql 114 Aug 19 12:04 .bash_profile drwxr-xr-x 2 mssql mssql 4096 Aug 19 10:00 data drwxr-xr-x 2 mssql mssql 4096 Aug 19 11:50 log -rw-rw-r-- 1 mssql mssql 51 Aug 19 10:51 mssql.conf drwxr-xr-x 2 mssql mssql 4096 Aug 19 09:53 secrets drwxr-xr-x 5 mssql mssql 4096 Aug 19 09:53 .system -rw------- 1 mssql mssql 727 Aug 19 12:04 .viminfo
11 Configure Operating System Firewall
Open ports to allow other servers to connect to MS SQL.
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent sudo firewall-cmd --reload
12 Close System D Autostart Service
systemctl disable mssql-server
13 Start MSSQL database
systemctl start mssql-server
14 Close MSSQL database
systemctl stop mssql-server
15 Transfer mssql data, log directory
1. Stopping the library
systemctl stop mssql-server
2. Confirm that the warehouse has been shut down
systemctl status mssql-server
3. Transfer the Working Directory directory
# df -h Filesystem Size Used Avail Use% Mounted on /dev/vdc1 1.5T 517G 940G 36% /data02 /dev/vdb1 1.5T 955G 501G 66% /data01
Transfer to Data Catalog
# mkdir /data02/mssql # chown mssql:mssql /data02/mssql # mv /var/opt/mssql /data02/mssql/ # ln -s /data02/mssql/mssql /var/opt/mssql
4. Restart mssql
systemctl start mssql-server
5. View Startup Status
systemctl status mssql-server mssql-server.service - Microsoft SQL Server Database Engine Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; disabled; vendor preset: disabled) Active: active (running) since Sun 2018-08-19 12:17:54 CST; 3s ago Docs: https://docs.microsoft.com/en-us/sql/linux Main PID: 7145 (sqlservr) CGroup: /system.slice/mssql-server.service ├─7145 /opt/mssql/bin/sqlservr └─7147 /opt/mssql/bin/sqlservr Aug 19 12:17:57 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:57.64 Server Node configuration: node 3: CPU mask: 0x00aaaaaaa0000000:0 Active CPU mask: 0x00aaaaaaa0000000:0. This message provides a descr...ion is required. Aug 19 12:17:57 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:57.94 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informa...ion is required. Aug 19 12:17:57 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:57.94 Server Lock partitioning is enabled. This is an informational message only. No user action is required. Aug 19 12:17:57 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:57.96 Server In-Memory OLTP initialized on highend machine. Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:58.01 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File...ion is required. Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: ForceFlush is enabled for this instance. Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:58.02 Server Query Store settings initialized with enabled = 1, Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:58.03 Server Software Usage Metrics is disabled. Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: 2018-08-19 12:17:58.04 spid11s Starting up database 'master'. Aug 19 12:17:58 iZbp13nu0s9j3x3op4zpd4Z sqlservr[7145]: ForceFlush feature is enabled for log durability. Hint: Some lines were ellipsized, use -l to show in full.
16 Connect mssql with sqlcmd client
Using sqlcmd cli, connect mssql, test
sqlcmd -S localhost -U SA -P '<YourPassword>' CREATE DATABASE TestDB SELECT Name from sys.Databases GO USE TestDB CREATE TABLE Inventory (id INT, name NVARCHAR(50), quantity INT) INSERT INTO Inventory VALUES (1, 'banana', 150); INSERT INTO Inventory VALUES (2, 'orange', 154); GO SELECT * FROM Inventory WHERE quantity > 152; GO QUIT
17 mssql simple performance test
18 Configure database parameters
for example
vi /var/opt/mssql/mssql.conf [sqlagent] enabled = false [EULA] accepteula = Y [network] ipaddress = 10.31.124.69 tcpport = 1433 [memory] memorylimitmb = 16384 //Restart instance systemctl restart mssql-server
Reference resources
man sqlservr
man mssql-conf
https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-overview?view=sql-server-2017
PostgreSQL on Linux Best Deployment Manual - Collection Level