Voice of the DBA

Author: way0utwest

Watch Your DataTypes in Aggregates–#SQLNewBlogger

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

I’ve got a database of NBA statistics with data like this for players. I downloaded a CSV and loaded it into SQL Server.

I decided to play with the data a bit and at one point wanted to see who scored the most points for a team and year. So I ran this query:

SELECT
    year,
    team,
    MAX(pts)
FROM dbo.player_regular_season
WHERE
    year = ‘1972’
    AND team = ‘LAL’
GROUP BY
    year,
    team;

The result was 705. That’s a decent number of points, and if I weren’t careful, this might seem fine. 1972 was a long time ago, and they didn’t score as many points as they do today in games.

In fact, if I were putting this in a summary report with lots of data, it might be the case that someone glancing at this would make a poor decision based on the data.

Why?

Let’s look at the data.

Even a quick glance would let me know this seems funny. There are values of 1575 and 1084 in there, but the MAX() I returned was 705. If I look deeper at the import, I can see why.

Anything stand out there? If you look, pts is a varchar, not a numerical value. In the character world, 705 beats 1575. I really need this query:

Always be aware of the datatypes you work with and manipulate. Knowing a little bit about the meaning and use of the data can help you spot anomalies like this. As much as I like random test data, I’d also be sure you have some real data cases when you have users check your work. It’s easy for them to miss problems like this without good reference cases.

Or use good test data that you’ve setup and unit tests.

March 29, 2017
Big Companies are Improving with DevOps

One of the comments I’ve often heard from people that work in IT and haven’t adopted DevOps is that the principles and changes required won’t work at their organization. Quite a few people think that only small, new companies, like Flickr and Spotify can use the ideas. Plenty of others look at only high-tech, progressive companies like Amazon and Facebook able to change.

That’s not true.

In fact, there are four Fortune 500 companies using DevOps, including a large (though young) bank, Capital One, and another, older one, WestPac. I’m not sure anyone would consider American Airlines or Hertz to be small, agile companies, though certainly they are in highly competitive industries and need every advantage over their competitors that they can get. I suspect that is the driving reason for many of these companies to adopt fast, quick software development. They can’t afford to have an idea take months to implement.

Those companies aren’t like yours? What about Maersk or Nationwide? Ticketmaster? Maybe Norstrom (and a few more)? I actually had the chance to speak with a number of Nordstrom employees that had taken a POC concept for the mobile group and proved that DevOps has value. From there, almost the entire IT department, hundreds of employees in groups from internal IT to mainframe to web, all have adopted various types of DevOps processes, starting with value stream analysis. Over a few yeasr, they have dramatically transformed their delivery of software. When someone in the business proposes an idea or need, it used to take over 6 months for something to get deployed. That’s down to a couple of weeks, and it’s released in a true, get-something-useful-to-the-customer fashion. This isn’t alpha or beta software, but a basic item that can be used and is then grown and changed according to customer feedback on a daily or weekly basis.

The transition to DevOps really requires some belief and understanding of the ways in which you can deliver better software, faster. This requires some slow growth, which seems crazy, but the the cultural changes take time, and even the technology tools you choose, require some patience, trust, and experimentation from your technology staff. While it might take months or even a year to get a DevOps process working well and one you’re comfortable with, the gains grow and grow over time.

Even if you don’t believe in DevOps now, why wouldn’t you try to get someone in management to set up a proof-of-concept and build something. It’s a small investment, that could have huge payback with limited risk. You’ll learn a lot and can then decide if it helps you delivery value to your customers in a better way. And if you do adopt DevOps, don’t forget to include the database in your process.

Steve Jones

The Voice of the DBA Podcast

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

March 29, 2017
Hacked
Hacked

By Steve Jones, 2013/01/02
This editorial was originally published on Jan 2, 2013. It is being rerun as Steve is out of the office.

