Skip to content

Managerial Moneyball

I really enjoyed reading Moneyball. Its a book about baseball and data and how information should be used to choose baseball players for the Oakland As. It’s an interesting approach, one that has rarely been used in the sport in the past, though it is gaining traction. It does seem that this approach has helped the As to high level of success given the constraint of their limited payroll. There’s even a great movie if you don’t want to read the book, but the book is really much better and goes into more detail on data points and how they are used.

The idea of using data to make decisions has been applied to other areas, with “The Moneyball Effect” being talked about in other industries. Recently I also ran across an opinion piece on bad managers that also referenced Moneyball. The piece notes that most people make poor managers. They lack the skills, and more importantly, they really lack those innate qualities that motivate, inspire, and engage employees. Whether you agree with that last part, I think most of you agree that most managers are poorly chosen, trained, and certainly not qualified.

The idea of using data to identify people that would make good managers, and perhaps even move people out of managerial roles. The premise of the piece is really the bad managers make their teams perform worse, so if you’ve got one of the seven-out-of-ten people ill suited to the work, you should move them out of that position. Then identify, promote, train, and support the others to manage your employees and help them to perform at their best.

Steve Jones

The Voice of the DBA Podcast

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

T-SQL Tuesday #61 – Giving Back

tsqltuesdayIt’s that time of month again, the time when we have a day where everyone writes on the same topic.

This month Wayne Sheffield is the host and has chosen Giving Back as his theme. It’s a good one with the holidays and it’s one that really inspired SQLServerCentral. We built a business, but one that was founded on helping others and giving back to the community. The same could be said for SQL Saturday as well.

Volunteering

First the soap box. Everyone should give back to the world at some point in their lives. Perhaps it’s when you’re younger, perhaps when you retire, maybe it’s this weekend, but you should volunteer to make the world a better place.

Now for my plans for giving back.

They won’t change a lot from the past few years. I look to speak at various SQL Saturdays and User Groups in 2015. While Red Gate funds my travel and sometimes asks me attend events, I choose many on my own. I also donate my time as I don’t get a shorter week when I’m delivering a talk or two on Saturday. I still have the same responsibilities and deadlines to manage during the week.

In 2015, I’m going to try and get to 8 SQL Saturdays. I did 11 in 2014, but I’m not sure I’ll end up with the same number. We’re still planning out 2015, and I don’t have any SQL Saturdays in the first quarter, but I’m hoping to make up a few later in the year.

I also plan to get to each of the Denver area user groups in 2015. I managed to speak in Boulder, Denver, and Colorado Springs in 2014 and I hope to do the same in 2015.

SQL Server Disk Space Emergencies

One of the things I’ll see happen often with SQL Server instances is that the system will run out of space on a drive. This could be for a variety of reasons, some of which can be prevented, and some cannot. You might have:

  • Don’t delete old backup files
  • Data growth fills the disk over time, usually years
  • tempdb rapid growth that uses all space
  • old import files not deleted over time

There are other reasons, but I’ve often found that some process will cause an emergency and the SQL Server stops working, or stops backing up database, and administrators are in a panic to free space so the server can continue to function.

Here’s what I suggest to smooth the way with a series of placeholders and a job.

Create Placeholders

First, create a folder on your SQL Server (or really every server) called Placeholder. I’d put it in the root to make it easy to find and standardize on it.

placeholders3

In the folder, place a series of files to save space. If you don’t know how to do this, I can show you an easy way. I have 4GB reserved here.

placeholders4

Now create a SQL Server Agent job. I might standardize this on every server I have with the same name and path.

placeholders5

The job has one step, which is designed to delete one file, each time it’s run.

Note that I had a slight bug in what I shot above. I had the contig.exe utility in the folder and the first execution of the job deleted that file. Not a big deal in an emergency, because I can run the job again, but I’d make sure that only the place holder files are in this folder on machines.

Here’s the job. It’s a PoSh type of step.

