This Site Makes Cookies

Apparently under new guidelines here and in Europe I’m ethically obligated that I’ve been known to make cookies from time to time.  Oh, excuse me, something is coming in to my earpiece now.  Oh, never mind, I’ve been informed those laws apply to a different type of cookie.

In any event, I first got into the habit of baking cookies on a somewhat regular basis while in college. It became a stress release for me, and also apparently made me quite popular among the sorority sisters and outing club members I lived with.  I would, probably at least once a month my sophomore year make a double-batch of Tollhouse Chocolate Chip cookies. They rarely lasted more than a day or two.

Since then, I’ve expanded my repertoire, including once trying “bacon cookies” for my very first SQL Saturday. Those weren’t a huge hit, but haven’t stopped me from baking.

That said, I’ve learned a few things over the years about baking cookies. For example, my daughter would bring cookies to school for an event and would often be asked, “oh did your mom make them?”  She’d patiently explain that no, her dad did. Even today, the assumption is that when it comes to school events, the mom does the baking. I’m glad that my kids both realize that it’s unfair to expect that mothers have to do all the baking and other domestic duties.

But, I also learned something else that sort of threw me for a loop. People don’t like homemade cookies from a zip-lock bag.  Sometimes I’d bring cookies to events and people wouldn’t eat many of them. Now, being practical and in a hurry, I’d almost always just toss the cookies into a zip-lock bag.  It was my daughter who suggested I start putting them into a plastic container with a lid instead. Suddenly I found the same cookies were much more popular. My daughter explained her theory, which I tend to believe. For whatever reason, perhaps hygiene, people don’t want to reach into plastic bags for food. It may be touching the same sides that everyone else did or something else. But regardless, putting them into plastic tub with a container works.

Call it a UI problem, but, it seems to work.

Today’s take-away, just because you’re comfortable with a solution and think it works, don’t be adverse to making changes, even if they seem silly or trivial, if that’s what your users desire.

P.S.: Check out my latest writing for Red-Gate: PowerShell and Secure Strings.

Two Minds are Better Than One

I’m going to do something often not seen in social media. I’m going to talk about a mistake that I made. It’s all to common on various certain media sites to talk about how perfect our lives are and how great things are. You rarely hear about mistakes. I decided, in the theme of this blog of talking about how we approach and solve (or don’t solve) problems, I’d be up front and admit a mistake.

This all started with a leak in the downstairs shower. It had been growing over the years and I frankly had been ignoring it.  Why put off to tomorrow what you can put off to next month or even year? But finally, in December of last year it became obvious that it was time to fix the leak. It had continued to grow, and now that my son was home from college for extended break, he had setup a work area in the basement, below the bathroom.  I figured he didn’t really need to suffer from water dripping onto his desk.

So, he and I went into full demolition mode and ripped out the old tile and backer board to get to the plumbing.

New plumbing in bathroom

New plumbing

You can see some of the work here. I also took the opportunity to run wiring to finally put in a bathroom fan. That’s a whole other story.

Anyway, the demolition and plumbing went well. Then we put up the backer board and sealed it. And left it like that. It didn’t look good, but it was waterproof and usable. It was “good enough”. So for about 8 months it sat like that. But with an upcoming pool party, I decided it was time to finally finish it off. One of the hold ups had been deciding on tile. Fortunately, on a shopping trip about a month earlier, my son, my wife and I found tile we liked (my daughter, who ironically still lives at home and will be using the shower more in the next few years than her brother, was in LA on vacation, so she ended up not really having much say in the matter).

So, earlier this month, while the rest of my family took a weekend to go to Six Flags New Jersey, I figured I’d surprise them with finally tiling the shower.  I went to the big box store whose favorite color is orange and bought the required materials. Since tile we had selected is approximately 6″ wide and 24″ long, I had to make sure I got the right mastic.  This was a bit different from stuff I’ve worked with in the past with a bit more synthetic materials in it and it mixed differently, and had slightly different drying characteristics. That, combined with growing darkness lead me to move quickly. The darkness was a factor since any tile cutting I was doing was outside.

