Monday Coffee 2017-03-27

Hey all, I’m back after attending SQL Saturday in Iceland. This week’s going to be busy!

I’ve said before that SQL Saturday Iceland was the first time that I presented a full hour session. It seemed to go well although having it start at 14:50 wasn’t the best as I was kinda nervous so I stressed out for most of the day! But done now and I was pretty pleased with it as I finished on time whilst getting all my material in and I answered all the questions.

I don’t really want to write an “Advice for first time presenters” post as there are plenty of them out there but I will say a couple of things that really helped me. First one, don’t wing it. I made notes on each of the slides that I had in my deck and worked through them one by one to make sure I got everything in that I wanted to say. I then grabbed a meeting room after hours in work and went through it over and over, making sure that it flowed nicely and that I got used to referring to slides whilst talking.

Also, my demos would have required a lot of typing and parts that required things like server bounces which would have taken too long to do live so I invested in decent video editing software and it paid for itself imho. The demos worked well and I was able to talk whilst they were playing, something that I don’t think that I’d have been able to do if I was doing everything live.

So all in all, really chuffed that I’ve done it and it’s now onto SQL Bits!

Final word, Iceland is an absolute amazing country. I hired a car and went driving round for a couple of days and the scenery is nothing sort of spectacular. My advice, if you have an chance, go.

Have a good week!


Friday Reading 2017-03-17

As this is being posted I’m on my way up to Dublin Airport to catch my flight to Iceland ready for SQL Saturday Iceland tomorrow! Been looking forward to this for a while now, my first opportunity to give a full session on a subject that I’ve become quite involved in, containers.

After the session I’m hanging around for a few days afterwards to explore, it’s going to be amazing but that does mean no posts next week ๐Ÿ˜ฆ (ha)

A bit of shameless self promotion first: –

Summary of my Container Series
I’ve added this because I’ve written a few posts about containers and thought it’d be handy to have one page that references them all.

Ding – The Worldโ€™s Largest Mobile Top-up Network Streamlines QA with SQL Server Containers
SQLServerCentral.com has published a case study I wrote on how Ding.com implemented containers to improve their QA processes. This could be handy for anyone looking at doing the same

So anyway, this week I’ve been reading: –

Defending Invoke-SqlCmd
Should this cmdlet be used? Some think not, others…

The Biggest Danger to your Database: Me
Love articles like this! Everyone makes mistakes and it takes guts to post them online.

The Database Corruption Challenge
I’ve linked to this (lots of times) before but it’s really good practice for DBAs so want to keep putting it out there

DevOps Isn’t Perfect
DevOps is all the rage at the moment. Steve Jones talks about how the process should lead to fewer mistakes

Alright that’s it from me. Have a good weekend and I’ll see you in just over a week.


Monday Coffee 2017-03-13

Less than a week to go until SQLSaturday Iceland now, it’s going to be a busy couple of months for me but am looking forward to it. I’ll get my slides and demos uploaded here at some point.

I’ve been debating whether or not to do demos live when I’m speaking. In the past I’ve everything live but certain presentations make that a little risky. I’ve done lightening talks where I’ve performed the demo then and there but that’s because I know that the risk of something going wrong is minimal. I’m working on a local instance of SQL and am not depending on anything else (e.g. – an internet connection).

It also depends on what the demo actually is, if it’s code based then running it live means that you can talk about the code but if the code is simple and the demo is more about the process, running it live really doesn’t add anything to the table.

For my session on containers, the demos will be videos. This is in part to reduce the risk of something going wrong but mainly it’s because the code is very simple, what’s happening when I’m running it is what I want to talk about. A video demo means that I don’t have to concentrate on typing the code (you really should never type in demos, copy & paste) and can talk about what’s happening.

Doing it this way will be beneficial as I can focus entirely on the audience and not be staring at my screen. I want to engage with the audience as much as possible and make the session as good as it can be.

Have a good week!


Friday Reading 2017-03-10

Just over a week to go until SQL Saturday Iceland! Really looking forward to presenting my session and then I have four days to explore!

