Home > Workload Solutions > SQL Server > Guides > Design Guide—SQL Server 2022 Database Solution with Object Storage on Dell Hardware Stack > Containerized environment set up
The architecture details a separate environment using RedHat OpenShift 4.11 to host containerized applications and refers to it as the containerized environment.
RedHat provides step by step guidance for the installation and configuration of OpenShift on bare-metal servers.
Figure 21. RedHat OpenShift deployment summary
With the cluster deployed, it is possible to view cluster information and manipulate the cluster’s settings through RedHat OpenShift web console.
With the OpenShift cluster up and running, the next step is to deploy the Dell CSI Operator so that the containerized environment can access persistent storage.
The Dell CSI Operator is a Kubernetes Operator, which is used to install and manage the CSI Drivers provided by Dell for various storage platforms. This operator is available as a community operator for upstream Kubernetes deployable through OperatorHub.io. It is also available as a certified operator for OpenShift clusters and can be deployed using the OpenShift Container Platform. Both these methods of installation use the OLM (Operator Lifecycle Manager). The operator can also be deployed manually.
The process of installing the Dell CSI operator for Dell PowerStore is captured in detail on the GitHub page for Installing CSI Driver for PowerStore via Operator.
After the successful deployment of the CSI Operator/Plugin for PowerStore on the OpenShift Cluster, a storage class with the name “dellemc-powerstore-sc” is configured within the OpenShift cluster. The name can be changed during the deployment process.
The storage class will be used to create persistent volumes (PV) and persistent volume claims (PVC) which can be mapped to containers and pods to provide persistent storage to the application. Following is a reference for the storage class in OpenShift cluster.
Deploying SQL Server 2022 in an OpenShift cluster is simple. The SQL Server 2022 image was modified to include PolyBase for the testing of potential use cases. PolyBase is not available by default and is used in this solution to enable data virtualization.
PolyBase is not currently installed and enabled by default in the published container image mcr.microsoft.com/mssql/server:2022-latest, so Dell engineers created a custom image with PolyBase installed. The following script is used to create a custom container image for SQL Server 2022.
FROM ubuntu:20.04
#Create file layout for SQL and set permissions
RUN useradd -M -s /bin/bash -u 10001 -g 0 mssql
RUN mkdir -p -m 770 /var/opt/mssql/security/ca-certificates && chgrp -R 0 /var/opt/mssql/security/ca-certificates
# Installing system utilities
RUN apt-get update && \
apt-get install -y apt-transport-https curl gnupg2 && \
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - && \
curl https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-preview.list > /etc/apt/sources.list.d/mssql-server-preview.list
# Installing SQL Server drivers and tools
RUN apt-get update && \
apt-get install -y mssql-server-PolyBase && \
apt-get clean && \
rm -rf /var/lib/apt/lists
RUN /opt/mssql/bin/mssql-conf traceflag 13702 on
# Run SQL Server process as non-root
USER mssql
CMD /opt/mssql/bin/sqlservr
Use the following command to build the docker container image:
docker build -t sql-custom-2022:latest
Change the image artifacts using the tag command.
docker tag sql-custom-2022:latest docker.io/sanran/sql-custom 2022:latest
Then push the newly created container image to docker’s public container registry.
docker push sanran/sql-custom-2022:latest
In this command, sanran refers to a username on the dockerhub.io public container registry. This container image can be pulled to run SQL Server 2022 with PolyBase pre-installed on any setup. More details about using SQL Server with PolyBase can be found in the Use Case section.
The certificate must be mapped so that the Kubernetes cluster can communicate with the Elastic Cloud Storage deployment. This can be done using the Kubernetes ConfigMap, which maps the certificate to a SQL container.
apiVersion: v1
kind: ConfigMap
metadata:
name: rootca-cert
namespace: demo
data:
rootCA.crt: |
-----BEGIN CERTIFICATE-----
MIID9zCCAt+gAwIBAgIUKeay3nw4sQe8QHbIlPr8q4vK7AMwDQYJKoZIhvcNAQEL
BQAwgYoxCzAJBgNVBAYTAlVTMQswCQYDVQQIDAJNQTESMBAGA1UEBwwJSG9wa2lu
dG9uMQ0wCwYDVQQKDAREZWxsMQ8wDQYDVQQLDAZCaXpBcHAxGDAWBgNVBAMMD0hB
.
---- certificate content -----
.
4/XHKa9DXk8g9pBvSpF7HC1DJRee7ZnJ6p4Vme9LyXjmA+OBzaovOU4i54iKwvkl
qXXlevx+E0KLy1QbhL9n49F2zi0SZRsjJ5+A5+gCz9CKhLL9b7VvsYqg/Ok40ZT8
to7ahH31PTtnrzM=
-----END CERTIFICATE-----
There is another way to deploy a ConfigMap within an OpenShift environment. When creating a ConfigMap this way, rootCA.crt is the certificate file that will be used.
oc create ConfigMap ca-s3lb --from-file=rootCA.crt
A PersistentVolumeClaim (PVC) is a request for storage by a user. It is like a Pod, as Pods consume node resources and PVCs consume PV resources. Pods can request specific resource amounts (CPU and Memory). Claims can request specific amounts of storage and can be mounted with ReadWriteOnce, ReadOnlyMany or ReadWriteMany access modes.
To provide persistent storage to a container or pod, a persistent volume claim is created using the Dell CSI storage class. The following script provides an example of how to create a PVC that will be used by the SQL Server pods. This pvc yaml script creates a 500 GB volume in ReadWriteOnce access mode.
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: new-sql-pvc-31445
namespace: demo
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 500Gi
storageClassName: dellcsi-powerstore-sc
A Secret is an object that contains a small amount of sensitive data like a password, token, or key which might otherwise be put in a Pod specification or container image.
Secrets can be created independently of the Pods that use them, reducing the risk of the secrets data being exposed during the workflow of creating, viewing, and editing Pods.
To provide the SA password within the deployment file for the containers, a secret was created in OpenShift’s cluster environment using a base 64 encryption for the password.
apiVersion: v1
kind: Secret
metadata:
name: mssql-secret
namespace: demo
data:
MSSQL_SA_PASSWORD: QFZhbnRhZ2U0
type: Opaque
The deployment section provides a declarative update pattern for pods and replica sets. A preferred state is described, and the deployment controller changes the actual state to match the preferred state at a controlled rate. This allows high availability to be maintained, and it is possible to specify when resources should be destroyed, allowing cluster updates to occur without any loss of service in some cases.
This yaml script is used to deploy SQL Server 2022 on a Kubernetes cluster.
apiVersion: apps/v1
kind: Deployment
metadata:
name: new-sql-deployment
namespace: demo
spec:
replicas: 1
selector:
matchLabels:
app: mssql-31445
template:
metadata:
labels:
app: mssql-31445
spec:
terminationGracePeriodSeconds: 30
hostname: mssqlinst
securityContext:
fsGroup: 10001
containers:
- name: mssql
image: docker.io/sanran/sql-custom-2022:latest
resources:
requests:
memory: "128G"
cpu: "12000m"
limits:
memory: "128G"
cpu: "12000m"
ports:
- containerPort: 31445
env:
- name: MSSQL_PID
value: "Developer"
- name: ACCEPT_EULA
value: "Y"
- name: MSSQL_SA_PASSWORD
valueFrom:
secretKeyRef:
name: mssql-secret
key: MSSQL_SA_PASSWORD
volumeMounts:
- name: mssqldb
mountPath: /var/opt/mssql
- name: rootca-cert
mountPath: /var/opt/mssql/security/ca-certificates/rootCA.crt
subPath: rootCA.crt
readOnly: true
volumes:
- name: mssqldb
persistentVolumeClaim:
claimName: new-sql-pvc-31445
- name: rootca-cert
ConfigMap:
name: rootca-cert
Services are an abstract way to expose an application running on a set of Pods as a network service. With OpenShift and Kubernetes there is no need to modify the application to use an unfamiliar service discovery mechanism. Pods are given IP addresses by Kubernetes. Pods can be load balanced and can share a single DNS.
A Service is an abstraction which defines a logical set of Pods and their access policy. The set of Pods targeted by a Service is usually determined by a selector (in this case it is “mssql-31445”). Specific ports can be specified for container port mapping.
kind: Service
apiVersion: v1
metadata:
name: mssql-service-31445
namespace: demo
spec:
type: NodePort
ports:
- port: 1433
targetPort: 1433
nodePort: 31445
selector:
app: mssql-31445
There are several types of services that can be created such as ClusterIP, NodePort, LoadBalancer, and ExternalName. This example creates a service of type NodePort. More details on the service and service types can be found on the Kubernetes website.
Once the SQL Server pods are running successfully, these SQL Server pods can be connected to just like any SQL Server instance. The above screenshot shows a connection to a SQL Server 2022 instance running in an OpenShift cluster environment.
Many different tools can be used to connect to a SQL Server instance that is running in a pod. SQL Server Management Studio, PowerShell, Visual Studio, third-party SQL monitoring or development tools and other platforms will connect to the container instance as intended.