SQL Server on Linux public preview(blogs.technet.microsoft.com)
blogs.technet.microsoft.com
SQL Server on Linux public preview
https://blogs.technet.microsoft.com/dataplatforminsider/2016/11/16/announcing-sql-server-on-linux-public-preview-first-preview-of-next-release-of-sql-server/
170 コメント
I think he already did a while ago[1], [2], [3], [4] etc, but am sure this is a good excuse to open another one. :-)
[1] - https://code.visualstudio.com
[2] - https://github.com/PowerShell/PowerShell
[3] - https://www.microsoft.com/net/core#linuxfedora
[4] - https://blogs.msdn.microsoft.com/wsl/
[1] - https://code.visualstudio.com
[2] - https://github.com/PowerShell/PowerShell
[3] - https://www.microsoft.com/net/core#linuxfedora
[4] - https://blogs.msdn.microsoft.com/wsl/
Heh, i somehow missed/forgot those.
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...
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...
Of those, I think only #1 and #2 should be considered "applications".
You're probably right. Point being, they already shipped code for Linux.
Given the licencing of an enterprise installation of MSSQLServer, I'd say 'winning' is a relative term.
I'm not sure if I'm misinterpreting your comment but paying for software is not bad, even if the software is expensive.
I'd say the simple fact that an enterprise Microsoft product is running natively on Linux is a pretty big win. I guess hell has frozen over.
I'd say the simple fact that an enterprise Microsoft product is running natively on Linux is a pretty big win. I guess hell has frozen over.
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.
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.
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.
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.
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.
You must have checked back in 2010. It has like a 10gb limit. But comes with most standard features. Even full text search. (Wasn't available in 2008)
A 10 gb limit makes it unusable for me, so that's probably why I discounted it.
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.
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.
The Microsoft JDBC driver is MIT licensed: https://github.com/Microsoft/mssql-jdbc
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.
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.
> (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).
Add another to the list. I've had to resort to using hs-odbc who's forced to use freetds, meaning there are weird issues like this:
https://github.com/hdbc/hdbc-odbc/issues/17
I did find one pure haskell mssql implementation and was trying to use it here:
https://github.com/hdbc/hdbc-odbc/issues/17
Oh cool, it looks like it has commits over the past few weeks!
Add another to the list. I've had to resort to using hs-odbc who's forced to use freetds, meaning there are weird issues like this:
https://github.com/hdbc/hdbc-odbc/issues/17
I did find one pure haskell mssql implementation and was trying to use it here:
https://github.com/hdbc/hdbc-odbc/issues/17
Oh cool, it looks like it has commits over the past few weeks!
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".
It can be in Debian contrib. As a matter of fact, libdbd-oracle-perl is in Debian contrib.
Officially, Debian contrib is not Debian, but there is no practical difference.
Officially, Debian contrib is not Debian, but there is no practical difference.
There is open source support for contacting MS SQL servers for many years:
https://en.wikipedia.org/wiki/FreeTDS
https://en.wikipedia.org/wiki/FreeTDS
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.
To see how you can use Django via Django-pyodbc-azure(and other frameworks like Laravel, Express.js etc.) with SQL Server check out the tutorials here: https://www.microsoft.com/en-us/sql-server/developer-get-sta...
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.
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.
> We have seen strong reception for the private preview to date with more than 50% of Fortune 500 companies applying for the private preview
wow
wow
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.
Anecdata point: I've heard SQL Server Enterprise runs $20k a core. I've never heard pricing for Oracle.
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.
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 guess it is easier to get rid of Windows than it is to get rid of SQL Server.
It probably is, but there's an even better reason: Windows may be worth getting rid of, SQL Server isn't (except perhaps on grounds of licensing cost)
I've worked with all major RDBMS systems at one point or another and SQLS is by far the nicest.
I've worked with all major RDBMS systems at one point or another and SQLS is by far the nicest.
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.)
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.
How has SQL Server kept up in the meantime?
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.
How has SQL Server kept up in the meantime?
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).
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).
What is MSSQL's replication story these days? Master/slave or master/master? Automatic failover?
Automatic failover with read-only replicas.
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.
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.
[deleted]
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.
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.
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?)...
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?)...
Absolutely to (1): both Oracle and PostgreSQL support this. Note my comments on the topic of the lack of deferred constraint support at https://connect.microsoft.com/SQLServer/feedback/details/124.... It's extremely aggravating.
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.
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.
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)
No idea about JSONB, but CTEs and window functions are common features in most systems, SQL Server included.
> 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.
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.
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).
Hopefully this will change now.
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).
Hopefully this will change now.
[deleted]
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.
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).
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).
i dont think the BI stack SSAS, SSIS, SSRS ... is part (for now at least) of ms sql on linux
i do hope thought they do provide it and i really hope they provide SSAS and SSIS development tools on linux
i do hope thought they do provide it and i really hope they provide SSAS and SSIS development tools on linux
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.
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)
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.
IDK how PostgreSQL's and SQL Server's query planners compare. My point is that if one produces more optimized queries, then yes, that is a big deal.
> 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.
http://www.theregister.co.uk/2016/03/11/sql_server_linux_201...
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.
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.
http://www.theregister.co.uk/2016/03/11/sql_server_linux_201...
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.
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.
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
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.
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.
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:
https://news.ycombinator.com/item?id=13068512
https://news.ycombinator.com/item?id=13068512
> 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.
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?
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.
Official blog post from today that shows more details: https://blogs.technet.microsoft.com/dataplatforminsider/2016...
> They must be using various win32 apis and might have needed to shim them on Linux
Here's a high level overview: http://arstechnica.com/information-technology/2016/12/how-an...
Here's a high level overview: http://arstechnica.com/information-technology/2016/12/how-an...
If I'm not mistaking, SQL is an OS on itselve, so it was designed crossplatform from the start
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.
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.
https://appdb.winehq.org/objectManager.php?sClass=applicatio...
...but it's unfortunately not even installable.
https://appdb.winehq.org/objectManager.php?sClass=applicatio...
...but it's unfortunately not even installable.
I'm not sure if current releases of SQL Server include the infamous DeWitt clause (http://stackoverflow.com/questions/12115397/is-it-against-li...).
Keep in mind that SQL Server has had a role in driving certain windows features which I'm not sure exist natively in the Linux kernel.
IIRC fibers were added to the NT kernel specifically for SQLS.
IIRC fibers were added to the NT kernel specifically for SQLS.
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.
Sybase SQL Server 4.2 and Microsoft SQL Server (circa 1993) were identical. The products have since diverged, https://en.wikipedia.org/wiki/Sybase#History
Sybase 11.0.3.3 for Linux was made free for production use somewhere around 2002. It is still useful for some applications, if you can find the binaries, http://froebe.net/blog/2013/03/10/howto-installing-and-runni...
Sybase 11.0.3.3 for Linux was made free for production use somewhere around 2002. It is still useful for some applications, if you can find the binaries, http://froebe.net/blog/2013/03/10/howto-installing-and-runni...
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.
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 been expecting MSLinux for 5 years > now.
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.
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.
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.
* Open Sourcing it is probably unrealistic at this point.
the "alien" program can convert packages between different install formats.
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!
Now if they can just fix windows, I might start using that too. Maybe WindowsX? I do enjoy using VSCode on Mac!
I think you meant WinX: https://www.wired.com/2005/02/microsoft-5/
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 wonder who would choose Microsoft or MySQL over PostgreSQL these days.
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.
One other big reason is SQL Studio is fantastic. I've never found anything remotely comparable for PG
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.
> The tooling is in a completely different league
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.
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.
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.
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.
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.
There is no such thing as have the money, every expense chips away from the profits.
Edit. I understand that it weighs against development time but that is a different matter than money in the bag.
Edit. I understand that it weighs against development time but that is a different matter than money in the bag.
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.
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 wager the labor savings from SQL Server will pay for itself in a few months.
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.
At some point, you end up paying more for your employees to manage stuff than build stuff. That's when you buy better tools.
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.
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.
Is there a source somewhere that says Mysql is used behind DynamoDB? As far as I know Amazon is very silent about what is used underneath.
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 :/
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 :/
Thanks. No downvotes from me, I was just curious because I hear the DynamoDB-MySQL rumor here and there but never saw it confirmed.
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.
Old school client server database applications is still very much alive.
Have you used SQL server? Tooling is light years ahead of pretty much anything.
No to mention the SQLS execution planner will beat the pants off both MySQL's and Postgres' when it comes to non-trivial queries.
Don't know how it compares to Oracle as I've never used it.
Don't know how it compares to Oracle as I've never used it.
You mean the CTRL-L query profiler, the table editor and the server performance monitor that the MSSQLMS had since 2005? Or is there something else?
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.
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.
There has been a command-line client as long as I can remember (OSQL.EXE first, and later SQLCMD.EXE).
Why wouldn't you use an ssh tunnel?
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."
[deleted]
[deleted]
[deleted]
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.
For OLAP use cases it is years ahead of Postgres.
For OLAP use cases it is years ahead of Postgres.
That really depends on your use cases.
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 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.
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.
All companies eventually need a generic BI stack and Microsoft has one of the best around, all built on top of SQL Server & co.
AFAIK PostgreSQL currently doesn't support in-memory table.
In-memory table is blazing fast.
Well at first I thought that you could create an in-memory tablespace. But it doesn't seem like its a good idea though, http://stackoverflow.com/questions/7872693/running-postgresq...
So I guess if you need in-memory data, SQLite or Redis are better options.
So I guess if you need in-memory data, SQLite or Redis are better options.
There are other options for in-memory datastores on Linux though, such as Redis.
Not to trigger a debate which is better, Many corporate companies choose SQL server over MySQL or PostgreSQL.
One reason is, Paid support and excellent integration with existing Microsoft products. The features are totally worthy to use SQL server.
One reason is, Paid support and excellent integration with existing Microsoft products. The features are totally worthy to use SQL server.
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?
If you're asking this question then you shouldn't be using SQL Server.
raises hand and waves excitedly
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!
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.
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.
There are a number of similar solutions (https://flywaydb.org/), in practice I've found them all much better than database projects.
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.
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...
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...
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..
>You automatically get 3 highly-available replicas of your database for no extra cost ... All of this for $30/mo (Standard S1
Note that Standards do not have more than one replica. Only Premiums do.
Note that Standards do not have more than one replica. Only Premiums do.
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?
[1] https://azure.microsoft.com/en-us/blog/fault-tolerance-in-wi...
[1] https://azure.microsoft.com/en-us/blog/fault-tolerance-in-wi...
Basics and Standards (edit: on v12 servers) have always had a single replica. Web and Business are the ones that had three replicas.
zunzun(1)
Are you genuinely comparing by technical superiority?
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?
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?
Well, it depends on what they need.
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?
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.
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
DISTINCT ON if I recall correctly is a Postgres-specific extension, and is rather like a shortcut to an OVER WINDOW query selecting where RANK() = 1.
And I love it, but I wouldn't be expecting it to show up in MSSQL.
And I love it, but I wouldn't be expecting it to show up in MSSQL.
[deleted]
[deleted]
[deleted]
They say they have top scores on performance benchmarks but weren't those run on Windows servers? Would like to see Linux benchmarks.
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.
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.
It is time to port those legacy .net 2.0. to node. At least for me.
Thank Microsoft... good one
Thank Microsoft... good one
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).
SQL 2016 Enterprise 2-pack of Core Licenses $14,256 ($7,128 per core)
SQL 2016 Standard 2-pack of Core Licenses $3,717 ($1,859 per core)
Not cheap, but perhaps attractive compared to Oracle.
SQL 2016 Standard 2-pack of Core Licenses $3,717 ($1,859 per core)
Not cheap, but perhaps attractive compared to Oracle.
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.
People associate cost with value.
Satya may have pushed it hard
The 16.10 packages seem to be missing and I can't find where to report it.
> If Microsoft ever does applications for Linux it means I've won.
https://en.wikiquote.org/wiki/Linus_Torvalds