SQL Server in Azure Kubernetes Service AKS
SQLShack
SQL Server training Español SQL Server in Azure Kubernetes Service AKS
May 22, 2019 by Ranga Babu In this article, we will review how to create a Kubernetes cluster in Azure Kubernetes Service, provision the persistent volume to store the database files and deploy SQL server on Kubernetes cluster. Kubernetes is a container orchestration tool which automates deploying, scaling and operating containers. SQL Server on Kubernetes cluster with persistent volume is like SQL Server failover cluster instance with the shared disk. In case of failure at the container level, a new pod is created on the same node and the volume is attached to the new pod. In case of a node failure, a new pod is created on the other node in the replica set and the persistent volume is attached to the new pod. Let us see Step by step to deploy SQL Server on a Kubernetes cluster in Azure Kubernetes Service. We need to create an Azure account and login to the Azure portal. Click on the Cloud Shell icon as shown in the image to launch the console. If you are launching it for the first time, it will ask you to create storage for running cloud shell. We can also execute commands from our local windows machine. To execute commands from the local machine you need to install azure CLI and kubectl on your machine. Azure CLI is a command line tool to manage Azure resources. Kubectl is a command line interface for running commands against Kubernetes cluster in Azure Kubernetes Service. Download the MSI installer from https://docs.microsoft.com/en-us/cli/azure/install-azure-cli-windows?view=azure-cli-latest and install it. Once you install Azure CLI, use below command in the command prompt to login into the Azure account. 1 az login Azure CLI opens your default browser and asks you to login using your Azure account. To run the kubectl commands on your local windows machine you have to download the kubectl.exec and set the environment variable. Download the installer from https://kubernetes.io/docs/tasks/tools/install-kubectl/#install-kubectl-on-windows. To set the environment variable, right click on This PC and click on properties. Click on Advanced system settings -> Environment Variables -> Path -> Edit. Click on New and add the path of kubectl.exe Creating a Kubernetes cluster in Azure Kubernetes Service
Now we need to register the resource providers. Execute below commands to register them. 1234 az provider register -n Microsoft.Computeaz provider register -n Microsoft.ContainerServiceaz provider register -n Microsoft.Networkaz provider register -n Microsoft.Storage Once you register the required resource providers. We need to create a resource group. Use the below command to create a resource group in your desired location. A resource group is a collection of multiple resources of an Azure solution. 1 az group create -n democlus01 --location eastus “democlus01” is the name of the resource group and “eastus” is the location. You can replace them as per your choice. Create a Kubernetes cluster using the resource group you created above. Below command create a managed Kubernetes cluster with name “rbctechdemo” in the resource group “democlus01” with 2 nodes of size Standard_B2s which has 2 cores of CPU and 4GB RAM. 1 az aks create --resource-group democlus01 --name rbctechdemo --node-count 2 --generate-ssh-keys --node-vm-size=Standard_B2s Once you run this command it will take some time around 10 minutes to create the managed Kubernetes cluster. It creates all the resources required for the Kubernetes cluster. To view the resources created, Click on All resources in the Azure portal. Get the credentials of the managed Kubernetes cluster by using the below command. This configures your kubectl to connect to your managed Kubernetes cluster. 1 az aks get-credentials --resource-group democlus01 --name rbctechdemo Now verify if your nodes in the Kubernetes cluster are ready. Use below kubectl command to get the status of the nodes in the cluster. 1 kubectl get nodes Configuring Persistent volume and Persistent volume claim
When we deploy SQL server on Kubernetes cluster in Azure Kubernetes Service, the deployment is created in the pod. When the pod is deleted or restarted the data inside the pod is lost. To retain the data on SQL Server we need persistent volume and volume claim. A persistent volume is a piece of storage created by the administrator and used in the Kubernetes cluster. Open notepad and paste the below code and save as .yaml file. In this case, I saved code in VolumeClaim.yaml file. 123456789101112131415161718192021 kind: StorageClassapiVersion: storage.k8s.io/v1beta1metadata: name: azure-diskprovisioner: kubernetes.io/azure-diskparameters: storageaccounttype: Standard_LRS kind: Managed---kind: PersistentVolumeClaimapiVersion: v1metadata: name: mssql-data annotations: volume.beta.kubernetes.io/storage-class: azure-diskspec: accessModes: - ReadWriteOnce resources: requests: storage: 8Gi We need to upload the file to Azure cloud shell storage. To upload VolumeClaim.yaml to the cloud shell storage, Click on the Upload/Download icon in the console and Upload. Now select the VolumeClaim.yaml file from your drive and click on Open. Once the upload is completed it displays the path of the uploaded file. Please note the path which will be used in applying the VolumeClaim.yaml file. Please refer to the below image. Use kubectl command to apply the VolumeClaim.yaml file. 1 kubectl apply -f /home/ranga/VolumeClaim.yaml As we are using azure Kubernetes cluster we need to provision Azure disk. Once you apply the VolumeCliam.yaml it creates an azure disk which will be used as persistent volume and a volume claim with name mssql-data. Below are the commands to check the status of persistent volume and volume claim. 12 kubectl get pvkubectl get pvc The status of the persistent volume and volume claim should be bound. Now we will the volume claim in the SQL Server deployment file. Deploying SQL Server
First, create a SA password in the Kubernetes cluster using below command which will be used in the deployment. 1 kubectl create secret generic mssql --from-literal=SA_PASSWORD="yourownpassword" Create a manifest file for SQL Server deployment, upload it to the cloud shell storage like how we did for VolumeClaim.yaml and apply the manifest file using kubectl. Below is the sample code to deploy SQL Server Linux container of developer edition which uses the persistent volume claim mssql-data created above. The SQL Server will run on the specified port number (1433). 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647 apiVersion: apps/v1beta1kind: Deploymentmetadata: name: mssql-deploymentspec: replicas: 1 template: metadata: labels: app: mssql spec: terminationGracePeriodSeconds: 10 containers: - name: mssql image: mcr.microsoft.com/mssql/server:2017-latest ports: - containerPort: 1433 env: - name: MSSQL_PID value: "Developer" - name: ACCEPT_EULA value: "Y" - name: MSSQL_SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: SA_PASSWORD volumeMounts: - name: mssqldb mountPath: /var/opt/mssql volumes: - name: mssqldb persistentVolumeClaim: claimName: mssql-data---apiVersion: v1kind: Servicemetadata: name: rbcsqlspec: selector: app: mssql ports: - protocol: TCP port: 1433 targetPort: 1433 type: LoadBalancer In this case, I save the above code in sqldeployment.yaml file. Apply the manifest file using below command. 1 kubectl apply -f /home/ranga/sqldeployment.yaml Once the manifest file is successfully applied, the pod is created in which SQL Server is deployed. Use below command to get the pod status. 1 kubectl get pods Above command lists all the pods in the Kubernetes cluster and their status. Please refer to the below image. the pod is created, and the status is ContainerCreating. To know the exact event, use below command with pod name. this command also tells on which node the pod is running. 1 kubectl describe pods mssql-deployment-5bc9cc6d54-l4pzs For the first time, it may take some time for the pod to come into the running state as it must pull the container image from docker hub. Subsequent deployments will be fast as the image already exists in the node. Once the deployment is completed, the status of the pod will be changed to running. Execute the below command in Azure cloud shell to know the services on Kubernetes cluster in Azure Kubernetes Service, internal and external IP address of the services. 1 kubectl get services Use the external IP address returned from the above command and the SA password you created earlier to connect to the SQL server. Now open SQL Server management studio and create a database with few tables. 1234567 CREATE DATABASE [testdb]GO USE testdb CREATE TABLE test (id int )insert into test values (1),(2) Let us delete the pod so that Kubernetes cluster will create a new pod and deploy the container image and attach the persistent volume to the new pod. Here the service is of load balancer type and the IP does not change even after recovery from failure. Use the below statement to delete the pod. 1 kubectl delete pod mssql-deployment-5bc9cc6d54-cttcm As soon as the pod is deleted, a new pod is created as per the deployment specification. Please refer to the below image. After the new pod is created, verify the services by using the below command. 1 kubectl get services Login to the SQL server using management studio and query the tables you created earlier. To delete the managed Kubernetes cluster use below command. This will delete all the resources created by aks create command. 1 az aks delete --name rbctechdemo --resource-group democlus01 Once you execute the above command it will ask for confirmation. On entering “Y” it will start deleting the cluster and its resources. It takes a few minutes to delete the cluster and all the resources. I hope you’ve found this article on SQL Server in Azure Kubernetes Service AKS useful. Feel free to post any feedback in the comments below Author Recent Posts Ranga BabuSQL Server DBA, Developer with good experience in SQL Server administration, development, performance tuning, monitoring, high availability and disaster recovery technologies Latest posts by Ranga Babu (see all) Geo Replication on Transparent Data Encryption (TDE) enabled Azure SQL databases - October 24, 2019 Overview of the Collate SQL command - October 22, 2019 Recover a lost SA password - September 20, 2019 Related posts
Azure Kubernetes Service (AKS) – Managing SQL Server database files SQL Server in Kubernetes Cluster using KOPS Microsoft Clustering in SQL Server Windows Failover Cluster Quorum Modes in SQL Server Always On Availability Groups SQL Database on Kubernetes: Considerations and Best Practices 8,697 Views Follow us
Popular
SQL Convert Date functions and formats SQL Variables: Basics and usage SQL PARTITION BY Clause overview Different ways to SQL delete duplicate rows from a SQL Table How to UPDATE from a SELECT statement in SQL Server SQL Server functions for converting a String to a Date SELECT INTO TEMP TABLE statement in SQL Server SQL WHILE loop with simple examples How to backup and restore MySQL databases using the mysqldump command CASE statement in SQL Overview of SQL RANK functions Understanding the SQL MERGE statement INSERT INTO SELECT statement overview and examples SQL multiple joins for beginners with examples Understanding the SQL Decimal data type DELETE CASCADE and UPDATE CASCADE in SQL Server foreign key SQL Not Equal Operator introduction and examples SQL CROSS JOIN with examples The Table Variable in SQL Server SQL Server table hints – WITH (NOLOCK) best practices Trending
SQL Server Transaction Log Backup, Truncate and Shrink Operations Six different methods to copy tables between databases in SQL Server How to implement error handling in SQL Server Working with the SQL Server command line (sqlcmd) Methods to avoid the SQL divide by zero error Query optimization techniques in SQL Server: tips and tricks How to create and configure a linked server in SQL Server Management Studio SQL replace: How to replace ASCII special characters in SQL Server How to identify slow running queries in SQL Server SQL varchar data type deep dive How to implement array-like functionality in SQL Server All about locking in SQL Server SQL Server stored procedures for beginners Database table partitioning in SQL Server How to drop temp tables in SQL Server How to determine free space and file size for SQL Server databases Using PowerShell to split a string into an array KILL SPID command in SQL Server How to install SQL Server Express edition SQL Union overview, usage and examples Solutions
Read a SQL Server transaction logSQL Server database auditing techniquesHow to recover SQL Server data from accidental UPDATE and DELETE operationsHow to quickly search for SQL database data and objectsSynchronize SQL Server databases in different remote sourcesRecover SQL data from a dropped table without backupsHow to restore specific table(s) from a SQL Server database backupRecover deleted SQL data from transaction logsHow to recover SQL Server data from accidental updates without backupsAutomatically compare and synchronize SQL Server dataOpen LDF file and view LDF file contentQuickly convert SQL code to language-specific client codeHow to recover a single table from a SQL Server database backupRecover data lost due to a TRUNCATE operation without backupsHow to recover SQL Server data from accidental DELETE, TRUNCATE and DROP operationsReverting your SQL Server database back to a specific point in timeHow to create SSIS package documentationMigrate a SQL Server database to a newer version of SQL ServerHow to restore a SQL Server database backup to an older version of SQL Server Categories and tips
►Auditing and compliance (50) Auditing (40) Data classification (1) Data masking (9) Azure (295) Azure Data Studio (46) Backup and restore (108) ►Business Intelligence (482) Analysis Services (SSAS) (47) Biml (10) Data Mining (14) Data Quality Services (4) Data Tools (SSDT) (13) Data Warehouse (16) Excel (20) General (39) Integration Services (SSIS) (125) Master Data Services (6) OLAP cube (15) PowerBI (95) Reporting Services (SSRS) (67) Data science (21) ►Database design (233) Clustering (16) Common Table Expressions (CTE) (11) Concurrency (1) Constraints (8) Data types (11) FILESTREAM (22) General database design (104) Partitioning (13) Relationships and dependencies (12) Temporal tables (12) Views (16) ►Database development (418) Comparison (4) Continuous delivery (CD) (5) Continuous integration (CI) (11) Development (146) Functions (106) Hyper-V (1) Search (10) Source Control (15) SQL unit testing (23) Stored procedures (34) String Concatenation (2) Synonyms (1) Team Explorer (2) Testing (35) Visual Studio (14) DBAtools (35) DevOps (23) DevSecOps (2) Documentation (22) ETL (76) ►Features (213) Adaptive query processing (11) Bulk insert (16) Database mail (10) DBCC (7) Experimentation Assistant (DEA) (3) High Availability (36) Query store (10) Replication (40) Transaction log (59) Transparent Data Encryption (TDE) (21) Importing, exporting (51) Installation, setup and configuration (121) Jobs (42) ►Languages and coding (686) Cursors (9) DDL (9) DML (6) JSON (17) PowerShell (77) Python (37) R (16) SQL commands (196) SQLCMD (7) String functions (21) T-SQL (275) XML (15) Lists (12) Machine learning (37) Maintenance (99) Migration (50) Miscellaneous (1) ►Performance tuning (869) Alerting (8) Always On Availability Groups (82) Buffer Pool Extension (BPE) (9) Columnstore index (9) Deadlocks (16) Execution plans (125) In-Memory OLTP (22) Indexes (79) Latches (5) Locking (10) Monitoring (100) Performance (196) Performance counters (28) Performance Testing (9) Query analysis (121) Reports (20) SSAS monitoring (3) SSIS monitoring (10) SSRS monitoring (4) Wait types (11) ►Professional development (68) Professional development (27) Project management (9) SQL interview questions (32) Recovery (33) Security (84) Server management (24) SQL Azure (271) SQL Server Management Studio (SSMS) (90) SQL Server on Linux (21) ►SQL Server versions (177) SQL Server 2012 (6) SQL Server 2016 (63) SQL Server 2017 (49) SQL Server 2019 (57) SQL Server 2022 (2) ▼Technologies (334) AWS (45) AWS RDS (56) Azure Cosmos DB (28) Containers (12) Docker (9) Graph database (13) Kerberos (2) Kubernetes (1) Linux (44) LocalDB (2) MySQL (49) Oracle (10) PolyBase (10) PostgreSQL (36) SharePoint (4) Ubuntu (13) Uncategorized (4) Utilities (21) Helpers and best practices BI performance counters SQL code smells rules SQL Server wait types © 2022 Quest Software Inc. ALL RIGHTS RESERVED. GDPR Terms of Use Privacy