Voice of the DBA

Category: Editorial

  • 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

  • Adjusting Model

    In July the T-SQL Tuesday theme was defaults. There were lots of posts about various items, but I found one by Kevin Kline interesting. Kevin noted that the settings for model are not necessarily what many of us would want in production.

    Is that really the case? This week I wanted to take a poll and see if many of you actually do create defaults for your new databases by changing model that are different than what Microsoft has done.

    Do you change the model database?

    Certainly there are some people that have create scripts with various settings for their databases. Some people prefer to customize the setup each time, based on the purpose of the database. Still others have scripts that alter the database to change settings after a someone has installed the instance.

    There are any number of ways to ensure you have good settings for your production (or other) databases. Let us know this week if you have a step to change the model database when a new instance is installed.

    Steve Jones

    The Voice of the DBA Podcast

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