placeholders6

The actual PoSh code is here:

$fileEntries = [IO.Directory]::GetFiles(“d:\placeholder”);
$delete = 1;
foreach($fileName in $fileEntries)
{
if ($delete -eq 1)
{
Remove-Item $fileName
$delete = 0;
}
}

When I run this, each time I run it, it’s just a single click or sp_start_job call.

placeholders7

After it runs, I have 1GB more free space. If I need more, run it again.

placeholders8

However, once you clear your low space condition, I’d be sure I put the placeholders back.

For the next emergency.

Holiday Struggles

It’s the slow time of year. As I saw tweeted recently, “And now for the month when nothing gets done.” I haven’t usually felt this way at a few jobs as the budget and planning process has typically been a part of a busy month. As I’ve run SQLServerCentral, I’ve also been looking back and forward this month, trying to think about our industry and data in a long term way. I have found December to be frenetic as times as I try to get in some ski days while still getting work done.

However this year I’ve been in a situation that I think many people get into. I had a number of vacation days I needed to take, so I scheduled them a day here and there across the weeks. Between being off for Thanksgiving week, and missing almost the complete last two weeks of the year, I’m finding it a bit difficult to get into the swing of things as I have a few days back at work. It seems that work naturally slows down in teams as various people take holiday time off, but this is the first time that I’ve struggled to actually get my own work done as I’m in and out of the office on an irregular schedule.

Perhaps this is the time when those of us who don’t work in Christmas related industries can catch up on work that we are often too busy to do otherwise. Are there maintenance items you can schedule? Maybe tackle some in-depth query tuning that is needed for end of year processes? Is it the time to “scratch an itch” at work? Red Gate has their Down Tools Week scheduled this week, and it’s a time when people can tackle fun projects that improve the business, but are rarely worked on. Our SQLServerCentral Scripts plug in for SSMS came out of one of these weeks.

Pitch an idea to your boss to take a week or two and dig into something that is needed, but never prioritized. Maybe you’ll get your wish as a Christmas present.

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.

SQL in the City 2014 – Washington DC

I’m off today, traveling to SQL in the City 2014 – Washington DC to meet Grant and deliver another database delivery seminar. We’ll be talking about Version Control, testing, continuous integration, and database delivery, and showing off some of the Red Gate tools that can make the process easier.

I really think that you will reap benefits if you start to build a software delivery pipeline and incorporate some of the agile/ALM/DLM processes into your software build system. It’s some work up front, and maintenance ongoing, but it does allow you to regression test, rapidly review changes, and consistently deploy software to your production systems.

Not easy, but it does build solid engineering habits and should help you deliver software reliably, and quickly.

We’re looking at doing more seminars in 2015 and are planning things now. If you’re interested in having us some to your city, perhaps around a SQL Saturday, or maybe just because you don’t have any events, send a request to Red Gate. Use sqlinthecity@red-gate.com and let them know you want Grant and myself to swing by.

What Have You Learned Lately?

Recently we had the 60th T-SQL Tuesday blog party. This was hosted by Chris Yates, and had the theme of Something New Learned. That’s a great topic and a lot of fun to write about.

There were some great posts, and while it’s good to see people participating, it’s also the chance for everyone in the community to learn a few things. I’d bookmark some of these posts, and across the next few months, read one and dig into the topic a little. Do you know about the PSR in SQL Server? Do you know how to determine who has access to what in your SQL Server?

These posts, and many other posts written for the various T-SQL Tuesday events are a great way to dig into a particular topic area, learn a few things and help guide your learning. There have been a few times that the topic itself has gotten me to experiment with some aspect of SQL Server prior to writing something, but you could just as easily go back and look at previous topics and start improving your learning.

While there are many sites like SQLServerCentral to help you learn new skills, it can be fun to pick a single topic and dive in for a few weeks, practicing using the skill to solve some problems. This might be a great way for those of you that aren’t challenged at work to gain some new skills.

