Configuring SQL Server on Linux in Azure

Log in to the Azure Portal using the provided credentials.

• On the Home page, click Create a resource.
• Search for "SQL Server 2017" and hit enter.
• Filter the search by the criteria Operating system -> Redhat and Publisher -> Microsoft.
• Click on Free SQL Server License: SQL Server 2017 Developer on Red Hat Enterprise Linux 7.4 (RHEL).
• Click Create.
• Select the Resource Group created by the lab.
• Provide a Virtual machine name.
• Click Change size under Size.
• Select B2s and click Select.
• Choose Password for the Authentication type, then provide a Username and Password.
• Click Allow selected ports.
• Select SSH (22).
• Click Disks.
• Click Create and Attach a New Disk.
• Change the name to mssql_data.
• Click Change Size.
• Change the disk size to a custom value of "10" and click OK.
• Click OK.
• Repeat these steps twice more to create mssql_log and mssql_backups disks.
• Click Review + create.
• Verify that everything looks good and click Create.
• Once the deployment is complete, click Go to resource.

To allow incoming connections, follow these steps.

• On your resource page, click Networking in the left pane.
• Click Add inbound port rule.
• Leave all of the defaults, and change Destination port ranges to 50000.
• Change Protocol to TCP.
• Change Name to Port_50000.
• Click Add.

• On the resource page, click Connect at the top.
• Use the provided information to SSH to the server.

• Stop the mssql-server service. sudo systemctl stop mssql-server
• Change the SA password. sudo /opt/mssql/bin/mssql-conf set-sa-password
• Start the mssql-server service. sudo systemctl start mssql-server

Use the following commands to open the firewall port on the VM. Choose a port that won't conflict with others on the system.

sudo firewall-cmd --zone=public --add-port=50000/tcp --permanent
sudo firewall-cmd --reload

• Connect to the instance with SQLCMD.

 /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'AwesomePassword!'
 ```
* Create an admin account called `ACAdmin`.
 ``` SQL
 CREATE LOGIN ACAdmin WITH PASSWORD = 'AwesomePassword!'
 exec SP_ADDSRVROLEMEMBER 'ACAdmin','SYSADMIN';
 GO
 ```
* Log out of SA by typing `quit` and hitting enter.
* Connect to SQL Server again, this time as `ACAdmin`.
``` bash
 /opt/mssql-tools/bin/sqlcmd -S localhost -U ACAdmin -P 'AwesomePassword!'

• Disable the SA login

  ALTER LOGIN SA DISABLE;
  GO
  

• Use mssql-conf to set a new TCP port. sudo /opt/mssql/bin/mssql-conf set network.tcpport 50000

• Find the disks via dmesg (probably sdc, sdd, and sde). dmesg | grep SCSI
• Partition the disk with fdisk. sudo fdisk /dev/sdc
• At the Command line, enter n and press enter.
• For Partition type, choose p and press enter.
• Press enter to accept the default. Do this twice more until the system prompts for another command.
• Print the partition table with p, then write it with w.
• Write a file system to the disk. sudo mkfs -t ext4 /dev/sdc1
• Create a directory for the mount point.

sudo mkdir /var/opt/mssql/data/UserData

• Change the directory ownership to mssql.

sudo chown mssql:mssql /var/opt/mssql/data/UserData

• Mount the disk. sudo mount /dev/sdc1 /var/opt/mssql/data/UserData
• Add the drive to /etc/fstab.
• Get the UUID with blkid.

  sudo -i blkid
  

• Edit /etc/fstab.

  sudo vi /etc/fstab
  

• Add an entry similar to the one below, using your UUID.

  UUID=1c8e3964-b444-4243-ac76-88cb6ea0cf2b   /var/opt/mssql/data/UserData   ext4   defaults,nofail   0   0
  

• Save and exit.
• Repeat these steps for the log and backup drives.

• Change the default data path.

  sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/opt/mssql/data/UserData
  

• Change the default log path.

  sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /var/opt/mssql/data/UserLog
  

• Change the default backup path.

  sudo /opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /var/opt/mssql/data/Backups
  

• Restart the mssql-server service.

  sudo systemctl restart mssql-server
  

• To see the current mssql-conf configuration, run the following command.

sudo cat /var/opt/mssql/mssql.conf

• Connect from an external client (Azure Data Studio, VS Code or SQLCMD) using the new port number and user.
• Install SQLCMD.

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel

• Connect with SQLCMD.

/opt/mssql-tools/bin/sqlcmd -S <IPAddress>,50000 -U ACAdmin -P 'AwesomePassword!'

• Verify the SA account is disabled.

  SELECT Name, is_disabled
  FROM sys.server_principals;
  GO
  

• Create a new database, then list the contents of our new directories to verify the .mdf and .ldf files are placed there. CREATE DATABASE AwesomeCompany; GO

   sudo ls /var/opt/mssql/data/UserData
   sudo ls /var/opt/mssql/data/UserLog
  

• Back up the database and verify the backup file is placed in the new location. BACKUP DATABASE AwesomeCompany TO DISK = '/var/opt/mssql/data/Backups/AwesomeCompany.bkp'; GO

   sudo ls /var/opt/mssql/data/Backups