Kubernetes
Due to the clustered architecture of Kubernetes, there are special storage considerations when running SQL Server instances on Kubernetes. Kubernetes has created its own method of dealing with persistent storage in a Kubernetes cluster using persistent volumes.
Dell Container Storage Modules (CSM) for Unity XT enable integration and automation of Unity XT storage resources to Kubernetes. Unity XT storage integrates with Kubernetes through the Unity XT CSI driver. The Unity XT CSI driver and CSI drivers for other Dell Technologies products can be found on GitHub.
Whenever possible, perform storage operations through Kubernetes. In some cases, modifying CSI volumes directly on the Unity XT appliance can cause CSI operations to fail or volumes to be orphaned. For example, CSI volumes added to a consistency group need to be removed from the consistency group before the CSI driver can delete them.
The T-SQL snapshot backup and restore process is streamlined on Kubernetes. All snapshot and volume orchestration is handled by Kubernetes and the Dell Unity CSI driver. Therefore, all operations can be performed through the Kubernetes control plane. This eliminates the need for interaction with Dell Unity or the host operating system.
Backup workflow
- Issue the following ALTER DATABASE command to prepare the database for snapshot where SnapTest is your database name: ALTER DATABASE SnapTest SET SUSPEND FOR SNAPSHOT_BACKUP=ON
- Create the snapshot on Unity XT. This is done by creating a K8s Volume Snapshot. Below is an example of the YAML used to create a Volume Snapshot “mssql-data-snap” of a Persistent Volume Claim “mssql-data”. Perform a Volume Snapshot for each Persistent Volume Claim that contains database data or log files.
apiVersion: snapshot.storage.k8s.io/v1
kind: VolumeSnapshot
metadata:
name: mssql-data-snap
spec:
volumeSnapshotClassName: unity-snapshot
source:
persistentVolumeClaimName: mssql-data
NOTE: At the time of writing, it is possible to snapshot a volume group with the Unity XT CSI driver, but not recover the volume group. Recovery of a volume group is done at the volume level. Therefore, this example operates at the volume, not the volume group level for workflow consistency.
- Optionally, volume snapshots can be viewed with the kubectl get volumesnapshot command to verify they were created successfully. The new snapshots will also be visible in the Unisphere UI.
- Issue a BACKUP DATABASE command using the option WITH METADATA_ONLY. This will create an entry in the backup metadata file for this backup. The MEDIANAME and MEDIADESCRIPTION fields are used to store information about the location of the snapshot: BACKUP DATABASE SnapTest TO DISK = '/var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm' WITH METADATA_ONLY,NOFORMAT,MEDIANAME='Dell UnityXT PS8',MEDIADESCRIPTION='K8s volume snapshots',NAME='Unity U8',DESCRIPTION='PVCs: mssql-data mssql-data2 mssql-log'
Restoring SQL Server databases on K8s
Due to storage volume functionality in Kubernetes, the concept of removing or altering storage volumes from running containers (in this case a SQL Server Instance) is not supported. Therefore, it is recommended to run a single database per containerized instance of SQL Server. A single database per instance allows the entire container/SQL Server instance to be dropped and re-created to accommodate storage addition and removal. The examples below assume a single database per SQL Server instance.
For more information about K8s Persistent Volumes, refer to the Kubernetes documentation on Persistent Volumes.
Restore workflow
- If the database exists, drop the existing database by deleting the pod definition for the SQL Server instance and the Persistent Volume Claims (PVC) that contains database data and log files. A suggested way to do this is to contain all objects in a K8s deployment, and then delete the deployment.
- Create PVCs and the SQL Server pod. The new PVCs will reference a snapshot as the dataSource. In the example below, the PVC “mssql-data-copy” is being created based on the data source “mssql-data-snap”. Create a PVC for each volume required.
Note: Make sure the storage size matches the size of the source volume or an error will result.
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: mssql-data-copy
labels:
volume-group: db
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 5500Gi
storageClassName: unity-xfs
dataSource:
name: mssql-data-snap
kind: VolumeSnapshot
apiGroup: snapshot.storage.k8s.io
- Connect to the new instance and issue the RESTORE DATABASE WITH METADATA_ONLY command to begin the recovery process. The examples below recover the database named SnapTest using a backup metadata file “/var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm” and specify the location of the data and log files. These locations must match the mount paths in the SQL Server instance deployment definition.
- When performing a simple recovery, use the RECOVERY option to immediately bring the database online: RESTORE DATABASE SnapTest FROM DISK = /var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm' WITH FILE=1, MOVE 'SnapTest1' to '/var/opt/mssql/data1/snaptest1.mdf',MOVE 'SnapTest2' to '/var/opt/mssql/data2/snaptest2.ndf',MOVE 'SnapTest_log' to '/var/opt/mssql/log/snaptest.log', METADATA_ONLY,RECOVERY
- When performing a point-in-time recovery and applying differential, transaction log backups, or both use the NORECOVERY option: RESTORE DATABASE SnapTest FROM DISK = ' /var/opt/mssql/backup/SnapTest_SQLBackupFull.bkm' WITH FILE=1, , MOVE 'SnapTest1' to '/var/opt/mssql/data1/snaptest1.mdf',MOVE 'SnapTest2' to '/var/opt/mssql/data2/snaptest2.ndf',MOVE 'SnapTest_log' to '/var/opt/mssql/log/snaptest.log', METADATA_ONLY,NORECOVERY
- For point in time recoveries, apply differential or transaction log backups per the standard recovery process.