I wonder who would choose Microsoft or MySQL over PostgreSQL these days.
Edit. I understand that it weighs against development time but that is a different matter than money in the bag.
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.
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 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.
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.
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.
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.
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 :/
Old school client server database applications is still very much alive.
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.
Don't know how it compares to Oracle as I've never used it.
For OLAP use cases it is years ahead of Postgres.
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).
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.
So I guess if you need in-memory data, SQLite or Redis are better options.
One reason is, Paid support and excellent integration with existing Microsoft products. The features are totally worthy to use SQL server.
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!
You can get it here: https://msdn.microsoft.com/en-us/library/mt238290.aspx
In addition to that, SQL Server Developer Edition is also free-free now. (You do have to log in to get it, though.) Link here: https://myprodscussu1.app.vssubscriptions.visualstudio.com/D...
Note that Standards do not have more than one replica. Only Premiums do.
[1] https://azure.microsoft.com/en-us/blog/fault-tolerance-in-wi...
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.
If I would venture for a new product without definitive advantage, one needs better "googlability", which seems still in much favor to MySQL.
Besides that, you have tools that works fine with MySQL and people who are used to using it which also needs conversion.
I'd like to know how you would convince any average MySQL users the switch?
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?
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
And I love it, but I wouldn't be expecting it to show up in MSSQL.