I’ve had far too many friends find the need to unexpectedly look for a new job in the last year. I never take my employment for granted, and it’s one reason I try to continue to learn more about my craft, and build skills that might be in demand. After all, you never know when your company might make a change and you’ll need to look for new employment.

Steve Jones

The Voice of the DBA Podcast

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

SQL Injection Issues–Password Hashing

I’ve got a demo for one of my talks that really highlights some issues we have with SQL Injection. It’s part of my encryption talk, and it goes like this.

NOTE: I am showing a simple example here, not one that I would deploy into production. The concepts are similar, but this specific code is not designed or applicable for cut/paste into a production system.

Imagine I have a simple table of users and passwords.

go
create table UserTest
( firstname varchar(50)
, passwordhash varbinary(max)
);
go
-- insert passwords
insert usertest select 'Steve', HASHBYTES('SHA2_512', 'AP@sswordUCan!tGuess');
insert usertest select 'Andy', HASHBYTES('SHA2_512', 'ADiffP@sswordUCan!tGuess');
go

I’ve got two users and a fairly strong hash of their passwords. I’m using the SHA2 algorithm, at 512 bits, and complex passwords. I’m showing this in T-SQL, though you could easily hash these passwords in the application layer and just store the values in the database.

I create a simple proc that takes a username and a password as parameters.

create procedure CheckPassword
   @user varchar(200)
 , @password varchar(200)
as
if hashbytes('SHA2_512', @password) = (select passwordhash 
                                 from UserTest
                                 where firstname = @user
                                )
  select 'Password Match'
else
  select 'Password Fail'
  ;
return
go

NOTE: This is shown at the DB layer for simplicity, but having a user’s password transit the network in plaintext and be passed to a proc is a poor practice. It would be better to hash this and only send the hash to SQL Server.

If I want to verity a user, I can do this:

declare @p varchar(200);
select @p = 'AP@sswordUCan!tGuess';
exec CheckPassword 'Steve', @p;
go

The result of this call is the password matches.

pwd1

If I try a different password, say the one for the other user, it will fail.

pwd2

That’s good. This is very similar to how many applications, including AD and SQL Server, validate users. However, here’s one problem with a simplistic implementation like this.

Imagine that through SQLInjection, someone learns the structure of the table. Not hard to do. Now the data in the table is hashed, and there are lots of hashing algorithms. Certainly it’s a lot of work to try all different combinations of possible passwords, and algorithms to find a match. It’s possible and it’s a brute force attack.

Here’s the data in the table.

pwd3

However, the hacker, Andy,  doesn’t need to decode the password. Imagine that the hacker creates his own account, which is probably a low level account. However the hacker runs code like this, substituting different accounts for “Steve” until a privileged account is found.

pwd4

Now the attacker does this. They use my (Steve’s) privileged account, with their password:

pwd5

The hacker (Andy), can now log in as Steve using his password. Any rights that are assigned to Steve are available for Andy.

We have an attack without decryption.

This is one reason that SQL Injection is a big problem in applications, especially those that implement some type of their own security. Solving this is slightly tricky, and I’ll talk about it in another post.

One side note, the only way this is usually detected is if Steve logs in with his password. He’ll see this:

pwd6

Even then, unless Steve suspects an attack, he might write this off to a mistyped password, try multiple times and eventually reset his password without a second thought.

T-SQL Tricks – Customizing SSMS Templates with Parameters

I wrote briefly about templates in Management Studio (SSMS), and showed the default templates that come with SQL Server. I now want to customize some of the templates in a way that makes sense for me.

If I grab a script I use often, like this one, I can make it generic.

SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = 'bob@bob.com'
    GROUP BY
        username
    ,   topic;

I run this often to check things, but I rarely need Bob’s information. Instead, I’ll often get different users, and sometimes I need dates. I can add these changes:

