Voice of the DBA

Author: way0utwest

  • Tougher Privacy Laws

    I am all for tougher privacy laws, especially for companies that have not followed basic security practices for securing data. There is a proposal from US Senator Ron Wyden that would increase penalties and give more rights to consumers. Consumers could opt out of data sharing and executives could be fined or jailed. The penalties are stiff, and I think it’s not likely to pass, and more practically, many of the penalties might not actually get enforced.

    In the US we don’t have much in the way of rights over our own data as humans. Companies, for the most part, have complete control over the data they collect about us and can re-use, sell, share, etc. that data in any way they wish. There are some laws concerning notifications of data loss, and some penalties in California’s recent law, but for most of the country, consumers are at the mercy of organizations. I’d like that to change, and I don’t think doing so would hurt most businesses. Aggregators and data only companies might struggle, but I’d like to see less of those companies in business.

    Stronger penalties might stimulate change and better practices, but only if we fine or jail those that limit security efforts. Most technical people try to implement security but are often prevented or limited from making many changes when there is pressure to keep moving forward. Certainly some technical people don’t take security seriously, but I’d like to see employees absolved of responsibility if they show that they have asked for time or resources for security, but those aren’t granted. I’d also like to see some way for management at all levels to prove they have actually requested and funded security efforts, not just remain ignorant of the lack of security. Too many layers of management muddy the waters and often prevent those that are responsible for pushing other work over security from being held accountable. We need more accountability at all levels for poor security.

    Likely there is a limited amount of structure that government can provide. Developers and infrastructure groups need to build and configure secure systems. Some funding needs to be available for security work, along with the time to do better. Management needs to make security a priority It’s a group effort and while I hope we can get there, I’m not terribly confident things will improve soon.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Take the State of DevOps Survey

    Kendra wrote a nice piece on this the other day, and I agree.

    Take the Survey

    The data that comes out of this helps to influence executives and managers. You can show them results, which might get them to help you change your job.

    This is also a way that we can share information with each other. Are others doing their jobs better than you? Can they actually get code tested and reliably deployed? Are most people using VCS?

    Or maybe not.

    Take the Survey

    Share some information and learn something back. The results will be available online, and you can use them however you like to convince your organization to make changes.

    We don’t manipulate the data, and we’ve hired a company that specializes in doing this, so we can try and get accurate accounting of the data.

    Take the Survey

  • Adding the Constraint Name to the PK at the End of Create Table–#SQLNewBlogger

    Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

    A good habit to get into is to explicitly name your constraints. I try to do this when I create tables to be sure that a) I have a PK and b) it’s named the same for all environments.

    I can create a PK inline, with a simple table like this:

    CREATE TABLE Batting
       (
            BattingKey INT NOT NULL CONSTRAINT BattingPK PRIMARY KEY
            , PlayerID INT
            , BattingDate DATETIME
            , AB TINYINT
            , H TINYINT
            , HR tinyint
       )
    
;

    This gives a primary key, named “BattingPK, that I can easily see inline with the column.

    Not everyone likes this, and I do run into clients and customers that want the keys separated from the column. This is fine, and I understand that this explicitly calls out the keys separately from the column.

    This is an easy change to my code.  I move the CONSTRAINT part to the end, as a separate item in the column list, and add the column(s) that I want to use in the constraint.

    CREATE TABLE Batting
       (
            BattingKey INT NOT NULL 
            , PlayerID INT
            , BattingDate DATETIME
            , AB TINYINT
            , H TINYINT
            , HR TINYINT
            , CONSTRAINT BattingPK PRIMARY KEY (BattingKey)
       )
    
;

    As you can see, inlining names for constraints is pretty easy, and it’s a good practice to get in the habit of adopting.

    If I didn’t do this, I’d get a system generated name, which is fine, but the constraint name would then be different on every system where I deployed this object. Since I often want to test something on one system and deploy on another, future coding gets much more complex than it is by just doing this from the start.

    SQLNewBlogger

    This was a quick 5 minute post for me, following a short session teaching a client how to add the constraint to their table code.

  • Mitigate Issues Early

    This is a great postmortem from Basecamp with a detailed explanation of a problem and how they are hoping to avoid issues with their service. Basecamp is a Software as a Service tool from 37 Signals that had an outage recently. Actually, the service was up and could be read, but new items couldn’t be added and existing items couldn’t be changed. That somewhat defeats the purpose of the tool. 37 Signals recognizes this and notes that they’re calling this downtime and not trying to get pedantic about the state of the service.

    I have sympathy here because I’ve gotten the same error in SQL Sever. I’ve reached the point where I had 2,147,483,647 items in a table. Any additional items caused an error, which some of you might guess. This is the largest positive INT value and if you’ve started at 0 and incremented all new rows, when you reach this mark, you can’t add more data. There are a few fixes for this, including moving to negative numbers or changing to a bigint type.

    The staff at 37 Signals knew this was an issue and had actually set a new default for Basecamp that used a bigint in MySQL and PostgreSQL. What they didn’t do is actually make the changes in their hosted Basecamp database because they thought they had more time. I’ve seen that before, often when an application runs at a higher workload than expected for a few years and DBAs aren’t paying attention. When staff least expects it, the INT will reach the max value an clients start complaining. I’ve had to respond to an urgent phone call about this in a few companies.

    If you use INT values that are being incremented in some standard way, I’d likely set some alert process that triggers when the value gets to 2 billion or so, depending on how fast you use the numbers. SQL Monitor can do this with a custom metric, and I’m sure other software can as well, including SQL Agent. Having some monitoring in place will help you plan and be prepared to address issues before they cause problems.

    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.