SQL Server in Azure Kubernetes Service (AKS)
SQL Server on Kubernetes with persistent volumes is similar to a SQL Server failover cluster instance with shared disk in the sense that it provides high availability and resiliency against failure at the container or node level.
In a Kubernetes cluster with persistent volumes, the database is stored on a persistent volume that is decoupled from the pod. This means that if a pod fails or is terminated, the persistent volume remains intact, and a new pod can be created on the same or a different node and the persistent volume can be attached to the new pod. This ensures that the data is not lost and the database can continue to operate without interruption.
In case of node failure, Kubernetes automatically detects the failure and promotes a new replica set to replace the failed node. The persistent volume is then attached to a new pod on the new node, and the database can continue to operate without interruption.
Overall, SQL Server on Kubernetes with persistent volumes provides a highly available and scalable solution for running SQL Server in a containerized environment.
~~~~~~~~~~1st Command (AZ Login)~~~~~~~~~~~~
AZ Login
~~~~~~~~~~ 2nd Command(Create a resource group)~~~~~~~~~~~~~~~
az group create --name RGP-USE-RAK-AKS --location eastus
~~~3rd Command (Create a VNET and Subnet for AKS Cluster Creation)~~~~~~~
az network vnet create --resource-group RGP-USE-RAK-AKS --name Vnt-USE-RAK-AKS --address-prefix 10.20.0.0/16 --subnet-name sub-use-rak-aks --subnet-prefix 10.20.0.0/24
~~~~~~~~~~~4th Command to create a basic AKS Cluster~~~~~~~~~~~~~
az aks create --resource-group RGP-USE-RAK-AKS --name AKS-USE-RAK-DEV --node-count 2 --generate-ssh-keys --network-plugin kubenet --network-policy calico --vnet-subnet-id /subscriptions/69b34dfc-4b97-4259-93f3-037ed7eec25e/resourceGroups/RGP-USE-RAK-AKS/providers/Microsoft.Network/virtualNetworks/Vnt-USE-RAK-AKS/subnets/sub-use-rak-aks
~~~~~~~~~~~5th Command~~~~~~~~~~~~~~~
az role assignment create --assignee-object-id 4deb7b66-2ab4-4d17-b3d5-4d63d195d8db --scope /subscriptions/69b34dfc-4b97-4259-93f3-037ed7eec25e/resourceGroups/RGP-USE-RAK-AKS/providers/Microsoft.Network/virtualNetworks/Vnt-USE-RAK-AKS/subnets/sub-use-rak-aks --role "Network Contributor" --assignee-principal-type ServicePrincipal
Explanation:-
- This command creates a role assignment in the specified subscription and resource group, granting the role of "Network Contributor" to the specified Service Principal (identified by its Azure AD object ID) at the specified scope, which is a subnet in a virtual network.
- A role assignment is a mapping between a security principal and a role definition. In this case, the security principal is a Service Principal identified by its Azure AD object ID, and the role definition is "Network Contributor". The "Network Contributor" role allows the principal to manage network resources, such as virtual networks and subnets, but not other types of resources like virtual machines or storage accounts.
- The --scope option specifies the scope of the role assignment. In this case, the role is assigned at the subnet level of the virtual network identified by /subscriptions/69b34dfc-4b97-4259-93f3-037ed7eec25e/resourceGroups/RGP-USE-RAK-AKS/providers/Microsoft.Network/virtualNetworks/Vnt-USE-RAK-AKS/subnets/rakAksSubnet.
- The --assignee-principal-type option specifies the type of the security principal being assigned the role, which is a Service Principal in this case. The --assignee-object-id option specifies the object ID of the Service Principal that the role is being assigned to.
- Note that to successfully create a role assignment, the account used to run the az role assignment create command must have the appropriate permissions to assign the specified role at the specified scope. In this case, the account must have the "Owner" role at the subscription level or have been granted the appropriate RBAC permissions on the resource group and virtual network to be able to assign the "Network Contributor" role to the specified Service Principal.
~~~~~~~~~~~~~~~~6th Command to enables Azure Active Directory (AAD)~~~~~~~~~~~~~
az aks update -g RGP-USE-RAK-AKS -n AKS-USE-RAK-DEV --enable-aad --aad-tenant-id 1c5558a6-XXX-4a35-8463-7592105355ff --aad-admin-group-object-ids 0725f885-f90f-4889-9d29-e86e80XXX782
Explanation:-
- This command enables Azure Active Directory (AAD) integration for the AKS cluster named "AKS-USE-RAK-DEV" in the resource group "RGP-USE-RAK-AKS".
- The --enable-aad option enables AAD integration for the cluster, and
- the --aad-tenant-id option specifies the ID of the AAD tenant that will be associated with the cluster.
- The --aad-admin-group-object-ids option specifies the object ID of the AAD group that will be granted administrative access to the cluster. This group will be added as a cluster admin with the clusterAdmin Azure RBAC role.
~~~~~7th Command ~~enables Azure Role-Based Access Control (Azure RBAC)~~~~
az aks update -g RGP-USE-RAK-AKS -n AKS-USE-RAK-DEV --enable-azure-rbac
Explanation: -
- This command enables Azure Role-Based Access Control (Azure RBAC) for the AKS cluster named "RGP-USE-RAK-AKS" in the resource group "RGP-USE-RAK-AKS".
- The --enable-azure-rbac option enables Azure RBAC for the cluster, which allows you to assign Azure AD users and groups to RBAC roles in the cluster. With Azure RBAC, you can control access to cluster resources and actions based on the RBAC roles assigned to users and groups.
- Note that before enabling Azure RBAC, you need to enable AAD integration for the cluster, as Azure RBAC relies on AAD for user and group authentication and authorization.
~~~~~~~~~7th Command Get your AKS Resource ID~~~~~~~~~~~~~~~~~
az aks show -g RGP-USE-RAK-AKS -n rakAksSubnet --query id -o tsv)
~~~~8th Command ~~~ Azure Kubernetes Service RBAC Admin to group 0725f885-f90f-4889-9d29-e86e808ce782 ~~~~~
az role assignment create --role "Azure Kubernetes Service RBAC Admin" --assignee 0725f885-f90f-4889-9d29-e86e808ce782 --scope "/subscriptions/69b34dfc-4b97-4259-93f3-037ed7eec25e/resourcegroups/RGP-USE-RAK-AKS/providers/Microsoft.ContainerService/managedClusters/rakAksSubnet"
Explanation:-
This command creates a role assignment in Azure that grants the "Azure Kubernetes Service RBAC Admin" role to the principal with the object ID "0725f885-f90f-4889-9d29-e86e808ce782" on the specified Azure Kubernetes Service (AKS) cluster. The scope of the role assignment is set to the resource ID of the AKS cluster, which is "/subscriptions/69b34dfc-4b97-4259-93f3-037ed7eec25e/resourcegroups/RGP-USE-RAK-AKS/providers/Microsoft.ContainerService/managedClusters/rakAksSubnet".
To break this down:
az role assignment create: This command creates a new role assignment in Azure.
--role "Azure Kubernetes Service RBAC Admin": This option specifies the role to assign to the principal. In this case, the role is "Azure Kubernetes Service RBAC Admin", which is a built-in role in Azure that allows the principal to manage Kubernetes RBAC (Role-Based Access Control) on an AKS cluster.
--assignee 0725f885-f90f-4889-9d29-e86e808ce782: This option specifies the object ID of the principal to whom the role will be assigned. In this case, the object ID is "0725f885-f90f-4889-9d29-e86e808ce782".
--scope "/subscriptions/69b34dfc-4b97-4259-93f3-037ed7eec25e/resourcegroups/RGP-USE-RAK-AKS/providers/Microsoft.ContainerService/managedClusters/rakAksSubnet": This option specifies the scope of the role assignment, which is the resource ID of the AKS cluster to which the role will be assigned. The resource ID includes the subscription ID, the resource group name, the resource provider (Microsoft.ContainerService), and the name of the AKS cluster (rakAksSubnet).
Overall, this command is granting the "Azure Kubernetes Service RBAC Admin" role to the specified principal on the specified AKS cluster, giving them the ability to manage RBAC for that cluster.
az role assignment create --role "Azure Kubernetes Service RBAC Cluster Admin" --assignee 0725f885-f90f-4889-9d29-e86e808ce782 --scope "/subscriptions/69b34dfc-4b97-4259-93f3-037ed7eec25e/resourcegroups/RGP-USE-RAK-AKS/providers/Microsoft.ContainerService/managedClusters/rakAksSubnet"
Azure Kubernetes Service RBAC Cluster Admin
- When you run az aks get-credentials, it uses the Azure CLI to retrieve the cluster credentials and then saves them to your local machine. This method may use a different authentication method that does not require kubelogin.
- However, when you try to use kubelogin, it expects to be able to authenticate to the cluster using the Kubernetes API server. If kubelogin is not installed or not properly configured, you may see the error message you mentioned.
To use kubelogin, you will need to ensure that it is properly installed and configured. You can check whether kubelogin is installed by running the command which kubelogin in your terminal. If kubelogin is not installed, you can download and install it by following the instructions in the documentation: https://github.com/Azure/kubelogin
Once kubelogin is properly installed, you may need to configure it to use the correct authentication method for your cluster. You can find more information on how to configure kubelogin in the documentation as well.
Deploying and Securing a Private Endpoint for Azure Storage with Azure Kubernetes Service (AKS)
az network vnet subnet create --name pip-subnet --resource-group RGP-USE-RAK-AKS --vnet-name Vnt-USE-RAK-AKS --address-prefix 10.20.2.0/24
az storage account create --name stouserakdv --resource-group RGP-USE-RAK-AKS --location eastus --sku Standard_LRS --kind StorageV2 --hns true --access-tier Hot --default-action Allow --allow-blob-public-access false
az network private-endpoint create -g RGP-USE-RAK-AKS -n PEP-stouserakdv --vnet-name Vnt-USE-RAK-AKS --subnet pip-subnet --private-connection-resource-id "/subscriptions/69b34dfc-4b97-4259-93f3-037ed7eec25e/resourceGroups/RGP-USE-RAK-AKS/providers/Microsoft.Storage/storageAccounts/stouserakdv" --connection-name tttt -l eastus --group-ids file
az network private-endpoint show --name PEP-stouserakdv --resource-group RGP-USE-RAK-AKS
az network private-dns zone create --name file.core.windows.net --resource-group RGP-USE-RAK-AKS
az storage account update --name stouserakdv --resource-group RGP-USE-RAK-AKS --default-action Deny
az network vnet subnet update --resource-group RGP-USE-RAK-AKS --vnet-name Vnt-USE-RAK-AKS --name pip-subnet --service-endpoints Microsoft.Storage
az storage account network-rule add -g RGP-USE-RAK-AKS --account-name stouserakdv --vnet-name Vnt-USE-RAK-AKS --subnet pip-subnet
az network vnet subnet update --resource-group RGP-USE-RAK-AKS --vnet-name Vnt-USE-RAK-AKS --name sub-use-rak-aks --service-endpoints Microsoft.Storage
az storage account network-rule add -g RGP-USE-RAK-AKS --account-name stouserakdv --vnet-name Vnt-USE-RAK-AKS --subnet sub-use-rak-aks
az network private-dns record-set a add-record --resource-group RGP-USE-RAK-AKS --zone-name file.core.windows.net --ipv4-address "10.20.2.4" --record-set-name stouserakdv
az network private-dns link vnet create -g RGP-USE-RAK-AKS -n MyDNSLink -z file.core.windows.net -v Vnt-USE-RAK-AKS -e false
kubectl debug node/aks-nodepool1-83490439-vmss000000 -it --image=mcr.microsoft.com/dotnet/runtime-deps:6.0
root@aks-nodepool1-83490439-vmss000000:/# chroot /host
#nslookup stouserakdv.file.core.windows.net
Server: 168.63.129.16
Address: 168.63.129.16#53
Non-authoritative answer:
Name: stouserakdv.file.core.windows.net
Address: 10.20.2.4
it is returning private IP address of storage account.
C:\Users\kusha>
az storage share create --account-name stouserakdv --account-key VkXiopaXXXX7k9sEGaHurt4h0hQmwy5ykP6YQ1wjaUxE/ndZSkrQC6ZV7zzUs/znnHTrbHOzZZ7l+ASt9JLYBw== --name sqlfileshare
kubectl create secret generic azure-secret --from-literal=azurestorageaccountname=stouserakdv --from-literal=azurestorageaccountkey=VkXiopaDg3x7k9sEGaHurt4h0hXXXwy5ykP6YQ1wjaUxE/ndZSkrQC6ZV7zzUs/znnHTrbHOzZZ7l+ASt9JLYBw== -n rak
Kubernetes YAML file for Provisioning an Azure File Storage Persistent Volume with ReadWriteMany Access Mode
apiVersion: v1
kind: PersistentVolume
metadata:
name: pv-azure-file
spec:
capacity:
storage: 20Gi
accessModes:
- ReadWriteMany
azureFile:
secretName: azure-secret
shareName: sqlfileshare
readOnly: false
~~~~~~~~~~~~~~~~~~~~~~~~~~
apiVersion: v1
kind: PersistentVolumeClaim
metadata:
name: pvc-azure-file
namespace: rak
spec:
accessModes:
- ReadWriteMany
storageClassName: ""
volumeName: pv-azure-file
resources:
requests:
storage: 5Gi
---
- This is Kubernetes YAML file that specifies the configuration for a Persistent Volume Claim (PVC) object.
- apiVersion: v1 indicates that this file uses the Kubernetes core API version 1.
- kind: PersistentVolumeClaim specifies the type of Kubernetes object being created.
- metadata defines the name and namespace for the PVC. In this case, the PVC is named pvc-azure-file and is created in the rak namespace.
- accessModes: specifies the access mode for the PVC, which is ReadWriteMany in this case. This means that the volume can be mounted as read-write by multiple pods.
- storageClassName: specifies the storage class to use for dynamic provisioning. Since this is an existing PV, this is set to an empty string.
- volumeName: specifies the name of the PV that this PVC will be bound to, which is pv-azure-file in this case.
- resources: specifies the storage request for the PVC. In this case, the PVC is requesting 5Gi of storage. When the PVC is created, it will be bound to the existing PV with a capacity of 20Gi.
kubectl create secret generic mssql --from-literal=MSSQL_SA_PASSWORD="MyC0m9l&xP@ssw0rd" -n rak
apiVersion: apps/v1
kind: Deployment
metadata:
name: mssql-deployment
namespace: rak
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-latest
resources:
requests:
memory: "1G"
cpu: "1000m"
limits:
memory: "1G"
cpu: "1500m"
ports:
- containerPort: 1433
env:
- name: MSSQL_PID
value: "Developer"
- name: ACCEPT_EULA
value: "Y"
- name: MSSQL_SA_PASSWORD
valueFrom:
secretKeyRef:
name: mssql
key: MSSQL_SA_PASSWORD
volumeMounts:
- name: mssqldb
mountPath: /var/opt/mssql
volumes:
- name: mssqldb
persistentVolumeClaim:
claimName: pvc-azure-file
---
apiVersion: v1
kind: Service
metadata:
name: mssql-deployment
namespace: rak
spec:
selector:
app: mssql
ports:
- protocol: TCP
port: 1433
targetPort: 1433
type: LoadBalancer
