Voice of the DBA

Author: way0utwest

  • True HA and DR

    For many years, SQL Server administrators have tried to ensure their systems run smoothly by executing DBCC commands against their production databases. As workloads have grown, this has become an issue with resource contention with production users. Backups have had the same issue, and the smart DBAs have offloaded this work to another server. With the more recent versions of SQL Server, we could move backups and DBCC checks to secondary systems.

    The problem with this has been licensing. Microsoft has required a secondary node (often referred to as passive, which Allan Hirt notes is incorrect) to be separately licensed if any activity is performed, which includes DBCC. We could have a secondary system receiving data as a part of Software Assurance licensing, but no activity on this system. A common misunderstanding, and a long and often raised complaint by customers.

    That has changed as of Nov 1, 2019. Microsoft posted a blog that now lets us use those secondary systems for a number of purposes. We can now run DBCC, log and full backups, and monitor resource usage data on a secondary system without requiring licensing. What’s more, we can now have multiple secondary systems that are not separately licensed for both HA and DR purposes.

    Perhaps best of all, this isn’t limited to SQL Server 2019. This is in effect for all supported SQL Server systems. With SQL Server 2012 in Extended Support, I would assume this would apply there. The caveat is that you need Software Assurance (SA) on the licensed systems. You don’t get this for free, but many organizations get SA to allow them to upgrade, and this will be a welcome addition to the value for SA.

    I think this should have been the way that licensing worked forever, as I’d argue DBCC isn’t a workload, but a verification that things are working appropriately. SQL Server should detect this stuff automatically to me, but since it doesn’t, this isn’t any active workload. Neither are backups. Finally, Microsoft agrees.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • New HA Licensing Benefits

    Microsoft announced recently that they were changing the way licensing works for HA and DR situations. I think this is a great change, and the summary is:

    • You can have up to 3 other un-used secondaries for free
    • You can a sync on-prem replica, async on prem replica, and a remote DR replica.
    • You can run DBCC, backups, monitor resource data on the secondaries

    This is a huge change, mostly because most of us expected these would be acceptable uses already, but they weren’t. And many of us had to go ask for more licensing money.

    The Caveat

    There is a caveat here, actually two. You need:

    • Software assurance
    • A supported version of SQL Server

    Those two things mean you need to be under a support contract, but also that this benefit isn’t just SQL Server 2019, but back ported to all supported versions.

    That’s good new.

  • The Network Bottleneck

    Most of us know that sending extra data back in a result set is a waste of resources. This can certainly cause delays and poor response in our apps, and across slow networks, this can even cause timeouts. Good developers try to limit the data transfer to just the data necessary, without pulling back extra fields. This is one reason a “SELECT *” is a bad idea.

    What about when we are sending just the data we need, but that data is so voluminous that we can’t easily transmit it across a network. That’s one of the ideas that is noted in this piece, which asks for innovation in the way that we move data around, especially IoT data. This type of data is growing, and potentially growing across networks that are bandwidth constrained, such as satellite and cellular links.

    Do many of you find that your network links are constrained? I certainly do at times, though I live in a place where the broadband and cellular networks are not very robust. Even so, most of the time I never notice issues with moving data around. Even when using a real time application, such as streaming data.

    Perhaps there are tricks that buffer data and hide network issues, but even if that is the case, is there a problem? After all, that’s part of what our applications should do: isolate and protect us from the inherent unreliability of hardware.

    Relational databases continue to store and process larger amounts of data all the time. Size of data operations, which grow increasingly cumbersome as data volumes grow, are a place where many vendors are trying to improve their offerings. Microsoft is one of these, and some of the improvements in SQL Server 2019, with Big Data Clusters, Accelerated Database Recovery, and scale out architectures are designed to precisely help us cope with more data.

    I have confidence that we will continue to solve these problems, and that networks will keep growing to support the demands of our ever increasing database sizes. We just need to be sure we continue to evolve and grow our applications to take advantage of these improvements.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.

  • Focusing on Business Value

    This was a fun talk with David Atkinson at Redgate. We talk satellites, finance, and more. Including some early history of the company.

    We are talking about how to get things done for the business, to be effective in your work. Tools help, but it’s more that we want you to reframe the way you work to ensure that we don’t just try to get our specific task done, but ensure that our task gets a feature or fix into a customer’s hands.

    Take a quick 30 minutes break and enjoy this discussion. I had fun recording it and hope you do as well.