Voice of the DBA

Category: Editorial

  • We Need Data Privacy Consistency

    For most of the last year, I’ve had quite a bit of my time devoted to the GDPR and related topics. My company is affected, as it’s based in the UK. Not only must we comply, but we know many other companies must as well. As a result, some of our product focus was aimed at helping companies solve their data privacy issues, especially with regards to data.

    That continues to be a good idea as the GDPR isn’t the only regulation out there affecting organizations’ data handling practices. There are other laws around the world, but the US is a big market, one of the biggest we have, and we are seeing increased need in the US for the same types of data privacy and protection solutions mandated by the GDPR.

    California recently passed their own data protection legislation, and it’s leading the way in the US. Tim Ford wrote a short piece on how this affects his company. He notes that as a consumer, he’s glad to see stricter data handling practices being required. However, as a business owner, he’s concerned and I think there is some basis to be worried.

    There are other laws that might pass soon in the US. New York has a bill, Colorado has signed a weaker, but still new, law. Other states are considering items, but the US Congress has yet to really move forward on any legislation, which might lead us to have multiple data handling practices that are required. That would be a nightmare, much more difficult than the hiring and tax practices of different states.

    I couldn’t imagine having to work with different processes, and certainly wouldn’t want to have more restrictive laws being passed in the future that might cause us to change practices multiple times. I can only hope that the US gets a common law for all our states, and that the practices are in line with what the GDPR requires. Other countries have used that as a basis for their laws, and I can only hope the US does the same.

    Steve Jones

  • How Do You Setup Your Instances?

    I’ve set up a lot of SQL Server instances in my career. I’ve gone from manual only setup in SQL Server 4.2 to more automated means in the latest versions. The easiest was actually in Azure where I only need to specify a few parameters for a PoSh cmdlet. However, unattended setup is pretty easy as well for local SQL Server instances. If you’ve never done it, you’re missing out. There are plenty of other ways to do this with tools like Chef and Puppet, AMIs in AWS, and more.

    Erik Darling wrote a post recently called Setting Up SQL Server: People Still Need Help. Erik’s point in the piece is not that installing SQL Server is hard, but that many people stick with the defaults once they’ve installed the instance, never changing anything. There are some basic things that you’ll want installed all the time, so having a repeatable process is important.

    This week I’m wondering how you install instances in your job. If you need to add a new SQL Server for production or development, what do you do? Let us know your process and procedure.

    I don’t set up too many instances, and in fact, I mostly add them as a lab instance on one of my machines. For the initial install at times, I’ll just run through the manual install, but I then have a quick config script that I use to change a few items, but very few. In most cases, I don’t do much more than add a few logins, limit memory, and enable the DAC. For the cases where I want to add a few different instances for testing, say for looking at patches or using mutli-server features, I’ll use an unattended install script.

    There are some amazing ways people have created for repeatable installs, such as the Finebuild project. If you’ve got a way that works for you, let us know. Just be sure that whatever repeatable process you use changes some of the defaults and ensures your SQL Server is better prepared for any workload to come.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Defending the RDBMS

    A few weeks ago I ran across an essay from Randolph West called, Relational Databases Aren’t the Problem. This was a response to another essay that made a case for relational databases being bad for many businesses. I thought that both pieces were interesting for different reasons. Certainly I don’t believe the the RDBMS is perfect, and it certainly can be hard for developers to build software that interfaces with a relational system.

    The original complaint about the RDBMS is somewhat rambling and deceitful, in my opinion. It is an excellent study of how to use a few concepts to confuse and create doubt in a casual reader. If I weren’t reading closely, I might fall for a number of the issues that exist with relational databases. However, in my mind, part of the issue is that quite a few of the issues that are discussed aren’t problems with relational databases, but often the issue with poorly developed software or design of the entities and relationships. I find myself even more disappointed that the author hasn’t really addressed any comments, but rather just pasted a link to his followup article.

    I do think that the defense from Mr. West does a good job, though it also misses some of the primary issues we struggle with relational databases. There are problems with the knowledge of how to build a well performing database, both from application developers that view this as a necessary evil as well as experienced database developers that don’t regularly improve their skills and try new design techniques.

    I also think that both of the pieces fail to address the issues of gathering and working with multiple rows of data. The second discussion of “doing without databases” really implements its own database management structure, which may work well, but is fraught with issues such as the concurrency issues of multiple users searching and scanning through data without having indexes. While indexes are overhead, they are necessary as hash buckets aren’t necessarily feasible for all the properties in a class. Also, if you end up building them for multiple properties, you’re building an index. There’s another good defense of some of the issues here.

    I do think that keeping more data in memory and synchronizing access to structures sounds great, but scaling that out to multiple systems, and ensuring consistency at high volumes, not to mention potential loss of data issues from crashes are a problem. Having a write ahead log in SQL Server does a wonderful job of ensuring we can handle redo/undo on system restart. The method presented doesn’t necessarily ensure this, though perhaps accepting some data loss from high concurrency changes is OK for many applications.

    I will say that the idea of all data in memory is interesting. I had to stop and think about how many databases really have more than 1TB of data. If we throw out indexes, does this cover most data stores? I bet this does, though that doesn’t mean that there aren’t issues with using in memory array structures, with widely varying data sizes.

    Would I use an in-memory data structure for software? It’s tempting, but honestly, I wouldn’t. The value of data is too high, with potential issues from poorly implemented ACID control structures. Plenty of issues have been found with different RDBMSs over their years, and even some in NoSQL systems. Thinking that I could avoid any issues and protect data is something I wouldn’t even try. After all, if there is some error, I’d prefer it from a system that many people use, rather than one I tried to emulate for no good reason.

    Steve Jones

    The Voice of the DBA Podcast

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

  • We Don’t Have Perfect Information

    I was discussing the PASS Summit with someone and they were wondering about building their schedule. Actually, they wanted to pick sessions, but see the choices in a calendar format, but the schedule wasn’t out at that point in time. My suggestion was to just build the schedule and then sort out conflicts later.

    A few people have mentioned over the years that they want to build a schedule and be ready for the event to maximize their experience and be efficient. I think that’s a common, normal thing that many technical people like to do. Many of us are Type-A, and we like knowing our plans and having a schedule prepared in advance. We can then decide which sessions might have greater priority for us during a time slow and adjust our choices to have the best experience. After all, we may want to see two speakers, but if they present at the same time, we have to make a choice.

    The problem is that we don’t have perfect information. Even if the descriptions and abstracts included perfect information about the agendas, what is covered, and to what depth, including demos, we’d still not necessarily assimilate and recognize all that data in a way that makes sense, even to us. There are no shortage of people that make plans today and are unhappy a few weeks later.

    Even if we knew what we wanted to watch, many of us might think a session on database design has to cover third normal form, even when the text said this examines PKs and FKs. We might assume an SSIS data load talk included something on CSVs when the presenter described the talk as being for ragged right text files.

    We’re human, and that means we have flaws in how we deal with the world. This includes the ways in which we model and analyze data. We can make mistakes in our analysis often when we simplify our view of a problem to the point where our analysis is inherently flawed. If we don’t account for this and assume we’re flawed, we may overweight our conclusions.

    I try to remember this when I write reports from systems that others will use. I won’t have every piece of information that might affect a decision, but I try to ensure I have the most important, or significant, data. Or at least, the data I and the users feel is significant. The important thing to remember is that out data is always incomplete, and it’s entirely possible that we have missed a valuable piece of data.

    When that happens, we have to adapt and adjust our report, our application, or our conference schedule. We’ll learn more across time and we can use that information to change our system. I know that my view of a conference like the PASS Summit today, or even a week before the event, will be different than how I feel at the event. I should have a plan, but be willing to flex as circumstances change. And, always have a backup. I like to pick two or three sessions for every time slot, just in case.

    Steve Jones

    The Voice of the DBA Podcast

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