Voice of the DBA

Author: way0utwest

  • What Data is Really Needed?

    The GDPR took effect in May of this year, at least with regards to enforcement. A few days after the May 25 date, a German court ruled against ICANN, the company that registers domain names on the Internet and manages the global WHOIS database. The case revolves around the information collected when you register a domain. ICANN wants multiple contacts, which they’ve required for decades. However, a company in Germany that is a partner, argued that the additional technical and administrative contacts were not required for fulfilling the business that both ICANN and EPAG (the German registrar) are engaged in. ICANN Is appealing the ruling, citing the need for clarification of what this means with regard to the law.

    This is interesting to me, because a) it concerns data, and b) there is an interesting argument here to be made about what data is needed for a business purpose. I could see this being argued successfully either way, and not just in court. As a domain holder, does the registrar really need multiple different sets of personal information from me? Arguably, this is a convenience for them, one that is based on tradition. However, one could argue the other way.

    It is a little scary that a court, with no expertise in some industry (Internet domain registration, in this case), will decide if there is an actual business need. After all, can a lawyer or judge really understand what data a business needs in their daily activities?

    Maybe, maybe not, but I do think this forces businesses to actually stop and think about what data they collect, have a justification, and document that. That’s a good thing, because often I find business people just asking to collect data without any idea what they’ll do with the information. I also find technical people collecting data, not maliciously, but often to anticipate what might be asked of a system, or because they want to avoid rework and just decide to collect everything they can.

    Data is precious, and while I don’t want to put many limits on what data businesses can collect, I also don’t want to them be able to collect anything, not disclose what they’ve collected, and not secure it properly. Having some limits, or at least forcing them to consider the risk of holding old, useless data, is likely a good thing for all of us.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Maintain Your Trustworthiness

    Many of us that are DBAs and/or sysadmins find ourselves with privileged access to many systems. We can often read the data that’s stored in these systems, whether that’s a relational database, a NoSQL store, or even a mail system. As a result, it is incumbent upon us to be trustworthy and maintain confidentiality with privileged information.

    Overall I think most of us do this, but there are always some rogue administrators out there, some of which might take malicious actions. There have been a few people that were arrested or sued for hacking into systems, trashing backups, or causing other issues. Often those are emotional outbursts that disrupt operations, and many people are aware there is an issue. However, what if people weren’t aware they were being hacked in some way?

    I ran across this story about some “admin” software being sold on a hacker forum site, which was marketed as sys admin software, but used to control other people’s computers without their knowledge. This is essentially a remote access trojan application that a developer sold to others who used it to steal data from their victims. The software developer was arrested and signed a plea agreement, knowing that it was used in a malicious manner.

    For those of us that have privileged access, we might learn passwords of users as we watch them enter the value over and over. We certainly might work with their data to help them solve an issue or understand the manipulation taking place. We We may have auditing systems or logs that allow us to replay or examine the data values people have entered into applications. We do this with their permission and understanding, or at least someone’s permission. A user might not know we have substantial instrumentation, perhaps even the equivalent of a keystroke logger, but there will be some management that is aware of the existence of these tools.

    While I’ve played a few pranks on people, moving keyboards or mice, I’ve always ensured they knew it was me in a short period of time. Using admin software to spy on others without their knowledge is a breach of trust and ethics, in my opinion. Even being asked to use this by management would be immoral for me.

    Knowing this type of software exists, is important, and if you find it, I’d make sure you report it to management immediately, preferably to a few different people. If anyone is using tools to spy on users, they’re likely up to no good and I’d hope we would all attempt to put a stop to the practice.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Master of the Schema

    “The database … should be the master of the schema”.

    It’s not often I see an application developer talk about the importance of the database, or at least that the database (and data) are very important to the success of the application, but in this case, that’s what I saw. The quote is from this blog, which talks about the problems of the code first approach to building an application. It’s from a Java development company, though it’s their jooQ product blog that actually generates Java code from the database, so they get the need to pay some attention to the database.

    The thrust of the article is that using an ORM is fine, but this isn’t necessarily the best way to design your datastore if you need a relational system. There will be mistakes made in naming objects, in structuring them and ensuring indexes exist and more. The longer you go with generated database code from some code-first type system, the more issues you might have later as you try to modify both the application and database, especially the database, since you can’t drop it and recreate it.

    If we could drop the tables and rebuild them, life would be great. Maybe we should keep copies of data on the clients, and just reload the necessary data after a deployment… It would make my life easier as a database professional, but I suspect this isn’t the best way to ensure data quality and consistency (not to mention completeness).

    I agree that if you’re building an application, you should go database first. At least start with some basic structures and learn to modify them. The practice you get modifying code, with scripts not GUIs, will be invaluable later to both developers and DBAs/admins. You’ll start getting practice and understand what it means to deploy scripts to your database. After all, making schema changes with scripts in your development database is the first “practice” you get for deployment. If you can’t get the scripts to work here, how would you have any confidence they’ll work in production?

    Steve Jones

    The Voice of the DBA Podcast

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

  • Another Brick in the Wall–T-SQL Tuesday #105

    tsqltuesdayIt’s that time again, and this month we have a good topic from Wayne Sheffield. We’re asked about getting stuck, about being blocked, about encountering a brick wall.

    I have to say that I don’t think I encounter these very often, usually because I try to be effective, even if things get ugly in code. The important thing for me is to deliver value. That doesn’t mean I build slapdash applications and leave them. I do my best, and try to continue to learn to build things better. However, in the heat of the moment, I might need to do something silly that I end up either rewriting later or having someone else rebuild the solution.

    With that…

    The Large Aggregate

    I once worked for a financial services company. This was decades ago, back in the age of spinning disks, SQL Server 6.5, and flip phones. We had a number of clients on our main application, but one large client that dominated our business.

    We needed to provide quarterly summaries of activity and portfolio performance for all clients, but for this main customer, they also wanted us to add monthly reports. They were actually their own management firm, but we provided them with back end services. Within a month, they would have millions of transactions that we had to group by some organization and then calculate performance.

    This wasn’t a big deal with other clients, though most of them had relatively simple calculations. For this client, however, they had separate rules for certain types of activity and the result was a calculation that we couldn’t easily get into CASE statements or other types of query workflows.

    In trying to handle all their requirements, I (and my junior team), got stuck. We couldn’t come up with a set of queries that would work. We didn’t want to go down the road of writing custom queries for each organization as this would be an ongoing nightmare as new requirements were introduced.

    We had delay after delay in trying to implement their monthly reporting and pressure kept mounting. Eventually we turned to using temp tables and making different passes through them to handle specific requirements. We had ugly flags in our temp tables to let us know which set of rules we’d applied and which we hadn’t.

    The code worked, but it was slow. Too slow, in fact, and my boss wasn’t pleased. After a particular unpleasant dressing down, I was wondering if this was the career for me. I’d talked with friends, posted questions, but in this pre-SQLServerCentral era, there were limited places to get assistance. I had hit brick wall.

    I never got to find a solution to the problems. My boss at the time was one of my worst, and he became petty. I started to get assigned various inane tasks, which were slightly annoying. However, on a night off, while on a date with my wife at a concert, he paged me with an emergency. I had to leave the show to answer and call in. When I did, he told me this was just a test to see if I would respond, then berated me for taking more than 5 minutes to do so.

    I quit the next day.

    Perhaps not the best reaction, but between the brick wall and poor treatment, I decided to move on. Hopefully someone else managed to solve the the performance issues and help the client.