SQL Server & Containers – Part Three

This post follows on from Part Two in which we created a custom docker image. We’ll now look at pushing that custom image up to the Docker repository.

Let’s quickly check that we have our custom image, so run (in an admin powershell session):-

docker images

If you’ve followed Part One & Part Two you should see:-

customimagespart3

If you don’t have testimage there, go back to Part Two and follow the instructions. Don’t worry, we’ll wait 🙂

Ok, what we’re going to do now is push that image up to the Docker repository. You’ll need an account for this so go to https://cloud.docker.com/ and create an account (don’t worry, it’s free).

dockercloudsignup

Once you’re signed up and logged in, click on the Repositories link on the left hand side and then click on the Create button (over on the right): –

createrepository

Give your repository a name and then click the button to make it private (seriously, otherwise it’ll be available for everyone to download!) and hit Create.

On the next screen you should see the following over on the right:-

dockercommands

This is the code to push your custom image into your repository! Pretty cool but there’s a couple of things that we need to do first.

Back on your server we need to connect to the repository that we just setup. Really simple to do, just run the following:-

docker login

And enter in the login details that you specified when you created your Docker account.

dockerlogin

Ok, one more thing to do before we can push the image up to the repository. We need to “tag” the image. So run: –

docker tag testimage dbafromthecold/testsqlrepository:v1

N.B.- Replace my repository’s name with your own 🙂

What I’ve essentially done here is rebrand the custom image as a new image that belongs to my repository, tagged as v1. You can verify this by running:-

docker images

customimagespart3_2

You can see that a new image is there with the name of my repository.

Now that that’s all done we can push the image to the repository using the command that was given to us when we created our repository online:-

docker push dbafromthecold/testsqlrepository:v1

pushimage

Good stuff, we’ve successfully pushed a custom Docker image into our own Docker repository! We can check this online in our repository by hitting the Tags tab:-

dockerrepository

And there it is, our image in our repository tagged as v1! The reason that I’ve tagged it as v1 is that if I make any changes to my image, I can push the updated image to my repository as v2, v3, v4 etc…

Still with me? Awesome. Final thing to do then is pull that image down from the repository on a different server. If you don’t have a different server don’t worry. What we’ll do is clean-up our existing server so it looks like a fresh install. If you do have a different server to use (lucky you) you don’t need to do this bit!

So first we’ll logout of docker:-

docker logout

dockerlogout

And then we’ll delete our custom images:-

docker rmi testimage dbafromthecold/testsqlrepository:v1

dockerdeleteimages

And now we have a clean docker daemon to test pulling images from the repository! If you have a new server to use, it’s time to jump back in!

So log into your repository:-

docker login

And now we can pull our image down from our repository: –

docker pull dbafromthecold/testsqlrepository:v1

dockerpullimage
N.B.- I’m seeing “Already Exists” as I’m running this on the same server as I created and then deleted the image.

Once that has completed, you can check that the image is there by running:-

docker images

dockerviewimages2

And there’s the image that we’ve pulled from our repository! And we can use it to create containers!

So that’s how you can create a custom image that can be shared across multiple servers!

Hmmm, I can hear you saying (seriously??:-)). That’s all well and good but I’m not using SQL Server vNext in any of my test/dev/qa environment so this isn’t going to be of much use. Is there a way of getting earlier versions of SQL in containers?

Well, would you believe it? Yes there is! I’ll go over one such option in Part Four.

Monday Coffee 2016-11-28

MCSA SQL Server 2016

The new SQL 2016 MCSA exams are out and joy of joys, the DBA path no longer requires the Data Warehouse exam! I say this as a DBA who has had limited practical exposure to the topics covered by that exam so it’s always put me off going for the MCSA.

I feel that the MCSA should be a test of the knowledge that you’ve acquired through practical experience, so that the award is recognition of skills earned. Of course there’s going to be areas that you need to study as nobody works with every feature of SQL Server but having limited experience of the entire subject matter (such as mine with the subjects covered in the Data Warehousing path) is another thing completely.

So now I have no excuse to get certified. Well, the exams are still currently in beta so I might wait…

Or will I go for them at all? I’ve never had an employer ask if I have the MCSA and I’ve met lots of people who have various Microsoft certifications but got them not by studying, but by finding the exam questions online and practicing them. To me that vastly devalues the certifications but now that Microsoft no longer the MCM the MCSA (and subsequent MCSE) are all we have.

