SQL Server in Kubernetes Cluster using KOPS
SQLShack
SQL Server training Español SQL Server in Kubernetes Cluster using KOPS
May 24, 2019 by Ranga Babu In this article, we will review how to create a Kubernetes cluster in AWS using KOPS, provision Elastic Block Store (EBS) as persistent volume to store the database files and deploy SQL Server in the K8s cluster. Here is step by step to configure the K8s cluster in AWS using KOPS. Creating a K8s cluster in AWS using KOPS
Log in to the AWS console, Click on Services and search for EC2. Click on EC2(Virtual Servers in the Cloud). In the EC2 Dashboard, Click on Launch Instance and select Ubuntu server with t2.micro size. Configure instance details, storage, and security groups and launch the instance using a new key pair or use the existing key pair if already have one. Create an IAM role with below policies and assign the role to Ubuntu instance you created above.This role is used to create Kubernetes cluster resouces. To create an IAM role, click on Services and search for IAM. Click on IAM (Manage User Access and Encrypted Keys) AmazonS3FullAccess AmazonEC2FullAccess IAMFullAccess AmazonRoute53FullAccess In IAM console, Click on Roles (1). Click on Create Role (2). Select EC2 in “Choose the service that will use the role” and click on Next: Permissions. Select the above-mentioned policies. Click on Next and Review. Enter the role name and click on Create Role. Now navigate to the EC2 Dashboard and select the Ubuntu instance you created above, Right-click -> Instance Settings -> Attach/Replace IAM Role. Select the IAM role you created above and click on Apply. To connect to the Ubuntu instance, we must download PuTTy from putty.org and install it. After installing PuTTy, open PuTTygen and click on load, select the .pem file (key pair) which was used to launch the Ubuntu instance and click on Save private key. Now open putty and enter the hostname. To know the hostname of the Ubuntu instance, navigate to the EC2 dashboard, select the instance and copy the public DNS as shown in the below image. Click on Auth (1). Browse the private key you created in the above step (2). Click on Open (3). Login with Ubuntu user. Log in as a superuser using below command. 1 sudo su - Install AWS CLI using below commands. AWS Command Line Interface is a tool to configure, manage AWS services from the command line. 123456789 curl https://s3.amazonaws.com/aws-cli/awscli-bundle.zip -o awscli-bundle.zip apt install unzip unzip awscli-bundle.zip apt install unzip python ./awscli-bundle/install -i /usr/local/aws -b /usr/local/bin/aws Once we install AWS CLI, we need to install the Kubernetes command-line tool (kubectl) on Ubuntu instance which is used to run commands against K8s cluster. Use below commands to download the latest version and install kubectl. 12345 curl -LO https://storage.googleapis.com/kubernetes-release/release/$(curl -s https://storage.googleapis.com/kubernetes-release/release/stable.txt)/bin/linux/amd64/kubectl chmod +x ./kubectl sudo mv ./kubectl /usr/local/bin/kubectl Configure AWS CLI using below command. Leave the access key id and secret key blank as we are using the IAM role that is attached to the Ubuntu EC2 instance. Input the default region of your choice and output format like JSON. 1 aws configure We need to download and install KOPS on EC2 Ubuntu instance. KOPS is used to create a Kubernetes cluster on Amazon Web Services. Use below commands to download and install KOPS. 12345 curl -LO https://github.com/kubernetes/kops/releases/download/$(curl -s https://api.github.com/repos/kubernetes/kops/releases/latest grep tag_name cut -d '"' -f 4)/kops-linux-amd64 chmod +x kops-linux-amd64 sudo mv kops-linux-amd64 /usr/local/bin/kops Now, create a private hosted zone in Route53. To create a hosted zone, click on Services and search for Route 53. Select Route 53 (scalable DNS and Domain Registration) Click on Create Hosted Zone. enter the domain name and select Private Hosted Zone for Amazon VPC as type.
Select the VPC ID and click on Create. Now we need to create an S3 bucket. This S3 bucket will hold the K8s cluster configuration. To create an S3 bucket and set environment variable, execute the below command in the console. 123 aws s3 mb s3://kubeclus01 export KOPS_STATE_STORE=s3://kubeclus01 Create SSH key using below command. 1 ssh-keygen Execute below commands to create Kubernetes cluster configuration which will be stored in the S3 bucket created above. This will only create the cluster configuration and not the cluster. 1 kops create cluster --cloud=aws --zones=ap-south-1b --name=ranga.com --dns-zone=ranga.com --dns private Create the cluster by executing below command. This will create the cluster in the zone “ap-south-1b” with cluster name as “ranga.com”. 1 kops update cluster --name ranga.com --yes Once you execute the above command, it will create all the necessary resources required for the cluster. Now execute validate command to validate the cluster. 1 kops validate cluster It takes some time to create all the cluster resources. Execute the same command after a few minutes. Once validation is a success and you see “your cluster is ready”. Then list the nodes using below command. 1 kubectl get nodes Creating Persistent Volume Claim
Once your Kubernetes cluster setup and ready, we need to create a persistent volume and volume claim to store the database files. As we created the K8s cluster on Amazon Web Services, we will create a persistent volume using AWS EBS. Use below code to create a manifest file directly on the Ubuntu server for creating persistent volume and volume claim. 12345678910111213 kind: PersistentVolumeClaimapiVersion: v1metadata: name: dbvolumeclaim labels: type: amazonEBSspec: accessModes: - ReadWriteOnce storageClassName: gp2 resources: requests: storage: 10Gi If you have any parsing errors due to special characters when you create the .yaml file directly on the Ubuntu server, Open the notepad in your local machine, paste the above code and save it as dbvclaim.yaml file and upload the dbvclaim.yaml file to S3 bucket using S3 console. Now on Ubuntu instance execute below command to download the same file from S3 bucket to the Ubuntu server. 1 aws s3 cp s3://kubeclus01/dbvclaim.yaml /root Now apply the manifest file using kubectl to create persistent volume and volume claim on Kubernetes cluster. 1 kubectl apply -f dbvclaim.yaml After executing the above command, it creates a persistent volume with a random name and volume claim with name “dbvolumecliam” Deploying SQL Server container in K8s cluster in AWS
Before deploying the SQL Server in K8s cluster created in AWS using KOPS, we need to give permissions to create a load balancer for the role which is attached to the master node in the cluster. Navigate to IAM console and click on the role associated with the master node. In my case it is masters.ranga.com. Click on Attach policies. Select ElasticLoadBalancingFullAccess and click on Attach policies. This policy allows the master node to create load balancer and assign public IP to the service. Create SA password in Kubernetes cluster which will be used in the SQL Server deployment. Your password should meet password policy requirements else your deployment fails and the pod show “CrashLoopBackOff” status. 1 kubectl create secret generic mssql --from-literal=SA_PASSWORD="yourpassword" Create a manifest file which will be used for deploying the SQL Server container image. Replace claimName value with the name of your persistent volume claim. You can create the .yaml file directly on the server or upload it s3 from your local machine and download back to Ubuntu server. 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: dbvolumeclaim---apiVersion: v1kind: Servicemetadata: name: mssql-deploymentspec: selector: app: mssql ports: - protocol: TCP port: 1433 targetPort: 1433 type: LoadBalancer Apply the manifest file using kubectl to create a deployment in the K8s cluster. 1 kubectl apply -f sqldep.yaml Once you execute the above command, it will create a deployment with name mssql-deployment in Kubernetes cluster. Now a pod is created with SQL Server running in it. Execute the below command to get the status of the pod. 1 kubectl get pods Once the container is created the status of the pod is changed to running. To know the details of the pod, execute below command by replacing the name of the pod. 1 kubectl describe pod mssql-deployment-5469897589-j79xg In case of any errors during deploying, use below command and replace the pod name with the name of your pod to get the logs. 1 kubectl logs -p mssql-deployment-98f6fc85c-v59xs To know the public IP of the SQL Server, execute below command. This command will list all the available services in the Kubernetes cluster with the service name, internal IP and external IP. 1 kubectl get svc To connect to the SQL Server, Open SQL Server management studio. input the copied external IP and password of SA which you created. Deleting the Cluster using KOPS
Execute below command to delete the K8s cluster using KOPS. Replace ranga.com with the name of your K8s cluster. This will delete all the resources created by KOPS. Before executing this command you need to remove the policy “ElasticLoadBalancingFullAccess” that you attached manually to the role associated with the master node. 1 kops delete cluster ranga.com --yes Once you execute above commad, It takes few minutes to delete the Kubernetes cluster and displays a message “Deleted cluster: cluster name” at the end. 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
SQL Server in Azure Kubernetes Service (AKS) Azure Kubernetes Service (AKS) – Managing SQL Server database files SQL Database on Kubernetes: Considerations and Best Practices Learn AWS CLI – Explore IAM users, roles, policies using AWS CLI Deploying an AWS RDS SQL Server using the AWS CLI 1,760 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