SQLGeordie's Blog

Helping the SQL Server community……where i can!

Docker and SQL Server on Linux series — June 20, 2018

Docker and SQL Server on Linux series

DockerBuildShipRunAnywhere

I’ve been playing around with Docker (more specifically with SQL Server on Linux) the beginning of 2017 to see if indeed I can “Build, Ship, Run, Any App Anywhere” and will be putting together a series of how-to’s and issues experienced so you guys don’t spend the countless hours trying to investigate and work out what is going on when things do not work.

I’ll be adding links to this main page as and when new posts are added as a central point of reference.

Free DataSets – Kaggle — July 5, 2020

Free DataSets – Kaggle

Pretty much anything and everything that you would need for a SQL Server, Azure, AWS, GCP, Power BI etc demo dataset can be found on kaggle. The search facility is easy to use and you can also filter on the particular file type and license you want. I also like the “quicklook” option so you can quickly check to see if indeed it is the sort of data you want as opposed to having to download the data, open it and then realise it’s nothing like what you are after.

I never knew a lot about kaggle and to be honest, other than being able to get datasets from it, I never had a need to. Taken from wikipedia, “Kaggle, a subsidiary of Google LLC, is an online community of data scientists and machine learning practitioners. Kaggle allows users to find and publish data sets, explore and build models in a web-based data-science environment, work with other data scientists and machine learning engineers, and enter competitions to solve data science challenges.

It’s actually no surprise that they’re a subsidiary of Google when you look at most if not all of Google’s datasets, they all seem to link to kaggle….

I’ve included a couple of useful datasets that I’ve used in the past, hopefully you’ll find them useful also:

Back to dataset main page

Free DataSets – Google —

Free DataSets – Google

For most people on the planet with internet access, the first place to visit when looking for anything on the web is Google……….other search engines are available. Not only can you search using the standard search engine but they have various spin offs specific for searching datasets.

Google tracks dateset searches, allows you to explore these and provide the ability to download the data either direct from one of their charts or by providing back links to the site in which it came from. Chances are, kaggle will be one of them which is quite obvious when they are a subsidiary of Google LLC 🙂

There are various places within google to search the datasets, depending on what it is you’re looking for, below are some examples:

Back to dataset main page

Free DataSets…..for SQL Server, Azure, AWS, Power BI etc etc Demo Purposes —

Free DataSets…..for SQL Server, Azure, AWS, Power BI etc etc Demo Purposes

This is just a short overview page of various dataset sources I’ve used in the past for usage in my SQL Server, Azure, AWS and Power BI demo’s. Most if not all of these are free as I don’t like paying for stuff and there are some whereby you can generate your own data if there is something more specific you need……..just be careful as although its “randomly” generated, I have had issues where I pushed the files to github and as it happens, one of the email addresses randomly generated was that of an employee of a company which I’d never heard of. Pure chance (I believe) but something to be mindful of if that is your intention for use.


Kaggle

Pretty much anything and everything that you would need for a SQL Server, Azure, AWS, GCP, Power BI etc demo dataset can be found on kaggle. The search facility is easy to use and you can also filter on the particular file type and license you want. I also like the “quicklook” option so you can quickly check to see if indeed it is the sort of data you want as opposed to having to download the data, open it and then realise it’s nothing like what you are after.


Google

For most people on the planet with internet access, the first place to visit when looking for anything on the web is Google……….other search engines are available. Not only can you search using the standard search engine but they have various spin offs specific for searching datasets.


Power BI Sample DataSets

Not only datasets but also Power BI sample pbix files for anyone looking to get started with it.


GenerateData – Create your own

If there is a specific dataset you are looking for, with specific column names and data types then this is a useful tool. Data is restricted to 100 rows initially unless you sign up / donate but worth a look:


data.gov.uk

At the time of writing there are 54,846 datasets available on here. Some are a bit bizarre (See below) and are specific to the UK so unless you’re looking for that kind of thing, it may not be the place for you.

Other very specific examples (apart from that below) I’ve found are “NHS Bassetlaw CCG Expenditure over £25K” and “Planned road works on the HE road network“………..


Super Bizarre DataSets

I’ve not used any of these but saw this article via Brent Ozar’s weekly newsletter – https://thestacker.com/. Some of them sound pretty cool, others are a bit out there such as: “Abandoned Shopping Trolleys in Bristol Rivers” from data.gov.uk and “The Great British Toilet Map“…….

MSBuild 2020 – Build Book of News — May 20, 2020
How To Fix OBS Black Screen when recording SQL Server Session Demo’s? — April 2, 2020

How To Fix OBS Black Screen when recording SQL Server Session Demo’s?

