Buch erstellen
Diese Seite zum Buch hinzufügen

Buch erstellen
Diese Seite aus Buch entfernen

Dies ist eine alte Version des Dokuments!

DWB im SMNS

OS: Linux Debian 12
Hostname: lserver-dwb01
User: dwbdebby
Passwort: ••••••••••

Festplatte/Partitionierung

Installation Debian auf 8TB (7,7) NVMe nvme0n1, Bilder liegen in /var/log/installer/.
LVM (Logical Volume Manager) mit Verschlüsselung.

Die Partition ist 3,7TB groß, lässt sich später aber Dank LVM noch flexibel erweitern. Wir haben hier Platz gelassen, falls später noch VMs o. Ä. auf dem Server laufen sollen.

SAS RAID5

Im System befinden sich 4 10TB SAS Festplatten im RAID5 Verbund über einen Supermicro MegaRAID 9440-8i, 8-Ports 12Gb/s SAS/SATA Hardware RAID Controller.
Im OS erscheint das als ein Volume sda mit 30TB (27,3).

LVM erstellen

Convert the RAID5 array into an LVM physical volume:

sudo pvcreate /dev/sda

Wenn:

bash: pvcreate: Kommando nicht gefunden.

Dann:

export PATH=$PATH:/usr/sbin
source ~/.bashrc

Create a new volume group on the physical volume:

sudo vgcreate vg-raid /dev/sda

Create a logical volume within the volume group. Adjust the size (<size>) as needed:

sudo lvcreate -L 10T -n lv-mssql vg-raid

Format the logical volume with XFS:

sudo apt-get update
sudo apt-get install xfsprogs
sudo mkfs.xfs /dev/vg-raid/lv-mssql

Das Logical Volume mounten

Create a secure mount point:

sudo mkdir /mnt/mssql_data
sudo chmod 700 /mnt/mssql_data

Mount the logical volume:

sudo mount /dev/vg-raid/lv-mssql /mnt/mssql_data

Mount verstetigen

Eintragen in /etc/fstab:

/dev/vg-raid/lv-mssql /mnt/mssql_data xfs defaults 0 2

SSH-Verbindung erstellen

ssh-keygen -t ed25519 -C "your_email@example.com"

Den Inhalt des Public Keys in ~/.ssh/authorized_keys (dwbdebby) auf dem Server kopieren.

Verbindung zum Server von Windows aus dann über die Konsole möglich mit:

ssh -p 666 dwbdebby@172.31.13.31

Dockeruser erstellen

adduser dwbdocker

Create the required directories in /opt:

sudo mkdir /opt/dwb-mssql-dev
sudo mkdir /opt/dwb-mssql-prod

Change the ownership of these directories (and the Mount) to the dwbdocker user:

sudo chown dwbdocker:dwbdocker /opt/dwb-mssql-dev
sudo chown dwbdocker:dwbdocker /opt/dwb-mssql-prod
sudo chown -R dwbdocker:dwbdocker /mnt/mssql_data

Set appropriate permissions (read, write, and execute for the owner only):

sudo chmod 700 /opt/dwb-mssql-dev
sudo chmod 700 /opt/dwb-mssql-prod

If you want to avoid using the root user, you can add your dwbdocker user to the docker group, which allows executing Docker commands without needing to prefix with sudo:

sudo usermod -aG docker dwbdocker

Docker Container für MSSQL Server

sudo apt-get install docker

sudo apt-get install docker-compose

Verzeichnis erstellen und betreten:

mkdir /opt/dwb-mssql
cd /opt/dwb-mssql

docker-compose.yml erstellen, je nachdem ob production oder development container_name (dwb-mssql-prod/dwb-mssql-dev) und port („5432:1433“/„5433:1433“) anpassen:

version: '3.8'

services:
  mssql-server:
    container_name: dwb-mssql-prod
    hostname: SMNS-LINUX
    build:
      context: .
      dockerfile: Dockerfile
    image: mcr.microsoft.com/mssql/server:2022-latest
    environment:
      - TZ=Europe/Berlin
      - MSSQL_SA_PASSWORD=${SA_PASSWORD}
      - ACCEPT_EULA=Y
      - MSSQL_AGENT_ENABLED=true
      # TLS environment variables
      - "MSSQL_SSL_CERTIFICATE_PEM_PATH=/var/opt/mssql/security/ca-certificates/fullchain.pem"
      - "MSSQL_SSL_PRIVATE_KEY_PEM_PATH=/var/opt/mssql/security/ca-certificates/privkey.pem"
    ports:
      - "5432:1433"
    volumes:
      - /mnt/mssql_data:/var/opt/mssql
      - ./log:/var/opt/mssql/log
      - /etc/letsencrypt/live/smns.diversityworkbench.de:/var/opt/mssql/security/ca-certificates
    networks:
      - mssql-network
    tmpfs:
      - /var/opt/mssql/tempdb:size=20g

