Voice of the DBA

Author: way0utwest

  • Statistical Protection

    Statistics are being used more and more, and many of us don't understand the lack of security, even in anonymized data.
    Statistics are being used more and more, and many of us don’t understand the lack of security, even in anonymized data.

    The things people can do with data is amazing. I remember reading about the anonymous data set released by Netflix and how some of the people were identified based on other, related actions on the Internet. This de-anonymization, while scary, was amazing to me. There have been other, related reports of similar “attacks” taken against other data sets. These reports worry me that we will have more and more data security issues in the future, not less.

    I ran across an article that talked about protecting data in statistical databases. These are the databases that contain data from multiple sources, and are used to analyze the information from these sources. The security of these databases becomes important when the data contains information about individuals that we consider sensitive. Interestingly enough, it seems that the security protections being used are query restrictions.

    However these restrictions are the reverse of what we might expect. There might be minimum restrictions on the number of rows returned, to try and prevent information about a specific individual from being returned. There are also limitations on the types of queries that can be run, usually requiring aggregate functions in the query, and restricting which aggregates are allowed.

    This is definitely an area of our industry that needs more work and research. Lots of organizations, especially government organizations are being called on to open their databases up to the public, and many of them are doing so right now, allowing queries of their statistical databases. This might improve the use of this information by the public, but there are plenty of ways in which this data could be potentially misused. If your companies wants to open some of your data to clients or customers, you might raise the concerns with possible abuses of the database and ask that time and effort be included to try and secure the data, possibly by implementing query restrictions.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • T-SQL Tuesday #38 – Standing Firm

    tsqltuesdayIt’s T-SQL Time again, and this time it’s #38, from Jason Brimhall (b | t | li). The topic this month is Standing Firm. You are supposed to look at any of these words, and write something that fits with one of them.

    • resolve
    • resolution
    • resolute

    This is the monthly blog party, with each month hosted by someone in the SQL Server community. To participate, just write a blog post on the top, include the logo, and link to Jason’s invitation.

    If you’d like to host, contact the founder, Adam Machanic (b | t).

    Standing Firm

    I decided to write about being Resolute.

    res·o·lute

    /ˈrezəˌlo͞ot/

    Adjective

    Admirably purposeful, determined, and unwavering.
    (from the Googles)

    One of the things that I’ve found in my career is that so many people are unwilling to stand up for what is important to them. They’ll argue points, they’ll debate about what is better or worse, but ultimately they are often afraid to make a stand for things that are important for their own personal well being. It’s not even the things that they feel strongly about that they give in to, often it’s subtle pressure from managers, from peers, or from the community to act, say, or do certain things.

    In short, they can’t say no.

    Most of us like to please others. We want to do a good job. We want others to like us, and we want to solve problems. We want to get things done and be seen as a positive part of the community.

    That’s good, and I admire that. I also know it’s not sustainable. As humans, we need flex in our lives. We need some down time to compensate for the busy times. We need practice time in between learning times. We need balance.

    One of the hardest things I’ve learned to do in my career is say no. I learned it’s important from Andy Warren, and this part year I’ve finally felt like I can comfortably say no. I can say “no” to more work. I can say “no” to another opportunity for a client or job. Most importantly, I can say “no” to myself, when my goals, ambitions, and desires greatly impact my family, my wife, or my opportunity for downtime.

    I would urge you to learn to say no. It’s harder than you think, and it takes practice. However learning to stand up for the balance in your life is important to your long term career, health, and enjoyment of life.

  • The Default Fillfactor for an Index

    I ran down the rabbit hole on transaction logs recently. I started with Paul Randal’s post over at the SQL Sentry blog on trimming the transaction log, then went to his video on log analysis. I also glanced at the posts on index cleaning and what index stats don’t tell you. What started out as a quick “what can I do to help transaction logs perform better” became a few hours of reading, executing code and thinking.

    However the post that caught my eye was Paul’s post on choosing an index fill factor. I’ve seen various notes on the fact that fill factor can matter for performance and maintenance, but I haven’t often seen someone give some good concrete rules on what you should choose. In a nutshell, here’s Paul’s advice:

    1. Don’t set a system wide fill factor with sp_configure
    2. Start with 70 for specific indexes that seem to experience lots of fragmentation

    I like this advice. It’s simple, and easy to start using, although the caveat to #2 is that you need to monitor and perhaps adjust the fill factor (up or down from 70) and possibly change your maintenance schedule. I might lean towards leaving my maintenance alone, especially with a script like the SQL Fool Index Defrag Script running and playing with fill factor to ensure I minimized page splits.

    There’s also the trade-off of requiring more space for your index (and maintenance) if you move to 70 from 100.

    I do think that changing the system wide level is a bad idea. If you aren’t sure what your system wide fill factor is, here’s a post on checking it.

  • The $50,000 Laptop

    Hopefully you won't lose this amount of data.
    Hopefully you won’t lose this amount of data.

    I wrote a long time ago about the value of the data on a laptop being worth more than the hardware. That’s certainly true for me, and I very much worry more about losing the data on my devices than the any of the devices themselves. I use sync services to keep a backup of most things, but I still worry about losing any of my bits.

    There was a large study competed recently, called The Billion Dollar Lost Laptop Program, which examined 329 organizations. The idea was to find out the economic costs of lost laptops related to various public and private entities. The conclusion? The average value of a lost laptop, just one laptop, is $49,000. The conclusions say that least expensive part of losing a laptop of replacing the hardware.

    As we would expect, most losses occurred away from the office, however it’s not known how many of these losses might be targeted thefts. Many of the losses did occur through theft, which is disturbing when most of these disks did not have encryption in place. This was true even when confidential data was contained on the laptop.  It is nice to see that companies that realize they were targets of theft, as opposed to losses, typically do use encryption.

    As the study shows, there are a lot of costs that go into replacing a laptop. Many people don’t think of all of these costs, and even if the costs are double what they should be, these are still substantial costs for companies to absorb. The costs will only go up in the future, especially as more and more people move from desktop workstations to laptops.

    The one positive note? Encrypting the laptop almost cuts the loss in half. A good reason to require encryption on all laptops.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.