13 ms·

Making SQL Server run on Linux

334 points by kogir 10y ago

rokosbasilisk 10y ago
What are the advantages of even using sql server when I can use postgres far more easily on linux ?
- iRobbery 10y ago
  Depends who you ask, for you probably not that much. For mickeysoft however... new territory to sell licenses to at some point. [edit] https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing https://www.microsoft.com/en-us/sql-server/sql-server-2016-p... It would have been nice if some were able to explain why the above reasoning of selling licenses is incorrect? There is no other reason for MS to do this besides money.
  - tedunangst 10y ago
    Maybe next time try without saying "mickeysoft".
  - tracker1 10y ago
    Licensing is probably a big reason... integration and migration to azure is probably another reason... MS pricing for SQL is pretty competitive in terms of what cloud offerings are available. I wish their compute would come down a bit across the board though. I keep thinking it would be cool to see an interactive graph of connection latency, and throughput for 1k, 1mb, 1gb xfers between the major data centers for the big cloud providers. If I could get compute from DO/Linode and use services from Azure and have under 20ms latency for data queries, I might use Azure for services, and Linode or DO for compute. I know that's too much for some things, but may be work the cost in latency for many use cases... saving a few hundred a month on web/service vm's and leverage Azure beyond that. (as for downvotes, probably mickeysoft)
- icebraining 10y ago
  Some discussion here: https://news.ycombinator.com/item?id=9464505 https://news.ycombinator.com/item?id=9464505
  - rokosbasilisk 10y ago
    thanks! The sql server integration services seem interesting.
    - danzig13 10y ago
      I have used it recently. The main thing I like is that it organizes code visually as workflows and it has many pre-packaged "tasks" you can use. This means I didn't have to come up with some structure for 900 script files. I did run into several annoying errors when trying to import from excel.
  - thomasz 10y ago
    That's one of the cases where the HN discussion is really great, while the original article is rather bad.
- callinyouin 10y ago
  For you personally there probably are no advantages. For large organizations that are already heavily invested in SQL Server, however, there may be benefit in being able to migrate off of Windows, especially if most of the rest of the server infrastructure is already Linux based.
- tuwtuwtuw 10y ago
  PostgreSQL has features MSSQL does not have, and vice versa. PG is still very lacking when it comes to multi-node clusters for high availability for example.
- thomasz 10y ago
  Better tooling a better query optimizer and more reassuring "enterprise support", if that's your kind of kink.
- teilo 10y ago
  OLAP for one. Initial support for basic OLAP conventions was introduced in 9.5 (grouping sets, rollups, cubes, upserts), but there's still a lot missing (like nested aggregations), and a lot of performance tuning that needs to be done. We cannot run our BI on Postgres or we would. But with Microsoft's latest work, even products like Oracle Hyperion can run against SQL Server running on Linux.
  - timClicks 10y ago
    You almost certainly already know this, but PostgreSQL behaves very differently depending on its config parameters. We've been able to get decent OLAP performance at least to the dozens of TB scale. Very interested to hear how to made the decision to use MS SQL and the test cases that drove that decision.
    - teilo 10y ago
      Timing. Postgres 9.5 was not on the horizon when we were building out our solution. Lacking OLAP support, the query complexity to achieve similar functionality would have been very cumbersome, error-prone, and non-performant (excessive use of unions, massive case clauses, lots of repetition, inscrutable window functions - a mess). Further, our finance team later insisted on using Oracle EssBase, where Postgres in any version will never be an option.
    - coderzach 10y ago
      Not OP, but for us, the reasons we switched from postgres to sql server were the "clustered columnstore indexes" and query parallelization. Before switching to sql server, we spent a ton of time optimizing query performance with "clever" use of indexes, ctes, etc. With sql server, we've been able to just use the columnstore indexes and write simple, straightforward queries that run in ~200ms vs 10-20 seconds in postgres. And before you ask, we spent A TON if time and money on configuration, paying for multiple postgres experts to help with optimization.
      - raarts 10y ago
        What version(s) of PostgreSQL were you using?
