Voice of the DBA

Author: way0utwest

It’s Time to Patch and Upgrade

I don’t want to be chicken little here, but the Meltdown/Spectre bugs have me concerned. I don’t know the scope of the vulnerabilities, as far as exploits go, but I do know the lax ways in which humans interact with machines, including running code, opening untrusted documents, and just making silly mistakes. No matter how careful you think you are, can you be sure everyone else in your organization is just as careful? Are you sure they won’t do something silly from a database server? Or do something from a server (or workstation) that has access to a database server? Or use a browser (yes, there’s an exploit)

PATCH your system, soon.

Vulernabilities in hardware are no joke, and even if you think you’re fairly safe, it’s silly to let this one go by and assume you won’t get hit. The advent of widely deployed scripting tools, botnets, and more mean that you never know what crazy mechanism might end up getting to your database server. Is it really worth allowing this when you can patch a system? This is a no brainer, a simple decision. Just schedule the patches. With all the news and media, I’m sure you can get some downtime approved in the next few weeks. After all, your management wouldn’t want to explain to their customers any data loss from this any more than you’d want to explain it to your boss.

We’ve got a page at SQLServerCentral that summarizes the links I’ve found for information, patches, etc. I’m sure things will change rapidly, and I’ll update the article as I get more information. The important things to note are that not all OSes have patches yet, and there are situations where you might not need to change anything. That’s good, as there are some preliminary reports of patches causing issues with performance (degrading it) for PostgreSQL And MongoDB systems. I did see this tweet about no effects on SQL Server, which is good, but YMMV.

Most of us know patching matters, and we need to do it periodically (even if it’s a pain), however, many of you are like me in that you rarely upgrade systems. Once they work, and because I have plenty of other tasks, I don’t look to necessarily upgrade a database platform for years. One downside to that is that a major vulnerability like the Meltdown/Spectre attacks is that patches likely won’t come out for old system and versions of SQL Server. That is the case here.

That means that if you’re on SQL 2005-, or even on older Windows OSes, you might really consider planning an upgrade. Even if you aren’t overly worried about this exploit, you won’t want a vulnerability to live for a long time in your environment. You never know when a firewall will change, server will move, or some malware will slip through (did I mention the browser exploit?). Plan on an upgrade. I’ve started asking about accelerating our upgrade plans, and you might think about that as well. I know management doesn’t want to spend money unneceesarily, but this feels necessary, and a good time to refresh your system to a supported version.

In general I like to delay my patches slightly from the world and not be on the bleeding edge. That’s fine, but don’t wait too long with this one. I would hope that most people get systems patched in the next month. If not, don’t expect any sympathy if you lose data.

Steve Jones

The Voice of the DBA Podcast

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

January 5, 2018
Advent of Code 2017 Day 1–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I like the Advent of Code. I stumbled on the site a few years ago and enjoyed working through some of the challenges in 2015. I tried solving them with three languages (Python, PoSh, and T-SQL) but only managed to get through about half the items before life got busy. Last year I was worn out and too busy to mess with the code.

This year I was busy in December when the puzzles came out, but decided to work through the puzzles again as a break from work and life, and to keep my mind flexible. I’ll do a series here, hopefully all 25, but here’s my view of Day 1.

Part 1

Each puzzle has two parts, and you need to solve part 1 to get part 2. In this case I need to take a list of input and compare each digit to the next digit and see if they match. If they do, then you add that digit to your sum.

This sounds like a perfect case to use the LEAD/LAG functions in SQL Server, so I did that. Since I get a long list of input, I also decided to use a string splitter. Here’s the first part where I split the string:
```
DECLARE @i VARCHAR(5000);
 --SET @i  = '1122';
 --SET @i  = '951344679963668529';

WITH myTally(n)
 AS
 (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) d(n)
 )
 , cteSplit (i)
 AS
 ( SELECT
 i = SUBSTRING(@i, n, 1)
 FROM myTally
 WHERE n <= LEN(@i)
 )

select i

from cteSplit
```
If you run this, you’ll get your @i string out a character at a time. This get’s me the data in a set of rows, similar to an array.

Now I need to compare each row with the next one, and I’ll use the LEAD function. My data is ordered, so I don’t need an order. That means I’m looking at this:
```
LEAD(i, 1) OVER (ORDER BY (SELECT NULL))
```
I’ll compare that with the current value and if they match, I’ll return the value. If not, I return a zero, adding nothing to the sum.

