Voice of the DBA

Category: Editorial

  • Containing the Work

    Containment is becoming more prevalent in the computer world. We have containers coming to Windows Server 2016 (or earlier versions with WinDocks), a concept that’s been embraced in the Linux world for some time. If you haven’t looked at containers, they are a great way to isolate and stabilize your application environment. They can also be great for dev/test environments. You might do a little reading to understand how they can be used.

    While containers might be good for SQL Server (or maybe not), we’ve had other types of containment in SQL Server. We got partially contained databases in SQL Server 2012, with little change since then. That is susprising to me since I expected that all Azure SQL Databases would be contained, and we would want more functionality inside of them. However, those have opened up slightly and I’m not sure where containment is going.

    Personally I’d like to see jobs contained inside of a database. The vast majority of processes I’ve written over the years for application or system work on a database pertained only to that database. I’d like to see things like backups, maintenance, etc. contained as jobs inside a database, that travel with the database as I detach/attach it, backup/restore, and more. That might eliminate some of the issue with clusters and AGs if most jobs moved with a database.

    That got me thinking. What else would we want to contain? The thing that comes to mind is a distributor. I’d like to have a distributor somehow contained with all the information about replication processes contained inside of this database (including the jobs). The database would need a way to register with the instance so the publisher and subscribers could find it, but wouldn’t that make replication administration easier?

    How about the SSIS Catalog and ETL information. Having more information here as a contained database might make managing ETL operations easier, especially if we need to move the load to another instance.

    I’m sure there are other ways in which having information, metadata, and job functions contained inside of a database would be helpful, and easier on the administration of the system, whether by humans or automated systems.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Null Defaults

    One of the things that becomes important in a distributed, team environment is the interface that you present to other systems. In a Devops environment, we really need to have an API for every system, including the database. This means a contract for our database, that spells out what access points are available and what they return. For our data, this often means the structure and shape of a table.

    Our tables often aren’t static. In fact, they grow and chance over time. This means that the values returned from the table, or even views or stored procedures referencing the table, will change over time. In some sense, this means we’re really versioning our API. If that’s the case, then as you make additive changes, you’ll be adding columns for the most part. The will be time when your application, or maybe a subset of your applications, will not know how to provide data for those columns. In those cases, we need to make a decision about how to handle the column.

    What’s your default choice? Do you use NULLs when you aren’t sure? Perhaps you choose some magic value, such as a blanks or a known date? I know some people like to use 1900-01-01 to mean an unknown date, or even a number such as 99999. Any choice has advantages and disadvantages, and what works for one person might not work for another.

    Let us know your default choice and why. I’m sure some of you have great reasons for why you choose a value, and I bet more than a few of us learn something about why one value might be better than others. I’m looking forward to reading your thoughts.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Detecting Issues

    Here’s a simple question: how are more of your application issues detected, by people or systems? I bet most of you initially think of your monitoring systems and the automated messages or pages that are sent out regularly as detecting most, or even all, of your problems. Have you stopped to think how many times a phone call lets you know about an issue? Do you consider the ways in which a code review or human tester brings up a concern?

    I try to think about all software problems, both the ones that reach production and the ones that are prevented early. If I catch a SELECT * in a view during development, I can prevent a problem months later when a table adds a column and no one refreshes the view in production. Those potential issues that never get to the customer are wins for me, and I think this is something we should be proud of as software engineers and testers.

    Can you move the numbers, though? Is there a way to find problems before people find them? I think there is, and it’s with better monitoring and better testing. For monitoring, we need better, and more, instrumentation that measures what we expect, looks for deviations, and (low level) alerts someone. This is an area where I think machine learning and better analysis will help. Those ML models can be hard to setup, so I’m hoping that some individuals or projects will start some work here. Microsoft is doing some of this in Azure, and I hope they share some knowledge with us.

    Testing is really the way to catch more issues before humans do. We’ve known this for decades in software development, but so many developers have been resistant to the idea of building some sort of formal test for their code. It’s not fun, it’s hard to maintain, and really, it’s just hard for most people to start writing tests.

    I think things are getting better with testing frameworks that make building and executing tests easier. We have frameworks for all major application languages, and even quite a few for T-SQL. We’ve also learned more about the types of tests to write, which type to ignore, and how to avoid building so many brittle tests that testing is more work than coding features. If you know nothing about testing, you owe it to yourself to spend a little time learning about unit testing and practicing writing tests.

    Now that we are collecting more and more data about our applications, we have the opportunity to really build software that better meets the goals and needs of our customers. However, we have to take advantage of this data, and the advances in testing, to ensure that we build the best software we can.

    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.

  • Containers and Databases

    There’s this push to use ever thinner and lighter weight computing techniques. We moved from mainframes to servers to blades to VMs, and now many developers are looking at containers more and more. What started as a Linux idea popularized by Docker has come to Windows, with container support in Windows Server 2016. Just recently SQL Server 2016 Developer edition was announced as a container, which makes deploying a SQL Server easy, with no install.

    Is this a good idea, though? Containers certainly make sense for many applications. Got a flaky Java app that crashes? Put it in a container, avoid dependencies on the OS, and if there’s an issue, restart it quickly. Got a service that needs to scale? Put it in a container and run multiple instances. Messaging, endpoint services, middle and front end apps, all of these can make fantastic use of containers.

    Containers fit in quite a few spaces, but do they make sense for databases? I’m not sure. After all, one of the advantages of containers is that they are stateless. Drop one, restart another. That’s not something we want to do with databases, as we need the data to persist. Containers also help when the application is unstable and may need restarting, but SQL Server, along with most database platforms, is very stable to run.

    Where do containers make sense? I think for development and test environments, containers have value. These are places where we may need to stop and recreate an environment quickly. Certainly  a container with a small amount of data, say a curated set of test data, is a great way to try or test code, then destroy the container, modify something, and repeat. A DevOps process, repeatable with containers.

    In production, however, I’m not sure that there is much of a place. Perhaps Express makes sense on laptops, where we can avoid the install of a SQL Server, but certainly the data needs to persist outside of the container. We’ve seen how to do this with WinDocks, and I’m sure some vendors will deploy containers in this way once they are available on laptop OSes. However, on servers? I don’t see the place where a SQL Server container makes sense, but please, let me know if you have a place for a containerized database.

    Steve Jones

    The Voice of the DBA Podcast

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