Home > Workload Solutions > SQL Server > White Papers > Dell EMC PowerMax Storage for Mission-Critical SQL Server Databases > Deployment of SQL Server infrastructure
Deploying a stateless container into a Kubernetes cluster is quite simple, but SQL Server is stateful. Therefore, we need a way to persist the created data across pod restarts. Kubernetes provides us with the means to implement persistent storage. This section covers the deployment of SQL Server on Kubernetes under different scenarios including restoring an existing database backup from the PowerMax volume, as well by using a snapshot of the existing data volumes on PowerMax.
Kubernetes namespaces are intended for use in environments with many users spread across multiple teams or projects. Namespaces provide a scope for names and provide a way to divide cluster resources among multiple users (via resource quota). It is recommended to have a separate namespace for SQL. Create a Kubernetes namespace with the name “mssql” using this command:
# kubectl create namespace mssql
Create a password for SQL user SA before you create the SQL Server container. The recommended method is to create a Kubernetes secret and use it for the SA password. The following command creates a password for the SA account. Set SA_PASSWORD to that password that you would like to use with this command:
# kubectl create secret generic mssql --namespace mssql --from-literal=SA_PASSWORD="MySecretP@ssw0rd"
Note: SA password should be at least 8 characters long and must contain a combination of uppercase letters, lowercase letters, 0 - 9 numerals and non-alphanumeric characters. If minimum password requirements are not met, SQL Server container will fail to come up. Detailed password requirements can be found at Password Policy.
Secrets can also be created by using manifest files as described in the Kubernetes documentation about using Secrets. When a secret is being used for SQL SA password, the deployment manifest for SQL Server will have password setting as shown below:
env:
- name: MSSQL_PID
value: "Developer"
- name: ACCEPT_EULA
value: "Y"
- name: MSSQL_SA_PASSWORD
secretKeyRef:
name: mssql
key: SA_PASSWORD
Instead of using a Kubernetes secret, the password can be specified in the manifest file for the deployment. This method is the simplest one, but it is not recommended as it exposes the password clearly in text:
env:
- name: MSSQL_PID
value: "Developer"
- name: ACCEPT_EULA
value: "Y"
- name: MSSQL_SA_PASSWORD
value: "MySecretP@ssw0rd"
Kubernetes pods are ephemeral by nature, so the data does not survive through the restart/re-scheduling of a pod. The Kubernetes persistent volume (PV) framework allows administrators to provision persistent storage for a cluster. Using persistent volume claims (PVCs), developers can request storage resources defined by a Storage Class (SC) without having specific knowledge of the underlying storage infrastructure.
Once the PowerMax CSI plug-in has been installed on a Kubernetes cluster, it creates a default storage class using parameters from the myvalues.yaml file that was created during plug-in installation. You can also create your own storage class by specifying parameters that determine how storage is provisioned on the Dell EMC PowerMax array. Some of the important parameters that you need to define while creating a storage class are:
Note: For valid values for parameters SYMID, SRP and ServiceLevel, consult your PowerMax administrator. These values are specific to your environment. To better understand various service levels, see DellEMC PowerMax: Service Levels for PowerMaxOS.
Below are two examples of Storage Class. The first example specifies only mandatory parameters.
kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
name: pmax-8000-449
provisioner: csi-powermax
parameters:
SRP: "SRP_1"
ServiceLevel: "Diamond"
SYMID: "000197600358"
The second example specifies File System type as XFS and reclaim policy as Retain. Setting reclaim policy to Retain will not delete the PowerMax volume when the associated PV ID is deleted. This is useful when you want to preserve the data on the volume for future use.
apiVersion: storage.k8s.io/v1
kind: StorageClass
metadata:
name: pmax-retain
parameters:
SRP: SRP_1
SYMID: "000197600358"
ServiceLevel: Diamond
FsType: xfs
provisioner: csi-powermax
volumeBindingMode: Immediate
You can create Persistent Volumes (PV) and Persistent Volume Claims (PVC) using these storage classes. These PVC names can be used in the pod manifests where you can specify which containers need these volumes and where they must be mounted.
In general, a container’s root filesystem is not suitable for storing persistent data. The containers you run on Kubernetes Engine are typically disposable entities, and the cluster manager may delete, evict, or reschedule any containers that become unavailable due to node failure or other causes. In such an occurrence, all data saved to a container’s root filesystem is lost.
To deploy SQL server in Kubernetes cluster with persistent volumes from PowerMax, create at least three PVCs with the appropriate Storage Class using a manifest. One volume is for the SQL Server installation which will be mounted as /var/opt/mssql so that the SQL Server configuration can persist. The other two volumes are for data and log respectively. The number of data and log volumes can be increased as needed. It is recommended to have at least two different volumes: one volume for data and one for log. Having data and log on the same volume is not recommended as described earlier in this paper. A sample manifest for creating PVCs to be used by SQL server is given below:
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: mssql-server-1
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 10Gi
storageClassName: pmax-358
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: mssql-data-1
namespace: mssql
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1024Gi
storageClassName: pmax-358
---
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: mssql-log-1
namespace: mssql
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 250Gi
storageClassName: pmax-358
Create PVCs using the manifest. It will create a PVC and an associated PV.
# kubectl create -f pvc-449-mssql-server-data-log.yaml
persistentvolumeclaim/mssql-server-1 created
persistentvolumeclaim/mssql-data-1 created
persistentvolumeclaim/mssql-log-1 created
Use Kubectl command to list PVCs created by manifest and their associated PVs. The volume capacity may be larger that requested in the manifest. This is not a problem.
# kubectl get pvc -n mssql
NAME STATUS VOLUME CAPACITY ACCESS MODES STORAGECLASS AGE
mssql-data-1 Bound pmax-5f45ddbfb2 1025Gi RWO pmax-358 29s
mssql-log-1 Bound pmax-5f464d68b2 251Gi RWO pmax-358 29s
mssql-server-1 Bound pmax-5f451fb8b2 11Gi RWO pmax-358 29s
# kubectl get pv -n mssql
NAME CAPACITY ACCESS MODES RECLAIM POLICY STATUS CLAIM STORAGECLASS REASON AGE
pmax-5f451fb8b2 11Gi RWO Delete Bound sql/mssql-server-1 pmax-449 8m53s
pmax-5f45ddbfb2 1025Gi RWO Delete Bound sql/mssql-data-1 pmax-449 8m53s
pmax-5f464d68b2 251Gi RWO Delete Bound sql/mssql-log-1 pmax-449 9m7s
If a containerized SQL Server needs to restore backup from an existing PowerMax volume or needs to use existing PowerMax volumes for data and log, you need to create a PV using the existing PowerMax volume. Create this PV with Storage Class with ReclaimPolicy set to Retain. Use the kubectl command to verify the ReclaimPolicy setting for the Storage Class:
# kubectl describe sc pmax-retain-358
Name: pmax-retain-358
IsDefaultClass: No
Annotations: <none>
Provisioner: csi-powermax
Parameters: SRP=SRP_1,SYMID=000197600358,ServiceLevel=Bronze
AllowVolumeExpansion: <unset>
MountOptions: <none>
ReclaimPolicy: Retain
VolumeBindingMode: Immediate
Events: <none>
Identify the PowerMax volume that needs to be used for PV and find its volume identifier. If there is no volume identifier for the volume, set a unique identifier using Unisphere or SymCLI. The command symdev -sid <sym> list -identifier device_name displays the list of volumes with identifier.
# symdev -sid 358 list -identifier device_name
Symmetrix ID: 000197600358
Device
-------------------------------------------------------
Sym Config Attr Device Name
----- --------------- ---- ----------------------------
000F7 TDEV SQLbackup1TB
000FC TDEV csi-K8S-pmax-2a908fccb3
000FF TDEV csi-K8S-pmax-88f40510b4
00101 TDEV csi-K8S-pmax-88f4c8f6b4
00108 TDEV csi-K8S-pmax-706fba4db9
Unisphere shows the volume identifier in the volume details next to the device ID:
Figure 15. Locating volume identifier for a PowerMax volume using Unisphere
If the volume identifier is not set or needs to be changed, use SymCLI or Unisphere. Do not change the volume identifier if the volume is in use by the CSI driver, because the volume identifier is validated for CSI operations. A volume is in use by CSI driver if a Persistent Volume (PV) is already mapped to the volume.
# symdev -sid 358 set 000F7 -device_name sqlbackup2
Figure 16. Setting volume identifier using Unisphere
A volume identifier is used to specify the volumeHandle for PV. The volume handle needs to be in the format <volumeIdentifier>-<SymmID-<device ID>. For example, if volume is 000F7 on PowerMax 000197600358 and identifier is set to “SQLbackup1TB”, volumeHandle would be SQLbackup1TB-000197600358-000F7 as shown in the manifest for PV and PVC:
apiVersion: v1
kind: PersistentVolume
metadata:
name: mssql-backup
namespace: mssql
spec:
accessModes:
- ReadWriteOnce
capacity:
storage: 1024Gi
csi:
driver: csi-powermax.dellemc.com
volumeHandle: SQLbackup1TB-000197600358-000F7
persistentVolumeReclaimPolicy: Retain
storageClassName: pmax-retain
volumeMode: Filesystem
---
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: mssql-backup
namespace: mssql
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 1024Gi
storageClassName: pmax-retain-358
volumeMode: Filesystem
volumeName: mssql-backup
apiVersion: apps/v1beta1