There is one special case. The list is circular, so if the last digit (when we get there) matches the first digit, I need to include that. I’ll add that as special FIRST_VALUE, LAST_VALUE function match. Here’s my code:
```
WITH myTally(n)
 AS
 (SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
 FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
 CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) d(n)
 )
 , cteSplit (i)
 AS
 ( SELECT
 i = SUBSTRING(@i, n, 1)
 FROM myTally
 WHERE n <= LEN(@i)
 )
 , datacte(j)
 AS
 (
 SELECT j = CASE WHEN i = LEAD(i, 1) OVER (ORDER BY (SELECT NULL))
 THEN cteSplit.i
 ELSE 0
 END
 FROM cteSplit
 UNION all
 SELECT TOP 1 j = CASE WHEN FIRST_VALUE(i) OVER (ORDER BY (SELECT NULL)) = LAST_VALUE(i) OVER (ORDER BY (SELECT NULL))
 THEN i
 ELSE 0
 end
 FROM cteSplit
 )
 SELECT SUM(j) FROM datacte
```
When I do this with the input string, I get a result. It happened to be right

Part II

Part II is a variation of Part I. Instead of looking at the next digit, I need to look halfway around the list. The list is an even number, so if it’s 10 digits long, I need to start with char 1 and look at char 6. If they are equal, add to sum. Then look at 2 and 7, and repeat. Since it’s a circular list, when I get to char 5, I look around to char 1. There’s an optimization here, but when I solved this, I decided to take an easy way out.

I first considered adding the first half of the list to the end and only running through the first len(input) characters. Then I thought, I could easily do a LAG. So, I modified my datacte query to do this:
```
, datacte(j, k, i, l, n)
 AS
 (
 SELECT j = CASE WHEN i = LEAD(i, @len) OVER (ORDER BY (SELECT NULL)) AND n <= @len
 THEN cteSplit.i
 ELSE 0
 END
 ,
 k = CASE WHEN i = LAG(i, @len) OVER (ORDER BY (SELECT NULL)) AND n > @len
 THEN cteSplit.i
 ELSE 0
 END

)
```
This quickly counts the matches up to the middle and then counts the next bunch.

There’s a better way, but I’ll leave you to figure that out in the comments.

In any case, I solved both of day 1. Now on to day 2 during the next break.
January 4, 2018
Using Better Tools

There are good tools available to help you work with your SQL Server database or build better applications. Microsoft has built some, most of which I think are basic and not great, but plenty of third parties have offered products in the Microsoft ecosystem that can help you build better systems. I work for a software vendor (Redgate Software). We build all sorts of tools to help you work with SQL Server. I enjoy working for Redgate because I think we build some great software that’s valuable, and I hope you check us out. Most of our our utilities cost money, but we have some cool, free tools, like SQL Search and DLM Dashboard.

I get that some organizations don’t have the budget for third party tools. That’s too bad, but there are some good tools out there, and I think many of us vendors do provide value in saving you time and effort in working with the Microsoft platform. If that’s worth the cost, you should consider using tools. At the very least, you should be aware of the free and paid extras out there and consider the ones that help you.

What I find strange is that some orgs don’t allow any third party tools, free or paid, because they don’t come from Microsoft. They may even disallow utilities like sp_Blitz, sp_whoisActive, and the dbatools project. What I don’t understand is why there is a blanket ban on software from companies other than Microsoft? How can you not take advantage of these tools? I get that many companies might not want a developer or DBA installing some random software on their system whenever they want. There are good reasons to not do that, but there are also good reasons to test and use actual code that is useful, even if produced by someone else.

What’s also interesting is that rarely find an issue with Ola’s backup scripts, so why would Minion Backup or these other tools be different? After all, most of these are open source, so you can see the code. It’s really no different than the code that an employee might write, howover many companies don’t have employees that can write this software. You can see the Powershell for dbatools on Github, so what’s the issue? You can test this code like you might test your own code. In fact, you should aways do this, but you can also count on other people having tested this code as well, perhaps in ways you wouldn’t think of exercising it. You might even be doing this, with employees cut and pasting code from one of these utilities on your system and passing it off as their own work.

