Deploy SQL Server Container Clusters using Amazon EKS and Amazon FSx for Windows

Introduction

This blog post shows how to deploy a highly available SQL Server instance in a container using Amazon Elastic Kubernetes Service (Amazon EKS) with persistent storage backed by Amazon FSx for Windows File Server.

Running databases and other stateful workloads in containers has grown significantly over the years. According to the CNCF Annual Survey 2024, 74% of organizations are using containers to manage stateful applications, up 10% from 2023. Running SQL Server in containers offers several benefits. You can quickly create and start multiple SQL Server instances for development or testing, leveraging rapid parallel workflows. Containers also maximize resource density, allowing multiple instances to run efficiently on the same host which is ideal for microservices architectures in test or production environments. And when you run SQL Server in containers, you save up to 38% on Windows licenses since SQL Server is supported only in Linux containers.

Cost savings example

Besides the various benefits highlighted earlier regarding running SQL Server in containers, here is an example of the Linux vs. Windows annual cost comparison with a 16 core, r6idn.4xlarge on-demand Amazon Elastic Compute Cloud (Amazon EC2) instance.

Operating System	Instance type	Annual cost
Linux	r5b.4xlarge	$10,441.929
Windows	r5b.4xlarge	$16,889.28

Based on this example, there is over 38% savings simply by running SQL Server on Linux. (This is accurate as of June 24, 2025, in the us-east-1 region.)

Solution overview

We use a SQL Server container image to deploy SQL Server as a Kubernetes StatefulSet in an Amazon EKS cluster, with FSx for Windows File Server providing persistent storage for the SQL Server pods. Amazon EKS orchestrates the resources in the cluster and natively provides high availability for the pods. If a SQL Server container (instance) fails, Amazon EKS automatically bootstraps a new SQL Server container in a new pod and reconnects the FSx for Windows File Server. If a node fails, Amazon EKS creates a new pod on a healthy node and attaches the storage. This Kubernetes-managed approach ensures the cluster remains resilient to SQL Server instance and node failures and maintains continuous database access.

Architecture diagram of SQL Server on Amazon EKS and Amazon FSx for Windows

Figure 1: SQL Server on Amazon EKS and Amazon FSx for Windows

Storage options for SQL Server containers on AWS

AWS provides different persistent storage options for containerized SQL Server on Amazon EKS. They include Amazon Elastic Block Store (Amazon EBS), Amazon FSx for Windows File Server and Amazon FSx for NetApp ONTAP.

Amazon EBS natively provides persistent storage for Amazon EKS stateful applications within a single availability zone. Therefore, pods created in nodes in the same availability zone can connect to the Amazon EBS volumes to provide persistence across pod restarts and replacements. Multi-AZ storage persistence options for Amazon EKS workloads using Amazon EBS are available through third-party solutions like Portworx and DH2i.

Native Multi-AZ persistent storage for your containerized SQL Server workloads is available on Amazon FSx for Windows File Server or Amazon FSx for NetApp ONTAP. These storage solutions provide capabilities for Amazon EKS pods to connect to persistent storage when scheduled on nodes running in multiple availability zones in your VPC. To know more about implementing Amazon FSx for NetApp ONTAP, read the article Run containerized applications efficiently using Amazon FSx for NetApp ONTAP and Amazon EKS. We will talk about leveraging Amazon FSx for Windows File Server in this post.

Key considerations

For production databases, Microsoft supports SQL Server containers only on Linux and recommends deploying one SQL Server instance container per pod in a Kubernetes cluster. To achieve this, set the “replicas” value to 1 in the manifest when deploying the StatefulSet for the SQL server on Amazon EKS. Using the StatefulSet Kubernetes workload type for SQL Server provides identity stickiness. This guarantees the assignment of a stable, unique, and persistent identity on each pod in the StatefulSet, even when rescheduling, replacing, or scaling pods on Amazon EKS.
When creating a Multi-AZ, Amazon FSx for Windows File Server, it provides a preferred File Server and a Standby File Server for accessing the file system. Multi-AZ FSx File Servers automatically fails over from the preferred to the standby file server during zonal service events, planned maintenance, or if the preferred file server becomes unavailable. However, this only applies on Windows clients by default. Linux clients do not support automatic DNS-based fail overs and don’t automatically connect to the standby file server during fail over or resume file system operations after the Multi-AZ file system fails back to the preferred file server. Because of this, SQL Server on Linux containers using FSx for Windows File Server will experience disruption during maintenance operations. As part of the proposed solution, I have provided a mechanism to mitigate this limitation in the walkthrough section.

