MSSQL

Материал из ALT Linux Wiki
Работает не только на голом железе или в виртуальной машине, но и в контейнере.
Есть рецепт в epm!
epm play -y mssql-server mssql-tools


Установка MS SQL на Альт p11

Для Альт p10 всё точно так же, как описано ниже про р11, разве что черпать вдохновение предстоит у SLES, а не у RHEL, откуда не всё «одинаково полезло» в р10.

Поскольку выпуск 2025 (версия 17) на р10 пересобирается, запускается, но тут же падает с кучей ошибок, придётся устанавливать версию 16 (выпуск 2022) сервера.
  1. Подробный вариант:
    • для ручной установки и настройки заменить все вхождения rhel/10 на sles/15, а 2025 на 2022;
    • или раскомментировать строчку REL=2022 вместо REL=2025 в скрипте (см. ниже) — остальное он сделает сам.
  2. Краткий вариант: 
    # epmp -y mssql-{server,tools}
Поскольку выпуск 2022 (версия 16) на р11 хоть и ставится посредством epmp, но ввиду требования более старых библиотек не работает, предстоит устанавливать версию 17 (выпуск 2025) сервера.

NB: для SLES поддержка MSSQL с выпуска 2025 прекращена, так что все пакеты берутся из RHEL-репозиториев.

Подготовка

Освежить систему после установки недостающего: 

# apt-get update && apt-get install -y eepm libatomic1 lftp wget unixODBC
# epm -y full-upgrade

Размещение баз данных с журналами транзакций

БД с журналами следует держать на ФС с поддержкой функции Forced Unit Access (FUA), без которой всё люто тормозит:
  • для ядер ≤5.6 — на XFS,
  • для более новых — на EXT4.

Если сервер на ZFS, под базы с журналами можно создать отдельный датасет по фамилии mssql, отформатированный в ЕХТ4 (-V — объём датасета, -b — размер блока): 

# zfs create -s -V 2TB zpool_name/dataset_name
# mkfs.ext4 -b 4096 /dev/zvol/zpool_name/dataset_name

Привязаться им к MSSQL можно одним из следующих способов.

  • Монтировать созданный датасет в точку входа по умолчанию,
    зафиксировав её в /etc/fstab (системно):
    /dev/zvol/zpool_name/dataset_name /var/opt/mssql ext4 noatime 1 2
    и отдав команду (на первый раз):
    # mount -o noatime /dev/zvol/zpool_name/dataset_name /var/opt/mssql
  • Задать каталоги размещения из ком.строки: 
    # /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /path/to/mount/databases
# /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir  /path/to/mount/translogs
    или через /var/opt/mssql/mssql.conf:
    [filelocation]
defaultdatadir = /path/to/mount/databases
defaultlogdir  = /path/to/mount/translogs

После чего перезапустить службу mssql-server.

Установка

Можно всё сделать одним скриптом, при успешном завершении которого запустится первоначальная настройка, а за ней и сам сервер:

mssql-install   
#!/bin/sh

apt-get update && apt-get install -y eepm libatomic1 lftp wget unixODBC
epm -y full-upgrade
useradd mssql
install -o mssql -g mssql -d /var/opt/mssql

## Раскомментировать нужный вариант: ##
#REL=2022 # Для Альта вплоть до р10.
REL=2025  # Для Альта начиная с р11.

case $REL in
    2025)SRC="rhel/10";;
    *)   SRC="sles/15"
esac

for RPM in mssql-{server-{1,fts},tools} msodbcsql; do
    case $RPM in
        *-server-*)
            DIR=${RPM%-*}-$REL
        ;;
        *)  DIR=prod
    esac
    URL="https://packages.microsoft.com/$SRC/$DIR/Packages/m"
    wget $URL/`lftp -e 'ls;quit' $URL | awk '/'$RPM'/{print $5}' | sort -V | tail -1`'` &&
        epmi -y --repack `ls -1 ${RPM}*`
done

systemctl enable mssql-server
sed -i 's|PATH=.*|&:/opt/mssql-tools18/bin|' ~/.bashrc
sed -i 's|su $whitelistArg|su|' /opt/mssql/lib/mssql-conf/invokesqlservr.sh
odbcinst -i -d -f /opt/microsoft/msodbcsql18/etc/odbcinst.ini &>/dev/null
odbcinst -q -d # проверка доступности драйвера.

# Автонастройка после установки, где "2" — редакция для разработчиков, а "9" — русский язык:
PASS="DerPar0!" # Пароль для пользователя sa
/opt/mssql/bin/mssql-conf setup <<<`echo 2; echo Y; echo 9; echo "$PASS"; echo "$PASS"`