- dogma1138 10y ago
  Be able to use the 1000s of commercial products that need MSSQL. Encryption is easier to manage and it has built in solutions for HIPPA and PCI. SQL reporting services and the entire ecosystem of apps that run on top of MSSQL.. Better clustering, ha, revision control and backup. Most likely better enterprise support, and you'll be able to port "legacy" applications that were designed with win/sql in mind without having to rewrite your entire DB access and query code.
  - rokosbasilisk 10y ago
    Wouldnt it be easier just to run sql server on windows at that point to take advantage of the ms ecosystem ? Edit: I am not familar with much of anything microsoft related.
    - UnoriginalGuy 10y ago
      You'd never run MS SQL Server and these other servers on the same machine either way, so you could easily run SQL Server on Linux and Reporting Services on Windows Server. Although if you're virtualizing Windows Server, it may be effectively free to run an additional Windows Server instance (since you pay per core regardless); but if your hypervisor's resources are already saturated then you could take SQL Server off of it and put it onto a inexpensive Linux Server.
    - Deinumite 10y ago
      Maybe but this also gives you more hosting options if you are in the cloud. Also tools like puppet and chef for example have an easier time provisioning Unix like machines than Windows (although this has been changing, and the situation could be much better I haven't dealt with it for a bit). Also you don't have to deal with licenses for Windows server instances which can be another huge painpoint.
    - dogma1138 10y ago
      There are quite a few products that require a Linux host for the main app server and a MSSQL or Oracle DB. Oracle support is being dropped left and right MSSQL is there because it was the defacto enterprise DB for ages and it also has a free version via MSSQL express. And lastly having more options is never a bad thing.
- sitharus 10y ago
  From a development perspective clustered indexes and SSDT are the best features. Clustered indexes vastly improve query performance on tables where you often want the data by something other than insert order. SSDT lets you version your database in a really easy way and generates change scripts on demand.
  - snuxoll 10y ago
    You can optionally cluster a table based on index order with PostgreSQL, simply create the index and then `ALTER TABLE foo CLUSTER ON bar_ix`. Clustered indexes are actually an issue I run into with SQL server, because, like MySQL, the table data is always indirectly referenced by the clustered index, so any query that uses a secondary index requires a second pointer deref to find the data in the table heap. This has a benefit to insert/update performance, but I'd argue that more tables are higher on reads than mutations.
    - dx034 10y ago
      You can use INCLUDE to put some columns in the index. Doesn't always help but speeds up many queries. AFAIK Postgres doesn't have that feature, not sure about MySQL.
      - snuxoll 10y ago
        Using INCLUDE comes at the cost of bloating your indexes making scans take a considerable longer time, not having to chase that extra pointer works well enough in most cases (and if you are able to benefit from Heap-Only Tuples you can even have in-place UPDATES without needing to rewrite the pointer in every index).
    - dhd415 10y ago
      SQL Server supports heaps (tables w/o clustered indexes) on which indexes point directly to the referenced row. It's nice that they allow you to choose between those and tables with clustered indexes where any secondary index references the primary key. See https://msdn.microsoft.com/en-us/library/hh213609.aspx https://msdn.microsoft.com/en-us/library/hh213609.aspx
- rodgerd 10y ago
  I pray for the day when Postgresql clustering is as good/easy as AlwaysOn Availability Groups.
  - thijsvandien 10y ago
    Paying works better than praying: https://www.postgresql.org/about/donate/ https://www.postgresql.org/about/donate/ /semi-serious :)
    - rodgerd 10y ago
      My employer spends money with EnterpriseDB so we've got that covered 8)
- gigatexal 10y ago
  Parallel queries baked in from long back. I think Postgres got this just recently.
- nimchimpsky 10y ago
  We did a performe comparison on handling Geo data. SQL server came out on top.
  - Demiurge 10y ago
    In what way? I can't even tell if you mean features or performance. PostGIS is unmatched as far as features, as far as I know.
    - dx034 10y ago
      Don't know if the comment was edited, but it says performance comparison? (If I interpret the typo correctly)
      - nimchimpsky 10y ago
        it wasn't edited
    - dagw 10y ago
      While it is true the SQL Server is missing a fair number of features compared to PostGIS (and Oracle Spatial), I've always found that the spatial performance of SQL Server is been really great for the common features it does support.
      - Demiurge 10y ago
        Ok, thanks, that makes it worth checking out!
    - nimchimpsky 10y ago
      I said "performe" a typo from my phone, you really didn't know that I meant performance ? Queries returned results faster in sql server, after checking we had set everything up optimally in everything.
  - tracker1 10y ago
    That's one area, where at the time (about 4 years ago), Mongo worked out really well for me... iirc there was a bug I experienced with ElasticSearch, and RethinkDB didn't have geo indexing yet. It doesn't surprise me though... MSSQL tends to do very well out of the box for most workloads.
- tresil 10y ago
  I love Postgres, but lack of parallel query processing (same query leveraging multiple threads) has been a deal-breaker for some projects. Thankfully, Postgres is finally starting to make some progress here. Also of note, In November MS added a number of previously enterprise-only features to all versions of SQL Server 2016 (Service Pack 1 and later). Examples: In-memory OLTP, ColumnStore indexes, are available even in the free Express edition now. Native Postgres, to my knowledge, doesn't have comparables for these yet. https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-serv...
  - tracker1 10y ago
    I've always found MS-SQL pretty impressive in terms of features/value, though they get pricey pretty quickly, I've always found it nicer to work with than Oracle or DB2 by comparison... Although, I do wish they'd support the more common LIMIT syntax that many other DBs use. That said, from a devops perspective MSSQL doesn't have any real peers. Some of the analytical tooling around MSSQL is pretty good too. Have had to track performance issues in the past. Even the general query analyzer integrated with Management Studio is great. I do hope they consider going the electron route for cross-platform management studio, it's worked really well for VS Code, and can imagine it would be a great fit for this as well. Even if quite a bit outside their current tooling.
  - dspillett 10y ago
    > I love Postgres, but lack of parallel query processing ... for some projects. It has surprisingly little effect on many workloads though - then engine is fairly conservative on when it will use it too (and there are easy ways for you to accidentally make it not consider parallelism). For many workloads you are often better off using the many CPU cores to run different queries than cooperate on a smaller number. Data warehousing and "big data" are a place where this isn't the case though, and other features MS SQL Server has over PG and others shine here too: the column store indexes, in-memory processing, and so forth. And you get these in the free "express" editions too, as of 2016sp1, though that is only really useful for small projects and prototypes due to other limitations in that edition (10Gb/database, max 4 CPU cores & 1.5Gb RAM put to use per instance) so you'll be paying for standard edition at least for real work.
- bsg75 10y ago
  My common answer to this is query parallelism. For those of us in the DWH/OLAP space it's a big deal that the F/OSS engines are just starting to tackle, and it's not an easy problem to solve. I might not mind paying for MSSQL if I also did also not have to pay for Windows.
  - hashhar 10y ago
    Quite a lot of enterprise features made it into the free SQL Server Express this winter. [1]: https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-serv...
    - dspillett 10y ago
      Other limitations (10Gb/database, max 4 CPU cores & 1.5Gb RAM put to use per instance) mean Express is only useful for small projects and/or prototypes though - you'll be paying for standard edition for real work. Those previously enterprise-only features being in standard is a huge and convenient change too though. It is worth noting that developer edition is also free these days (as of 2016RTM IIRC) so for prototyping work (unless you need to support older versions) you are better off with that than express as it has always basically been the full enterprise edition.
- lafar6502 10y ago
  Well using Postgres on Windows is just as easy as on Linux. Yet still SQL server is the king :)
