Ranga Babu
master database log file location in Kubernetes cluster

Azure Kubernetes Service (AKS) – Managing SQL Server database files

June 18, 2019 by

In this article, we will review on managing database files of SQL Server running on Azure Kubernetes service.

Please refer to “Creating a Kubernetes cluster” section in SQL Server in Azure Kubernetes Service (AKS) for creating a Kubernetes cluster using Standard_B2s size VM’s

We will cover the following topics of about managing database file in SQL Server running on Kubernetes cluster.

  1. Create multiple persistent volume claims and mount them to pod running SQL Server container
  2. Changing the location of tempdb database files in Kubernetes
  3. Changing the location master database files in Kubernetes

Mount multiple volumes to the Pod

By default, only one volume is mounted to the pod that is running SQL Server container when we deploy SQL Server using default manifest file in SQL Server in Azure Kubernetes Service (AKS). All the data and log files of the databases reside in the same volume. In case if you want to place the data files in one volume and the log files in another volume or spread database files across different volumes, we need to create multiple volumes and mount them to the pod running SQL Server container.

After creating the Kubernetes cluster using steps mentioned in SQL Server in Azure Kubernetes Service (AKS) and the nodes are in the ready state, create multiple volumes using below manifest files.

Open cloud shell in your Azure portal and run the following command to create pv1.yaml manifest file.

Paste the following code and press Ctrl + Z.

Persistent volume in AKS

Similarly, create a pv2.yaml manifest file with the following script.

Execute ls command and make sure you have both pv1.yaml and pv2.yaml files

Persistent volume in AKS

Now we need to apply both pv1.yaml and pv2.yaml to create two persistent volumes and volume claims.

Apply pv1.yaml using the below script. Applying pv1.yaml creates a persistent volume claim with name mssql-data of capacity of 8GB.

Persistent volume claim in AKS

Similarly, apply pv2.yaml which creates persistent volume claim with name “mssqllogs” of capacity 8GB in Azure Kubernetes Service.

Now we need to create a deployment and specify both “mssql-data” and “mssqllogs” volume claims in the deployment and apply the deployment manifest.

Before creating a deployment, create a secret key that will be used in the deployment. Please execute the following script by replacing the password of your choice.

Now create a deployment manifest file using the below script.

Paste the following script and press CTRL + Z.

Now apply the sql.yaml to create a deployment which creates a pod and mount both volumes claims to it.

Verify the status of the pods using the below command.

pods in AKS

Once the pod is in the running state, we can execute commands on the pod and check the files inside the volumes.

To run commands against pod in Azure Kubernetes Service, we need to get the pod name and replace it in the below script.

running command against pod in AKS

Execute ls command against pod to retrieve files and directories. The persistent volumes are under /var/opt.

Execute below command to navigate to /var/opt and execute ls command to list files and directories under /var/opt

commands against pods in AKS

Changing the tempdb database file location

By default, the log and data files are placed under “/var/opt/mssql/data”. the error log, agent log, and default trace files are placed under “/var/opt/mssql/log”

Now to move the log file of a tempdb database from “/var/opt/mssql/data” in the default volume “mssqldb” associated with persistent volume claim “mssql-data” to “/var/opt/mssqllog” in the new volume “mssqllog” which is associated with persistent volume claim “mssqllogs”.

Get the IP address of the SQL Server running in Azure Kubernetes Service using the below command and login to the SQL Server using SQL Server management studio.

IP address of SQL Server

Use the IP address and the secret you created earlier to login into the SQL Server using SQL Server management studio.

Execute below T-SQL script to know the current location of the log file of tempdb database.

tempdb database log file location

Now execute the flowing script to change the location of the log file of a tempdb database.

Now restart the SQL Server by running the SHUTDOWN command in SQL Server management studio which will automatically create the log file of tempdb database “templog.ldf” file in the new location.

tempdb database log file location in Azure Kubernetes Service

Changing the master database file location.


Generally moving master database files involves the following steps.

  • Set the new location using mssql-conf
  • Stop the SQL Server
  • Move the files to the new location
  • Start the SQL Server

Changing the master database file of SQL Server running on Kubernetes cluster in Azure Kubernetes Service is not a straightaway procedure.

In Kubernetes, you need to run commands at the pod level to access mssql-conf. to execute commands at the pod level using the below command and replace the pod name with your pod name.

mssql-conf is at location “/opt/mssql/bin”. Navigate to “/opt/mssql/bin” and run the following command to change the location of the master database log file.

mssql-conf in AKS

Execute the following command to change the location of the master database log file.

mssql-conf in Azure Kubernetes Service

Now we need to restart SQL Server. Issue a shutdown command from the SQL Server management studio.

As soon as shutdown command was executed the SQL Server and the pod running SQL Server container in Azure Kubernetes Service is also restarted. Now we need to move the log file of the master database to a new location and start the SQL Server. But, to move the log file to a new location we need to execute the “mv” command on the pod and the pod is not in the “Running” state.

In Kubernetes, as per the deployment, the pod will always check if the SQL server services are running or not. If the SQL Server is not running, the pod will restart, which will automatically restart the SQL Server inside it. But the SQL Server does not find the master database log file in the new location as we have not yet moved it. In this case, the SQL Server will never start, and the pod in Azure Kubernetes Service will go into “Error” status and keeps on restarting and we will be not able to move the log file to a new location as we need pod in “Running” status to execute “mv” command.

pod status in AKS

SQL Server restart error in Azure Kubernetes Service

I tried a workaround to sort out this. The following are the steps.

  • Delete the existing deployment
  • Create a dummy pod
  • Mount the volumes to dummy pod
  • Move the file to a new location
  • Delete the dummy pod
  • Create the deployment again by applying sql.yaml manifest file.

SQL Server deployment in AKS

Deploy below manifest to create a dummy pod and mount the volumes.

To run commands in the pod use the below script.

Use the flowing script to move the log file of the master database to a new location in Azure Kubernetes Service.

alternate way to move log file of master database in Azure Kubernetes Service

Verify if the file is moved or not, exit the pod and delete the dummy pod.

dummy pod in AKS

Once the dummy pod is deleted. apply the sql.yaml again.

Check the status of the pod by executing the below script.

pod status in Azure Kubernetes Service cluster

The pod is in Running status as it started SQL Server services successfully. The SQL Server services are successfully started as the log file of the master database is found in the new location.

Login to SQL server using SQL Server management studio and check the location of the master database log file.

master database log file location in Kubernetes cluster

Conclusion

In this article, we have explored how to create multiple persistent volume claims in Azure Kubernetes Service, mount the persistent volume claim to the pods and move the database files of SQL server running in Kubernetes cluster from the location in the default volume to the location in another volume. In case, if you have any question or other methods/workarounds to move the master database files to a new location, please feel free to post in comment section below.

Ranga Babu

Ranga Babu

SQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies
Ranga Babu
778 Views