Либо поэтапно...

  1. Инструментарий MSSQL и драйвер ODBC: 
    for RPM in mssql-tools msodbcsql; do
    URL="https://packages.microsoft.com/rhel/10/prod/Packages/m"
    wget $URL/`lftp -e 'ls;quit' $URL | awk '/'$RPM'/{a=$5}END{print a}'` &&
        epmi -y --repack `ls -1 ${RPM}*`
done
    • Зарегистрировать драйвер и проверить его доступность: 
      # odbcinst -i -d -f /opt/microsoft/msodbcsql18/etc/odbcinst.ini &>/dev/null
# odbcinst -q -d
[PostgreSQL]
[MySQL]
[ODBC Driver 18 for SQL Server]
  2. Полнотекстовый поиск и сам сервер: 
    for PAK in fts 1; do
    RPM=mssql-server-$PAK
    URL="https://packages.microsoft.com/rhel/10/mssql-server-2025/Packages/m"
    wget $URL/`lftp -e 'ls;quit' $URL | awk '/'$RPM'/{print $5}' | sort -V | tail -1` &&
        epmi -y --repack `ls -1 ${RPM}*`
done
    • Cоздать пользователя и каталог для службы:
      # useradd mssql; install -o mssql -g mssql -d /var/opt/mssql
    • Во избежание ошибки su: invalid option -- 'p' поправить кривой вызов su:
      # sed -i 's/$whitelistArg//' /opt/mssql/lib/mssql-conf/invokesqlservr.sh
    • Провести первоначальную настройку:
      # /opt/mssql/bin/mssql-conf setup
      Можно автоматически («2» — редакция для разработчиков, «9» — русский язык):
      PASS="DerPaR0L!"
# /opt/mssql/bin/mssql-conf setup <<<`echo 2; echo Y; echo 9; echo "$PASS"; echo "$PASS"`
    • Навсегда запустить службу:
      # systemctl enable --now mssql-server

SQL Server Integration Services

Если потребуется ещё и служба SSIS, то поскольку таковой на момент редактирования статьи в вышеприведённых репозиториях не наблюдалось, придётся...

  1. Поставить версию для выпуска 2022 из репозитория rhel/9 — и сразу же настроить, а также добавить путь до исполняемых файлов: 
    # wget https://packages.microsoft.com/rhel/9/mssql-server-2022/Packages/m/mssql-server-is-16.0.4215.2-3.x86_64.rpm
# epmi -y --repack mssql-server-is-16.0.4215.2-3.x86_64.rpm
# /opt/ssis/bin/ssis-conf setup <<<`echo 2; echo Y; echo 9`
# sed -i 's|export PATH|&=$PATH:/opt/ssis/bin|' /etc/profile
  2. Создать /etc/systemd/system/ssis-telemetry.service.d/user.conf (во избежание размножения лишних юзеров сверх необходимого) со следующим содержимым: 
    [Service]
User = mssql
  3. Создать каталог для службы и перезапустить её: 
    # install -o mssql -g mssql -d /.system
# systemctl daemon-reload
# systemctl restart ssis-telemetry

Проверка

По завершению любого из вариантов установки следует активировать агент сервера для выполнения запланированных заданий и проверить «подключабельность» после перезапуска службы: 

# /opt/mssql/bin/mssql-conf set sqlagent.enabled true
# systemctl restart mssql-server
# /opt/mssql-tools18/bin/sqlcmd -C -S localhost -U sa -P '<пароль>' -Q 'sp_databases'
DATABASE_NAME DATABASE_SIZE REMARKS
------------- ------------- -------
master                 7936 NULL
model                 16384 NULL
msdb                  24192 NULL
tempdb                24576 NULL

Примеры работы с базами данных

Подстановка в примерах ниже
sa логин администратора
Pa$$word пароль, указанный при mssql-conf setup

Сброс пароля системного администратора sa при остановленном mssql-server (процедура небыстрая): 

# /opt/mssql/bin/mssql-conf set-sa-password

Использование MSSQL-tools

  1. Дабы не вызывать команду sqlcmd всякий раз с полным путём, имеет смысл...
    • либо пробросить её симлинком в общедоступное место: 
      # ln -s /opt/mssql-tools18/bin/sqlcmd /bin/
    • либо добавить путь до неё в переменную PATH: 
      $ sed -i 's|PATH=.*|&:/opt/mssql-tools18/bin|' ~/.bashrc
  2. Просмотр доступных баз: 
    $ sqlcmd -C -S localhost -U sa -P 'Pa$$word' -Q 'sp_databases'
  3. Создание базы test: 
    $ sqlcmd -C -S localhost -U sa -P 'Pa$$word' -Q 'create database test'
  4. Работа с дампами базы test: 
    # Переменные