And, all that lead to a simple mistake.  On the end wall, there’s a window and as a result part of that wall needed tiles just less than 24″ long. On one hand, this is a huge plus since it means less seams and less places to grout. On the other, it meant in one spot having to work around the trim of the window. And that’s where I made my mistake.  The trim had been put over the original tile, so there was in theory room behind the stool of the window to fit in a piece of tile. That had been my plan.

But, when it came to sliding in the nearly 24″ long piece of tile, it wouldn’t fit. It wouldn’t bend (obviously) to let me get it tucked behind the stool and due to the stickiness of the mastic, I couldn’t slid it in from the top.

So, I cut out a notch. In the back of my mind I somehow was thinking, that it wouldn’t look that bad and tile would cover it.  Well, I was obviously wrong.

In hindsight, I realized I should have cut the tile in two pieces, created an extra seam (like the row below it that had to cover more than 24″ wide in any event) and then I could have slide in the smaller piece and then put in the remaining piece. It would have been perfect, looked great and more likely to be waterproof.

So, this gets me to the title. Had I been doing the work with someone else, I’m sure I’d have said, “Damn, this is gonna suck, any ideas?”

A mistake

My mistake

And I’m sure someone else would have suggested, “Hey, cut the piece and slide it in.”

I like working alone, but sometimes, you need a second person to help. Or more than one brain as I’ve mentioned in the past.  If nothing else, sometimes a duck can help.

Idera Ducks!

A bunch of rubber ducks, including two from Idera!

So, the moral of the story is sometimes two heads are better than one. Oh well, I won’t be the one using that shower, so I won’t see my mistake all the time!

Oh and check out my latest Red-Gate Article on Secure Strings in PowerShell.

Security: Close isn’t good enough!

I was going to write about something else and just happened to see a tweet from Grant Fritchey that prompted a change in topics.

I’ve written in the past about good and bad password and security polices. And yes, often bad security can be worse than no security, but generally no security is the worst option of all.

Grant’s comment reminded me of two incidents I’ve been involved with over the years that didn’t end well for others.

In the first case, during the first dot-com bubble, I was asked to partake in the due diligence of a company we were looking to acquire. I expected to spend a lot of time on the project, but literally spent about 30 minutes before I sent an email saying it wasn’t worth going further.

Like all dot-com companies, they had a website. That is after all, sort of a requirement to be a dot-com. And it was obvious it was backed by a database server (which I knew was SQL Server, which sped up my process, but only by a few minutes). So, I did the obvious thing and got the IP address of the web-site. Then, I simply tried to connect to SQL Server from my desktop to that IP address and one or two on either side of it. On my second attempt, the IP address right before the one of the website replied to my attempt to reach SQL Server. That was not a good sign. The reply meant there was no effective firewall in place. Note, had they not been using SQL Server, but some other tech, it might have taken me another 10-15 minutes to find the right client to connect. So knowing it was SQL Server wasn’t overly important.

But of course at least they had a password right? Well, back then, the latest and greatest version of SQL Server was 2000 which still did not require a password when you set it up.  I asked myself, “it couldn’t be that easy could it?”

Sure enough it was. Within minutes I had logged in as sa without a password. I now had complete control of their SQL Server. But even more so, back then SQL Server allowed unfettered access to xp_cmdshell. In theory at that point I could have done anything I wanted on the box, including installing remote access software and creating and giving myself administrator access.  I didn’t. But, my email to my boss was short and sweet. I explained how there was absolutely no way we could acquire their platform without a complete top to bottom review of it for any signs of malware. If it took me only 30 minutes or less to get in, I was almost certain their system was owned.

We never acquired that company. I’ve wondered since then what happened to them. My guess is, like many dot-com companies they folded. I can’t say it would have been because of their lack of security, but I can say that the lack of security played a huge factor in us NOT acquiring them. (and for the record, the company I worked for at the time ended up acquiring 1-2 other companies, merging with a 3rd and finally being acquired by a 4th, which is still around. So we were doing something mostly right.)