- known 10y ago
  https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Fundamental_features https://en.wikipedia.org/wiki/Comparison_of_relational_datab...
- gaius 10y ago
  Consider that PG only just got fairly rudimentary parallel queries in 9.6 this year, SQL Server had it in (IIRC) 2000. For DW and general DSS workloads that is significant.
- koffiezet 10y ago
  It will probably allow me to eliminate some Windows VM's that are strictly there to provide some enterpricy Java apps with an MSSQL database.
quickben 10y ago
Anybody looked into migration to the Linux platform? Realistically, what would be the advantages/cost?
- lisivka 10y ago
  MicroSoft Linux is still not released, so wait till it will be.
- kabdib 10y ago
  Not having to run Windows is a big factor. (Just so you know: I run a lot of Windows servers, so I'm not being knee-jerk snarky. This is a real cost).
  - teilo 10y ago
    Yes, and the cost savings go well beyond a Windows license + SA. Being able to leverage an existing toolchain for configuration management and deployment is a huge cost savings, given that the Windows alternatives are both expensive, more difficult to use, and break a lot more (IMHO).
    - Scuds 10y ago
      apt install mssql-server https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-... and for the people doing docker https://hub.docker.com/r/microsoft/mssql-server-linux/ https://hub.docker.com/r/microsoft/mssql-server-linux/
      - teilo 10y ago
        I was thinking more of Ansible, but yeah. I've done SQL Server installs on Windows Core, and it is painful. Aside from the obscure PowerShell commands, you have to write a config file - just to get the damn thing installed. Rubbish. (I hear it's gotten better in 2016 though).
  - keithnz 10y ago
