SQL Server vNext

Posted on November 8, 2021 by way0utwest

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.

Posted in Editorial | Tagged , | Comments Off on SQL Server vNext

Range Anxiety is Easing

Posted on November 5, 2021 by way0utwest

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.

Posted in Blog | Tagged , | Comments Off on Range Anxiety is Easing

What I Learned at Community Summits

Posted on November 5, 2021 by way0utwest

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.

Posted in Editorial | Tagged , , , | Comments Off on What I Learned at Community Summits

Daily Coping 5 Nov 2021

Posted on November 5, 2021 by way0utwest

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 take time to reflect on what you accomplished this week.

I’m writing about last week because if I delay this until Friday morning, or Thur afternoon, I’ll forget to do it. So, Oct 25-Oct 31.

It was a busy week. Really the last stressful week with a bunch of deadlines for the next month, at least, I hope that’s the case. Here’s a list of things that happened last week:

  • Record a presentation
  • Review another presentation that was recorded in a group with comments for the editor
  • Schedule a bunch of social media stuff
  • Deliver a sales presentation on our products
  • Organize a few things I want to read through for learning (the actual learning is for next week)
  • Rehearse a presentation
  • Deliver a live presentation (SQL Saturday Orlando 2021)
  • Get on a plane, have a few dinners and breakfast with friends, and fly home. Actually got some work done on a plane.
  • Write (or really, finish) 5 editorials.
  • Watch a marketing presentation and give feedback (I didn’t expect this to be interesting)
  • Renewed my driver’s license
  • Watched a few kids I coached last year at their last high school game
  • Hold a first practice with kids starting their new season with me
  • Get to yoga 3 times, lift twice, ride the elliptical once and walk once
  • Cook 5 dinners for the family
  • Cut some grass, and cut down a tree that I’ve been meaning to clear all summer
  • Learn part of a new song on guitar

I know I did more little stuff, but this came to mind.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 5 Nov 2021