You’ve been a SQL Server DBA for many years, and now you have a project to deploy SQL Server containers as part of Data Estate modernization, or as part of the company vision to diversify your environment, you need to deploy SQL Server on the Linux ecosystem, which could be on a Virtual Machine or BareMetal running on-prem or on cloud like Azure. If you are this DBA now or could be in few months of time, then this article is for you to get started on this wonderful journey.
In this article, I’ve attempted to answer some of the common questions that users have when they first start working with SQL Server on Linux/Containers. We do have a document in place for SQL Server on Linux FAQs; nonetheless, this article may contain some queries that are similar to those already answered in the FAQ area. But the idea is to also provide you with the SQL Server containers context for those questions.
Here, I attempt to answer some of the common questions that I’ve been asked at various conferences, customer meetings, and by our support team when working with SQL Server on Linux/containers. Wherever feasible, I’ve tried to respond to these queries using reference links to the documentation, as these are continuously updated and maintained by us, hence ensuring that whenever you refer to this article you get the updated information. Having stated that, it’s now time to let the questions rolling:
• Is the SQL Server engine same across SQL Server deployed on any operating system or environment?
Amit: The SQL Server engine is same across SQL Server deployed on different operating systems, be it SQL Server on Linux, SQL Server on Windows, SQL Server on Linux based containers deployed on standalone hosts or kubernetes platforms. The only difference is there are few features which are currently not supported as of today on SQL Server on Linux/containers; we are working to ensure that we soon bring parity for those features as well. The current list of unsupported features for SQL Server on Linux or containers is available at Unsupported features & services for your reference.
• Is SQL Server on windows-based containers supported for production?
Amit: As of today, when writing this article, SQL Server running on Windows container is not supported for production workload. It was in preview mode for some time, but due to the current ecosystem limitations, for now it is unsupported, and out of beta program as well.
• How do I access SQL Server configuration manager for SQL Server on Linux, or in simple words how do I configure SQL Server on Linux?
Amit: You can configure SQL Server on Linux using the mssql-conf tool. All the tasks such as changing the SQL Server port, Enabling AD authentication, enabling/disabling trace flags and more can be done through the mssql-conf tool. You can run the command “mssql-conf –help” to understand the various settings available using the mssql-conf tool.
• Are SQL Server on Linux containers deployed on supported Linux distributions supported for production workload? What are the support boundaries?
Amit: Yes, SQL Server on Linux container is supported for production workload. You can deploy SQL Server on Linux based containers on any of the supported Linux distributions. You can obtain the SQL Server images from the Microsoft container registry, to discover the Ubuntu based SQL containers images you can refer to docker hub and to discover the Redhat based containers you can refer to Redhat container catalog. SQL Server images can also be created using custom docker file, but ensure that you follow the support guidelines available here when you create the custom docker file.
• How do I create SQL Server Linux container image for SUSE based host? Is there a sample dockerfile that I can refer to?
Amit: You can create SQL Server Linux container image for SUSE based host through dockerfile, the sample for the same is available here for reference. This is also supported for production.
• Can I deploy SQL Server Linux containers using podman as well? like I do with docker.
Amit: Yes, Podman/docker both are supported and can be used to deploy and run SQL Server containers.
• If I am new to Linux ecosystem but well versed with SQL Server, is there any tutorial that I can refer to learn about SQL Server on Linux?
Amit: If you are planning to start working on SQL Server on Linux with previous experience on SQL Server on Windows, you would find that SQL Server on Linux is not all that different, only the setup experience and ecosystem experience changes. To get started, you can refer to this tutorial to help you with the basics of Linux and then you can further build on the basics based on your interest. Similarly, if you are well versed with Linux, but new to SQL Server you can start with this tutorial.
• Can I attach/move/restore databases across SQL Server instances running on various Operating systems or on containers?
Amit: Absolutely, the SQL Server engine is same across all deployments of SQL Server. So, you can take the back up of a database from SQL Server Linux based containers, move it to the Windows environment and then restore it on a SQL Server on Windows on bare metal/VM. In fact, we have documentation available which talks about database migration to SQL on Linux.
• Is there a guided recommendation on how to deploy SQL Server on Linux for best performance?
Amit: Yes, we have a detailed documentation available here which talks about various performance configurations that can be set at Storage, filesystem, kernel, CPU and more for optimal SQL Server performance. Most of the recommendations mentioned in this document can also be applied to the host machine running SQL Server containers, like the storage setting can be applied on the host if the persistent storage is presented from the host, the tuned profile setting can be applied to the host machine, etc.
• Can I go ahead, and limit resources assigned to SQL Server containers?
Amit: Yes, when deploying SQL Server containers, you can set resource limits like CPU and memory for each SQL Server container that is deployed. The docker flags like ‘–cpus’ and ‘—memory’ can be used to set the resource limits.
Let’s take an example to understand this further let’s say you have a total of 16 logical processors on the host and when you deploy the container you use the following command,
docker run -e “ACCEPT_EULA=Y” -e “SA_PASSWORD=strongpassword” -p 1433:1433 –name sql1 –hostname sql1 –cpus 8 -d mcr.microsoft.com/mssql/server:2019-CU10-ubuntu-18.04
This command ensures you limit the SQL Server container to use only 8 logical processors out of the 16, in the error log you will see that SQL Server can see all the 16 logical processors, but it only utilizes 8 out of the 16 CPUs to run the workload.
• Can I deploy SQL Server on Kubernetes cluster and is this supported?
Amit: Yes, you can deploy SQL Server on Kubernetes cluster or opiniated kubernetes cluster like openshift and it is supported for production workload. You can deploy SQL Server as statefulset or deployment kind. The sample deployment yaml file is available here for reference and can be used for all kubernetes clusters for deployment.
• How do I configure SQL Server on containers, can I use the mssql-conf tool?
Amit: For SQL Server containers, you can provide the configuration settings by mounting the mssql-conf file inside the container when you deploy SQL Server containers. Some of these configuration settings are also available as environment variables that can be set when deploying the SQL Server containers. You can see the samples in the above referenced article.
• Do you have any sample Helm Charts to deploy SQL Server Containers?
Amit: Yes, you can deploy SQL Server via helm charts and here are some sample helm charts to get you started, Please refer the statefulset helm chart for the statefulset deployment and this for the normal deployment.
• Can we also setup the resource limits when deploying SQL Server Containers on Kubernetes platform?
Amit: Absolutely, you can always use the resource limits option in the deployment yaml file when deploying SQL Server containers. In fact, you should always try and ensure that QoS (Quality of Service) for the SQL Server container/pod is set to guaranteed. This means that SQL Server resource request and resource limits are the same to ensure SQL Server container gets the resources when it is scheduled to run on the worker node by the kubernetes cluster.
• Can I configure AD (Active Directory) authentication for SQL Server on Linux/containers?
Amit: Active directory authentication is supported for both SQL Server on Linux and SQL Server Linux based containers. In fact, you can now use a preview tool called adutil to easily configure the active directory authentication for both SQL Server on Linux and SQL Server Linux based containers. This tool eases the configuration of the active directory authentication by ensuring that you can manage the windows active directory from a Linux machine which is joined to the domain.
When enabling AD authentication for SQL Server on Linux containers you can have an environment where the host machine running the SQL Server container is not joined to the domain, though the SQL Server inside the container is joined to the domain. But, for SQL Server on Linux BareMetal/VM you need to ensure that both the host and the SQL Server service are part of the same domain. Cross domain logins are also supported provided both the domains have two way trust and are part of the same forest.
• I want to setup Always on Availability group between multiple SQL Server containers running on the same kubernetes cluster, how do I do it?
Amit: As of today, when writing this article SQL Server availability group setup on containers is only supported in Read Scale mode and not in any other mode. Hence, you have a DR option using always on availability groups for SQL Server on containers but not HA (High Availability). You can follow this blog to setup Read scale always on availability group on SQL Server containers running in kubernetes.
• If I have more questions, where can I post them or can I write them directly to you?
Amit: Yes, Please send your questions on SQL Server on Linux or containers to firstname.lastname@example.org or follow me on linkedin.com/in/amvin87/ or on twitter.com/amvin87 I’d be more than happy to assist.