grabcocque 10y ago
That's some seriously cool tech. The great thing from Microsoft's perspective is not needing to maintain two completely separate codebases, beyond the SQLPAL and the linux host extenstion, which are fairly small in comparison. Also, by having the SQLPAL be directly descended from the Windows SQLOS should mean that Windows and Linux SQL Server have similar performance, and they're not paying a significant overhead that's typical of platform emulation. Good shit.
- rplst8 10y ago
  > The great thing from Microsoft's perspective is not needing to maintain two completely separate codebases... This is a great thing for customers too. The systems will work very similarly and less code means fewer bugs and security risks.
deleted 10y ago
[deleted]
alistproducer2 10y ago
Here's a little lighter reading from ars on the subject http://arstechnica.com/information-technology/2016/12/how-an-old-drawbridge-helped-microsoft-bring-sql-server-to-linux/ http://arstechnica.com/information-technology/2016/12/how-an...
johansch 10y ago
"Because of SQL Server’s long history on a single operating system, Windows, ..." It did come out of the UNIX-based Sybase SQL Server, though: https://en.wikipedia.org/wiki/Sybase https://en.wikipedia.org/wiki/Sybase I wonder if there was an OS abstraction layer at that time (Sybase ported it to OS/2 together with Microsoft first) that was then thrown away by Microsoft after they licensed the source code and went on their own?
- usloth_wandows 10y ago
  Microsoft remade SQL Server and optimized for Win after they bought it
- dspillett 10y ago
  There was a fairly large amount of reenginerring work around the 20000/2005 era, and a significant number of smaller but significant feature additions/changes since, and I doubt that non-windows compatibility was considered at any point during those developments.
stuartd 10y ago
So SQLPAL (great name) is in the current SQL Server for Windows? It's quite an engineering achievement, though you could hopefully automate a lot of the actual abstraction, and I bet the testers were on overtime. I'd be interested to know the timeline, is any version of SQLPAL in SQL Server 2016? Or is it SQL.Next?
rbanffy 10y ago
The article mentions win32. I remember it from the Window NT 3.1 days. Is it still used? Is the "32" in the name there because someone thought that 32 bits should be enough for anyone?
- chungy 10y ago
  It's merely the name of the API, even on 64-bit Windows, where it ends up being a misnomer :-)
known 10y ago
SQLPAL = https://en.wikipedia.org/wiki/Wine_%28software%29 https://en.wikipedia.org/wiki/Wine_%28software%29
kaushiks 10y ago
Slightly tangential, and while I know nothing about the implementation details of the SQLPAL, the SSCLI (and later CLR/CoreCLR) took the same approach to port Windows based code to Unixes [1] and makes for interesting code reading. For e.g. The SSCLI PAL's implementation of SEH [2]. [1] https://github.com/lewischeng-ms/sscli/tree/master/pal/unix https://github.com/lewischeng-ms/sscli/tree/master/pal/unix [2] https://github.com/lewischeng-ms/sscli/blob/adbd6bbad0816f65650a66e1701112a307dc4b09/clr/src/inc/palclr.h#L176 https://github.com/lewischeng-ms/sscli/blob/adbd6bbad0816f65...
xedarius 10y ago
Feels exactly like how they abstracted DirectX from direct graphics driver access, which coincidentally was called HAL.
- lafar6502 10y ago
  Not only graphics, HAL in Windows NT era was (as the name says) a set of common abstractions for talking to different hardware in standardized way
biehl 10y ago
Are there any plans for SQL Server to run on MacOS?
- dhd415 10y ago
  I've not heard of any. I would imagine that the real value for Microsoft is in expanding the number of server platforms on which SQL Server can run and MacOS doesn't really fall into that category.
