Voice of the DBA

Author: way0utwest

Closing Down for the Year

Today is my last day of work for 2015. I’m actually proud that I’ve managed to take almost all my vacation (3 days left), and will be enjoying myself next week with my family, taking our holiday vacation in the mountains.

It’s been quite a year for me. Lots of travel in short spans, some long trips, but also some good breaks. I’ve spent some time learning, and improvind my skills, and at the same time I turned down lots of work that might have overloaded me.

I think 2015 was a good year for me, and I’m looking forward to continuing some momentum and making 2016 even better.

December 24, 2015
Correct Old Mistakes

I ran across this piece on the VTech hack that recently occurred. It’s almost a classic example of what not to do in data storage. You can read the piece, and also look at Troy Hunt’s analysis, but clearly we can see that poor encryption, unencrypted communications, plain text storage of passwords, and more. What’s especially disconcerting is that we have kids’ information disclosed, plenty of which could be problematic years down the road as these kids grow up.

Apart from all of the technology issues, there are certainly responsibility issues. I expect that VTech will deny knowledge of issues, and certainly limit the amount of time they admit to knowing about the security issues. After all, they’re a corporation and if they can deny liability it could certainly limit the number of actions taken against them. However I’m hoping that the developers and operational people that manage this technology realize they made mistakes while building these systems.

There’s a certain immaturity that’s prevalent in the analysis of this system. I’m guessing that developers were under pressure to get websites up and running, in concert with product launches, and that plenty of code was shared among their various sites and web domains. However I would hope that the current developers at VTech would have learned more about building robust applications, and would be looking to rewrite and rebuild their systems to be more secure with more current technologies. I hope that any of you running Flash based systems, or using MD5, or any other well known, poor security practices, would be pressing your management to correct those deficiencies and giving them more secure solutions. You might also give them a copy of the article linked above.

Likely most companies out there, I’m guessing VTech’s management don’t want to spend money to rebuild systems that work, regardless of security flaws. Likely developers that have learned how to better code public facing sites don’t have the time to spend rewriting old code when they have new systems to develop. However I think that the old code that lives out there, that poorly built code that most of us have written in our past, would get updated over time, as part of the cost of doing business. This is especially true for anyone using encryption, where upgrades should be regular and mandated as Moore’s law and better mathematics consistently eliminate the security provided by older algorithms.

Steve Jones

The Voice of the DBA Podcast

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

December 24, 2015
Getting all Yesterday’s Sales, or Finding Midnight Yesterday
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as#SQLNewBloggers.

I see questions like this regularly. How do I get all the sales from yesterday? I tried using DATEADD(day, –1, getdate()), but I only get some of the sales.

Many people working with T-SQL know this is an issue. They know that getdate() returns the date and time of this instant (roughly). At the time of this writing, that’s 3:11 pm.

However if I want sales from yesterday, I really want all timestamps from midnight on. So I probably want code that looks like this:
```
SELECT SUM(ordertotal)
 FROM sales
 WHERE SalesDate > '20151124 00:00:00'
 AND SalesDate < '20151125 00:00:00'
```
How do I get the time to be midnight?

The easy answer is one I’ve been using quite a bit lately to answer questions, and I’ve refreshed my knowledge of the datetime trick. I use a combination of DATEADD and DATEDIFF to get to a 0 based datetime.
```
SELECT DateAdd(Day, Datediff(Day,0, GetDate()), 0)
```
In this case, I’ll get midnight yesterday, or 2015-11-24 00:00:00. This is because I’m using 0 as my base date and looking for the days (in DATEDIFF) since that 0 based date. When I add those days with DATEADD to the same zero based date, I get the correct date, but with a 0 based time.

This same technique works to find the first of this month.
```
SELECT DateAdd(Month, Datediff(Month,0, GetDate()), 0)
```
You can also use other datetime values to normalize those times.

SQLNewBlogger

This was a quick post. I had answered the question and spent less than ten minutes putting this together.
December 23, 2015
The Database is the Cornerstone
I saw a case study video the other day from Redgate Software. It was a look at one of the upper management on Fitness First, a fitness/gym company that has facilities in dozens of countries. It’s a good, short summary of the benefits they’ve seen from implementing DLM and ALM, with the DevOps Guys.

It’s worth a watch, and I especially like that this manager understands and gets that the database is the cornerstone of better software deployment. That’s his quote. Without being able to be flexible and agile with the database, it hampers your flexibility.

Fitness First at SQL in the City

I also appreciate that they do regression testing in an automated fashion. It helps them reduce the number of bugs and speeds their ability to deploy. They also have moved to curated sets of test data to allow them to test known cases and bugs they’ve seen.

They also “run at speed”. They meet the business needs, they can work quicker to meet client demands. That’s important.

Fitness First also did a presentation at SQL in the City. I sat in and was impressed, especially with some of the work from the DevOps Guys. It’s long, 40min, but interesting.

Fitness First Case Study

I wasn’t sure what to think when it started, but by the end, I think that this is a good view of how DevOps can help. It probably is more valuable to you if you have a backlog of work, but even if you want to develop and deploy at a pedestrian pace, having a DLM/ALM setup can build your confidence and reduce bugs.

There are a few other short videos that showcase what some of our clients think of DLM and automated database deployment:
December 22, 2015