Posted on

SQL Server 2017 CU6 … I shall not fear planned obsolescence.

This past week Microsoft released SQL Server 2017 CU6, Microsoft Support. So, I thought it would be a good idea to set up my Linux SQL Server to follow the same automated WSUS-driven patch methodology as we do on some of our Windows SQL Server installations. Some of these servers apply Microsoft updates on a weekly scheduled maintenance window on Sundays. For these servers, it’s a little extra effort to closely monitor them through the maintenance window, and makes me nervous about Monday morning phone calls. However, it’s a good feeling to know these databases are running the latest patches, and Microsoft recommends “ongoing, proactive installation of CUs as they become available.”

My first Linux version of this scheduled maintenance window for SQL Server is just a cron job running apt-get commands. I’m going to follow up with my Linux colleagues on other patch maintenance approaches and will come back to edit this post with any better ideas, or likewise I’ll edit the post if I’m missing something important.

Patching SQL Server on Linux is super simple to do because the apt-get repository for Microsoft was added during the original installation Quickstart: Install SQL Server and create a database on Ubuntu. It’s literally just two simple apt-get commands:

sudo apt-get update
sudo apt-get install mssql-server

To repeat this in the weekly Sunday maintenance window I’m just using this cron job:

#
# m h dom mon dow command
0 13 * * sun /usr/local/scripts/sun-maint-win-sqlserver.sh

The bash script looks like this: Continue reading “SQL Server 2017 CU6 … I shall not fear planned obsolescence.”

Posted on

Presentation: Three SQL Server Always Encrypted Findings.

On Friday I gave a presentation at the April meeting of the newly created Santa Fe SQL Server User Group santafesql.org.

I was a little nervous since I haven’t given a presentation since a DOE conference in Amarillo, TX in 1991. But in spite of my nerves, it went OK since it was a short 30-minute talk and since we had a great group of attendees. The group was patient with a couple technical difficulties and interacted well when I lost focus doing things like shifting back from slides to demos.

My three findings were amazement, horror, and satisfaction.

The first finding, amazement, was the subject of my last blog post. SQL Server’s ability to flawlessly upgrade an Always Encrypted database over an upgrade and a platform shift to Linux is amazing. I covered it more thoroughly in the blog post than I did in the presentation Taking SQL Server Always Encrypted on a road trip.

I had some pretty serious technical issues in my presentation.  Continue reading “Presentation: Three SQL Server Always Encrypted Findings.”

Posted on

Taking SQL Server Always Encrypted on a road trip.

In the last post, I built out a SQL Server 2017 instance on a Linux host at Linode.com.

The first thing I wanted to try out was SQL Server Always Encrypted (AE). I had used AE in the past on an application and wanted to see if it still worked the same. I was also really curious to see how AE would survive migrations. So, I took it on a road trip to California. I decided to take a SQL Server 2016 database, encrypt a column with AE, restore and upgrade it into a SQL Server 2017 SQL, then restore and “platform-shift” it into a SQL Server 2017 on Linux at the Linode datacenter in Fremont, CA. I expected pain. But the pain never came. Here’s what I discovered.

I’ll skip most of the details of the SQL Server Always Encrypted setup since several others have already documented it well. A guide that I found easy to understand is here: exploration-of-sql-server-2016-always-encrypted-part-1

I was skeptical about AE support on Linux, so I tested some things first on Linux before the migration. Exporting the private key in the *.pfx file was a little tricker than I remembered. That certificate can hide in the certmgr depending on which account you use on SSMS to generate the Column Master Key and the Column Encryption Key. In my case, I created the keys while running SSMS in a privileged account. What finally worked for me was to run a command prompt as the same privileged user and then run certmgr /r currentUser to extract the *.pfx file.

Continue reading “Taking SQL Server Always Encrypted on a road trip.”

Posted on

Spinning up SQL Server on Linux

So I spun up another Linode instance for a SQL Server 2017 installation. I’ve had great success with six other linodes at Linode.com for a Cassandra cluster in the past, and so far this one has been just as good. The UI is very intuitive, and their price plans fit my budget … don’t need to sell body parts or sacrifice my privacy. Here is what their console looks like:

I’m not a stranger to Linux, but I started with this guide on digitalocean which worked good:   initial-server-setup-with-ubuntu-16-04

The guide includes ufw commands for allowing ssh through it’s firewall. Note that I’m not opening any SQL Server ports and I’ll be tunneling SQL Server ports inside of ssh. This should help minimize the chance of this server becoming a slammer host.

I also tried some sneaky stuff to get SQL Server to install on a really cheap 1GB host with mount -t tmpfs to sneak around the minimum RAM specs, but I wound up crashing the host:


Out of memory: Kill process 18260 ((sd-pam)) score 1 or sacrifice child
Killed process 18260 ((sd-pam)) total-vm:61940kB, anon-rss:1692kB, file-rss:0kBB
 Continue reading “Spinning up SQL Server on Linux”