SQL Server containers on Kubernetes with S3-compatible object storage - Getting started
One of the new features introduced with SQL Server 2022 is the ability to connect to any S3-compatible object storage and SQL Server supports both Backup/Restore and data lake virtualization with Polybase integration. In this blog, we will demonstrate both of these features for SQL Server 2022 Containers running on Kubernetes. As usual, I will use the Azure Kubernetes Service as my Kubernetes environment 🙂. To know more about these features, refer SQL Server backup to URL for S3-compatible object storage - SQL Server | Microsoft Learn and Access external data: S3-compatible object storage - PolyBase - SQL Server | Microsoft Learn. Before we enable Polybase for SQL Server containers, let's go over the prerequisites for this demo: Pre-requisites: You need to have an S3 – Object storage that you can connect to and in this blog, I have configured a MinIO based S3-object storage server on Ubuntu with TLS configured. For details on how to configure this refer: Deploy MinIO: Single-Node Single-Drive — MinIO Object Storage for Linux and Network Encryption (TLS) — MinIO Object Storage for Linux. MinIO setting details: Minio Console is configured at : https://10.0.0.82:9001/ and the Server endpoint is configured at : https://10.0.0.82:9000/ On the MinIO server, I have enabled ports 9000 and 9001 in the firewall. The public certificate that I used to configure TLS on the MinIO server is called "public.crt," and I have it downloaded and saved on my Windows client. Customized SQL Server Container: Let's create the customized SQL Server container image, that'll be used to deploy the SQL Server containers on the Kubernetes cluster. Here are the steps for creating the customised SQL Server containers; for this demonstration, I created the custom container image for this demo on the same Ubuntu-based Azure VM that I used to configure the MinIO Server. ## Step 1: Create a DockerFile with the below content # Base OS layer: Latest Ubuntu LTS FROM mcr.microsoft.com/mssql/server:2022-latest USER root # Install prerequistes since it is needed to get repo config for SQL server RUN apt-get update && \ apt-get install -y software-properties-common && \ rm -rf /var/lib/apt/lists/* RUN add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2022.list)" && \ apt-get install -y mssql-server-polybase EXPOSE 1433 USER mssql # Run SQL Server process CMD ["/opt/mssql/bin/sqlservr"] # Step 2: Let's build the container image sudo docker build -t mssql-pb-update . # Step 3: Tag the image and push to your container registry, in this case I am using Azure Container Registry(ACR)that I have. sudo docker tag sql-pb-update amvinacr.azurecr.io/sql-pb-update:latest sudo docker tag mssql-pb-update amvinacr.azurecr.io/sql-pb-update:latest # To know more about the ACR refer: https://learn.microsoft.com/en-us/azure/container-registry/container-registry-tutorial-quick-task Azure Kubernetes Service based cluster creation and attaching it to the Azure Container Registry(ACR) In this blog, I am deploying SQL Server containers on Azure Kubernetes Service(AKS) based Kubernetes cluster called "sqlk8s", I have integrated my AKS cluster with the above Azure Container Registry(ACR) to which I pushed my images in the above sample code. For detailed steps on AKS cluster creation and attaching it to your choice of container registry in Azure please refer the below two articles: Quickstart: Deploy an AKS cluster by using Azure CLI - Azure Kubernetes Service | Microsoft Learn Integrate Azure Container Registry with Azure Kubernetes Service - Azure Kubernetes Service | Microsoft Learn Let's get started With the pre-requisties done, now we can start deploying SQL Server containers on our AKS cluster using the custom image that we created, that installs polybase. But, before we start deploying SQL Server containers, we need two configmaps and one secret object in the AKS cluster, with the details as shown below, I am running all these commands from my Windows client which has kubectl installed and is able to connect to the AKS cluster 'sqlk8s' that I created in previous steps. As the endpoint will be validated by the certificate, we will use one configmap object to load the public.crt certificate required for configuring the secure channel between SQL Server and the MinIO Storage. You can use the command below to generate the configmap from the public.crt file. I have copied the certificate public.crt to the following location on my Windows machine: "C:\pb\demo\certs\public.crt" # Use the below command to create the configmap using the file "public.crt" kubectl create configmap mssql-tls --from-file="C:\pbdemo\certs\public.crt" # You can verify that the, the configmap is created using the command below and the sample output is shown below with obfuscated lines in the output. C:\>kubectl describe configmap mssql-tls Name: mssql-tls Namespace: default Labels: <none> Annotations: <none> Data ==== public.crt: ---- -----BEGIN CERTIFICATE----- MIIDjjCCAnagAwIBAgIUf/RP+g+9uFAKD4TB0R2ot6jZMJAwDQYJKoZIhvcNAQEL BQAwZjELMAkGA1UEBhMCVVMxCzAJBgNVBAgMAlZBMRIwEAYDVQQHDAlTb21ld2hl cmUxDjAMBgNVBAoMBU15T3JnMQ0wCwYDVQQLDARNeU9VMRcwFQYDVQQDDA5taW5p b3NlcnZlcnVidTAeFw0yMzAxMDYxNTA0MjhaFw0zMzAxMDMxNTA0MjhaMGYxCzAJ BgNVBAYTAlVTMQswCQYDVQQIDAJWQTESMBAGA1UEBwwJU29tZXdoZXJlMQ4wDAYD VQQKDAVNeU9yZzENMAsGA1UECwwETXlPVTEXMBUGA1UEAwwObWluaW9zZXJ2ZXJ1 YnUwggEiMA0GCSqGSIb3DQEBAQUAA4IBDwAwggEKAoIBAQCcC0irpVFJQaVqiuKP LuRQUdYxehTvFCuCJLpGhS03XczCWKzemd0a3W/UC01N+tbFzpmCi0/l+R5SFsVC 3tRPKlngtDOICnYPpKSaWz03o3BgwNqtuKtucboXp3Qo+5DSd7XDi1qY+plY6LwC vbjLtjNBfyWlwS6nhTjDebvUY6w0xNmG8w1H9fPZcq/gsSGUnPyvywEENYmk5R6X xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx 1J/rLOJUVeKRvpbLraN25VoEoS8hz07DT3gmtVyJgpEK1oJ+mEs94ae76ujr5MGm M4HgV4+o9wxqjWdPyFDny/1EPsv30VqbcwmbkuPE7UHG4JH/5gxqxnXJcPnr3BQy 4GU= -----END CERTIFICATE----- The second configmap object is used to load mssql.conf, which contains the polybase Trace flag 13702, which is required for SQL Server on Linux. This configmap is generated using the yaml script shown below: # Create a yaml file called: mssql_config.yaml and save it to the location "C:\pbdemo\certs\mssql_config.yaml" kind: ConfigMap apiVersion: v1 metadata: name: mssql data: mssql.conf: | [EULA] accepteula = Y accepteulaml = Y [coredump] captureminiandfull = true coredumptype = full [language] lcid = 1033 [filelocation] defaultdatadir = /var/opt/mssql/userdata defaultlogdir = /var/opt/mssql/userlog [traceflag] traceflag0 = 13702 # We are ready to create the configmap using the command as shown below: Kubectl apply -f “C:\pbdemo\certs\ mssql_config.yaml” # You can inspect the configmap using the command shown below with the sample output as well C:\>kubectl describe configmap mssql Name: mssql Namespace: default Labels: <none> Annotations: <none> Data ==== mssql.conf: ---- [EULA] accepteula = Y accepteulaml = Y [coredump] captureminiandfull = true coredumptype = full [language] lcid = 1033 [filelocation] defaultdatadir = /var/opt/mssql/userdata defaultlogdir = /var/opt/mssql/userlog [traceflag] traceflag0 = 13702 Events: <none> The secret object in the cluster is used to save the sa password for SQL Server, the command to create the secret is as shown below kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD="MyC0m9l&xP@ssw0rd" Deploy SQL Server Containers We are now ready to deploy SQL Server containers, below is the deployment yaml called "sqldeploy.yaml" that is used to deploy one SQL Server container as a statefulset deployment using the Azure disk as the storage class. Using this YAML we deploy: One SQL Server instance, with separate PV(persistent volumes) and Persistent volume claims (PVCs) for User database, user log and tempdb files. We are loading the public.crt certificate to the folder: /var/opt/mssql/security/ca-certificates folder inside the container. We are loading the mssql.conf file with various parameter and the required trace flag 13702. At the end, we create the external load balancer Service to be able to connect to the SQL Server. # Create the sqldeploy.yaml at the location "C:\pbdemo\certs\sqldeploy.yaml", and paste the content below, you can modify it according to your requirements. kind: StorageClass apiVersion: storage.k8s.io/v1 metadata: name: azure-disk provisioner: kubernetes.io/azure-disk parameters: storageaccounttype: Standard_LRS kind: Managed --- 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: amvinacr.azurecr.io/sql-pb-update:latest ports: - containerPort: 1433 env: - name: ACCEPT_EULA value: "Y" - name: MSSQL_ENABLE_HADR value: "1" - name: MSSQL_SA_PASSWORD valueFrom: secretKeyRef: name: mssql 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: mssql-config-volume mountPath: "/var/opt/config" - name: mssql-tls-configmap mountPath: "/var/opt/mssql/security/ca-certificates/" volumes: - name: mssql-config-volume configMap: name: mssql - name: mssql-tls-configmap configMap: name: mssql-tls volumeClaimTemplates: - metadata: name: mssql spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi - metadata: name: userdata spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi - metadata: name: userlog spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi - metadata: name: tempdb spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi --- apiVersion: v1 kind: Service metadata: name: mssql-0 spec: type: LoadBalancer selector: statefulset.kubernetes.io/pod-name: mssql-0 ports: - protocol: TCP port: 1433 targetPort: 1433 name: tcpsql # You can run the below command to deploy SQL Server conatiner based on the deployment yaml file called sqldeploy.yaml Kubectl apply -f “C:\pbdemo\certs\sqldeploy.yaml” # You can run the kubectl get command to see all the SQL Server container deployed, sample command and output shown below for reference C:\>kubectl get all NAME READY STATUS RESTARTS AGE pod/mssql-0 1/1 Running 0 92m NAME TYPE CLUSTER-IP EXTERNAL-IP PORT(S) AGE service/kubernetes ClusterIP 10.0.0.1 <none> 443/TCP 16h service/mssql-0 LoadBalancer 10.0.252.15 20.235.55.7 1433:31551/TCP 13s NAME READY AGE statefulset.apps/mssql 1/1 97m Connect to SQL Server - Demo time We are ready to connect to the SQL Server, feel free to use your favorite SQL Server tool .viz SQL Server Management Studio(SSMS) or Azure Data Studio (ADS) and connect to the SQL Server and run the below commands to virtualize data and access CSV or parquet files and/or to take backups of the databases on the S3 based object storage. I'm using the SSMS tool and running the below queries. I connect to the SQL Server and confirm that that TLS certificate public.crt is loaded by SQL Server, I can do that from the errorlog using the command : sp_readerrorlog from the T-SQL edition and the sample output is shown below with the certificate loaded: Backup/Restore to S3-based object storage: To take S3 based backups, I need to first create bucket on the storage in this case, I connect to the MinIO portal and create the bucket called "sqltest" as shown below and then create a credential to access the storage. Now, use the below sample T-SQL script to create the credential and running the backup, the backup should be successful as shown below CREATE CREDENTIAL [s3://10.0.0.82:9000/sqltest] WITH IDENTITY = 'S3 Access Key', SECRET = 'amit:mypass12345'; BACKUP DATABASE S3_backup_demo TO URL = 's3://10.0.0.82:9000/sqltest/S3_backup_demo_db.bak' WITH STATS = 10 You can view the backup file created on the MinIO console as shown below: Data Virtualization- Polybase Here are the steps to configure data virtualization, below T-SQL sample script is used to configure the following steps: We verify the polybase feature is installed, then configure and enable polybase feature for SQL Server. Create the database scoped credential to access the object storage Now, we create the external data source and link it with the credential that we created in the preceding step. We are now ready to query the data from the external data source -- Verify the Polybase feature is installed: SELECT SERVERPROPERTY ('IsPolyBaseInstalled') AS IsPolyBaseInstalled; -- Enable polybase feature using the commmands below: exec sp_configure @configname = 'polybase enabled', @configvalue = 1; reconfigure with override exec sp_configure @configname = 'polybase enabled' --Next, let’s create a database and database scoped credential to access the object storage. create database pb_demo USE pb_demo CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypass123@'; CREATE DATABASE SCOPED CREDENTIAL s3_dc WITH IDENTITY = 'S3 Access Key', SECRET = 'amit:mypass12345' ; -- To verify, the credential is created you can run the below command: SELECT * FROM sys.database_scoped_credentials; -- Now go ahead and try creating the External data source: CREATE EXTERNAL DATA SOURCE s3_ds WITH (LOCATION = 's3://10.0.0.82:9000/',CREDENTIAL = s3_dc) -- When creating the external data source, if you see the below error, then please restart the container and run the same command again that should solve the below error Msg 46530, Level 16, State 11, Line 20 External data sources are not supported with type GENERIC. --We now have the credential and the external data source created to query files on the object storage to query a CSV based file that is already present on the object storage you can run the command as shown below SELECT * FROM OPENROWSET( BULK '/sqltest/test.csv', FORMAT = 'CSV', DATA_SOURCE = 's3_ds') WITH ( c1 varchar(50) ) AS [Test1] -- To query a parquet file that is available on the object storage you can run the below query, I copied a sample parquet file that is available here: https://github.com/Teradata/kylo/tree/master/samples/sample-data/parquet to my Minio object storage. SELECT * FROM OPENROWSET ( BULK '/sqltest/userdata1.parquet' , FORMAT = 'PARQUET' , DATA_SOURCE = 's3_ds' ) AS [Test12] Note: You can also use Create External Table as (CETAS) to access both parquet and CSV files for SQL Server on Linux. I hope you find this helpful to get started with polybase and S3 storage integration with SQL Server 2022 containers. Special thanks to my colleagues from the Polybase team for helping me with this demo. Happy learning!SQL Server 2022 now available for both RHEL 9 and Ubuntu 22.04 in Preview mode
Update as of 23 Nov 2023: SQL Server 2022 on RHEL 9 and Ubuntu 22.04 is now GA starting with the release of CU 10 for SQL Server 2022. For details, please refer the GA blog here: SQL Server 2022 for RHEL 9 and Ubuntu 22.04 is now Generally Available (GA) - Microsoft Community Hub We are glad to announce that SQL Server 2022 is now available in preview mode for both Red Hat Enterprise Linux (RHEL) 9 and Ubuntu 22.04. For this preview, only Evaluation edition is available, which is limited to 180 days starting Thursday, July 27th, 2023. In your Dev/Test environments, you may now take advantage of the most recent SQL Server 2022 improvements on both RHEL 9 and Ubuntu 22.04. Currently, production workloads on RHEL 9 and Ubuntu 22.04 are not supported by the SQL Server 2022 preview packages. You can run the production workloads for SQL Server 2022 on RHEL 8 and Ubuntu 22.04 and they are fully supported. SELinux Integration: With this preview release, you now have the option to run SQL Server 2022 as a confined application when you have the SELinux enabled and in enforcing mode. Thus, seamlessly integrating with the SELinux enabled secure environments. This integration is made possible by the new package called "mssql-server-selinux" which enables the custom policies required to run SQL Server as confined application with SELinux. For more information about confined and unconfined applications with SELinux, see Getting started with SELinux. If you prefer to run SQL Server as unconfined application like in previous version of RHEL, then you can skip the installation of the "mssql-server-selinux" package and just install the mssql-server package like you did with the previous RHEL versions. For details on how you can get started with SQL Server 2022 on RHEL 9 preview please refer: RHEL: Install SQL Server on Linux - SQL Server | Microsoft Learn Getting Started with SQL Server 2022 on Ubuntu 22.04 (preview) You can get started with the SQL Server 2022 on Ubuntu 22.04 preview for details refer : Ubuntu: Install SQL Server on Linux - SQL Server | Microsoft Learn SQL Server Container images (preview) The preview packages for SQL Server 2022 on RHEL 9 and Ubuntu 22.04 are also available as container images and it is easy for you to get started. Like always, you can use both podman and/or docker tool. SQL Server 2022 preview container image based on RHEL 9: You can pull the preview container image using the tag: 2022-preview-rhel-9 as shown below podman pull mcr.microsoft.com/mssql/rhel/server:2022-preview-rhel-9 Deploy the SQL Server containers using the command below, please note the use of Evaluation edition as this is currently in preview podman run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Evaluation' -h sqlpreview --name sqlpreview -p 1433:1433 -d mcr.microsoft.com/mssql/rhel/server:2022-preview-rhel-9 SQL Server 2022 preview container image based on Ubuntu 22.04: You can pull the preview container image using the tag: 2022-preview-ubuntu-22.04 as shown below docker pull mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04 Deploy the SQL Server containers using the command below, please note the use of Evaluation edition as this is currently in preview docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Evaluation' -h sqlpreview --name sqlpreview -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-preview-ubuntu-22.04 For further details on container images, refer: For RHEL based SQL Server 2022 preview container images: Microsoft SQL Server - RHEL based images For Ubuntu based SQL Server 2022 preview container images: Microsoft SQL Server - Ubuntu based images Let us know your experience with the preview package: If you have any feedback or issue that you'd like to report for these preview packages we recommend you to use any of the following options that suits you the best. 1) Send us an email with your feedback to sqlpreviewpackage@microsoft.com. 2) Another option would be to submit your comments directly on Azure Ideas ( Use the SQL Server on Linux Group on the left side of the page) 3) Alternatively, you can open issues related to the preview packages Issues · microsoft/mssql-docker (github.com) on GitHub. We hope you give SQL Server 2022 preview packages on Red Hat 9 and Ubuntu 22.04 a try and let us know what you think!SQL Server 2022 for RHEL 9 and Ubuntu 22.04 is now Generally Available (GA)
We are happy to announce that Starting CU 10 release for SQL Server 2022 , SQL Server 2022 on RHEL 9 and Ubuntu 22.04 is now generally available (GA), meaning you can run production workload on SQL Server 2022 deployed on Red Hat Enterprise Linux (RHEL) 9 and Ubuntu 22.04. For SQL Server 2022 on RHEL 9, the SELinux integration that enables you to run SQL Server 2022 on RHEL 9 as confined application is also now generally available. If you've had the preview packages installed previously, we recommend that you please uninstall the preview bits and upgrade to SQL Server 2022 GA packages available at the following repos, for steps on how to update repos refer the Configure repositories for installing and upgrading SQL Server on Linux - SQL Server | Microsoft Learn documentation. For RHEL 9 use the repo: https://packages.microsoft.com/config/rhel/9/ For Ubuntu 22.04 use the repo: https://packages.microsoft.com/config/ubuntu/22.04/ For further details on getting started with the installation of SQL Server 2022 on RHEL 9 refer: RHEL: Install SQL Server on Linux - SQL Server | Microsoft Learn & for SQL Server on Ubuntu 22.04 refer: Ubuntu: Install SQL Server on Linux - SQL Server | Microsoft Learn SQL Server 2022 container images on RHEL 9 & Ubuntu 22.04 also generally available: SQL Server 2022 on RHEL 9 and Ubuntu 22.04 are also available as container images and generally available (GA). It is easy for you to get started. Like always, you can use both podman and/or docker tool. To pull and run the production ready SQL Server 2022 images on RHEL 9 use the tag: 2022-CU10-rhel-9.1 #Pull the image podman pull mcr.microsoft.com/mssql/rhel/server:2022-CU10-rhel-9.1 #Run a container using the above image podman run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Developer' -h sql22rhel9 --name sql22rhel9 -p 1433:1433 -d mcr.microsoft.com/mssql/rhel/server:2022-CU10-rhel-9.1 To pull and run the production ready SQL Server 2022 images on Ubuntu 22.04 use the tag: 2022-CU10-ubuntu-22.04 #Pull the image docker pull mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04 #Run a container use the above image docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourP@ssword>' -e 'MSSQL_PID=Developer' -h sql22ubu2204 --name sql22ubu2204 -p 1433:1433 -d mcr.microsoft.com/mssql/server:2022-CU10-ubuntu-22.04Enterprise vs EnterpriseCore Edition for SQL Server on Linux production Workloads
Recently, in one of the meetings with our CSS team (support) we realized that our customers might not be selecting the right Enterprise edition for their SQL Servers deployed on Linux. To learn more about different SQL Server editions please refer: Editions and supported features of SQL Server 2022 - SQL Server | Microsoft Learn. Thus, through this blog we would like to provide guidance on how you can identify and update the SQL Server to use the right Enterprise edition which should show up in the error log as : "Enterprise Edition: Core-based Licensing (64-bit)" and not "Enterprise Edition (64-bit)" which is only needed in case you need to follow historical licensing agreements that limits to a maximum 20 cores per SQL Server instance. For New SQL Server installations: After, the SQL Server setup, when you run the SQL Server configuration using the mssql-conf tool as shown below: sudo /opt/mssql/bin/mssql-conf setup You are presented with the following editions, please note that option 9 (Standard (Billed through Azure)) and option 10 (Enterprise Core (billed through Azure)) are only available for SQL Server 2022 versions. 1) Evaluation (free, no production use rights, 180-day limit) 2) Developer (free, no production use rights) 3) Express (free) 4) Web (PAID) 5) Standard (PAID) 6) Enterprise (PAID) - CPU core utilization restricted to 20 physical/40 hyperthreaded 7) Enterprise Core (PAID) - CPU core utilization up to Operating System Maximum 😎 I bought a license through a retail sales channel and have a product key to enter. 9) Standard (Billed through Azure) - Use pay-as-you-go billing through Azure. 10) Enterprise Core (Billed through Azure) - Use pay-as-you-go billing through Azure. When you intend to run the Enterprise edition assuming you have the required agreements in place, you should normally choose to run the “Enterprise Core” that is option 7 in the above list, unless you have a specific reason to use the legacy license which is “Enterprise” i.e option 6 in the above list, this option is mostly needed to follow historical agreements and is limited to 20 cores per SQL Server instance and not available for new agreements anymore. Change edition for existing SQL Server instances. To identify, if you have the correct Enterprise edition selected for the SQL Server on Linux installed, you can review the errorlog and if you see the version listed as “Enterprise Edition (64-bit)” then you are using the legacy Enterprise (PAID) edition that is limited to 20 cores. To update this license, to use the Enterprise Core license you can run the command as shown below, please note this procedure REQURIES SQL SERVER TO BE STOPPED AND STARTED, so please do this during a scheduled maintenance window only. Note: It is important that you use the ACCEPT_EULA clause otherwise SQL Server service will not start after the edition change. Also, please answer the Software Assurance question based on your scenario, if you have it covered then say yes, else please select no. [amvin@ mylinux ~]$ sudo cat /var/opt/mssql/log/errorlog | grep Enterprise Enterprise Edition (64-bit) on Linux (Red Hat Enterprise Linux 8.8 (Ootpa)) <X64> [amvin@ mylinux ~]$ sudo systemctl stop mssql-server [amvin@mylinux ~]$ sudo ACCEPT_EULA='Y' /opt/mssql/bin/mssql-conf set-edition Choose an edition of SQL Server: 1) Evaluation (free, no production use rights, 180-day limit) 2) Developer (free, no production use rights) 3) Express (free) 4) Web (PAID) 5) Standard (PAID) 6) Enterprise (PAID) - CPU core utilization restricted to 20 physical/40 hyperthreaded 7) Enterprise Core (PAID) - CPU core utilization up to Operating System Maximum I bought a license through a retail sales channel and have a product key to enter. 9) Standard (Billed through Azure) - Use pay-as-you-go billing through Azure. 10) Enterprise Core (Billed through Azure) - Use pay-as-you-go billing through Azure. Details about editions can be found at https://go.microsoft.com/fwlink/?LinkId=2109348&clcid=0x409 Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program. By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software. By choosing an edition billed Pay-As-You-Go through Azure, you are verifying that the server and SQL Server will be connected to Azure by installing the management agent and Azure extension for SQL Server. Enter your edition(1-10): 7 Is the product selected covered by Software Assurance? [Yes/No]:Yes Configuring SQL Server... ForceFlush is enabled for this instance. ForceFlush feature is enabled for log durability. Please run 'sudo systemctl start mssql-server' to start SQL Server. [amvin@ mylinux ~]$ sudo systemctl start mssql-server [amvin@ mylinux ~]$ sudo cat /var/opt/mssql/log/errorlog | grep Enterprise Enterprise Edition: Core-based Licensing (64-bit) on Linux (Red Hat Enterprise Linux 8.8 (Ootpa)) <X64> As you can see after the change the errorlog shows the SQL Server licensing changed from “Enterprise Edition (64-bit)” to “Enterprise Edition: Core-based Licensing (64-bit)” which is the correct licensing to use. For SQL Server containers When deploying SQL Server container instances, please set the MSSQL_PID environment to EnterpriseCore as shown below. You can verify the edition by looking at the errorlog. amvin@mylinux:~$ docker run --name sql22 -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=LS1setup!' -e 'MSSQL_PID=EnterpriseCore' -p 1436:1433 -d mcr.microsoft.com/mssql/server:latest amvin@mylinux:~$ sudo docker logs sql22 | grep Enterprise 2023-09-28 14:12:10.60 Server The licensing PID was successfully processed. The new edition is [Enterprise Edition: Core-based Licensing]. Changing the edition for SQL Server containers deployed: For the SQL Server containers that are already deployed, you can check the existing Enterprise edition in SQL Server errorlogs with a query as shown below and If you see the licensing as Enterprise Edition, then please stop and remove the container, ensure the container is using persistent volumes (to avoid data loss), and then redeploy the container using the MSSQL_PID environment variable set to Enterprisecore as shown below. Once the container is deployed, you can verify that the edition is changed using the same command: amvin@ mylinux:~$ sudo docker logs sql22 | grep Enterprise 2023-09-28 14:24:14.07 Server The licensing PID was successfully processed. The new edition is [Enterprise Edition]. amvin@ mylinux:~$ sudo docker rm -f sql22 amvin@ mylinux:~$ sudo docker run --name sql22 -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=LS1setup!' -e 'MSSQL_PID=EnterpriseCore' -p 1436:1433 -d mcr.microsoft.com/mssql/server:latest amvin@mylinux:~$ sudo docker logs sql22 | grep Enterprise 2023-09-28 15:09:36.99 Server The licensing PID was successfully processed. The new edition is [Enterprise Edition: Core-based Licensing]. Hope this helps!!Updating SQL Server containers deployed on Kubernetes!
I'm sure you've thought about how to update SQL Server containers running on a Kubernetes cluster at some point. So, this blog attempts to answer the question. According to the Kubernetes documentation, there are two Update strategies for statefulset workloads. For your convenience, I'm quoting the summary below: OnDelete update : When a StatefulSet's .spec.updateStrategy.type is set to OnDelete, the StatefulSet controller will not automatically update the Pods in a StatefulSet. Users must manually delete Pods to cause the controller to create new Pods that reflect modifications made to a StatefulSet's .spec.template. Rolling update : When a statefuleset's .spec.updateStrategy.type is set to RollingUpdate, the StatefulSet controller will delete and recreate each Pod in the StatefulSet. It will proceed in the same order as Pod termination (from the largest ordinal to the smallest), updating each Pod one at a time. This is the default update strategy. Before, we get into the SQL Server update strategy, let's go over the guidelines for deploying SQL Server containers as statefulset workloads, which are documented here: Deploy SQL Server Linux containers on Kubernetes with Statefulsets. As mentioned in the article, you could have chosen to deploy only one SQL Server instance per statefulset deployment script by setting the .spec.replica parameter to 1. In this case, the rolling update strategy does not make sense because you only have one SQL Server instance (one pod) deployed using the single statefulset deployment yaml, so you would normally choose the OnDelete update option to update the SQL Server instance. Alternatively, if you set the .spec.replicas to more than one, you will deploy that many SQL Server instances with co-related names. In this case, you can choose the Ondelete strategy if you want to control the order in which the updates are pushed to each pod or Rolling update strategy for Kubernetes-controlled phased updates of SQL Server pods. I’d like to clarify that .spec.replicas here refers to Replicas as defined in Kubernetes and this does not refer to the SQL Server Always On availability groups replicas. What I am alluding to here is that these are all standalone (independent) SQL Server instances that are not part of the Always On availability groups replicas. (I'm working on a blog about the update strategy for SQL Server containers as part of the AGs, but for now let's refocus on this blog ) OnDelete Update for SQL Server container: In my opinion, you can use this update option for your SQL Server deployment if any of the following conditions are met: You’ve set the .spec.replicas to 1 and are deploying one pod per statefulset deployment script. If you’ve set the .spec.replicas to a value greater than 1 and want to precisely control the order in which the update is pushed to each pod in the statefulset deployment indexed by the ordinal ID. The downside of this option is: An accidental delete of the pod can cause the pod to be started with the updated image. Consider the following scenario to better understand this. The initial script used to deploy SQL Server as described in this article is shown below, and as you can see, we are deploying SQL Server 2022 RTM with the .spec.replicas set to 3. Here's an illustration of the scenario and I want to make sure that I upgrade the pod mssql-1 first. I've depicted how the pod mssql-1 is upgraded, the same is true for next SQL Server pod, I decide to update. Note: All the commands are being run from a cmd shell running kubectl from a Windows client machine connected to an Azure Kubernetes Service cluster that serves as the kubernetes platform for SQL Server container deployments. apiVersion: apps/v1 kind: StatefulSet metadata: name: mssql spec: serviceName: "mssql" replicas: 3 selector: matchLabels: app: mssql template: metadata: labels: app: mssql spec: securityContext: fsGroup: 10001 containers: - name: mssql image: mcr.microsoft.com/mssql/server:2022-RTM-ubuntu-20.04 resources: limits: memory: 2Gi cpu: '2' ports: - containerPort: 1433 name: tcpsql env: - name: ACCEPT_EULA value: "Y" - name: MSSQL_ENABLE_HADR value: "1" - name: MSSQL_AGENT_ENABLED value: "1" - name: MSSQL_SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: MSSQL_SA_PASSWORD volumeMounts: - name: mssql mountPath: "/var/opt/mssql" volumeClaimTemplates: - metadata: name: mssql spec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi Now let's say we want to upgrade all the pods to run SQL Server 2022 latest image, I can do this using the following steps, followed in the same order: Let's run 'kubectl describe statefulset mssql` command to see what the current update strategy is, by default the Update Strategy is set to RollingUpdate, we need to update the statefulset and change the update strategy from RollingUpdate to OnDelete. We have to use the rollingupdate null option due to an issue as described here Error when trying to update StatefulSet updateStrategy.type to "OnDelete" from default value. To do this run the below command: kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"OnDelete\",\"rollingUpdate\": null}}}" Once you set the Update Strategy to OnDelete, you can verify this by running the 'kubectl describe statefulset mssql' command again. Name: mssql Namespace: default CreationTimestamp: Fri, 12 Mar 2023 20:45:20 +0530 Selector: app=mssql Labels: <none> Annotations: <none> Replicas: 3 desired | 3 total Update Strategy: OnDelete .. .. .. We now update the image version in the statefulset to use the desired SQL Server version. In this case, I am updating SQL Server 2022 RTM to the most recent SQL 2022 version, which is SQL Server 2022-CU1. Please keep in mind that running this command will not immediately update the SQL Server pod. Only when you delete and recreate the pod will the update take place. kubectl set image statefulset mssql mssql=mcr.microsoft.com/mssql/server:2022-latest You can confirm that the image for the statefulset has been updated by running the same "'kubectl describe statefulset mssql"' command again, and you will see that the Image value is now set to: mcr.microsoft.com/mssql/server:2022-latest instead of mcr.microsoft.com/mssql/server:2022-RTM-ubuntu-20.04. When you are ready to update the SQL Server container, most likely during a scheduled downtime, you can run the following command, which will immediately trigger the SQL Server update. Once the pod is online, the upgrade scripts will run, and you can inspect the errorlog for the "Recovery is complete. This is an informational message only. No user action is required" message indicating you that SQL Server is now ready for connections post the update. Kubectl delete pod mssql-1 #Check the logs to see the version updated kubectl logs mssql-1 Now, if you have more than one replica defined in the statefulset yaml, as in this case, you can delete the other pods one at a time with the kubectl delete <yourpodname> command. This way, you have control over which pods and when they are updated. Rolling Update for SQL Server Containers: I consider this is an advanced option, as you can perform a phased roll out of the SQL Server update. Read partitioned rolling updates to know more. You can choose to update the highest ordinal pod first and then push the updates in a phase, with the pod with the lowest ordinal updated last. The benefit of choosing this option is that an accidental deletion of a pod does not result in the SQL Server instance being updated. Let’s consider the same script as used above and this time, lets update the pods using the rolling update option to see how this works: If you have used the same script as shared in this article above, you should have mssql-0, mssql-1 and mssql-2 pods deployed in the cluster. Now, lets push the updates in stages, where you first update mssql-2 then mssql-1 and finally the mssql-0 pods from SQL 2022 RTM version to SQL 2022-latest which as of today is SQL 2022-CU1 as shown below. Please remember this order cannot be changed when using the phased rolling updated. To get started with this update strategy, make sure you follow the steps below in the correct order: Let's run 'kubectl describe statefulset mssql' command to see what the current update strategy is, by default the Update Strategy is set to RollingUpdate and the partition is set to 0. Look at the .spec.updateStrategy.type that should be set to RollingUpdate and the .spec.updateStrategy.rollingUpdate.partition should be set to 0. Now update the statefulset to set the partition to 2 (in this case, highest ordinal), which ensures that only pods with ordinal indexes equal to or greater than 2 are updated. Only mssql-2 will be updated in this case. kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"RollingUpdate\",\"rollingUpdate\": {\"partition\": 2}}}}" #output when you run the 'kubectl describe statefulset mssql' command: Name: mssql Namespace: default CreationTimestamp: Fri, 12 Mar 2023 20:45:20 +0530 Selector: app=mssql Labels: <none> Annotations: <none> Replicas: 3 desired | 3 total Update Strategy: RollingUpdate Partition: 2 .. .. .. We are now ready to push our updates. To do so, we must first update the statefulset to use the most recent SQL Server image, as shown below. The mssql-2 pod will be deleted and recreated automatically by the cluser with the upgraded image as soon as you run the below command; the other pods, mssql-0 and mssql-1, will remain untouched and running the older version. As a result, you should only run the following command when you are ready to update the pod mssql-2. Please note, I did not have to manually delete the pod as I did with the Ondelete option to update the image. kubectl set image statefulset mssql mssql=mcr.microsoft.com/mssql/server:2022-latest If you are ready to update the mssql-1 pod, you must update the statefulset and set the partition to 1. As soon as you set the partition to 1, the update is pushed to all the pods with ordinal values equal or greater than 1. In this case, because the mssql-2 pod has already been updated, it is not deleted and recreated, but the mssql-1 pod is. So, only run the following command when you are ready to update the mssql-1 pod. kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"RollingUpdate\",\"rollingUpdate\": {\"partition\": 1}}}}" #output when you run the 'kubectl describe statefulset mssql' command: Name: mssql Namespace: default CreationTimestamp: Fri, 12 Mar 2023 20:45:20 +0530 Selector: app=mssql Labels: <none> Annotations: <none> Replicas: 3 desired | 3 total Update Strategy: RollingUpdate Partition: 1 .. .. Similarly, when you're ready to update the mssql-0 pod, set the partition value to 0, and all pods with ordinal index values greater than or equal to 0 will be updated. Because mssql-2 and mssql-1 were previously updated, they are not deleted in this case, and only the mssql-0 pod is deleted and recreated. Again, only run the following command when you are ready for the pod to be updated. kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"RollingUpdate\",\"rollingUpdate\": {\"partition\": 0}}}}" #output when you run the 'kubectl describe statefulset mssql' command: Name: mssql Namespace: default CreationTimestamp: Fri, 12 Mar 2023 20:45:20 +0530 Selector: app=mssql Labels: <none> Annotations: <none> Replicas: 3 desired | 3 total Update Strategy: RollingUpdate Partition: 0 .. .. That’s it you have now completed the phased rollout of the SQL Server update. I hope you find this blog helpful in planning and executing SQL Server container updates on the Kubernetes platform. Happy learning!!Always on Availability Groups for SQL Server containers on Kubernetes - The DH2i's DxOperator way!!
Update as on 12 Feb 2024: The DH2i Operator is released on 6th Feb 2024, and generally available as per the latest blog from our Partner DH2i, I recommend you read this for further information: DxOperator for SQL Server Availability Group - DH2I. Recently, I had the privilege of presenting with OJ Ngo from DH2i, our partners who offer the cluster stack for SQL Server on Windows, Linux and containers, at PASS Data Community Summit 2023. In this session, we revealed the private preview of the DxOperator!! - A simple and effective option to deploy SQL Server containers with availability groups configured in high availability mode, with DxEnterprise cluster stack, all deployed on Kubernetes platform. Prerequisites A Kubernetes cluster to deploy SQL Server instances. For this demo, I am using Azure Kubernetes Service (AKS) cluster. A client machine to run the kubectl commands and manage the object creation and administration on the Kubernetes cluster, today I am going to use a windows machine and here are the instructions to setup kubectl to connect and manage AKS cluster. On the same client machine, I also have the SSMS (SQL Server Management Studio) or Azure Data Studio (ADS) installed to connect to SQL Server instances and view availability groups. What does this operator do? This operator allows you to complete all the following tasks in 5 commands and less than 5 minutes!! Deploy three or more SQL Server instances on a Kubernetes cluster with specific mssql-config parameters. Configure and install DxEnterprise cluster. Create and configure an empty SQL Server Always On availability group (AG) and join all the three or more SQL Server instances to this AG. You don’t believe me! Well do it with me to believe it. Do you have 5 minutes now? Yes, then setup the pre-requisites and let's get rolling: Step1: Create the configmap object on the Kubernetes cluster that has the SQL Server specific mssql-conf settings defined. Here is a sample script: # Create a file called mssqlconfig.yaml, with your specific mssql-conf settings added to it: apiVersion: v1 kind: ConfigMap metadata: name: mssql-config data: mssql.conf: | [EULA] accepteula = Y [network] tcpport = 1433 [sqlagent] enabled = true # Now run the command to create the object: kubectl apply -f "D:\operator\mssqlconfig.yaml" Step 2: Now, create a secret object to store the sa account password and another secret to store dxe passkey and license key, as shown below: # Create the secret to store the sa password for SQL Server. kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD= "MyP@SSw0rd1!" # Create the secret to store the license key for DH2i. You can get the developer license from -> https://dh2i.com/trial/. Replace the XXXX-XXXX-XXXX-XXXX with your License Key. kubectl create secret generic dxe --from-literal=DX_PASSKEY="MyP@SSw0rd1!" --from-literal=DX_LICENSE=XXXX-XXXX-XXXX-XXXX Step 3: Install the private preview operator using the following commands: # download the operator yaml from and save as v1beta2.yaml: dxoperator.dh2i.com/dxe/files/v1beta2.yaml # Now install the operator using the command: kubectl apply -f ”D:\operator\v1beta2.yaml” Step 4: Finally, provide the SQL Server and AG details like the name, limits and then watch the deployment happen: # create a file called crd_v1.yaml ( you can name it as you like) and copy the content as is, if you want to deploy 3 containers with the instance name contoso-sql-0, contoso-sql-1 and contoso-sql2 and configure AG between the three instances apiVersion: dh2i.com/v1beta2 kind: DxEnterpriseSqlAg metadata: name: contoso-sql spec: synchronousReplicas: 3 asynchronousReplicas: 0 # ConfigurationOnlyReplicas are only allowed with availabilityGroupClusterType set to EXTERNAL configurationOnlyReplicas: 0 availabilityGroupName: CONTOSOAG # For a contained availability group, add the option CONTAINED availabilityGroupOptions: null # Valid options are EXTERNAL (automatic failover) and NONE (no automatic failover) availabilityGroupClusterType: EXTERNAL createLoadBalancers: true template: spec: dxEnterpriseContainer: image: "docker.io/dh2i/dxe:beta" imagePullPolicy: Always acceptEula: true clusterSecret: dxe vhostName: VHOST1 joinExistingCluster: false # QoS – guaranteed (uncomment to apply) resources: limits: memory: 1Gi cpu: '1' # Configuration options for the required persistent volume claim forDxEnterprise volumeClaimConfiguration: storageClassName: null resources: requests: storage: 1Gi mssqlServerContainer: image: "mcr.microsoft.com/mssql/server:latest" imagePullPolicy: Always mssqlSecret: mssql acceptEula: true mssqlPID: Developer mssqlConfigMap: mssql-config # QoS – guaranteed (uncomment to apply) resources: limits: memory: 2Gi cpu: '2' # Configuration options for the required persistent volume claim for SQL Server volumeClaimConfiguration: storageClassName: null resources: requests: storage: 2Gi # Now deploy the custom resource using the command: kubectl apply -f "D:\operator\crd_v1.yaml" That’s it, now relax you’ve worked hard, wait for a few minutes, see the deployment happen, when it completes you see this: Step 5: Connect to your SQL Server primary instance to run the below T-SQL command to add a database “test” to your AG “CONTOSOAG” #T-SQL Command on the primary, create a database and back up the same to add it to the AG. create database test go backup database test to disk = '/var/opt/mssql/data/test.bak' #Add the newly created database to the availability group "CONTOSOAG" ALTER AVAILABILITY GROUP [CONTOSOAG] ADD DATABASE [test]; Once this step is completed view the cluster using AG dashboard as shown below: This is you regular AG deployed on Kubernetes with SQL Server containers based on your requirement!! Let us know what you think and how was your experience?Upgrade SQL Server container with Always On availability groups configured on Kubernetes!
This is a follow-up to my previous blog post I published a while ago titled "Updating SQL Server containers deployed on Kubernetes!" In that blog post, I discussed two approaches that are OnDelete & Rolling options to update your Kubernetes-deployed independent SQL Server container instances. In this article, we will go a step further and upgrade SQL Server containers running SQL Server 2019 to SQL Server 2022, with High Availability (HA) configured using Always On availability groups and the cluster stack provided by DH2i. As documented in this article: "Deploy availability group with DH2i for SQL Server containers on AKS." configuring Always On availability group for SQL Server containers with DH2i is a fully supported solution stack for SQL Server HA. We'll use the "OnDelete” upgrade option today, but you can also use the Rolling upgrade option. As discussed in the previous blog, when the .spec.updateStrategy.type of a StatefulSet is set to RollingUpdate, the StatefulSet controller will delete and recreate each Pod in the StatefulSet. It will update each Pod one at a time, in the same order as Pod termination (from the largest ordinal to the smallest). As a result, if you have three Pods, namely <yourpodname>-0 (Secondary), <yourpodname >-1 (Primary), and <yourpodname>-2 (Secondary), you must make <yourpodname>-0 the primary because it has the least ordinal index and will be updated last when you choose rolling upgrade. As you may be aware, when performing rolling upgrades for availability groups, the best practice guide requires you to first upgrade the secondaries, then perform a failover from the primary to any of the upgraded secondaries. Finally, upgrade the old primary, so in this case, making <yourpodname>-0 the primary ensures that it is updated after you have updated all the secondaries (<yourpodname>-2 and <yourpodname>-1) and performed the failover. Deploying SQL Server containers and configure Always On availability group: As previously stated, we will use the OnDelete upgrade option for this blog. I'm using the script below to deploy three SQL Server replicas as containers and to enable high availability with the DH2i cluster stack. DH2i’s DxEnterprise will be deployed with sidecar architecture as described here: Deploy availability groups with DH2i DxEnterprise sidecar on Kubernetes - SQL Server | Microsoft Learn. apiVersion: v1 kind: Service metadata: name: mssql-0 spec: clusterIP: None selector: statefulset.kubernetes.io/pod-name: mssql-0 ports: - name: dxl protocol: TCP port: 7979 - name: dxc-tcp protocol: TCP port: 7980 - name: dxc-udp protocol: UDP port: 7981 - name: sql protocol: TCP port: 1433 - name: listener protocol: TCP port: 14033 --- apiVersion: v1 kind: Service metadata: name: mssql-1 spec: clusterIP: None selector: statefulset.kubernetes.io/pod-name: mssql-1 ports: - name: dxl protocol: TCP port: 7979 - name: dxc-tcp protocol: TCP port: 7980 - name: dxc-udp protocol: UDP port: 7981 - name: sql protocol: TCP port: 1433 - name: listener protocol: TCP port: 14033 --- apiVersion: v1 kind: Service metadata: name: mssql-2 spec: clusterIP: None selector: statefulset.kubernetes.io/pod-name: mssql-2 ports: - name: dxl protocol: TCP port: 7979 - name: dxc-tcp protocol: TCP port: 7980 - name: dxc-udp protocol: UDP port: 7981 - name: sql protocol: TCP port: 1433 - name: listener protocol: TCP port: 14033 --- apiVersion: apps/v1 kind: StatefulSet metadata: name: mssql spec: serviceName: "mssql" replicas: 3 podManagementPolicy: Parallel selector: matchLabels: app: mssql template: metadata: labels: app: mssql spec: securityContext: fsGroup: 10001 containers: - name: sql image: mcr.microsoft.com/mssql/server:2019-latest env: - name: ACCEPT_EULA value: "Y" - name: MSSQL_ENABLE_HADR value: "1" - name: MSSQL_SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: MSSQL_SA_PASSWORD volumeMounts: - name: mssql mountPath: "/var/opt/mssql" - name: dxe image: dh2i/dxe env: - name: MSSQL_SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: MSSQL_SA_PASSWORD volumeMounts: - name: dxe mountPath: "/etc/dh2i" volumeClaimTemplates: - metadata: name: dxe spec: accessModes: - ReadWriteOnce resources: requests: storage: 1Gi - metadata: name: mssql spec: accessModes: - ReadWriteOnce resources: requests: storage: 1Gi Here is how the cluster looks after I deploy the above script and run the commands described in the document to configure SQL Server Always On availability groups. In total, I have three pods, each pod running two containers, one SQL Server container and the other DH2i sidecar container to provide the required cluster stack for Always On availability groups. I have three SQL Server instances: mssql-0 (Primary), mssql-1 (Secondary), and mssql-2 (Secondary). When you look at the resources using SSMS and the Always On dashboard here is how the resources are configured: As we used the sidecar architecture during the deployment, we will now upgrade the SQL Server instances from SQL Server 2019 to SQL Server 2022 independently of the DH2i containers running on the same pod. Upgrade the SQL Server 2019 instances to SQL Server 2022: To begin the upgrade process, use the command below to change the update strategy on the statefulset deployment from Rolling Upgrade to OnDelete upgrade. C:\>kubectl patch statefulset mssql -p "{\"spec\": {\"updateStrategy\": {\"type\": \"OnDelete\",\"rollingUpdate\": null}}}" statefulset.apps/mssql patched # Let us now confirm that the preceding command was successful and that the Update strategy has been changed to the OnDelete option. C:\>kubectl describe statefulset mssql Name: mssql Namespace: default CreationTimestamp: Tue, 25 Apr 2023 16:48:58 +0530 Selector: app=mssql Labels: <none> Annotations: <none> Replicas: 3 desired | 3 total Update Strategy: OnDelete Pods Status: 3 Running / 0 Waiting / 0 Succeeded / 0 Failed Pod Template: Labels: app=mssql Containers: sql: Image: mcr.microsoft.com/mssql/server:2019-latest .. .. .. With this, we can now update the statefulset to use the SQL Server 2022 image by running the command below. Please keep in mind that running this command will not trigger the upgrade; the upgrade will occur only when you delete the pod and kubernetes recreates the deleted pod. C:\>kubectl set image statefulset mssql sql=mcr.microsoft.com/mssql/server:2022-latest statefulset.apps/mssql image updated # Verify that the image is updated. C:\>kubectl describe statefulset mssql Name: mssql Namespace: default CreationTimestamp: Tue, 25 Apr 2023 16:48:58 +0530 Selector: app=mssql Labels: <none> Annotations: <none> Replicas: 3 desired | 3 total Update Strategy: OnDelete Pods Status: 3 Running / 0 Waiting / 0 Succeeded / 0 Failed Pod Template: Labels: app=mssql Containers: sql: Image: mcr.microsoft.com/mssql/server:2022-latest .. .. Now that we are ready to upgrade the SQL Server containers, we will begin by updating all our secondary servers in accordance with best practices for rolling upgrades for SQL Server Always On availability groups. Before we begin the upgrade, we must ensure that our secondary replicas are in synchronized state, as shown below I perform the upgrade on the mssql-2 instance by deleting the pod with the following command: C:\>kubectl delete pod mssql-2 and you'll notice that the pod is recreated by the Kubernetes cluster, as shown below; the upgrade took only 6 seconds And a few more seconds for the mssql-2 instance to rejoin the AG; as shown below, the mssql-2 instance is now upgraded to SQL 2022 version, and the database has rejoined the AG in "Synchronized/In-recovery" mode. At all times, the primary pod mssql-0 was still operational and accepting connections, and the secondary pod (mssql-1) allowed read-only connections. Now that we are ready to upgrade the other secondary, mssql-1, we use the same command to delete the pod: C:\>kubectl delete pod mssql-1 And you'll see the same thing happen: the pod is deleted and recreated with the new image, rejoining the AG with the database state "Synchronized/In-Recovery", as shown below: It is now time to upgrade the primary pod, mssql-0, but before we do so, we must first failover the AG from the current primary, mssql-0, to one of the upgraded secondaries; in this case, I will failover to mssql-1 and make it the primary. To perform the failover, I use the DxAdmin and follow the steps below Once the failover is completed the dashboard looks like this: As you can see, mssql-1 is now the new primary, and the old primary (mssql-0) cannot sync because it is running an older version of SQL Server. We upgrade the old primary mssql-0 by deleting the pod, as shown below. C:\>kubectl delete pod mssql-0 When the old primary mssql-0 rejoins the AG as secondary, the database will be in the "Not synchronizing/In-recovery" state, which is normal. You will need to resume the AG by running the T-SQL command on the mssql-0 instance as shown below, and then the database will join as secondary and should be in sync: alter database [test] set hadr resume That's it; you've finished upgrading the SQL Server containers deployed on Kubernetes and configured for Always On availability groupsMicrosoft SQL Server on Ubuntu pro-The preferred choice for deploying SQL Server on Ubuntu in Azure!
Today, you can deploy SQL Server on Ubuntu based Azure virtual machines (VMs) in one of the three ways: Option 1: Use the pre-configured Microsoft SQL Server on Ubuntu Pro Azure marketplace image for the Azure virtual machine(VM) creation. Option 2: Deploy a Azure VM based on an Ubuntu Pro image and then manually install and configure the SQL Server. Option 3: Deploy an Ubuntu LTS image based Azure VM, then manually install and configure SQL Server. In this blog, I'd like to spend some time discussing option 1, which is Microsoft SQL Server on Ubuntu Pro Azure marketplace images which were introduced in November 2021 last year. And, why should you consider this as the preferred alternative? The first question to consider is: What additional capabilities are offered by Ubuntu Pro? To help you answer this question, I’d recommend you to read the below articles published by Canonical, which clearly document the advantages of Ubuntu Pro, such as FIPS & CC-EAL2 certification, open-source security, kernel live patch. Ubuntu Pro for Azure | Ubuntu The benefits of running Microsoft SQL Server on Ubuntu Pro | Ubuntu. Now that you are aware of the benefits of Ubuntu Pro, option 3 is no longer among the preferred options as it does not use Ubuntu pro. As a result, we are left with two options: Option 1 and Option 2. The most important aspect to consider for any production grade database workload is the supportability of the entire solution stack, and this is where option 1 of using pre-configured Microsoft SQL Server on Ubuntu Pro Azure marketplace images gives you an advantage, as the SQL Server Azure VMs deployed on Ubuntu Pro using the above image, are a fully 24/7 supported stack from both Microsoft and Canonical. You can basically open a support ticket through the Azure portal for assistance, and both the Microsoft and Canonical teams will work together to promptly provide you with the required support. When you choose option 2, which is where you first deploy an Ubuntu Pro Azure VM and then manually deploy SQL Server, you still have gaps in your support coverage. Ubuntu Pro is designed to be a cost-effective way to increase the security of your Ubuntu estate and by default does not include any technical support. Technical support can be added to Ubuntu Pro with a private offer or separate support subscription from Canonical, but even so the support will be for the operating system (OS) and the database separately & independent of each other. In contrast, when using the pre-configured SQL Server on Ubuntu Pro marketplace image, you get 24/7 support for the entire stack by default. Hence, the preferred deployment method for a production grade workload deployment for SQL Server on Ubuntu is to use the pre-configured Microsoft SQL Server on Ubuntu Pro Azure marketplace image for creating the SQL Server VMs in Azure. So get started with your production workload deployments on SQL Server on Ubuntu Pro Azure VM using the SQL Server on Ubuntu Pro in Azure Gallery image!!SQL Server 2022 Release Candidate 1 is now available on Linux
Continuing with our release cadence, we are pleased to announce the release of SQL Server 2022 Release Candidate 1 (RC 1) for Linux. To download the latest RC 1 container images, please use the ‘2022-latest’ tags for both RHEL and Ubuntu based container images. Or you could also use the following tags : For RHEL-based SQL Server containers : "2022-RC1-rhel-8.5" For Ubuntu-based SQL Server containers : "2022-RC1-ubuntu-20.04" Please see SQL Server 2022 public preview blog for detailed instructions on how to get started with the container images. To install the SQL Server 2022 RC 1 packages, follow these steps: For RHEL-based installations see RHEL: RHEL: Install SQL Server on Linux For SLES-based installations refer: SLES: Install SQL Server on Linux For Ubuntu-based installations refer: Ubuntu: Install SQL Server on Linux In addition to the new features added in RC 1, this Linux release includes a preview of SQL Server 2022 packages for SLES 15 distributions. Also, the configuration of PMEM for SQL Server on Linux is supported since SQL Server 2019, you can read Configure persistent memory (PMEM) - Linux - SQL Server for further details. For information on the features supported, see : Editions and supported features of SQL Server 2022 Preview - Linux - SQL Server , and for release notes, see Release notes for SQL Server 2022 Preview on Linux - SQL Server
