Voice of the DBA

Author: way0utwest

Rebooting Ubuntu Linux from the Command Line–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

Since SQL Server 2017 is now out, and Linux is an option, I thought I’d write a bit more about little Linux things that admins might want to know.

After installing an update, I neede to reboot my Ubuntu install. Apparently there was a dialog, but it was hidden. Unlike on Windows, I didn’t see an easy way to click a button and restart. However, since I had a terminal shell open, I was sure there was an easy way to do this.

I could have run a quick search, but I fell back to the old standby:

man shutdown

This showed that there is a shutdown, as on Windows, with the same –r parameter. This needs sudo to run, and by default it reboots after a minute. The command I ran was:

shutdown -r

Learned something, which I should have known. I almost typed shutdown, but I decided to double check.

Note: I realized later that there is a small menu in the upper right. I had the VM in a window and didn’t notice the icon. Clicking the gear gives a menu.

This lets you then choose shutdown or restart.

October 18, 2017
Software Patents

This editorial was originally published on Jan 30, 2014. It is being re-published as Steve is out of town.

I highly dislike the current patent system in the US. I think awarding patents for an idea like “1-click purchasing” is a poor practice that discourages innovation. There are numerous problems with our patent system, not the least of which is that it can stifle new ideas and prevent small companies from building on similar ideas that are, well, what I’d all common sense. I don’t quite share Richard Stallman’s view of software patents, but I do think we have fundamental flaws in the way we handle this intellectual property.

I do, however, think that software has value, and the work we do as software engineers deserves compensation, as noted in this post. We should have some patents, and we should reward those that create truly innovative, and not common sense, ideas in the digital world. How we define those, and how we measure innovation isn’t clear to me, but I do think that non-trivial ideas deserve protection.

Our legal systems around the world are slow to catch up to the digital world, and may never do so. As quickly as we can debate patents, copyright, trademarks, and more, the digital world grows, expands, and changes to stretch and challenge our thinking of how the world works. It almost seems as though digital ideas and software defy analogies and explanations that we can relate to the real world.

I don’t know how I’d change patents, but I don’t want them to go away completely. I do, however, know what I’d do with copyright. I’d change it back to the original system we had, granting 14 years exclusivity to creators, with a single option to renew for 14 years and that’s it. The world certainly changes too fast to wait 95 years to use someone else’s work.

October 18, 2017
Quick SQL Prompt Updates in a Pattern
I work for Redgate and write about products. I’ve got a series of SQL Prompt posts here on little things I like. SQL Prompt might be my favorite tool. SQL Prompt will be yours as well if you give it a try.

We had a customer post a question today on how they can built an update statement with a pattern. Specifically, they said that the code often looks like:
```
UPDATE dbo.Contacts
   SET 
   c.Salutation = @Salutation
, c.FirstName  = @FirstName
, c.MiddleName = @MiddleName
, c.LastName   = @LastName
, c.Suffix       = @Suffix
WHERE ContactID = @contactid
```
The table columns are the same name as a variable. That’s a good pattern, and I’d think SQL Prompt could handle that.

It doesn’t.

The column picker doesn’t work with Updates (logged w/ product team), and I can’t duplicate selected text over (also logged for discussion). However, I do have a workaround.

As I thought about it, I realized there are some features of Prompt that help here, and some of SSMS that will work.

I made a quick video of the process, but I’ll describe it below:

The Process

The first thing is to get a column list. ssf<tab> does for me. I’ll get the select statement for a table and then expand the list of columns with a tab when on the *.

Now, I’ll copy the columns. I tend to copy all since it’s usually easier to remove than pick and choose specific ones. I’ll wrap these in an update, which could be a snippet. If it’s not, that’s fine.

From here, I use the power of Shift+ALT. If you’ve never done this, it’s amazing. I use this to select the columns and copy them. Then I’ll CTRL+ALT to add the = and paste in the columns. I can then use CTRL+ALT once again to remove the alias and replace with a @.

And, of course, I can reformat to make it look nice with SQL Prompt. Give SQL Prompt a try today and see how it can improve coding and feel free to share your tips here.
October 17, 2017
The Cloud is Better In At Least One Way

Many people don’t see the cloud platform as necessarily better run than what they do in their local data center. That might be true, and certainly the caliber of people, their experience, responsiveness, and more can make a big difference to the way in which any environment operates. One would like to think that AWS, Azure, Google Compute, etc. would all hire the best people and pay them more to operate a top notch system. I’m sure they have some of those people, but they also may have some average people that might not be better than your administrators.

Corruption is something I’ve experienced a few times in my career, and it was incredibly scary each time. I’ve been on multi-day support calls, trying to recover data, exported out pages to reconstruct values, and had to have very difficult conversations with management and clients to let them know data is gone. I think the worst experiences for me were breaking the news to workers that would have to try and re-enter data. Many of them had lots of extra work to look forward to without extra pay.

The Microsoft Azure team takes corruption seriously, and there’s a good description in this post of how they protect against and deal with corruption in Azure SQL Database. It’s quite comprehensive, and it’s a set of things that I wouldn’t expect most companies to implement. Even some of the better SQL Server people I know don’t necessarily get called in during off hours when a corruption alert fires. Plenty of people working with SQL Server might not have been through an Immersion event and understand how to even deal with corruption outside of calling Microsoft.

The cloud is a vast array of service and settings, many of which we can manage ourselves. In those cases, certainly the cloud might not do a better job than we would. No one on a cloud vendor’s staff is going to manage your VM or help you ensure your database design is solid. However, in checking for corruption, ensuring disk level backups, watching for DDOS and other threat vectors, the cloud vendors certainly do a better job than most of us. And for corruption, Azure SQL Database seems better watched than most databases I’ve seen in my career.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.5MB) podcast or subscribe to the feed at iTunes and Libsyn.

October 16, 2017