Why Not to Make SQL Server the De Facto Choice for Bespoke Development
Writing bespoke software always comes with a certain opportunity to protect the company from vendor lock in. This is one of the most important reasons to consider bespoke software. Certainly not the only one. But this is a factor in every technology decision and why seeing anyone writing software using the MS stack should set off red flags everywhere. There are some cases for it, but they are few and far between as the stack creates lock in, encourages the use of costly tools, costly ecosystems and pushes you to hire less qualified developers. We could all day analyzing why this is true, it's loads of little factors.
Some things, like using .NET, is itself not too bad, because it's a powerful technology that is available in many free ways. You can build .NET apps and run them in PaaS, on Linux, on Windows... whatever you want. It's not the best tech for nearly any bespoke software, but it has use cases and scenarios where it makes sense.
What basically never makes sense is SQL Server. This is the ultimate in common red flags that you have major development issues. SQL Server is incredibly expensive, extremely hard to migrate off of and very limiting. It's a powerful product, but not in ways that are useful to companies that are stuck with it. It doesn't "do" anything special that you can't get for free some other way. And it's not easier for developers to use, either. It's really just a lazy choice 99.99% of the time. Or someone actively sabotaging, of course. Windows or SQL teams will sometimes pressure developers to sabotage the company in order to deploy something that requires a lot of cost and management in order to secure their jobs. That's not good, either.
Whether you are writing in .NET or Java or Node, Ruby, Python, Scala, Clojure, whatever... you are free to use any DB system you want. You need or want relational DBs less than half of the time. So right there, we should be wary of relational use as it is slow and complicated and we want to avoid it unless we need that specific power. Assuming we are building financial systems or something that need the complex integrity features of relational databases, then we want to be looking at powerful options like PostgreSQL and MariaDB, as examples. What makes them really important is that they are fast and powerful and nearly on feature parity with SQL Server but don't require licenses. But without requiring licenses, they both offer broader and more robust support. So whether you need speed, power or support.. they are your go to choices.
Because MariaDB and PostgreSQL (and others, certainly not limiting to these) have open licenses, you can deploy for free, at any scale. You can run them on any platform. You can host on any cloud. You can run in house, you can failover from one site to another. You get clustering and other advanced features, for free. you get second, or third sites, for free. All of the things that limit you from SQL Server are gone. They need less care and feeding and zero licensing management. No more special case VMs limited in CPU count to meet licensing limitations. No more lack of failover because no one would approve the purchase. No more skipping the fastest, more applicable hosting options because they don't meet your assumed license purchasing model. No more DBAs siphoning off salaries to run systems that aren't needed.
You can get these products in SaaS models just like anything else. You can run them yourself wherever you want. You still have all the power than you had with MS SQL Server, but without the crippling limitations and complexities. No longer do you have to choose your deployment models based off of how impactful the licensing will be, you are free to put them anywhere that makes technical sense. No longer do you have to budget or get approval for updates, you get all updates for free, forever.
Databases are one of the most important places to rid yourself of crippling licensing lock in and one of the places where your programmers can quietly make infrastructure purchasing decisions without anyone questioning them. If Microsoft wants to get its hooks into your organization, there is a reason that they go to the developers - in the hopes that they will make long term, costly commitments to Microsoft without ever seeking approval from IT or management to do so or will do so without explaining that equally good, totally free alternatives exist that there is no business reason to not have used. No one ever questions the developers for this, and so they tend to think that they are safe. Why they are so often so willing to do this, I don't know.
Seeing companies allow developers to screw the organization in this way without IT questioning them was a major factor in me turning down a CIO role just last week. That no one from developers to CEO questioned such insane decisions... which were already being done to fix similar mistakes in the past, was a key reason that I was worried that their management structure wasn't competent. The developers were looking for ways to ensure their jobs and no one was calling them out on it.
The issue is around what we call technical debt.
This was a response to someone that asked me to clarify when I had recommended rethinking a costly lock in to MS SQL Server that was so dramatic that it was causing someone to be unable to pick the best vendors for their needs due to how licensing was done.
Instead of "what's appropriate", it's more than there are a handful of solutions that are red flags and are generally not appropriate. There are some cases where you could make a case for locking into one of these, but they should be insanely rare because they hurt the organization in so many ways. Whether it is in tens or hundreds of thousands of dollars in licensing fees, or by lowering agility and making the organization unable to do other sensible things down the road (look at the OP who is now dedicated to paying double per workload just because of that one lock in - the decision for SQL Server didn't just make SQL Server cost more, but doubled the cost of IIS and other workloads, too!)
Generally avoid things like MS SQL Server, Oracle DB, Informix, Sybase, etc. These all have the same issues - no clear benefits while coming with staggering caveats. Never choose one without a significant amount of research and analysis and without clearly understanding how technical debt might impact you from this for decades. A small project might easily more than double in lifetime cost from a bad choice here. Think about that, over a product lifespan this could easily make a one million dollar project into a two million dollar project without a single other thing changing!
in fact, or in effect, whether by right or not.
sorry, my inner grammar nazi insisted
There are so many potentially appropriate products out there. From SQLite on the tiny end to MariaDB, PostgreSQL and Ingres for enterprise relational systems. But today, we should generally look at NoSQL first, just to see if it meets our needs. So MongoDB, Cassandra, CouchDB, Redis and we could go on all day. There are so many awesome, new powerful databases for special use cases. Really, one of the big things that has changed in the last decade, is that there is NO "go to" database for anything anymore. In some ways, this makes things a little more difficult. But most development frameworks gravitate towards a favoured database so that can make things easier (unless you keep using .NET then people use that as an excuse to use MS SQL Server, so caveat emptor.) But part of being a useful developer is knowing which tools for the job. And a lot of coordinating with IT is appropriate. IT can help to talk about architecturally what is going to work best.
If you were looking for a de facto direct replacement to MS SQL Server and just didn't want to have to "think" much about anything, the obvious choice is PostgreSQL. It is one of the world's most powerful, mature and respected database platforms. It is insanely fast, scales insanely big, is supported on the most enterprise style deployments, does relational the same way that MS SQL Server does and so forth. It is the most direct competitor. And you have loads of top end support options for it. So if you want a safe "starting point", start there. It's hard to go really wrong. But best to do an evaluation of needs as there are just so many great options