Voice of the DBA

Category: Editorial

  • Abstraction

    One of the core tenets of good software design is to abstract away details in any particular part of an application. We want to use well defined and constructed interfaces so that the implementation of any particular method or section can change without affecting the way the system works. This allows for improvement and upgrade over time. The same thing occurs in hardware, where we can replace a hard drive, a graphics card, or other components and the system should still function in the same manner. There might be a bit of work, such as updating a device driver, but the core system should still work.

    This is also present in the real world. I can replace the wheels and tires on my car, since as long as I have the same pattern for the bolts to attach to the axle, things still work. Electrical systems work this way, allowing any device that has the correct plugs and uses the expected voltage to interface with an outlet. The examples of abstraction are numerous, and the more we use abstraction, the more flexible our systems can be. Where we haven’t abstracted away details, it becomes complex and expensive to change part of a system.

    In a database setting, we want to use abstraction where possible. The use of views or stored procedures (or functions) allow the underlying table implementations to change without an application being too tightly coupled to the structure. This isn’t always well adhered to, despite the well known practice of building a data access layer into an application. Too often developers want to tightly couple their application to the underlying table structure.

    But, how abstracted should you be? Certainly I’d hope that your application had the ability to easily change connection settings, and these days I’d hope you actually had two: a read/write connection and a read-only connection. What about the database name? Should that be abstracted away? Again, I’d hope so, even in a multi-database application. If for no other reason than to simplify development by allowing the database name to change on a development server. Certainly security objects, especially encryption mechanisms, need some abstraction to prevent the requirement that they exist in non-secure environments.

    Are there other abstractions you’d want to see widely implemented? I wonder what other examples might be important to developers or DBAs out there. I know that allowing abstractions also brings complexity, and the ability to change those values between environments is critical. This could be performed with injection of different parameters as software changes are deployed, but the mechanisms for doing this are still immature and not standardized.

    There are plenty of other places we can abstract away implementations, but without mentioning too many, I want to know what you’d like to see. What abstractions would you want, or do you implement in your systems?

    Steve Jones

    The Voice of the DBA Podcast

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

  • A New Sample Database

    When I started working with SQL Server we had the pubs database. This was in SQL Server 2014, and I only used it because code samples from the Internet used it. It wasn’t a great database, but it was consistent and known. When Northwind came along, it was a welcome improvement. The schema was larger and a better fit for showcasing various features. However those two samples were superseded by AdventureWorks.

    AdventureWorks (ADW) was a fictional bicycle company, and we got a much larger, more complex schema. Perhaps overly complex, but many of us have learned to work with AdventureWorks for our sample code and demos through the years. This sample was released with SQL Server 2005 and then expanded and grown with 2008, 2012, and 2014, including newer features like Filestream and Memory-optimized Tables. However the complexity sometimes caused issues for people that didn’t want those features.

    There are multiple versions of ADW from MS, a script to make it larger, or even larger, a workload generator, a version for Azure, and more. It seems as though lots of time and effort has been put into building demos and tests against the ADW database. However that might need to change. I saw a note that Microsoft has a new sample database, Wide World Importers, on Github. It’s for SQL Server 2016+ as it includes a number of items that are only available on that version. So far, it’s bare bones (31 tables, many of them System-Versioned), and MS is looking for comments for improvement. There isn’t a lot at Github yet, but I’m expecting that to change over time.

    The idea of having sample database is good, but there’s effort to maintain them. We’ve had a few over the years at Redgate for demos, but we may move to using a Microsoft one so we don’t have to maintain it. Our preference is AdventureWorks, and maybe we’ll end up keeping it alive over time. I’m not sure I want a new database, but I also disliked the confusion of which AdventureWorks database versions would work with which SQL versions. Over time the database was named to include the version year, but it could still be confusing.

    Perhaps we should get a new sample every version, but not completely new. Let’s have a core schema that’s always available, with some tables that are normalized, some not. Some with PKs and FKs, somewithout. We can add new objects for new features. Let’s ensure that older scripts work by leaving old objects alone, but new items covering all aspects of database development, are included. Let me know what you think today.

    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.

  • Accept Failure

    Today’s Editorial was originally published on Feb 22, 2012. It is being re-run as Steve is out of town.

    We don’t expect ourselves to be perfect, do we? Is there ever any project you tackle that you might not complete? Is there a doubt that it might not work as expected, or that it may need substantial rework? I think that the vast majority of projects I undertake have some level of risk involved, and while I might understand that, I’m not sure I ever believe I will ever fail.

    Most things that I’ve built in technology don’t work the first time, and in fact, I expect that. I have learned from mistakes, corrected the problems, and usually finished them with some level of success. That’s the way that so many of us in technology approach our jobs. We start building, find issues, and then fix them.

    However you cannot every eliminate the risk that something will fail. There are times we need to abandon the project or abandon the work done and rebuild the software from scratch. Those failures should be learning opportunities, and should allow developers to improve their work. From my perspective it seems that too many managers, however, view failures as events that have to be avoided. Perfection and success are the only possible outcomes that are acceptable. One slip up and you may get fired.

    It seems that’s what managers think about their career, so they continue to push down dead end roads, and throw more resources at a project to recover some small level of success.

    We will always make mistakes. The true failure should come from failing to learn from the mistakes and improving your future work. If management cannot tolerate these setbacks, this problems, and allow for them, then the work will not only continue to be substandard, but people will spend more time worrying about avoiding blame than actually looking to improve their skills.

    I can’t tell you when work should be abandoned, or a project is hopeless, but every project ought to be examined periodically for this situation, especially when it is apparent that it is in trouble. You can’t save all projects, but you can learn to let some of them go, or change the situation, before it becomes a bigger problem than it is.

    Steve Jones

     

  • Another Re-Release

    If you haven’t noticed, SQL Server Release Services dropped an updated CU6 for SQL Server 2014 SP1 this week. This replaces a previous CU6 that had a NOLOCK bug in it, and the old KB article (and patch) have been deprecated. You can install the new CU#6 on top of the old one, and you should. The old patch could cause you some issues, so if you’ve applied CU#6 previously (build 12.0.4449), go download the updated patch, test it, and apply it to your instances.

    However, there’s a couple issues with the process here. First, re-releasing a patch under the same name (with a different build) is confusing. I am sure there are going to be plenty of people, especially accidental DBAs, that think they’ve applied CU 6, and they don’t realize there has been a change. There will be others that apply the patch from an old download that’s shared on their file system. I’d much rather have fixed patches released as a new CU. What does it matter if CU #7 is released now instead of in a few months? There’s no limit I’m aware of for the number of CUs allowed for a particular version, so let’s just increment numbers.

    The second issue, for me, is that this eats up time. Releasing quickly is one of the problems with an agile approach, where you update software quite often. There isn’t necessarily enough time to completely test the the fixes, and as comprehensive as the Microsoft testing suite is, there will be things that are missed. I certainly think Microsoft deserves kudos for finding the issue and releasing a fix so quickly. However, will this patch be distributed as quickly as the original CU #6?

    If you are used to applying these CUs, are you going to notice there is a new version of this CU to apply? The blog entry title doesn’t note this is re-released. If you look for the latest patches and see CU #6, will you realize this fix has been updated? If you know there’s a new patch, will you have time to re-test the update and schedule another release? I know from experience in a large organization, re-applying Service Pack 3a for SQL Server 2000 was a chore, with limited time to re-deploy a patch among all the other work we had scheduled.

    One thing I’ve noticed is that more and more companies are depending on their databases more often, demanding higher uptime and fewer maintenance windows. The more patches we have, the more troublesome it can be to get permission to apply these patches, especially across a large server farm. Microsoft is building a better engineering process, that allowed for more comprehensive (internal) testing and quicker releases, but this process doesn’t necessarily prevent all mistakes. Those mistakes are not only bad press, but they reduce confidence in the entire process.

    I do think these CUs will start to take the place of Service Packs at some point, though I think the pace will become problematic for many organizations, especially those running third party software. I’m guessing that at some point, a good portion of the SQL Server community will start treating these patches like upgrades, and not applying every one. Many people will end up applying only every fourth or fifth patch, much like people seem to be upgrading many instances every 6 or 8 years.

    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.