- jpalomaki 10y ago
  On docker: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-docker https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...

334 points by kogir 10y ago

rokosbasilisk 10y ago

What are the advantages of even using sql server when I can use postgres far more easily on linux ?

iRobbery 10y ago
Depends who you ask, for you probably not that much. For mickeysoft however... new territory to sell licenses to at some point. [edit] https://www.microsoft.com/en-us/sql-server/sql-server-2016-pricing https://www.microsoft.com/en-us/sql-server/sql-server-2016-p... It would have been nice if some were able to explain why the above reasoning of selling licenses is incorrect? There is no other reason for MS to do this besides money.
- tedunangst 10y ago
  Maybe next time try without saying "mickeysoft".
- tracker1 10y ago
  Licensing is probably a big reason... integration and migration to azure is probably another reason... MS pricing for SQL is pretty competitive in terms of what cloud offerings are available. I wish their compute would come down a bit across the board though. I keep thinking it would be cool to see an interactive graph of connection latency, and throughput for 1k, 1mb, 1gb xfers between the major data centers for the big cloud providers. If I could get compute from DO/Linode and use services from Azure and have under 20ms latency for data queries, I might use Azure for services, and Linode or DO for compute. I know that's too much for some things, but may be work the cost in latency for many use cases... saving a few hundred a month on web/service vm's and leverage Azure beyond that. (as for downvotes, probably mickeysoft)
icebraining 10y ago
Some discussion here: https://news.ycombinator.com/item?id=9464505 https://news.ycombinator.com/item?id=9464505
- rokosbasilisk 10y ago
  thanks! The sql server integration services seem interesting.
  - danzig13 10y ago
    I have used it recently. The main thing I like is that it organizes code visually as workflows and it has many pre-packaged "tasks" you can use. This means I didn't have to come up with some structure for 900 script files. I did run into several annoying errors when trying to import from excel.
- thomasz 10y ago
  That's one of the cases where the HN discussion is really great, while the original article is rather bad.
callinyouin 10y ago
For you personally there probably are no advantages. For large organizations that are already heavily invested in SQL Server, however, there may be benefit in being able to migrate off of Windows, especially if most of the rest of the server infrastructure is already Linux based.
tuwtuwtuw 10y ago
PostgreSQL has features MSSQL does not have, and vice versa. PG is still very lacking when it comes to multi-node clusters for high availability for example.
thomasz 10y ago
Better tooling a better query optimizer and more reassuring "enterprise support", if that's your kind of kink.
teilo 10y ago
OLAP for one. Initial support for basic OLAP conventions was introduced in 9.5 (grouping sets, rollups, cubes, upserts), but there's still a lot missing (like nested aggregations), and a lot of performance tuning that needs to be done. We cannot run our BI on Postgres or we would. But with Microsoft's latest work, even products like Oracle Hyperion can run against SQL Server running on Linux.
- timClicks 10y ago
  You almost certainly already know this, but PostgreSQL behaves very differently depending on its config parameters. We've been able to get decent OLAP performance at least to the dozens of TB scale. Very interested to hear how to made the decision to use MS SQL and the test cases that drove that decision.
  - teilo 10y ago
    Timing. Postgres 9.5 was not on the horizon when we were building out our solution. Lacking OLAP support, the query complexity to achieve similar functionality would have been very cumbersome, error-prone, and non-performant (excessive use of unions, massive case clauses, lots of repetition, inscrutable window functions - a mess). Further, our finance team later insisted on using Oracle EssBase, where Postgres in any version will never be an option.
  - coderzach 10y ago
    Not OP, but for us, the reasons we switched from postgres to sql server were the "clustered columnstore indexes" and query parallelization. Before switching to sql server, we spent a ton of time optimizing query performance with "clever" use of indexes, ctes, etc. With sql server, we've been able to just use the columnstore indexes and write simple, straightforward queries that run in ~200ms vs 10-20 seconds in postgres. And before you ask, we spent A TON if time and money on configuration, paying for multiple postgres experts to help with optimization.
    - raarts 10y ago
      What version(s) of PostgreSQL were you using?
