Voice of the DBA

Category: Editorial

  • NoSQL Inside SQL Server

    There has been quite a debate the last few years between using NoSQL and SQL Server as data stores for different applications. We’ve seen a great deal of interest from developers in document stores as the data structures are closer to what a C# or Java developer sees in their code. Graph databases are becoming popular for certain types of data as well, since their query performance can be extremely powerful in some cases. There are also the scalability aspects of NoSQL systems touted as a better way to build Internet based applications.

    However NoSQL databases aren’t the ultimate data store. There are pros and cons to using them, and while it might be easier to being development on one for an application developer, I would argue it’s just as easy to get caught mis-using a graph database or a document database as it is to poorly program a relational database.
    This week, in advance of the SQL Server 2016 release, I caught a post from the SQL Server storage engine team showing how JSON storage inside of a table can provide some NoSQL-like benefits to applications. It’s a good idea, but like many NoSQL solutions, you need to spend a bit of time examining your particular requirements. If you need to update your JSON often, especially in large batches, this might not prove to be a good idea, especially if you need consistency.
    I think ultimately that everything does relate to the CAP theorem, precisely because data has to exist somewhere, and anytime you need to duplicate it, move it, aggregate it, or anything to relieve some other issue, then there isn’t any magic taking place. You’re going to have to do work to move the data. Some platforms may make that easier, but I bet there are other drawbacks that you will have to consider.
    Ultimately I think you need to pick the platform that best suits your environments. That’s not only the application requirements, but also the developers’ skills, the operational staff’s ability to manage the platform, and the maturity of the platform. Those are tough questions, and while I’m glad people experiment with new systems, I do hope that they think carefully about the positives and negatives, and more importantly, share their experiences with the rest of our industry.

    Steve Jones

  • The Case for Upgrading

    I heard from a member of the community that’s still on SQL Server 2005 recently. Yes, 2005. That’s an old, unsupported version, but they’ve got some Access/VBA apps that work well and aren’t worth investing a lot of development effort in. The person noted that they could only upgrade to 2008 R2 without rewriting code, and they didn’t want to move from one unsupported version to another.

    I suspect a few of you are in this situation, though it seems many people I know that still run 2005 (or 2000), also have newer versions of SQL Server running. As there is the need, or opportunity, to replace and upgrade instances, they do so.

    In this case, it seems there’s not good case to upgrade. A change to newer code would be expensive, but also licensing costs have dramatically risen. If the existing hardware were an older 4CPU (licensed), perhaps dual code x 8GB RAM system from, let’s say, 2008 ish hardware, that’s a quad core machine. While you might be able to run on fewer cores with today’s processors, you’d still be looking at a quad core, single CPU as a minimum for licensing and hardware.

    There’s also the decision of upgrading to 2014 with new code. That could be a significant effort, with resources spent here instead of on new development. Is that worth it? I think it’s hard to decide without knowing more.

    Is it worth it to upgrade to 2008 R2? I’m not sure. While you get some enhancements, you’re still moving to a version that will go out of mainstream support in a year or two. I’d say this isn’t worth the cost.

    Ultimately I think Microsoft is shooting themselves in the foot a bit with the minimum core requirements and larger licensing costs, without giving customers flexibility. I think Microsoft would be better served by letting customers license the scale they need.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Software Estimates

    We really stink at estimating the amount of time that we’ll spend on building software. However I’m not sure that we’re much worse off than many professions. If you ask a doctor how long it takes to get better, do they know? They do in some small, often seen, well documented issues, but in many cases, they don’t really know. Can lawyers determine how long it takes to solve a case beforehand?

    It seems the more accurate estimates typically come from places where the problem is fairly simple and very well known. However that’s probably true in software as well. If I asked you how long to write a new blog site, I bet many developers could come up with a close estimate.

    The idea of telling a computer to do x seems more bounded and contained than other professions, but it’s really not. This is often because developers don’t get a complete statement of all the requirements from clients. However this also isn’t really any different than many professions. How many of you know exactly how you want a house built, or legal matter settled, or the specifics of medical treatments and effects?

    I read an interesting piece on the idea that developers should just make small estimates, small changes, and move forward. They should be willing to undo, or redo their efforts as needed. That’s one of the benefits of Version Control Systems (VCS) and the great developers take advantage of the possibilities.

    While there are certainly problems with moving too fast, there should be plenty of reviews, tests, and other steps in the process that keep the quality of code high. Letting developers push forward, experimenting, and building sections of code quickly can help continue to push your development effort quickly. As long as they’re willing to undo and redo their efforts as they get closer to the end product.

    Steve Jones

    The Voice of the DBA Podcast

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

  • No More Foreign Keys?

    I struggle with removing FKs as a general idea. It’s presented in a piece from a developer. It appears to me that if you do this, you are merely moving the problem, and potentially causing other issues. Apart from Grant’s response on performance, which is very true, there are other challenges with microservices and separate databases for each service.
    Let’s say we are doing something trivial, like a simple e-commerce site. I could have a separate database for products, one for orders, one for shipping actions, one for customers, etc. However as I scale, I’m not using intra-database queries or FKs/joins/other techniques to verify information for an order. Now I’m sending messages. At small scale, these are trivial. At large scale, my network traffic and messaging is vastly growing and potentially a bottleneck.
    Also, since there is a “products” database, and an “orders” database and a “customers” database, I still can’t necessarily scale out these microservices beyond their own machine. Perhaps I can scale them higher in terms of each being as large a machine as I can have now with a single Oracle/SQL Server/MySQL/etc. box, but I’m still have a scaling issue. I also now have a new messaging problem I need to manage and architect. If I lose one database, how gracefully can my application degrade or does it start to fail with unforeseen interactions? Do I create more frustration when customers cannot place an order because we can’t find the customer db or because the site is down?
    Certainly there are domains of problems that would work fine here. Spotify is probably a good example of this. There may be e-commerce systems that allow this, perhaps being willing to notify customers after some time (hopefully minutes, perhaps hours) that a product is out of stock because the message from the orders database didn’t get to the application. There are certainly ways to copy and replicate some  data, assuming that “fresh enough” works for your application. However that’s a business decision more than a coding decision. This also means that you also have a new load on your system to move data. I also think the more you work with singleton rows and single objects in an application context, the less you need RDBMS capabilities.
    Ultimately I think FKs and strong RDBMS systems work very well in many situations. They work less well in many situations and your domain may qualify for those areas where a NoSQL, a multi-db, or other architecture works. We should certainly investigate and write about where things work and don’t work, but I wouldn’t assume the RDBMS with it’s PKs and FKs isn’t a valid, well thought out and incredibly useful architecture.

    Steve Jones

    The Voice of the DBA Podcast

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