Prerequisites

The steps in the walkthrough assume that you have:

An Amazon EKS cluster. Otherwise, use our guide to create an Amazon EKS cluster.
A Multi-AZ Amazon FSx for Windows File Server. Otherwise, use our guide to create your file system.
An Active Directory user account with read/write permissions to the FSx for Windows File Server.
Installed Helm for deploying the SMB CSI Driver.
A license to run Microsoft SQL Server on AWS. We will use SQL Server Developer Edition container image, which is free to use for development and testing purposes.

Walkthrough

In the following walkthrough, we will go step by step to deploy an SQL Server instance in the Amazon EKS cluster.

1. Install the SMB CSI Driver which allows Kubernetes to access SMB servers (in this case our Amazon FSx for Windows File Server) on both Linux and Windows nodes.

helm repo add csi-driver-smb https://raw.githubusercontent.com/kubernetes-csi/csi-driver-smb/master/charts
helm install csi-driver-smb csi-driver-smb/csi-driver-smb --namespace kube-system --set windows.enabled=true --version v1.17.0

2. Create a Kubernetes secret to store the SMB credentials for accessing FSx. The SQL Server pods will require valid credentials to connect to the Amazon FSx for Windows File Server for reading and writing data. In this step, we will create a Kubernetes secret that contains an Active Directory username and password with the required read/write permissions. Refer to the Amazon EKS documentation to read about best practices for managing Kubernetes secrets on AWS.

kubectl create secret generic fsx-creds --from-literal domain=YourADDomain --from-literal username=UserNameForFsx --from-literal password=<YourPasswordGoesHere>

3. Create a password for the the sa user for accessing the SQL Server instance. This password must meet the recommended password policy.

kubectl create secret generic mssql-creds --from-literal=MSSQL_SA_PASSWORD=<YourSAPassword>

4. Use the mssql.conf file to create a ConfigMap in Kubernetes. First, define your SQL Server configuration in the mssql.conf file.

[EULA]
accepteula = Y
accepteulaml = Y

[filelocation]
defaultdatadir = /var/opt/mssql/userdata
defaultlogdir = /var/opt/mssql/userlog

[memory]
memorylimitmb = 28672

[control]
WriteThrough=1
AlternativeWriteThrough=0

[traceflag]
traceflag0=3979

Second, use the kubectl create configmap command with the –from-file flag to create a ConfigMap from the mssql.conf file. The SQL Server pods will use this ConfigMap to access the configuration settings.

kubectl create configmap mssqlconf --from-file mssql.conf

5. Provision the storage. To provide storage for the SQL Server containers, we will create a Storage Class in Amazon EKS. A storage class defines how to provision Amazon FSx for Windows File Server automatically when the pod requests persistent storage. Replace the “source” value with the fully qualified domain name of your Amazon FSx for Windows File Server in the following manifest file and save it as fsx-smb.yaml.

apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
name: fsx-smb
provisioner: smb.csi.k8s.io
parameters:
source: "//amznfsxa7ko7pfq.company.com/share/sql" # Use the FQDN provided by Amazon FSx for Windows File Server
csi.storage.k8s.io/provisioner-secret-name: "fsx-creds"
csi.storage.k8s.io/provisioner-secret-namespace: "default"
csi.storage.k8s.io/node-stage-secret-name: "fsx-creds"
csi.storage.k8s.io/node-stage-secret-namespace: "default"
reclaimPolicy: Retain
volumeBindingMode: WaitForFirstConsumer
mountOptions:
- dir_mode=0777
- file_mode=0777
- uid=1001
- gid=1001

Then run this command to create the Storage Class:

kubectl apply -f fsx-smb.yaml

6. Deploy the StatefulSet for SQL Server. Create a new text file, add the following code, and save it as mssql-server.yaml.

apiVersion: apps/v1
kind: StatefulSet
metadata:
 name: mssql
 labels:
  app: mssql
