Voice of the DBA

Category: Editorial

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

  • Moving to Query Store

    In SQL Server 2016, Microsoft introduced the Query Data Store (QDS) as a tool that would capture data about the execution of queries inside of your database. This was a project that had been in the works for a number of years, and one that many of us that were bound by NDA agreements had been following. We were excited by the chance to actually gather some information on the.

    Are you using Query Store? You should be, as this tool will become more valuable over time. I know that there are potential overhead issues (3-5% for most people, but possibly larger). I would argue that the potential for better performance and understanding of our systems outweighs the overhead. After all, if we’re unwilling to devote some resources to measuring our systems, how do we really know what to improve?

    We upgraded the SQLServerCentral servers to SQL Server 2017 this year (2018), and I’ve been wanting to enable the Query Store. I’ve been slightly hesitant with over 75,000 air miles and 5,000 driving miles on the road since the upgrade. Being distracted and out of my routine isn’t the best way to document and carefully observe the effects of a change. Not to mention concerns over data leakage for a company bound by the GDPR. After a little discussion and debate, and my schedule slowing, I’m looking to change that soon.

    I don’t expect that a lot of improvement at SQLServerCentral from changing this, as our third party forums and much of the internal code is batch SQL, and quite a bit generated on the fly. However, there are some stored procedures, and I might be very wrong. While we’re over provisioned with resources to avoid any performance problems, I do expect that we’ll learn a few things. I hope we find places to better tune code, and with some documentation of the process, hopefully some of you out there might spot things our team doesn’t.

    If you’ve got stories of the QDS working well or not well, let us know. Certainly let Microsoft know as well. The QDS is a major part of the SQL Server platform improving in the future and there are enhancements in SQL Server 2019. While I don’t know that the QDS and some of the automatic tuning features remove the need for a data professional to watch a system, I’d like to think they do provide opportunities and insight for how we might better structure and develop applications, as well as help us find better patterns that are useful in our initial database coding.

    If you’ve got stories, Erin Stellato wants to know (and she has a few in the post). If you’re concerned about overhead, read her other post. If you’re confused, we’re working on some articles to help you learn more. Give the QDS a try, especially if you’ve got some less critical systems. Part of our job is learning how to use new tools, and this is one that ought to be on most DBAs ToDo list.

    Steve Jones

    The Voice of the DBA Podcast

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