The second incident that comes to mind was about 8 years later at another start-up. I was asked by the COO to do some due diligence on the setup in another division’s datacenter setup. Again, I didn’t do anything fancy. I knew they weren’t running SQL Server, but I figured I could still do some probing. This time what I found was a bit different. It wasn’t software per se, but rather their iSCSI switch. Sure enough not only did it have a public facing IP address, but, the CTO of that division had failed to change the default password. I was very tempted at the time to give the IP address to my 8 year old son, without any other details and asking him to try to log in. Given his skills, even at that age, I’m 99% sure he’d have figured how to Google the required information and get in. But I figured I didn’t really need to do that to make my point.

That and other factors later lead to the CTO leaving the company.

Moral of the story: Make sure your sensitive information is under some form of lock and key and don’t use blank or factory default passwords, let you or your company end up in a headline like this one: Evisort Data Exposed.

Design Thoughts

Ever look at a product and wonder, “why did they design it that way?” I know I have, and I have some examples I want to bring up.

Years ago, over dinner, I had a programmer from our Wisconsin office basically ask, “why the hell is your file system for your web servers setup the way it is?”  It was a fair question. It wasn’t something one would normally see.  But before I explain that…

Like any modern American, I’m physically incapable of being more than 10′ from a flat screen TV in my house.  We have several, including one in my office and one in the kitchen. I couldn’t tell you the brand of the one in the kitchen (well I could, but I’m too lazy to go downstairs and find out) and the only reason I can tell you the brand of the one in my office is because I can see it from here. It’s an Inginsia brand.

Both serve the same function: they allow me to watch TV. But both have design quirks.

Their button layouts are a bit different (note the layout of the numbers and the volume/channel control buttons.)

Kitchen TV Remote

Kitchen TV Remote

Office TV Remote

Office TV Remote

The kitchen TV also has a built-in DVD player, so it has additional controls for that.

So obviously, there’s different design philosophies and requirements here. But I want to go a step deeper and talk a bit about functionality.

The kitchen TV remote, if you mistype a number, you can hit the Vol – button and it will essentially backspace and delete the number. Actually a handy feature.  The Office remote has no such functionality, though hitting EXIT will remove the entire channel already entered.  Score one for Dynex. (Ok, I did go downstairs so I could grab the remote and take a photo).

But, the Dynex has one annoying quirk I’ve never figured out. When I hit the OFF button, there’s a noticeable delay of 1-2 seconds before it actually turns off. For the life of me, I have NO idea why. I mean I’m turning off a TV. It’s not like I’m shutting down a computer where it has to write the contents of memory to disk and perform other tasks. Sure, maybe it has to save the last channel I was tuned to, but it could do that right after I tuned into that station. Same with the volume.  Every other TV in the house, including my office one, when you hit the off button, turns off instantly.

I’m reminded a bit of early computers that had the big red switch. There was something satisfying about turning off an early PC. You knew it was instantly off. There was no two questions about it. Now, shutting off a PC is a far more complex operation and can take sometime.  But a TV? I’d love to know why the kitchen TV takes a long time to turn off.

Now back to the file design the programmer was asking me about. Essentially we had 5-6 web front ends, each with a virtual directory in IIS pointing to a NAS. Not an entirely awful setup, but uncommon at the time.  We were offering a web platform to newspapers so they could publish their content. Originally we tried using a 3rd party package to make sure the content on all the servers was always in synch (since a newspaper could upload content at any time to any of the servers and wanted it available instantly). What we found was sometimes we’d get into race conditions where files could actually end up erasing themselves. The 3rd party company kept assuring us they had the solution. Well after a desperate call at 4:00 AM call from my on-duty NOC person, I drove into the office, scrambling to figure out a better solution. On the drive, the idea of using the virtual directories to the NAS occurred to me. We implemented it in about 30 minutes and solved our problems. It was supposed to be a temporary solution until we came up with a more robust, permanent solution. But, 18 months later it was still in place, working great and I was explaining it to our out of town programmer. He went from, “that’s nuts” to “Hey, that makes a lot of sense.”