Looking at these exams and knowing myself, whether I do them or not solely depends on my workload next year. I’ve a few projects lined up so if they go smoothly and if I have time, I’ll go for them (maybe).

Friday Reading 2016-11-25

Friday again so before I spend a weekend watching sci-fi movies, I’ll be reading:-

Virtue in the Virtual
Tony Davies discusses containerisation and virtualisation technologies and how they could be used to benefit processes within RedGate

Query wait stats in SQL 2016 SP1 Execution Plans
What a cool feature!

Source Control in SSMS
Ken Van Hyning, Engineering Manager of SQL Server Client Tools at Microsoft discuss enabling TFS integration in SSMS
(N.B.- To get this to work you have to uncomment all the lines in the section specified and then run SSMS as administrator. Interestingly enough this unlocks the dark theme but it’s not complete, the custom areas of SSMS won’t change)

Giving back with code
Steph Locke talks about the reasons why you should make the code you write publicly available

SQL Server v.Next—Linux Preview and Ola Hallengren’s Jobs
Joey D’Antoni talks about SQL Server vNext on Linux and gives a quick intro into using Ola Hallengren’s maintenance scripts

SQL Server & Containers – Part Two

This post follows on from SQL Server & Containers – Part 1 and will go through how to build custom container images.

Since Part 1 came out Microsoft has released SQL Server vNext which is available in the Docker repository. I used the SQL 2016 Express image in Part 1 but that has now been deprecated so for this part we’ll use one of the new images.

To see what SQL Server images are available for you to download and run as containers, you can run:-

docker search microsoft/mssql-server

searchdockerrepository

So let’s crack on and build a container.

One word before we start however, this post assumes that you’ve installed the docker engine on a Windows Server 2016 installation as detailed in Part 1. If you haven’t installed the Docker engine, go back to Part 1 and follow the instructions, we’ll meet you here 🙂

As before to create a container, we first need to pull an image from the respository. Let’s go for the vNext image.

Open an admin powershell prompt and run:-

docker pull microsoft/mssql-server-windows

And now we can run a container from the image: –

docker run -d -p 15888:1433 -e sa_password=Testing11 -e ACCEPT_EULA=Y microsoft/mssql-server-windows

runningcontainer

We now have a running SQL Server vNext container. Note that the syntax to run the container has changed slightly from the code we ran in Part 1, the difference being
-e sa_password=Testing -e ACCEPT_EULA=Y instead of –env sa_password=Testing

Details of commands needed to run containers are documented in the Docker Hub which we’ll explore further in Part 3.

What we are going to do now is create a database within that container, then stop the container and create a new image from it.

So connect to the container (server IP address and the port we specified in the run command) and run the following SQL scripts:-

CREATE DATABASE [TESTDB];
GO
USE [TESTDB];
GO
CREATE TABLE dbo.DummyData
(PKID INT IDENTITY(1,1) PRIMARY KEY,
 ColA VARCHAR(10),
 ColB VARCHAR(10),
 ColC DATETIME);
GO
INSERT INTO dbo.DummyData
(ColA,ColB,ColC)
VALUES
(REPLICATE('A',10),REPLICATE('B',10),GETUTCDATE());
GO 10

This container now has a custom database in it with some random data. What we’re going to do now is stop that container and create a new image from it, so first run:-

docker stop b71

N.B. – b71 is the first three digits of my container ID. You’ll need to substitute for your container’s ID

Now that the container is stopped we can create a new image: –

docker commit b71 testimage

This will create a new image from our container called testimage which we can view by running:-

docker images

newimages

Great stuff, we’ve created a custom docker image! What’s really cool now is that we can create containers from that image, exactly the same as we did with the generic original image from the repository:-

docker run -d -p 15666:1433 -e sa_password=Testing22 -e ACCEPT_EULA=Y testimage

Once the command has executed you can connect remotely via SSMS using the server name and the port we specified above. The database that we created in the original image will be there, along with the data that we entered!

This is where containers start to come into their own in my opinion. You can build your own custom images and quickly spin up multiple instances that already have all the databases that you require!

Hmmm, you say. That’s great and all but are those custom images only available on the server that I’ve created them on?

Well, yes and no, but that’s something that’ll be covered in Part Three.

Monday Coffee 2016-11-21

Well there were quite a few announcements in the SQL Server world last week.

