Voice of the DBA

Author: way0utwest

SQL Server is Getting Smarter

There’s been a lot of press and media about Microsoft on the AI and machine learning work they’re heavily investing in. From Cognitive Services to Cortana to Bots, Microsoft is really investing in developers and applications that will perform detailed analysis and make more complex decisions in any environment. Event SQL Server has gotten Python added to R Services and will change the way we run queries for analysis and reporting.

The effect this will have on data professionals is not just limited to SQL Server’s machine learning capabilities. This week I saw a couple announcements from Microsoft on changes in SQL Server. Automatic plan correction is coming in SQL Server 2017 and automatic index management is in SQL Azure. I expect the latter to make an appearance in the on-premises product at some point, perhaps 2018 or 2019, but I would certainly count on this coming to your local installations at some point.

Currently plan correction still requires a DBA to decide which plans need correcting, but once that the decisions are made, SQL Server can handle things. However, with automatic plan correction, we can allow SQL Server to force the last good plan when it detects an regression. This isn’t a huge change, but it can dramatically reduce some of the random calls that DBAs get when plans regress and performance tanks. Over time, we might find that many of those nuisance calls go away. I hope that most of you have other work that you can do instead of tracking down plan regressions, and you certainly should have more.

There are other changes, such as threat detection, that I expect will allow a single person, perhaps the accidental DBA or developer, to manage a lot of the trivial, but important, administrative items for a SQL Server instance. This means that we need less data professionals that focus on the infrastructure side of databases. There will be more and more ways that SQL Server improves to handle the mundane tasks, amplifying the power of a single human, reducing the management burden, and using less people to manage more and more.

I expect additional capabilities in SQL Server to simplify most tasks over time, and I’m hoping there’s one in particular that gets built soon. I’d like to see automatic backups (full and log) as a part of the database creation process. Some helpful defaults, perhaps at the instance level, that are applied to ensure that all databases are being backed up and we never see the “transaction log full” error unless we run out of disk space. It will come, as we move to the quick, button click, automatic setting method of managing all the cattle in our infrastructure. At least, I’m planning on this happening.

Steve Jones

The Voice of the DBA Podcast

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

June 8, 2017
The Poor State of Secure Coding

Security is becoming a bigger issue all the time. More companies are getting hacked and losing control of data. It seems every week there’s a new disclosure from some firm. Recently OneLogin had a breach, which is very disturbing as they provide a single sign on solution for customers. This year we’ve learned about E-Sports, XBOX, Playstation, IHG, Arby’s, River City Media, Verifone, Dun and Bradstreet, and more. At this point, there’s no reason for any large organization to wonder if they’ll get hacked. They should be preparingfor when they get hacked.

The state of coding is poor, with far too few developers understanding how to write secure code. Even trying to learn how to code securely is hard. Too many examples given show poor coding practices. If you search for secure coding practices, you’ll get information, but none of the sample applications, none of the common information that most people would use to write code, is returned. This is especially true of data access, where far too many examples use dynamic strings.

Even if we had great developers, there are still issues. A look at a survey from O’Reilly and SIG shows that there are still plenty of companies that are interested in security, but don’t perform reviews or use tools. Certainly many organizations don’t invest in security tools or resources heavily, and many companies don’t want to spend extra time worrying about security when there are features to build and deploy.

My wish is that large organizations would engage in constant pen testing and review of their systems, looking for vulnerabilities, and patching them. I would hope that insurance companies would start to deny claims when a patch for any software has been available for six months or longer. That might help reduce the number of issues from older libraries not being upgraded.

I would also expect that any vendor selling software engage in some security review for their products. In fact, I’d hope that once a company sells a certain number of units, this would be required. I’m still amazed that this isn’t a requirement for purchase from more customers, but since most vendors don’t bother, perhaps avoiding purchases of un-reviewed software isn’t feasible. Maybe it’s just as well; even if we did have some sort of review, how many of us would understand what that means? How many of you really understand what PCI or HIPAA compliance means? How well has that helped us? I guess things could be a lot worse than they are today.

Security is going to be an issue for a long time. All I can do is try to improve my own skills and ask you to do the same. Learn to code securely and try to improve the software you work on. It might only make a small difference, and you might never know if it helps, but I bet you’ll feel better about your own work.

Steve Jones

The Voice of the DBA Podcast

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

June 6, 2017
VSTS– Hosted Agent is the Default for Releases