spec:
 serviceName: "mssql"
 replicas: 1
 selector:
  matchLabels:
   app: mssql
 template:
  metadata:
   labels:
    app: mssql
  spec:
   securityContext:
     fsGroup: 10001
   containers:
   - name: mssql
     command:
       - /bin/bash
       - -c
       - cp /var/opt/config/mssql.conf /var/opt/mssql/mssql.conf && /opt/mssql/bin/sqlservr
     image: mcr.microsoft.com/mssql/server:2022-latest
     resources:
      limits:
       memory: 2Gi
       cpu: '2'
     ports:
     - containerPort: 1433
     env:
     - name: MSSQL_PID
       value: "Developer"
     - name: ACCEPT_EULA
       value: "Y"
     - name: MSSQL_ENABLE_HADR
       value: "1"
     - name: MSSQL_SA_PASSWORD
       valueFrom:
         secretKeyRef:
          name: mssql-creds
          key: MSSQL_SA_PASSWORD
     volumeMounts:
     - name: mssql
       mountPath: "/var/opt/mssql"
     - name: userdata
       mountPath: "/var/opt/mssql/userdata"
     - name: userlog
       mountPath: "/var/opt/mssql/userlog"
     - name: tempdb
       mountPath: "/var/opt/mssql/tempdb"
     - name: mssqlconf
       mountPath: "/var/opt/config"
   volumes:
     - name: mssqlconf
       configMap:
        name: mssqlconf
 volumeClaimTemplates:
   - metadata:
      name: mssql
     spec:
      accessModes:
      - ReadWriteOnce
      resources:
       requests:
        storage: 8Gi
      storageClassName: "fsx-smb"
   - metadata:
      name: userdata
     spec:
      accessModes:
      - ReadWriteOnce
      resources:
       requests:
        storage: 8Gi
      storageClassName: "fsx-smb"
   - metadata:
      name: userlog
     spec:
      accessModes:
      - ReadWriteOnce
      resources:
       requests:
        storage: 8Gi
      storageClassName: "fsx-smb"
   - metadata:
      name: tempdb
     spec:
      accessModes:
      - ReadWriteOnce
      resources:
       requests:
        storage: 8Gi
      storageClassName: "fsx-smb"
---
apiVersion: v1
kind: Service
metadata:
  name: mssql-server
spec:
  selector:
    app: mssql
  ports:
    - protocol: TCP
      port: 1433
      targetPort: 1433
  type: ClusterIP

And then run this command to create the StatefulSet:

kubectl apply -f mssql-server.yaml

7. View the status of your SQL Server instance in the Amazon EKS cluster. After deploying the SQL Server container successfully in the Amazon EKS cluster, execute these commands to see the StatefulSet, pod, and SQL Server service endpoint within the cluster.

kubectl get statefulset
kubectl get pods
kubectl get service

Viewing the StateFulSet, pods and service on the command line.

Figure 2: Viewing the StateFulSet, Pod and Service for SQL Server

In this example, the SQL Server service endpoint is accessible at 10.100.40.100. We can now connect to the SQL Server instance using standard SQL Server tools like sqlcmd and SSMS.

Using sqlcmd to connect to the SQL Server container instance.

Figure 3: Connecting to the SQL Server container using sqlcmd utility

Connecting to the SQL Server container instance from SSMS

Figure 4: Connecting to the SQL Server container using SSMS

8. Mitigating the Linux client fail over on Amazon FSx for Windows File Server. To mitigate the fail over scenario we discussed earlier about Linux clients, run the following script, which does the following:

a. Sets the Time-To-Live (TTL) of the DNS records of the Amazon FSx for Windows File Server to 30 seconds.
b. Monitors the IP addresses of the preferred and the standalone Amazon FSx for Windows file servers.
c. If it detects that the file system is not reachable via the preferred Amazon FSx for Windows file server, it updates DNS with the standby IP address.
d. It continues to monitor the Amazon FSx and updates DNS again when the file system fails back to the preferred file server.

Make sure to replace the Amazon FSx hostname and IP addresses with the hostname and IP addresses your Amazon FSx for Windows File Server.

# Create new folder to store the FSx monitoring script
New-Item -Path C:\FSxCheck\ -ItemType Directory -Force

#Set FSx DNS record TTL to 30 seconds
$dnsServer = ((ipconfig | findstr [0-9].\.)[0]).Split()[-1]
$domain = (Get-ADDomain).Forest
$record = Get-DnsServerResourceRecord -ZoneName $Domain -ComputerName $dnsServer | where-Object {$_.HostName -eq "amznfsxa7ko7pfq"} #Replace this with the DNS hostname of your Amazon FSx for Windows File Server

$newRecord = $record.Clone()
$newRecord.TimeToLive = [TimeSpan]::FromSeconds(30)

