Voice of the DBA

Category: Editorial

  • What’s Downtime?

    There was a time when I worked for a company that sold products on-line  Since our wares could be purchased at any time of the day or night, we wanted to ensure that our systems were running all the time. This led us to build some sort of monitoring, which we tried. That led us to buy some monitoring software, which we did. This led us to build more tools, and it felt like we were in an endless loop for a period of time.

    Eventually we stepped back and tried to answer the question that many Operations people have asked themselves and others: what is downtime?

    It’s a tough question, and I want to give you a few examples of how I’ve viewed things, and debates I’ve had. For example, we had a database server and a web server. We used a simple script to ensure that the services (IIS and SQL) were running on both machines. If they weren’t, we received a page. Is that sufficient to detect if our system is working?

    We also had a process that would ping our web server from outside the data center, using a public machine. If that works, is the system working?

    In this job, we deployed new code every week, in a DevOps style process that existed before anyone had ever uttered the term. These updates sometimes included schema changes, but almost always included application changes. If a page on our website broke after a deployment, was our system up or down?

    We integrated with some third party software to perform various tasks. There were times that we couldn’t communicate with the third party, or received broken communications. In those cases, were we up or down?

    We built our application to work with multiple browsers, but at times there would be a new piece of functionality that didn’t render or work correctly on either a new (Firefox)  or old browser (IE6). Did that mean the application was down?

    Determining uptime isn’t a single thing. Even when you provide mechanisms that ensure all parts of your application are working, are they working for everyone? Many of us might see this in various online calls, where a system like GoToMeeting or Skype might work for some of the audience and not others. I see this at times with Microsoft sites where some of us can use one of their online systems, but others can’t, sometimes because of the browser of the end user.

    I was thinking about this while researching zero-downtime deployments, which can be hard for database changes. There are people that have success, but many others don’t. At Redgate Software, we are trying to build tools to make this easier for everyone, but there seem to be plenty of edge cases that cause issues. There are also many different processes and flows that groups use to perform database development, which often affects the final deployments. It is hard to build a general solution that needs to apply to specific environments.

    I tend to learn towards measuring uptime of the systems I’m responsible for and letting others worry about intermediate infrastructure. I’ll caveat that with the note that I sometimes only worry about sections of the system and if those are broken. It’s good to be clear when talking about this topic with others. For example, we might be able to take orders, but can’t report on them, or can’t add new customers. That’s downtime for some sections of our application, but less stressful than if we couldn’t take orders.

    Let us know today. How do you measure downtime or uptime, and where is your responsibility?

    Steve Jones

    The Voice of the DBA Podcast

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

  • In Memory of Robert

    I was returning from a trip yesterday when I got an email from a friend that Robert Davis (@sqlsoldier) had passed away. Unfortunately, this happens more and more to me as I age. Family and friends are getting older and some will leave us sooner than we’d like. A Memorial and Grief fund has been set up, if you’d like to contribute.

    There are various tributes from Kendra Little, Thomas LaRock, Jen McCown, and likely a few others. Robert was a friend, and I’m again saddened by his passing. If you knew of Robert, either from reading his blog, following him on Twitter, or attending one of his talks in person, you know he was a very intelligent, humorous, and as Tom says, quite generous soul. I was honored he syndicated his blog at SQLServerCentral, but it wasn’t because of me or the site. Robert was overly giving of his knowledge, always looking to help others with their problems. He probed and questioned the questioners, trying to ensure that he could provide the best advice and solutions to others.

    It wasn’t easy, physically, for Robert to always get around, but he made the effort to attend many events in person, and help others even though it was a challenge. I was always pleased to see him in person, shake his hand, and marvel at the lengths he was willing to go in order to help others and share his wisdom and experience. Others have noted his sharp mind and amazing recall. I’ll point to the 92,800 tweets he left behind, many of which were in service to others.

    Kendra noted his New Year’s Resolutions for 2018 included more time for Robert and less work. Those are important mantras for us all.

    I encourage people to work on their brand, or their career, or improve their skills. I really want all of you to do those things. Become better craftsman and craftswomen and improve the state of our industry. I want you to work at being better. When I present this live, or even write, I do want you to always keep this in mind.

    We work to live; we don’t live to work.

    Find a balance in life, find time for family, faith, hobbies, and friends. In the last decade, I’ve lost a number of family and friends, far too many in their 40s or 50s. I’m at the age where I likely have less life ahead than behind me. Each of us never knows how many days we have left.

    Find a balance and ensure you enjoy your life along the way.

    Robert, you are missed, and I hope Chrissy finds solace and strength in your memories. Please consider contributing to the Memorial and Grief fund.

    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.

  • Is the DBA Title Dying?

    Not is the DBA joib dying, though we could argue about that as well. Instead, I’m asking if the title of Database Administrator is going away. Are there going to be people that really want to send out resumes and apply to be Database Administrators beyond the next few years? As I look at my resume, I wonder if that’s a job or title I want to keep around.

    Think about that for a few minutes. We’re in the age of complex systems with cloud platforms, automated backup software, PowerShell and other scripting, Chef, Puppet, containers, self-service clones, automatic indexing, query store plan fixing, and more monitoring options than you would want to spend time evaluating. We have plenty of tools to assist, or takeover, much of the daily administration of database platforms. Even security can be mostly outsourced to AD, AAD, other directory services. While there is some level of what we might consider administration, is that the core of many people’s jobs? Will it still be in five years?

    I read a few of the posts from T-SQL Tuesday #100, including Adam Machanic and Brent Ozar. They both think the DBA is dying, and I tend to agree. I know that inertia slows change in many large organizations, and I’d bet there will be a delay just because of HR. After all, if you want to be a data reliability engineer or data professional, most organizations don’t list that job title and would have no idea how to hire you. Even if they loved your resume, they’d end up hiring you as a DBA or developer (developer III maybe) just because they like those nicely labeled buckets for the HR systems. They might not even know how to pay someone in some new role.

    However, the more I talk with people, the less they seem to be doing administration. They still tune queries, but often they’re helping do more database development or even database architecture than administration. They may to more reporting or ETL work, which can easily fall under development as well. Security is still a part of their jobs, but that doesn’t seem to change as often as it used to. Many people have moved to database roles and AD integration for authentication. Then security becomes more of a set-it-and-let-someone-else-manage-it. I even see Slack bots or other tools that let people self-service requests, and others quickly (or automatically) approve them.

    We still do some work, as things like HA/DR still aren’t quite a smooth as they could be, but that’s changing. Cloud services, and the evolving Azure Stack may mean that more and more of our work will be done by templates and patterns. We may have to create the template, but that feels more like architecture and less like basic administration. I expect at some point we’ll just link a grid of machines, on premises or in the cloud, and let people self-service their requests for systems. The “template” will let a service deploy HA databases across the machines as it sees fit, providing an address to connect to, implementing monitoring, and even solving many simple problems without human intervention. I expect that security and auditing features and capabilities will also grow rapidly, becoming more automated, or at least automatic, and request less administration.

    I used to joke I was a data janitor in many jobs. Just dealing with whatever situations people couldn’t, or wouldn’t, bother working on with databases. I often cleaned up messes made by developers that weren’t sure how to build a database that scaled beyond “their machine”. These days I think I’m no less of a data janitor, but I call us data professionals. It’s a better catch-all term, and certainly sounds more appropriate. We do our best to ensure the safety, accuracy, and availability of data in whatever way works best in our environment. We’ll run your T-SQL, R, python, machine learning, SSIS, SSRS, or whatever code for you, and audit the actions.

    I think that as we do that, we’ll do more development, cleansing, and analysis than actual administration.

    Steve Jones

    The Voice of the DBA Podcast

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

  • ETL or ELT

    I was reading a piece on ETL patterns and found it interesting that the author noted that one way to reduce the challenge of ETL processes keeping up with changing schemas is to move to an ELT process. That’s an interesting perspective, and I wonder what those who work with the relational model think. IS this a pattern you like?

    In a traditional ETL pattern, we get data from a source (Extract), manipulate it to suit the target schema (Transform) and then Load it into the target. The hassles here, and the reason this is hard, is that the transformations are often complex. We deal with ragged rows, various delimiters and terminators, and more. Lots of consultants have made quite a bit of money by building these transformational applications and processes and maintaining them as requirements change.

    The flip of the last two parts, moving from TL to LT, looks to defer some of the transform work until the data is in the destination. This means that the transformation could be a schema-on-read, as the article proposes. The idea here is that all data is ingested into the target system as is. This means that structured CSVs reside alongside spatial data and Word documents.  Then, once you have the data, you can transform it as needed, to suit whatever requirements exist at that time.

    Certainly this sounds appealing, but for many of us in organiztions, the challenge is still determining how to transform the data. This is often dependent on what the data is and how it can be used. We still need to do the work to understand how this data is structured and change something to make data available to the end user. Whether we do this in the middle or after the data is in a target system doesn’t matter. I know some products, like the MarkLogic one in the post and Azure Data Lake want to reduce must of the ETL effort, but I’m not sure that will work for really important data. For some, sure, we can collect it and sort out the transformations later. However, for useful data, data that the business wants regularly and quickly, most of us need to just get a flow working that will move data on a regular basis.

    I’ll admit that I often prefer to transform data inside of a SQL Server target, OLTP or DW database. The reason is that mass changes are often easier with T-SQL, working sets of data rather than row by row. I recognize that doesn’t always work, but I know that for most data that business people do find useful, I’d rather have an ETL process build sooner rather than later.

    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.