Voice of the DBA

Author: way0utwest

  • Daily Coping 8 Nov 2021

    I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

    Today’s tip is to make a list of new things you want to do before the year ends.

    What do I want to do?

    • First, get the grass cut. I got some more done this weekend, but need to get out there for about 10 more hours.
    • Finishing learning Tears in Heaven on guitar. I got partway through and moved on to a few other things, but I want to finish this one.
    • More guitar. I’ve got a lot of partial songs, but I realize I need to pick one and focus for a week or two and then move on. I’d like to aim for 4 completions by Dec 31
    • Go snowboarding. I want to do this. It’s not a goal, but I’m hoping the snow looks good enough.
    • See a live movie – I miss doing this with my wife
    • Get some auto maintenance done. No, not the Tesla, the other things with oil here.
    • Convince a few people to plan a 2022 SQL Saturday – definitely on my mind more after Orlando
    • Go eat sushi – live
    • Have lunch with a few friends. I’ve neglected this lately with a busy schedule.

    There are certainly things I need to do, and plenty of chores, but these are more items I’d like to work on for myself.

  • 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.

  • Range Anxiety is Easing

    This is part of a series that covers my experience with a Tesla Model Y.

    One of the things that bothered me for a long time about electric cars is range anxiety. Maybe because I’ve almost run out of gas many times, or because I sometimes have a few days with lots of driving. In any case, I often worried that I’d run out of charge. I used to feel that way about my phone, though I have gotten used to running low on power at times.

    The same thing has happened in the Model Y. It’s almost two months, and I’ll do a mini review next week on how I feel, but for now, I’ve come to grips with the fact that I don’t worry about range in my daily life anymore.

    When I picked up the car, it was set to charge to 90%. I wrote about the charging experience, and have since gotten even more relaxed. The car has a scheduled charge set (another blog coming), and when I plug it in, by 7am the next day it gets to 83% charge.

    Often I drive it and the charge only goes down to the low 70s or high 60s on many days. On those days, I sometimes don’t bother to plug it in again. I often don’t have plans that will require a lot more than my normal 20-50 miles, so I just let the car sit.

    On the second (or third) day, I will be driving around and see the charge drop to 50%, sometimes even into the low 40s. Surprisingly this is more of a cue I need to plug in the charger that night rather than worry if I’ll be able to get home.

    So far, no long trips. This week I’ve started to drive more, as coaching has started, so we’ll see how that changes my attitude, and if this continues in the winter. Already I’m starting to use the seat heaters, so I’m definitely consuming more power.

  • What I Learned at Community Summits

    I went to the first  Community Summit in 1999. This was one of the first conferences of my career, and I was excited to have one focused on SQL Server and data. I met Kalen Delaney and shook her hand after having her educate me about how tempdb worked in SQL Server 7. That event started a 20-year journey of traveling to subsequent Summits nearly every year since. Over the years, I have a lot of memories from Summits, mostly of people that I’ve met and talked with. Much of the time I’ve been at any of the Summits was spent on networking and business purposes more than learning, but I have picked up a few things along the way.

    Early on Brian Knight and I delivered a presentation in a debate format. We were talking about the pros and cons of using identities. It was an enjoyable session and one where we learned a few things. A developer from Microsoft was in the audience and helped clarify some of the specific numbers related to limitations and scaling issues with using the feature.

    When SQL Server 2005 was ready for release, I still remember going to watch Donald Farmer talk about Integration Services, a radical change from the way that Data Transformation Services used to work. It was quite an eye opening session for me.

    Early in the time when I was working with DevOps and databases, I watched a talk on how you might apply some of the techniques with Reporting Services to implement CI and ensure your report changes would actually work correctly. It was a hack, but I think that a number of the technologies that Microsoft has added to SQL Server aren’t designed with version control and automated code review, which is a shame. However, that’s one of the great things about Summits. You can learn how someone else solved a problem.

    There are many other memories from the event where someone taught me a bit more about SQL Server. Being in the same place as so many smart, talented, inspiring data professionals was magical. That was one of the big reasons that my boss wanted to see the conference continue. Nowhere else in the world brings so many people together.

    You can still register for the event next week. It’s virtual this year, and completely free. No cost, and even if you get busy next week with work, you have access to the content after the conference, so register today. It’s a few minutes, and you’ll get the chance to learn from some amazing speakers.

    Steve Jones

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