Also, hilarious that they offer visual studio in not just RPM and DEB but tar-ball as well, never mind 32-bit versions. Thats much more thorough than certain web browsers these days...
I'm excited about this release because it means that developers of frameworks and libraries that target SQL Server will no longer need to have Windows available to test their software. As a specific example, the SQL server backend for Django could now (soon) be bundled with Django directly and enjoy the same level of support that other backends do. It'll also mean that users will be free to run Django on Linux, and use the drivers to communicate with a windows or Linux based SQL server.
I should note that Django isn't planning to add more backends to the core project, and has actually discussed moving some into separate packages. But that doesn't mean a backend couldn't be developed by the Organisation.
Microsoft had SQL Server Native Client (ODBC) driver for Linux available for a few years now. So your could develop/test on Linux with SQL Server running in a VM. This is how we have been doing it in ODB (C++ ORM) for years.
Of course, don't expect either of them to be open source. Which means an open source ORM backend that depends on them can't be in an official Debian/Ubuntu/RH repository.
The SQL Server running in a VM is precisely the part you no longer have to worry about. Drive by contributors aren't going to do this when offering ORM patches. But if they have an easy way to test on Linux it becomes a lot more viable. Going back to Django, we provide a vagrant image with most databases preinstalled. Adding sqlserver to this setup will now make sense.
Also Microsoft are deprecating the old bindings and are creating new ones I believe will be open source. I might be off slightly.
> Adding sqlserver to this setup will now make sense
I would strongly recommend you check SqlServer license terms first; the last thing you want is to make any of your downloaders liable for license payments as soon as they start the image. I really don't think what you suggest is possible, because you would be redistributing SqlServer - maybe with the Express version, but that was pretty crippled last time I checked.
FreeTDS is "good enough" for every time I've needed to talk with SQL Server, including the various Python/Ruby/etc. drivers built on top of it. Ditto with jTDS for Java. Outside of .Net development I have never used the official Microsoft SQL Server driver.
jTDS is severely lacking in features. AFAIK it stayed on JDBC 3.0 / J2EE 1.3. This is quite outdated for for todays world an is likely going to cause issues with current frameworks and libraries.
Fair enough, though I haven't run into (m)any situations where the driver not supporting JDBC 4.X has caused problems.
Now that Microsoft's JDBC driver is open source and on Maven Central I might switch to it, though thankfully MSSQL is starting to lose favor in my team as we become more familiar with PostgreSQL.
And the TDS specification is also available (no registration needed to read the docs). So anyone can write their own freely licensed driver (much like FreeTDS). I've been thinking of writing a native Haskell port, myself, as SQL Server on Linux is so much easier to start with (SQL Server is one of the systems that has almost 0 support anywhere in our ecosystem, but I know of more than one person who'd like to see it).
So while SQL server is unavailable, free client drivers will hopefully begin popping up more and more and will become accessible through e.g. Debian repositories, with time.
I looked at the TDS spec and it's one of the most confusingly written protocol specifications I've seen. The unusual/somewhat verbose wording, general lack of examples (except for a brief section at the end, in hexdump and XML(!?)) and overall (mis)organisation give the impression of hastily redacted internal documentation and/or deliberate attempts to discourage implementation while still being able to claim the protocol is "open".
I too am excited for this, along with the rest of their movement towards adding support for Linux. I know I'll be a die hard C# guy forever (though I still can't help but enjoy learning other tech), but I want a non-windows option. I'd add that though it's nice to have SQL Server on Linux, I'm still waiting for them to make DocumentDB an on-premise installable package. For many people heavily invested in SQL, it is the only tool in their toolbox. Having a Microsoft supported NoSQL option would only help people invested in the Microsoft stack of products. I hate to be cliche, but when you know only SQL, every data problem looks like a SQL nail to be hit with your SQL hammer.
You can connect to SQL Server from Windows, Linux and Mac using Python, Node.js, PHP, Java and C#. This includes integration with frameworks like Django, Laravel, Entity Frameworks, Hibernate etc.
It's not about getting rid of Microsoft: it's about getting rid of Oracle. Their licensing costs even for a very large shop may start out cheap (think 90% discounts) but will creep up over time. And when you inevitably manage to get a combination of oracle-dependent data, queries, processes, etc entrenched the prices start to climb and the even more expensive options come out. Oh you need more performance? Go horizontal with RAC. You need to go faster but can't reengineer things? Exadata will fix you right up.
While the licensing details can be somewhat complicated, the retail price of ~$7k/core is published on Microsoft's site at https://www.microsoft.com/en-us/sql-server/sql-server-2016-p.... Oracle's pricing is harder to come by but is closer to $25k/core for the Enterprise edition on x86 CPUs. There are often lots of extras involved in Oracle licensing, too, so the total cost differential between the two is generally higher than the per-core license cost differential.
The licensing difference is not that much; what is really different is TCO. Any IT guy can reasonably look after SqlServer, whereas Oracle needs specialised personnel which will inevitably be outsourced offshore, increasing complexity and costs.
And Oracle support contracts (which are basically mandatory) are really expensive.
How hard is looking after postgres compared to SqlServer? I'm a .net developer that's been spoiled by SqlServers ease of use. I just started at a place that use sybase which is a nightmare.
I concur. It's not the most powerful in all aspects or for all applications but, most of the time, if you want an enterprise grade RDBMS (and you're happy to pay) it's the one to go for simply because by comparison with the others it's so easy to work with.
The ease of use also makes it suitable for smaller shops as well. At the lowest level you install it, and it just works, and it's back by a great tooling ecosystem, and excellent documentation. It really does stand in absolutely stark contrast to, for example, Oracle.
I wouldn't call it idiot-proof, but it's certainly friendlier than most RDBMS. (That's not to say there aren't issues and annoyances with it, mind, but that's true of any product.)
Beyond this, if you need a relational store and you really, really don't want to have to give a monkey's about it, I'd say SQL Azure is probably the way to go.
(Saying all of this I recognise that lots of people will be able to tell war stories about the torrid time they had with SQL Server, or for any other data store. My experience has largely been it's a winner though.)
Anyone care to elaborate on how well it has aged? When I last used SQL Server in 2010, it was indeed pretty cool, especially the awesome ecosystem (SQL Studio, Reporting Services, graphical Query Planner, etc.).
Then I went to MySQL because my startup couldn't afford a database and it was a pretty large step down.
Then at the next startup, I got used to PostgreSQL and by now I'd have a pretty hard time to not being able to use JSONB, window functions or CTEs - made me realize how bad most other DB systems are by now. That is, except for a really nice GUI for PostgreSQL I'm a pretty happy camper by now.
Yes SQL 2016 has native support for JSON, CTEs (this one has been in since 2005), and windowed functions I believe were also in 2005.
Some other massive improvements are Columnstore indexes for absolutely ridiculous OLAP data reads and compression (we are seeing 90% compression and < 1 sec instant lookup times on multi-billion-row fact tables).
No master/master - that's the only thing I would consider missing compared to Oracle. It scales up, but not out. Peer to peer replication technically is master/master, but calling that clunky would be a kindness.
The short of it is that it's pretty good. They call it "Always On", and you can do both asynchronous and synchronous, with the latter offering automated failover.
IMO, it's matured very well. I'm always surprised by how good performance is in a virtualized environment compared with Oracle using the same front-end enterprise applications.
The biggest advantage IMO is that you can get strong performance out of SQL Server with relatively little DBA knowledge compared with Oracle. Installation is easy, integrated authentication is easy, and backups are easy. Running a cluster and doing replication is an area that all RDBMS systems need serious improvement on, but initial setup and working with a relatively stagnant schema is straightforward.
The one area that SQL Server has been lacking is having a native linux client (which is hopefully alleviated now). Not having one is the only real drawback that I run into.
Absolutely agree on the performance front. If you're starting out, generally SQL Server will run well out of the box, and if you upgrade hardware and reinstall on the new hardware it'll generally work well without having to do anything else. At some point you might have to start thinking about I/O and how best to arrange files but maybe not for a while, depending on what you're doing. (Just to be clear, let me restate: I'm talking starting out here - this is obviously NOT the approach you'd take when building or upgrading a large, complex application - I'm talking basic CRUDdy type apps here.)
Contrast this with some of the NoSQL platforms where immediately you're forced to think about topics such as sharding and clustering (I'm looking at you Couchbase) and the pain in the ass factor is much lower.
Key point: you don't need much of a clue about SQL Server administration to start using it, but you perhaps do with Couchbase, Elasticsearch, etc. From an ease of use point of view with NoSQL, I'd probably point to MongoDB as having the most approachable learning curve.
There's also no BS about having to keep everything in memory to keep performance acceptable (I'm looking at you, again, Couchbase - that's two strikes). The whole point of SQL Server and other RDBMSs is that they continue to work well when the amount of data you need to store vastly outweighs the amount of physical memory you have. Of course, if you can get everything into physical memory it will perform better but, even if you can't, it will still perform well.
Of course, if you're using SQL Server you do need to learn SQL (true for any RDBMS), which can be off-putting. It's absolutely not a beautiful language - in fact it's bloody aggravating when you write a lot of it - but it is extremely powerful and excellent for working with sets of data arranged in rows (i.e., tables). Nothing else really comes close.
Here's a couple of pain points from a dev that doesn't know any other database:
1) Lack of deferred constraints. To me it's natural to think that database constraints shouldn't apply while you're shuffling things around during a transaction. Other databases support this, apparently
2) Lack of multiple cascade paths. Why should I have to implement this functionality in triggers?
3) Why can't you create a column in the middle of a table? I know it has something to do with the column order being tied to the actual physical storage order, but why are they connected in the first place? Just show me the columns in the order I specify, I don't give a damn what order they're actually stored in (or should I?)...
AD 3) The typical answer is "why would you ever want to insert column in the middle of a table"? Logical order of columns is not connected with physical storage. MSSQL will physically arange columns in an order which ensures most effective perfomance (generally smallest data types at the beginning, largest at the end). Microsoft could make it so that new column is being inserted into any place in table definition, there just isn't any real reason to do it. You have to specify column order in select statement anyway so.. (SELECT * is bad bad practice for anything serious)
EF will deal with much of this for you because it uses "empty" rows so FKs references can always be satisfied during a transaction, but this obviously slows things down.
With (2) I'd question what you're doing. I'm not saying absolutely don't use them, or that what you're doing is "wrong", but TRIGGERs often result in side-effects that cause problems further down the line. They can be OK for auditing (but consider other options such as event sourcing if you have this kind of requirement), but the moment you start putting business logic in them, or hanging it off data modified by a trigger, you've started down a potentially dark path. The reason for this is that suddenly DML operations have side effects that may not be anticipated by people working with the database in future. In fact, depending upon how permissions are set for developers, they may not even be aware that triggers exist, leading to unexpected results, "weird" performance problems, etc. I've run across this recently in a moderately sized client database: ~500 tables, ~2500 stored procedures, and a few dozen magic triggers that most people are unaware of, which manifested themselves when we collected execution plans for some query tuning ("Where's all this stuff coming from? ... Oh.").
With (3) I'd just be interested in hearing your use case.
> Windows may be worth getting rid of, SQL Server isn't (except perhaps on grounds of licensing cost)
I second that. I am not a big fan of Windows, but SQL Server has not given me the slightest trouble so far, except in cases where I was kind of asking for it.
> SQL Server isn't (except perhaps on grounds of licensing cost)
What about supported applications? Most apps I've worked with (eg: django) support MSSQL as a second-class citizen, while other (eg: postgres) are much better supported.
Speaking to some folks from Microsoft at RubyConf last year, I know they are actively bringing in developers for the various adapters to try to improve this story. I believe this is a matter of the frameworks choosing first-class support rather than Microsoft. (been running a Rails app backed by a large "legacy" SQL Server for 3-4 years)
> I believe this is a matter of the frameworks choosing first-class support rather than Microsoft.
The problem has generally been the license and the environment.
Any framework wanting to support MSSQL needed to have MS Windows and MSSQL licences for CI (+developers), plus the infrastructure to run windows on the CI (I've no idea how you'd do that, so there's also a knowledge barrier).
There are few things which can be more embedded in your app's architecture than the data store, especially if the initial (and reasonable) assumption is that you won't ever switch away from it.
My default RDBMS of choice has always been PostgreSQL and I'm not sure why I should choose SQL Server, even now that it's available on a non-Windows platform. I understand that there are some fancy DB things that it can do and it's optimizer can be better in some circumstances, however I like to use ORMs and don't generally interact directly with the DB. Besides, those things pale in comparison (in my mind) to the hassle in dealing with Microsoft and licensing. Using PostgreSQL is simple and doesn't involve breaking out a spreadsheet to determine how much we need to pay for it. I've seen Microsoft licensing and the companies I've been at have had to have entire departments to figure it out. No thanks.
Now, the one thing I've heard from everyone is that the SQL Server tooling is beyond belief, and I believe it. If there is one weakness in the open source RDBMS world it's tooling. With such as large and obvious gap how is it that no one has filled it yet? Will no one pay for tooling? Are there tools available but the quality is not there? Seems like a good candidate for someone to fill a niche and possible make a successful business.
Well, with paid db management tools like Navicat, and open source BI tools like say, Pentaho (both just examples) you can get roughly on parity with SQL Server's tooling for something like Postgres.
Microsoft has just been polishing the integrated set of tools with SQL Server for a long time, especially with GUI tools that make the job of administering SQL Server more accessible for a different class of user (e.g. the ones who don't compile their own kernels and spend all day in a shell).
Oh, SSAS/SSIS/SSRS definitely aren't included on Linux currently. I wasn't suggesting they were.
But getting the features of these tools (cubes/OLAP = SSAS, reports = SSRS, ETL = SSIS) for other databases such as Postgres is possible with open source BI tools.
Open source BI platforms like Pentaho, BIRT, Jaspersoft (now owned by TIBCO) roll together families of different open source tools to give the same type of functionality for any DB platform. For example, things like Jaspersoft use the Mondrian OLAP cube engine underneath.
And also, with all the newer engines for data processing (Apache Spark, Hadoop, Ignite, etc.) you also have a lot of new backend options for analytical/report/BI processing also.
> I understand that there are some fancy DB things that it can do and it's optimizer can be better in some circumstances
That's a pretty big deal. (Also not sure what the ORM has to do with it... Unless it's just the fact that ORMs dumb down the queries that are possible)
Is it though? Is the Postgres optimizer that much worse? As for the ORM, I was trying to point out that something like ActiveRecord doesn't rely on the inherent capabilities of the RDBMS as much as raw SQL might. So things like stored procedures, triggers, advanced CTEs, etc. don't really add any value.
What tends to happen is death by a thousand cuts. Postgres can probably be tuned to efficiently run most queries that SQL Server could run, but DBA time is spent on it when on SQL Server the issue might just simply not exist. It also really helps power users who know enough SQL to write a report, but maybe not enough to tune queries.
> I understand that there are some fancy DB things that it can do
I haven't seen this anywhere else in this thread, so here's The Register saying that the Linux version would be lacking some features compared to the Windows version, though it doesn't mention what those might be.
It doesn't surprise me; I can imagine there's quite a bit of stuff in the server software, as a whole, that is dependent on native OS integration. I'll be interesting to see a matchup once it starts getting reviewed.
SQL Server, Sharepoint, Active Directory and Office are all platforms in their own right. It does not make sense to constrain them by requiring the windows platform to run.
Balmer was jumping across the stage yelling Developers, Developers, Developers! Now someone in Redmond is executing and pushing the applications and tools platforms. It will be interesting to see where this leaves Windows in the medium term.
Well, even though he was completely lambasted and lampooned by "Developers Developers Developers", he wasn't wrong.
We see the 85%/15% split in programming and developing as well. When something is just too good to pass up, a lot of us switch over and start working on the next hotness. Sometimes, those things switch and become the embedded tech you have to learn to get in there. Arduino is one such thing. As is C, and Linux. For DB's, it used to be MySQL, and now PostgreSQL, and large heapings of good/bad for MongoDB and Redis.
Where MS lost for a long time, was the insane amount of bad will towards protocol obfuscation, protocol "extensions" that break IETF protocols, and of course the monopolist behaviors that they were found guilty for.
The problem is they ran away people who'd develop for their platform, and make crazy awesome things. Can't say I blame them. I'm one who ran, and really haven't looked back. And recently, we had a "party" at work, because I was able to wipe the last 2 windows boxes we had. To Linux.
Active directory is not a separate product like the others it is very much a core part of Windows Server. Unless you think AAA parts of an OS are a separate product from the OS, AD is very much tied to Windows.
You could turn AD into just another identity management platform, but you would pretty much lose everything that makes AD awesome. AD is impressive because it is so integral and closely tied to Windows. It begins to lose its lustre when you integrate other platforms because when you lose that close coupling, there's nothing but a distributed user authentication store.
I'm not sure what else you want from a directory server but a "distributed user authentication store." Maybe you're referring to file and print services? Email server integration? All of these kinds of things should be available, regardless of what platform the directory server is running on.
I'd make some long argument about the Linux market longing for an easy-to-use directory server, but I see that Novell Directory Services still lives on as NetIQ, and I've never heard of anyone using it, so maybe the market just isn't there. (Another victim of Microsoft's monopoly.) So maybe it doesn't matter that AD isn't platform independent. I guess companies running a lot of Linux are happy to hassle with the nightmare that is OpenLDAP.
The various open source solutions that Oracle EOL'd which are now offered by ForgeRock have seemed to be decent alternatives in this space, but of course they are now making the source less accessible:
> Active directory is not a separate product like the others it is very much a core part of Windows Server. Unless you think AAA parts of an OS are a separate product from the OS, AD is very much tied to Windows.
Which is aggravating when you want to setup a test environment.
I'm curious what results we will see when people start benchmarking it running on the same hardware but Windows in one case and Linux in the other.
Also, what would happen (if anything) if the Linux results crushed the Windows ones? Would that be embarrassing to Microsoft? And to take the thought to the next level of paranoia, let's say Microsoft already ran those benchmarks in-house and found the Linux version vastly faster. To avoid embarrassment, would they slow it down to be more in line with the Windows version?
I'd guess that it would be slower on Linux by design, but not in a malicious way. They must be using various win32 apis and might have needed to shim them on Linux. Maybe they had assumptions on NTFS performance, paging behavior etc. All would contribute to a performance difference.
Even if it was, it likely was tuned for, or even built for, only one OS for years.
That will have made an impact. For example, if thread creation is faster on an OS, starting threads for smaller tasks becomes a win; if locking primitives are slower, it may be better to take a full-table lock more often.
It reminds me of when the Windows version of Firefox was faster running in WINE than the native Linux one... which then begs the question of whether the same could be true of SQL Server.
I have heard this too. However when working with SQL server it was generally recommended (iirc) that fibers not be used except, and were only goof for very specific niches.
I used Sybase ASE on Linux in the early 2000s. It was a joy to use back then. Same dialect of SQL as Microsoft SQL Server, easy to set up and work with.
Microsoft SQL Server has since diverged to the point of being a completely different codebase, whereas Sybase stagnated and were acquired by SAP.
I've always thought that Microsoft's operating systems were the Albatross around their neck. Their apps and systems are OK. Having those available on a superior OS, like Linux would be good for the world, and MS.
Buying canonical would probably be the quickest way.
> We have made it easier than ever to get started with SQL Server. You’ll find native Linux installations with familiar RPM and APT packages for Red Hat Enterprise Linux and Ubuntu Linux, and packages for SUSE Linux Enterprise Server will be coming soon as well.
So easy for some ;) Not to not applaud their efforts though.
SQL Server is meant primarily for enterprises, what they're doing makes sense. IMO if they want to be super nice* they could provide a zip package which other distros can turn into their specific packages.
* Open Sourcing it is probably unrealistic at this point.
Microsoft is adapting at a good pace, going beyond the windows ecosystem. Last time I used SQL server, it didn't support sharding across machines, so only supported vertical scaling (unless you buy the appliance or add sharding logic at the application layer). I think that's something they need to fix, to start using SQL server for large scale applications.
Now if they can just fix windows, I might start using that too. Maybe WindowsX? I do enjoy using VSCode on Mac!
Thanks for sharing this! Its interesting to know Microsoft tried this 10 years ago. They might eventually bring it back, after making sure all their other applications work on ..nix :) They are definitely sowing the seeds now, with .NET support on Linux, adding the ubuntu subsystem, SQL server...
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).
You might consider it if you have to ship with a lot of pre-loaded data ("vanilla" data), as it'd be easier to do all those inserts/BCP executions just once on a local network and not over the internet.
Will be very interesting to see how this affects SAP installations. AFAIK the only database options were SQL Server and Oracle. Granted it's been >5 years since I had to deal with this mess but this could be amazing news for some people on SAP who were pretty much forced to run Windows just for the database.
In fact, this is not the case.
Actually, besides ORACLE and MS SQL Server you can use also DB2 and other SAP's own DB products(MAXDB, Sybase, HANA).
I am talking about ERP and CRM, the main products.
Anyway, this is a very interesting news. Given the "not so good" relationship between SAP and Oracle, this option will give SQL Server a boost when it comes to SAP Customers, although it may take some time for SAP to fully support the Linux-MSSQL option.
SAP Business One is available with SQL server or HANNA. In fact resellers I have been talking to seem to have been instructed to sell HANNA first. I was told with the trade-off of more expensive hardware (HANNA) against more expensive licensing (SQL Server) it would come in about the same cost, but with the advantages of an in-memory database (especially when it comes to analytics) going to HANNA.
I assume it shares the same insane licensing model. I genuinely do not understand how people put up with that (outside of enterprises that normally overpay for everything).
I stand corrected. Cost is not what I was referencing.. the CAL model was my problem. No software should require a data sheet to understand the licensing.
Yes, understanding licensing has a cost all on it's own. Not just to buy the products, but as your developing you have to be aware of what you can and can't use, the developer edition has everything, which can create some traps.
Ahh, yes. It is confusing. MS makes you choose either "license by core" or "non core licenses + CAL per user". Then, the differences between Enterprise, Standard, etc.
It is simple, imagine you were the FD of a SME who uses a certain tech, but it is incidental to the business. You have two proposals put to you, and one of them is various strung together FOSS solutions and the other MICROSOFT. You take a personal risk by approving the project. Do you have time/ability to dig down into the risks/payoff for the other solution. £20k on a db from a brand you trust, as part of a £250k project. Would you quibble over it? That is why people pay the money.
> If Microsoft ever does applications for Linux it means I've won.
https://en.wikiquote.org/wiki/Linus_Torvalds