So What Does A DBA Do?

Working on a presentation for a student group at my alma mater about what a DBA does and why it matters even if you are never a DBA…

My argument in large part is…

Odds are you are going to have to deal with one, so make an effort not to tick the DBA off because it will make your life difficult…

 

What if you had an object type that is inherently resistant to injection attacks?

Have to admit this wasn’t my idea

For one thing, follow Rob if you don’t already, he does some interesting stuff with data visualization, data and news reporting

But he asks the sort of question that makes a decent amount of sense and to be blunt would have to come from someone who isn’t a DBA because it is kind of out of the box from how a DBA thinks about database design.

So lets ask the questions…

How would you design/limit such an object? Could you have an object that only allowed access via a single select or some sort of limited statement?  What if there was an object that limited the number of rows it would return regardless of how many qualified  and that could be defined at object creation time? So the object  would only return 10 rows regardless of how many qualified? What if you could create an object that had explicit statements that were the only statements allowed access it.  How about an object that only allowed some fields to be qualified on? How would you design a database object that was resistant to injection attacks via an application?

Yeah it adds a whole bunch of complexity but it could end up being a lot more secure. A injection attack resistant object could be a bit of a game changer in the database world.

But wait, wait, good apps should prevent injection attacks!  Yep, you are right about that but just because you have a good lock on the door doesn’t mean you don’t also have ADT come out to the house to put an alarm in and not leave the jewelry out in the open at home.  Good attack resistance should come from both good application design and good database design and the more tools on the database side the better.

But Can You Do It With Distractions?

Was having a discussion a while back with some people who have system management responsibilities about problems they have solved and or outages they have dealt with while being sick, medicated (various ways), sleepy or otherwise distracted.  It got me thinking about the challenges of those situations and some workarounds I have found over time.

The first key is realizing that you are distracted when dealing with the issue.  Just because the phone has woken you up doesn’t mean you are with it when talking to someone on the other end of it or you are reading the e-mail they just sent you right.  Without awareness the rest of this is moot…

The second thing I have found works for me it saying out loud and following with my finger every command or action that I am about to take before I take it and doing that twice.  I have found that saying it out loud can get my attention if I am about to do something that might be destructive. For me at least it is one thing to read drop or delete, it’s another thing to hear it.  For me at least it is different to hear it, perhaps it is the ol’ DBA sense, but hearing it makes me focus on it.

The third thing is wrapping everything in a transaction, this sort of happens by default in Oracle so it is more of a SQL Server thing but putting a transaction around everything can give you an out from lots of possible problems.  If I have been woken up about an issue, when I ramble down to the basement and connect the first thing I do in SSMS is type BEGIN TRANSACTION and then type — ROLLBACK, when I am ready to commit I have to type out COMMIT and execute it.

Finally if you are not in a position to deal with an issue (I was once at the hospital with a family member when I got called) get help, as much as being the hero can be a cool part of the DBA role, just like Superman can’t deal with kryptonite no DBA can deal with every distraction and a database issue at the same time. Know your limits, better caution before than tears after…

 

 

Why RTFM Is Never The Right Answer…

Sometimes when I see a question on twitter or on one of the database help sites, there will be one or more responses that will basically say ‘RTFM’. They may be much more polite than just saying RTFM, but when it comes down to it, that is what they are saying. It seems at times it would be a very logical response. The answer is right there in the manual (or related webpage) just point them to it and be done…

But, the problem is sometimes to be blunt, the manual is more harmful than helpful…

Take  the technet page for PIVOT and UNPIVOT for example, it starts with this three sentence description.

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

A logical comment if you are used to thinking in terms of relational calculus, but if you are looking to basically pivot a result set, it isn’t all that helpful or logical IMHO.  That paragraph is going to do nothing but intimidate someone from the accounting team that has basic SQL skills and is looking to try and pivot a result.  Once you get down to the examples it starts to make sense, but that opening salvo is just intimidating.

So when you start to think the answer to someone is to RTFM, I would suggest you RTFM and try and figure out what is confusing them.

 

 

Something I Sometimes Wish SQL Server Did…

I was looking into a MySQL security issue today and realized that the ability to limit rights not only by user but also by host could come in handy.  Such that you could have it so some accounts might have some specific system rights or role memberships only if they were connected directly to the server for example might come in handy.

MySQL can do this and dealing with it today (it was preventing me from doing something) I realized that it could be kind of handy.

Some Curling Info You May Not See Other Places Pt 3…

So was the 7 point end for the UK vs the US a big deal?

Yes, it was/is.  You don’t see 7 point ends that often at this level of competition. Heck you don’t see them that often at any level. It requires really good shooting for one rink (team) and some missed shots for another rink (team).  Usually you don’t even try or have the chance to get 7 in the house, no less have them all in scoring position…

