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.

Advertisements
Creating a Power BI demo environment with SQL Server 2019 on Linux in a Docker Container as the data source. — December 5, 2018

Creating a Power BI demo environment with SQL Server 2019 on Linux in a Docker Container as the data source.

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.

Prerequisites:

Setup

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.

 

Make sure this is min 3250MB

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.

Have a read here and here for more information.

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!!!!!

SQLNorthEast is now Newcastle DPaC! — November 21, 2018

SQLNorthEast is now Newcastle DPaC!

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.

This means that if you wish to find out the latest news and about the next meetup then please join our Meetup and make sure you follow us on twitter otherwise you may miss out!

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 Jan 17th
DPaC Feb
Power BI Mar
DPaC Apr
Power BI May 28th
DPaC Jun
Power BI Jul
DPaC Aug
Power BI Sep
DPaC Oct
Power BI Nov
DPaC Dec

The month’s without a date are up for grabs so get in touch with Chris Taylor (b|t)  or any of the other organisers if you wish to speak next year.

Newcastle Power BI Usergroup — November 4, 2018

Newcastle Power BI Usergroup

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

 

 

Speaking — July 3, 2018

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

October 8th 2018: SQLSaturday #782 Slovenia 2018 (Ljubljana)
Slovenia (Ljubljana)
??

Previous Speaking Engagements

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

 

Merge image layers in Docker using –squash — June 20, 2018

Merge image layers in Docker using –squash

A question which always gets asked regarding the union layered file system for images is whether the intermediary image layers can be merged into one? The answer is yes, you can –squash image layers during a build from a Dockerfile.

The attached video shows 2 example builds of the same Dockerfile, one is standard, the second using –squash to merge the layers.

Merge image layers in Docker

Example run script:

docker build -t NewImageNameHere . --squash

To view the image layers related to the newly created image you can run:

docker history NewImageNameHere

This will show all the layers created from each instruction in the dockerfile but the Imageid is “missing” (only 1 layer / Imageid now exists) and there will be a comment to indicate the merge has occurred and only 1 image layer now exists:

Output Without –squash

Output from Without --squash

Output With –squash (and comment showing merge)

Output from With --squashComment showing merge information

Docker for Windows -SwitchDaemon easily — May 3, 2018

Docker for Windows -SwitchDaemon easily

If you are using Docker for Windows and want to switch between Linux or Windows containers you can do this by right clicking the Docker “Whale” in the systray and selecting “Switch to Windows containers”:

Switch to Windows containers

….but no one likes clicking around do they!

There is an alternative way to do this which I use in my docker session demo’s which makes things so much easier and the switch is a lot quicker! In powershell, run the script below and it’ll switch from Linux to Windows containers and vice versa:

Linux Containers:

LinuxContainerDaemon

Run the SwitchDaemon script:

& 'C:\Program Files\Docker\Docker\DockerCli.exe' -SwitchDaemon
Windows Containers:
WindowsContainerDaemon
and there you have it, a very simple but extremely useful snippet to switch between Linux and Windows containers,
Workaround for – The transaction log for database ‘master’ is full due to ‘NOTHING’ – Docker — April 24, 2018

Workaround for – The transaction log for database ‘master’ is full due to ‘NOTHING’ – Docker

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’.

The transaction log for database 'master' is full due to 'NOTHING'

Apparently I’m not the only one experiencing this and there is an issue opened (Since October 2017!!!) on GitHub and social.msdn.microsoft.com describing the issue.

I have tested:

  • micorosft/mssql-server-linux:latest
  • micorosft/mssql-server-linux:2017-GA

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:

Works:

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.

Introduction to Docker – Slides and Demo’s —
Docker – SQL on Linux image tag has changed! — October 4, 2017

Docker – SQL on Linux image tag has changed!

With the announcement of SQL Server 2017 last week came the updated microsoft/mssql-server-linux and microsoft/mssql-server-windows* docker images.

By default if you do a docker pull without a tag you will pull down the latest however with these new docker images this no longer works:

Docker pull latest.png

You get the error:

Error response from daemon: manifest for microsoft/mssql-server-linux:latest not found

I found this a bit strange so went onto the docker hub searching for microsoft/mssql images and see if they actually did exist:

Docker images on msft repo.PNG

SQL on Linux is what I was after and you can see from the image above it does exist but taking a close look at the tags tab you can see that they have now amended the tags to be microsoft/mssql-server-linux:2017-GA and microsoft/mssql-server-linux:2017-latest.

SQLonLinux-tags
The new tags

 

SQLonLinux-tags 2
This kind of tells us things have changed!

 

NOTE: The windows docker images still have the “latest” tag available

SQLonWIndows-tags

Now, if we run docker pull with the 2017-GA or 2017-latest tag it will grab it:

docker pull microsoft/mssql-server-linux:2017-GA

Docker pull 2017-GA.png

Obviously I have already done this and the image is up to date 🙂

 

Time to update all my demo scripts 😦