We want to share an Ansible Role to provision MSSQL-Server on Linux and some Know-How related to running it.
The Ansible Role focuses on the setup for Ubuntu Server.
It only supports one instance.
Ansible Galaxy: Role
ansible-galaxy install niceshops.mssql_linux
# OR
ansible-galaxy install git+https://github.com/niceshops/mssql-linux.git
Make sure to read into the limitations of MSSQL on Linux beforehand:
Install the Ubuntu-Server version as specified as supported in the MSSQL documentation linked above.
Make sure you prepare the target server filesystem as described in the best-practice guide (RAID, partition format, mounts).
The role will also check your existing mssql-related mounts for those best-practices.
You might also want to optimize the NIC configuration.
Pull the 'LCID' of for your preferred language to configure it.
The Role will install the 'SQL Server Integration Services' as shown in the documentation
SSIS on linux needs to execute its xml-based .dtsx packages using the dtexec binary.
We did not (yet) manage to successfully get maintenance-plans running using dtexec locally..
As a workaround we use a minimal SQL-Server installation on a Windows-Server to manage and run those scheduled tasks. Adding maintenance-plan and changing 'server connection' to remote MSSQL host. See: connect to MSSQL on Linux using SSMS
If you are using the 'SQL-Server Agent' you might want to configure database mail for it using the SSMS-GUI and add set/provision your 'agent_mail' and 'operator_mail' so it is used by the MSSQL instance!
If you want to run SSIS jobs/packages you need to:
- Copy the dtsx-packages to the target server
- Test the execution manually using the dtexec utility (make sure login is working and mssql-processes are spawned)
- Schedule the execution via systemd timer or cron
---
# see defaults/main.yml 'mssql_defaults' for all available settings
mssql:
agent: true # enable sql-server agent
fts: true # install full-text-search
ssis: true # install sql-server integration services
admin_script: true
# either version or license need to be set!
version: 'standard'
# license: 'YYYY-AAAAA-KKK-UUUU-OOOOO'
path:
config: '/var/opt/mssql' # is default
data: '/var/opt/mssql/data' # is default
index: '/var/opt/mssql/index' # is default
binlog: '/var/opt/mssql/binlog' # is default; for transaction-logs
backup: '/var/backups/mssql' # is default
open_file_limit:
soft: 16000
hard: 32727
settings:
network:
ip: '192.168.0.1'
port:
tcp: 1433
rpc: 13500
dtc: 51999
memory:
limit_mb: 10240
language:
lcid: 1031
agent_mail: 'local_postfix' # created via Studio-GUI
operator_mail: 'Admin' # created via Studio-GUI# status
systemctl status mssql-server.service
# logs
journalctl -u mssql-server.service -n 100
tail -n 100 /var/log/mssql/sql.error.log
tail -n 100 /var/log/mssql/agent.error.log
# stop
systemctl stop mssql-server.service
# restart
systemctl restart mssql-server.servicesystemctl stop mssql-server.service
apt update
apt upgrade --only-install mssql-*
# reboot or start
systemctl start mssql-server.serviceIf 'admin_script' is set to true => the role provisions a useful script to simplify administration!
You can enter the interactive MSSQL-shell using this command:
root@mssql:~# mssql.sh
>
> ####################
> Connecting to MSSQL-Server..
> ####################
>
> 1> Or run a single command:
root@mssql:~# mssql.sh "select login_time,hostname,program_name,cpu,physical_io,status,cmd from master..sysprocesses"
>
> ####################
> Connecting to MSSQL-Server to run query: 'select login_time,hostname,program_name,cpu,physical_io,status,cmd from master..sysprocesses'
> ####################
>
> login_time hostname program_name cpu physical_io status cmd
> ----------------------- ------------------------- ------------------------- ----------- -------------------- ------------------------- -------------------------
> 2023-04-22 07:14:20.537 5760 0 background XIO_RETRY_WORKER
> ......For useful commands see: Commands
You can also generate script-templates using the Management Studio GUI.