Voice of the DBA

Category: Editorial

  • Email or Username

    Quite a few of us will work with application databases where we need to identify a particular user for working with the various parts of the application. I’m not talking about the authentication and verification of identity, but just having a token to identify a user once they’ve been authenticated. I don’t want to talk security here since most of you aren’t great at building these systems and should just implement an authentication scheme that’s been vetted (OAuth, Live, etc).

    However, once you get a way of authenticating a user, how do you identify them in the database? Many people store a username, but there are issues with these. They aren’t consistent across systems, a user might find theirs taken in one application, and then needs a second one for the next application. This leads to the complexity of people needing a way to retrieve their username, which can be a whole separate set of application programming pain. Often the default is to move back to an email associated with the username.

    However what does someone do when they don’t have access to that email? Therein lies the issue with using email as an authentication mechanism. I use my sqlservercentral.com email for many things, most of them related to this site. However, if I were to ever leave this job, then I wouldn’t be able to access some sites if I couldn’t remember the login information. Perhaps I shouldn’t be using this email, but there are some services that are related to both my career as a DBA/developer as well as my position here at SQLServerCentral.

    Plenty of people have a separate email for personal use, but not necessarily many users of a generic application. There are plenty of people using their work email for registration at sites, assuming they’ll always have access to the application through a username of some sort. You might even change your email, deciding at some point that coolteenager@hotmail.com isn’t the one you want to give out for your kid’s school registration. In that case, if you’ve used that email at a variety of places, then what can you do?

    PASS recently changed to using email instead of username, which is fine. I think more sites use email, but in our case, as DBAs and developers, are emails less likely to change? Should we be sure that our PASS email is always a non-corporate email so that we can keep membership as we change employers? I’d think username is more stable for people in technology, and it can be fun. I love my way0utwest moniker, and try to ensure I reserve it on many applications I may want to use.

    There’s no good answer here, and there are issues with using either one as an identifier, but what do you think? Are there compelling reasons to choose one over the other? No matter which one you use, I think the need to have multiple ways for users to gain access to their account is important.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Democratic Data Science

    I know quite a few people that are jumping on the data science bandwagon, brushing up on statistics, learning R, and hoping to land one of those lucrative data scientist positions. I wish you good luck, and some of you will succeed, but I wouldn’t count on there being lots and lots of data science positions in the future. The work is hard, and it will take a lot of learning and practice to become good. Plus, perhaps we won’t need as many data scientists as some are predicting.

    There seems to be a lot of work being done with intelligent agents and “bots” that can perform some lower level analysis of data and interact with humans. However, there is also work being done with more capable software that might analyze data and find patterns by applying different algorithms and analysis to make it easier for business analysts to comb through the mountains of data. After all, the growth of IoT, of more metrics and measurements being made in all kinds of applications mean that we can’t just throw bodies at problems. We need better ways to work with large amounts of data.

    This work isn’t really aimed at eliminating data scientists or data analysts. Rather, this is aimed at trying to free up their time to do the creative, thoughtful work of examining data. The grunt work of playing with algorithms and testing them according to some measure of success is handled by software. I think that this is a mantra that will be repeated over and over in the next couple decades in computing. Grunt work is automated, thoughtful work is done by (well-paid) humans. If you’re not doing the latter, you might not have a job with the former.

    I do think there is still plenty of opportunity for data professionals, after all, we need to manage all that data, find ways to ensure it’s available and can be queried, data is somewhat clean, and certainly that we have some idea of what the data means to our business. I’m not sure software agents will easily be able to set those things up, though certainly they’ll do the work of applying our rules to large sets of data.

    There will still be plenty of data science roles, and certainly humans need to be watching the software to be sure it’s working well. There will always be tuning efforts, and perhaps always reports that users need help in building. However, as software becomes more sophisticated, there will certainly be less grunt work in our industry, just like many other industries have seen lower, or at least lower mid-level positions reduced in number. We should be prepared to prove our value and ensure our skills are useful to our organizations.

    Steve Jones

    The Voice of the DBA Podcast

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

  • DevOps and Security

    DevOps is a buzzword these days, and like many of the hyped concepts written about, it has a lot of meanings. There is this idea releasing software more often, using automation, having various groups talk to each other, and more, all of  which we might see as common sense. However other DevOps ideas, such as releasing more often (with potentially less review), being willing to break applications and fix them quickly, having developers able to release code to live, production environments, these don’t seem to be ideas that would enhance security for most organizations.

    However, that’s not necessarily the case. Security and DevOps practices aren’t necessarily mutually exclusive. I ran across a piece from the security perspective, looking at some of the ideas in DevOps that can actually enhance security.

    Writing more code, especially around the configuration and infrastructure, allows versioning, auditing, and more that can ensure we have fewer mis-configured systems. Adding some Desired State Configuration (DSR), with some automated testing of this code, could ensure that the changes made don’t open up security holes. Or, at least, allow us to determine who made the change and when the issue appeared. These are important for understand security risk.

    There are also the ideas of measurement, metrics, and feedback, which are important for ensuring security. After all, anomalous behavior should be investigated, as this could be a sign of intrusion. For databases, it’s especially important with the large number of clients that connect to our systems. Adding DevOps style monitoring can allow us to determine if a workload is normal, or perhaps a sign of intentional, accidental, or malicious data query activity.

    I enjoyed the piece, and I’d recommend you read it. Plus, whenever I see “snowflake” in an article, I think of Grant and want to read further to see how someone else has used the same analogy he does.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Have You Designed a Database from Scratch?

    One of the things that I try to regularly promote is the #SQLNewBlogger project from Ed Leighton-Dick (and #SQLNewBlogger hashtag on Twitter). In keep a column open scanning for the tag and try to respond to those that post. I also write a series of posts under that tag, mainly beginner type posts, that showcase things I think are good, basic topics that I’d want a DBA or developer to know about SQL Server. These are exactly the types of posts that you write for your next resume (or next hiring manager).

    I saw recently someone begin their blogging journey with a new post about designing a database. What struck me was this person with a lot of experience noted they had never designed a database from scratch. I’m sure that most of you have worked with a database, and have even modified various objects inside of a schema. Certainly you’ve changed the code of stored procedures or functions. I’m even sure many of you have modified tables, adding columns where you need them. After all, that’s a favorite first technique for many people that need a place to store data.

    Probably the majority of you have built tables, at least temporary tables. I’m sure you spent a few minutes thinking about the columns, datatypes, etc. I’d also hope that most of you have added indexes and constraints to tables. Those are certainly skills that more people should better understand, especially the developers that work on software powered  by SQL Server. Above all, please learn how to index OLTP tables.

    However, have you designed a database from scratch? Not a standalone database, but a database the actually has an API or application built against it. Have you tried to use the database and found places where your modeling was lacking? Did others complain about your design?

    I know that lots of school coursework requires people to design parts of a database, but what about a full, complete database. One that fulfills the requirements to actually manage a set of data well? I’ve done a few, though admittedly, with input and help from other DBAs and developers. I am better for that, and I think that a good design requires some interaction and input from others.

    With that in mind, whether you’ve built one or now, what about tackling this as a project for your own blog? Certainly there are some good problem sets out there, but I’d actually give you a new one if you’d like. The SQL Saturday site for PASS has a database behind it. How would you design a database, given the requirements that you need to support that site, and actually generate out the XML feed for the Guidebook app? Maybe that would be a fun, multi-part series that looks at your decisions for the various entities required. You’d learn something and get lots of feedback.

    If you’re like to publish a series here at SQLServerCentral, let us know. We’d love to have some design series on databases that back applications, especially if you have a side project with an application.

    Steve Jones

    The Voice of the DBA Podcast

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