Voice of the DBA

Category: Editorial

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

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

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

  • Building Better Training Opportunities

    Recently I wrote a piece about some advice on quitting over training budgets, or the lack thereof. It had some interesting comments, but one stood out to me because it’s something I’ve heard and seen before. One of the readers noted that their company had bought a subscription for employees to learn new skills, but few of them had taken advantage of the courses.

    I’m not surprised. Many people are tired at the end of the day. Many people are fairly satisfied with their jobs. They’re not great, but not horrible, and we enjoy solving problems most days. Most of us would like to continue working where we are, in a stable situation. Not all of us, but many of us want to go to work and get paid for doing so, without a lot of motivation to do more.

    That’s fine, and I understand the pressures and stresses from the rest of life that weigh you down. I do understand that there are times that most of us would like to get away from work when we can and enjoy time with family, hobbies, faith, and more. My advice is that those things are important, but so is your career. Make some time to improve and grow your career and skills, even if you plan to stick with your current job. You never know when things will change.

    With that in mind, I think buying a subscription to Pluralsight or some other training option without providing any motivation or incentive is a poor plan. There needs to be some goals or expectations, and hopefully some sharing of the learning experience.

    If you need motivation, or you want to motivate co-workers, what about a competition? Challenge each other to complete a module of a course and write some code. Whether that’s to script installs with Chef, create a CI pipeline, count words with Python, or write faster T-SQL, if you have a goal, you’ll do better. I’ve had companies where we scheduled group watching of training, and with a small competition at the end, we found more people to be engaged and active in their learning.

    You could even make this a part of the review process, though please don’t just make this a checkbox. Ensure that if you want employees to learn something, you ask that they complete a class and contribute something back. Teach others, build something useful, or benefit the organization in some way.

    When there is a little more motivation, there’s a little more effort, and that can go a long way towards improving the skills of your workforce.

    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.