SQL Server vNext CTP was made available for download, the big news there is that that version will be available either on Windows or Linux. The Linux version of SQL has been in private preview for a while but for a lot of people out there (myself included) this was the first time that they could get their hands on the product.

However mad this may sound, SQL running on Linux wasn’t the biggest news for me last week. We’ve all known that Microsoft was going to release a SQL version for Linux for a while now so it wasn’t that much of a shock to the system. What was a shock was the first point in a blog detailing the first service pack for SQL Server 2016.

As of SQL Server 2016 SP 1 many of the features of SQL Server that were previously only available in Enterprise Edition will be available in the other editions where possible.

This is big news for me as what this means is that instead of scaling up our server running Enterprise Edition, we could scale out i.e. – build multiple new servers running SQL Server Standard Edition and split our workload across them for a fraction of the cost of our current Enterprise licence.

I mean OK, not all Enterprise features are available (no online operations for a start) and there is still the CPU and memory limits in the lower editions but the option of scaling out cannot be ignored (for purely fiscal reasons). I can see some interesting design discussions coming my way in the near future, and that’s a good thing.

Friday Reading 2016-11-18

Another week almost over so in-between daydreaming about relaxing this weekend I’ll be reading…

https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/
SQL Server 2016 SP1 has been released. If you haven’t read this blog post yet, have a look at the first point when it details what new in SP1

https://www.microsoft.com/en-us/sql-server/sql-server-vnext-including-Linux#resources
SQL Server vNext is out for CTP. This includes SQL for Linux. Grab a copy of Ubuntu 16.04 and get installing!

SQL Server Tail Of Log Caching on NVDIMM
Bob(?) talks about the ability of SQL Server 2016 to use non-volatile memory to cache the tail of the log file (look at the IOPS!)

Microsoft SQL Server Team AMA
Dear Microsoft, where the heck is the dark theme for SSMS???? Seriously…

Code I’m still ashamed of
Bill Sourour talks about code he wrote as a junior developer and the grim functionality behind it

How we make money at StackOverflow
Nick Craver explains how Stack Overflow makes money and how they maintain their business ethics

The tale of the database with no indexes
Devon Leann talks about her strategy for dealing with a database that has no indexes (favourite line “…nary an index was present…” ha!)

SQL Server & Containers – Part One

Containers are a hot topic at the moment, there seems to be a new article about them on Hacker News every day and now that Microsoft is supporting containers on Windows Server 2016 I thought it was time to have a look for what that means for us DBAs.

The question is can SQL Server run in a container?

Well, yes! SQL Server can run in containers. I have to admit that the ability to quickly create new instances of SQL Server without having to run through a lengthy (no matter how much you automate it, come on…it’s quite lengthy) install process is very appealing. This would be very useful for dev/qa environments where they need new instances spun up on a regular basis.

So how do you create a SQL container?

The first thing to do is get yourself a copy of Windows Server 2016, install it in a dev environment and get it fully patched. Btw Windows Updates are no longer located in the Control Panel options in Windows Server 2016, go to Settings > Update & Security (same as Windows 10 wouldn’t you know?).

For simplicity I’m going to be using an installation of Windows Server 2016 with the Desktop. You can do this on a core installation but as i’m used to a GUI, I’ll use the GUI.

WARNING! For some reason certain commands fail when copying and pasting. If a command fails try typing it out manually and re-running.

Now your server is fully patched you need to enable the Containers feature: –

server2016_enablecontainers2

Then once that’s done the Docker engine needs to be installed. Open up an administrative powershell prompt and run the following: –

Install-PackageProvider -Name NuGet -MinimumVersion 2.8.5.201 -Force
Install-Module -Name DockerMsftProvider -Force
Install-Package -Name docker -ProviderName DockerMsftProvider -Force
Restart-Computer -Force

Code source – https://blog.docker.com/2016/09/build-your-first-docker-windows-server-container/

Once the server has restarted you will have the Docker Engine running as a service. This can be verified by running:-

docker version

Now it’s time to build a container that’s running SQL Server 2016 (I’m going to use the SQL 2016 Express Edition image for this demo). Docker requires images to build containers so first thing is to get the image: –

docker pull microsoft/mssql-server-2016-express-windows

getsqlimage

Once this is complete you can view the image downloaded:-

docker images

dockerimages

Now we can create a container by running:-

docker run -d -p 15789:1433 ––env sa_password=Testing11 microsoft/mssql-server-2016-express-windows

