The More SQL Server Features You Use, The More Painful Database Changes Become

Vanilla


Features are new. Features are sexy. Features do things for you automatically.

But they often come with a lot of baggage.

  • They may not work at all with other features
  • They may not support full capabilities across the board
  • They may require you to make architectural changes to use them
  • They may never progress beyond V1 because of low adoption or shifting priorities

Once you get past that stuff, consider that things change and get added so fast that the documentation can’t keep up. You might dive headlong into a feature and have to build your own documentation about what works and what doesn’t along the way. If you’re an early adopter, there’s a pretty good chance it’s up to you to find bugs and other issues, and work with the nice folks at Microsoft to fix them.

That’s not always pleasant for databases, where changes — especially for data of a certain size — can often mean downtime, or weeks to months of preparation.

Manila


Some features require things, too. Think of the number of features that rely on tables having a Primary Key.

If you wanted to make changes to a Primary Key in order to incorporate another feature like Partitioning, you may find yourself at the at the unfortunate end of an Ouroboros of error messages, turning things like Replication or Change Data Capture or Temporal Tables off to get something working, and then finding yourself waiting a very long time for them to turn back on. And that assumes that turning them back on doesn’t require other changes.

I can hear a lot of you saying that this is the value of hiring someone with the experience to implement things correctly the first time, but that gets harder to do as you incorporate more features to accomplish database side-quests (take Auditing, for example), and new features get added into the mix that no one really has practical experience with on their own, never-you-mind when you mix them up with other features you might be using.

Gorilla


The 800lb gorilla in the room is testing space. Without that, it’s impossible to fit, retrofit, or even scope the amount of work it would take to get a new thing working in your current environment.

If you can test the process, great. But a lot of people have a hard time testing features under load, which is where many snags get hit.

At the heart of it is that there’s no such thing as a free feature. When you turn something on that requires your database to do more work on top of processing transactions, it’s up to you to figure out how to compensate for that load. You also have to make peace with the fact that certain things might be impossible, or become much harder as you introduce features.

Make sure you understand what you’re signing up for.

Thanks for reading!

Going Further


If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.