INFLATE="COMPRESSION,NOFORMAT,NOINIT,NOREWIND,NOUNLOAD,SKIP,STATS=10"
DEFLATE="FILE=1,NORECOVERY,NOUNLOAD,REPLACE,STATS=5"
ARGS="-C -S localhost -U sa -P Pa$$word -Q"

# Резервное копирование базы
sqlcmd $ARGS "BACKUP  DATABASE [test]  TO  DISK=N'/path/to/test.bak' WITH $INFLATE"

# Восстановление из резервной копии
sqlcmd $ARGS "RESTORE DATABASE [test] FROM DISK=N'/path/to/test.bak' WITH $DEFLATE"

Пример скрипта для обслуживания и бэкапа баз

(вызывается по ежедневному таймеру)

Не взирая на активированного агента и запущенный SSIS, предпочитаю регламентные задания скриптовать вместо отдачи оных на откуп скуль-студийным виндогенератам, норовящим толкать неподходящие по форме бруски в несоответствующие по сечению отверстия.

/usr/local/bin/sqldump   
#!/bin/sh

BASES=( # Перечень баз для обработки.
    edo
    uat
    zup
)
ARGS="-C -S localhost -U sa -P DerPaR0L! -b"
TGT=/mnt/dumps # Куда класть ежедневные дампы.
INDEX=Build # ежедневная пересборка индексов
LIM=6

[ `date +%u` -eq 7 ] && {
    TGT=$TGT/weeks      #  еженедельные дампы.
    INDEX=Index # еженедельная переиндексация
    LIM=4
} || {
    [ `date +%d` -eq 1 ] && {
        TGT=$TGT/months #   ежемесячные дампы.
        LIM=2
    }
}
[ -d $TGT ] || mkdir $TGT

Lim(){ # Зачистка дампов сверх лимита.
    while true; do
        [ `ls -1 $TGT/${DB}* | wc -l` -ge $LIM ] &&
            rm -f `ls -1 $TGT/${DB}* | head -1` ||
            break
    done
}

Fail(){ # Прерывание обработки БД с оповещением о сбое этапа.
    [ $1 -eq 0 ] || {
        MSG+="_*__Сбой $2\\!__*_"
        Ntfy
        break
    }
}

Ntfy(){ # Оповещение в телегу (подробнее см. здесь: https://www.altlinux.org/Бот_Telegram).
    Token="жетон телегобота"
    URL="https://api.telegram.org/bot$Token/sendMessage"
    ChatID="-100"
    ChatID+="номер чата"
    Topic="номер темы"
    curl -s "$URL" -F chat_id=$ChatID -F reply_to_message_id=$Topic -F text="$MSG" -F parse_mode=markdownv2 2>&1 >/dev/null
}

Check(){ # Проверка целостности.
    START=`date +%s`
    sqlcmd $ARGS -d $DB -Q "DBCC CHECKDB(N'$DB') WITH PHYSICAL_ONLY"
    Fail $? проверки
    MSG+=$(date -ud @$[`date +%s`-START] +%T)
    MSG+=" — проверка целостности
"
}

reStat(){ # Обновление статистик (опция Full Scan необходима, судя по докам).
    START=`date +%s`
    sqlcmd $ARGS -d $DB -Q "exec sp_MSforeachtable N'UPDATE STATISTICS ? WITH FULLSCAN'"
    Fail $? статистики
    sqlcmd $ARGS -d $DB -Q "DBCC FreeProcCache" # Очистка процедурного кэша
    Fail $? очистки
    MSG+=$(date -ud @$[`date +%s`-START] +%T)
    MSG+=" — обновление статистик
"
}

reBuild(){ # Для ежедневного применения.
    START=`date +%s`
    sqlcmd $ARGS -d $DB -Q "ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=ON);" # переиндексация
    Fail $? перестроения
# Дефрагментация индексов (включить блокировки, выполнить дефрагментацию, выключить блокировки)
    sqlcmd $ARGS -d $DB -Q "exec sp_MSforeachtable 'ALTER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS=ON, ALLOW_ROW_LOCKS=ON)'"
    Fail $? блокировки
    sqlcmd $ARGS -d $DB -Q "exec sp_MSforeachtable N'DBCC INDEXDEFRAG ($DB,''?'')'"
    Fail $? дефрагментации
    sqlcmd $ARGS -d $DB -Q "exec sp_MSforeachtable 'ALTER INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS=OFF, ALLOW_ROW_LOCKS=OFF)'"
    Fail $? разблокировки
    MSG+=$(date -ud @$[`date +%s`-START] +%T)
    MSG+=" — оптимизация индексов
"
}

