Voice of the DBA

Tag: high availability

  • Separate Reads

    Recently I was watching a presentation on how to scale performance in your SQL Server environment and one of the suggestions was setting up Availability Groups (AGs) and having read-intent connections that would query the secondary and not the primary. It’s not a bad idea, and the SQL Native Client (and other drivers) support this and make it easy to implement.

    The pattern of using multiple connections in an application, one for reads and one for writes, has been suggested often. However, in practice, I’ve rarely seen this work. Apparently having a connection variable, named dbConn, for writes and a second one, named dbConnReadOnly, for reads is too complex for most developers or teams.

    Or maybe the idea of having to pick the right access point is a human problem? I’ve seen no shortage of problems in restaurants when we have specific “in” and “out” doors. Lots of people go through the wrong one and we end up with plates and food on the floor. Even broken noses or fingers at times. Perhaps I shouldn’t pick on software developers too much.

    How many of you use two connections from apps? Meaning, do you think about reads and writes in separate connections. Even if you read and write from the same database, this can be a nice practice that future proofs code. It’s a small change, but it gives you room to grow if you get a read replica for analytics or reporting.

    Of course, you could take it too far with different connections for different “services”, aiming for a microservice-style architecture. We could have dbUser for user stuff and dbOrders for the business side, and other connections for other services. I wouldn’t do that, as I think many of us will get confused, and we’ll often be doing two different type of service things in the same code. If I need something from a customer to write an order, do I have two connections in my method? I could, but I bet lots of developers would try to re-use a single one.

    Plus, if developers get into trouble with two connections, then what will they do with 5 or more? There are lots of ORMs that might even support this, or if they do, not make this easy to code.

    I’ve always liked the idea of separating reads and writes, but maybe the better solution is using one connection whenever we have simple CRUD work and another one for any sort of complex querying or reporting. That would make more sense as I suspect many of us will eventually offload reporting or analytics in some way to another system. A Delta Lakehouse of some sort seems likely if the current trend takes hold in more organizations.

    Let me know today if you used (or have tried) different connections for reads and writes.

    Steve Jones

    Listen to the podcast at Libsyn, Spotify, or iTunes.

    Note, podcasts are only available for a limited time online.

  • Database Mirroring is Back in Azure SQL Database

    Database mirroring was a cool feature in SQL Server 2005. I guess it’s still a feature, though it’s listed as deprecated in the documentation. There is still a mirroring dialog in the SSMS database properties dialog in more recent versions of SQL Server, but I don’t know if there is a good reason to use mirroring over Availability Groups.

    That’s why I was surprised to see a public preview announcement of Azure SQL Database Mirroring to Microsoft Fabric announcement. Apparently you can easily move Azure SQL Database data to Fabric and have it written to Delta Parquet tables in OneLake. No ETL, no need to do the data conversion yourself, or at least not much of an effort. I suspect you still need to understand this and do some configuration for how your Parquet files will get written.

    If you go through the documentation, it’s interesting (and annoying) to me that the docs keep saying replication. I hate when we’ve overloaded terms as mirroring and replication mean specific things in SQL Server, so I wish they would use some other term (copy, extract, ??) to describe what is happening.

    Whether this is useful to analytic workloads remains to be seen. I am curious what the people working with Fabric think of this feature. I wonder if this is useful, or if this might cause headaches or performance issues. Since this feature is in preview, I doubt anyone knows yet, but it will be interesting to see how this compares with Synapse Link and if it is more or less helpful.

    Moving data around for different purposes has always been a challenge. I know that some people might feel the costs involved in this aren’t worth it. I know a lot of technical people that would say “I could do that.” I have no idea what they costs are. but I know that a lot of people have spent a lot of hours managing ETL packages and adjusting them as schemas change and new requirements appear. I get the appeal of Synapse Link and this new Mirroring to Fabric features.

    I do wish this type of feature was more solidly built into the SQL Server instance. I suspect it will come at some point, and it’s being tested and baked in Azure first. However, I hope that if/when it comes, that the feature has good tooling and some polish to its operation. We’ve had too many features in SQL Server that are partially built, with limited tooling, and a lack of performance characteristics that many of us would desire.

    Steve Jones

  • Improving Availability Groups

    Availability Groups (AG) were introduced in SQL Server 2012, with the idea that we could dramatically improve (and ease) the burden of dealing with high availability in SQL Server. At the time the (code named) HADRON technology seemed full of possibilities.  Since then, there have been some enhancements, but it seems that setting up and managing an AG, especially across subnets, isn’t as simple as Microsoft would have us believe.

    One of the problems with AGs is that there are non database resources (logins, jobs, etc.) that create dependencies. Working around the issues is a headache for many administrators, and it shouldn’t be. While there are some enhancements potentially coming, I don’t know what shape these will take or if they will make things easier.

    Some of my previous work as a DBA relied heavily on SQL Agent and jobs, neither of which are handled by AGs, or by plenty of other HA/DR technologies. Instead, administrators cobble things together, save scripts, and manually repair issues after failover. To me, this is one area that I’d hope Microsoft enhanced for AGs.

    Another area is the listener, which seems to be brittle. It works great, or it’s a nightmare to get working, without always an easy way to debug. I’d certainly welcome improvements here, including the ability for tooling to support multiple listeners easily.

    Many of you work with AGs now, and many more of you may in the future as the need for HA grows all the time for databases. Are there improvements you’d like to see in AGs, or any other SQL Server HA/DR technology? Feel free to leave a comment or submit something to Microsoft.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • Have You Deployed an Availability Group?

    The last few weeks have been interesting, to put it mildly, with both more and less engagement online. I see more people holding video calls, both inside and outside of work, but I also see less engagement at SQLServerCentral, blogs, Slack, and some of the more common community tools we’ve had. I suspect this new way of working is strange, and it’s upset many of the rhythms that people have built over the years.

    In any case, one very important and common technology in the SQL Server platform is an Availability Group. This technology has been in the product since 2012, and unlike clustering, it’s relatively easy to set up. Not to say it’s easy, and you do need to develop some skills outside of T-SQL to understand how this works. This week I’m wondering how many of you might never have done this?

    Or maybe you’ve done it once or twice and forgotten about it.

    While we’re dealing with the various challenges of a pandemic, this is also a time where you might brush up on a few skills or learn new ones. Deploying, configuring, and testing an AG is something that is valuable to both DBAs and developers, and it’s a good skill to bring to your next interview. Every employer wants to know you understand HA/DR.

    We have a Stairway Series that can help you along with quite a few articles and blogs that people have written. There are various lab posts as well, including a good one from Ryan Adams, that can help you get started. If you don’t have equipment handy, you can build one in Azure for cheap, and even turn off the VMs when you’re not using them and spend just a few dollars a month on storage. That’s not much cost to build a valuable skill in your career.

    If you’ve built a lab, maybe you want to tackle something complex, like the scenarios that Allan Hirt writes about on his blog. If you’ve never done one, maybe try to build one, take notes and capture screenshots, and blog about it. In Word if you want to start a portfolio to send out with your resume, or on your live blog. It might be a good time to start a new blog.

    Focusing on a project like this can help shut out some of the noise in the world and engage you in a new way with your career. Hopefully you are doing well, and life is proceeding for you. If not, maybe tackle a project to take your mind away from things, and check out our daily coping tips in the newsletter.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher or iTunes.