How to configure SQL Server 2017 on Amazon Linux 2 and Ubuntu AMIs

When you deploy Microsoft SQL Server on AWS, you have many choices for how to optimize the performance, availability, reliability, and costs of your applications. Amazon offers multiple SQL Server versions, broad compute options, and numerous licensing options to optimize usage and reduce costs. You can choose the pay-as-you-go model and use the AWS license-included options, or you can choose to bring your own license (BYOL) on Amazon EC2.

With SQL Server 2017 available on EC2 instances, you have great flexibility to deploy your SQL Server–based applications on either Microsoft Windows or Linux operating systems running on Amazon EC2. AWS today announced support for Microsoft SQL Server on Amazon Linux 2 and Ubuntu by offering license-included Amazon Machine Images (AMIs). With this release, you can launch your SQL Server 2017 on Amazon Linux 2 LTS Candidate and Ubuntu 16.04 (HVM) instances on demand using license-included AMIs directly from the EC2 console. The AMIs are available in all four editions—SQL Server Web Edition, SQL Server Express Edition, SQL Server Standard Edition, and SQL Server Enterprise Edition.

This blog post discusses the differences in management between traditional Windows-based configurations and the new Linux SQL Server installations. The post also walks you through the steps to configure SQL Server running on a Linux AMI on Amazon EC2.

This post assumes that you have configured a workstation in your VPC to connect to the SQL Server instance you install as part of this blog post. We recommend that you use SQL Server Management Studio (SSMS) from that workstation. You can download the SSMS bits from this site.

Differences in configuring SQL Server on Linux and Windows

Configuring SQL Server when it’s running on Linux is different from configuring SQL Server running on Windows. In the traditional Windows environment, you use the SQL Server Configuration Manager to manage changes to SQL Server services, TCP ports, startup parameters, or any other instance-level configuration items.

For Linux-based SQL Server workloads, Microsoft has made a configuration script named mssql-conf available. The mssql-conf script is used to set parameters for things such as the default data and log file location, TCP ports, and so on.

Let’s get started with the configuration steps.

Before you begin

Before you begin, there’s some setup:

When you’ve successfully connected to the Linux EC2 instance running SQL Server, execute the following so you can run commands using the root user account:
```
sudo su
```
To view volumes and devices you’ve added to the Linux instance, run lsblk to see a list of all Amazon EBS volumes that you attached during launch.
Because this AMI includes SQL Server 2017, you reset the SA password for the SQL Server instance. To do this, first stop the SQL Server instance by using the following command:
```
sudo systemctl stop mssql-server
```
Then run the following mssql-conf script to reset the SA password:
```
sudo /opt/mssql/bin/mssql-conf set-sa-password
```

1. Create a target directory and change the default directory

As a best practice, we recommend isolating data files from log files for the given SQL Server instance. Before you can change the default directory locations, you first create a target directory for new database data and log files.

The following steps create a new directory named /SQLServerData. But before doing so, a file system needs to be formatted, a directory created, and that directory mounted to the device (volume).

Format the volume of your choice to use the ext4 file system using the following:
```
sudo mkfs –t ext4 /dev/devicename
```
For example: sudo mkfs -t ext4 /dev/xvdc
Create your directory. This example uses /SQLServerData:
```
sudo mkdir /SQLServerData
```
Mount the volume to the /SQLServerData directory using the following (we use the /dev/xvdc volume for this example):
```
sudo mount /dev/xvdc /SQLServerData
```
Change the owner and group of the directory to the mssql user:
```
sudo chown mssql /SQLServerData
sudo chgrp mssql /SQLServerData
```
Now that we have these steps done, we can change the default data directory using the set command. The filelocation.defaultdatadir and filelocation.defaultlogdir settings change the location where the new database and log files are created. By default, this location is /var/opt/mssql/data.
To change the settings, use the following steps (the example uses /SQLServerData as the default data directory):
```
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /SQLServerData
```
After you complete the preceding step, SQL Server needs to be restarted to apply the new settings. Before you do so, let’s also mount a different volume to a directory specifically for database log files.
Run the following command to list the disks you attached to your instance:
```
lsblk
```
You can see from the screenshot preceding that /dev/xvdc has the /SQLServerData mount point. The steps include a similar mount point but for the default log directory.
Format the volume to use the ext4 file system (the xvdd volume is used for this example):
```
sudo mkfs -t ext4 /dev/xvdd
```
Create a directory of your choice to mount the new ext4 volume (this example uses /SQLServerLog for the new directory name):
```
sudo mkdir /SQLServerLog
```

2. Mount your volume and set the default log directory

Now that the directory is created, mount the volume of your choice to the new directory.