dogma1138 10y ago
Be able to use the 1000s of commercial products that need MSSQL. Encryption is easier to manage and it has built in solutions for HIPPA and PCI. SQL reporting services and the entire ecosystem of apps that run on top of MSSQL.. Better clustering, ha, revision control and backup. Most likely better enterprise support, and you'll be able to port "legacy" applications that were designed with win/sql in mind without having to rewrite your entire DB access and query code.
- rokosbasilisk 10y ago
  Wouldnt it be easier just to run sql server on windows at that point to take advantage of the ms ecosystem ? Edit: I am not familar with much of anything microsoft related.
  - UnoriginalGuy 10y ago
    You'd never run MS SQL Server and these other servers on the same machine either way, so you could easily run SQL Server on Linux and Reporting Services on Windows Server. Although if you're virtualizing Windows Server, it may be effectively free to run an additional Windows Server instance (since you pay per core regardless); but if your hypervisor's resources are already saturated then you could take SQL Server off of it and put it onto a inexpensive Linux Server.
  - Deinumite 10y ago
    Maybe but this also gives you more hosting options if you are in the cloud. Also tools like puppet and chef for example have an easier time provisioning Unix like machines than Windows (although this has been changing, and the situation could be much better I haven't dealt with it for a bit). Also you don't have to deal with licenses for Windows server instances which can be another huge painpoint.
  - dogma1138 10y ago
    There are quite a few products that require a Linux host for the main app server and a MSSQL or Oracle DB. Oracle support is being dropped left and right MSSQL is there because it was the defacto enterprise DB for ages and it also has a free version via MSSQL express. And lastly having more options is never a bad thing.
sitharus 10y ago
From a development perspective clustered indexes and SSDT are the best features. Clustered indexes vastly improve query performance on tables where you often want the data by something other than insert order. SSDT lets you version your database in a really easy way and generates change scripts on demand.
- snuxoll 10y ago
  You can optionally cluster a table based on index order with PostgreSQL, simply create the index and then `ALTER TABLE foo CLUSTER ON bar_ix`. Clustered indexes are actually an issue I run into with SQL server, because, like MySQL, the table data is always indirectly referenced by the clustered index, so any query that uses a secondary index requires a second pointer deref to find the data in the table heap. This has a benefit to insert/update performance, but I'd argue that more tables are higher on reads than mutations.
  - dx034 10y ago
    You can use INCLUDE to put some columns in the index. Doesn't always help but speeds up many queries. AFAIK Postgres doesn't have that feature, not sure about MySQL.
    - snuxoll 10y ago
      Using INCLUDE comes at the cost of bloating your indexes making scans take a considerable longer time, not having to chase that extra pointer works well enough in most cases (and if you are able to benefit from Heap-Only Tuples you can even have in-place UPDATES without needing to rewrite the pointer in every index).
  - dhd415 10y ago
    SQL Server supports heaps (tables w/o clustered indexes) on which indexes point directly to the referenced row. It's nice that they allow you to choose between those and tables with clustered indexes where any secondary index references the primary key. See https://msdn.microsoft.com/en-us/library/hh213609.aspx https://msdn.microsoft.com/en-us/library/hh213609.aspx
rodgerd 10y ago
I pray for the day when Postgresql clustering is as good/easy as AlwaysOn Availability Groups.
- thijsvandien 10y ago
  Paying works better than praying: https://www.postgresql.org/about/donate/ https://www.postgresql.org/about/donate/ /semi-serious :)
  - rodgerd 10y ago
    My employer spends money with EnterpriseDB so we've got that covered 8)
gigatexal 10y ago
Parallel queries baked in from long back. I think Postgres got this just recently.
nimchimpsky 10y ago
We did a performe comparison on handling Geo data. SQL server came out on top.
- Demiurge 10y ago
  In what way? I can't even tell if you mean features or performance. PostGIS is unmatched as far as features, as far as I know.
  - dx034 10y ago
    Don't know if the comment was edited, but it says performance comparison? (If I interpret the typo correctly)
    - nimchimpsky 10y ago
      it wasn't edited
  - dagw 10y ago
    While it is true the SQL Server is missing a fair number of features compared to PostGIS (and Oracle Spatial), I've always found that the spatial performance of SQL Server is been really great for the common features it does support.
    - Demiurge 10y ago
      Ok, thanks, that makes it worth checking out!
  - nimchimpsky 10y ago
    I said "performe" a typo from my phone, you really didn't know that I meant performance ? Queries returned results faster in sql server, after checking we had set everything up optimally in everything.
- tracker1 10y ago
  That's one area, where at the time (about 4 years ago), Mongo worked out really well for me... iirc there was a bug I experienced with ElasticSearch, and RethinkDB didn't have geo indexing yet. It doesn't surprise me though... MSSQL tends to do very well out of the box for most workloads.
tresil 10y ago
I love Postgres, but lack of parallel query processing (same query leveraging multiple threads) has been a deal-breaker for some projects. Thankfully, Postgres is finally starting to make some progress here. Also of note, In November MS added a number of previously enterprise-only features to all versions of SQL Server 2016 (Service Pack 1 and later). Examples: In-memory OLTP, ColumnStore indexes, are available even in the free Express edition now. Native Postgres, to my knowledge, doesn't have comparables for these yet. https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-serv...
- tracker1 10y ago
  I've always found MS-SQL pretty impressive in terms of features/value, though they get pricey pretty quickly, I've always found it nicer to work with than Oracle or DB2 by comparison... Although, I do wish they'd support the more common LIMIT syntax that many other DBs use. That said, from a devops perspective MSSQL doesn't have any real peers. Some of the analytical tooling around MSSQL is pretty good too. Have had to track performance issues in the past. Even the general query analyzer integrated with Management Studio is great. I do hope they consider going the electron route for cross-platform management studio, it's worked really well for VS Code, and can imagine it would be a great fit for this as well. Even if quite a bit outside their current tooling.
- dspillett 10y ago
  > I love Postgres, but lack of parallel query processing ... for some projects. It has surprisingly little effect on many workloads though - then engine is fairly conservative on when it will use it too (and there are easy ways for you to accidentally make it not consider parallelism). For many workloads you are often better off using the many CPU cores to run different queries than cooperate on a smaller number. Data warehousing and "big data" are a place where this isn't the case though, and other features MS SQL Server has over PG and others shine here too: the column store indexes, in-memory processing, and so forth. And you get these in the free "express" editions too, as of 2016sp1, though that is only really useful for small projects and prototypes due to other limitations in that edition (10Gb/database, max 4 CPU cores & 1.5Gb RAM put to use per instance) so you'll be paying for standard edition at least for real work.
bsg75 10y ago
My common answer to this is query parallelism. For those of us in the DWH/OLAP space it's a big deal that the F/OSS engines are just starting to tackle, and it's not an easy problem to solve. I might not mind paying for MSSQL if I also did also not have to pay for Windows.
- hashhar 10y ago
  Quite a lot of enterprise features made it into the free SQL Server Express this winter. [1]: https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/ https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-serv...
  - dspillett 10y ago
    Other limitations (10Gb/database, max 4 CPU cores & 1.5Gb RAM put to use per instance) mean Express is only useful for small projects and/or prototypes though - you'll be paying for standard edition for real work. Those previously enterprise-only features being in standard is a huge and convenient change too though. It is worth noting that developer edition is also free these days (as of 2016RTM IIRC) so for prototyping work (unless you need to support older versions) you are better off with that than express as it has always basically been the full enterprise edition.
lafar6502 10y ago
Well using Postgres on Windows is just as easy as on Linux. Yet still SQL server is the king :)
known 10y ago
https://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Fundamental_features https://en.wikipedia.org/wiki/Comparison_of_relational_datab...
gaius 10y ago
Consider that PG only just got fairly rudimentary parallel queries in 9.6 this year, SQL Server had it in (IIRC) 2000. For DW and general DSS workloads that is significant.
koffiezet 10y ago
It will probably allow me to eliminate some Windows VM's that are strictly there to provide some enterpricy Java apps with an MSSQL database.

