Voice of the DBA

Category: Editorial

  • MoSQL

    Google is doing more SQL, or at least shifting towards relational SQL databases as a way of storing data. At least, some of their engineers see this as a better way to store data for many problems. Since I’m a relational database advocate, I found this to be interesting.

    When Google first started to publish information on BigTable and other new ways of dealing with large amounts of data, I felt that these weren’t solutions I’d use or problems that many people had. The idea of Map Reduce is interesting and certainly applicable to the problem space Google had of a global database of sites, but that’s not a problem I’ve ever encountered. Instead, most of the struggles I’ve had with relational systems are still better addressed in a relational system.

    Google feels the same way, and in a blog, they talk about choosing strong consistency where possible. This is a post that promotes their relational SQL database (Cloud Spanner), but there is a good discussion of why consistency matters and where moving to popular NoSQL models, like eventual consistency, cause a lot of problems. Both for developers and clients.

    This quote caught me eye, and I may use this with developers that look to avoid RDBMS systems: “Put another way, data stores that provide transactions and consistency across the entire dataset by default lead to fewer bugs, fewer headaches and easier-to-maintain application code.” I think that’s true as I think many of the advantages promoted in non-RDBMS systems are often placing a greater burden on the application developer than they realize. A burden that grows over time as the techniques used cause more technical debt.

    I think more SQL based systems are the way to go for many problem domains. Google agrees, and if you read more about the Cloud Spanner team, you might agree as well. You’ll also find them to be incredibly smart people that think deeply about the problems that are both raised and solved by relational systems.

    So go ahead and promote more SQL Server databases. Google thinks they’re good for many applications, and that’s good enough for most developers.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Lax Security is Harmful for Employment

    Manure rolls downhill Since I live on a horse ranch with some slight hills, I can attest this to be true. At least, it’s true for horses and it’s true for short distances. Manure isn’t very friction free and often ceases movement quickly. The same isn’t likely true for bull droppings, but I haven’t done much testing in that area.

    Most of us would agree that those that are negligent in their jobs, especially with regard to security, ought to be punished. In some cases, this should lead to termination, though I think many of us technical people would prefer that management who doesn’t budget resources for security be the ones punished.

    I mentioned manure rolls downhill, and this article on the after effects of data breaches bears that out. Not only were there record numbers of issues last year, but the typical cost is nearly $4million. That’s likely some very expensive breaches and lots of relatively inexpensive ones, but even the low cost ones probably feel expensive to small companies that experience them. In the lists of breaches I’ve seen, lots of smaller firms (retail, law, etc.) are included, and tens of thousands of dollars might be expensive for them.

    One thing that article points out, there are an increasing number of C-level executives being terminated after breaches. I’d like to think that’s good, but I’m somewhat pessimistic that the next hire will find ways to improve security. There are lots of impediments to fundamental change in more organizations, so I suspect this trend leads more to short term employment for CIOs and others, and likely higher demands for salaries because of the risk of security issues inside the company. The further puts pressure on budgets, which is another impediment to better security.

    Note that it’s not just IT execs, but non-IT staff as well. Maybe I’ll be wrong and this will make a difference. Of course, IT staff are let go as well, often blamed for issues. There will always be some security issues, but I urge those of you with privileged accounts and access to sensitive data to be careful with your credentials and work to improve security when you see issues. Get written documentation when someone doesn’t allow security changes, in addition to noting your requests. This might not stop a data breach, but perhaps it will give you a better chance of not being blamed for security incidents.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Revving the Error

    I was pleased to see the fix for the string or binary data truncated error saw some development work in SQL Server 2019. If you haven’t read about this, the changes are described on MSDN and I thanked the development team. What’s even better news is that the fix has been ported to SQL Server 2017 in CU12 is is also slated to appear in an upcoming SQL Server 2016 CU.

    In working on this error, Microsoft provided some guidance about where the first occurrence of the truncation would occur, in the column and which data value. This is invaluable in troubleshooting data movement, though this could be a trial and error process if you have lots of possible errors in different rows and columns. I could see us working our way through the list of problem data cell by cell.

    Microsoft didn’t change the error message, as that might have broken code. Instead, we get a new error message, number 2628, which allows us to slowly rev our error handling code. To me, this was a much better solution than merely revving the old error. We have a trace flag to turn the behavior on and off, so we can work through the places our code is affected.

    The plan is that at some point this will become default behavior, and error 2628 will replace that the all too familiar and frustrating error 8152. That makes sense, and I like the idea of limiting how long we can control behavior of our systems. While we might have code that needs to be fixed, we need to fix the code, and sooner rather than endlessly deferred. Even if you have an existing SQL Server 2016 or 2017 system, I’d hope that you’d be looking to patch your system at some point, perhaps to a level where this is the default behavior.

    There are many reasons why and when we patch, but we might be forced to because of some security issue. If that happens, I hope you’ve been working to fix code that is looking for error 8152 and are prepared to have SQL Server behavior changed.

    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.

  • GitHub Downtime

    I didn’t notice any issues with GitHub, but others did. The majority of my interaction is just through the git protocol, so things tend to work fast, and I don’t have any database access. I rarely use the Issues, and other parts of GitHub, which were affected when GitHub had a MySQL cluster fail over. There’s a good write up of the post incident analysis that’s worth reading, from a database perspective.

    I’m not a big MySQL guy, only running an instance to power T-SQL Tuesday. The structure of a write primary and many read replicas that GitHub describes makes sense. It’s similar to what I’ve done in SQL Server, and certainly the idea of some quorum management, handled at GitHub with the Orchestrator software, is something that needs to be configured properly. Allan Hirt has talked about the complexities of quorum in large installations, and it’s not a simple thing to configure.

    In reading about this, there are a couple things that strike me. First, the analysis talks about a degredation of service because East coast applications had to send writes to West Coast database servers. There were some problems with the way the database servers were working, but it seems to me that there should be some sort of application failover that’s possible. If you can’t have an application and database fail separately without customer impact, then there should be some way to fail applications over. Perhaps not, but if you’re responsible for designing HA for the database, make sure you talk to the application people and test for issues.

    The second thing for me is that somehow there was a period of time when writes were occurring to the East Coast system that weren’t sent to the West Coast. My ignorance of how this HA stuff works in MySQL prevents me from making a big deal of this, but this isn’t something that should happen. If the quorum moves data to another node, it must stop writes to the first node. This could happen in SQL Server, but for me, this is the level of data loss I’d need to accept in my RPO.

    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.