Containers: Working With Volumes

In the previous two posts on containers I showed how use Docker commands to get an image and create a container. This time, we’re going to create a container again, but, we’re also going to create a volume so we can do some fun stuff.

For an understanding of why I’m doing a series of blog posts on containers, read here.

Docker Volumes

You can create a container with a volume, or local, persistent storage. The usage is really simple:

docker run -e 'ACCEPT_EULA=Y' `
-e 'SA_PASSWORD=$cthulhu1988' `
-p 1450:1433 `
--name DockerDemo17vol `
-v sqlvol:/var/opt/mssql `
-d mcr.microsoft.com/mssql/server:2017-latest

This will create and kick off a new container based on SQL Server 2017. Nothing to it really. If you get the IP address for the machine, you can connect to it through Azure Data Studio or SQL Server Management Studio. Just be clear, I used a port, 1450, that’s non-standard (I have several containers running now, so I moved this one to a different port).

Connected to the instance of SQL Server within this container, I run the following T-SQL script:

use master;
go
create database TestingVolume;
GO

use TestingVolume;
GO

CREATE TABLE dbo.TestTable
(ID int primary key not null identity(1,1),
MyVals varchar(200));
GO

insert dbo.TestTable
(MyVals)
SELECT @@VERSION;


select @@VERSION,
t.MyVals
from dbo.TestTable as t;

This will show that I’m on version 2017, as expected. Let’s have some fun. First, we’ll stop the container:

docker stop DockerDemo17vol

Now, let’s create a new container, but, let’s use the same volume:

docker run -e 'ACCEPT_EULA=Y' `
    -e 'SA_PASSWORD=$cthulhu1988' `
    -p 1450:1433 `
    --name DockerDemo19 `
    -v sqlvol:/var/opt/mssql `
    -d mcr.microsoft.com/mssql/server:2019-CTP2.5-ubuntu

What happens next is marvelous. This upgrades the databases on my volume. It may take a second or two to complete, but you can go back and re-run the SELECT statement above and the output looks like this:

Easiest SQL Server upgrade I’ve ever had.

Stop the new container and start the old one. Or rather, try to start. It will error out. To see the error, run this command:

docker logs 8d79c80ff4b7

Substitute your own Container ID of course. You’ll see that SQL Server failed to start because it doesn’t support a downgrade process.

Removing containers does not remove the volume. That’s persisted. To remove the volume, you send this command:

docker volume rm sqlvol

Just make sure your containers are stopped first, or you will get an error

Conclusion

As you can see, there is a lot to containers. The ability to control the storage opens up a slew of possibilities. Just don’t forget that the rules of SQL Server still apply.

Tomorrow, we’ll create a custom container of our own.

---

I love to share this kind of information. I have a few opportunities coming up where I can spend all day with you. We can talk query tuning or DevOps. Follow the links for the details:

SQLSaturday Columbus Precon, June 7 2019, Columbus OH
SQLSaturday Indianapolis Precon, Friday August 16th, 2019. Click here now to register.
SQLSaturday Oslos Precon, Friday August 30th, 2019. Click here to register.