MSSQL on Linux – the details | Hacker News Reader

MSSQL on Linux – the details

adfinis-sygroup.ch

82 pointswinged58 comments

This article feels like a long paraphrasing a Microsoft blog post [1] that was already discussed on HN[2]. I'm not seeing any new insights or analysis-- just restating of the points already made on the Microsoft blog.

[1] https://blogs.technet.microsoft.com/dataplatforminsider/2016...

[2] https://news.ycombinator.com/item?id=13206438

Article implies only clients for managing SQL Server are SSMS or VS Code - for most of what you'd need to do, tools like DataGrip, Navicat, or any other solution that supports JDBC works well on most platforms.

Not forgetting HeidiSQL which although is a Windows app runs well under Wine:

http://www.heidisql.com/download.php

https://appdb.winehq.org/appview.php?iAppId=3326

IntelliJ db browser is nice as well. You can connect to all mainstream DBs including MS SQL.

Yes, DataGrip is the full blown standalone version of the database tool in their various IDEs.

user space version of the NT kernel (known as NTUM - "user mode NT")

This is pretty neat. Wonder if people could use it to run other Windows stuff on Linux

Edit: formatting

That's like an official implementation of WINE from Microsoft.

It's much more complete than Wine, it actually has the whole Windows graphics stack running, outputting over RDP ;)

I wonder if the license for that only allows running specific applications.

The PAL runner is actually locked down to run SQL Server only ;-) Trying to run another app, even reg or cmd, currently makes AppLoader crash. (AppLoader is the first .exe that is ran in the NTUM)

Interesting. It seems to be based on Microsoft's "Drawbridge" research project:

https://www.microsoft.com/en-us/research/project/drawbridge/

I wonder if there's any chance they'd allow this mechanism to be used by 3rd parties to port their Windows services to run on non-Windows platforms. I doubt it...

To me its quite amazing that in 2016 we have a fully approved & supported Windows kernel running in linux and a linux kernel running in Windows. Makes me rethink the definition of what an OS really is.

unless it's the good ol` Embrace & Extend & Extinguish. it's here now, but i'd wait and see where this goes. we're lucky we still have linus.

It's possible to come up with FUD regarding anything.

Windows subsystem for Linux does not have Linux kernel in it.

Exchange and AD would be huge. Unfortunatly Exchange needs IIS and probably an AD server.

SAMBA has had an excellent track record of joining mixed OS environment AD forests for years.

IIS already runs on Drawbridge since a while AFAIK.

We replaced all our AD servers with SAMBA4, so much more reliable and while AD/LDAP is still a nightmare of an architecture having it running on standard Linux servers means it's much easier to monitor, backup, snapshot, introspect and it far out performs windows based DCs.

Are there links to licensing costs? Are there regular client access license costs as well? So... if there are costs for licensing, then converting from postgresql to mssql is a little harder to swallow.

AFAIK, the costs and editions will be the same as in SQL 2016 (i.e. Express, Web, Standard, Enterprise).

This is correct. SQL Server licensing is not changed. The major benefit is being able to share a single infrastructural OS for application and DB - it is not uncommon to see Linux app environment and Windows DB environment in a lot of large organizations.

The secondary benefit is removing the Windows OS licensing cost.

How's performance?

Performance is fine.

Can you configure MSSQL just editing files without a GUI, Unix-style?

You've always been able to do that. Almost all configuration of MSSQL can be done via a script. I can't think of anything that you need to do on a day to day basis that can't be scripted.

yep. i've been using this https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...

Do people actually run this ?

It just barely became available as a public preview. It's not recommended for protection yet.

Any valid use cases?

No offence, was just wondering why would anybody use it...

> Any valid use cases?

I'm personally a fully competent Linux sysadmin, and an extremely incompetent Windows sysadmin.

SQL Server is great, I love .NET, and the only thing holding me back all these years has been the Windows system requirement.

I consider myself a first-rate Linux sysadmin, and a perfectly competent Windows sysadmin as well, but this is a position I can't wrap my head around. If you're willing to pay thousands of dollars for a properly-licensed SQL Server install, why do you care if it needs to run on Windows? I would normally assume you don't want the license cost of the OS, but it's a tiny fraction of the overall licensing cost.

The only thing I see MSSQL on Linux being useful for is for development and testing purposes before deployment to a local server farm or the Azure cloud. (Even then, why not just run it on your Windows dev machine?) Honest question: What's the scenario where you'd license a system to run on Linux in production? To me, this is interesting, from the virtualization standpoint, but it seems like a solution in search of a problem.

If MSSQL runs on a synology nas, then a lot of smaller companies can save a lot of money, because they wouldn't need a huge server anymore.