networks:
  mssql-network:
    driver: bridge

Das Passwort kommt in die .env Datei.

Dockerfile erstellen mit Inhalt:

#FROM mcr.microsoft.com/mssql/server:2022-latest
FROM mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04

USER root
RUN id -u dwbdocker || useradd -u 1001 -m dwbdocker

# Install Unzip, Curl and required libraries
RUN apt-get update \
    && apt-get install -y curl gnupg2 unzip

# Add Microsoft repo for mssql-tools and msodbcsql17
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/10/prod.list> /etc/apt/sources.list.d/mssql-release.list

ENV PATH="/opt/mssql-tools/bin:${PATH}"
# Install SQL Server tools
RUN apt-get update \
    && ACCEPT_EULA=Y apt-get install -y mssql-tools \
    && echo 'export PATH="$PATH:/opt/mssql-tools/bin"'>> ~/.bashrc \
    && /bin/bash -c "source ~/.bashrc"

COPY startsql.sh /startsql.sh
RUN chmod +x /startsql.sh

USER dwbdocker

ENTRYPOINT ["/startsql.sh"]

startsql.sh:

#!/bin/bash
/opt/mssql/bin/sqlservr

Wichtig!

In a Docker environment, tmpfs size is not automatically managed; it defaults to half of your system's RAM but can be specified manually. If you don't set a limit, it could potentially use up to 50% of your available memory, which might be excessive or insufficient depending on your workload.

Deshalb ist die Größe hier auf 20GB festgelegt (/var/opt/mssql/tempdb:size=20g), das kann je nach Auslastung angepasst werden.

DWB beim Booten starten

Datei dwb-startup.service erstellen und nach /etc/systemd/system/ verschieben:

[Unit]
Description=Start the DWB MSSQL Server at Boottime
Requires=docker.service
After=docker.service

[Service]
Type=oneshot
RemainAfterExit=yes
WorkingDirectory=/opt/dwb-mssql-prod/
ExecStart=/usr/bin/docker-compose up -d
ExecStop=/usr/bin/docker-compose down

[Install]
WantedBy=multi-user.target

Service aktivieren:

sudo systemctl enable your-service.service

Service starten:

sudo systemctl start your-service.service

Status des Services einsehen:

sudo systemctl status your-service.service

Netzwerkkonfiguration

DHCP deaktivieren und eine statische IP festlegen:

sudo nano /etc/network/interfaces

enp129s0f1np1 ist eine eigene LWL-Netzwerkkarte. :FIXIT:

#Bypass an der DMZ vorbei
#iface eno1np0 inet static
#    address 192.168.1.31
#    netmask 255.255.255.0
#    gateway 192.168.1.254

auto enp129s0f1np1
iface enp129s0f1np1 inet static
    address 172.31.13.31
    netmask 255.255.255.0
    gateway 172.31.13.254
    dns-nameservers 172.31.13.254

Netzwerkservice neustarten:

systemctl restart networking

Netzwerkinterface aktivieren:

ip link set dev eno2np1 up

IP hinzufügen:

ip address add 172.31.13.31/24 dev enp129s0f1np1

IP entfernen:

ip address del 172.31.13.31/24 dev enp129s0f1np1

Interfacekonfiguration anzeigen:

ip addr show

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host noprefixroute
       valid_lft forever preferred_lft forever
2: enp194s0f0np0: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN group default qlen 1000
    link/ether 14:23:f2:9a:0d:50 brd ff:ff:ff:ff:ff:ff
3: enp194s0f1np1: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN group default qlen 1000
    link/ether 14:23:f2:9a:0d:51 brd ff:ff:ff:ff:ff:ff
4: eno1np0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 3c:ec:ef:9f:a5:46 brd ff:ff:ff:ff:ff:ff
    altname enp71s0f0np0
    inet 192.168.1.31/24 brd 192.168.1.255 scope global eno1np0
       valid_lft forever preferred_lft forever
    inet6 fe80::3eec:efff:fe9f:a546/64 scope link
       valid_lft forever preferred_lft forever
5: eno2np1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc mq state UP group default qlen 1000
    link/ether 3c:ec:ef:9f:a5:47 brd ff:ff:ff:ff:ff:ff
    altname enp71s0f1np1
    inet 172.31.14.32/24 brd 172.31.14.255 scope global eno2np1
       valid_lft forever preferred_lft forever
    inet6 fe80::3eec:efff:fe9f:a547/64 scope link
       valid_lft forever preferred_lft forever
