Although these screenshots show SQL Server 2019 preview CTP 2.3, this also applies to SQL Server 2017 on 18.04.2, because that’s what I had installed before upgrading the SQL Server version. However, as my friend Jay Falck pointed out on Twitter, Microsoft has stated publicly that it is not yet certified for production use:
Important, this does not change the support state of SQL Server 2017 on Ubuntu 18.04. Work to certify Ubuntu 18.04 with SQL Server 2017 is planned and we will announce when it will be supported for production use on this page. Until such as an announcement occurs, SQL Server 2017 on Ubuntu 18.04 should be considered experimental and for non-production use only.
Read on for Randolph’s thoughts on the issue.
If you’re still on an earlier version of PSCore and are unable to install PSCore 6.2 right now, you can still download preview of the SqlServer module to get the latest fixes and new features. You just won’t be able to use the Invoke-Sqlcmd cmdlet.
Another quick thing to note is that this is like a v.0.0.1 of Invoke-SqlCmd on PSCore; it does not have all the bells & whistles of the version of Invoke-Sqlcmd for [full blown] Windows PowerShell. Obviously, more features will be added over time, but the basic functionality was ready to for customers to start “kicking the tires”.
Read on for more notes and the link to check this all out.
The “docker commit …” command, you’ll provide both the image-name (all lowercase) and a TAG name (uppercase allowed). You can be creative in having an naming conversion for you images repositories.
It’s very important to save images after doing the commit. I found out that having an active container would be useless without an image. As far as I know, I haven’t found a way to rebuild an image from an existing container if the image was previously removed.
Max has a full demo, including installing various tools and programs as well as tips on how to minimize the pain.
Microsoft has decent instructions on installing SQLCMD on
linux. My current company blocks access to external yum repositories, so I followed the “Offline” installation. There’s two packages to install:
msodbcsql– aka Microsoft’s ODBC driver for SQL Server
mssql-tools – SQLCMD
Read on for the step-by-step instructions.
The steps that need to be performed are:
– Allow the new port in the RHEL firewall
– Change the SSH daemon to listen on the new port
– Add an incoming rule in the VM network security group for the new port
– Remove the rule that allows port 22
The Ubuntu process will be pretty close to this as well.
In essence Kubespray is a bunch of Ansible playbooks; yaml file that specify what actions should take place against one or more machines specified in a hosts.ini file, this resides in what is known as an inventory. Of all the infrastructure as code tools available at the time of writing, Ansible is the most popular and has the greatest traction. Examples of playbooks produced by Microsoft can be found on GitHub for automating tasks in Azure and deploying SQL Server availability groups on Linux. The good news for anyone into PowerShell is that PowerShell modules can be installed via Ansible and PowerShell commands can be executed via Ansible. Also, there are people already using PowerShell desired state configuration with Ansible. Ansible’s popularity is down to the facts it is easy to pick up and agent-less because it relies on ssh, hence why one of the steps in this post includes the creation of keys for ssh. This free tutorial is highly recommended for anyone wishing to pick up Ansible.
Click through for a step-by-step tutorial.
Now this works a treat. It waits ten seconds for the SQL instance to come up within the container and then runs the sqlcmd script below.
The problem with this is, it’s a bit of a hack. The HEALTHCHECK command isn’t designed to run once, it’ll carry on running every 10 seconds once the container comes up…not great.
So, what’s the better way of doing it?
Andrew gives us a clear explanation of what’s going on and gives a shout out to Bob Ward’s SQL Server on Linux book.
So, if PowerShell Core isn’t available in the package repository, with a few steps you can download and install PowerShell. But, the first thing I do is to remove it before installing.
Ubuntu## - When PowerShell Core isn't available in their repository: (download and execute install) cd Downloads wget https://github.com/PowerShell/PowerShell/releases/download/v6.1.1/powershell_6.1.1-1.ubuntu.18.04_amd64.deb sudo dpkg -i powershell_6.1.1-1.ubuntu.18.04_amd64.deb ## - When available in Apt/Apt-Get repository: sudo apt install -y powershell #-> Or, powershell-preview
Click through for demos of CentOS (or any other yum-based system) and MacOS X.
Now in SQL Server 2017 with that 7GB program running would cause Linux to need to make room in physical memory for this process. Linux does this by swapping least recently used pages from memory out to disk. So under external memory pressure, let’s look at the SQL Server process’ memory allocations according to Linux. In the output below we see we still have a VmSize of around 10GB, but our VmRSS value has decreased dramatically. In fact, our VmRSS is now only 2.95GB. VmSwap has increased to 5.44GB. Wow, that’s a huge portion of the SQL Server process swapped to disk.
In SQL Server 2019, there’s a different outcome! In the data below we see our 16GB VmSize which won’t change much because of the virtual address space for the process. With that large external process running SQL Server reduced VmRSS from 7.9GB (from Table 1) to 2.8GB only placing about 4.68GB in the swap file. That doesn’t sound much better, does it? I thought SQL Server was going to react to the external memory pressure…let’s keep digging and ask SQL Server what it thinks about this.
Anthony is doing some great work digging into this. This is an area where you do have to understand the differences between Windows and Linux.
Let’s begin with discussing WHY it’s not a good idea to be root on a Linux host unless absolutely necessary to perform a specific task. Ask any DBA for DB Owner or SA privileges, and you will most likely receive an absolute “No” for the response. DBAs need to have the same respect for the host their database runs on. Windows hosts have significantly hardened user security by introducing enhancements and unique application users to enforce similar standards at the enterprise server level, and Linux has always been this way. To be perfectly blunt, the Docker image with SQL Server running as root is a choice that shows lacking investigation to what privileges are REQUIRED to run, manage and support an enterprise database. This is not how we’d want to implement it for customer use.
Unlike a Windows OS, the Linux kernel is exposed to the OS layer. There isn’t a registry that requires a reboot or has a safety mechanism to refuse deletion or write to files secured by the registry or library files. Linux ASSUMED if you are root or if you have permissions to a file/directory, you KNOW what you’re doing. Due to this, it’s even more important to have the least amount of privileges to perform any task required.
Proper deployment would have a unique MSSQL Linux login owning the SQL Server installation and a DBAGroup as the group vs. the current configuration of ROOT:ROOT owning everything. With all the enhancements to security, this is one area that as DBAs, we should request to have adhered to. Our databases should run as a unique user owning the bin files and database processes.
Much of this post is walking us through some basics of security, but it also includes helpful built-in commands unrelated to security, like
df to view free disk space.