I've worked extensively with both SQL Server and Postgres and money being no object, there are valid reasons to investigate both. SQL Server's query planner is better, Postgres has only just got limited parallelism for some sequential scans, and still treats CTEs as second class citizens. Postgres in version 10 is going to get proper partitioning, which SQL Server has had for years. There are vast numbers of feature differences, down to individual GIS functions one platform has over the other.
If you have the money MSSQL is a better all-purpose RDBMS. The tooling is in a completely different league and there are fewer surprises with regards to performance (like PG's slow counts, paging, etc.). I very much hope PG will close the gap though.
I keep hearing about Microsoft SQL's tooling. Can someone explain to me, at length even, what "tooling" is?
This is coming from someone who has written dozens of web applications for PostgreSQL. These are business applications with complex rules and complex SQL like window functions, recursive queries, PL/pgSQL functions, and so on. The only tools I had were vim and psql, and I've been completely happy.
Every time I have had to write an application that uses Microsoft SQL (because it was already set up for a related application) I cringe, because it is a hundred times harder.
SQL Server Profiler = awesome. You can basically say "record every query that's going through the server for the next 15 minutes" – and then rerun those queries through a planner (SQL Server Tuning Wizard) to see where you can optimize them by adding certain indexes, etc. It'll even suggest indexes, etc.
So you could basically trace a production workload and then tune it offline.
Doing things like backups in SQL server using the agent, and running various workflows, also works really well. To this day, as far as I can tell, doing backups for most open source databases are a hodgepodge of bash scripts, cron jobs, and *dump executables, which every admin reinvents every single time.
These are all GUI apps that, albeit have barely been updated in a decade, but still better than most first party (or even third party) open source DB management tools out there.
I've heard all the high availability stuff is really good too but I've also heard that it's a pain to setup and until recently, only available in the really expensive enterprise license.
It's not usually that simple with profiler, as production queries can be highly contextual and aren't always 'replayable' unless they're interacting on the same data as the original. It can also be pretty high impact on a busy system.
Agent is nice because of the surrounding infrastructure -scheduling, notifications, etc - that said, it still often ends up being a hodgepodge that every admin reinvents, just using different underlying structures (cron=scheduling, system mail=notifications, etc).
And yeah, the HA stuff is good - with the setup becoming gradually easier with each new version, but those license costs are definitely reflecting that.
While SQL Server does have some great features - it's also missing a fair bit of functionality. I.e., there's no real 'overview' of how the system is performing. Look at SQLSentry for an idea of what I mean - I only manage one 'major' database, but without that tool I'd be hard pressed to keep up. There's also things that have been broken for years now which they've failed to address - i.e., one of the most popular add-ons for SQL Studio is SQL Prompt to get actual working intellisense. They can manage it for multiple extensible languages in visual studio, but flub it for years with the fairly static TSQL. I actually thought it must have been a fairly complex problem until I saw JetBrains implement it for multiple variations of SQL in DataGrip.
SQL Server ships with a number of "tools". I.e. applications that are pretty awesome.
SQL Server Management tool is a great core user interface to managing database servers and databases. Other tools that are commonly used by developers include the rather awesome query planner, query profiler, performance analyser and data import and export tool.
There are lots more told available for data warehousing / OLAP etc that are not so commonly used by most developers. SQL server is undoubtedly tool rich.
Except for ones that increase revenue or allow offsetting costs in other areas.
Having used MySQL and SQL Server, there's not much of a contest. You can do everything in both, pretty much, but you can do it more quickly and more maintainably in SQL Server. The developer tools are all really good, the functionality is outstanding, it requires less care and feeding. There are plenty of cases where the added money isn't worth it. But your time has value, too.
It's been my finding that experienced MS SQL developers tend to hire out at a much greater cost than experienced MySQL developers... MS SQL Standard also starts at $3,700+ USD per core (and a minimum purchase of 4 core license, I believe), with a yearly SA fee on top of that. You could use Express, but it's missing many features - SQL Agent for instance.
These companies all use MySQL as either their primary database layer, or at least as an essential storage system for a major property or system: Facebook, Google (doubleclick, cloudsql), Twitter, LinkedIn, DropBox, Alibaba, YouTube, WordPress.com, Wikipedia, GitHub, Yahoo, Yelp, Pinterest, Etsy, Tumblr, Flickr, Uber, Box, Booking.com, Instagram (migrating afaik), Lyft (migrating), Amazon (mysql backs DynamoDB)
re: "these days" meaning would these companies still choose MySQL if founded today? Impossible to say for certain, but some of them migrated to MySQL recently. Most have the resources to change databases if there was a compelling reason to do so.
Postgres has many appealing qualities, but so does MySQL (as well as SQL Server). Use the right tool for the task.
No authoritative source, but it's been leaked in enough places by enough people that I assume it is truthful. Here's a recent HN thread for example: https://news.ycombinator.com/item?id=13173927
I'm a bit dismayed by the downvotes on my original post above. The parent asked "who would choose Microsoft or MySQL over PostgreSQL" and I answered factually and literally with who chose MySQL :/
Maybe you don't have a choice. There's a ton of business applications that only support SQL Server. I worked for a company the used SAP Business One for their ERP system, it only supports SQL Server. Our infrastructure was mostly Linux, but for SAP B1, there's not a choice, you need SQL Server.
Old school client server database applications is still very much alive.
S/he is probably talking about the large ecosystem of companies that provide SQL Server tooling on top of the basics Microsoft provides. (Disclaimer: I used to work for one of them.)
That being the case then, yes, the tooling is light-years ahead of anything else.
MS have a long history of encouraging third party developers to create tooling around their platforms to help sell those platforms, and keep people using them.
Do they have a proper CLI client now? Working with SQL Server is great as long you have SQL Server Management Studio, but not having a CLI client makes me cry when I just want to do something quickly over SSH.
I worked in a reasonably sized organization that was heavily invested in MS products. If I had used SQL Server I would have had access to DBA's and secondary apps we were already licensing for ETL, reporting, etc... I chose postgres because at the time, the Linux drivers for SQL Server were junk. The message from our ops folks was basically, "Knock yourself out but you're totally on your own."
Postgresql is awesome, best open-source RDBMS by far, but MSSQL is more advanced. This is expected since hundreds of developers are being paid by Microsoft to work full-time on it.
I love Postgres and have been using it for a long time. However if you need good support for, for example, self-updating efficient materialized views, then Postgres won't really do (yet).
I would suspect that most instances of SQL Server support off-the-shelf applications that require SQL Server rather than custom built applications.
Of course, a lot of these come from Microsoft - but there are a lot of 3rd party applications that support SQL Server. There are also a lot of advantages of standardizing on a single database platform within an organization.
I suspect this will become a problem for Oracle/Microsoft over time, most startups seem content with the open source solutions. I doubt this will change as they mature, this leaves Oracle and Microsoft trying to screw more money out of a dwindling and aging set of customers.
The classic silicon valley "startups" are a tiny fraction of the market, and if they can't afford or don't want to pay for licenses of good software, they wouldn't be good customers anyway.
Paid support is available for MySQL and PostgreSQL too. It is from different companies than those who offer the SQL Server/Oracle/whatever support though, which may a problem if the newcomers don't already have a foot in the door already. Think of it from the business POV: why would the sellers of existing support leave their business to some outsider?
I'd probably pick PostgreSQL over either of the other two today for any case where my constraint was that I had to have the application run on Linux. Otherwise, I'm going Azure SQL Database or SQL Server if on-prem (as in, non-cloud) is needed. This will change soon with the OP about SQL Server on Linux.
SQL Server Management Studio is an incredible tool and beats the pants off of equivalent free tools like MySQL Workbench and pgAdmin. (SSMS is now freely available without an MSDN subscription or SQL Server license.) Yes, it requires Windows, but compared to the other tools on Windows it is second to none.
Azure SQL Database has been nothing but a good experience to work with. You automatically get 3 highly-available replicas of your database for no extra cost, built-in transparent data encryption, query auditing support, threat detection alerts, near 100% compatibility with SQL Server, and no need to keep the underlying OS or software up to date and secure. All of this for $30/mo (Standard S1 we've found to be adequate for most small businesses with line-of-business type apps.) Depending on the needs of the application, Azure SQL Database can either be a good replacement for an existing SQL Server instance, or a stepping stone towards a larger SQL Server installation. We're seeing many small businesses power down their on-prem/colo SQL Server installs and moving to Azure SQL Database to save on TCO.
SQL Server Express Edition is a free edition that supports up to 10GB databases, so it is perfect for your local development environment (assuming the size constraint allows that). In 2016 SP1, they even added all of the Standard and Enterprise edition features to Express edition like memory-optimized tables and columnstore indexes. With integrated Windows authentication, it is incredibly easy to have your team use a single connection string and for a new dev just install SQL Express and go.
SQL Server Database Projects in Visual Studio are by far the biggest reason for me to prefer Azure SQL Database or SQL Server. This is the most elegant way of storing your database schema in source control that I've ever seen. You define the schema as just CREATE scripts, and when you build you generate a DACPAC with the entire schema defined as if you're creating it from scratch. But upon deployment it determines what objects need to be created, altered, or (if you want) removed to make the target database schema match the normative schema in the DACPAC. So it builds its own migration script based on the schema of the target database, and it won't allow any operations that cause data loss by default. This is super handy in a team environment because you don't need to worry about writing migrations by hand, you just define the schema "as it should be" and no matter what version of the database your team members had on their machine it will get caught up. Also dealing with merge conflicts is easier, because you're just doing a line-by-line merge of the e.g. CREATE TABLE statement rather than having to worry about which order your migrations run in. If anyone knows of something equivalent for MySQL or PostgreSQL I'd love to know!
>So it builds its own migration script based on the schema of the target database, and it won't allow any operations that cause data loss by default. This is super handy in a team environment because you don't need to worry about writing migrations by hand, you just define the schema "as it should be" and no matter what version of the database your team members had on their machine it will get caught up
This only works if you're inside their rails. If you do have a change that requires a migration of data and risks data loss, you're then in the realm of creating pre and post-deploy scripts, and you're back on the migration train.
I've been playing around with a tool called sqitch ( http://sqitch.org/ ), but I'm not familiar with it enough to have an opinion on it yet.
Just a minor correction: SQL Server Management Studio is free-free. You don't need to own any Microsoft products to use it. (Not even SQL Server, go figure.) Microsoft changed the licensing a few months back.
Regarding SQL Server Express Edition, it is limited in much more ways than just max database size (memory, buffer size, etc). Since SQL Server Developer Edition become free most MSSQL developers I know switched to this one, as it has no limits whatsoever (except one - cannot be touched by end users). MSSQL Express seems more like a DB for small scale production environments now..
When did this change? Back in 2012 [1] before there even was a Premium tier, all Azure SQL databases included built-in fault-tolerance with two secondary replicas at any one time. Did they remove this feature for Standard?
Do they write a lot of queries? The breadth and depth of SQL features MSSQL supports blows MySQL out of the water. I work mostly with MySQL now, and it's painful to go back. For example, I really like windowing functions and table-valued functions. But there's a lot, lot more.
Do they want much better query performance? MSSQL again.
Are they a business type? They will probably like stuff like SSRS, SSIS, and SSAS, and all the additional tooling around them.
There really is no comparison between MySQL and MSSQL. Postgres is great, and generally what I use if I have a choice because it's free and better in many respects than MySQL, but even there I have problems thinking of things that Postgres does better than MSSQL, though there are a few. There's just _so much_ MSSQL does and so much it does right, and the stuff it does wrong is generally getting fixed (no more XML PATH, they finally added STRING_AGG!!!).
But if they're happy with what they're got, well, it'd be silly to spend the money on an MSSQL license.
IME, MSSQL is pretty google-able - that's really how I learned it. Do you have any specific problems with its googlability?
How much PostgreSQL do you run? Because I am a primarily PostgreSQL dev who occasionally uses MSSQL and MySQL. I always get annoyed at missing features when I have to use another database, while I do not know as well what features are missing in PostgreSQL simply because I know PostgreSQL way better than I know the other databases. It is easy to be blind this way.
Some features which PostgreSQL have which I believe are missing for MSSQL. This are features I use all the time in my every day work.
- Transactional DDL concurrently with snapshot isolation
- Exclusion constraints: a generalized form of unique constraints
- "Writable-CTEs": the ability to use RETURNING from an UPDATE, INSERT or DELETE in a CTE
- Regular expression support (I think fixed in 2016)
- JSON support (I think fixed in 2016)
- Many small things like lack of DISTINCT ON and array_agg
I think MSSQL is a pretty good database though, unlike MySQL which lacks too many features to be a competitive general purpose RDBMs (InnoDB has some nice properties though).