In keeping with the idea I don’t want this to get out of date, this is VSTS as of Jun 1, 2017. If your screen looks different, find a different article.

I was setting up a test the other day using VSTS (Visual Studio Team Services) and once had something bite me (again). This has happened a few times this year as the service has evolved, but it is one of those things that I keep having to change.

Here’s a new Release definition, where I start with an empty process. I clicked “New Release Definition” and then selected an Empty task, hooking this up to my current CI process.

Usually I add tasks, and then create a release and execute it. Since I mostly work with databases, I’m usually releasing to a local SQL Server instance and want to use my local agents. I have separate ones running on my desktop and laptops, so I can easily demo releases in different environments.

However, when I’ve done this, usually things fail at first. If I’m not paying attention, I won’t notice my build agent looking like this:

That’s the screen showing nothing happening with my agent.

Grouping Tasks in VSTS

Earlier this year, the release process in VSTS changed to allow us to group tasks. I can now add different groups of phases, which I stumbled upon by clicking the arrow next to “Add Tasks”. This is a UI Fail because the arrow in other parts of VSTS is linked to the text to the left (like the Add environment area).

I stumbled on this one day, thinking the arrow let me add tasks, but I accidently added a new agent phase. By default, we have a single agent phase, but clicking the arrow allows more.

What’s also interesting, and not intuitive to me, is that if I click on the “Run on agent” area, I get settings to the right. This is different than the old release section, which had the properties as a global item at the top.

This is where I need to change to the agent queue, in this case, the default for my desktop.

This might not be a big deal, and I’m sure lots of people are happy to use the hosted agent. I’m always wary of costs, so I try to avoid spending compute dollars in Azure when I don’t need to, and this is a place I want to avoid spending $$.

Hopefully, after writing this, I’ll remember to set the queue for my future demos the first time. Now if I can just get the VSTS people to give me a “default” setting I can change…

June 5, 2017
Big Power Issues

Recently British Airways had a massive computer failure and had to cancel hundreds of flights across a couple days. This was a large disruption for travel as tens of thousands of passengers were stranded around the world. In addition to the concerns many of us have about security, I also worry about large scale systems that continue to grow in scale, with more users depending on them, and not necessarily getting updated with modern technologies. While I know many of the customer facing systems have been enhanced for companies like airlines, I’m not sure the core infrastructure that backs these systems has changed.

British Airways has blamed the cause of the outage on a power failure, with some finger pointing between the airline and their outsourcing company. BA appears to say a power surge caused problems with it’s UPS’s and batteries, which resulted in a situation where “the controlled contingency migration to other facilities could not be applied.”

I’m sure some of the information being published is carefully vetted by lawyers and doesn’t necessarily reflect the actual technical issue, but it doesn’t matter. A well designed HA solution shouldn’t care if a primary system drops off line, falters, or anything else. In the worst case, any IT system failover can be forced, understanding a potential loss of data. In the SQL world, we could always remove a primary and just go with whatever data is on the secondary. Certainly within a few hours we could be up and running.

I suspect that the architecture of the BA system is not well designed for large failures. I know that dealing with power is tricky. While working for a large company (10k+ employees), our data center went down one day, which affected our public presence, customer support, and plenty of internal systems. We had UPS service underway, which had disconnected main power for some reason and we couldn’t quickly switch to the public grid when a system failed. It took a few hours to reroute power, during which our CTO paced the data center floor in an irate manner. Certainly that didn’t speed things along.

As noted in the piece I’ve linked, there are some strange inconsistencies with the explanation. It’s entirely likely that a data quality issue caused problems that took hours to rectify. I’ve been on the wrong side of those, working through queries and attempting to piece together some version of the correct data from various sources. If that’s the case, then I’d really like to know what the faliure of their systems allowed bad data to disrupt operations. There are learning opportunities here for us data professionals.

I do hope that some of the details of how BA architected their system gets shared among technical staff. At the least, I’d like to have large companies like UPS, Wal-Mart, British Airways, and other companies publish and share information about technical details and architectures. Some of the tech companies do this already (Amazon, Google, etc.), but it’s good for other industries to share their information as well. We owe it to the industry to learn what works well and what fails as we grow systems to larger scales and more complex interactions. Certainly I’m proud that many SQL Server experts share their experiences, helping others learn and make fewer mistakes in the future.

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.

June 5, 2017