Tag: software development

  • The Design Investment

    This editorial was originally published on Sept 29, 2010. It is being re-run as Steve is traveling.

    I ran across a bit of a rant from Don Halloran on the lack of design effort being made in many applications and software. He talks about a lack of consistency in databases, confusing column names and datatypes not used correctly. Don laments that “It seems database design doesn’t get much respect, and I really don’t understand why this is.”

    We all probably have some idea of why systems get built like this: the designer is ignorant, or lazy, or maybe doesn’t pay attention because it’s not part of their job. Any reason could fit, but ultimately it comes down to someone taking on a job without making the investment in their own career.

    If I were putting tile in my own bathroom, I accept a few mistakes, and I might cut some corners. I might be happy to accept a little fumbling around while cutting tile. I would not, however, put up with that if I were paying someone. I would expect a more professional job. If I decided to change careers and lay tile for a living, I’d also expect to make an investment in my own skills to learn how to do a professional job.

    I understand that developers are paid to write code, and that they are more interested in manipulating objects, methods and properties and implementing DR or indexing. If you plan on working with SQL Server, it pays to make the investment in learning how to do it properly. If for not other reason than because you should consider if part of your professional responsibility.

     

    Steve Jones

  • No State Based Deployments

    I’ve been studying deployment ideas and technologies for quite some time now, but it’s been a focus for the last year or two as my employer is interested in this area. I’ve been reading books and talking to people, comparing their experiences with my own. I’ve found that deployments for databases are much more complex than those for other software, but essentially there are two approaches that people choose: migrations and state based migrations based deployments.

    Migrations based deployments are based on tracking each change to a database in a separate script and then executing each script, in order, on your production database. This is hard to do, requires discipline, and is what most people find difficult to do. Scripts get quite numerous, ordering is hard, and deployments become scary and largely unsuccessful without a lot of care. The scripts require tweaks and fixes to work efficiently, and you need smart DBAs and developers.

    State based deployments are what many people try to use. They look at the state of production, the state of development, run some tool like SQL Compare, or a process, and determine what changes need to be made to production to get it to match development. There are variations, but this is the basis for how many people try to create a deployment process. It seems easier.

    The problem is that at some point a state based deployment process won’t work. It’s actually impossible for a state based approach to work in all situations. Simple actions, like renames, can’t be handled by examining two states. In order to properly manage changes, and keep your data intact and safe, you need to understand “what” happened during the process, not just the end result.

    In many situations, state based deployments can work, and they can be used successfully, but there is always the possibility that you’ll need to customize your state based approach to include some migrations scripting. Keep that in mind, and make sure that any process or tool you choose has that flexibility. If you understand you need to review and modify scripts at times, you should be successful.

    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.

  • Building Small Habits

    I was reading Kenneth Fisher’s piece on constraints recently and was struck by the fact that this is one of those small changes you make to your coding that has big benefits. In essence, rather than being lazy and writing this:

    CREATE TABLE mytable ( myID INT PRIMARY KEY, MyCol ....

    You decide to explicitly add a few more characters. You note this is a constraint and you type this:

    CREATE TABLE mytable ( myID INT CONSTRAINT PK_MyTable_MyID PRIMARY KEY, myCol

    It’s a small change, but it takes you from an object named PK__mytable__783FBF31F8293E74 to one named PK_MyTable_MyID, which is way easier to understand and debug when you have issues. More importantly, this is a DevOps move, where a developer can make it easier for operations people to understand the constructs and organization of a system that has moved to a production environment.

    There are all sorts of little coding changes you can make that improve the way you write code, and perhaps make it perform better. Aaron Bertrand has a whole series of bad coding habits, and at SQLServerCentral, we’ve published a number of pieces on different ways to code better, such as removing cursors from your code.

    By making some small improvements in your coding habits, changing to better techniques over time, you’ll slowly improve your entire codebase and help yourself and other developers code more efficiently in the future. I’d even recommend as a team that you choose a single change to make, and begin implementing it across the next month or two. Then repeat the cycle again. In a year, you’ll be amazed where you are.

    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, Still?

    It seems as though SQL Injection issues are still around. Attacks from SQL Injection are on the uptick as we begin moving through 2015. As noted in that piece, the constraints put on software developers usually mean that testing and security are the first things to let go when time becomes short. That’s sad, and in some sense, I think this means that we aren’t teaching secure coding early enough to developers, and certainly not often enough.

    I ran across a piece from Kevin Kline that asks why this keeps happening. After all, as Kevin shows, much SQL Injection is easy to prevent. The coding patterns and tools we use are simple to write. There are lots of articles out there that show a variety of techniques you can cut and paste into your code. However there are two big problems that prevent us from eradicating SQL Injection: aging code and bad habits.

    There’s no shortage of code that comes from frameworks and application templates, not to mention naive or ignorant developers. Lots of this code is vulnerable to SQL Injection. Since so many of these existing applications work, there is no great pressure to go back and change them to be more secure. Since data theft may not even be noticed, there are plenty of companies (and technologists) that have no idea their systems are vulnerable.

    The other problem is bad habits. Far too many developers and DBAs have spent years writing insecure code. When they prototype, mock, or quickly knock out code, they often rely on their experience to get work done quickly. And they do this poorly if they aren’t writing in a pattern that prevents SQL Injection. They haven’t updated their templates, tools, or their knowledge in a way that ensures all their future code will be secure.

    Ultimately we as an industry need to take SQL Injection seriously and write better code. Whether you use an ORM, a framework, or anything other shortcut to build applications, if you don’t create secure software, you’re part of the problem.

    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