6: enxbe3af2b6059f: <BROADCAST,MULTICAST> mtu 1500 qdisc noop state DOWN group default qlen 1000
    link/ether be:3a:f2:b6:05:9f brd ff:ff:ff:ff:ff:ff
7: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
    link/ether 02:42:0e:79:27:cb brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       valid_lft forever preferred_lft forever
    inet6 fe80::42:eff:fe79:27cb/64 scope link
       valid_lft forever preferred_lft forever
8: br-b3f2654f0e35: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
    link/ether 02:42:0b:7c:e1:7b brd ff:ff:ff:ff:ff:ff
    inet 172.20.0.1/16 brd 172.20.255.255 scope global br-b3f2654f0e35
       valid_lft forever preferred_lft forever
9: br-b96e9731af96: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default
    link/ether 02:42:b2:89:d9:9c brd ff:ff:ff:ff:ff:ff
    inet 172.18.0.1/16 brd 172.18.255.255 scope global br-b96e9731af96
       valid_lft forever preferred_lft forever
    inet6 fe80::42:b2ff:fe89:d99c/64 scope link
       valid_lft forever preferred_lft forever
55: veth005fc44@if54: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue master br-b96e9731af96 state UP group default
    link/ether 56:06:5b:dc:5f:28 brd ff:ff:ff:ff:ff:ff link-netnsid 0
    inet6 fe80::5406:5bff:fedc:5f28/64 scope link
       valid_lft forever preferred_lft forever
88: eno2np1.3113@eno2np1: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default qlen 1000
    link/ether 3c:ec:ef:9f:a5:47 brd ff:ff:ff:ff:ff:ff
    inet 172.31.13.31/24 scope global eno2np1.3113
       valid_lft forever preferred_lft forever
    inet6 fe80::3eec:efff:fe9f:a547/64 scope link
       valid_lft forever preferred_lft forever

Firewallregeln

sudo iptables -S

