Voice of the DBA

Category: Editorial

  • The API Bottleneck

    While talking with a client recently about their performance challenges, I was relieved to find that the database wasn’t the problem. Instead, their API server was overloaded by the number of calls taking place in their application. While the database did provide the backing for the API calls, there was a fair amount of caching. However, as they’d moved to microservices, more and more of the interaction between modules was taking place as a network call to a single server, which became overloaded.

    Certainly, they could add more API servers, or break out different types of API servers, but each of these changes potentially has downsides. Data can become out of sync across two systems, which is why we often want a single database storing all data. Distributed systems are hard to architect and implement, and I suspect as more developers try to build them, we’ll find more issues like this. Not that developers are doing anything wrong, but they often don’t realize the challenges of building a distributed system when the workload scales.

    They’re relearning the knowledge that others have gained through their own experiments and failures.

    One of the things we try to do here at SQL Server Central is share our knowledge with others. We hope to help each of you learn from others, and hopefully not need to make the same mistakes in your applications.

    As I listened to the client talk, I was reminded of this post from Troy Hunt. In it he talks about the HaveIBeenPwned site, which he built a scaled to meet demands. The site doesn’t cost a lot, but there is a great quote about the API server he set up. He wrote: “I’ve tested it with 380k queries a minute! Infrastructure will scale out and magic will happen, so long as you’re not maxing it out for perpetuity, just go for it.”

    However, in your company, you might be scaling in perpetuity in an app, especially as a workload grows and more parts of your application need to make queries against an API. Good design, good coding, and adhering to good distributed system design, will help ensure you can handle the load. This is especially true in the cloud, where scaling up is just provisioning another resource, but there is a cost.

    You have to know what to do, so I urge anyone looking to build a distributed system to read about the experiences of others. Learn from their knowledge, rather than make your own mistakes and try to correct them. There is a lot of great information out there, but you need to be looking for resources that provide technical details, like Troy’s posts on how he built his site or Jeff Moden’s posts on writing high performing queries. That’s the type of knowledge that is invaluable if you want to be an efficient and effective developer.

    Steve Jones

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

  • Summit Reflections

    This is the second year that the annual PASS Data Community Summit has been virtual. Last year, as a pandemic gripped the world, I thought we might never see another one. I’m glad we have one this year, and I look forward to going back to a live, in-person Summit in 2022. That being said, this year’s event has been better than I expected in many ways, mostly because of spatial.chat.

    I can remember walking through the hallway of the Washington State Convention center and seeing Taiob Ali coming the other way. We stepped to the side of the hallway and had a chat among hundreds of people walking by. I thought about that in the Community Zone when he and I, with a couple others, were talking with each other in a group, away from others that were also in the room. We could see their avatars, but we were far enough away in the virtual space that our conversation didn’t upset others.

    At early Summits in Washington, there were couches in the Convention Center. Andy Warren and I often planted ourselves for hours on a couch, chatting with each other and those that came by. People would walk up and spend some time, others would drift away, but we had a conversation ebbing from work, life, Microsoft, family, hobbies, and more. We’d laugh and joke among ourselves, bonding and sharing, inspiring, and encouraging each other. I had that experience a few times in the Community Zone last week, as I stopped in various rooms, sometimes gathering in a crowd, sometimes (virtually) walking away to chat with just a friend or two.

    When I presented at the Summit, I often felt pressured, rushed, and excited by interacting with people. Getting questions and needing to go off script and explain something was a challenge and a joy. When the Summit opened this Wednesday, I had one of the first Q&A sessions. Most people hadn’t watched my recording, so I started to deliver the talk, but quickly realized I didn’t have time. I saw a few questions come in the chat, feeling pressure to get to them and quickly answer them. I had no idea how many I’d get, so I did my best to share my screen, show examples, talk through some of the concepts. It was scary and invigorating. It reminded me of the times I’d get a few questions, realize I wasn’t quite explaining things well enough, and then feeling pressure to deviate from what I’d rehearsed to help clarify the idea.

    Most years I’d have early mornings and late nights. I’ve be buzzing from my interactions and physically exhausted. As I logged off from holding open the Community Zone Wednesday night, I felt the same way. Getting going Thursday to host the SQL Saturday room for a long time and trying to watch other sessions was tough. Friday harder. Exactly like I’d feel many years at the physical event. I needed a break. I’ve enjoyed walks around the grounds of the Gaylord, or in the park behind the Washington State Convention Center; at home I go outside and see the horses. Rather than a quiet dinner with a friend (or even solo), I could go cook something and relax with my wife and son here. Both different shades of the same experiences.

    I am tired of virtual events. For me, they often lack the excitement and interest I get when I’m in a physical space. I wandered into different sessions, listened in on conversations of others, and enjoyed the interactions of being able to see and chat with others. I escaped at times to get away from the hectic environment, embraced it at others. Unlike my experience two weeks ago at Ignite, I felt like I was at an event.

    That being said, I’m looking forward to more live events in the next year. I want to engage with actual people and remember the wonderful emotions of being in the same room. I’ll miss those that don’t feel comfortable, I respect your choice, and I hope we can continue to find ways to touch each others’ lives. I’m also grateful that some of you are willing to come back to physical events and enjoy the #sqlfamily.

    Steve Jones

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

  • A Summit Rebirth in 2022

    This year the SQL Server and Data Platform community is getting together for the annual Summit virtually. You can still register and attend today through Friday for free. The content is also being recorded, and if you register, you can watch it later, or re-watch it, at your convenience. It’s free training for your career across a wide variety of topics, so take a moment and fill out the form.

    There’s another good reason to register now. Next year’s Summit will be hybrid, with the in-person portion happening back in Seattle next fall. However, there will be a limited number of tickets available for those that want to travel to the event and be in the same room as other professionals. Tickets for the live portion are only going to be available to people registered for this year’s event, so make sure you head over to the site and register today. You can also read about the announcement on the Redgate blog.

    I am thrilled that there will be a live conference next year. I have missed the chance to get together with others now that I’m vaccinated. I was lucky to attend a couple of events this year and am looking forward to SQL Server and Azure SQL Conference in December. I am hoping to get to SQL Bits in the spring in London, and I am very excited to go to next year’s Summit and spend time with the #sqlfamily that have made the last two decades a joy. I relish the chance to talk live with others, get inspired by their knowledge and stories, and bond over meals. It’s been invaluable in my career and brought many smiles to my face.

    I am also glad that next year’s event will be hybrid, allowing those that can’t travel, or don’t feel comfortable, to still access content and interact with others. I know that this is important, and I hope we find ways to provide additional hybrid options in the future. While I do think the pandemic will recede, some of the concerns will remain over large crowds and event safety. I think we should accommodate those that are not attending conferences live, and I expect we will find ways to do this better in the years ahead.

    The annual Summit has been a large part of my career for over 20 years. It has been a time and place where most of the best, brightest, and more generous Data Platform professionals from inside and outside of Microsoft gather to share information and help each other grow our industry. I look forward to the rebirth of this amazing experience in a hybrid form next year, with changes and ideas to make the event even more invigorating.

    Note: The deadline to register and be eligible for a ticket next year is Friday morning, before the Community keynote, presented by Brent Ozar.

    Steve Jones

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

  • SQL Server vNext

    We now know when the next version of SQL Server is coming. At Ignite last week, Microsoft announced SQL Server 2022, which comes nearly 3 years after the last version, SQL Server 2019. Apparently, the pandemic’s effects include delays in product development as it’s been nearly three years since SQL Server 2019. The new version is in preview, with a few new features announced. There’s also a video version from the Ignite site, but if you want a quick summary of things, read on.

    There were a few items that get me a bit excited about the changes. The first is that we get some query improvements with regard to parameter sniffing in stored procedures. This has been a constant problem for many databases and workloads for years. This is because only one plan has been kept in the cache. That changes in SQL Server 2022, as the instance can now keep multiple plans around. It will be interesting to see if this helps many customers.

    The Query Store was an interesting edition to SQL Server and plenty of people have found it valuable. However, its use was optional and you had to enable it. In SQL Server 2022, Query Store is now on by default and there is support for read replicas as well. This change is there to enhance the intelligent query processing (IQP) work that has been growing in each new version. This new version gets MAXDOP and Cardinality Estimator being incorporated into the feedback loop. IQP isn’t perfect, and it might cause you some issues with certain workloads, but many of the changes made in the last few versions do seem to be helping customers.

    There was a quick note in the Ignite video about multi-write replication is now available. Not true peer to peer, but this version should automate the last write wins rule, based on UTC timing. I’ve been skeptical of using peer-to-peer replication in the past, mostly because of the need to code conflict scenarios, but a last-writer wins scenario might work well for some applications. If you can accept this rule, and you have good resources for propagating data, this might be something you can use.

    Blockchain is coming to SQL Server. There is a Ledger feature that provides an immutable ledger of data changes. This ensures that data integrity is maintained with full auditability. Trusted storage is needed to ensure this meets all audit and regulatory requirements, though if the data has been tampered with, you might only know something happened. It’s not perfect, but it does provide the ability to prove there is trust in the data you are watching. I suspect this will be a feature like TDE that auditors want enabled, even if it isn’t perfect. I do wonder what this will do to storage costs and complexity.

    Azure Purview is being integrated into SQL Server 2022. The big takeaway here is that you can scan on-premises data for free. Now I don’t know if this means you can use your data for free in any way, but I do think that this does start to make governance easier. I think that’s a big deal for all of us in the future if companies were to better know what data is risky and work on protecting it. Normalizing the way we look at data governance is a big step forward. This certainly is a long way from most applications using the “sa” account.

    One of the challenges I’ve seen in many companies is dealing with reports. Often companies try to run complex queries on their OLTP system, or they spend a lot of time and effort to build ETL structures. Synapse Link provides automatic change feeds from a SQL Server to a SQL pool in Synapse. This is like an automatic replication of the table(s) into your data warehouse. It’s been available for CosmosDB, but now it comes to SQL Server. Given the cost of development and managing ETL jobs, I could see some organizations upgrading to SQL Server 2022 just for this feature. If it works well and quickly.

    Disaster Recovery (DR) is something I’ve always been concerned about. I’ve had too many issues over time to treat this lightly. In this new version, you can set Azure SQL Managed Instance as a target for a DR recovery, with failover to the cloud through a distributed availability group. That’s has been possible before, though now this seems easier than ever (as it should be). The failover is a good feature, but a lot of us wouldn’t necessarily want to live in the cloud, so the big announcement here is that you can restore the backup from MI back to an on-premises SQL Server 2022 instance. We can restore back from the cloud to our own systems, which has been a hassle for a long time.

    Now, this isn’t failback. I have no idea if logs can be backed up and restored, or if this means a lot of downtime to leave the cloud if you have a big database. From the demo’s, it doesn’t look practical to backup and restore a database of any size to fail back. My guess is this is really a first cut, but the ability to move from MI back on-premises is a huge step forward. If for no other reason than you can backup things for dev work inside your organization. That’s a big win for a lot of customers I know, at least it is if Azure SQL DB and Azure SQL MI are close in functionality from the inside-the-database perspective.

    There are other enhancements as well. The cloud offerings from Microsoft gain some scale, which is good. Most of us don’t have huge systems, but more scale usually trickles down with better pricing for the mid levels. Azure Arc grows, allowing you to run Azure SQL MI and Hyperscale PostgreSQL on your own hardware. You’ll pay something to Microsoft, but having these systems stand up, keeping data local, and having them managed easily is a big deal. I expect that more companies will consider things like Azure SQL DB on their own hardware over time, especially as standing up and managing a Kubernetes cluster becomes easier. This feels like the early days before vSphere when we struggled to manage lots of virtual machines. I suspect we will find better ways to deploy and manage container orchestrators on-premises over time.

    It feels like a long time since we talked about SQL Server. Certainly the last 18 months have felt like the world is frozen and not much has happened. I know I’ve done some things, but it’s been a strange feeling of suspension for much of life. For me, the announcement of a new version of SQL Server brings some excitement. It feels as though the world is moving forward, and I’m looking forward to learning and writing about the future. I’m also hoping to get the chance to deliver some presentations on something new in SQL Server in 2022.

    Steve Jones

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