Forewarning: This likely sounds like a shill post. I'm okay with that. I work for a Microsoft partner and we're tightly integrated with the MS sellers for a number of products. I am trying to provide an answer to the use cases.
A fully integrated data platform suite with several tools that are best in class. SQL Server is not just a relational database engine, though that is at the core of most tools and services.
SQL Server is a SKU that includes many things:
* Relational engine
* In-memory OLTP (SQL compiles to native code)
* Columnstore indices for OLAP workloads
* The two above can be combined for real time analytics
* Embedded R engine in DB
* SSIS - data processing orchestration tool AND ETL tool
* SSRS - traditional reporting platform (with native mobile reports)
* SSAS - best in class OLAP engine/semantic layer
* MDS - Master Data Services (basically a rules engine)
* DQS - Data Quality Services (pluggable back end for data quality needs - batch or real time)
I do think SSAS is truly best in class. The others are all solid products, some better than most competitors, some middle of the road. You can certainly find equivalent open source products or vendor products and roll your own. But SQL Server as a SKU is not competing with your Postgres server. It's competing with damn near everything that touches data at your company.
> It's competing with damn near everything that touches data at your company.
MSSQL for data processing? Really? I think only huge legacy projects still using it. We're using Hadoop with some C++/Hardware acceleration.
Really? Because I see Microsoft revenue continuing to increase and the partner ecosystem continuing to thrive. I also get to see licensing agreements and I get to see what sales quotas are in the MS data platform.
None of the numbers indicate a shrinking market to me. SQL Server does compete for analytics workloads. Is it a direct competitor to Hadoop? No.
I have clearly stated my bubble up front and I'm trying to keep my claims within that bubble. The question was "what are the use cases?" I answered that. I am now saying that I see a thriving SQL Server business. I'm not saying no one uses Hadoop because more of my projects leverage SQL Server components than Hadoop. You are claiming that SQL Server is only for legacy projects because you are not using it.
I consider myself a first-rate Linux sysadmin, and a perfectly competent Windows sysadmin as well, but this is a position I can't wrap my head around. If you're willing to pay thousands of dollars for a properly-licensed SQL Server install, why do you care if it needs to run on Windows? I would normally assume you don't want the license cost of the OS, but it's a tiny fraction of the overall licensing cost.
The only thing I see MSSQL on Linux being useful for is for development and testing purposes before deployment to a local server farm or the Azure cloud. (Even then, why not just run it on your Windows dev machine?) Honest question: What's the scenario where you'd license a system to run on Linux in production? To me, this is interesting, from the virtualization standpoint, but it seems like a solution in search of a problem.
You might want to use MSSQL as a database and be able to sell your product to companies that do not have Windows servers around (and no staff with experience running them). And Microsoft wants to allow you to do that so you don't start building integrations with other databases to support those clients, and then those take MSSQL sales away.
And even in companies that have Windows MSSQL on Linux might be useful, e.g. if they have a linux-centric virtualization infrastructure.
> why do you care if it needs to run on Windows? I would normally assume you don't want the license cost of the OS, but it's a tiny fraction of the overall licensing cost.
At my company we had a single Windows server for aaaaages that we kept around because of a legacy MSSQL install we needed. Only Windows box in the whole server farm. Was a bit of a pain.
Of course, MSSQL-on-Linux wouldn't necessarily have been the answer here... I'm sure we would have had to buy a new MSSQL license to run it on Linux.
Eventually we eliminated the need for it entirely, but had MSSQL-on-Linux been an option a few years ago we might have simply migrated it to Linux and paid the necessary license.
As others have noted, SQL Server is a very powerful platform. I only really know about it from the perspective of a web/app programmer, but that's only the tip of the iceberg.
To answer your question directly...
Don't think of it from the perspective of "green field" development where you're building something from scratch.
While "green field" development is every dev's dream, the overwhelming majority of dev work involves maintaining and integrating with existing services. There are lots of MSSQL installations out there, after all.
On another note, I know soooo many developers for whom "SQL" is synonymous with "MySQL." MySQL is okay, but they should know that Postgres and MSSQL offer a lot of programmer-friendly things that make working with them a heck of a lot more fun than MySQL. Different index types, data types, window functions, CTEs, etc.
Now that .NET is multiplatform and various versions of Visual Studio are also available to Linux/macOS, you can actually think about doing "Microsoft development" on these platforms. As SQL Server is quite common in the Windows shops, this makes it possible to run that one on local development machines as well (for example via Docker).
SQL Server on Nano is hard to install properly - MS didn't think about how to install SQLS on Windows without a GUI. Docker is the only usable way that doesn't take hours.
Because SQL Server is actually an excellent database, fast, reliable and full-featured. Consider that Postgres only got parallel queries a few months ago that SQL Server has had for 15 years! And in terms of replication, clustering etc SQL Server is still well ahead of PG. Now don't get me wrong I am a huge PG fan, but burying your head in the sand "because it's Microsoft" is unprofessional.
MSSQL still doesn't have replication on Linux. Here's one data scientists hillarious take on the difference between MSSQL and Postgres: https://www.pg-versus-ms.com/
I'm a big fan of PG and PG is good enough that it can compare favorably to MSSQL on its own merits, but that article reads more as a screed against commercial software than an objective assessment of PG's strengths relative to MSSQL. As an example, a big advantage of MSSQL for data analysis applications is its column store indexes which the author dismisses as something he doesn't use.
I'm rather biased, I'll freely admit that. Having deployed some number of Django on PG solutions (some of which deliver moderate req/s) I will gravitate there just on experience alone.
A fully integrated data platform suite with several tools that are best in class. SQL Server is not just a relational database engine, though that is at the core of most tools and services.
SQL Server is a SKU that includes many things:
I do think SSAS is truly best in class. The others are all solid products, some better than most competitors, some middle of the road. You can certainly find equivalent open source products or vendor products and roll your own. But SQL Server as a SKU is not competing with your Postgres server. It's competing with damn near everything that touches data at your company.