SELECT
        username
    ,   topic
    ,   COUNT(replies)
    FROM
        users u
        INNER JOIN posts p
        ON u.userid = p.userid
    WHERE
        u.email = '<email, varchar, bob@bob.com>'
    AND startdate > <startdate, datetime, dateadd(m, -1, getdate())> 
    AND enddate <lessthan, char <> <enddate, datetime, getdate()>
    GROUP BY
        username
    ,   topic;

I’ve changed some of my variable items to parameters. I do this by taking an item that I want to make variable, like “bob@bob.com” and changing it to “<email, varchar, bob@bob.com>”.

The format for a template is:

  • name
  • type
  • default

all of which are placed inside angled brackets and separated by commas. Now when I click CTRL+Shift+M, I get this:

templates16

I can click OK for the defaults to be placed in the script, or I can enter new ones. Either way, I save time and effort with saved queries, but saved as templates, not queries I need to edit constantly.

UPDATE: Someone pointed out that the less than, the <, was . I got this from Stack Overflow, which had a good solution. I made the < a parameter as well.

Always Canary

Deploying software is hard. Despite decades of experience working to update software smoothly, it seems that many of us tasked with that responsibility find ourselves nervous about making changes. This is especially true for database changes, where we must not only update the schema and code, but we must preserve the data as well. If only we could drop and recreate the database during every deployment.

One of the ways in which you can make changes in a live environment is to have a second system already in place and running. With a second system available, you can stop the secondary system from handling user requests, make changes, test them, and if things are working well, you can have users connect to the updated system while you take the one one offline and perform your updates there.

This is the same process that many people use to patch clustered database servers, though the “rolling upgrades” are usually done for patches from Microsoft. It would be great if we could easily apply this same system for our schema upgrades. Deploy our changes to the passive node, have the application able to connect to this node through some switch, test the changes, and if things are working, switch users over. The problem is that our databases on the passive node aren’t live.

Really the ability to deploy database changes to a live system and perform checks requires a strong architecture in both the database and application to support this. Your code must be able to handle additions to the database objects without breaking. The use of feature flags, turning functionality on and off with switches, allows certain users to test new features without most clients being aware of the changes.

It’s a pattern that more and more software companies are using when they want to deploy changes rapidly to systems for limited numbers of users. However it’s not a design pattern that is widely used. I hope we get more sample applications and reference architectures in the future that will help developers and DBAs code more robust and resilient applications, allowing changes to be deployed live without affecting every user.

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. feed

The Value of Code

It seems that many businesses can operate with a large margin for error. Managers can make some poor decisions, processes can be a bit inefficient, and employees can be disengaged, yet companies can still turn a profit. Non-profits, and certainly government organizations, can run without optimizing their work, much to the chagrin of many. However I continue to find companies that look for ways to not only become more efficient, but also to fundamentally alter their business models by better understanding their particular market. They can turn profits (or operate efficiently) far in excess of the averages for their industry with one tool: software.

There’s a belief in many of these highly optimized companies that data contains lots of value, but in order to understand and harness the information contained within data, an enterprise must be willing to invest in better software. That requires better software developers. In a forward looking enterprise, that means lots of opportunity for those of us that work with data and build software.

Those of us that create technical solutions need to learn to better interpret data and find ways to help our clients find and understand the patterns revealed by our software. Those of us that primarily manage data, need to better understand how software renders and showcases information compiled in our databases. We truly have the ability to reshape the way organizations run with our technological skills.

Computing power continues to grow, accessibility to information improves, and our enterprises become more dependent on our systems. Now is the beginning of an era where we will have tremendous influence in helping change the way business works. I’d urge all of you to continue to look for new ways to manipulate data and find ways that showcase the value that software can bring to your organization. Even if your current company doesn’t value your skills, I bet another one will sometime in the future.

Steve Jones

The Voice of the DBA Podcast

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

Follow

Get every new post delivered to your Inbox.

Join 4,956 other followers