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:
To view volumes and devices you’ve added to the Linux instance, run
lsblkto 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:
Then run the following mssql-conf script to reset the 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/xvdc
- Create your directory. This example uses /SQLServerData:
- Mount the volume to the
/SQLServerDatadirectory using the following (we use the
/dev/xvdcvolume for this example):
- Change the owner and group of the directory to the mssql user:
Now that we have these steps done, we can change the default data directory using the set command. The
filelocation.defaultlogdirsettings change the location where the new database and log files are created. By default, this location is
- To change the settings, use the following steps (the example uses
/SQLServerDataas the default data directory):
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:
You can see from the screenshot preceding that
/SQLServerDatamount point. The steps include a similar mount point but for the default log directory.
- Format the volume to use the ext4 file system (the
xvddvolume is used for this example):
- Create a directory of your choice to mount the new ext4 volume (this example uses
/SQLServerLogfor the new directory name):
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
- With the volume mounted, change the owner and group of the directory to the
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
lsblkcommand: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-confscript to set the default log directory on the SQL Server instance. Do this by running the following command:
You see this message after setting the default directory:
- When you set the default log directory, you’re prompted to restart the
mssql-serverDo this by running the following:
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
- Run this command to change the default backup directory:
- When you’ve set all three default directories, you run
lsblkto 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.memorylimitmbsetting in the
mssql-confscript, with the memory limit should of 3328 MB:
- Run the following to perform a service restart, which needs to follow the preceding configuration change:
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:
- To disable a trace flag, run the following:
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.serviceby running the following:
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 (
xvdevolume is used for this example):
- Create a directory of your choice to mount the new ext4 volume. This example uses
/SQLServerTempDBfor the new directory name:
- Mount the volume of your choice to the new directory using the following command (this example uses the
- Change the owner and group of the directory to the
- 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:
- 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
/SQLServerTempDBdirectory mounted on
- 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.
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’
You are now ready to use SQL Server 2017 on the Linux AMI with best practices implemented for ease of administration.
About the Author
Bini Berhe is a solutions architect at Amazon Web Services.