Video recording of the Techorama.nl demo.
This video shows a failed test in the previous release and how we go about fixing it. It also shows how the build pipeline was built and steps through the release pipeline.
Blog posts to follow……
Video recording of the Techorama.nl demo.
This video shows a failed test in the previous release and how we go about fixing it. It also shows how the build pipeline was built and steps through the release pipeline.
Blog posts to follow……
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:
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 😦
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 🙂
I wanted to through together a quick post here to help out others who may be having issues with running multiple jobs in a build pipeline and there being no consistency in what order they run in.
When I first started with VSTS and ultimately Azure DevOps, I went through many failed builds because the order of the jobs in your pipeline don’t run in the order that you’ve built them and how you would logically believe them to run. The image below shows two Build Pipeline jobs but when the build is queued, whether this be manual or via CI, the second job is running before job #1. In this example the build will fail because Job #2 is to deploy a dacpac to a SQL Server on Linux Docker Container (Using Ubuntu Agent Host) but obviously this cannot be done until the dacpac has been created in Job #1 which is running on a VS2017 Agent Host:
The reason for this is that when you specify multiple jobs in a build pipeline , by default they run in parallel. In my experience I never found that both jobs ran in parallel, always one after the other so it doesn’t seem to quite match what the Microsoft docs states but it’s not something I’ve ever spent the time investigating further.
This can obviously be very frustrating especially as (from my testing) there is no consistency in which order they run but I did find that cancelling a build or re-running a build straight after a failure seemed to through it out of sync whereas previously it was all running in the correct order.
To stop this sporadic order of job running you can set Job dependencies in Azure DevOps. The process is so simple to setup and if you didn’t know about it and have been tearing your hair out over failed builds due to ordering then you’re going to kick yourself when you see the simplicity.
All you need to do is select the job you wish to start after another (other specific conditions can be applied) job completes and scroll to Dependencies, click the drop down and (in this example there is only one) the job you want to depend on will be in the list:
That’s it, couple of clicks and your job ordering is sorted 😉
In part 1 of this series we went about setting up our Azure DevOps account, creating a project and adding a Database Project to it. In Part 2 we will look to run through creating a new build pipeline creating a new Docker Image and pushing it to DockerHub.
NOTE: In Part 3 we will change to using Microsoft Container Registry (MCR) for two reasons:
Before we begin creating our build pipeline, it is advised that a Service Connection to Docker Hub (we will also be creating one for Kubernetes in Part 2) is created. This means we aren’t entering passwords / other secure information into our YAML file.
We can do this by selecting Service Connections from the Project Settings. From the image below you can see that there are a large variety of service connections that can be created but we will be choosing Docker Registry:
Simply give the connection a name and enter the Docker ID and Password:
NOTE: once you have created this you need to re-save your pipeline before it will work. This Resource Authorization link provides more information but Microsoft are working improving the user experience on this.
Now that we have created the service connection, we can now look to create our Build Pipeline. Select Builds from the menu on the left and click “New Pipeline”
Select the repository where the project you wish to build resides, in our case it is Azure Repos:
Select the project we have created – TestProj”:
You will now be presented with the option to use a Build Pipeline template or start from scratch.
One of the templates is “Docker Image” so we will choose that one:
This will auto generate some YAML code to get you started:
As we are using DockerHub as opposed to MCR we have to make a change to the azure-pipelines.yml file which will be used.
This link provides more information but in short we need to change the filename:
If you have a Docker Hub account, and you want to push the image to your Docker Hub registry, use the web UI to change the YAML file in the build pipeline from azure-pipelines.yml to azure-pipelines.docker.yml. This file is present at the root of your sample repository.https://docs.microsoft.com/en-gb/azure/devops/pipelines/languages/docker?view=azure-devops&tabs=yaml#example
Once you have made the change, annoyingly you don’t seem to be able to exit from the file with a simple “Save”, you have to “Save And Run”, which will initiate a failed build.
You can pull the latest changes locally and view/change the file in VS if you prefer:
NOTE: You will also need to update the Pipeline to use the new file. You can do this using the Visual Editor:
So, we now have our YAML file name updated and commited as well as the build pipeline updated to use it. However, before we proceed we need an actual Docker image and push that to our Docker Hub repo
Pull latest SQL Server 2019 on Linux image to local repository:
docker pull mcr.microsoft.com/mssql/server #This will pull the latest version
Now push this image up to Docker Hub giving it the tag “testprojsql2019“:
docker tag mcr.microsoft.com/mssql/server:latest sqlgeordie/azuredevops:testprojsql2019
docker push sqlgeordie/azuredevops:testprojsql2019
Using VSCode for output:
We’re not quite ready to run our build, the build pipeline doesn’t create a Dockerfile so we need to create this ourselves. If we don’t we get this error:
"unable to prepare context: unable to evaluate symlinks in Dockerfile path:" lstat /home/vsts/work/1/s/Dockerfile: no such file or directory
FROM sqlgeordie/azuredevops:testprojsql2019 RUN mkdir -p /usr/src/sqlscript WORKDIR /usr/src/sqlscript CMD /bin/bash
Now, we have to amend the YAML file to login in to DockerHub for us to be able to pull down the image in order to build using the Dockerfile. You will notice in the image below that i have highlighted “build an image”, the reason for this is relevant in the next section.
steps: task: Docker@1 displayName: 'Build an image' inputs: containerregistrytype: 'container Registry' dockerRegistryEndpoint: sqlgeordiedockerhub imageName: 'sqlgeordie/azuredevops:$(Build.BuildId)' command: build an image dockerFile: '**/Dockerfile'
task: Docker@1 displayName: Login inputs: containerregistrytype: 'container Registry' dockerRegistryEndpoint: sqlgeordiedockerhub command: login
task: Docker@1 displayName: 'Push an image' inputs: command: push an image imageName: 'sqlgeordie/azuredevops:$(Build.BuildId)'
There are examples on GitHub docs which have (in my opinion) errors. For example, I mentioned earlier that I highlighted “build an image” for a reason, that reason is that it is incorrectly stated as “build” (also the same for “push”) on GitHub and this gives errors.
Complete YAML File
#Docker image #Build a Docker image to deploy, run, or push to a container registry. #Add steps that use Docker Compose, tag images, push to a registry, run an image, and more: #https://docs.microsoft.com/azure/devops/pipelines/languages/docker trigger: - master pool: - vmImage: 'Ubuntu-16.04' steps: - task: Docker@1 displayName: 'Build an image' inputs: containerregistrytype: 'container Registry' dockerRegistryEndpoint: sqlgeordiedockerhub imageName: 'sqlgeordie/azuredevops:$(Build.BuildId)' command: build an image dockerFile: '**/Dockerfile' - task: Docker@1 displayName: Login inputs: containerregistrytype: 'container Registry' dockerRegistryEndpoint: sqlgeordiedockerhub command: login - task: Docker@1 displayName: 'Push an image' inputs: command: push an image imageName: 'sqlgeordie/azuredevops:$(Build.BuildId)'
The “incorrect” example in the docs is:
- task: Docker@1 displayName: Build image inputs: command: build azureSubscriptionEndpoint: $(azureSubscriptionEndpoint) azureContainerRegistry: $(azureContainerRegistry) dockerFile: Dockerfile imageName: $(Build.Repository.Name)
The strange thing is that if you edit the file directly online there is no error:
However, if you edit the pipeline you see the red syntax error “squiggle“:
Please make sure you change this otherwise you will receive and error in your build.
To get back on track, in theory, we should now be able to run the build which will pull the image from our DockerHub repository, and initiate building a new Docker image from our Dockerfile (granted a very basic build) and push it back up to DockerHub
We can now check the image exists in Docker Hub:
Pull it down:
Check local images:
There we have it, we have successfully built a new Docker image from a Dockerfile which resides on DockerHub and pushed the newly created image back to DockerHub.
In the Part 3 we will look to expand on this and look to incorporate it into the TestProj we created in Part 1 and show how we can push changes made to our TestDBProj to Azure DevOps to initiate a build process to provide a new Docker Image with our changes applied.
I have created some videos (no audio I’m afraid) of this process which was used as part of my sessions at Data In Devon and DataGrillen earlier this year. I will be looking to replace these with either a full blog post (ie. Part 3) or perhaps re-record the videos with audio.
I decided to throw together some steps on how you can start utilising SQL Server running on Linux in a Docker container a little more than just doing a docker run, connecting via SSMS/sqlcmd, running a few commands then blowing it away and saying you now know docker 🙂
DevOps, CI/CD, automation, whatever you want to call it is a massively hot topic at the moment and I’ll be running through how you can get yourself started with a database project (.sqlproj) and source control using Azure Repo (Repository). We will then run through how to take a Docker image (this can be stored in Dockerhub or Microsoft Container Registry) and push any changes to this via an Azure DevOps build pipeline to create a new Docker image with your changes applied. For the purposes of this blog post I will be using DockerHub but Part 3 will be using the Microsoft Container Registry (MCR) due to issues I have discovered in the Deployment pipeline in Azure DevOps meaning that I can’t use DockerHub.
This is quite a lengthy post so I will save the Deployment pipeline to run the newly created image in a Kubernetes cluster ready for UAT, Pre-Prod or even Production (if you’re brave enough) for part 3. Or, if you’re in Exeter for Data in Devon on Saturday April 27th, or DataGrillen on Thursday/Friday 20th/21st June then come and see my session where we will delve a bit more into this.
Seeing as most of this will be run in Azure, there are small costs associated with running the Kubernetes cluster but everything else in part 1 and part 2 are (at the time of writing) completely free – so no excuses! the list of tools used are listed below:
First off, in order to create our Database Project (sqlproj) then we will need to download and install SQL Server Data Tools. This will install a Visual Studio Shell so you don’t necessarily need a full blown edition of Visual Studio. You can download it here.
Azure DevOps is a one stop shop for your Development life cycle requirements and can simplify the automation of an entire continuous integration (CI) and continuous delivery (CD) pipeline to Azure . There are several tools within it including:
There are paid for aspects of Azure DevOps. For example if you wish to make an Artifact available to teams larger than 5 users then you will have to pay $30/mth. Everything we will be using here is free!
To get started, you will need to create an account or if you have one already, sign in:
Once you have created your account and went through the various authentication/verification, you can start creating a project in Azure Repo. In the screen shots below you can see that even a Private repo is free which is fantastic!
Give it a name and Description if you wish and choose a version control type. I have chosen Git because that’s what all the cool kids are using.
Congratulations! Your project is now created and ready to connect to using VS / SSDT.
You can connect directly to your Azure Repo (Git) project directly from VS, this is done by selecting the Team Explorer menu option:
Enter your login details and select the repo
You will have to “Map” the Git Repo to a folder on your local machine, this can be anywhere so choose somewhere suitable.
Enter the details you used to create the Azure DevOps account and Save Git Credentials so you won’t have to re-enter these later.
That is it, you are now connected to your Azure Repo so any project you make changes to locally can now be pushed to an online repository!
For this demo I have created a folder in the project called “TestDBProj” in Solution Explorer, this isn’t necessary but I like to try and keep things a little tidy:
Looking at Team Explorer, everything is now setup and you have now cloned the TestProj project to your local development machine. We can now get started with making a few changes.
Seeing as TestProj literally has nothing in it, we need to create a new Database project and we can call it TestDBProj:
Fantastic, our TestDBProj is now created and we can start adding objects to it. At this point if you have a database schema you need to import you can do that using the schema compare but again, keeping it simple we’ll just add a new table called TestTable1:
Add a new column called [descr]:
We’ve made some changes but at the moment our project has not been added to Source Control so let’s add it:
Check the changes and commit making sure you add a commit message:
NOTE: This can also be done via command line but as this is an introduction / getting started we are using the Gui.
You should now get a message saying that the changes have now been committed locally:
To get the changes into our Azure Repo we need to push them, or in the case of the Gui “Sync” our changes and using “Push”:
There we have it, our changes have now successfully pushed to our Azure Repo and we can check online and it should now be available:
In Part 2 of this series we will start looking at the Azure DevOps Build Pipeline and how to create a new (very basic changes made) Docker Image from an Image stored in DockerHub and push it back.
I have created some videos (no audio I’m afraid) of building an Azure DevOps Pipeline which will create a new docker image with the changes made to the project and deploy it to Kubernetes. These are part of the demos used as part of my sessions at Data In Devon and DataGrillen earlier this year.
I (and many others) have done a series of docker blog posts over the last couple of years but they’ve all tended to evolve around spinning up a SQL Server 2017+ container for testing or demo purposes. This is only really the start, think of the bigger picture here, once you have your database environment the world is your oyster.
This blog post will show how we can use SQL Server 2019 CTP2.1 running on Linux (Ubuntu) in a docker container as our data source for a Power BI environment in next to no time!
These steps show a very manual process for completing this setup, if it is something you are looking to do frequently then I suggest creating a Dockerfile and/or yml file and use docker-compose. This way you can have all your setup in one file and it will be a single statement to get your SQL Server 2019 environment up and running.
Once you have Power BI installed you will have to enable Hyper-V windows feature before installing Docker for Windows. If you don’t, the docker install will ask you if you wish to enable Hyper-V but in my experience this has rarely (ie. never) worked well – could just be me but I play it safe and enable it before installing. Running Linux containers on Windows 10 inevitably means that the Container will run a Isolated / Hyper-V Container which basically runs a cut down, lightweight Hyper-V VM.
Once DfWCE is installed, you will have to modify the Memory settings to be at least 3250MB. By default this is 2048MB but this isn’t enough to run SQL Server On Linux Containers and needs to be amended.
Once configured, we’re now ready to create our SQL Server 2019 CTP2.1 on Ubuntu. As we are going to be creating a container with a database that we can use the data from (ie. AdventureWorks2016CTP3), we’re first going to create a data volume and copy the bak file (from C:\Docker\SQLServer\Backups) to it so it is available to restore:
#Create dummy container to define and copy backup file docker container create --name dummycontainer -v sqldatavolume:/sqlserver/data/ sqlgeordie/sqlonlinuxdemo:demo #Check that the container exists docker ps -a --format $psformat #Copy AdventureWorks or whatever you like ;) docker cp C:\Docker\SQLServer\Backups\AdventureWorks2016CTP3.bak dummycontainer:/sqlserver/data/AdventureWorks2016CTP3.bak #Check what volumes you have docker volume ls #Don't need the dummycontainer anymore so get rid docker rm dummycontainer
NOTE: We have “created” a container but don’t need to run it so the status will indicate that it is “Created” and not the up-time:
You may be asking why I’ve created a datavolume as opposed to just bind mounting my local folder to the container – good question. There are many blog posts out there explaining the pros/cons of both but for me the biggest reason is that mounting a host folder goes against everything Containers are about – portability. The container shouldn’t care about where it is or anything outside of it, bind mounting means that wherever this container is created, you will need that host folder setup and available. Using a data volume means we can reuse this for other containers, even on another host if we were in an environment that has access.
Now we have our data volume with a copy of AdventureWorks2016CTP3.bak, we can proceed to creating the container:
#Create SQLLinuxLocalPersist container docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssword1' ` --name SQLLinuxLocalPersist -d ` -p 1433:1433 ` -v sqldatavolume:/sqlserver/data ` mcr.microsoft.com/mssql/server:2019-CTP2.1-ubuntu #Check it is running docker ps -a
All going well, your container should be created and running:
The next step is to create the database from the backup, we will do this manually via Azure Data Studio (Other SQL Server Management GUI’s are available).
Simply connect to our locally created container. I have used the default SQL Server port of 1433 so we can use “Localhost”. As windows authentication is not currently supported, we have to use a SQL Login, in this example I’m using “sa”:
Next, we want to restore the DB:
You should now have a restored version of AdventureWorks2016CTP3 ready to use and abuse in Power BI. Simply setup a connection to (again) Localhost, no port mapping is required in this instance but if you had a different port to 1433 you’d have to use something like: Localhost,15105.
Once connected, you can play around with the data til your hearts content 🙂
There we have it, a fully working Power BI demo / sandbox environment ready for you to play around with. The longest aspect of this setup will be the downloading/installing of Power BI Desktop and DfWCE. Once setup, the scripting of creating the container can be done in a minute or two -AMAZING!!!!!
Having recently gone past our 5th birthday, we have decided to make some changes for 2019. Myself and Mike (t) have been extremely busy this year and perhaps not dedicated as much time to SQLNorthEast as we would have liked and have therefore made a decision to bring in additional help with the organising so I would like to announce two new additions to the committee in Glen Leck (t)and Mark Steinberg (t) – I hope they know what they’re letting themselves in for 😉
We are also in the process of re-branding the Usergroup from SQLNorthEast to Newcastle DPaC (Data Platform and Cloud) as we are not just SQL Server and cover most if not all areas of Microsoft’s Data Platform. The PASS website and associated details have also been amended and we have also setup a new Meetup which will be where our communications will be done from now on. Twitter has also been updated so please now use the handle @DPaCNewc.
Feel free to join our Newcastle DPaC slack workspace.
Our next meetup will be on Tuesday November 27th with Craig Porteous (b|t) and Paul Broadwith (b|t)talking Git Fundamentals & PowerShell Standards Agency. Both sessions are fantastic and definitely not one to be missed!!
Our schedule will be changing slightly to accommodate our new Power BI Meetup so we will be running these on alternate months:
|Power BI||Thursday 17th January|
|DPaC||Tuesday 19th February|
|Power BI||Tuesday 19th March|
|DPaC||Tuesday 16th April|
|Power BI||Wednesday 29th May|
|DPaC||Tuesday 18th June|
|Power BI||Tuesday 16th July|
|DPaC||Tuesday 20th August|
|Power BI||Tuesday 17th September|
|DPaC||Tuesday 15th October|
|Power BI||Tuesday 19th November|
|DPaC/Power BI Social||TBC|
The people of the North East have asked and we have listened, introducing the Newcastle Power BI Usergroup
A newly created meetup spun off from Newcastle Data Platform and Cloud meetup to help people across the North East get up and running with Power BI by offering a huge range of talks from the very basics to some of the more advanced analytics. So whether you know nothing about Power BI and looking to learn or a seasoned Pro there will be something for everyone.
We will be running these meetup’s bi-monthly initially to gauge interest with an aim to have these running every month at Quorum Business Park.
With 25 new members within a couple of days of setting it up and before we even officially announced it, we are expecting great things from this new Usergroup 🙂
Join us on our Newcastle-PowerBI slack channel
As with anything open source you’re prone to things changing without you necessarily being aware and the error message in the blog title is something that has been sprung upon me whilst tidying up demo’s for my latest session on Docker and SQL Server On Linux. I hadn’t touched these particular demo’s since October 2017 so as to when things stopped working, I don’t know but they certainly worked the last time I did the session but doing a re-pull of the images, things broke.
With the micorosft/mssql-server-linux images, if you wish to mount a volume to a container then you will experience an error message I’ve never seen in SQL Server:
The transaction log for database ‘master’ is full due to ‘NOTHING’.
I have tested:
and the issue was apparent on both images. The reason as highlighted on GitHub seems to be related to the filesystem, seeing as I’m running Docker for Windows I can’t change my file system from ext3 to ext4 like what others have done.
There is a workaround for this. The issue (certainly for me) only seems to be apparent if you try to mount a volume to a folder that already exists (ie. /var/opt/mssql/data/), mount to a new folder (ie. /sqlserver/data) then it will work:
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssword1' ` --cpus="2" ` --name SQLLinuxLocal1 -d -i ` -p 1433:1433 ` -v C:\Docker\SQLServer\Linux\SQLLinuxLocal:/sqlserver/data/ ` microsoft/mssql-server-linux:latest
Does not work:
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssword1' ` --cpus="2" ` --name SQLLinuxLocal2 -d -i ` -p 15106:1433 ` -v C:\Docker\SQLServer\Linux\SQLLinuxLocal:/var/opt/mssql/data/ ` microsoft/mssql-server-linux:latest
Or if you can, create a data volume instead and use that 😉
Once you have the files you need (ie. .bak file) in the /sqlserver/data folder within the container, you can then restore it using WITH MOVE to the /var/opt/mssql/data/ folder and this will work.
Any questions on setting up the demo’s just give me a shout 🙂