So, I like to think that when there’s a design I don’t understand, the designers at the time had their reasons. But, to be honest, I’m not always sure.

For example, the photo that should be heading up this article, of a shampoo bottle and a bottle of conditioner, both from the same manufacturer, both designed to be cap down, are printed the opposite way. The only reason I can think of that makes sense is so that in a befuddled, sleep deprived state, I can more easily determine which is which. But even if that is the answer, why this way, and not the other? Inquiring minds want to know!

And yes, the shampoo bottle can be placed cap up, but the conditioner bottle can’t be. Again, why? The viscosity of the two aren’t that different. Again, inquiring minds want to know.

Shampoo/Conditioner bottles

One of these is upside down!

 

Fail-safes

Dam it Jim, I’m a Doctor, not a civil engineer

I grew up near a small hydro-electric dam in CT. I was fascinated by it (and still am in many ways). One of the details I found interesting was that on top of this concrete structure they had what I later found are often called flashboards. These were 2x8s (perhaps a bit wider) running the length of the top of the dam, held in place by wooden supports.  The general idea was they increased the pooling depth by 8″ or so, but in the advent of a very heavy water flow or flood, they could be easily removed (in many cases removed simply by the force of the water itself).  They safely provided more water, but were designed in fact to fail (i.e. give away) in a safe and predictable manner.

This is an important detail that some designers of systems often don’t think about; how to fail. They spend so much time trying to PREVENT a failure, they don’t think about how the system will react in the EVENT of a failure. Properly designed systems assume that at some point failure IS an not only an option, it’s inevitable.

When I was first taught rigging for cave rescue, we were always taught “Have a mainline and a belay”.  The assumption is, that the system may fail. So, we spent a lot of time learning how to design a good belay system. The thinking has changed a bit these days, often we’re as likely to have TWO “mainlines” and switch between them, but the general concept is still the same, in the event of a failure EITHER line should be able to catch the load safely and be able to recover. (i.e. simply catching the fall but not being able to resume operations is insufficient.)

So, your systems. Do you think about failures and recovery?

Let me tell you about the one that prompted this post.  Years ago, for a client I built a log-shipping backup system for them. It uses SSH and other tools to get the files from their office to the corporate datacenter.  Because of the network setup, I can’t use the built-in SQL Server log-shipping copy commands.

But that’s not the real point. The real point is… “stuff happens”. Sometimes the network connection dies. Sometimes the copy hangs, or they reboot the server in the office in the middle of a copy, etc. Basically “things break”.

And, there’s another problem I have NOT been able to fix, that only started about 2 years ago (so for about 5 years it was not a problem.) Basically the SQL Server in the datacenter starts to have a memory leak and applying the log-files fails and I start to get errors.

Now, I HATE error emails. When this system fails, I can easily get like 60 an hour (every database, 4 times an hour plus a few other error emails). That’s annoying.

AND it was costing the customer every time I had to go in and fix things.

So, on the receiving side I setup a job to restart SQL Server and Agent every 12 hours (if we ever go into production we’ll have to solve the memory leak, but at this time we’ve decided it’s such a low priority as to not bother, and since it’s related to the log-shipping and if we failed over we’d be turning off log-shipping, it’s considered even less of an issue). This job comes in handy a bit later in the story.

Now, on the SENDING side, as I’ve said, sometimes the network would fail, they’d reboot in the middle of a copy or something random would make the copy job get “stuck”. This meant rather than simply failing, it would keep running, but not doing anything.

So, I eventually enabled a “deadman’s switch” in this job. If it runs for more than 12 hours, it will kill itself so that it can run normally again at the next scheduled time.