-P INPUT ACCEPT
-P FORWARD DROP
-P OUTPUT ACCEPT
-N DOCKER
-N DOCKER-ISOLATION-STAGE-1
-N DOCKER-ISOLATION-STAGE-2
-N DOCKER-USER
-N f2b-sshd
-A INPUT -s 45.134.26.0/24 -j DROP
-A INPUT -p tcp -m multiport --dports 666 -j f2b-sshd
-A INPUT -i eno2np1 -p tcp -m tcp --dport 3030 -j ACCEPT
-A INPUT -i eno2np1 -p tcp -m tcp --dport 5432 -j ACCEPT
-A INPUT -i eno2np1 -p tcp -m tcp --dport 7879 -j ACCEPT
-A INPUT -i eno2np1 -p tcp -m tcp --dport 7878 -j ACCEPT
-A INPUT -i eno2np1 -p tcp -m tcp --dport 9175 -m conntrack --ctstate NEW,ESTABLISHED -m comment --comment "Allow Prometheus Node Exporter scraping" -j ACCEPT
-A FORWARD -j DOCKER-USER
-A FORWARD -j DOCKER-ISOLATION-STAGE-1
-A FORWARD -o br-b96e9731af96 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT
-A FORWARD -o br-b96e9731af96 -j DOCKER
-A FORWARD -i br-b96e9731af96 ! -o br-b96e9731af96 -j ACCEPT
-A FORWARD -i br-b96e9731af96 -o br-b96e9731af96 -j ACCEPT
-A FORWARD -o docker0 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT
-A FORWARD -o docker0 -j DOCKER
-A FORWARD -i docker0 ! -o docker0 -j ACCEPT
-A FORWARD -i docker0 -o docker0 -j ACCEPT
-A FORWARD -o br-b3f2654f0e35 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT
-A FORWARD -o br-b3f2654f0e35 -j DOCKER
-A FORWARD -i br-b3f2654f0e35 ! -o br-b3f2654f0e35 -j ACCEPT
-A FORWARD -i br-b3f2654f0e35 -o br-b3f2654f0e35 -j ACCEPT
-A FORWARD -o br-182c22c80515 -m conntrack --ctstate RELATED,ESTABLISHED -j ACCEPT
-A FORWARD -o br-182c22c80515 -j DOCKER
-A FORWARD -i br-182c22c80515 ! -o br-182c22c80515 -j ACCEPT
-A FORWARD -i br-182c22c80515 -o br-182c22c80515 -j ACCEPT
-A OUTPUT -p tcp -m tcp --sport 3030 -m state --state ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -m tcp --sport 7878 -m state --state ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -m tcp --sport 5432 -m state --state ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -m tcp --sport 7879 -m state --state ESTABLISHED -j ACCEPT
-A OUTPUT -p tcp -m tcp --sport 9175 -m conntrack --ctstate ESTABLISHED -m comment --comment "Allow outgoing responses for Prometheus Node Exporter" -j ACCEPT
-A DOCKER -d 172.18.0.2/32 ! -i br-b96e9731af96 -o br-b96e9731af96 -p tcp -m tcp --dport 1433 -j ACCEPT
-A DOCKER-ISOLATION-STAGE-1 -i br-b96e9731af96 ! -o br-b96e9731af96 -j DOCKER-ISOLATION-STAGE-2
-A DOCKER-ISOLATION-STAGE-1 -i docker0 ! -o docker0 -j DOCKER-ISOLATION-STAGE-2
-A DOCKER-ISOLATION-STAGE-1 -i br-b3f2654f0e35 ! -o br-b3f2654f0e35 -j DOCKER-ISOLATION-STAGE-2
-A DOCKER-ISOLATION-STAGE-1 -j RETURN
-A DOCKER-ISOLATION-STAGE-2 -o br-b96e9731af96 -j DROP
-A DOCKER-ISOLATION-STAGE-2 -o docker0 -j DROP
-A DOCKER-ISOLATION-STAGE-2 -o br-b3f2654f0e35 -j DROP
-A DOCKER-ISOLATION-STAGE-2 -j RETURN
-A DOCKER-USER -j RETURN
-A f2b-sshd -j RETURN
\\
*nat\\
:PREROUTING ACCEPT [864689:102993021]\\
:INPUT ACCEPT [860617:102471719]\\
:OUTPUT ACCEPT [25936:1637619]\\
:POSTROUTING ACCEPT [222708:13401142]\\
:DOCKER - [0:0]\\
-A PREROUTING -m addrtype –dst-type LOCAL -j DOCKER\\
**-A PREROUTING -p tcp -m tcp –dport 7878 -m comment –comment "Make Diversity Workbench SQL Server accessible from outside" -j DNAT –to-destination 172.32.23.31:5432** \\
**-A PREROUTING -p tcp -m tcp –dport 7879 -m comment –comment "Make Diversity Workbench SQL Server accessible from outside" -j DNAT –to-destination 172.32.23.31:5433**\\
-A OUTPUT ! -d 127.0.0.0/8 -m addrtype –dst-type LOCAL -j DOCKER\\
-A POSTROUTING -s 172.17.0.0/16 ! -o docker0 -j MASQUERADE\\
-A POSTROUTING -s 172.18.0.0/16 ! -o br-182c22c80515 -j MASQUERADE\\
**-A POSTROUTING -s 172.18.0.2/32 -d 172.18.0.2/32 -p tcp -m tcp –dport 1433 -j MASQUERADE**\\
-A DOCKER -i docker0 -j RETURN\\
-A DOCKER -i br-182c22c80515 -j RETURN\\
**-A DOCKER ! -i br-182c22c80515 -p tcp -m tcp –dport 5432 -j DNAT –to-destination 172.18.0.2:1433**\\

Iptables

Regel hinzufügen, z.B.:

iptables -A INPUT -i eno2np1 -p tcp --dport 7878 -j ACCEPT

Speicherbar machen:

sudo apt-get update
sudo apt-get install iptables-persistent

Backup Existing Rules:

sudo iptables-save > ~/iptables_backup.txt

Mehr dazu: https://wiki.ubuntuusers.de/iptables/

fail2ban

apt install -y fail2ban

Configdatei anlegen:

nano /etc/fail2ban/jail.local

Mit Inhalt:

[mssqld]
enabled = true
logpath = /opt/dwb-mssql-prod/log/errorlog
maxfailures = 10
findtime = 1200
bantime = 3600
filter = mssqld-auth
port = 5432
action = iptables-allports
backend = auto

[sshd]
enabled = true
port    = 666
filter  = sshd
logpath = /var/log/auth.log
maxretry = 5

fail2ban starten:

systemctl start fail2ban

Monitor MSSQL:

fail2ban-client status mssqld

Encrypt MSSQL connection

certbot installieren:

sudo apt-get update
sudo apt-get install certbot

Zertifikat generieren:

sudo certbot certonly --standalone -d smns.diversityworkbench.de

Backups einspielen

Backups auf den Server kopieren:

scp -r -P 666 C:\Users\walbaum\Downloads\dwb2\ dwbdebby@192.168.1.31:~/

Backups auf dem Server in ein von MSSQL aus erreichbares Verzeichnis kopieren:

