The application process within most Docker containers is running as a root user meaning the process has root privileges within the container user space. The root user within the container is also the same root (uid 0) on the host machine, and if the user can break out of the container, they would have root permissions on the host. Running as root is convenient for development, testing and CI/CD use cases but for production use cases, it is safest to run SQL Server as a non-root process within the container. In this blog, we’re going to share with you how you can preview this upcoming improvement by creating your own non-root SQL Server container.
Vin has a quick demonstration of how it works.
We’re attaching to an already running docker container running SQL. But what we get is an idle SQL Server process this is great if we have a running workload we want to analyze but my goal for all of this is to see how SQL Server starts up and this isn’t going to cut it.
My next attempt was to stop the sql19 container and quickly start the strace container but the strace container still missed events at the startup of the sql19 container. So I needed a better way.
Don’t worry—Anthony finds a better way.
Sys.dm_os_ring_buffers DMV has been a key DMV used for monitoring SQL Server by built-in tools as well as third party monitoring utilities. When SQL Server 2017 was released on Linux, unfortunately this DMV did not return correct CPU usage information by SQL Server process. SQL Server team is glad to announce that the starting with SQL Server 2019 release candidate, the sys.dm_os_ring_buffers DMV returns SQL CPU utilization correctly. This improvement should benefit the SQL Server monitoring ecosystem on Linux by providing a way to monitor SQL Server CPU usage and enable decision making to engage corrective action if required.
This brings it in line with what we have on Windows.
In this article, we will discuss to deploy SQL Server Merge Replication on Linux environment. Many SQL Server features are not available on Linux by Microsoft but the Replication feature exists for Linux Environment Edition. Before starting anything, let’s address this question – what is Merge Replication?
Merge Replication is a data synchronization process with one database (Publisher) to other databases (Subscriber) and vice versa. The data synchronization audit will be controlled by the distributor (Distribution database). The distributor database will manage the data synchronization between Publisher and Subscribers.
Merge replication is also an unending nightmare of pain, but you do what you gotta do.
Another key point is that even though SQL Server on Linux is supported on various distributions SQL Server can be installed on other distributions instead.
Although I would only advise this for testing purposes only because SQL Server would not be supported by Microsoft on these other distributions.
In reality, you can probably install SQL Server on more diverse distributions than the ones listed. However, I have focused on the main ones below.
As Kevin points out, there’s a difference between “working” and “supported.” If you’re futzing about with a dev database or trying to learn the platform, go ahead and install it on Elementary or some other unsupported distro. But if you have a production issue, the fact that you installed SQL Server on an ancient version of Slackware may win you plaudits but won’t get you support.
With SQL Server 2017, a new era was heralded with SQL server being available to deploy on Linux (and Linux based container) systems. While all functionality of the SQL Server engine were brought over as is to SQL Server on Linux, some of the functionality which depended on Windows system processes such as distributed transactions (which relies on MSDTC service) were not brought over immediately.
Well, now your wait is over.
I’m going to admit, that the reason I didn’t embrace Powershell at first, was most of the examples I found were of full of hardcoded values. I found it incredibly obtuse, but I started to realize that it came from many sources who might not have the scripting history that those of other shells, (this was just my theory, not a lot of evidence to prove on this one, so keep that in mind…) As Powershell scripts have matured, I’ve noticed how many are starting to build them with more dynamic values and advance scripting options, and with this, I’ve become more comfortable with Powershell.
I think the best way to learn is to see real examples, so let’s demonstrate.
Read on for those examples.
First, look under the release documentation and search for the deb package. In my case I’m install the amd64 version.
Then, right-click on the “powershell-preview_7.0.0-preview.2-1.ubuntu.18.04_amd64.deb”, and select “Copy link address“.
This is also something you could script out. It’s quite useful in scenarios where your apt repositories don’t have the latest version of something but you still need or want it.
I noticed a common theme in how easy it is to install SQL on Linux that tells me if you didn’t think you had time to install SQL on Linux then you probably do and should give a try in the near future.
Thanks for all that participated!
Let me put it this way: one of my employees, who had never really worked with SQL Server before, got SQL Server on Linux installed through Docker in about 15 minutes. It’s really easy to do.
This will fail before the query really even gets out of the gate. Why? The proc xp_create_subdir cannot create the directory because it requires elevated permissions. The fix for that is easy enough – grant permissions to write to the Database directory after creating it while in sudo mode. I will get to that in just a bit. Let’s see what the errors would look like for now.
Msg 22048, Level 16, State 1, Line 15
xp_create_subdir() returned error 5, ‘Access is denied.’
Msg 25602, Level 17, State 23, Line 36
The target, “5B2DA06D-898A-43C8-9309-39BBBE93EBBD.package0.event_file”, encountered a configuration error during initialization. Object cannot be added to the event session. The operating system returned error 5: ‘Access is denied.
‘ while creating the file ‘C:\Database\XE\PREEMPTIVE_OS_PIPEOPS_0_132072025269680000.xel’
Read on for the solution.