Voice of the DBA

Category: Editorial

  • The Traveling Data Professional

    It’s summertime in the Northern hemispheres, and a time when much of the world takes their holiday time away from work. Most kids are out of school for at least part of the June/July/August time period. With many of us workers trying to spend time with our families, this is the part of the year that we schedule trips and events.

    Taking time away to be with family is important, but just getting away from work is healthy for all of us. Whether it’s a planned trip to some new location, visiting relatives in another city, or even just taking time to enjoy a leisure activity, we all need a break. Some of us will try to do too much during our break, arriving back at work physically worn out. Even in these situations, it’s still nice to get your mind away from the environment you face most days. I think any break is good, and especially in the era of smartphones and nearly ubiquitous connectivity, time away is good for your health.

    This week, I’m wondering if any of you have plans for an adventure that you’re excited about. Are you going somewhere new or engaging in some activity you look forward to? Perhaps you don’t have big plans this year, but something you’re working towards in the future? Make us jealous and let us know what holiday break might be coming your way.

    I’ve been more of a winter person for most of my life, looking to take time off from work when there’s snow on the ground, but I’ve had my share of summer trips. Usually my family will camp and take a few trips. With my children getting older, I expect to take even more trips when it’s just my wife and me. Since I can work from anywhere, I can follow along on some of her horse adventures.

    This year we had planned on a long trip to Glacier National Park, but with older kids, scheduling looks to be a bit of an issue, so we’ll likely stick to camping in Colorado. However, I am honored to have been chosen to speak at the Data Platform Summit in Bangalore, India, so we’ll all have a bit of a vacation in August as we travel to a new country.

    Enjoy the weekend and let me know what your plans are this year.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Backup Plans for Data Loss

    The British Airways computer failure has been on my mind for a few weeks. It seems like such an epic failure in 2017 from a high public company that many, many people depend on. I still struggle to believe the power explanation, and really, I’m not sure there’s any explanation that I would accept. At this point in our industry, there’s no good reason for any large, global company to not be able to restart services inside of a couple hours in a DR site. In fact, it really shouldn’t even take that long.

    However, for many of us, we will have a failure or disaster at some point. It might not even be a hardware failure or system crash. It’s much more likely that a user will cause an issue. As our systems grow larger, perhaps even more loaded with transactions, we might not always be able to easily separate out good data from bad, and I would expect we’ll experience a restore.

    For many of us this will mean we will lose some data from the system. Even with frequent log backups, we might end up with a short period where we can’t recover data. Most of us should have conversations with business stakeholders on what the acceptable level of data loss is, and plan to meet those requirements. We should also have plans around how to rebuild data. I wouldn’t recommend a full test on a system, but it might be worth a few conversations with those that deal with transactional data and discuss how the latest data might be recreated.

    No one wants to lose data, and in many cases, there are ways to rebuild or recover the data with manual efforts. Perhaps your company has paper records, or maybe there’s an audit trail that could be used to reconstruct actions. Maybe you rely on memory or even customers to provide information again. Today I’m wondering if you’ve thought about how you might recover data in a non-technical way and what methods you’d use.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Migrating Schema Between Platforms

    PostgreSQL is a popular relational database, one that was designed to be an object relational system, based on standards, and allowing for extensibility. I’ve always thought this was a great system, exceeding SQL Server in some ways, though falling short in others. It’s popular, and certainly used enough so that Azure and Amazon Aurora include PostgreSQL compatability products.

    I ran across an interesting post, where AWS has a Schema Conversion Tool that will help you migrate from Oracle to Aurora running PostgreSQL. I think this makes some sense as there are some capabilities in PostgreSQL that make moving Oracle code easy. AWS also has a Migration Service to move SQL Server to MySQL. I’m not sure how smoothly that goes, but SQL isn’t a terribly complex language and perhaps the conversion isn’t too bad, though I’m sure this isn’t painless.

    I wonder how many people will choose to move their database systems to a new platform. Whether you’re on a commercial database, such as SQL Server or Oracle, or on a free one (MySQL, PostgreSQL), changing the underlying platform is disruptive for many applications. I’m sure there are some software packages that could change a driver and connection string and their code might work, but I bet that’s relatively few systems.

    For most of us, the cost of change is high. New skills need to be learned and new code written. Staff may leave, requiring an organization to hire new employees, which is always a bit of a gamble. The entire process is also a disruption that prevents other work from being completed in a timely manner. Often a large ROI is needed to justify the change, though that’s not always the case as sometimes management decides to change for other reasons, regardless of cost, ignoring any ROI. I have even seen some developers psuh to make the platform change, working many extra hours because of some desire to port to a new platform for dubious reasons.

    I do see plenty of companies looking to move away from Oracle systems, with a very large cost for support and maintenance. While SQL Server isn’t always cheap, it is much less expensive and Microsoft has programs to help port Oracle to SQL Server. Companies may also choose to move to MySQL or PostgreSQL from Oracle, accepting the disruption and costs with a long view towards reducing their yearly outlay for database services.

    I have migrated platforms before, and it’s been an ordeal each time. I haven’t ported code to a new platform, and not sure I would. Perhaps if we were abandoning a codebase and rewriting the application I’d consider it, but in most cases, the cost of staff and time to rework code makes this an expensive proposition. If you have different thoughts or experiences, let us know today.

    Steve Jones

    The Voice of the DBA Podcast

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

  • DevOps Can Help

    Amazon had a load balancer failure in 2012. The analysis of the event shows that there were missing data in the devices that caused issues. The restore of data from these devices is complex, way more complex with less mature tools than most database platforms. The result was a nearly 10 hour period of time when some customers were experiencing issues.

    In 2016, Gliffy had three days of downtime from a database error. In this case, an admin was updating a replicated system, but failed to sever a link with the primary node. Forgetting this step caused a data removal on the node, which replicated to the secondary nodes. They discovered the restore and replay of logs would take many days due to the size. They hadn’t practiced a DR situation in some time, and were not prepared for the delays.

    Digital Ocean received alerts earlier in 2017 that some services were not functioning. They traced this down to the primary database being deleted. The issue was a process used the wrong credentials for automated testing, and I’m guessing that part of the testing was removing and rebuilding a database. Five hours across the middle of the night resulted in the main database being restored, and a couple more hours to get replicas caught up.

    In the first two cases, there were issues with the deployment of changes to systems, as well as inadequate backup and restore processes. In both of these cases, I would argue that a good DevOps process would have automated the way the code was deployed, including ensuring that steps weren’t forgotten or predeployment backups captured the state of configuration. DevOps includes the “Ops” changes and should ensure that all state information is captured and stored in a VCS. If this had been done, it’s possible that these companies wouldn’t have had these issues.

    In the last case, certainly whoever sets up a system is responsible for using the correct credentials. While it’s easy to say that a developer or tester shouldn’t know the production credentials, but it’s entirely possible that the person that configured the process would have the credentials. I don’t know what to do here, as the first test of this might cause the issue. Maybe a second set of eyes is important for security changes in automated systems? That certainly could be part of your DevOps process. What I’d like here is two factor authentication for all security setup, including for SQL Server.

    DevOps isn’t a prescriptive set of things that someone does. Whenever I talk with people about DevOps and they give reasons why a particular step I’ve demonstrated won’t work for them, I tell them to stop doing that step. After all, the way you implement DevOps doesn’t have to match what I did. We each need to do what works for our environment, and ensure we have some consistency and repeatability in our process. Hopefully preventing downtime from simple mistakes.

    Steve Jones

    The Voice of the DBA Podcast

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