N.B.- Notice the values after the -p flag. What this is doing is allowing SQL to listen on port 1433 (the default) in the container but be available on the host server on port 15789.

When that completes, you can view details of the new container by running:-

docker ps

dockercontainers

So now we have a container running SQL Server 2016, but how on earth are we going to connect to it?

Well there’s different ways of connecting depending where you are connecting from. If connecting locally you need to find the private IP address assigned to the container. This can be found by running:-

docker inspect –format='{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}’ 6dc

containerip
N.B. – You don’t have to type out the entire ID to refer to a container, the first three digits are all that’s needed as I’ve done above with 6dc

So my container has a private IP address of 172.30.47.32 on the host server. To connect via SSMS I just enter 172.30.47.32,1433 into the connection, enter the user sa & password and boom! I’m in:-

connectedcontainerlocal

But what about connecting remotely? This isn’t going to be much use if we can’t remotely connect!

Actually connecting remotely is the same as connecting to a named instance. You just use the server’s IP address (not the containers private IP) and the non-default port that we specified when creating the container (remember to allow access to the port in the firewall).
Easy, eh?

Hmmm, I imagine you’re saying to yourself. That’s all well and good but it’s a bit involved and I don’t really see what benefit I’m going to get from setting this up. Well, don’t worry, I’ll cover actually using the software and what benefits it brings in Part Two.

Monday Coffee 2016-11-14

Communication in the workplace.

With the release of Microsoft Teams we now have a dazzlingly array of software designed to increase communication between workers and departments in the workplace. There’s the old staple that is email, instant messengers such as Skype for Business and now relatively new systems such as Teams, Yammer or Slack.

I have to admit that I’m kinda on the Slack fanboy bandwagon. I find it a much better tool than Skype for Business mainly due to the fact that I find it less intrusive in my daily work. When someone sends me a message on Skype I find that I’m obliged to respond immediately whereas with Slack I feel I have the option to finish what I’m doing and then get back to whoever messaged me. Anyone else feel like that or am I on my own in that way of thinking?

Another feature that’s helped me out a lot is the ability to send messages to an entire channel. Before I would send an email to a certain department’s group address and wait for a response but now I can post in a channel and (hopefully) someone will get back to me.

Slack’s big problem will be Teams. A lot of companies will already be paying for Office 365 which Teams will come with, so why pay extra for Slack? I’ve had a brief look at Teams and the functionality that’s there is pretty much exactly the same as Slack so I think it’s a no-brainer that companies will prefer using Teams. Or…will they though? Slack’s user interface is a lot better than Teams (what’s with the purple?) and Slack also has the ability for me to be logged into different sites in the same app.

For instance, I have my work’s Slack but I’m also logged into the SQL Server & Irish Tech communities. This has given me access to a huge online tech presence who I can talk to and draw resources from. So I don’t really want to give that up, I guess in the end I’ll end up having email, Slack, Skype for Business and Teams open whilst working. Ah well…could be worse.

Friday Reading 2016-11-11

It’s Friday so no releases to Production today (ha!) which means in-between my code reviews, server audits and other tasks I’ll be reading the following…

Speaking? You? Go on.
Rob Sewell talks about why you should be presenting and gives his tips.

Public Speaking: A Primer
Paul Randal with a pretty detailed post on tips for presenting.

Build And Run Your First Docker Windows Server Container
Containers are a hot topic at the moment, this article guides you through creating a docker Windows container on Windows 10 and Windows Server 2016.

Edge running in a VM
Anyone out there actually using Edge? Well, here’s some (oldish) news.

Sega Genesis returns to production — in Brazil
The Sega Genesis (or Mega Drive for us Brits) has apparently been resurrected in Brazil! Bit of a blast from the past.

That’s it from me, have a good weekend.

Drag & Drop Table & Column Names in SSMS

I was working with a developer the other day and he was typing out each table name and all the column names he needed when working in Management Studio. He didn’t know that you can drag a table or column from object explorer into the query window and thinking about it, I didn’t know for ages when I first started with SQL.

It seems obvious but if you haven’t seen it before then how would you know that you could do that? Maybe you’d work it out but I thought I’d write this quick post to show anyone out there who wasn’t aware of this. What’s also really cool is that you can drag the columns folder from object explorer into the query window and it’ll drop all the columns from the table. So much better than typing out all the column names (even with intellisense) or using SELECT *

gif4