Now, here’s what often happens. The job will get stuck. I’ll start to get email alerts from the datacenter that it has been too long since logfiles have been applied. I’ll go in to the office server, kill the job and then manually run it. Then I’ll go into the datacenter, and make sure the jobs there are running.  It works and doesn’t take long. But, it takes time and I have to charge the customer.

So, this weekend…

the job on the office server got stuck. So I decided to test my failsafes/deadman switches.

I turned off SQL Agent in the datacenter, knowing that later that night my “cycle” job would turn it back on. This was simply so I wouldn’t get flooded with emails.

And, I left the stuck job in the office as is. I wanted to confirm the deadman’s switch would kick in and kill it and then restart it.

Sure enough later that day, the log files started flowing to the datacenter as expected.

Then a few hours later the SQL Agent in the datacenter started up again and log-shipping picked up where it left off.

So, basically I had an end to end test that when something breaks, on either end, the system can recover without human intervention. That’s pretty reassuring. I like knowing it’s that robust.

Failures Happen

And in this case… I’ve tested the system and it can handle them. That lets me sleep better at night.

Can your systems handle failure robustly?

 

 

Hours for the week

Like I say, I don’t generally post SQL specific stuff because, well there’s so many blogs out there that do. But what the heck.

Had a problem the other day. I needed to return the hours worked per timerange for a specific employee. And if they worked no hours, return 0.  So basically had to deal with gaps.

There’s lots of solutions out there, this is mine:

Alter procedure GetEmployeeHoursByDate @startdate date, @enddate date , @userID varchar(25)
as

— Usage exec GetEmployeeHoursByDate ‘2018-01-07’, ‘2018-01-13’, ‘gmoore’

— Author: Greg D. Moore
— Date: 2018-02-12
— Version: 1.0

— Get the totals for the days in question

 

 

set NOCOUNT on

— First let’s create simple table that just has the range of dates we want

; WITH daterange AS (
SELECT @startdate AS WorkDate
UNION ALL
SELECT DATEADD(dd, 1, WorkDate)
FROM daterange s
WHERE DATEADD(dd, 1, WorkDate) <= @enddate)

 

select dr.workdate as workdate, coalesce(a.dailyhours,0) as DailyHours from
(
— Here we get the hours worked and sum them up for that person.

select ph.WorkDate, sum(ph.Hours) as DailyHours from ProjectHours ph
where ph.UserID=@userid
and ph.workdate>= @startdate and ph.workdate <= @enddate
group by ph.workdate
) as a
right outer join daterange dr on dr.WorkDate=a.WorkDate — now join our table of dates to our hours and put in 0 for dates we don’t have hours for
order by workdate

GO

There’s probably better ways, but this worked for me. What’s your solution?

This is secure, right?

WP_20180114_001So, over the weekend, while I was waiting for my wife’s hockey game to begin I was exploring the facility. I saw an upper level and I was curious if it was open. But alas no, it was locked. And I mean locked. Above is a photo of the chain and lock used to keep people out.

There’s a saying that locks only keep out honest people. That’s not entirely true, but there’s some truth to that. Security, especially in my field of IT and databases is extremely important. I am often working with data that contains private information about people. I have to take steps to keep it safe. So, as I mentioned in Too Secure, I don’t have a problem with security per se. There I talked about security that prevented me from doing my job.

Here we have the opposite. Making something that look secure, but really isn’t. I mean this has a heavy chain and a lock. I’m not sure who did this or why, but I’m sure they felt the lock was important. It’s not a cheap one either.

 

This is a case where probably a simple sign and string, “Balcony closed” would have sufficed.  The only purpose the lock really had was to make sure the chain wasn’t stolen. The purpose of the chain appeared to be to give something to attach the lock to.

Now, sure, someone could have removed a sign and string and said, “oh we never saw it” but again, what’s the ultimate risk if someone got upstairs? It was simply an observation balcony. They weren’t really securing much.

But I’m sure someone felt good about their security.

So, when you’re making something secure, stop and make sure you’re spending your time wisely. Sometimes not everything needs the most secure system available. Sometimes a “keep out sign” might be enough and easier.