Every company might need restrictions on code that goes to production systems. Certainly they should have ways to patch and update this code, especially as many of the issues found from open source software stem from organizations not applying patches. All code should be tested and verified, whether written by FTEs, contractors, purchased, or downloaded from the Internet. However, once you can verify code, there shouldn’t be restrictions on deploying code just because it wasn’t written by Microsoft. After all, many in the community might write better code than Microsoft, and often do.

Take advantage of the tools that are out there. Use free ones if you can and buy third party products if they give you value for your money. However, don’t just say that we can’t install something because it’s not on the install media. Your build process should be scripted, treating configuration as code, and add those useful (tested) tools to all your systems.

Steve Jones

The Voice of the DBA Podcast

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

January 4, 2018
Creating a Books Online Pull Request

One of the neat things at Microsoft did last year was put Books Online in GitHub. This is in the MicrosoftDocs org under the sql-docs repo. The organization is a bit funny, but once you get used to it, you can find the docs.

One of the neat things that is available now is that anyone can edit Books Online. You can’t edit the live versions in GitHub, nor the versions published on Microsoft’s site (or downloaded), but you can submit your changes as a pull request, which Microsoft will review the changes, and if they like them, the changes can get added to the official docs.

This post shows how you can do this.

Requirements

First, you need a Github account. This is free, and you will end up with a fork (copy) of the official repo in your account when you edit.

Second, you need to learn a little markdown. Not much, but enough to make formatted changes. I like this cheatsheet, but there are plenty out there (kidding).

Editing BOL

When you go to a page in the online BOL, you should notice a short menu in the upper right. It has Feedback and Share links, but also an “Edit” link. You can see this in the image below.

If you click Edit, you’ll be taken to the page in Github. Below, I have the Backup page shown. Note the path near the top (by Branch:live) of sql-docs/docs/t-sql/statements/backup-transact-sql.md. This is the file in the repo. You’ll need this.

This is the real repo from Microsoft, and you won’t be able to edit it. If you click edit (a pencil icon on the right side, just above the doc and to the right of the Raw|Blame|History buttons, you’ll get this:

That’s fine. You can edit the page and then save this in your copy of the repo. In this case, I actually wanted to edit this page to add the NUL device. I scrolled down and found the area I wanted to edit. For ease of viewing, I’ve highlighted the place I edited the file.

Commit

All of you reading this should be familiar with version control. If you’re not, learn that.

To commit my change, I scroll to the bottom of the file and I’ll see the propose file change dialog. I can enter a title (meaningful for the change) and a comment that will let someone know what I’ve done.

Once I do this, a new branch is created in my repo. If I look at the result page, I’ll see this:

Reading this, I see that in my fork (way0utwest/sql-docs), I have a new branch (patch-4) that was created. Below this are the details of the commit, but essentially I have a copy of the official Microsoft repo in my area, with one new commit.

The Pull Request

A pull request (PR) is a notification to a repo that there are changes that someone wants to merge into that repo. In this case, a pull request will let MS know that my change is ready to merge, and they can review it.

To start a PR, click the green button in the image above. By default, I’ll get the same commit comments I had listed above. In this case, I see this:

Note, there are a couple important things here. First, this change is “able to merge”. We see that near the top. If you’ve made substantial changes that don’t necessarily merge cleanly, or you have an old copy of the repo, this won’t work. Go back and create a PR to update your repo from MS (you can approve this) and then make your changes.

Second, make sure that your comments make sense to the person that will review this. In my case, I wanted to have more complete docs with NUL as an option (since it is) and I noted this is a target. I could expound why here or add more details. I try to ensure each change I’ve made has a sentence, so if I added a note to a different place in the docs, I’d have a second sentence here.

Click “Create pull request” to complete this. Someone at MS will be notified, and you’ll get an email. In my case, I got this:

Waiting

The next step is to wait. I’ve submitted four or five minor PRs that helped complete or clarify docs. In some cases I got a note from the repo maintainer the same day, some took 3 or 4 days.

In any case, you may get a message that your change is accepted and it’s been merged. In this case, you’re done and I thank you for improving BOL.

You also may get a message back in your PR that there are other changes that need to be made, or on rare occasions, an conflict has occurred. In that case, you may need to edit your change, make a new commit, and a new PR.

This is a great model that Microsoft has given us to allow the community to issue corrections. Prior to this all feedback would get emailed to someone at MS, without any real organization. Or MVPs would send in feedback, but someone would need to interpret that and make a change. Now the community can easily make a change and help improve and correct our docs.

January 3, 2018