Voice of the DBA

Author: way0utwest

  • Understanding a Database

    I ran across a post that asked a good question, one which I want to ask you today: how do you learn about a database?

    I’ve run into quite a few databases in my career. Some were third party systems, like Dynamics and JD Edwards World. Some were databases that custom designed and built by developers and database modelers of widely varying skills. Some were well built in order to normalize data and define referential integrity, and other databases were put together in a piecemeal fashion over time, lacking keys and consistent naming. I’ll leave it to you to guess if there were more of the former or the latter.

    When a developer or DBA comes across a database, what’s the way that they can decode what fields and columns mean? Certainly names help at times, especially when the purpose of the database is understood, but all too often the names don’t quite make sense. This is especially true in many vendor databases. The one common theme I’ve seen in many databases is that there is no data dictionary provided by anyone.

    Trying to understand a database has been a trial and error detective task for me in the past. Usually this starts when I need to do some work that is requested by users: write a report, change data, etc. In these cases, I often will ask users to access certain data related to the change from their application while I run Extended Events and note which entities are accessed. I can then start looking for data elements, and note which columns might be mapped to which fields in an application.

    Often I’ve built a data dictionary of sorts outside of the database using something like ErWin, ER/Studio, or another tool. That has been somewhat flawed, as it’s hard to share the information with others. These days I think I’d make extensive use of Extended Properties to document what I learned, so that all my knowledge is available for anyone else that needed to work on the system. They can just look at the properties for various entities.

    If you’ve got other methods, share them with us today. I’m sure there are plenty of DBAs and developers out there that would like some tips and tricks for decoding a database design.

    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.

  • SQL in the City December 2018

    The last 2018 edition of SQL in the City Streamed is coming in a few weeks. You can register today and join us for a set of talks on December 12 that talk DevOps, compliance, and more.

    Steve-email signature

    I have to deliver the keynote, which I’m working on now, taking information from other reports, conversations with customers and others, and trying to meld together a summary of where we are with Database DevOps in the world, as well as how compliance in influencing our careers.

    There are also sessions on deployments, security and more. We have a few covering Redgate tools and even a look at a few developers that are building the software we sell.

    Join me and register today for a nice break from work.

  • 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