Mount the volume using the following command (this example uses the /dev/xvdd volume):
```
sudo mount /dev/xvdd /SQLServerLog
```
With the volume mounted, change the owner and group of the directory to the mssql user:
```
sudo chown mssql /SQLServerLog
sudo chgrp mssql /SQLServerLog
```
With the directory created and volume mounted, you should now have two locations. One directory is for the database data file, and the other is for the database log files.
To list your block devices for viewing current mount points, run the lsblk command:You can see from the screenshot preceding that we have two specific mount points now, for database data and for log files.
Now that we have a directory for log files, you can use the mssql-conf script to set the default log directory on the SQL Server instance. Do this by running the following command:
```
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /SQLServerLog
```
You see this message after setting the default directory:
When you set the default log directory, you’re prompted to restart the mssql-server Do this by running the following:
```
sudo systemctl restart mssql-server
```

3. Change your default backup location

To change the default backup location, follow the same steps you performed for creating the file system. These are creating the directory, mounting the volume to the directory, changing ownership and group for the mount point, changing the default location, and restarting the mssql-server service.

Run this command to change the default backup directory:

sudo /opt/mssql/bin/mssql-conf set defaultbackupdir /YourNewBackupDirectory

When you’ve set all three default directories, you run lsblk to view which devices and volumes are tied to a mount point. The result should look like the following:
Open SSMS, open the context (right-click) menu for the server, and choose Properties:
Choose the Database Settings tab, and view what the new database default locations read.This tab should read and reference the directories you created and mounted for the purpose of separating the location of database data, log, and backup files:

4. Set memory consumption as needed

If setting the amount of memory that SQL Server can consume on the Linux instance is important to your application, you can do so by using the memory.memorylimitmb setting in the mssql-conf script.

Run the following to use the memory.memorylimitmb setting in the mssql-conf script, with the memory limit should of 3328 MB:
```
sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 3328
```
Run the following to perform a service restart, which needs to follow the preceding configuration change:
```
sudo systemctl restart mssql-server
```

5. Enable or disable trace flags

You can also enable and disable trace flags for the startup of the SQL Server service. We recommend that you set trace flag 1222, which is helpful for diagnosing deadlocks.

To enable and disable trace flags, use the following commands:
- To enable a trace flag, run the following:
```
sudo /opt/mssql/bin/mssql-conf traceflag 1222 on
```
- To disable a trace flag, run the following:
```
sudo /opt/mssql/bin/mssql-conf traceflag 1222 off
```
When a trace flag set using the preceding command, it is globally configured so that it is included every time the SQL Server service is restarted (startup parameter for mssql-server.service).
Restart mssql-server.service by running the following:
```
sudo systemctl restart mssql-server
```

6. Isolate the TempDB log and data files

So that unexpected growth to the database doesn’t affect the operating system, we recommend isolating the TempDB database log and data files on a different directory and different volume. To do this, follow these instructions:

Format the volume to use the ext4 file system (xvde volume is used for this example):
```
sudo mkfs -t ext4 /dev/xvde
```
Create a directory of your choice to mount the new ext4 volume. This example uses /SQLServerTempDB for the new directory name:
```
sudo mkdir /SQLServerTempDB
```
Mount the volume of your choice to the new directory using the following command (this example uses the /dev/xvde volume):
```
sudo mount /dev/xvdd /SQLServerTempDB
```

Change the owner and group of the directory to the mssql user:

sudo chown mssql /SQLServerTempDB
sudo chgrp mssql /SQLServerTempDB

To move the TempDB files to a new location, do as you normally do with SQL Server on Windows with TempDB files, as follows.To open an SSMS query window, open SSMS, open the context (right-click) menu for the server, and choose New Query:
Run the following command to query where your current TempDB data and log files reside:As you can see from the screenshot preceding, the TempDB data and log files reside within the same directory.
Change the location of each file by using ALTER DATABASE:
Stop and restart the instance of SQL Server by running the following:
```
sudo systemctl restart mssql-server
```
To confirm the modification, run the same query you ran in step 4.You can see from the screenshot following that we have now successfully moved the TempDB over to the /SQLServerTempDB directory mounted on /dev/xvdd.
10. To make sure that your mounts persist through a system reboot, add in an entry for the device to /etc/fstab file. You can run ‘df’ command to get a list of all mount point information in regards to their device names, space used, etc. You can use the system’s current device name (/dev/sda1, /dev/xvda1, etc.) in /etc/fstab, but we recommend using the device’s 128-bit universally unique identifier (UUID) instead.
```
Run 'file –s /devicename' command to get the UUID
```
Add a new line to the top of the fstab file, paste in the specific UUID for the device, add the mount point (this example uses /SQLServerData), enter the filesystem type (ext4 for this example), and the mount options (reference step 7c for more information in https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-using-volumes.html):

Repeat the above steps for every mount point and device you wish to persist through reboots. To confirm that the devices have been successfully saved onto the /etc/fstab file, run ‘cat /etc/fstab’