Logical Microsoft SQL Server Backup
MS SQL Server is a relational database developed by Microsoft. The example below covers SQL Server instances running natively on Kubernetes. Use the following commands to deploy the SQL Server using Kubernetes manifests.
$ kubectl create ns sqlserver
$ kubectl create secret generic mssql --from-literal=SA_PASSWORD="MyC0m9l&xP@ssw0rd" -n sqlserver
$ cat <<EOF | kubectl create -f -
kind: PersistentVolumeClaim
apiVersion: v1
metadata:
name: mssql-data
namespace: sqlserver
spec:
accessModes:
- ReadWriteOnce
resources:
requests:
storage: 8Gi
EOF
$ cat <<EOF | kubectl create -f -
apiVersion: apps/v1
kind: Deployment
metadata:
name: mssql-deployment
namespace: sqlserver
labels:
app: mssql
spec:
replicas: 1
selector:
matchLabels:
app: mssql
template:
metadata:
labels:
app: mssql
spec:
terminationGracePeriodSeconds: 30
hostname: mssqlinst
securityContext:
fsGroup: 10001
containers:
- name: mssql
image: mcr.microsoft.com/mssql/server:2019-CU27-ubuntu-20.04
ports:
- containerPort: 1433
env:
- name: MSSQL_PID
value: "Developer"
- name: ACCEPT_EULA
value: "Y"
- name: SA_PASSWORD
valueFrom:
secretKeyRef:
name: mssql
key: SA_PASSWORD
volumeMounts:
- name: mssqldb
mountPath: /var/opt/mssql
volumes:
- name: mssqldb
persistentVolumeClaim:
claimName: mssql-data
EOF
$ cat <<EOF | kubectl create -f -
apiVersion: v1
kind: Service
metadata:
name: mssql-deployment
namespace: sqlserver
spec:
selector:
app: mssql
ports:
- protocol: TCP
port: 1433
targetPort: 1433
type: ClusterIP
EOF
Next create a file mssql-blueprint.yaml with the following contents
apiVersion: cr.kanister.io/v1alpha1
kind: Blueprint
metadata:
name: mssql-blueprint
actions:
backup:
outputArtifacts:
mssqlCloudDump:
kopiaSnapshot: "{{ .Phases.dumpToObjectStore.Output.kopiaOutput }}"
phases:
- func: MultiContainerRun
name: dumpToObjectStore
objects:
mssql:
kind: Secret
name: '{{ index .Object.metadata.labels "app" }}'
namespace: '{{ .Deployment.Namespace }}'
args:
sharedVolumeMedium: Memory
initImage: '{{if index .Options "kanisterImage" }} {{- .Options.kanisterImage -}} {{else -}} ghcr.io/kanisterio/kanister-tools:0.113.0 {{- end}}'
initCommand: ["bash", "-o", "errexit", "-o", "pipefail", "-c", "mkfifo /tmp/data; chmod 666 /tmp/data"]
backgroundImage: ghcr.io/kanisterio/mssql-tools:0.113.0
backgroundCommand:
- bash
- -o
- errexit
- -o
- pipefail
- -c
- |
root_password="{{ index .Phases.dumpToObjectStore.Secrets.mssql.Data "SA_PASSWORD" | toString }}"
root_uname="sa"
mssql_pod="{{ index .Deployment.Namespace }}/{{ index .Deployment.Pods 0 }}"
server_name="{{ index .Deployment.Name }}.{{index .Deployment.Namespace}}.svc.cluster.local"
databases=$(/opt/mssql-tools/bin/sqlcmd -S ${server_name} -U ${root_uname} -P ${root_password} -Q "SET NOCOUNT ON; SELECT name FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb')" -b -s "," -h -1)
for database in $databases; do /opt/mssql-tools/bin/sqlcmd -S ${server_name} -U ${root_uname} -P ${root_password} -Q "backup database $database to disk = '/tmp/backup/$database.bak' with format;"; done
kubectl cp ${mssql_pod}:/tmp/backup /tmp/backup
tar zcvf - -C /tmp/ backup > /tmp/data
kubectl exec -it {{ index .Deployment.Pods 0 }} -n {{ index .Deployment.Namespace }} -- rm -r /tmp/backup
outputImage: '{{if index .Options "kanisterImage" }} {{- .Options.kanisterImage -}} {{else -}} ghcr.io/kanisterio/kanister-tools:0.113.0 {{- end}}'
outputCommand:
- bash
- -o
- errexit
- -o
- pipefail
- -c
- |
kopiaPath="backup.tar.gz"
cat /tmp/data | kando location push --profile '{{ toJson .Profile }}' --path "${kopiaPath}" --output-name "kopiaOutput" -
restore:
inputArtifactNames:
- mssqlCloudDump
phases:
- func: MultiContainerRun
name: restoreFromObjectStore
objects:
mssql:
kind: Secret
name: '{{ index .Object.metadata.labels "app" }}'
namespace: '{{ .Deployment.Namespace }}'
args:
sharedVolumeMedium: Memory
initImage: '{{if index .Options "kanisterImage" }} {{- .Options.kanisterImage -}} {{else -}} ghcr.io/kanisterio/kanister-tools:0.113.0 {{- end}}'
initCommand: ["bash", "-o", "errexit", "-o", "pipefail", "-c", "mkfifo /tmp/data; chmod 666 /tmp/data"]
backgroundImage: '{{if index .Options "kanisterImage" }} {{- .Options.kanisterImage -}} {{else -}} ghcr.io/kanisterio/kanister-tools:0.113.0 {{- end}}'
backgroundCommand:
- bash
- -o
- errexit
- -o
- pipefail
- -c
- |
kopiaPath="backup.tar.gz"
kopia_snap='{{ .ArtifactsIn.mssqlCloudDump.KopiaSnapshot }}'
kando location pull --profile '{{ toJson .Profile }}' --path "${kopiaPath}" --kopia-snapshot ${kopia_snap} - > /tmp/data
outputImage: ghcr.io/kanisterio/mssql-tools:0.113.0
outputCommand:
- bash
- -o
- errexit
- -o
- pipefail
- -c
- |
root_password="{{ index .Phases.restoreFromObjectStore.Secrets.mssql.Data "SA_PASSWORD" | toString }}"
root_uname="sa"
mssql_pod="{{ index .Deployment.Namespace }}/{{ index .Deployment.Pods 0 }}"
server_name="{{ index .Deployment.Name }}.{{ index .Deployment.Namespace }}.svc.cluster.local"
cat /tmp/data | tar zxvf - -C /tmp/
kubectl cp /tmp/backup ${mssql_pod}:/tmp/backup
backup_files=$(ls /tmp/backup)
for script in $backup_files; do database="$(cut -d'.' -f1 <<<"$script")"; /opt/mssql-tools/bin/sqlcmd -S ${server_name} -U ${root_uname} -P ${root_password} -Q "restore database $database from disk = '/tmp/backup/$script' with replace"; done
kubectl exec -it {{ index .Deployment.Pods 0 }} -n {{ index .Deployment.Namespace }} -- rm -r /tmp/backup
delete:
inputArtifactNames:
- mssqlCloudDump
phases:
- func: KubeTask
name: deleteFromBlobStore
args:
image: '{{if index .Options "kanisterImage" }} {{- .Options.kanisterImage -}} {{else -}} ghcr.io/kanisterio/kanister-tools:0.113.0 {{- end}}'
command:
- bash
- -o
- errexit
- -o
- pipefail
- -c
- |
kopiaPath="backup.tar.gz"
kopia_snap='{{ .ArtifactsIn.mssqlCloudDump.KopiaSnapshot }}'
kando location delete --profile '{{ toJson .Profile }}' --path ${kopiaPath} --kopia-snapshot "${kopia_snap}"
And then apply the file using:
$ kubectl apply -f mssql-blueprint.yaml --namespace kasten-io
Note
The provided Microsoft SQL Server backup example serves as a blueprint template for logical backups on Kubernetes. Please note that these examples may need to be modified for specific production environments and setups. As a result, it is highly recommended to carefully review and modify the blueprints as needed before deploying them for production use.
Alternatively, use the Blueprints page on Veeam Kasten Dashboard to create the Blueprint resource.
Once the Blueprint is created, add an annotation to the SQL Server Deployment to instruct Veeam Kasten to use the Blueprint when performing operations on this instance.
$ kubectl annotate deployment mssql-deployment \
kanister.kasten.io/blueprint='mssql-blueprint' --namespace sqlserver
Finally, use Veeam Kasten to backup and restore the application.
Known Limitations
Currently, the backup process in the Kanister Blueprint creates the temporary database backup files in the same volume as the database. Due to this, it is necessary to use a PVC at least twice the size of the database.