Voice of the DBA

Category: Editorial

  • Handling Data Corruption

    I wrote about dealing with data loss recently, ansking what your plans are for a situation. Some of you might rely on digital systems, some rely on humans, and some just accept some data loss. All of those are valid responses, depending on your environment. Today I wanted to take another step on this journey.

    Imagine that one of your systems has data corruption. Any system you have, but pick one that might cause you some anxiety. Maybe you discover it from a failed query, maybe from an entry in the error log, maybe from a DBCC execution (I hope you use these). You have no idea how long the corruption has been there. Therefore, you don’t know how many backups are valid.

    What do you do? What are your plans? Certainly there could be data loss potential here. There will likely be some questions about why this wasn’t known immediately, and maybe you’ll experience some embarrassment in the moment. This will be a stressful moment in your career, and one with which you will want to be able to cope.

    The question today is to get you to prepare a bit and game the possibilities. Some of you might never experience corruption, but you never know, so it does make some sense to think in advance and anticipate the reaction you want to have in the moment.

    We always want to be prepared, but we won’t have actual preparation plans (scripts, documents, etc) for all situations. It’s much easier to think about the possibilities, rather that actually build plans for every possibility. Maybe you want to even discuss and debate them among your peers. Today’s question is good practice for the real situation, and great mental preparation for a real event. Plus it can be a bit fun to brainstorm, have someone shoot holes in your ideas, you do the same for them, and then come up with another solution.

    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.

  • SQL Server is Lagging a Bit

    I don’t expect this to last, or I hope it doesn’t, but SQL Server is lagging behind. I ran across a short piece on the SQL:2016 standards, and I know these standards aren’t necessarily an important consideration for most of us when choosing a database. The standards are a compromise from many industry professionals working together. However, they do influence the vendors a bit. The way the list is written makes me wonder if Oracle provides more input and resources, or exercises more influence than other platforms. I hope not.

    In any case, I would expect that Microsoft to be able to make change and catch up quickly with their rapid engineering process. At least, they will if they want to, which means, do these things matter do you as a customer? Or maybe, do these things matter to the really large customers that use, or might switch to, SQL Server?

    I’m not sure I understand all of the changes to the standards, but row pattern recognition looks like some sort of LIKE-across-a-row feature. I know there are people that might need this, but I worry about the performance implications of doing this. As we move to larger data sets, perhaps those that come from sensors and other large data producers, this might be an important feature. Hopefully the implementation perform well.

    The one thing I expect is more JSON support. As much as I saw the value of XML and hated working with it, I see JSON as a better format, and one that developers use extensively. I do hope that SQL Server gets more extensive JSON support, though I expect plenty of people with a native JSON type to just start stuffing blobs in a column and spending lots of CPU cycles trying to de-serialize the values for queries. If this a native JSON type is an intermediate store before moving the data into a better format, I think this makes sense. However, we can’t prevent poor choices from database designers, and I would expect consultants to love this, as I’m sure more JSON creates more issues for them to fix.

    I am glad that date/time work gets some attention in the standards, but since I can never remember the actual syntax and values, I don’t really care. I’ll depend on SQL Prompt to get me through changes here, and then try to get all data in yyy-mm-dd format so I am not confused. LISTAGG is interesting, but I expect JSON to be used more. There are other changes, and I’m not sure how valuable they are for most data professionals.

    Adding small functions and features, even those that are used rarely, are ways that we can dramatically improve developer productivity. Looking back over a career, I’m glad that I don’t need to write sorting routines anymore. Using an ORDER BY or x.Sort() is a great time saver. There are certainly some poor implementations of some features, but we can often override and write our own implementations if needed. Or find workarounds that will meet our needs.

    I do hope to see the T-SQL language grow and expand over time, with regular enhancements that might help us work with data in easier and more efficient ways in future versions.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Classifying Sensitive Data

    Our databases store all kinds of data in them, depending on the purpose of your database. Most of us create tables and store data in response to some requirement or necessity in our organizations. Our decisions should be driven by good design principles, and I’d hope they are, but we do need to find a place to put all the data that our system will receive.

    Depending on the sensitivity or personal nature of data, we may have to alter the way we store data (perhaps encrypt it) or alter the security for parts of the system. This is almost an ad hoc, deal with it at that time task. The exceptions might be when I’ve had to conform to a regulatory statute, such as SOX, PCI, HIPAA, etc. In those cases, I’ve often had to ensure the entire database is protected in some way that ensures it meets the requirements of the statute.

    The time when I have had to think about individual columns of data is usually when building a development database where potentially sensitive information can’t be transferred to development machines. In that case, because of the effort of changing data, I’ll try to build scripts that change out individual columns and ensure that sensitive data doesn’t get copied. However, the data that may be deemed sensitive for one company, isn’t always classified that way for another.

    At least that’s been my experience. I shared some of this with the Redgate Foundry, who is running research into data classification. They’re looking for people to share opinions, but the project has me curious. I’ve always felt intuitively we could classify data in tables, but perhaps that’s too simplistic a way of looking at the problem. I know that legal groups struggle with some this problem with email and file server documents. Classifying the content in different ways is a challenge.

    Is it the same in databases? I’m not sure. I don’t know if there complex rules needed or if this is a simple problem that we easily solve and rarely deal with. I’m curious from those of you that deal with highly regulated industries. Is data classification something that you work with often? How do you decide the data classes and does this impact your administration of the database? If you don’t classify the data, do you worry about the sensitivity of the bits in your database? Let us know today.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Improving Replication

    I really like replication as a technology. I think the ability to move data around to other systems, at a gross level, is extremely handy in many systems. While SSIS and other ETL tools are very flexible and powerful, they also require quite a bit of work to maintain. Being able to send a table (or a vertical/horizontal partition) to another system is just valuable.

    This is why I’m constantly disappointed that SQL Server hasn’t really bolstered their replication technologies to make it more robust.  Don’t get me wrong, there have been improvements in various versions, and replication has come a long way since SQL Server 6.5, but as an overall subsystem in SQL Server, it has a long way to go. The tooling needs work, the reliability and robustness needs work. I find replication brittle, as do many others, and when there are code deployments needed, it seems that administrators often just script out the system, tear it down, make changes, and rebuild it.

    That’s not a great plan. In 2017, that shouldn’t be the plan. I don’t get why Microsoft hasn’t made things better, after all, more replication options could mean more SQL Server instances installed to support disparate workloads. However, rather than complain, I’d like to give Microsoft ideas.

    Today I’m curious. What would you do to improve replication? What would you like to see? Perhaps you want better monitoring of the process. Simpler setup, such as that available in Azure. Better bidirectional replication? Maybe an easier way to deploy changes? Let us know your ideas for improving SQL Server replication.

    Steve Jones

    The Voice of the DBA Podcast

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