quickben 10y ago

Anybody looked into migration to the Linux platform? Realistically, what would be the advantages/cost?

lisivka 10y ago
MicroSoft Linux is still not released, so wait till it will be.
kabdib 10y ago
Not having to run Windows is a big factor. (Just so you know: I run a lot of Windows servers, so I'm not being knee-jerk snarky. This is a real cost).
- teilo 10y ago
  Yes, and the cost savings go well beyond a Windows license + SA. Being able to leverage an existing toolchain for configuration management and deployment is a huge cost savings, given that the Windows alternatives are both expensive, more difficult to use, and break a lot more (IMHO).
  - Scuds 10y ago
    apt install mssql-server https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-ubuntu https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-... and for the people doing docker https://hub.docker.com/r/microsoft/mssql-server-linux/ https://hub.docker.com/r/microsoft/mssql-server-linux/
    - teilo 10y ago
      I was thinking more of Ansible, but yeah. I've done SQL Server installs on Windows Core, and it is painful. Aside from the obscure PowerShell commands, you have to write a config file - just to get the damn thing installed. Rubbish. (I hear it's gotten better in 2016 though).
- keithnz 10y ago

grabcocque 10y ago

That's some seriously cool tech. The great thing from Microsoft's perspective is not needing to maintain two completely separate codebases, beyond the SQLPAL and the linux host extenstion, which are fairly small in comparison. Also, by having the SQLPAL be directly descended from the Windows SQLOS should mean that Windows and Linux SQL Server have similar performance, and they're not paying a significant overhead that's typical of platform emulation. Good shit.

