Voice of the DBA

Category: Editorial

  • Data Scientists

    Would you like to be a data scientist? In some sense I think that many of us working with SQL Server are amateur data scientists. Whether it’s building a report, or just writing code to summarize data, we are working with data in a scientific way.

    What is data science? It’s not really a science, but instead it describes the field of analyzing data and the tools used. It’s primarily used with more traditional sciences to make sense of the vast quantities of data that are accumulated and used to support or debunk some hypothesis.

    Many of us as data professionals have some experience in managing data and manipulating it, but not a lot of formal knowledge in statistics or heavy math. Just reading the recommendations of what might be needed become a data scientist reminds me of a graduate level curriculum, but I’m not sure most of us need that much schooling.

    However I do think that as a data professional that is looking to add strategic value to your company’s applications and trying to stand out, it makes sense to beef up your analytics skills. Finding ways to spot trends and patterns can be very valuable to finding new opportunities in business, and those talents are based on sound statistical analysis techniques.

    Steve Jones

    (Originally published at http://www.sqlservercentral.com/articles/Editorial/72171/)

    Podcasts

  • ETL Security Holes

    The addition of DTS to SQL Server meant that more and more of us could grab data from a variety of sources, easily, and move it to SQL Server systems. SSIS enhanced our capabilities and made it fairly simple for non-programmers to grab web service data, and other formats, and quickly decompose them into relational formats. This made it more likely that we would build data warehouses or reporting systems, and easily keep them up to date with data from our OLTP systems.

    However the ease of using ETL (extraction, transformation and loading) processes to move data did not include the moving the same security controls and restrictions to these new systems. That can create a big security holes, especially when you have secondary systems used for decision support that might not be contained in the production network and not audited to the same level as other production systems.

    Whenever you move data around, there ought to be some set of guidelines for the security protocols required. That can be hard to track when you are pulling data from a secure system and your connections look like every other connection. One of the dangers that I suspect we will run into with Powerpivot is that secure data will be downloaded to Excel worksheets on insecure systems, and all of the controls that we have in place for protecting sensitive information are ignored when we pull data for analysis elsewhere.

    Our data security has gotten better, but we have a long way to go. If you are working with ETL processes, and especially with end-users, make sure that some sort of security policy is in place to help protect this data from being accidently disclosed. It’s not a great suggestion, but until we have better security tools to allow policies to follow the data, that is all we can do.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72157/)

    Podcasts

  • Scaling Up

    A petabyte of disk space

    For most of my production DBA career, I worked with databases whose size was measured in MB. A few got into the GB range, with the largest dataset I ever managed being 800GB. That was in the days of 35GB and 70GB drives, when SANS were just being deployed widely into companies.

    I did have the opportunity in the SQL Server v6.5 days to interview with a few companies that had TB sized databases. One was running a 13TB v6.5 system and when I heard that on the phone interview, I declined to continue the process.

    I like a challenge as much as the next person, but at that time I had a 6 year old and an infant son and preferred being able to go home at night and see them. I am not sure how I would approach that challenge today, but I did think this would be a good Friday poll.

    Would you want to manage a large scale SQL implementation?

    By large scale I am thinking of a 10+TB OLTP system or a 30+TB warehouse or cube. Something that is out of the ordinary in terms of scale, and which would present challenges outside of the systems you currently work with.

    My thought is that at this stage of my life, I don’t need the long hours required to deal with some operations on these systems. Simple checks, test restores, and all sorts of maintenance become larger issues when you are dealing with TBs of data. Unless we get substantially faster hardware in the future (in terms of IOPS), I can’t imagine how we will handle PB sized databases.

    Let us know if you are up to that challenge today, and if you work on VLDBs, I would be interested in knowing how you like it.

    Steve Jones

    (originally published at http://www.sqlservercentral.com/articles/Editorial/72151/)

    Podcasts:

    1. Video Podcast 20.4MB WMV
    2. Video Podcast – 14.9MB MP4
    3. Audio Podcast – 3.4MB MP3

  • Operating in Failure

    This editorial was originally published on Sept 25, 2006.

    This quote is great: “Any large system is going to be operating in failure mode most of the time.”

    It’s from Peter Coffee’s essay on learning from paper based systems and what works in their digital equivalents. Peter examines the conversion of a paper based system for keeping track of Boy Scouts’ work on merit badges as the process moves to an online system. He points out a few flaws in the design matching the process.

    More importantly, he points out that the system fails in a few ways, which is to be expected, but it doesn’t fail gracefully. I think this is a good point and one that most developers fail to take into account. Most systems will “fail” in some way and should be able to handle that failure. Whether it’s data entered incorrectly, a mishmash of keys hit, incomplete data, or something else. We seem to expect that our systems will move along perfectly and that our systems will “force” the users to work in the approved method and process, thereby improving efficiency and ensuring things work well.

    I’ve encountered this in many places, but none more apparent then surveys that we used to send out to customers. We’d require answers to many questions to ensure that we could properly fill the parent and child tables in the database and ensure proper reporting. The developers were happy to run along and build this and make it work as expected. My issue, however, was that the surveys were sometimes long and complicated and we should be sure to capture some information in the event the user stops early or has connectivity issues. In other words, assume we will have issues and plan for them.

    You’ve probably encountered this in other places as well. Every system, especially web based ones, should be expected to fail at some point and you should gracefully deal with those issues. We’re all human and we make mistakes and the systems we build will have bugs, problems, or even unintended uses by our clients.

    So plan to fail and you’ll be better off.

    (This editorial is published at: http://www.sqlservercentral.com/articles/Editorial/72149/)