Building the Terminator Vision HUD in HoloLens
Because why not?

The $650 SQL Saturday
Steve Jones talks about running a SQLSat event on a budget

Azure Marketplace
Can someone find the Windows Server 2016 Core image please?

xp_cmdshell โ€“ Naming, Shaming and Taming
Setting up a SQL Server Audit to capture xp_cmdshell usage

Old news really but have you all got your Raspberry Pi Zero W?

Also – v 7.3.3 – Fix CIA Hacking Notepad++ Issue

Enjoy the rugby and have a good weekend!


Exporting Container Images

I’ve already covered how to push images to the Docker hub here but what if we only want to share a custom image in-house?

Well, it’s really simple to do by using the docker save and load commands. I’ll run through the process.

What I’ve done is created a custom image following the instructions here. Here’s my custom image: –


What I’m going to do now is save my custom image to a location [C:\temp] on my host server. So I’ll run: –

docker save -o myfirstimage.tar myfirstimage

N.B. – I’m saving the file as a .tar as this seems to be the format that works best for loading the image. I’ve tried exporting to other formats (.zip) but it always seems to become corrupt. The .tar format has always worked for me so I’ll stick with it.

Now the SQL images aren’t the smallest so this could take some time. But once it’s complete you’ll see the save .tar file: –


This can now be copied over to another server running the Docker engine and loaded into the local registry by running:-

docker load -i myfirstimage.tar


This will take some time but once it’s complete the exported image will be available in the server’s Docker registry. So that image was built on one server, exported and then loaded on another server. It’s an easy way to share images without having to push up to the Docker Hub.

By doing this you can ensure that every one of your team is using the same configured instance of SQL Server. Hopefully an end to “well it worked on my machine”…

Thanks for reading!


Monday Coffee 2017-03-06

Last week Docker announced the availablity of Docker Enterprise Edition. The existing version of Docker that I’ve been using has now been renamed to the Docker Community Edition.

So what does this mean for us Windows people hacking around with Docker on our local Windows 10 boxes? Very little it seems. The Community Edition will have access to the full Docker platform and (if needed) can be added to with paid-for addons from the Docker cloud. I haven’t had a chance to look over all the paid offerings but they seem to be mainly cloud based services, so I doubt I’ll need them (at the moment).

The Enterprise Edition is interesting as it raises the question, is Docker suitable for SQL Server in production environments? Now, I’m a big fan of Docker and have been using it extensively in my dev/test environments but I’m still not sure about production.

If you think about the advantages running SQL Server in containers brings; simple to setup, quick to deploy; are they relevant to production? I want to spend time setting up my 24/7 critical SQL Server instance, speed of build doesn’t really matter.

There may be other advantages that Docker Enterprise Edition brings so I’m definitely going to check it out but there’ll have to be something pretty good in there to convince me SQL Server containers are for production.

Have a good week!


Friday Reading 2017-03-03

Fun week, lot’s of things going on and throughout I’ve been reading…

Windows Server Premium Assurance and SQL Server Premium Assurance
Microsoft announce Premium Assurance, an additional six years of support

SQL VNext sp_configure on Windows and Linux with dbatools
Rob explores the SpConfigure commands in dbatools in SQL instances running on Windows and Linux

William Durkin – My first MVP Award
William Durkin thanks those who have helped him along the way to becoming a Data Platform MVP. Congrats William!

SQL Browser, what is it good for? Absolutely something!
Chris Sommer goes through what the SQL Browser service actually does

Announcing Docker Enterprise Edition
Docker have release a new version for business-critical deployments

Have a good weekend!


A GUI for Docker Container Administration

I’ve been working with containers for a while now and one of the questions that always gets asked when I demo the technology to people is, is there a graphical user interface out there that can be used to manage containers?

Now, I’m happy with working on the command line and in many ways, I prefer it. But everyone has different preferences so I went out and had a look to see what’s available. It didn’t take me long to run into Portainer who have built exactly what I was looking for. A management UI for Docker.

