Deploy MS SQL Server 2019 on Amazon Linux 2 at scale
Microsoft SQL Server 2019 has been available on AWS since November 2019 [1]. I use SQL Server and I have always wanted to try it on Amazon Linux. I prefer his combination because it is cheaper running it on Windows (AWS m5.xlarge Linux SQL Standard is about 20% cheaper that the same Windows based OS) and a lot lighter anyway. This would allow you to get more compute per dollar. Linux and SQL server can be sometimes daunting to configure. As a result, I did a bit of research and selected just the right amount of information to configure not just the Linux SQL image, but also the databases and launch template.
My Use Case
A client stores project data in SQL Database. My story is a success and more clients want the same service, but they have different requirements for the amount of data they require to store and for the performance of the database. I needed to create the different server solutions for each tier of my service. The requirements were to create servers reliably, that can satisfy the needs of the service provided and that are cost optimized as much as possible.
My Server Configuration
I started with Amazon Linux 2 LTS [2] with SQL Server 2019 Standard. I recommend starting with a 4 CPU 16GB RAM instance at the minimum (t3.xlarge would be adequate for setup).
For best practice, 4 additional volumes are needed to store the data, the logs, the backups and the temporary database, independently of each other. I recommend keeping them small for now and choosing different sizes to ease identification. I picked SSD (gp2) volumes of 5, 6, 7 and 8 GB, respectively. Leave the size of the root volume unchanged (30 GB).
I created a new Security Group which allows inbound traffic on port 1433 (MS SQL) to itself. All future instances placed in this group can communicate on this port. To connect to the instance I used SSH. This requires inbound access on port 22 from my IP. Alternatively, I can connect using the Session Manager [3] by attaching a role to the instance with the AmazonSSMManagedInstanceCore Policy. This is useful to do because, the instance will be listed as a ‘Managed Instance’ in AWS Systems Manager. The SSM agent is already installed on Amazon Linux 2 [4].
There are many ways to configure a SQL server. I have rearranged and added to the instructions provided in this article [5].
When the instance came into service (which is quickly because of the small volume sizes), I logged in and elevated to root by typing: sudo su -
The new volumes needed to be formatted and mounted correctly. The instance device listing from the AWS console does not always match with the listing provided with lsblk command:
Fortunately, I created volumes of different sizes, which helped me. I issued the following commands to format volumes into file systems, create mount points, mount the file systems (needless to say that any data on those volumes would be destroyed). The ownership of each mount point had to be changed to mssql, because this are the username and group employed by SQL Server. Finally the unique identifiers for each file system were printed to be included in the file system table.
A sample of my outputs is shown below, demonstrating correct operation:
File systems are mounted automatically at the next reboot/instance stop only if they are listed in the File System Table. These are located in the /etc/fstab file. Using the outputs of the df -h command and file -s /dev/devicename commands together, I associated each UUID and mount point, cross-referencing /dev/devicename. Based on this information, I edit the File System Table to add the last 4 lines representing the UUIDs and mount points:
The nofail flag ensures that the instance can still boot if the volume is not attached. I re-verified the information visually, by making sure that each UUID and mount point share the same device name (/dev/devicename). Finally I tested my file system settings in two ways:
— by un-mounting all points and mounting everything back and receiving no errors (errors need to be addressed or the instance may never boot again)
— by comparing the output of the df -h command before and after, ensuring the correct mount point matches the correct file system size.
This is what I have run (for details [6]):
Satisfied with my results, I started configuring the actual MS SQL Server through the mssql-conf script. I changed the sa password, the default file locations to the correct mount points, set the memory limit to 14 GB and enabled a trace flag to help identifying deadlocks. Finally, I restarted the service.
At this point a connection can be attempted to the server to check the settings, finish the setup and create & configure databases.
My Database Configuration
I like to use the SQL Server Management Studio (SSMS) to connect to my SQL server. I do this by installing SSMS on a Windows AMI, which runs on an instance (t2.micro) in the same security group as my SQL server instance. I used the private DNS name of the server and the sa credentials to connect.
First, I checked if I configured the SQL Server correctly. Right-click on the Server name in the Object Explorer Window and select Properties. Check the general details (including allocated memory) and the database default locations.
I migrated the TempDB from the root to the new dedicated file system by running the following query:
…and confirmed the new settings by running the following query, after I had restarted the mssql-server service on the server (see the previous section).
Next, I configured my SQL users and created my databases using Entity Framework scripted migrations. Finally, I sometimes run code on the server that is started automatically at the machine boots up. My code performs queries on the databases. The code runs under the ec2-user credentials, while all SQL queries are performed under the mssql credentials. As a result, the ec2-user needs to be added to the mssql group and its home directory permissions changed from 700 to 755 [7], so that it would be traversed by the code running under mssql.
Had I granted to many rights such as mode 777, the next login attempt for ec2-user would have failed. Should this happen, I would connect using the Session Manager and run the last command in the script with mode 700. This resets the permissions to their defaults. There is no substitute for testing.
My Monitoring
I like to use CloudWatch metrics that are available through the CloudWatch (CW) agent to monitor the used memory and used disk space. I attach the following policies to my instance role in addition to the AmazonSSMManagedInstanceCore Policy: AmazonEC2ReadOnlyAccess, CloudWatchReadOnlyAccess and CloudWatchAgentServerPolicy. These can edited to suit specific needs and security requirements at a later stage.
The CW agent may need updating or installing on the EC2 instance. The easiest way to use Systems Manager (SSM) Run Command to deploy it. I used the AWS-ConfigureAWSPackage command document to install the AmazonCloudWatchAgent package [8].
I have also made sure that my server (instance) has outbound internet access. This is necessary to be able to send data to CloudWatch and CloudWatch logs. The CW agent needs to be configured before starting it. This can be done by pushing an existing configuration file or by creating a new configuration file by using a wizard. Both ways involve deploying the configuration file and starting the CW agent.
The wizard needs to be launched on the actual instance. The procedure is simple enough. I requested Basic Level metrics to get the percentage memory and disk used. I have also requested all dimensions possible. At the end, I was asked to name the configuration file and if I wanted to upload it to SSM Parameter Store.
There are restrictions in place to upload the file to Parameter Store. Specifically, the file name should start with AmazonCloudWatch- and the role attached to the instance should have the administrative CloudWatchAgentAdminPolicy, rather than the operations CloudWatchAgentServerPolicy attached (see first Note in [9]). The file is uploaded to the Parameter Store as a plain (unencrypted) string type parameter.
I launched the wizard as many times as necessary until I was satisfied with the contents of my configuration file [10]. I edited and stored it manually as a parameter string (CW-Linux-UsedDiskSpace) in SSM Parameter Store.
To deploy this configuration file to CW agent, I used the AmazonCloudWatch-ManageAgent command document as shown in the Run Command History
Once this is done successfully, the requested metrics can be queried in CloudWatch under the CWAgent namespace. Detailed (1 min) monitoring rather than basic (5 min) monitoring should be enabled for a SQL Server, from the EC2 instance properties.
My Template
It is good practice to automate as much as possible. At this stage, I felt that I had the opportunity to capture a golden AMI that I could subsequently reuse from a launch template. I stopped my instance, created an AMI and then created a template from instance. I checked the following settings in particular, that: the security groups and role associated were correct and both EBS optimized and detailed monitoring were on. Volumes are not included in the launch template unless they are modified, otherwise the default settings from the AMI are used.
This is very useful because I can set the size of each volume or the encryption status, based on the project needs. A couple of points about encryption:
- enabled at the very last stage in a different version of the template
- once enabled, any subsequent snapshots are encrypted
- if snapshot sharing is required, then the encryption key must be shareable too; as a result, the default EBS key is a no go.
I set up the server using data volume ranging from 5 to 8 GB in size. While volumes sizes can be easily increased at any stage additional commands are needed to make the allocated space usable. These are captured in the User Data Section of the template, under Advanced Details [11]. I have also included other commands from my project (such as copying files from S3), which are not shown here.
I like to tag everything (very useful for automation and billing). In this case I focused on storage devices and their mount points.
Encryption can be enabled in the next version of the template.
Finally, launched an instance from the template and I confirmed the settings, the metrics and that the disk sizes were correct.
Clean Up
I deleted my instance and my data volumes. I kept my AMI, snapshots, launch template and CloudWatch configuration file stored in Systems Manager Parameter Store, as well as the IAM Role and Policies.
Limitations of SQL Server 2019 on Linux
This article was written using the Cumulative Update 3 (CU3) release of SQL Server 2019 (15.x). The SQL Server Database Engine version for this release is 15.0.4023.6. CU4 was released in April 2020 by Microsoft.
The master database cannot be moved with the mssql-conf utility. Other system databases can be moved with mssql-conf.
User permission ADMINISTER BULK OPERATIONS is not supported on Linux at this time.
For more, I recommend reading the release notes for SQL Server 2019 on Linux from Microsoft [12].
Summary
I identified the requirements of my use case. I created and configured a SQL 2019 Server on an Amazon Linux AMI. I implemented best practices, by configuring different volumes to serve different purposes. I implemented monitoring and automation by using System Manager and EC2 Launch Templates. The volume sizes (and encryption setting) can be adjusted in the launch template to serve all tiers of my service at scale.
Further Reading
[1] AWS support for MS SQL 2019
[2] Amazon Linux 2 LTS Candidate 2 Release Notes
[5] How to configure SQL Server 2017 on Amazon Linux 2 and Ubuntu AMIs
[6] Automatically Mount an Attached Volume After Reboot
[7] Chmod: Numerical Permissions
[9] Create IAM Roles and Users for Use with the CloudWatch Agent
[10] Run the CloudWatch Agent Configuration Wizard
