Posted Sunday, 23 February 2020 by Jeremy Cook
SQL Server on Ubuntu 16.04
Install and configure mssql
Create and mount mssql volume
Create a Digital Ocean volume. In the following examples it appears as volume_sfo2_03
or ``volume-sfo2-03`.
sudo su -
mkdir -p /mnt/mssql
mount -o discard,defaults,noatime /dev/disk/by-id/scsi-0DO_Volume_volume-sfo2-03 /mnt/mssql
echo '/dev/disk/by-id/scsi-0DO_Volume_volume-sfo2-03 /mnt/mssql ext4 defaults,nofail,discard 0 0' | sudo tee -a /etc/fstab
Configure mssql default directories
sudo su -
# Backup to the mounted drive
mkdir -p /mnt/mssql/backup
chown -R mssql:mssql /mnt/mssql/backup
chmod 770 /mnt/mssql/backup
/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /mnt/mssql/backup
# OPTIONALLY, store data in a mounted drive
mkdir -p /mnt/mssql/data
chown -R mssql:mssql /mnt/mssql/data
chmod 770 /mnt/mssql/data
/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /mnt/mssql/data
# OPTIONALLY, log to a mounted drive
mkdir -p /mnt/mssql/log
chown -R mssql:mssql /mnt/mssql/log
chmod 770 /mnt/mssql/log
/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /mnt/mssql/log
systemctl restart mssql-server.service
Use NGINX and certbot to generate certificates
sudo su -
# Install NGINX
apt update
apt install nginx
# Create NGINX stub site
nano /etc/nginx/sites-available/data1.flipforms.com
Paste this into nano and save.
server {
listen 80;
listen [::]:80;
server_name data1.flipforms.com;
return 404 'ignore mode';
}
sudo su -
# Enable the stub site
ln -s /etc/nginx/sites-available/data1.flipforms.com /etc/nginx/sites-enabled/data1.flipforms.com
# Disable the default site
rm /etc/nginx/sites-enabled/default
# Test the config and restart NGINX to apply the changes
nginx -t
systemctl restart nginx.service
# Install Certbot - https://certbot.eff.org/lets-encrypt/ubuntuxenial-nginx
apt-get update
apt-get install software-properties-common
add-apt-repository universe
add-apt-repository ppa:certbot/certbot
# press ENTER to agree
apt-get update
apt-get install certbot python-certbot-nginx
# Configure certbot and NGINX to work together
certbot --nginx
# Create a script that copies the Let's Encrypt certificates for MSSQL to use
nano /opt/flipforms-scripts/copy-mssql-letsencrypt-certs.sh
Paste this into nano and save.
cp /etc/letsencrypt/live/data1.flipforms.com/fullchain.pem /etc/letsencrypt/live/data1.flipforms.com/privkey.pem /var/opt/mssql/secrets/
chown mssql:mssql /var/opt/mssql/secrets/fullchain.pem /var/opt/mssql/secrets/privkey.pem
chmod 400 /var/opt/mssql/secrets/fullchain.pem /var/opt/mssql/secrets/privkey.pem
systemctl restart mssql-server
sudo su -
chmod 770 /opt/flipforms-scripts/copy-mssql-letsencrypt-certs.sh
# Run it and make sure there are no errors
/opt/flipforms-scripts/copy-mssql-letsencrypt-certs.sh
# Have root run it weekly
crontab -e
0 0 * * 1 /opt/flipforms-scripts/copy-mssql-letsencrypt-certs.sh
# Update mssql TLS settings
cat /var/opt/mssql/mssql.conf
/opt/mssql/bin/mssql-conf set network.tlscert /var/opt/mssql/secrets/fullchain.pem
/opt/mssql/bin/mssql-conf set network.tlskey /var/opt/mssql/secrets/privkey.pem
/opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
/opt/mssql/bin/mssql-conf set network.forceencryption 1
systemctl restart mssql-server.service