So let’s run through the setup and then look at the system. There’s a couple of pre-requisities to this I’m afraid, the first one is that you must setup remote administration using TLS on the Docker host that you want to manage via Portainer. I’ve detailed how to do this here.

Also, Portainer doesn’t support managing a local Docker Engine running on Windows so the way I’ve set it up is to run Portainer locally on Windows 10 and then point it at a server running the Docker Engine I want to manage. This means that you’ll need to install Docker locally, you can do that here.

EDIT: Anthony Lapenna (t) has let me know that you can run Portainer outside of docker, so you don’t need to have the engine running on your Windows 10 machine if you don’t want to. Instructions are here (at the bottom of the page).

Ok, so once you’ve got Docker running locally, run the following to see the Portainer image in the Docker Hub: –

docker search portainer


There’s the image that we need at the top, so pull that image down to your local repository: –

docker pull portainer/portainer


Once the image is down, verify that you can connect to the Docker Engine on the remote server from a powershell window on your local machine: –

docker --tlsverify `
  --tlscacert=$env:USERPROFILE\.docker\ca.pem `
  --tlscert=$env:USERPROFILE\.docker\server-cert.pem `
  --tlskey=$env:USERPROFILE\.docker\server-key.pem `
  -H=tcp://XX.XX.XX.XX:2375 images

What I’ve done here is copy the TLS certs generated on the server to my local machine and reference them via $env:USERPROFILE. Full details on setting this up is here.

Also, ignore the warning “Unable to use system certificate pool: crypto/x509: system root pool is not available on Windows“. Apparently it’s benign

If everything is working you should see the same output as running docker images on the server: –

OK, next step is to copy the certs into your C:\temp folder as the following script will copy them from that location into the container running Portainer. This is needed so that Portainer can connect to the Docker Engine running on the server.

copy-item $env:USERPROFILE\.docker\ca.pem C:\Temp
copy-item $env:USERPROFILE\.docker\server-cert.pem C:\Temp
copy-item $env:USERPROFILE\.docker\server-key.pem C:\Temp

Now we can create and run our Portainer container!

docker run -d -p 9000:9000 --name portainer1 -vC:/temp:C:/temp portainer/portainer -H tcp://XX.XX.XX.XX:2375 --tlsverify --tlscacert=C:/temp\ca.pem --tlscert=C:/temp\server-cert.pem --tlskey=C:/temp\server-key.pem


Once you’ve verified that the container is up and running you need to grab the private IP assigned to it: –

docker inspect portainer1


So the private IP address assigned to the container I’ve built is so I’ll enter into my web browser. If all has gone well you should see: –


Specify a password and then login. You will then see the Portainer dashboard:-


Viewing Containers: –


Viewing Images: –


It’s a pretty cool UI. Not only can you start/stop existing containers, you can pull new images down. I know it’s a bit fiddly to setup but if you can do this and hand it off to your users (don’t run it on your desktop though)…how much are they going to love you? ๐Ÿ™‚

Thanks for reading!


Monday Coffee 2017-02-27

Ergh, not a fun weekend rugby wise. But anyway…

Last week Microsoft released an image for SQL Server 2016 SP1 Developer Edition in containers. Previously the only edition available was vNext Enterprise Evaluation which was a real problem in making containers a viable option for many businesses.

There’s no point in having a development environment referencing a SQL instance that is not the same version as production. How many people would be running vNext in production? I bet there’s a few (mad) early adopters out there but in the main, I would say most businesses would be running 2016, 2014 or 2012.

Having this image available means that developers/DBAs can now seriously look at containers as an option when building development environments. Need to build an environment quickly? That’s what containers give you. I’d love to see this technology become widely used in the SQL Server world. I’ve been working with them for over a year now and being able to spin up a new instance of SQL Server in seconds is really cool.

It does beg the question are Microsoft going to release images for other, earlier versions of SQL Server? I’m honestly not sure that they will but if they want containers to become more widespread that would be the way to do it. We’ll see what happens but even if they don’t there are other options out there.

Have a good week!