So recently I got involved with an ASP.NET project which was build over 10 years ago and over the years Developers and Change Requests came and went. And over the period the Application became quite cumbersome and quite hard to understand and manage, the Application became quite large in terms of functionality, codebase and data. It was cumbersome and quite hard to understand for a new developer and manage for the Ops team. Lot of technical debts started accumulating because there was no real time spend on optimizing or refactoring the systems. The database was slow and deadlocks were becoming normal. It was hosted on huge on-premise hardware making it a heavy and costly solution. The management realized this and decided it was a time for a refresh. The plan was made to look at utilizing the cloud technologies. And I joined the project as the software architect for the upgrade/migration. In this article the focus is on Data and I am putting down my thoughts on what would be the right Cloud Data architecture for this organization. Let's start with categorizing Data, Use case and the corresponding system. Datastore can be categorized into two types of system. Transaction Analytical And here is the Data Architecture diagram On the left we have the client Apps connecting to Microservices through an API Management platform. The Microservices are hosted in Azure Kubernetes Service. The apps will be hosted within VMs appropriate for running regular applications. For database we are going with SQL Server Container: The SQL Server Container we will host in a different Nodepool with VMs optimized for Database usage. Then for Data Warehousing we will use Snowflake. Snowflake is SaaS based Cloud Warehouse. They are optimized for cloud and do not have an on-premise offering. ETL: To move data from Sql Server to Snowflake we will be using Apache Spark Jobs hosted on Azure Databricks. This article is going to focus on item #3. The SQL Server Container. The others will be visited in future articles. So now we will walk-through on how to Deploy SQL Server DB Instance into an AKS Cluster. We will be using Kubernetes Static Persistent Volume storage for DB as opposed to Kubernetes Dynamic Persistent Volume Storage for the DB. This will give us control when we have to restore an existing Database(s) into the SQL Server Container. Using this approach, we can take regular snapshots of our Azure Disk and restore from the snapshot when disaster strikes. Using Dynamic Provisioning we would not have the control to specify an existing Azure Disk for storage / restoration. We will look at how we are gaining Performance, High Availability and have a plan in for Disaster Recovery. (restoration from a Snapshot or restore DB from a backup file). Databases are stateful. That means it needs storage that can be persisted. So we will first look at storage. AKS Storage Applications hosted on Azure Kubernetes Service (AKS) may need to store and retrieve data. The data storage requirements of many types: Fast local data storage and that need not be persisted after pod is deleted. Data storage that need to be persisted even after pod is deleted or relocated to some other node in the cluster. Storage may need to be shared between multiple pods. Also, their Access Modes required by the applications (like read/write) will be different. For some application workloads, this data storage can use local, fast storage on the node that is no longer needed when the pods are deleted. Some storage may be used to inject configuration or sensitive data into pods. Below we will address four concepts that provides storage to applications in AKS Volumes Persistent volumes Storage classes Persistent volume claims Volumes This is the storage and in Azure it comes in two forms. Azure Disks (there are many flavors of this. Starting with HDD. SDD, Ultra SDD) Azure Files For our SQL Server container we will creating an Azure Disk for the data storage requirements. Persistent Volume A persistent volume is a storage resource that is managed by the Kubernetes Master API that can exist beyond the lifetime of Pod. It can be statically created by Kubernetes cluster or dynamically provisioned. We will be looking at static provisioning. apiVersion: v1 kind: PersistentVolume metadata: name: azure-disk-pv namespace: db spec: capacity: storage: Gi storageClassName: volumeMode: Filesystem accessModes: - ReadWriteOnce azureDisk: kind: Managed diskName: Kube_static_disk diskURI: cxx96af-xxxxx-xxx-a760 f58cxxxxxfe/resourceGroups/MC_maltax_southeastasia/providers/Microsoft.Compute/disks/Kube_static_disk 80 "" /subscriptions/ 15 -1 Storage Classes (SC) A storage class defines the tier (Premium/Standard), Access Modes. Reclaim policy. Persistent Volume Claim (PVC) When an application requires some persistent storage from AKS it has an issue a claim or Persistent Volume Claim. This has to define the Storage Class, Access Mode and Size. If the annotation like below is set in the PVC; then Kubernetes will try to dynamically create the resource. Assuming a matching storageclass called my-storage-class is found. volume.beta.kubernetes.io/storage- : - - class my storage classs But in our case, we are going for Static provisioning. We already created an Azure Disk earlier 80GB size. We also created the Persistent Volume (PV). Now let’s create a Persistent Volume Claim (PVC). apiVersion: v1 kind: PersistentVolumeClaim metadata: name: mssql-data-pvc namespace: db spec: storageClassName: accessModes: - ReadWriteOnce resources: requests: storage: Gi "" 80 As you can we don't have the annotation for Dynamic provisioning. Instead Kubernetes will map this PVC with earlier created PV. The above screenshot confirms the PVC is mapped to PV. Or the claim is mapped to real storage(disk). Now we got storage sorted. Let's deploy the SQL Server Container onto Kubernetes. Below is the file that has the Kubernetes SQL Server Deployment and Service details. Check the section under Volumes. apiVersion: apps/v1beta1 kind: Deployment metadata: name: mssql-deployment namespace: db spec: replicas: template: metadata: labels: app: mssql spec: terminationGracePeriodSeconds: containers: - name: mssql image: mcr.microsoft.com/mssql/server: -latest ports: - containerPort: env: - name: MSSQL_PID value: - name: ACCEPT_EULA value: - name: SA_PASSWORD valueFrom: secretKeyRef: name: mssql key: SA_PASSWORD volumeMounts: - name: mssqldb mountPath: opt/mssql volumes: - name: mssqldb persistentVolumeClaim: claimName: mssql-data-pvc --- apiVersion: v1 kind: Service metadata: name: mssql-deployment namespace: db spec: selector: app: mssql ports: - protocol: TCP port: targetPort: type: LoadBalancer 1 10 2017 1433 "Developer" "Y" /var/ 1433 1433 We will deploy this. See below screenshot. We can see after around 40 seconds the SQL Server Instance is up and running. We are able to connect using sqlcmd command. We can also see that the pod is running on the VM and below screenshot shows that the VM has mounted the storage disk that we provisioned earlier. Now SQL Server is set up and running; let's look at some key parameters. Key Parameters Performance High Availability Disaster Recovery Performance In AKS a feature called Azure Accelerated Networking is turned by default. Since the Applications and our SQL Server Container Instance are deployed in the same AKS instance we are automatically able to gain this benefits of this feature. Significantly improved network performance. Network throughput of up to 30Gbps. Reduced latency / higher packets per second (pps). Reduced jitters. Decreased CPU Utilization: Less CPU Utilization for processing network traffic. Another way to gain performance is to use Ultra SSD which scale performance up to 160,000* IOPS and 2 GB/s per disk with zero downtime. High Availability Container Level: Kubernetes regularly check whether the SQL Server Containers Instance are running healthy. If for some reason the instance crashes or stop being responsive. Kubernetes restarts it. When I tried to delete a Sql Server Pod; Kubernetes instantly detected and spun a new Pod within 4 seconds. See below screenshot. Disaster Recovery Our storage for the container is Azure Disks which provides an SLA of 99.999% and had no outage till now. But still we should be prepared for a Disaster. One of the options we have to take regular incremental snapshots of Azure Disks. If a disaster strikes, we can restore the most snapshot back to a Disk. This Disk can be then mounted onto a new SQL Server Instance. But since we have the data (maybe old by a few mins or hours based on our Recovery Point Objective (RPO) of Disaster Recovery strategy). Another way to get back up and running when a disaster strikes is to use SqlPackage command. We can regular backups (automated). Disaster strikes we can spin up a new SQL Server Instance and Azure Disk Storage. Then we will restore the bacpac file back on to the Azure Disk for use by SQL Server.
