{"id":24442,"date":"2018-04-05T10:06:21","date_gmt":"2018-04-05T17:06:21","guid":{"rendered":"http:\/\/joeydantoni.com\/?p=1952"},"modified":"2018-04-11T13:48:47","modified_gmt":"2018-04-11T20:48:47","slug":"getting-started-with-sql-sql-server-and-kubernetes-part-ii","status":"publish","type":"post","link":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/","title":{"rendered":"Getting Started with SQL SQL Server and Kubernetes, Part II"},"content":{"rendered":"

You might have noticed that I titled this post Part II, but with no \u201cof N\u201d. This will likely be a periodically recurring series of posts as I take things a bit further with Kubernetes. Also, likely I will be correcting things I screwed up along the way. The first of which is that I said if you had a Mac this is easy to configure using the Docker Edge configuration with Kubernetes. Well it was easy to configure K8s with Docker–however when I got deep into playing with SQL Server, I kept running into weird issues with storage persistence and instance startup. I was chatting with a colleague on the product team (thanks Mihaela!!) and she told me about this bug:<\/p>\n

https:\/\/github.com\/Microsoft\/mssql-docker\/issues\/12<\/a><\/p>\n

It\u2019s complicated–but basically the Docker filesystem mapper isn\u2019t consistent with SQL Server\u2019s I\/O code. So, in lieu of that, I installed Minikube on an Ubuntu VM on my Mac. You can find the instructions here<\/a>, you will have to install VirtualBox to host the VM for your Kubernetes install. It was really straightforward–if you want to build a \u201creal\u201d Kubernetes cluster, the process is a little bit more complicated, and outside of the scope of today\u2019s post.<\/p>\n

What are We Building?<\/h2>\n

In January, the SQL Server Product Group posted this blog post<\/a> on deploying SQL Server in a high-availability configuration on Kubernetes. It went without much fanfare, but I dug into the post, and built the solution, and realized it offers nearly the same functionality as a Failover Cluster Instance, with minimal configuration effort. While building an FCI on Windows is quite approachable, building an FCI on Linux is somewhere between painful and awful, depending on the distribution you are using, the day of the week, and the position of the stars. The post assumes you are building on top of the Azure Kubernetes Service–which is a great way to get started, but it costs money, and I wanted to do something in a VM. So we\u2019re building the same basic architecture that is in that PG post, however we are going to build it on minikube.<\/p>\n

Components<\/h2>\n

There are a couple of things we are building here:<\/p>\n

Persisted Disk: <\/strong>Inherently containers have no persisted storage. Obviously, this is a problem for database containers. We are going to define a persistent volume claim<\/em> to map our storage account. This is probably the biggest difference between my code and the PG blog post, as I\u2019m referring to local storage as opposed to Azure disks.<\/p>\n

Deployment: <\/strong>This refers to our container and volume. You will see this defined in the code below.<\/p>\n

Service: <\/strong>We are defining a service and creating a load balancer for our deployment. The load balancer is the key to this deployment, as it will maintain a persistent IP for our deployment when our container goes away.<\/p>\n

By defining this deployment in this way, if we have a failure on a host that is hosting our container, Kubernetes auto-healing process will automatically deploy a new pod <\/em>(in the context here, a pod just holds our single container, a web server might have multiple containers in the pod.<\/p>\n

Let\u2019s Build Something<\/h2>\n

This assumes that you have minikube up and running. The first thing you\u2019re going to do is build a secret to pass into your deployment, for your SA password.<\/p>\n

 <\/p>\n

kubectl create secret generic mssql --from-literal=SA_PASSWORD=\"MyC0m9l&xP@ssw0rd\"<\/pre>\n
 <\/p>\n
The next thing you are going to do is build you persistent volume claim.<\/p>\n
 <\/p>\n
kind: PersistentVolumeClaim\n\napiVersion: v1\n\nmetadata:\n\n\u00a0 name: mssql-data-claim\n\nspec:\n\n\u00a0 accessModes:\n\n\u00a0 - ReadWriteOnce\n\n\u00a0 resources:\n\n\u00a0\u00a0 requests:\n\n\u00a0\u00a0\u00a0 storage: 10Gi<\/pre>\n
You will save this text in a file. For the purposes of this posts, we will call it pv-claim.yaml<\/em>. You will then run the kubectl apply -f pv-claim.yaml<\/em><\/strong> command. You will see the message \u201cpersistentvolumeclaim \u201cmssql-data-claim\u201d created<\/em><\/p>\n
Next we are going to build our deployment and our load balancer.<\/p>\n
apiVersion: v1\n\nkind: Service\n\nmetadata:\n\n\u00a0 name: mssql-deployment\n\nspec:\n\n\u00a0 selector:\n\n\u00a0\u00a0\u00a0 app: mssql\n\n\u00a0 ports:\n\n\u00a0\u00a0\u00a0 - protocol: TCP\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 port: 1433\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 targetPort: 1433\n\n\u00a0 type: LoadBalancer\n\n---\n\napiVersion: apps\/v1beta1\n\nkind: Deployment\n\nmetadata:\n\n\u00a0 name: mssql-deployment\n\nspec:\n\n\u00a0 replicas: 1\n\n\u00a0 template:\n\n\u00a0\u00a0\u00a0 metadata:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 labels:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 app: mssql\n\n\u00a0\u00a0\u00a0 spec:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 terminationGracePeriodSeconds: 10\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 containers:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 - name: mssql\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 image: microsoft\/mssql-server-linux\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ports:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 - containerPort: 1433\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 env:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 - name: ACCEPT_EULA\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 value: \"Y\"\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 - name: SA_PASSWORD\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 valueFrom:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 secretKeyRef:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0name: mssql\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 key: SA_PASSWORD\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 volumeMounts:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 - name: mssql-persistent-storage\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 mountPath: \/var\/opt\/mssql\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 volumes:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0 - name: mssql-persistent-storage\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 persistentVolumeClaim:\n\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 claimName: mssql-data-claim<\/pre>\n
There\u2019s a lot of stuff here. Let\u2019s walk through the key elements of this file:<\/p>\n
We\u2019re defining our service and load balancer at the beginning of the code. Next, we are defining our deployment, which specifies the container we\u2019re going to use, which in this case it is the latest release of SQL Server 2017, and it picks up our predefined SA password. Finally, we are defining our volume mount and its path for where it will be mounted in the VM. Save this off to a file called sqldeployment.yaml. <\/em>You will run the same kubectl apply -f sqldeployment.yaml<\/em><\/strong> to deploy this. You will see service \u201cmssql-deployment\u201d created <\/em>and deployment \u201cmssql-deployment\u201d created<\/em>. You can verify the deployments by running the below commands:<\/p>\n
\"Screen<\/p>\n
You\u2019ll make note of that IP address and port for your SQL deployment. You do need to make note of that port, however, that IP address is not routable within that cluster. There is some weirdness here to running minikube. I\u2019d like to thank the Database Avenger for this post which showed me how to connect to the kube. \u00a0Run the following command, which will give the IP address to connect to:<\/p>\n
minikube service mssql-deployment --url<\/pre>\n
Your output will give you the IP address and port you can connect to.<\/p>\n
Can We Login Yet?<\/h2>\n
So, I have SQLCMD installed on my Linux VM (instructions for SQL tools for Linux<\/a>). If you have made it this far, this is just a standard SQL connection (albeit to a non-standard port, which is denoted by a comma after the IP address)<\/p>\n
\"Screen<\/p>\n
You can see my server name is mssql-deployment. <\/strong>I\u2019m already out of space, so come back next week to talk about persisting data, and how failover works.<\/p>\n","protected":false},"excerpt":{"rendered":"
You might have noticed that I titled this post Part II, but with no “of N”. This will likely be a periodically recurring series of posts as I take things a bit further with Kubernetes. Also, likely I will be correcting things I screwed up along the way. The first of which is that I […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[551,63,46],"tags":[],"class_list":["post-24442","post","type-post","status-publish","format-standard","hentry","category-blog","category-sql","category-syndication"],"yoast_head":"\nGetting Started with SQL SQL Server and Kubernetes, Part II – Denny Cherry & Associates Consulting<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Getting Started with SQL SQL Server and Kubernetes, Part II – Denny Cherry & Associates Consulting\" \/>\n<meta property=\"og:description\" content=\"You might have noticed that I titled this post Part II, but with no “of N”. This will likely be a periodically recurring series of posts as I take things a bit further with Kubernetes. Also, likely I will be correcting things I screwed up along the way. The first of which is that I […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/\" \/>\n<meta property=\"og:site_name\" content=\"Denny Cherry & Associates Consulting\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-04-05T17:06:21+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-04-11T20:48:47+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/joedantoni.files.wordpress.com\/2018\/04\/screen-shot-2018-04-05-at-12-49-49-pm.png?w=630\" \/>\n<meta name=\"author\" content=\"Joey D'Antoni\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dcacco\" \/>\n<meta name=\"twitter:site\" content=\"@dcacco\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joey D'Antoni\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/\",\"url\":\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/\",\"name\":\"Getting Started with SQL SQL Server and Kubernetes, Part II – Denny Cherry & Associates Consulting\",\"isPartOf\":{\"@id\":\"https:\/\/www.dcac.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/joedantoni.files.wordpress.com\/2018\/04\/screen-shot-2018-04-05-at-12-49-49-pm.png?w=630\",\"datePublished\":\"2018-04-05T17:06:21+00:00\",\"dateModified\":\"2018-04-11T20:48:47+00:00\",\"author\":{\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#primaryimage\",\"url\":\"https:\/\/joedantoni.files.wordpress.com\/2018\/04\/screen-shot-2018-04-05-at-12-49-49-pm.png?w=630\",\"contentUrl\":\"https:\/\/joedantoni.files.wordpress.com\/2018\/04\/screen-shot-2018-04-05-at-12-49-49-pm.png?w=630\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dcac.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Getting Started with SQL SQL Server and Kubernetes, Part II\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dcac.com\/#website\",\"url\":\"https:\/\/www.dcac.com\/\",\"name\":\"Denny Cherry & Associates Consulting\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dcac.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885\",\"name\":\"Joey D'Antoni\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x\",\"caption\":\"Joey D'Antoni\"},\"url\":\"https:\/\/www.dcac.com\/author\/joeydcac-co\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Getting Started with SQL SQL Server and Kubernetes, Part II – Denny Cherry & Associates Consulting","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/","og_locale":"en_US","og_type":"article","og_title":"Getting Started with SQL SQL Server and Kubernetes, Part II – Denny Cherry & Associates Consulting","og_description":"You might have noticed that I titled this post Part II, but with no “of N”. This will likely be a periodically recurring series of posts as I take things a bit further with Kubernetes. Also, likely I will be correcting things I screwed up along the way. The first of which is that I […]","og_url":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/","og_site_name":"Denny Cherry & Associates Consulting","article_publisher":"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/","article_published_time":"2018-04-05T17:06:21+00:00","article_modified_time":"2018-04-11T20:48:47+00:00","og_image":[{"url":"https:\/\/joedantoni.files.wordpress.com\/2018\/04\/screen-shot-2018-04-05-at-12-49-49-pm.png?w=630","type":"","width":"","height":""}],"author":"Joey D'Antoni","twitter_card":"summary_large_image","twitter_creator":"@dcacco","twitter_site":"@dcacco","twitter_misc":{"Written by":"Joey D'Antoni","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/","url":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/","name":"Getting Started with SQL SQL Server and Kubernetes, Part II – Denny Cherry & Associates Consulting","isPartOf":{"@id":"https:\/\/www.dcac.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#primaryimage"},"image":{"@id":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#primaryimage"},"thumbnailUrl":"https:\/\/joedantoni.files.wordpress.com\/2018\/04\/screen-shot-2018-04-05-at-12-49-49-pm.png?w=630","datePublished":"2018-04-05T17:06:21+00:00","dateModified":"2018-04-11T20:48:47+00:00","author":{"@id":"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885"},"breadcrumb":{"@id":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#primaryimage","url":"https:\/\/joedantoni.files.wordpress.com\/2018\/04\/screen-shot-2018-04-05-at-12-49-49-pm.png?w=630","contentUrl":"https:\/\/joedantoni.files.wordpress.com\/2018\/04\/screen-shot-2018-04-05-at-12-49-49-pm.png?w=630"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dcac.com\/2018\/04\/05\/getting-started-with-sql-sql-server-and-kubernetes-part-ii\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dcac.com\/"},{"@type":"ListItem","position":2,"name":"Getting Started with SQL SQL Server and Kubernetes, Part II"}]},{"@type":"WebSite","@id":"https:\/\/www.dcac.com\/#website","url":"https:\/\/www.dcac.com\/","name":"Denny Cherry & Associates Consulting","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dcac.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/ac58f2207d3f180aa3e4ee0ce6af0885","name":"Joey D'Antoni","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3fede03569772a55a091b587d76f78d4?s=96&r=x","caption":"Joey D'Antoni"},"url":"https:\/\/www.dcac.com\/author\/joeydcac-co\/"}]}},"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/24442","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/comments?post=24442"}],"version-history":[{"count":2,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/24442\/revisions"}],"predecessor-version":[{"id":82857,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/24442\/revisions\/82857"}],"wp:attachment":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/media?parent=24442"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/categories?post=24442"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/tags?post=24442"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}