Containers and Databases

Question

Containers and Databases

Viewing 10 posts - 16 through 24 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Brendan Mason SSC-Addicted Points: 497 More actions · Answer 1

This is a bit of an edge case, but I've worked with applications from crappy vendors that didn't support installation on a named instance and "required" sysadmin permissions, but weren't big enough apps to justify the expense of a dedicated SQL Server. With containers, you can give those types of applications isolated default instances on a shared server.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 2

alpinetrout - Wednesday, March 1, 2017 4:28 PM
This is a bit of an edge case, but I've worked with applications from crappy vendors that didn't support installation on a named instance and "required" sysadmin permissions, but weren't big enough apps to justify the expense of a dedicated SQL Server. With containers, you can give those types of applications isolated default instances on a shared server.

Now that's a good reason, though wouldn't a VM suffice here? Or is there a licensing issue?

pauls 72822 Ten Centuries Points: 1049 More actions · Answer 3

Each VM requires a license . . . even if it's a Windows and VMware there is cost. A core reason for using containers is simplicityand economy. . . fewer VMs, fewer VMs to maintain, and better use of server/host compute resources.

oridonner Grasshopper Points: 17 More actions · Answer 4

I would like to share some of my thoughts on this issue and get your opinion:
Containers were designed to deal with micro-services and stateless applications. SQL Server is not a stateless application, neither a micro-service (it is a huge monolith in fact). This is the reason, in my opinion, why, intuitively, we don't think about containers as a relevant solution for databases (in production).

SQL Server can be thought as a micro-service? As a stateless application? And I'm thinking loud here if you allow me.

The OS (Windows/Linux) is stateless, the service itself (SQL Server) is stateless, even the schema of the database application is stateless (in case you are working with DACPAC and BCP). Only the data is not (to some extent?). So if we separate the data from the application, we can use containers to source code the whole environment and mount/add the data (Windocks is doing it great for windows).

And please, let me take it a step further; Can data be treated as stateless? To some extent? In the range of the Transaction Log maybe?

Using an external transaction log (i.e., Kafka), it would be possible to build the data, valid for a chosen date in the range of the transaction log, as a container image. Mounting/adding the basic data image (the stateful part of the data) and updating it according to the transaction log (the stateless part of the data).

Gary Varga SSC Guru Points: 82166 More actions · Answer 5

We must also remember that many technologies have been successfully re-appropriated for other uses. One example that springs to mind is that Java was originally called Oak and was a language designed for embedded systems.

I am ignoring whether any of us like Java or what it has become or even where it is going but I think we can safely call it successful.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!

Brendan Mason SSC-Addicted Points: 497 More actions · Answer 6

Steve Jones - SSC Editor - Wednesday, March 1, 2017 5:43 PM
alpinetrout - Wednesday, March 1, 2017 4:28 PM
This is a bit of an edge case, but I've worked with applications from crappy vendors that didn't support installation on a named instance and "required" sysadmin permissions, but weren't big enough apps to justify the expense of a dedicated SQL Server. With containers, you can give those types of applications isolated default instances on a shared server.
Now that's a good reason, though wouldn't a VM suffice here? Or is there a licensing issue?

That's another option for sure, but does incur more overhead and possibly additional licensing depending on the environment. Containers can provide that isolation even on a lone physical server, which could be appealing for some scenarios. For example, a small business with a small on-prem footprint of only a few non-virtualized servers might have a need to roll out some new software that they could now isolate on their existing infrastructure without having to buy servers or virtualization software, or deal with setting up a hybrid cloud environment. I'm not really heavily for or against either option, I just like the idea of having more options at my disposal.

pauls 72822 Ten Centuries Points: 1049 More actions · Answer 7

In reply to Ori. Docker containers are designed to support emphemeral and stateless apps, each has a private file system and any data in the private file system is lost when the container is deleted. So, following the "best practice" of deleting and replacing containers is a poor fit for any app that requires data persistence. So, let's explore how SQL Server containers conform or are flexible to support different models:

While SQL Server containers include a private file system, and are created with Docker commands and are "containers" in a true sense. They can be deleted and replaced in seconds. They are ALSO full fledged SQL Server instances that are instantiated quickly. As a full SQL Server instance here are no restrictions from attaching databases that reside on the host, or are network or SAN attached. These instances can also be patched and maintained as any other SQL Server instance, so there is no technical reason why they are *less* capable of persistence data than other SQL Server instances. Use of the private container file system is fully optional.

So, SQL Server "containers" is an accurate description, but may not be the most useful. These are simply SQL Server instances that are created and delivered in seconds, usng Docker client commands (and with PowerShell, and soon with a new Web UI). Our thouands of users think they are great for Dev and QA, and are well suited for short-lived DR testing as well. I think over time they will find their way in varied production use-cases as well.

rustprooflabs SSC-Addicted Points: 404 More actions · Answer 8

Regarding data persistence, it's common to use the `-v` flag when running Docker containers. This attaches a volume to the container at a specific location. Putting data files in a defined location on the local server and having the container link to those allows the database service and data persistence to be separated. When the container is deleted, the paths on the local machine still exist and a new container can be linked to it again in the future. I haven't tried this with SQL Server specifically, but in theory it seems like it should work.

pauls 72822 Ten Centuries Points: 1049 More actions · Answer 9

Your summary is spot on Ryan. The Windocks implementation uses a MOUNTDB command in the Dockerfile to do the same.

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 10

alpinetrout - Thursday, March 2, 2017 9:13 AM
Steve Jones - SSC Editor - Wednesday, March 1, 2017 5:43 PM
alpinetrout - Wednesday, March 1, 2017 4:28 PM
This is a bit of an edge case, but I've worked with applications from crappy vendors that didn't support installation on a named instance and "required" sysadmin permissions, but weren't big enough apps to justify the expense of a dedicated SQL Server. With containers, you can give those types of applications isolated default instances on a shared server.
Now that's a good reason, though wouldn't a VM suffice here? Or is there a licensing issue?
That's another option for sure, but does incur more overhead and possibly additional licensing depending on the environment. Containers can provide that isolation even on a lone physical server, which could be appealing for some scenarios. For example, a small business with a small on-prem footprint of only a few non-virtualized servers might have a need to roll out some new software that they could now isolate on their existing infrastructure without having to buy servers or virtualization software, or deal with setting up a hybrid cloud environment. I'm not really heavily for or against either option, I just like the idea of having more options at my disposal.

I tend to agree. I'm not sure I like the idea of SQL in a container (prod) as the only app on a server, even small. Or if there are two instances, VMs seem to be a better choice, but I'm sure there are cases where this isn't true. One place for sure that I like SQL in a container is an Express app sent to lots of people. Rather than any install, or dealing with Compact/LocalDB in process, use a container. That makes sense, as does the idea of having options.