It will be interesting to see how they bounce back from that.

 

Some Curling Info You May Not See Other Places Part 2….

Why Shots Miss…

There are several different reasons why a shot misses, you would think. “Hey these guys do this all the time why did he miss that”, here are some of them.

You throw the wrong weight:  That is either the stone was thrown too hard or too soft.  That can be a mistake on the part of the curler who has thrown the stone or on the part of the skip who called the weight on the stone.  Sweeping can help this somewhat, but if the weight is off by too much all the sweeping in the world isn’t going to help

The skip misread the curl:   Keep in mind the location on the ice where the skip puts their broom is where you are supposed to aim your release, not where the stone is supposed to end up. The skip is reading the curl of the ice and using that information to determine the target line.  It isn’t an exact science.

A bad throw: Just generically sometimes even at the top levels you just have a bad throw.  Something happens during the slide or release and you screw up, just like Tiger Woods shanks one once in a while even top curlers do.

The ice has changed: Ice does not stay exactly the same as a match progresses, it might get faster, slower, the curl may change etc.  At the top events the ice is really, really good, but it still changes.

Something on the ice:  Something on the ice as small as a human hair can impact the path of a stone.  This is part of the reason you see curlers ‘clean’ the ice, that is very lightly sweep in front of a stone, sweep the bottom of a stone before the throw it and even sweep when there is no stone moving.

 

 

 

 

 

Some Curling Info You May Not See Other Places Part 1.

Ok,

Way off the normal database stuff, but I am also a curler (with the stones, brooms and ice) and wanted to share a few things about curling you may not see other places on-line.

Why do they pass up a chance to score 1 point at the end of the end?

Because if you have the hammer (the last stone in a end) at this level getting 1 point (unless it is in the 10 end to win) is not worth giving up the hammer normally.  If you can get 2 points when you have the hammer and hold the other rink (team) to 1 point when they have the hammer, you win.  So that is why you will sometimes see the rink with the hammer pass up the chance to score 1 point.

Why the different types of sweeping?

In curling sometimes you sweep lightly about 2 feet in front of the stone, this is called cleaning.  When you are doing this you are not trying to directly influence the path of the stone but you are just making sure there is nothing on the ice that is going to impact the path.  Something as simple as a hair can seriously impact the path of the stone.

When they are sweeping hard, they are trying to impact the path of the stone, either by making it go further or by reducing the curl (when you sweep hard near the stone you will make it go further and curl less, it will do both).  Also yes, it is physically hard and a workout when you are sweeping hard.

What the hell are they yelling?

They are yelling directions to the sweepers: Hard means sweep directly in front of the stone and depending on who is yelling the volume tells you how hard the hard needs to be.

Moving Dynamics Databases To A New Database Server…

Turned out to be easier than I expected.  You can Google how to do it and most of the current stuff out there seems logical enough.  More than anything else I would suggest is Don’t Panic.

A couple of things, keep in mind GP will encrypt passwords that is uses for defined SQL Server users, your best bet may just be after the move have the GP admin just reset all the user passwords in GP and have the users change them at first logon.  It will also kind of force the users to perhaps use a different password.

Also don’t forget to look for orphan users when you are done restoring.

Why NOLOCK is NSFW and FUD…

Ahh NOLOCK……

I have written before about NOLOCK and why I feel it is a bad idea, so I will point you to Grant Frichey’s blog about some of the issues with NOLOCK here.

But what makes NOLOCK NSFW is pure and simple, it isn’t consistent.  A dirty read by it’s very definition is inconsistent and consistency is one of the primary reasons you are using a database in the first place.  Consistency in any system, is an expectation and a requirement.  People have to have confidence in that what any system is showing them is the ‘truth’ at that moment. With NOLOCK you lose that ability, since by reading dirty data you can see data that was never ‘truth’. In my opinion nothing hurts a system’s credibility faster than that inconsistency, ‘that was wrong’ is always going to be worse that  ‘that was slow’.  Think about it, would you rather get a wrong answer or no answer at all?

Do you want to be the person behind a system in any way shape or form that is inconsistent?  I would take slow over inconsistent in a heartbeat.

To use a bit of a relevant example today for me, is that if you are in a fantasy football league and you check your score during a game, you wouldn’t expect it (nor would you want it to) to reflect plays that are in progress because you don’t know if the play is going to undone by a penalty or whatever.

I get that NOLOCK can solve all sorts of issues without much effort, but in solving those issues you are using a blunt instrument that can and will eventually undermine the faith users have in your system.  In the long run it isn’t worth it…