I’ve been hacked before. My personal web site has been hacked with a variety of injection and XSS attacks over the years. None too serious, and I’ve had backups that allowed me to fix things fairly easily, especially once I had a copy of Data Compare, which saved me a lot of time. At SQLServerCentral, we’ve been hacked as well, though not in a long time. I think we’ve closed most of the security holes, and I haven’t had any issues to deal with in quite some time.

However as I was reading a note from Richard Douglas about being hacked, it brought back memories of working at JD Edwards. Richard was hacked at work, on his personal system. At JD Edwards, we were required to lock our workstations at all times when we were not physically in front of them. We also had two accounts: a normal user and a domain admin “privileged” user. As you might expect, there were numerous lapses of people walking to the kitchen or bathroom and forgetting to lock their workstations. It was considered fair game to change settings, send email to our group, even place semi-SFW pictures on someone’s desktop. It was quite embarrassing to be caught, and was much more a an effective security reminder than a reprimand from our boss.

However there is a serious security problem here. Many of us would use our privileged account all too often, since it was a hassle to log out and back in. The “run as” option didn’t work well for some applications, and we were less secure than we probably should have been. If someone walking by, whether an employee, guest, consultant, or someone else noticed SSMS running, how long would it take them to type:
```
  sp_addlogin 'joeuser', 'joeuser'
  sp_addrole 'joeuser', sysadmin
```
I type quickly and that took me less than 30 seconds. I’m sure even a slow typist could get that entered, and erased, inside of a minute. That might result in a serious security breech, if the system to which you were connected contained HIPAA, PCI, or any identity information. Perhaps even worse these days is the chance someone might attach a USB key logger to your keyboard.

You might be safe in your environment, but you can never be sure. A little care in ensuring you are not unnecessarily exposing security holes, and making sure that outsiders are always escorted can prevent embarrassing incidents from occurring.
March 28, 2017
Getting a VHD into Azure with PoSh
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I thought this would make a nice SQLNewBlogger post, an easy get started one. I used the docs from Microsoft as a guide, so here’s how it went.

Login to my account. I used Add-AzureRMAccount here to get the login dialog. I’m not repeating this for now, so this is just an interactive test.

Now I tried to create a storage account, but I hadn’t assigned a subscription. I didn’t think I needed to since I only have one,but hey, I guess I do.

I started typing Select-AzureSubscription, but realized I didn’t know what it was. I can’t remember naming this and under my name in the portal, I didn’t see a place to find it.

Ah, under All Resources I see something, so I’ll try this.

On second thought, under My Permissions, I see it there.

Or not

Get-AzureSubscription doesn’t quite work.

It’s Get-AzureRMSubscription

So, why doesn’t my creation of the storage account work? I should really read error messages. The issue is Select-AzureSubscription fails because I need Select-AzureRMSubscription.

Sometimes the rev’ing of the cloud is hard.

Actually, maybe neither work.

I retreat.

I’m still annoyed. “US West” isn’t valid. FFS, Microsoft. Whether I type any of these, just ask me or process them.
- “US West”
- “West US”
- “USWest”
- “US_West”
OK, I decided to go exercise for a bit.

I’m back (30 minutes later), and I try Get-AzureLocation. I can guess some cmdlets. I get a lot of stuff back and see “West US” is valid. I try a few other valid names of regions, but get errors. It’s not that parameter.

Let’s try a new name.

#$%#@#@# engineers and developers. That’s a valid error message? Not, this is a duplicate or something that makes sense. Glad I left.

Maybe not. At least this is an error that makes sense.

Finally. Let’s upload the VHD.

$2017-03-23 14_33_25-{0%} cmd - powershell$

And it’s off.

$2017-03-23 14_33_41-{0%} cmd - powershell$

Hopefully this will work. I haven’t tried this with PoSh before, and it was somewhat frustrating, though it really didn’t take that long.

SQLNewBlogger

This was a bit of a live blog. Do something, take a screenshot, write some text. It was a bit of a learning experience.
March 27, 2017