rplst8 10y ago
> The great thing from Microsoft's perspective is not needing to maintain two completely separate codebases... This is a great thing for customers too. The systems will work very similarly and less code means fewer bugs and security risks.

deleted 10y ago

[deleted]

alistproducer2 10y ago

Here's a little lighter reading from ars on the subject http://arstechnica.com/information-technology/2016/12/how-an-old-drawbridge-helped-microsoft-bring-sql-server-to-linux/ http://arstechnica.com/information-technology/2016/12/how-an...

johansch 10y ago

"Because of SQL Server’s long history on a single operating system, Windows, ..." It did come out of the UNIX-based Sybase SQL Server, though: https://en.wikipedia.org/wiki/Sybase https://en.wikipedia.org/wiki/Sybase I wonder if there was an OS abstraction layer at that time (Sybase ported it to OS/2 together with Microsoft first) that was then thrown away by Microsoft after they licensed the source code and went on their own?

usloth_wandows 10y ago
Microsoft remade SQL Server and optimized for Win after they bought it
dspillett 10y ago
There was a fairly large amount of reenginerring work around the 20000/2005 era, and a significant number of smaller but significant feature additions/changes since, and I doubt that non-windows compatibility was considered at any point during those developments.

stuartd 10y ago

So SQLPAL (great name) is in the current SQL Server for Windows? It's quite an engineering achievement, though you could hopefully automate a lot of the actual abstraction, and I bet the testers were on overtime. I'd be interested to know the timeline, is any version of SQLPAL in SQL Server 2016? Or is it SQL.Next?

rbanffy 10y ago

The article mentions win32. I remember it from the Window NT 3.1 days. Is it still used? Is the "32" in the name there because someone thought that 32 bits should be enough for anyone?

chungy 10y ago
It's merely the name of the API, even on 64-bit Windows, where it ends up being a misnomer :-)

known 10y ago

SQLPAL = https://en.wikipedia.org/wiki/Wine_%28software%29 https://en.wikipedia.org/wiki/Wine_%28software%29

kaushiks 10y ago

Slightly tangential, and while I know nothing about the implementation details of the SQLPAL, the SSCLI (and later CLR/CoreCLR) took the same approach to port Windows based code to Unixes [1] and makes for interesting code reading. For e.g. The SSCLI PAL's implementation of SEH [2]. [1] https://github.com/lewischeng-ms/sscli/tree/master/pal/unix https://github.com/lewischeng-ms/sscli/tree/master/pal/unix [2] https://github.com/lewischeng-ms/sscli/blob/adbd6bbad0816f65650a66e1701112a307dc4b09/clr/src/inc/palclr.h#L176 https://github.com/lewischeng-ms/sscli/blob/adbd6bbad0816f65...

xedarius 10y ago

Feels exactly like how they abstracted DirectX from direct graphics driver access, which coincidentally was called HAL.

lafar6502 10y ago
Not only graphics, HAL in Windows NT era was (as the name says) a set of common abstractions for talking to different hardware in standardized way

biehl 10y ago

Are there any plans for SQL Server to run on MacOS?

dhd415 10y ago
I've not heard of any. I would imagine that the real value for Microsoft is in expanding the number of server platforms on which SQL Server can run and MacOS doesn't really fall into that category.
jpalomaki 10y ago
On docker: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-setup-docker https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...