Everyone loves a demo. When I say everyone, I mean I personally love seeing a demo in a session which is why 99% of any sessions I present will have at least one demo. This is a risky business, especially when dealing with Cloud stuff and conference wifi which is why I always record my demo’s in case they’re ever needed.

Those that know me know that I’m tight and don’t like spending money on stuff that I really need and tend to waste it on cars and watches instead so when it comes to recording I use the FREE opensource software Open Braoadcasting Software (OBS). This software is more than I’d ever need for recording and did I mention, it’s FREE!!

However, there has always been a bit of an issue with getting it to work. Especially for those fortunate to have a separate GPU. My laptop has a NVIDIA Quadro P1000 GPU, not the best out there but it came with the beast and does a job for me. This however is where the issue begins. When you have a separate GPU the software doesn’t know which one to use so when you fire up OBS and choose your Display Capture (ie. screen to record) you will see nothing but a black screen. You can see by the shock on my face that I’ve seen this before but updates seem to reset the changes you need to make (see further down):

Now, the fix used to be very simple, you would:

  1. Open the NVIDIA control panel
  2. Click on “Program Settings” tab
  3. Choose the Open Broadcaster Software in “Select a program to Customise”
  4. Change the preferred graphics processor to “Integrated Graphics”
  5. Click Apply and re-open OBS

However, recent updates to either windows, NVIDIA and/or OBS has meant that this alone no longer works and you will end up tearing your hair out as per image below

There is now an additional step you need to undertake which is to change the OBS exe to run in “Power Saving” mode.

To do this, navigate to Settings–>Graphics Settings and Browse to the OBS exe which can be found at “C:\Program Files\obs-studio\bin\64bit”. Once selected, click Options and choose “Power Saving” for the Graphics Specification as per image below:

Once you have saved this then the screen Capture should now show the screen you are wishing to record you demo’s on and we’re in a happy place :).

As to why this is now required, I really don’t know at the moment and I’m sure in a few months time there’ll be something else which stops it from working.

Hopefully this helps others and if nothing else, will act as a reference guide for when I have to do this again in a few months and will have forgotten the process!

“Kubernetify” your Containers — February 23, 2020

“Kubernetify” your Containers

Adding the link to github which contain the slides and demo’s from the various events I have delivered this session to:

Github – Kubernetify Your Containers

To see them all I have given the root folder and if you search for “kubernetify” you should see everything needed 👍

Abstract

We have all now had a play around with Docker and Containers or at least heard about them.

This demo heavy session will walk through some of the challenges around managing container environments and how Kubernetes orchestration can help alleviate some of the pain points.

We will be talking about what Kubernetes is and how it works and through the use of demos we will:

  • Highlight some of the issues with getting setup (Specifically Minikube on Ubuntu),
  • Deploying/Updating containers in Kubernetes (on-Prem as well as AKS using Azure DevOps)
  • Persisting data
  • How to avoid making the same mistakes as I have
Speaking — January 22, 2020

Speaking

Upcoming and previous speaking engagements. Links to slides and demo’s can always be found on github or youtube. If you would like me to speak at your event whether in person or remotely then please contact me.

Upcoming Speaking Engagements

September 2nd 2020: SQLBits London 2020
England (London)
Database CI/CD with Docker Containers and Azure DevOps (45mins)

September 26th 2020: SQLSaturday Denmark
Denmark
Database CI/CD with Docker Containers and Azure DevOps

Previous Speaking Engagements

February 29th 2020: Scottish Summit
Scotland (Glasgow)
Kubernetify” your Containers (45mins)

March 7th 2020: DataMinds.be
Belgium
Database CI/CD with Containers and Azure DevOps

February 1st 2020: SQLSaturday #927 Edinburgh 2020
Scotland (Glasgow)
Kubernetify” your Containers

December 14th 2019: SQLSaturday #910 Slovenia 2019 (Ljubljana)
Slovenia (Ljubljana)
Kubernetify” your Containers

October 1st 2019: Techorama.nl
Netherlands (Pathé Ede)
Database CI/CD with Containers and Azure DevOps

September 13th 2019: Data Scotland
Scotland (Glasgow)
Kubernetify” your Containers

June 20th 2019: DataGrillen 2019
Germany (Lingen)
Database DevOps with Containers and Azure DevOps

April 27th 2019: Data In Devon 2019
England (Exeter)
Database DevOps with Containers and Azure DevOps

December 8th 2018: SQLSaturday #782 Slovenia 2018 (Ljubljana)
Slovenia (Ljubljana)
Introduction to Containers

October 8th 2018: SQLRelay (Newcastle)
England (Newcastle)
AWS Glue – Let’s get “stuck” in!