cp -r /home/dwbdebby/dwb2/* /mnt/mssql_data/backup

Enter the container:

docker exec -it dwb-mssql-prod /bin/bash

Start MSSQL:

sqlcmd -S localhost -U SA -P 'YourStrongPassword'

Falls man die Dateinamen innerhalb der Backups braucht:

RESTORE FILELISTONLY FROM DISK = N'/var/opt/mssql/backup/DiversityCollection_SMNS/DiversityCollection_SMNS_backup_2023_11_28_001313_9993897.bak'

DiversityCollection_SMNS auf Singleuser setzen, Backups einspielen, Singleuser raus nehmen(!):

USE [DiversityCollection_SMNS]
GO
ALTER DATABASE [DiversityCollection_SMNS] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
RESTORE DATABASE [DiversityCollection_SMNS] FROM DISK = N'/var/opt/mssql/backup/DiversityCollection_SMNS/DiversityCollection_SMNS_backup_2023_11_28_001313_9993897.bak' WITH FILE = 1, MOVE N'DiversityCollection_Data' TO N'/var/opt/mssql/data/DiversityCollection_SMNS.mdf', MOVE N'DiversityCollection_Log' TO N'/var/opt/mssql/data/DiversityCollection_SMNS_log.ldf', NOUNLOAD, STATS = 5
GO
ALTER DATABASE [DiversityCollection_SMNS] SET MULTI_USER
GO

RESTORE DATABASE [DiversityAgents_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityAgents_SMNS/DiversityAgents_SMNS_backup_2023_12_12_153817_7905535.bak' WITH  FILE = 1,  MOVE N'DiversityAgents_Data' TO N'/var/opt/mssql/data/DiversityAgents_SMNS.mdf',  MOVE N'DiversityAgents_Log' TO N'/var/opt/mssql/data/DiversityAgents_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityAgents_GOEP] FROM  DISK = N'/var/opt/mssql/backup/DiversityAgents_GOEP/DiversityAgents_GOEP_backup_2023_12_12_153818_0036195.bak' WITH  FILE = 1,  MOVE N'DiversityAgents_GOEP' TO N'/var/opt/mssql/data/DiversityAgents_GOEP.mdf',  MOVE N'DiversityAgents_GOEP_Log' TO N'/var/opt/mssql/data/DiversityAgents_GOEP_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityCollectionCache01_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityCollectionCache01_SMNS/DiversityCollectionCache01_SMNS_backup_2023_12_12_153817_9896209.bak' WITH  FILE = 1,  MOVE N'DiversityCollectionCache01_SMNS' TO N'/var/opt/mssql/data/DiversityCollectionCache01_SMNS.mdf',  MOVE N'DiversityCollectionCache01_SMNS_Log' TO N'/var/opt/mssql/data/DiversityCollectionCache01_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityCollectionCache_Pilot] FROM  DISK = N'/var/opt/mssql/backup/DiversityCollectionCache_Pilot/DiversityCollectionCache_Pilot_backup_2023_12_12_153817_9706199.bak' WITH  FILE = 1,  MOVE N'DiversityCollectionCache_Pilot' TO N'/var/opt/mssql/data/DiversityCollectionCache_Pilot.mdf',  MOVE N'DiversityCollectionCache_Pilot_Log' TO N'/var/opt/mssql/data/DiversityCollectionCache_Pilot_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityCollectionCache_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityCollectionCache_SMNS/DiversityCollectionCache_SMNS_backup_2023_12_12_153817_8345840.bak' WITH  FILE = 1,  MOVE N'DiversityCollectionCache' TO N'/var/opt/mssql/data/DiversityCollectionCache_SMNS.mdf',  MOVE N'DiversityCollectionCache_log' TO N'/var/opt/mssql/data/DiversityCollectionCache_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityCollectionCache_SMNS_2] FROM  DISK = N'/var/opt/mssql/backup/DiversityCollectionCache_SMNS_2/DiversityCollectionCache_SMNS_2_backup_2023_12_12_153817_9315929.bak' WITH  FILE = 1,  MOVE N'DiversityCollectionCache_SMNS_2' TO N'/var/opt/mssql/data/DiversityCollectionCache_SMNS_2.mdf',  MOVE N'DiversityCollectionCache_SMNS_2_log' TO N'/var/opt/mssql/data/DiversityCollectionCache_SMNS_2_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityCollection_GOEP] FROM  DISK = N'/var/opt/mssql/backup/DiversityCollection_GOEP/DiversityCollection_GOEP_backup_2023_12_12_153818_0096007.bak' WITH  FILE = 1,  MOVE N'DiversityCollection_GOEP' TO N'/var/opt/mssql/data/DiversityCollection_GOEP.mdf',  MOVE N'DiversityCollection_GOEP_Log' TO N'/var/opt/mssql/data/DiversityCollection_GOEP_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityCollection_Pilot] FROM  DISK = N'/var/opt/mssql/backup/DiversityCollection_Pilot/DiversityCollection_Pilot_backup_2023_12_12_153817_9256302.bak' WITH  FILE = 1,  MOVE N'DiversityCollection_Data' TO N'/var/opt/mssql/data/DiversityCollection_Pilot.mdf',  MOVE N'DiversityCollection_Log' TO N'/var/opt/mssql/data/DiversityCollection_Pilot_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityCollection_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityCollection_SMNS/DiversityCollection_SMNS_backup_2023_12_12_153817_8546037.bak' WITH  FILE = 1,  MOVE N'DiversityCollection_Data' TO N'/var/opt/mssql/data/DiversityCollection_SMNS.mdf',  MOVE N'DiversityCollection_Log' TO N'/var/opt/mssql/data/DiversityCollection_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityDescriptionsCache_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityDescriptionsCache_SMNS/DiversityDescriptionsCache_SMNS_backup_2023_12_12_153818_0226066.bak' WITH  FILE = 1,  MOVE N'DiversityDescriptionsCache_SMNS' TO N'/var/opt/mssql/data/DiversityDescriptionsCache_SMNS.mdf',  MOVE N'DiversityDescriptionsCache_SMNS_Log' TO N'/var/opt/mssql/data/DiversityDescriptionsCache_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityDescriptions_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityDescriptions_SMNS/DiversityDescriptions_SMNS_backup_2023_12_12_153817_9505989.bak' WITH  FILE = 1,  MOVE N'DiversityDescriptions_Base' TO N'/var/opt/mssql/data/DiversityDescriptions_SMNS.mdf',  MOVE N'DiversityDescriptions_Base_Log' TO N'/var/opt/mssql/data/DiversityDescriptions_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityExsiccatae_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityExsiccatae_SMNS/DiversityExsiccatae_SMNS_backup_2023_12_12_153817_7965739.bak' WITH  FILE = 1,  MOVE N'DiversityExsiccatae_Data' TO N'/var/opt/mssql/data/DiversityExsiccatae_SMNS.mdf',  MOVE N'DiversityExsiccatae_Log' TO N'/var/opt/mssql/data/DiversityExsiccatae_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityGazetteers_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityGazetteers_SMNS/DiversityGazetteers_SMNS_backup_2023_12_12_153817_9435943.bak' WITH  FILE = 1,  MOVE N'DiversityGazetteer_Data' TO N'/var/opt/mssql/data/DiversityGazetteers_SMNS.mdf',  MOVE N'DiversityGazetteer_Log' TO N'/var/opt/mssql/data/DiversityGazetteers_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityProjects_GOEP] FROM  DISK = N'/var/opt/mssql/backup/DiversityProjects_GOEP/DiversityProjects_GOEP_backup_2023_12_12_153818_0156029.bak' WITH  FILE = 1,  MOVE N'DiversityProjects_GOEP' TO N'/var/opt/mssql/data/DiversityProjects_GOEP.mdf',  MOVE N'DiversityProjects_GOEP_Log' TO N'/var/opt/mssql/data/DiversityProjects_GOEP_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityProjects_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityProjects_SMNS/DiversityProjects_SMNS_backup_2023_12_12_153817_8035839.bak' WITH  FILE = 1,  MOVE N'DiversityProjects_Data' TO N'/var/opt/mssql/data/DiversityProjects_SMNS.mdf',  MOVE N'DiversityProjects_Log' TO N'/var/opt/mssql/data/DiversityProjects_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityReferences_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityReferences_SMNS/DiversityReferences_SMNS_backup_2023_12_12_153817_8165838.bak' WITH  FILE = 1,  MOVE N'DiversityResources_dat' TO N'/var/opt/mssql/data/DiversityReferences_SMNS.mdf',  MOVE N'DiversityResources_log' TO N'/var/opt/mssql/data/DiversityReferences_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversitySamplingPlots_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversitySamplingPlots_SMNS/DiversitySamplingPlots_SMNS_backup_2023_12_12_153817_8106114.bak' WITH  FILE = 1,  MOVE N'DiversitySamplingPlots' TO N'/var/opt/mssql/data/DiversitySamplingPlots_SMNS.mdf',  MOVE N'DiversitySamplingPlots_log' TO N'/var/opt/mssql/data/DiversitySamplingPlots_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityScientificTerms_SMNS] FROM  DISK = N'/var/opt/mssql/backup/DiversityScientificTerms_SMNS/DiversityScientificTerms_SMNS_backup_2023_12_12_153817_9375932.bak' WITH  FILE = 1,  MOVE N'DiversityHabitats_Data' TO N'/var/opt/mssql/data/DiversityScientificTerms_SMNS.mdf',  MOVE N'DiversityHabitats_Log' TO N'/var/opt/mssql/data/DiversityScientificTerms_SMNS_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityTaxonNames_SMNS_Animalia] FROM  DISK = N'/var/opt/mssql/backup/DiversityTaxonNames_SMNS_Animalia/DiversityTaxonNames_SMNS_Animalia_backup_2023_12_12_153817_9966010.bak' WITH  FILE = 1,  MOVE N'DiversityTaxonNames_Plants_Data' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_Animalia.mdf',  MOVE N'DiversityTaxonNames_Plants_Log' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_Animalia_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityTaxonNames_SMNS_GBIF] FROM  DISK = N'/var/opt/mssql/backup/DiversityTaxonNames_SMNS_GBIF/DiversityTaxonNames_SMNS_GBIF_backup_2023_12_12_153818_0296235.bak' WITH  FILE = 1,  MOVE N'DiversityTaxonNames_SMNS_GBIF' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_GBIF.mdf',  MOVE N'DiversityTaxonNames_SMNS_GBIF_Log' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_GBIF_log.ldf',  MOVE N'DiversityTaxonNames_SMNS_GBIF_Log2' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_GBIF_log2.ldf',  MOVE N'DiversityTaxonNames_SMNS_GBIF_Log3' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_GBIF_log3.ldf',  MOVE N'DiversityTaxonNames_SMNS_GBIF_Log4' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_GBIF_log4.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityTaxonNames_SMNS_Insecta] FROM  DISK = N'/var/opt/mssql/backup/DiversityTaxonNames_SMNS_Insecta/DiversityTaxonNames_SMNS_Insecta_backup_2023_12_12_153817_9635979.bak' WITH  FILE = 1,  MOVE N'DiversityTaxonNames_Plants_Data' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_Insecta.mdf',  MOVE N'DiversityTaxonNames_Plants_Log' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_Insecta_log.ldf',  NOUNLOAD,  STATS = 5
RESTORE DATABASE [DiversityTaxonNames_SMNS_Plants] FROM  DISK = N'/var/opt/mssql/backup/DiversityTaxonNames_SMNS_Plants/DiversityTaxonNames_SMNS_Plants_backup_2023_12_12_153817_9835849.bak' WITH  FILE = 1,  MOVE N'DiversityTaxonNames_Plants_Data' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_Plants.mdf',  MOVE N'DiversityTaxonNames_Plants_Log' TO N'/var/opt/mssql/data/DiversityTaxonNames_SMNS_Plants_log.ldf',  NOUNLOAD,  STATS = 5

Backups erstellen

Einen User für die Backups erstellen:

USE master;
GO

CREATE LOGIN BackupAdmin WITH PASSWORD = 'StrongPassword!123';
GO

ALTER SERVER ROLE dbcreator ADD MEMBER BackupAdmin;
ALTER SERVER ROLE diskadmin ADD MEMBER BackupAdmin;

Für jede Datenbank einschließlich master und msdb:

USE ExampleDatabase;
GO

CREATE USER BackupAdmin FOR LOGIN BackupAdmin;
GO
ALTER ROLE db_backupoperator ADD MEMBER BackupAdmin;
GO

In /opt/dwb-backup läuft backup_script.sh und erstellt jede Nacht um 0:00 Uhr ein Komplettabbild der Datenbank in /mnt/mssql_data/backup/full/, jede volle Stunde zwischen 7:00 und 18:00 Uhr ein inkrementelles Abbild in /mnt/mssql_data/backup/diff/. Für dieses Skript müssen auf dem Server mssql-tools installiert werden!

#!/bin/bash

NOW=$(date +"%Y%m%d-%H%M%S")
BACKUP_PATH="/var/opt/mssql/backup"
BACKUP_PATH_HOST="/mnt/mssql_data/backup"

trap 'fn_terminate_script' INT

sqlserver_dwb_full(){
    [ ! -d "${BACKUP_PATH_HOST}/full" ] && mkdir -p "${BACKUP_PATH_HOST}/full" || :

    touch "${BACKUP_PATH_HOST}/full/.in_process"

    IGNORE_DB="tempdb model"
    DB_LIST=$(sqlcmd -S 172.32.23.31,5432 -U BackupAdmin -P '}Cg5+~W7Hyye&6T%uy' -h -1 -Q 'SET NOCOUNT ON;SELECT name FROM sys.databases;')
    for db in $DB_LIST
    do
        skipdb=-1
        if [ "$IGNORE_DB" != "" ];
        then
            for i in $IGNORE_DB
            do
                [ "$db" = "$i" ] && skipdb=1 || :
            done
        fi
        if [ "$skipdb" = "-1" ]; then
            SQL_FILE="${db}_full_${NOW}"
            sqlcmd -S 172.32.23.31,5432 -U BackupAdmin -P '}Cg5+~W7Hyye&6T%uy' -Q "BACKUP DATABASE [${db}] TO DISK=N'${BACKUP_PATH}/full/${SQL_FILE}.bak' WITH NAME='Full backup of ${db}',INIT,COMPRESSION,CHECKSUM,FORMAT"
            if [ $? -eq 0 ]
            then
                printf "${SQL_FILE}.bak written" >> $LOG_FILE
                chmod g+r "${BACKUP_PATH_HOST}/full/${SQL_FILE}.bak"
            else
                printf "WARNING: An error occured while attempting to write {$db}" >> $LOG_FILE
            fi
        fi
    done

    rm "${BACKUP_PATH_HOST}/full/.in_process"
}

sqlserver_dwb_diff(){
    [ ! -d "${BACKUP_PATH_HOST}/diff" ] && mkdir -p "${BACKUP_PATH_HOST}/diff" && chown dwbdocker:dwbdocker "${BACKUP_PATH_HOST}/diff" || :

    touch "${BACKUP_PATH_HOST}/diff/.in_process"

    IGNORE_DB="tempdb master model"
    DB_LIST=$(sqlcmd -S 172.32.23.31,5432 -U BackupAdmin -P '}Cg5+~W7Hyye&6T%uy' -h -1 -Q 'SET NOCOUNT ON;SELECT name FROM sys.databases;')
    for db in $DB_LIST
    do
        skipdb=-1
        if [ "$IGNORE_DB" != "" ];
        then
            for i in $IGNORE_DB
            do
                [ "$db" = "$i" ] && skipdb=1 || :
            done
        fi
        if [ "$skipdb" = "-1" ]; then
            SQL_FILE="${db}_diff_${NOW}"
            sqlcmd -S 172.32.23.31,5432 -U BackupAdmin -P '}Cg5+~W7Hyye&6T%uy' -Q "BACKUP DATABASE [${db}] TO DISK=N'${BACKUP_PATH}/diff/${SQL_FILE}.bak' WITH NAME='diff backup of ${db}',DIFFERENTIAL"
            if [ $? -eq 0 ]
            then
                printf "${db}.bak written" >> $LOG_FILE
                chmod g+r "${BACKUP_PATH_HOST}/diff/${SQL_FILE}.bak"
            else
                printf "WARNING: An error occured while attempting to write ${SQL_FILE}" >> $LOG_FILE
            fi
        fi
    done

    rm "${BACKUP_PATH_HOST}/diff/.in_process"
}


if [ "$1" = "full"  ]; then
    LOG_FILE="${BACKUP_PATH_HOST}/full/backup-db_${NOW}.log"
    printf "Start full backup of MSSQL Server lserver-dwb01: $(date "+%F %T")" > $LOG_FILE
    sqlserver_dwb_full
    printf "Finish backup utility: $(date "+%F %T")\n\n" >> "$LOG_FILE"
fi
if [ "$1" = "diff"  ]; then
    LOG_FILE="${BACKUP_PATH_HOST}/diff/backup-db_`date "+%u"`.log"
    echo "Start differential backup of MSSQL Server lserver-dwb01: $(date "+%F %T")" >> $LOG_FILE
    sqlserver_dwb_diff
    printf "Finish backup utility: $(date "+%F %T")\n\n" >> "$LOG_FILE"
fi

Crontab (crontab -e) mit PATH, sonst ist 'sqlcmd' nicht bekannt:

PATH=/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/opt/mssql-tools/bin

0 0 * * * /opt/dwb-backup/backup_script.sh full > /var/log/backup_script_full.log 2>&1
0 7-18 * * * /opt/dwb-backup/backup_script.sh diff > /var/log/backup_script_diff.log 2>&1

Prometheus Daten

Die Healthdaten des Servers werden via Node Exporter an Prometheus auf Hetzner freigegeben.

Werkzeuge

Navigationsmenüs und Suche

Seitenstatus

Seiten-Werkzeuge

Metainformationen zur Seite