Voice of the DBA

Category: Editorial

  • Building Test Data

    One of the debates I’ve seen over the last few months is about test data in development environments. As I’ve been preparing for and learning more about the GDPR, it seems that many companies are concerned about holding sensitive data in their development systems. I think it’s a valid concern, and I’ve often had to deal with this issue in the past, before any regulation impacted my work.

    In one of my early jobs, we stored emails from customers in a table. We also had an email feature for our application. Needless to say, we needed to test that, though we didn’t want to obviously send emails to real customers when we were testing some sort of feature. I’ve done that and usually it results in a complaint and some scolding of the development staff. As a result, I learned to ensure that anytime we restored production to our QA system, we ran a script that would either change all emails to invalid values, or reset them to something we could use in a test system. In some cases, we’d reset them all to a specific address that we could check to see if the emails actually were sent.

    In talking to many people, they often don’t build test data for development systems because the data isn’t valid. What a developer thinks about, or what might be randomly generated by some utility often isn’t seen as valuable. Most developers want to see real data, perhaps because they can then better relate their work or a specific feature to the actual live system. Maybe it’s easier to see actual customers, products, accounts, etc. when working with clients or testers, but I do think that certainly in light of the GDPR and other regulation, there are risks here.

    While many people want to just restore production to refresh environments, I do think it’s a poor idea to use actual sensitive data. Even if you trust your developers, there have been no shortage of attacks against development systems, loss of laptops or other files with production data that were intended for developers. We just don’t secure test and development environments like production, and that means we are making a fundamental error in how work habits.

    I’ve gone through different views on this topic across the last couple decades, and now I want some real data, some not real data. What I’d really want is a bunch of random data that is close to production, mimicking the shape and skew of production, but without any sensitive data. Then what I’d like is a set of known cases of data that are the types of data that we need to ensure works in our system. Various cases of transactions and values designed to cover the functional edges that we support.

    Of course building these sets isn’t’ always easy, and it’s never going to be done. As long as we write software, we will need to maintain tests and data alongside the code. I do think this is a worthwhile investment in regulated industries, and likely worth doing in all industries. The thing is, it’s not interesting or fun, and likely not to be ever be done in most organizations. I’d like to change that, and I hope you do as well.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Data Submarine

    For some reason, this came to mind: under the sea, under the sea. That’s what I thought of as I was reading about Microsoft sinking a data center. There are probably jokes to be made about Microsoft and sinking, though they’re less humorous as the company’s stock price has risen quite a bit in the last few years.

    The is a research project from Microsoft on future data center design. Using modular devices that can be submerged for years at a time, only having a connection for power and data back to the surface. These are designed as sealed environments, without the creature comforts needed in data centers for human technicians. The systems inside are built to live on their own, using the water for cooling the heat generated by computations.

    There won’t be any repairs or replacements for failures. With the equivalent of twelve racks of servers in the system, I wonder how long they will list. I find it interesting that the experiment is designed for a year, though the device should have a timeline of 5 years. Does that mean that Microsoft expects current hardware to last for five years? Is that the new lifecycle of modern chips and storage? Or perhaps they find the lifecycle is shorter, but this is more a test of the extreme lifetime that they expect and they’ll track and chart the failures across time of components? I expect they already know some of the expected lifetime of hardware from their massive Azure data centers, which they can compare to this environment.

    It’s an interesting idea, and one that might see smaller, modular data centers spread around bodies of water where there is enough movement to carry heat away. This should reduce power consumption, as less is needed for cooling. This can also reduce latency, with devices perhaps located closer to clients for heavy compute capabilities or even content delivery.

    I’m not sure these will work at larger scales, as heat attracts life, with plant and animals potentially migrating to be near the submersibles. Who knows if were would be interference with operations, but I wouldn’t be surprised to find that some level of maintenance is needed. We might see the rise of a new type of job, like undersea gardener or window washer that keeps the submarines clear of encroaching biology.

    Of course, I wouldn’t be surprised to see Roomba-like automated devices that put many of these humans out of work.

    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.

  • Machine Learning in the Database

    When SQL Server added the ability to execute R code, the decision seemed to split the customer base into two groups. One group was impressed and thought the idea of executing R code to analyze data in the database was a good idea. They were excited and impressed by the loan classification demo. If you haven’t read about this or seen the demo, it’s very interesting, and it’s something you might take a few minutes to read or watch it.

    The other group of customers felt this was a poor use of CPU cycles for a very expensive SQL Server CPU license. Running a complex analysis, training models, and other functions commonly associated with R scripts aren’t a good use of scarce resources. They would rather have R code execute on a separate server, much like any large messaging workload might be better served by a service such as AWS’ Simple Queue Service rather than Service Broker.

    I tend to be in the first group, as is Dr. Low. He writes that there is a place where Machine Learning Services (MLS), with both R and Python, are a good use of resources. Not in all cases, and certainly not for all work. The difficult parts of training models and doing the hard work of coming up with new ways to perform an analysis is definitely better left to workstations and data scientists. Those actions might not be worth the resources they take.

    Once the models are trained, however, the executable load of submitting parameters to a model and getting a prediction is small. SQL Server allows us to load pre-trained models into the database and just call them as needed. Plus, the R models run in a multi-threaded fashion, unlike the single threaded execution in clients such as R Studio.

    As with any feature of SQL Server, it’s important to test and evaluate the real world impact of new code on production sized workloads. Not only will you want to measure the load of your model execution, but you should also measure any changes in your existing workload with the additional R or Python code load. While I wouldn’t prevent the use of MLS in SQL Server, just like SQL CLR code, I would be careful about introducing without extensive testing, including dark deployments and simulated loads.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Be Prepared with Baselines

    I visited a doctor recently, and he told me a measurement he’d made. I asked if it was good or bad, and he said he had no idea. The value varied too much from person to person, and without values from the past, he couldn’t really evaluate the significance of it. He will be able to in the future, now that he has a value, but there’s nothing that can be done now. At this point, he has a baseline (of sorts) and can now start to judge how things change over time.

    After that visit, I started thinking about Page Life Expectancy (PLE). PLE is one of those counters that so many DBAs look at early in their career. Often they’ve read guidance that they should worry once this is below 300, which isn’t true. There are calculations for this, but they are based on your system, and really, they’re a rough rule of thumb. Really you need to measure this for your system, so that you know what a the value often is and then worry when it dips.

    To do that you need a baseline. You need to measure various metrics about your system over time so that you understand what’s a normal value. Plenty of experts, like Erin Stellato and Tim Radney have written about baselines, why they’re important, and what you might want to capture. In fact, we have quite a few articles on baselines at SQLServerCentral.

    If that sounds like a lot of work to you, I agree. I’ve built systems in the past that captured metrics on my instances and stored the data. I wrote reports to view data, alerts to let me know when something is breaking (or broken), and maintenance that kept data storage under control. I essentially had to be both the software developer and operations staff for my systems. That works, but I’d try to avoid repeating that effort from now on. As Tim mentions in his piece, there are better ways to do this. There are products, such as SQL Monitor and SQL Sentry, that capture this data for you, that won’t have typos, mistakes, or holes in their operation.  Some will even show you the baseline visually to see if things are withing expected ranges.

    The monitoring software does lots for you, though at a price. It’s tested, and it does all the gathering, storage, basic analysis and alerting in a way that allows you to spend time on actually fixing issues, tuning queries, and providing value for your organization. I think it’s worth the cost, since I know that my time is better spent on solving problems, not writing monitoring software. You may feel the same way or youj may not. You may prefer to write your own system, or you may not have a budget and be forced to build your own. Whichever route you go, make sure you set up a baseline. You’ll appreciate having one the next time your phone rings with a call that the server is slow.

    Steve Jones

    The Voice of the DBA Podcast

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