Author: Jeremy Winters, Principal Architect

Why SQL Server on Linux?

The big answer is that you can eliminate the cost of a Windows server license. Many UTurn customers are running SQL Server from AMIs made available by AWS with SQL Server pre-installed, and the license included in the cost. No need to cut a deal with Microsoft, just spin up the instance and go.

AWS provides Windows, Ubuntu, and RedHat Enterprise Linux (RHEL) AMIs with SQL Server Web pre-installed. At the time of this writing, the following were hourly, on-demand rates for an EC2 m5.4xlarge server:

SQL Server Web on Ubuntu saves 41% over the Windows license. RHEL is a bit more but still a solid savings. If you’re using RHEL, you probably have your reasons and are willing to pay the premium. RDS was included for contrast, as it costs 27% more than the Windows AMI. Of course, RDS includes other features that aren’t included in the AMI. As a general rule, I steer people toward RDS, but in my opinion, the best features of RDS are in Aurora, which doesn’t support any flavor of SQL Server.

Other reasons to switch to SQL Server on Linux include:

• SQL Server can run in a Linux docker container, enabling lighter weight CI/CD pipelines and local development patterns.
• The Linux install of SQL Server is straightforward using apt-get and dnf package managers. All steps can be fully automated using an unattended install script, if you need to do so.

In our case, we had a customer running in GovCloud. While SQL Server on Linux AMIs are available in GovCloud, they are previous versions of both the OS and SQL Server. We opted to use Ubuntu, deployed via Terraform, with a shell script install for SQL Server.

Restoring Data from Backup

The backups for restore to the Linux SQL Server instance were kept in S3. SQL Server allows backup directly to and from S3 using the FROM URL = ‘s3://bucket/db.bak’ syntax, but this requires setting up credentials inside the SQL Server. I didn’t want to generate keys, so I leveraged the IAM instance profile to pull the backups down to local using the aws s3 cp s3://bucket/db.bak /tmp/, then restore using FROM DISK = ‘/var/dbname.bak’.

SQL Server backups employ the concept of a logical file. The restore process expects the target filesystem to be identical to the paths in the source system. If you restore to a different filesystem path, the restore will fail.

You can identify the logical file names using this query:

RESTORE FILELISTONLY
FROM DISK = ‘/tmp/dbname.bak’
GO

Note the following fields in the output:

The LogicalName and PhysicalName give you all you need to remap the file locations upon restore using the MOVE syntax as shown below:

RESTORE DATABASE dbname
FROM DISK = N’/tmp/dbname.bak’
WITH FILE = 1,
MOVE ‘DBNAME’ to ‘/data/DBNAME.mdf’,
MOVE ‘DBNAME_log’ to ‘/data/DBNAME.ldf’,
NOUNLOAD, REPLACE, NORECOVERY, STATS = 5;
go

Is it really the same?

This Microsoft blog provides excellent insight into their use of a Platform Abstraction Layer to support multiple operating systems with a single SQL Server code base. Most of the unsupported features make sense given the OS differences.

For my part, the fact that they have a single code base for all OS versions gives me confidence that the core database functionality will be consistent. RedHat touts that SQL Server runs even faster on Linux.

The last mile

Replatforming the OS for SQL Server is one thing, but what about all of the legacy apps and processes running on the SQL Server host? While SSIS runs (with limitations) on Linux, SSAS and SSRS are not supported at all, requiring you to continue running Windows hosts.

The trickier problems to deal with are the custom code and scripts you may be running on the Windows host. The solutions here are as varied as the legacy processes, but can typically be addressed using one of the following approaches:

• Continue running the app on Windows! Often, you can simply change the hostname to point to your new Linux SQL Server.
• .NET core applications can be run on Linux. Optionally, they can be run in a Linux docker container, which can be launched using AWS Fargate.
• For non-.NET core Windows applications, it is possible to run docker using Windows OS containers in AWS Fargate.
• Scheduled Tasks can be facilitated using services such as Amazon EventBridge and AWS Step Functions.

Conclusion

If you are currently paying Windows OS license fees for your SQL Server environment in AWS, it is definitely worth considering the move to Linux. Of course, you’ll want to make sure you have the skills in-house to manage the servers. General Linux server admin is relatively low touch if you are only running the SQL Server app but features such as high-availability will require deeper Linux knowledge.