Set-DnsServerResourceRecord -ComputerName $dnsServer -ZoneName $domain -OldInputObject $record -NewInputObject $newRecord 

# Script to monitor FSx
@'
# Define the file server and port
#$Server = hostname
$fileServers = @("11.11.69.37","11.11.98.78") # Replace with your Amazon FSx for Windows File Server IP addresses
$DCs = (Get-ADDomainController -Filter *).HostName
$port = 445

# Function to test the connection
function Test-Port {
    param (
        [string]$server,
        [int]$port
    )
    
    # Create a TCP client to test the FSx connection
    $tcpClient = New-Object System.Net.Sockets.TcpClient
    
    try {
        # Attempt to connect to the FSx on the specified port
        $tcpClient.Connect($server, $port)
        return $true  # Connection successful
    } catch {
        return $false  # Connection failed
    } finally {
        $tcpClient.Close()
    }
}

# Check Fsx endpoint
foreach ($fileServer in $fileServers){
    if ((Test-Port -server $fileServer -port $port)){
            if (!(Get-DnsServerResourceRecord -ZoneName company.com -ComputerName $DC | Where {$_.RecordData.Ipv4Address -eq $fileServer})){
                foreach ($DC in $DCs){
                    Add-DnsServerResourceRecord -ZoneName company.com -ComputerName $DC -A -Name "amznfsxa7ko7pfq" -IPv4Address $fileServer -TimeToLive 00:01:00 -AgeRecord
                }
            }

    }
    else{
         foreach ($DC in $DCs){
            Get-DnsServerResourceRecord -ZoneName company.com | Where {$_.RecordData.Ipv4Address -eq $fileServer} | Remove-DnsServerResourceRecord -ZoneName company.com -Force
         }
    }
} 
'@ | Set-Content -Path "C:\FSxCheck\FSxCheck.ps1"

# Create a new task action
$taskAction = New-ScheduledTaskAction `
    -Execute 'C:\Windows\System32\cmd.exe' `
    -Argument '/C C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -NoProfile -NonInteractive -NoLogo -ExecutionPolicy Unrestricted -File "C:\FSxCheck\FSxCheck.ps1"'

# Create a new trigger at startup
$taskTrigger = New-ScheduledTaskTrigger -Once -At (Get-Date).AddMinutes(1) `
-RepetitionInterval (New-TimeSpan -Minutes 1) `
-RepetitionDuration (New-TimeSpan -Days 1827)

# The name of your scheduled task.
$taskName = "FSxCheckScript"

# Describe the scheduled task.
$description = "Checks that FSx for Window File Server is reachable."

# Register the scheduled task
Register-ScheduledTask `
    -TaskName $taskName `
    -Action $taskAction `
    -Trigger $taskTrigger `
    -Description $description `
    -User "System" `
    -RunLevel "Highest"

Write-Output "Scheduled Task FSxCheckScript created"

Testing

To test the multi-AZ capabilities resiliency of the Amazon FSx for Windows File Server for the Amazon EKS deployment of SQL Server, you can manually initiate a failover of the Amazon FSx for Windows File Server. To do this, modify the throughput capacity of your Amazon FSx for Windows File Server which will trigger the failover process. We recommend testing this using the solution provided in this blog to see if it meets your requirements before using this in production.

Clean up

To prevent ongoing charges, delete any resources you created while following the steps in this blog post, including:

The Amazon EKS cluster.
The Amazon FSx for Windows File Server.

Conclusion

Containerizing database workloads has surged in popularity in recent years—and for good reason. Organizations are seeing increased agility, flexibility, and significant cost savings. The cost savings for SQL Server are especially noteworthy. Not only can you maximize resource density on your container hosts, but you also save on Windows licensing costs. In this blog, I showed how you can unlock these benefits by deploying a highly available SQL Server instance in a container on Amazon EKS, using Amazon FSx for Windows File Server for persistent storage.

Explore more content to get started with pathways to modernize your Microsoft workloads with Containers on AWS.

AWS has significantly more services, and more features within those services, than any other cloud provider, making it faster, easier, and more cost effective to move your existing applications to the cloud and build nearly anything you can imagine. Give your Microsoft applications the infrastructure they need to drive the business outcomes you want. Visit our .NET on AWS and AWS Database blogs for additional guidance and options for your Microsoft workloads. Contact us to start your migration and modernization journey today.

Microsoft Workloads on AWS