Voice of the DBA

Category: Editorial

  • The End of Summer

    Today is Labor Day in the US, and a federal holiday. Not much of one for me as I’m flying to the UK today for a week of meetings and the Redgate Community Hub livestream on Wednesday. Register, tune in and watch if you can. I don’t feel too bad about working today as I’ve had a few light days in the last few weeks.

    When I was younger, Labor day was the end of summer. School typically started the next day, so this was our last day to go to the beach and surf, play volleyball, or enjoy ourselves with few responsibilities. It was the last day off for quite some time, usually until late October or even Thanksgiving in November. We appreciated this holidays as only schoolchildren can.

    These days I view the world differently. Labor Day often means the start of conference season, with deadlines approaching and travel ramping up. So many events seem to occur in the Sept/Oct/Nov timeframe that it’s a busy part of my year. This year is no different with travel this week and 4 more events before December. Hopefully I’ll meet more of you at one of those stops.

    Most of us are the laborers, working for an organization and receiving a paycheck in response. We don’t usually have organized unions in our industry, but we have helped spur economic growth in our countries with our with in technology and this is a good day to remember our contributions and celebrate them in the US.

    Happy Labor Day.

  • Database Development Made Easy

    I ran across this post on developing database with SSDT. It has a lot of steps, and reading through it, I find this to make some sense, but I’m not sure I think this is easy. I can see why developers find databases to be a pain to work with. There are a lot of steps in this post to just setup and configure a database project. Databases are fundamentally hard to work with, as the model of maintaining state between changes and ensuring data is not lost can be hard. While the concept is similar to keeping track of configuration files, the scale of data in a database is vast and ever changing. Tooling to manage data that might need to be recovered isn’t very practical.

    I ran across a developer that was trying to automate their database development. They used tooling to deploy a table to a production system. The application connected and data was stored in the table. The developer then dropped a column from a table and deployed this change. It worked, but this was a mistake and this person decided to deploy the previous version of the database, with the additional column restored. The deployment worked, but there wasn’t data in the column that had been dropped, and added back. Why not, asked the developer?

    Many people are of two minds here. One, any tooling or automation should preserve data and allow for rollbacks. In the application world, this makes perfect sense, and even the data our application uses (reference files, configuration files, etc.) are restored if we rollback to a previous version.

    For the data people, this makes no sense. The data in a column could be of significant size. We often plan for systems to reach millions (or more) rows of data, and trying to save the state of this data before a change isn’t practical. Even if we were to store changes items for a few deployments, it’s entirely possible that putting the data back wouldn’t make sense as related information in other tables might not match up correctly. Consider the case of a financial system and restoring old money values. Who knows what issues would be created?

    For developers, this highlights one of the things they dislike about databases. They are must manage state transitions across deployments, and rolling back to previous versions isn’t often possible. This is one reason that I have often performed a backup before major deployments, and even today, in an automated DevOps process, I’d want to perform a backup if any significant data were being changed or deleted.

    I’ve spent a lot of time advocating for DevOps and smoother, modern database development practices for the last few years. I don’t want the database to be a hindrance or impediment to change, but I also don’t want to compromise the integrity or safety of data. My pitch has always been that our database automation tools at Redgate don’t perform any magic. They smooth, and hopefully speed up, the process of making database changes that we’ve used for decades. They save you time and effort, just as other tools may do, but they can’t change the rules of relational database changes.

    Everyone developing code inside or connecting to a database needs to understand how transactions and data changes work. There are rules and restrictions the protect our data. These mean we need to sometimes plan and consider the consequences of our actions. This should also mean that despite wanting to move faster and make changes, we can’t treat data placed in columns as malleable in the way a method in C# can be changed back and forth. We need to account for, and protect, the information stored in our systems. There are patterns that can help you evolve your database from one state to the next, but there isn’t any magic that lets you drop and add data storage elements without some preparation for handling the data itself.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Who’s Responsible?

    In college and for a short time afterwards, I worked at restaurants in various capacities. I’ve held most of the jobs available and enjoyed my experiences. I had good and bad bosses and sometimes had to endure questionable practices from management. Often I needed money, so I couldn’t take a moral stand and was at the mercy of some manager. I think a bad restaurant manager at a part-time bartending position eventually caused me to choose to leave that industry. With a fulltime day job in technology, I could forgo the extra weekend money to avoid additional stress.

    Bad managers and practices still exist, and I was reminded of this in an article recently. The practice of forcing employees to pay for mistakes is blatantly illegal, and I hope someone gets a few days in jail as a result of the case. However, I started to wonder if this trend might ever make it to our industry. Especially with the changes in services as more systems move to cloud environments.

    I’ve worked in companies that debated my expense reports, and refused payment for some charges. Some of your might have had the same experiences, which often cause you to be very cautious about future expenditures. That could be good or bad for business, but it certainly is a problem for the employee.

    These days I find plenty of employees that will setup or provision some cloud service using their own credit card, expecting to be reimbursed at the end of the month. What happens if the company doesn’t want to pay? A manager decides the service wasn’t necessary. What would you do? What if you provision the wrong service, or forget to terminate something. These are common mistakes in business, but would you pay for the mistake? What if you didn’t pay for the service, but the company wants to deduct the charges made by mistakes from your paycheck. Do you fight this? Bring a lawsuit? Plenty of possible actions might ensure you aren’t financially penalized but could cost you your job.

    I’d like to think that this wouldn’t be an issue for salaried, technology workers, but I wonder. Is there any reason to worry? Let me know what you think.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Graphing Performance

    We have a lot of different database platforms to choose from when building software. Most of us reading this are SQL Server users, and likely relationally biased. However, key-value stores, document databases, graph databases, and more are out there. If you work with developers that embrace change and new options, likely you’ve been asked about implementing some sort of NoSQL database instead of SQL Server for some project. Maybe you’ve even been asked to migrate away from SQL Server to an Open Source (OSS) NoSQL platform, with the lack of software cost being a factor.

    I do think that there are some domains of problems that relational systems don’t handle well. Certainly at scales (data volume or rate), there are better ways to deal with some data sets in a less structured and tightly coupled way. We see that in the large scale web companies like Google, Twitter, Facebook, etc. If these companies had tried to build their entire system on a RDBMS platform, they would have struggled to grow, and maybe not even reached the size they are.

    I’ve been reading and playing with the new graph capabilities of SQL Server 2017, trying to determine what I think of the concepts. Certainly large scale many-many relationships don’t seem to be a strength of relational databases and I’ve thought there are certain types of queries or data models that might be better handled by a graph database.

    Then I ran across this report from a few researchers that examine how graph database compare to relational ones. After all, we’ve grown accustomed to using RDBMSs in many environments and situations. What better way to evaluate the performance of a specialized database than compare its performance in the problem domain its designed to solve to that of a general database platform.

    The results are a little surprising. Even with a sub-optimal query language, I would have expected the graph database to perform better. Instead, relational seems to handle the reference graph workload better. Raw performance isn’t everything. Ease of development and ability to scale are important. There may be other considerations in your system as well, but I did find this to be an interesting paper.

    We will see how the world of specialized databases handles real world workloads over time as more companies use them, but for now, I’d be skeptical of replacing an existing, working RDBMS with something unproven. I’d need to see a good POC that shows quite a bit of improvement across a variety of metrics, not just scalability.

    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.