> Even then, why not just run it on your Windows dev machine?

A lot of devs don't understand/like windows enough to work on it. It's probably a lot easier to find *nix devs that windows devs.

You might want to use MSSQL as a database and be able to sell your product to companies that do not have Windows servers around (and no staff with experience running them). And Microsoft wants to allow you to do that so you don't start building integrations with other databases to support those clients, and then those take MSSQL sales away.

And even in companies that have Windows MSSQL on Linux might be useful, e.g. if they have a linux-centric virtualization infrastructure.

> why do you care if it needs to run on Windows? I would normally assume you don't want the license cost of the OS, but it's a tiny fraction of the overall licensing cost.

At my company we had a single Windows server for aaaaages that we kept around because of a legacy MSSQL install we needed. Only Windows box in the whole server farm. Was a bit of a pain.

Of course, MSSQL-on-Linux wouldn't necessarily have been the answer here... I'm sure we would have had to buy a new MSSQL license to run it on Linux.

Eventually we eliminated the need for it entirely, but had MSSQL-on-Linux been an option a few years ago we might have simply migrated it to Linux and paid the necessary license.

Porting existing/legacy software to cloud. Though SQL Server is just a part of the stack and you still can't fool software it's running Windows.

But it's like SQL Server on Windows Server Nano - nobody asks for it, because only full system has required tools and features.

SQL Server on Nano is hard to install properly - MS didn't think about how to install SQLS on Windows without a GUI. Docker is the only usable way that doesn't take hours.

Because SQL Server is actually an excellent database, fast, reliable and full-featured. Consider that Postgres only got parallel queries a few months ago that SQL Server has had for 15 years! And in terms of replication, clustering etc SQL Server is still well ahead of PG. Now don't get me wrong I am a huge PG fan, but burying your head in the sand "because it's Microsoft" is unprofessional.

MSSQL still doesn't have replication on Linux. Here's one data scientists hillarious take on the difference between MSSQL and Postgres: https://www.pg-versus-ms.com/

MSSQL is in the very new process of actually becoming available on Linux. Give'em a bit of time at least.

I'm rather biased, I'll freely admit that. Having deployed some number of Django on PG solutions (some of which deliver moderate req/s) I will gravitate there just on experience alone.

That's actually about MSSQL 2014. 2016 is a substantially different beast.

I'm a big fan of PG and PG is good enough that it can compare favorably to MSSQL on its own merits, but that article reads more as a screed against commercial software than an objective assessment of PG's strengths relative to MSSQL. As an example, a big advantage of MSSQL for data analysis applications is its column store indexes which the author dismisses as something he doesn't use.

Now that .NET is multiplatform and various versions of Visual Studio are also available to Linux/macOS, you can actually think about doing "Microsoft development" on these platforms. As SQL Server is quite common in the Windows shops, this makes it possible to run that one on local development machines as well (for example via Docker).

Anyone using MSSQL that doesn't want to keep spending on windows licenses.

Forewarning: This likely sounds like a shill post. I'm okay with that. I work for a Microsoft partner and we're tightly integrated with the MS sellers for a number of products. I am trying to provide an answer to the use cases.

A fully integrated data platform suite with several tools that are best in class. SQL Server is not just a relational database engine, though that is at the core of most tools and services.

SQL Server is a SKU that includes many things:

    * Relational engine
        * In-memory OLTP (SQL compiles to native code)
        * Columnstore indices for OLAP workloads
        * The two above can be combined for real time analytics
    * Embedded R engine in DB
    * SSIS - data processing orchestration tool AND ETL tool
    * SSRS - traditional reporting platform (with native mobile reports)
    * SSAS - best in class OLAP engine/semantic layer
    * MDS - Master Data Services (basically a rules engine)
    * DQS - Data Quality Services (pluggable back end for data quality needs - batch or real time)

I do think SSAS is truly best in class. The others are all solid products, some better than most competitors, some middle of the road. You can certainly find equivalent open source products or vendor products and roll your own. But SQL Server as a SKU is not competing with your Postgres server. It's competing with damn near everything that touches data at your company.

> It's competing with damn near everything that touches data at your company. MSSQL for data processing? Really? I think only huge legacy projects still using it. We're using Hadoop with some C++/Hardware acceleration.

Really? Because I see Microsoft revenue continuing to increase and the partner ecosystem continuing to thrive. I also get to see licensing agreements and I get to see what sales quotas are in the MS data platform.

None of the numbers indicate a shrinking market to me. SQL Server does compete for analytics workloads. Is it a direct competitor to Hadoop? No.

