Voice of the DBA

Category: Editorial

  • The Remote DBA

    I’ll start this week with a question, which I hope some of you answer in the discussion: would you like to, or do you, work at home the majority of the time?

    I remember when I worked in a company and needed to leave my desk and walk to a room somewhere to get some work done on a server console. Some of those rooms were cold rooms, which necessitated me keeping a jacket at my desk. I still remember going to a large company that had cables run from the data center to a couple specific workstations near the administrators’ cubes. At one point we installed a remote IP device allowing us to get to the console of any server without having to walk downstairs, or even use RDP, which was just becoming to Windows machines.

    That was the end of my visiting servers in person, and since then, the only times I’ve ever really needed to look at a server was when a critical error prevented me from connecting remotely. Even then, at many of the co-location facilities I’ve contracted with, I could call and have an individual go press a power button. These days, with cloud providers and virtual machines, even that is unnecessary.

    Those of us that have worked with SQL Server typically understand that we always make a network connection to work with the server. Even when we’re working on the server console, SSMS, SQLCMD, and more all make a “connection” to the database server. Therefore, is it really necessary that we ever work near a particular system?

    I’ve been working from home as a telecommuter for about 8 years. My wife worked in technology from home for nearly 20 years. More and more people are doing so, in fact, there was a piece on Fast Company recently that noted more people work from home than ever before. Unfortunately, this study shows that most people end up working more hours, adding a few from home to the 40 or more they spend at work.

    That’s not good, but if we can get some work done at home, why not more? I know meetings and face to face time matter, especially in some jobs, but more and more I find that lots of people that need time working alone could do a portion, perhaps a significant portion, of their work away from the office.

    So this week, would you like to do more work from home (or elsewhere)? Do you want more virtual meetings, more communication over email, Slack, Skype, or some other tool? Let me know.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The New Data Warehouse Choice

    I was listening to the SQL Data Partners podcast the other day with BI expert, Tim Mitchell, and the opening question was “Is the on-premises Data Warehouse dead?” Tim is a friend, so I tuned in knowing he has some good thoughts on the topic. It’s an interesting listen, and one you might enjoy if you’re at all interested in data warehousing and related topics. Spoiler alert, Tim says no, on-premise isn’t dead, but he does point out some interesting things about the Azure SQL Data Warehouse (ASDW) and similar offerings.

    One of the more interesting comments Tim made was about a health care company he worked for. They had an end of month process that heavily taxed their systems. If they didn’t need that peak level of processing, Tim noted that the cost of their large data warehouse architecture would be that halved. That need to scale up dramatically can be a big savings in moving to a cloud based system, where you can pay for a much lower level of performance most of the time and increase your scale at particular times.

    I know this is feasible as I worked with a similar situation. My employer purchased a very large system for our end of month and end of quarter closing load. Fortunately, we had an AIX machine that contained its own hypervisor. At the time (2001), we purchased a 32 processor server, with the idea that only 18 CPUs (and a slice of RAM) were running our financial systems most of the month. We had QA, development, and other guests on the same hardware. During the month closing, we would shut down some VMs and dedicate most of the processors to the finance system for a few days to handle the load. What’s more, the IBM machine actually contained additional CPUs that we could “rent” from IBM for a few hours if we really needed them.

    That’s what data warehouses in the cloud can do for you. Certainly the decision to move to a cloud architecture is more complex than just having the scale up power of ASDW or Amazon’s Redshift. The ability to load into the system, the development challenges, the tax implications, and more will impact the decision. I think the workload characteristics are also important. If you don’t have a highly variable, or large peak, workload, then the cloud might make less sense. If you don’t have any sort of data center, then maybe the cloud makes more sense.

    I do think, however, that the decision to implement a new data warehouse isn’t a simple one, and the cloud is a viable choice. The platforms are becoming more capable all the time, with more tools and scale options, as well as better performance guarantees. Many of the tools used to analyze data in a warehouse are more important than the underlying platform, with Excel, Tableau, Power BI, and more easily connecting to any data warehouse platform, in the cloud or on-premise.

    This means that we will end up managing more disparate systems over time, especially in larger organizations where some groups will adopt cloud systems while others stick with on-premise installations. Certainly if you are a person that works with a data warehouse, you might want to build a small POC on Azure SQL Data Warehouse and see what you think about its capabilities. At least then you’ll be able to add some educated and intelligent thoughts to the discussion when the question comes up inside your organization.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Losing Rows

    There has been a lot of hype in the last 3-4 years around NoSQL databases. Note that NoSQL isn’t Not SQL, but rather Not Only SQL, implying that the SQL language still has a place here. In fact, a number of companies that make NoSQL products have layered a SQL-like interpreter or interface on their products to allow familiar SQL language querying.

    One of the big reasons companies look at NoSQL is that the various types of databases have lent themselves to scaling easier than traditional RDBMS systems. In the CAP Theorem, these systems tend to be more in the AP range, sacrificing some consistency for scale and performance. That seems crazy to RDBMS people, but if you really think about your application, consistency that is delayed on the order of seconds or less isn’t that bad in most applications. Even seconds might not matter in many reporting scenarios.

    The thing is, databases aren’t perfect, and that goes for NoSQL systems. I ran across a post where MongoDB sometimes loses consistency within a single node. Now, this isn’t within a single document, so changes there are transactionally handled well (as we RDBMS people think of them), but for a query, you could have rows drop out and reappear according to some criteria, sometimes within a few minutes or seconds of each query.

    That is disturbing. In fact, I’d be terrified of this, mostly because I’d spend a lot of time trying to explain why and getting yelled out. Do you know how many times I’ve had business people run a report and then someone else run the report a minute later and try to compare things? If I had whole documents dropping out of one or the other, that would be maddening.

    Now, there are ways to fix this in a MongoDB database, and I’m not disparaging MongoDB over this. It’s a fine system and there are some good applications built on MongoDB that work well. There are some fine applications running on Neo4j, and on Couchbase, and other NoSQL systems. Some of these database work better than an RDBMS in managing particular workloads and problem domains. Some applications and workloads would do well with a NoSQL or a traditional relational database.

    The important thing to understand is how your system works. Your organization should understand what allows a database to scale and what doesn’t, what things limit HA/DR capabilities, which items might impact consistency across nodes or systems. In the SQL Server world, if you use Availability Groups, you should certainly understand what consistency means for the different databases in the same AG.

    NoSQL databases aren’t better or worse than relational databases by themselves. Finding a good fit for a particular database platform and your application requires some knowledge and planning. Ensuring a successful application is often less about the technology, and more about the people that architect and code the solution. Worry more about the latter than the former and you should be successful at building software.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Baby Steps to DevOps

    I’ve been re-reading the book, Continuous Delivery, as part of the current San Diego Technology Immersion Group monthly meeting track. This book was the focus of the first meeting in May, and will continue for the next couple months. Continuous Delivery and the related, DevOps, are fascinating concepts, but mostly I find the discussions really interesting for me to see and hear how others view the DevOps, Continuous Integration (CI), and Continuous Deliver (CD), and where they apply in each person’s organization.

    One of the things I’ve noticed is that lots of people get overwhelmed with DevOps. They hear stories of how others are running an extremely efficient software development shop and think they couldn’t implement CD in their organization. Or they think that CI/CD/DevOps is just a way of building software that’s so far removed from their own experience. However, DevOps isn’t really anything new or special. It’s not even one thing, as there are many divergent views of what DevOps means. However, plenty of people have been applying and adhering to the various definitions of DevOps for decades, just viewing their process as efficient, effective, and empowered.

    I use DevOps and CD somewhat interchangably, as they often proceed along the same path and each is a intertwined with the other. Note that going to a CD process doesn’t mean that you release every day/hour/whatever. If means that you release when you want, which could still be every few months. It’s just that you have a process set up to allow smooth process flow.

    As I talk to people who are looking to build a DevOps process, and I hear about more and more of them all the time, one thing I stress is to take things slow. You won’t build your DevOps process, whatever that looks like, this month. In fact, what you should expect to do is learn as you go, and whatever your vision of the process is today will change. You will learn, grow, change the way you do some things, and also change the way you implement new processes, even if you’re copying how other companies have implemented their own system.

    Most importantly, what I try to stress is to slowly move to CD (or DevOps). Look at the process you have now and just change one thing. Let’s imagine that you:

    • manually track changes to the database in email
    • then build a change script manually
    • then have a DBA apply those changes
    • then let QA run tests with an application
    • then modify the script that goes to production

    Leave that process as is. Let’s implement one thing. Add some version control that will let you build a base for other changes. Your new process would be (changes in bold).

    • manually track changes to the database in email
    • then build a change script manually
    • store the change script in a VCS
    • then have a DBA apply those changes
    • then let QA run tests with an application
    • then modify the script that goes to production

    Or change to this instead?

    • manually track changes to the database in email
    • then build a change script manually
    • then have a DBA apply those changes
    • then let QA run tests with an application
    • then modify the script that goes to production
    • track all changes to production in an automated fashion (hopefully using a tool)

    Over time, you can make another step. Maybe you use something like SQL Source Control or ReadyRoll to build change scripts and put them in a VCS. Or perhaps you build a process that takes the changes from the VCS and applies them to a test database without requiring the DBA to check things out and execute them. Both are valid ways to evolve your process. (Disclosure, I work for Redgate Software, maker of those products). I’m giving you a few suggestions here, but there are multiple ways, methods, and tools you could use.

    And that’s the key. You evolve your process, evolve your communication, learn to work together to accomplish goals. Not big goals, little goals. Add small items to your process as you realize they can be automated. Change communications in ways that help ensure everyone knows where to find code and how to deploy it. Or everyone knows how to set up environments (hopefully automated).

    Slowly get better and better, and before long you’ll find that you can make changes in smaller batches, and ensure those changes can be deployed at a rapid pace. Or maybe you’ll have confidence to begin moving to a feature flag architecture so that you deploy changes well in advance of users being aware of them. Note, they’re not perfect or the solution.

    DevOps is about learning and growing. This is really the same basic principles that have driven Six Sigma and Kaizen and various other philosophies that recognize that we can do better over time. Make small steps, measure, learn, and improve.

    Steve Jones

    The Voice of the DBA Podcast

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