I'm sure MSSQL is nice, but my money is on PostgreSQL being even nicer.
Having used both, I'd take that bet. MSSQL is in my opinion the best DB out there in terms of ease of use, documentation, integration, and tools. PostgreSQL is a distant second, and SQLite is probably a distant 3rd (though it's really not in the same category).
The pg documentation is quite through and informative.
> integration
Meaning? "Integration" in and of itself is meaningless because that depends on what we're integrating with. Rust integration? pg is much better. Some obscure windows-only business tool that most people have never heard of? well, it integrates with what they want it to (probably mssql). Some obscure BSD-only business tool that most people have never heard of? well, it integrates with what they want it to (probably pg or mysql).
> tools
Again, meaningless unless you discuss what the actual differences are. Being able to be used on BSD makes pg infinitely superior in terms of tooling for my cost function.
In my experience, people who like MSSQL are the kinds of people who prefer to use a GUI to access their database (or Entity Framework). Basically, the user experience is like this:
PostgreSQL: Just nice. Every once in a while, you learn about a feature that makes things easier.
It sounds like you haven't used MSSQL in quite a while. The 2012-2016 editions have progressed significantly. 2000-2008 were pretty vanilla but then most databases of that era were.
Pricing can be expensive if you need the standard, BI, or Enterprise editions but otherwise it's not. It's no where near the cost of Oracle though.
>It sounds like you haven't used MSSQL in quite a while.
You're wrong.
>The 2012-2016 editions have progressed significantly
Instead of saying "it progressed significantly," why don't you list these features that are so great? Come on, sell this product that you like so much! Help us understand why you like it!
some of them, off the top of my head..
columnstore index, in memory tables, R integration, Always ON, Stretch DB, Dynamic data masking, encryption at rest and transit (always encrypted), row level security, temporal tables, JSON support, query store and stats, live query stats,polybase, backup to cloud, TDE enhancements, replication enhancements, TSQL enhancements, FK relationship limit, managed backup, multiple tempdb files, trace flag improvements, db scoped configs, query optimizer improvements, HA-DR improvements, replication enhancements, Master data services, data quality services, SSIS catalogue, SSIS project model and execution, mobile reports in SSRS, datazen integration, HTML view in SSRS, tabular mode in SSAS, improvements to UDM model in SSAS, DAX language and improvements..
And I got tired of typing...
MS SQL has the best GUI for administration of any database I've used, if that sort of thing is important to you. Hands down better than anything I've seen for a free database or even Oracle. In that sense you do get what you pay for. I'm not sure how they'd bring that to Linux other than maybe with Wine or other emulation layer.
> SQLite is probably a distant 3rd (though it's really not in the same category).
Definitely not in the same category: the goals for SQLite are very different to those of MSSQL and Postgres.
It is impressive how well SQLite performs and scales so you sometimes see it do the job of a "larger" engine because a project accidentally grew and hasn't been re-factored in that respect yet, but if you are starting a new project I can't think of any cases where you would ask yourself if you should use it instead of MSSQL/Postgres. For an integrated storage engine for your app with SQL semantics, ACID, and so forth: SQLite wins hands down. For a fuller database and anything that needs any significant concurrency SQLite is not what you want and isn't trying to be that.
You're absolutely right, though given the choice between developing a large scale app with SQLite or MySQL I'm afraid I'd go with SQLite. At least I'd know what hell I've wandered into.
We are in danger of encouraging a small war here, but I would partly agree with you in that I would not by choice use mySQL for anything. IMO there is nothing for which one of SQLite or Postgres (or SQL Server or other decent RDBMS) are not more suitable for. mySQL and its descendants/variants are popular today mainly because of a mix of two things: many people not knowing better and its popularity having significant momentum.
I still wouldn't use SQLite for a large scale app (assuming that means one that needs concurrent data access for multiple users) though: it is not the right tool for that sort of job.
I'm not as familiar with later versions of MS-SQL, but it's replication story is well in the box... Postgres is a bolted on, hard to setup/maintain hodge podge... I really hope the out of the box, via a gui admin and all story for replication on postgres gets to where MS-SQL was in the box in 2005.
NOTE: this is not a bash or nag on postgres, I really like it's JSON integration, and plv8 is awesome. That said, even simple replication stories in pgsql have me pulling my hair out, mainly because I don't WANT to know every intricate detail about my dbms, I just want to build applications on top of it, and ms-sql is pretty nice in that regard.
Admittedly I've never used the replication in Postgres but that surprises me. I don't think I had the opportunity to use replication in MSSQL 2005 but it was pretty awful 2003 and pretty terrific in 2008 so I guess it was fixed somewhere in between.
One thing to be said about Microsoft and their products is that they probably support what you want to do. It may cost you an arm and a leg, but it's probably supported (as with most high-end enterprise products, I find). It's easy to see how this came about, too. "We have a multi-million dollar sale on our hand, but they really want feature X. Guess we can subsidize the development with that sale."
I feel like cost has only really become an issue recently with their drive to get everyone in the cloud or on Azure and even then it's only an issue for small businesses. They offer ridiculous discounts to nonprofits and in education settings it's basically free.
Having used both, I'd take that bet. MSSQL is in my opinion the best DB out there in terms of ease of use, documentation, integration, and tools. PostgreSQL is a distant second, and SQLite is probably a distant 3rd (though it's really not in the same category).