reIndex(){ # Еженедельное полное перестроение индексов таблиц с их блокировкой без нужды в дефрагментации.
    START=`date +%s`
    sqlcmd $ARGS -d $DB -Q "exec sp_MSforeachtable N'DBCC DBREINDEX (''?'')'"
    Fail $? реиндексации
    MSG+=$(date -ud @$[`date +%s`-START] +%T)
    MSG+=" — реиндексация таблиц
"
}

Dump(){ # Сжатый дамп.
    START=`date +%s`
    ACT=BACKUP; DST=TO
    OPT="COMPRESSION,NOFORMAT,NOINIT,NOREWIND,NOUNLOAD,SKIP,STATS=10"
    Lim
    sqlcmd $ARGS -Q "$ACT DATABASE [$DB] $DST DISK=N'$TGT/${DB}_`date +%Y-%m-%d_%H.%M.%S`.pak' WITH $OPT"
    Fail $? упаковки
# Просмотр режима доступа к базе (а то были случаи...)
#   sqlcmd $ARGS -Q "SELECT name, user_access_desc FROM sys.databases WHERE name='$DB'" | awk '/'$DB'/{print $2}'
    MSG+=$(date -ud @$[`date +%s`-START] +%T)
    MSG+=" — резервная упаковка
"
}

for DB in ${BASES[@]}; do
    BEGIN=`date +%s`
    MSG="*Обслуживание БД ${DB%%_*}*
"
    Check
    reStat
    re$INDEX
    Dump
    MSG+=$(date -ud @$[`date +%s`-BEGIN] +"*Итого %T*")
    Ntfy
done

Подключение через ODBC

Пример для сервера на локалхосте

  1. Настройка источника созданием файла /etc/odbc.ini или добавлением в него секции: 
    [MSSQL]
Driver = ODBC Driver 18 for SQL Server
Description = Test database on linux MSSQL Server
Trace = No
Server = 127.0.0.1
Port = 1433
TrustServerCertificate = yes
TDS version = 0.95
  2. Проверка источника по имени секции (MSSQL) из /etc/odbc.ini: 
    echo 'SELECT name FROM sys.databases' | isql MSSQL sa 'Pa$$word' -b -v
+--------------------------------------------------------------------+
| name                                                               |
+--------------------------------------------------------------------+
| master                                                             |
| tempdb                                                             |
| model                                                              |
| msdb                                                               |
| test                                                               |
+--------------------------------------------------------------------+
SQLRowCount returns 0
5 rows fetched

Подключение из LibreOffice Base

Установка JDBC

Загрузить архив и разместить извлечённый оттуда jar-файл по нужному пути: 

$ wget -O ~/sqljdbc_13.2.tgz https://go.microsoft.com/fwlink/?linkid=2330477&clcid=0x419
$ tar -zxvf ~/sqljdbc_13.2.tgz sqljdbc_13.2/enu/jars/mssql-jdbc-13.2.0.jre11.jar -C /tmp/
# mv /tmp/sqljdbc_13.2/enu/jars/mssql-jdbc-13.2.0.jre11.jar /usr/share/java/

Подключение к базе

Дальнейшие действия выполняются от обычного пользователя.

При входе из LibreOffice Base по паролю не тестируйте подключение в мастере создания БД: это приводит к зависанию приложения.
Указание базы данных через / (как в подсказке) не работает [пример от Microsoft].
  1. По методике из статьи о Microsoft Access добавить в LO Writer раздобытый на предыдущем шаге класс:
    /usr/share/java/mssql-jdbc-13.2.0.jre11.jar
  2. Перезапустить LibreOffice.
  3. В LibreOffice Base выбрать подключение к базе через JDBC, заполнив поля источника и драйвера:
    URL источника данных (сервер 10.4.4.26, порт стандартный, БД test)
    sqlserver://10.4.4.26:1433;database=test
    Класс драйвера JDBC
    com.microsoft.sqlserver.jdbc.SQLServerDriver
    LibreOffice-MSSQL-JDBC.png
  4. Указать имя пользователя и необходимость пароля для подключения.
  5. Сохранить базу данных LO Base в виде файла.
  6. После чего можно работать на сервере MSSQL:
    LibreOffice-MSSQL-Connected.png

Ссылки

Установка служб SSIS

Активация агента сервера

Резервирование и восстановление БД MSSQL

Обратная связь