Voice of the DBA

Author: way0utwest

  • 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.

  • Using OPENROWSET in SQL Server on Linux

    I wanted to import the million song dataset in SQL Server on Linux. There’s a github repo that has the SQL to allow you to use this with the graph database features. However, it’s built for Windows.

    Linux is a slightly different beast. Once I started down this path, I had memories of working on SunOS in college, messing with permissions and moving files.

    I run Ubuntu in VMWare, so I first downloaded the files to my Documents folder. That’s pretty easy. However, once there, the mssql user can’t read them. Rather than mess with permissions for my home, I decided to move these to a location where the mssql user could read them.

    First, I need to use mv to move the files. However, the default location for SQL Server (/var/opt/mssql) doesn’t let me drop files in there. Instead, I need to sudo the mv.

    sudo mv unique_tracks.txt /var/opt/mssql/unique_tracks.txt

    I repeated this for each file.

    However, I still had permissions errors. Files have their own permissions in Linux, so I needed to alter those. I decided to use chown since these are temp files the SQL Server will use and once imported, I’ll delete them.

    chown mssql unique_tracks.txt

    From here, I could easily run the OPENROWSET commands and get the data loaded. Now to play around with a graph.

  • Speaking at the Data Platform Summit 2017

    I am honored to be speaking at the Data Platform Summit 2017. The event takes place August 17-19, 2017 in Bangalore, India. This will be my first trip to India, and I’m excited to be traveling halfway around the world (literally 12:30 hours difference) to speak at this event.

    I’ve got a pre-con on Aug 16, and then a few sessions during the main conference.  If you have the chance, register and come. I’ve heard good things about the event and I look forward to seeing some of you there.

    See if you can pick me out of the Teaser Video:

  • 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.