October 2nd 2018: Introduction to Containers (One off for those who missed Data n’ Gravy)
England (Leeds)
Introduction to Containers

September 18th 2018: SQLNorthEast UserGroup
England (Newcastle)
AWS Glue – Let’s get “stuck” in!

September 14th 2018: SQLGLA 2018
Scotland (Glasgow)
AWS Glue – Let’s get “stuck” in!

September 6th 2018: Data Platform User Group (Leeds)
England (Leeds)
AWS Glue – Let’s get “stuck” in!

September 5th 2018: PASS Manchester SQL Server User Group
England (Manchester)
AWS Glue – Let’s get “stuck” in!

August 1st 2018: Introduction to Containers (One off for those who missed Data n’ Gravy)
England (Leeds)
Introduction to Containers

June 22nd 2018: SQLGrillen 2018
Germany (Lingen)
Introduction to Containers

June 12th 2018: Edinburgh Data Platform
Scotland (Edinburgh)
Introduction to Containers

April 28th 2018: Data n Gravy
England (Leeds)
Introduction to Containers

April 19th 2018: Glasgow SQL User Group
Scotland (Glasgow)
Introduction to Containers

November 30th 2017: SQLNorthEast User Group
England (Newcastle)
Introduction to Containers

Database CI/CD with Containers and Azure DevOps — September 29, 2019
AKS SQL Server Error – 0/1 nodes are available: 1 node(s) exceed max volume count — September 21, 2019

AKS SQL Server Error – 0/1 nodes are available: 1 node(s) exceed max volume count

Background

Whilst playing around with my session for Techorama.nl I encountered an error I hadn’t seen previously whilst deploying SQL Server on Linux in Azure Kubernetes Service (AKS)

0/1 nodes are available: 1 node(s) exceed max volume count

The yaml I used was only slightly modified (mainly names) from scripts used on minikube and docker-desktop so I was a little confused as to why I was getting this in AKS.

As it happens, the reason is because I am tight and don’t like spending money! During testing etc I drop my AKS node size to as small as I can have it, in this case it was a Standard_B2s (2CPU / 4GB) which previously I’ve never had issues with until this particular demo.

When playing around with AKS you may have used a single PersistentVolume (or no volumes at all) but this particular setup had:

  • 1 for systemdbs
  • 1 for sql data files
  • 1 for sql log files

Which if you can do maths, equals 3 files. Which is fine for this particular Azure VM size as you can attach 4 disks. However, the issue arises once you start adding additional deployments with the same setup but in a different namespace. This would take me over the threshold of the 4 allowed disks and gives you the error that you have exceeded the max volume count 😦

So how do you fix this?

The options are to either scale up your VM size or alter your deployment to use fewer disks. In my case I could get away with only having 1 disk for systemdbs/data/logs. This is a demo environment so I can do this 🙂

Upgrading AKS to higher than v1.13.10 — September 15, 2019

Upgrading AKS to higher than v1.13.10

I recently received an email from Microsoft Azure regarding some security vulnerabilities in AKS and to upgrade to >= 1.13.10:

Looking in the Azure Portal, there was only an option to upgrade to v1.12.8

and this was confirmed by running:

az aks get-upgrades --resource-group JCL-DevOps --name DevOps-K8s-Test --output table

As it is late at night my brain wasn’t working as it should be but thought I’d put a quick blog out there to say that if you are on v1.11.5 and want to upgrade to >= v1.13.10 then you have to do this in a 2 stage process by upgrading to v1.12.8 first:

az aks upgrade --resource-group myResourceGroup --name myAKSCluster --kubernetes-version 1.12.8
 az aks upgrade --resource-group myResourceGroup --name myAKSCluster --kubernetes-version 1.13.10

After upgrading to v1.12.8 you will now have the option to upgrade to v1.13.10 and then above that:

Now that I am up to 1.14.6, there are no further updates available:

az aks get-upgrades --resource-group myResourceGroup --name myAKSCluster --output table

As to why exactly this is I haven’t managed to find out yet but I have to assume it is like a lot of applications, it has to be a staged process – think upgrading SQL Server 2000 to SQL Server 2019, you can’t do this in 1 upgrade step 🙂

However, I’m a little confused and disappointed that this day in age and with the upgrade being minor version upgrades that it can’t be done in one go – perhaps I’m asking too much……………?

Upgrade scripts / path I used were:

az aks upgrade --resource-group myResourceGroup --name myAKSCluster --kubernetes-version 1.12.8
az aks upgrade --resource-group myResourceGroup --name myAKSCluster --kubernetes-version 1.13.10
az aks upgrade --resource-group myResourceGroup --name myAKSCluster --kubernetes-version 1.14.6