A Google Home Mini finds life as a Hotel Concierge.

Today’s post doesn’t have a lot to do with databases. But it relates to a recent SQL Saturday. What follows is the summary of a two week adventure of training a Google Home Mini to become a hotel concierge.

SQLSaturday

I was lucky to receive a winning ticket at the raffle at the recent Albuquerque SQL Saturday on May 5th. Jamey Johnston stepped up like many other volunteers and presenters to donate a Google Home Mini[1] for the raffle. Ironically I had just been pricing them this month so I could give one to a hotel where I moonlight. I missed Jamey’s session but I heard from a friend that he gave an amazing presentation.

The goal.

So, six of us work the front desk during the week at the hotel where I moonlight. Hospitality happens to be a good side gig in Santa Fe since the only work in town centers around government and tourism, there’s not much tech work here. Hotel work is good because every day seems different and I enjoy the challege of helping guest’s love their stay in Santa Fe and sharing people’s travel stories with the same passion that I enjoy my own travels.

At the front desk we are asked many questions while multitasking with the telephone and the reservation systems. Many of the questions we answer follow a repeated theme: “How far is it to Denver?” “The Grand Canyon?” “Phoenix?” “How long will that take?” “What is the phone number for Tecolote?” “Enterprise Car Rental?” To be able to answer these questions simultaneously as we’re clicking our way through the reservation systems is priceless.

With this goal in mind I set out over four weekend shifts to train the little gadget for concierge duties.

Continue reading “A Google Home Mini finds life as a Hotel Concierge.”

Creating a Paessler PRTG sensor for SQL Server Agent Job monitoring

This week it was time to integrate SQL Server job failure detection with our SNMP server across all our SQL Server instances. PRTG Network Monitor is an SNMP server by Paessler. It is comparable to HP Openview, Cacti, Spiceworks, Solarwinds, Datadog and others. I really love this tool, even though it wakes me up in the middle of the night 😉 A Cisco network engineer at ANM, https://anm.com/, installed it and trained us. Their NOC in Albuquerque used it to support servers and workstations for their customers. The typical use case for SNMP is for switches and routers. I’ve come to find it priceless for multi-tier application troubleshooting, creating a single pane of glass for locating issues in our enterprise. I’ll outline how to add this sensor. The end result looks like this:

Continue reading “Creating a Paessler PRTG sensor for SQL Server Agent Job monitoring”

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.”

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 interected 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 thouroughly 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.”

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.”

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 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”