I have clearly stated my bubble up front and I'm trying to keep my claims within that bubble. The question was "what are the use cases?" I answered that. I am now saying that I see a thriving SQL Server business. I'm not saying no one uses Hadoop because more of my projects leverage SQL Server components than Hadoop. You are claiming that SQL Server is only for legacy projects because you are not using it.

As others have noted, SQL Server is a very powerful platform. I only really know about it from the perspective of a web/app programmer, but that's only the tip of the iceberg.

To answer your question directly...

Don't think of it from the perspective of "green field" development where you're building something from scratch.

While "green field" development is every dev's dream, the overwhelming majority of dev work involves maintaining and integrating with existing services. There are lots of MSSQL installations out there, after all.

On another note, I know soooo many developers for whom "SQL" is synonymous with "MySQL." MySQL is okay, but they should know that Postgres and MSSQL offer a lot of programmer-friendly things that make working with them a heck of a lot more fun than MySQL. Different index types, data types, window functions, CTEs, etc.

Microsoft is misfiring on another major computing trend. If you can't fork the code, you are missing an important feature.

"It's astonishing but true - Microsoft really has figured out how to package software for Linux. \o/"

It is not very difficult to create linux packages, writing microsoft has figured it out makes it sound as if its rocket science. It also sounds as if these packages are for 'linux' but the ones mentioned in the article are specifically for CentOS linux.

And as far as i understand they only released packages for CentOS/REHL and Ubuntu. Not all linux distributions.

I notice the installation-example is broken:

   sudo curl ... > /path/to/local/file

That'll run the `curl` command with privileges, but the existing shell will handle the redirection, so writing beneath /etc, as in the example, will fail.

The simpler approach is:

   curl https://horrid.site.sh/ | sudo tee /etc/blah/path

Thanks a lot! Just fixed that line.

MS SQL Server database is a very old codebase. It dates back to Sybase SQL Server database (1980). Microsoft licensed and forked Sybase SQL in late 1980s and early 1990s and branded t as MS SQL Server. The strategic manoeuvre to get this database and basically got rid of a major vendor is not uncommon for MSFT: https://en.wikipedia.org/wiki/History_of_Microsoft_SQL_Serve... and additional background info: https://en.wikipedia.org/wiki/Ashton-Tate . Little has changed in the comming years, MS SQL kept the old SQL syntax for decades. Some interesting improvements came from the WinFS and XML teams (like full text search and refactoring to allow leaner editions - at one point it was planned to ship SQL Server (desktop ed) with Vista as part of WinFS update). Only recently with SQL Server 2014/2016 the very old and crufty syntax got some improvements. Or things like international date formats - remember it's a codebase from he 1980s, be happy with four non-standard non-ISO formats - things are getting better, but parts of the codebase slept for like 20 years with little love.

On Linux there are Oracle, DB2, Postgres, MySQL, ... and in near future MSSQL too. Depending on the use case, choose wisely. There are many nice features in all of these databases, and several little documented dark edges that you better know about.

You're misinformed about the nature of the SQL Server codebase. While it was originally licensed from Sybase, all parts of it have been completely re-worked. I read somewhere that they celebrated "zero Sybase day" or something to that effect in the early 2000s to mark the retirement of the last of the original Sybase code. Some more info here: https://blogs.msdn.microsoft.com/euanga/2006/01/19/sql-mythb...

As for its performance and features, I have run PostgreSQL, MySQL, and SQL Server in production on large workloads and am a big fan of PostgreSQL. That said, I've encountered the fewest sharp edges and disappointments with SQL Server. It is a very respectable RDBMS and it's great that it's now available on Linux.

The main issue I have seen with SQL Server was a constant deadlock (every two weeks or so) over a table with high writes (peak at 800k per sec), which was annoying because the fail-back to the replica affected the DNS entries and meant 5-15min of outage.

But to be fair it was an example of the wrong tool used badly in the wrong situation deployed in a poor environment.

Instead of accusing me, maybe look at yourself. I correctly mentioned that it started as a fork of Sybase, and if you would have spent a minute with my first link, it's clearly mentioned that they refactored the codebase from C to C++ they completed with v2005. Of course there are still many parts in there for various reasons dating back more than 20 years, but it's now maintainable, refactored and internally documented. It's like Internet Explorer which started as a Mosaic fork, and there are still behaviours in IE11 and even in Edge (which started as fork of Trident from IE11) that date back to mid 1990s. Or with Half Life 2 and other Valve's Source-Engine video games - it all started from a Quake 1 engine fork, and you can still spot behaviours and there is still old stuff from ID software in crucial parts, even after like 20 years of refactoring.

And yes, it's good to have a choice. Some software is designed to run in combination with MSSQL. So it's good that they ported it to Linux.

Reply on news.ycombinator.com