Ask HN: Would you consider running SQL Server on Linux?

[james_anderson]

SQL Server 2017 has been announced and the big feature is cross platform.

I'm wondering how many Linux people out there would consider running SQL Server on Linux in a production environment. If not, why not?

I can guess some of the reasons people will site but I expect there are many more.

[jasode]

If I put on my "CIO" hat, I would say "no".

An observation from several decades of the software business is the concept of the "reference platform". For MS SQL Server, the reference platform is Windows, not Linux.

This means that if development for the different operating systems gets out of sync, it will be the Linux version that lags instead of Windows.

If I have an urgent crisis with a database down and need to call MS tech support[1], their troubleshooting team will be way more familiar with Windows than Linux.

It's really a bad idea to deploy on a non-reference platform. For playing around and experiments, that's ok. However, for deployment on production environments, it's a risk.

The "reference platform" for MS Windows NT was x86, not MIPS or DEC. Therefore, we didn't deploy Windows apps to MIPS machines. (That was a good decision since MIPS, DEC, and Itanium versions of Windows got cancelled.) Solaris reference platform was their SPARC computers and not Intel x86. Yes, Solaris had a version ported to x86. That was ok to play with at home but we wouldn't deploy any commercial production apps with it.

Same for dev environments. If you're using C#, your life will be easier if you go with Windows instead of "C# NET Core" on Linux. If you're programming Ocaml, stick with Linux instead of trying to cobble together Cygwin+MinGW to deploy Ocaml apps on Windows.

Try to stay with the reference platforms to minimize headaches.

[1] https://www.microsoft.com/en-us/microsoftservices/support.as...

[tjoff]

One point to add, the reference platform is going to be much better tested and battle proven both in-house and in practice.

[manigandham]

Support is support, Microsoft is very comfortable with Linux and uses a lot internally already. They're also partners with Redhat which is what most enterprises will use anyway.

MSSQL also runs on a abstraction layer since v2005 so there's no inherent problem with running on Linux. Also containers change the entire scenario.

[jasode]

>MSSQL also runs on a abstraction layer since v2005 so there's no inherent problem with running on Linux.

Yes, one would think an extra abstraction layer would render platforms exactly equal but that's not the real-world outcome. For example, Windows Server also runs on an "abstraction layer" called HAL (Hardware Abstraction Layer) and yet we had production problems with Windows on Itanium that had specific knowledge base articles and hotfixes for Itanium.

Did Microsoft premier support help us with Itanium problems?!? Yes, they were responsive and eventually solved some obscure bugs. But their support for Intel x86 was more complete and up-to-date. Likewise, I anticipate that Microsoft will be competent and helpful with customers running SQL Server on Linux. But I don't expect their specialists to have the same level of expertise as MSSQL on Windows.

If someone wants to run MS SQL Server on Linux, that's fine but don't think the enterprise platforms are equal. That usually doesn't turn out to be the case.

>Also containers change the entire scenario.

I'd be totally comfortable with MSSQL on Linux for dev/sandbox/QA environments (avoids licensing costs of Windows). But not for mission-critical production deployment.

[james_anderson]

That is a worry. I guess it could depend on the uptake of SQL Server on Linux though.

[petraeus]

This. You always want to stay as close to standard as possible. Cowboy technology and too clever by a half solutions have no place in enterprise.

[teilo]

Yes, absolutely, because of Microsoft's licensing model for HyperV.

Windows Datacenter Edition is not cost effective for us, and that means we only run two Windows instances per host. By moving MSSQL to Linux, we can free up a Windows Server Standard license for other purposes.

You can run as many non-Windows instances on HyperV as your want, but you can only run 2 Windows instances unless you pay to license every physical core on the server a second or third time, even if you are not using them for Windows (for example, if you wanted to run a third Windows instance on a host.) This means we have additional hardware capacity that we use to run Linux instances next to the 2 allowed Windows instances.

[way0utwest]

Be careful here. I suspect that licensing terms may change a bit with this.

However, you are mistaken. Windows Server standard allows 2 VMs (plus the host). Datacenter allows unlimited VMs. If someone told you otherwise, that's incorrect: http://www.microsoftvolumelicensing.com/ProductResults.aspx?...

[teilo]

No. The licensing terms changed from per socket to per core. Other than that, the rules are the same. If you virtualize Windows Server, you must pay for core licenses for every physical core of the bare metal, regardless of which hypervisor you are using (HyperV, ESX, Xen, etc.). You may then run 2 Windows Server VMs on that host. If you want to run a 3rd, you must relicense every physical core on the machine a second time. You can then run up to 4 Windows Server VMs on that host.

The licensing terms you linked to are for Windows Server VMs. When the licensing documents refer to an "OSE," they are referring to a Windows Server Operating System Environment. They are not referring to other OSs you may be running on the same host (remember, the host can by any hypervisor). Non-Windows VMs are not relevant to Windows licensing, even if you are using HyperV to host them.

The only difference between using HyperV and other hypervisors is: IF you are installing the HyperV role on a Windows Server, and only using it to manage HyperV, you don't have to count that bare-metal Windows Server against your license count, provided you are running Windows Server VMs on that host.

Remember, there is also HyperV Server, which is console-only, and free, but fully functional, and managed just like any other HyperV server. It is a slightly scaled down version of Window Core. You can run as many non-Windows VMs on that as you want, without paying a cent, or pay for Windows VMs according to the above rules.

[horusthecat]

I would consider using it because:

1. I work in a backwards enterprise shop where "no one ever got fired for choosing Microsoft" and it would be easier (read as "possible) to get IT here to support it than Postgres. (That said, they do support Mongo internally but won't admit that to software teams)

2. It's much cheaper than Oracle, which is the other "enterprise" database where it's easier to get an install done

3. It's not Oracle

[131012]

Can you elaborate on point 3? If the argument is ideological, I get it. I'm more interested in the technical argument.

[cwyers]

Oracle is a fine enough database (it really is), but it costs an exceptional amount of money and (I haven't touched it in a long time so I may be out of date on this, but I don't think I am) the developer tool experience is not great. Oracle SQL Developer is ugly and slow compared to SQL Server Management Studio. Basically, if you've used both Oracle and SQL Server you can tell which company had their CEO on stage shouting "developers developers developers" and which is run by the lawnmower.

[pritambaral]

> and which is run by the lawnmower.

More on this reference, the brilliant slam down of Oracle by Bryan Cantrill: https://youtu.be/-zRN7XLCRhc?t=1981

[neilsimp1]

> Oracle SQL Developer is ugly and slow compared to SQL Server Management Studio

This cannot be said enough. I've recently changed positions to a team using Oracle, coming from MSSQL, and dammit if I don't hate SqlDeveloper.

[protomyth]

There is another argument for or against adoption of Oracle and that is a basic business argument. Oracle is expensive and their virtualization costs are well above Microsoft.

[dotancohen]

Not the OP, but licensing comes to mind. MSSQL licensing and Windows Server licensing are a pain, especially in virtualized environments. But Oracle? The word predatory - complete with claws and fangs - comes to mind.

[mindcrime]

No, almost certainly not. Why would I use MSSQL in the first place, when I could just use PostgreSQL? The main reason I could see to use MSSQL would be "you're in a Windows shop already", and if that isn't the case, I don't see much reason for it.

[manigandham]

MSSQL is fast. It's seamless to use with .NET code and it has several features like columnstores, in-memory tables and graph processing that are very useful. Also availability groups are much better than Postgres solutions for high availability today.

[james_anderson]

I really like AGs but I'm not sure I will love them on Linux. It still feels like it's early days for SQL Server's HA on Linux.

[manigandham]

That's true, although I'm looking forward to a far easier AG setup without Windows Clustering now.

[arethuza]

In my experience, the main reason why most organizations run things like SQL Server or indeed Oracle is because they want to run applications that depend on those particular databases.

[lmm]

Sure - but it would be weird for an application to depend on SQL server and not depend on Windows, and if you're going to be running Windows for some of your servers anyway it's easier to use the same platform for all of them.

[beefhash]

There are some .NET Core programs that also depend on SQL Server but not Windows necessarily. Bitwarden[1] comes to mind.

[1] https://help.bitwarden.com/article/install-on-premise/

[hungerstrike]

I would have said the same thing if I had never used MSSQL.

PG just doesn't have the same quality SQL-dialect or tools as MSSQL though. Some very basic facilities are missing from PG like snapshot and transaction logs, batched triggers and being able to return multiple resultsets from a single trip to the database server.

Even the free tools for SQL Server are better than anything you can get for PG and there's nothing like SQL Server Data Tools.

[snuxoll]

> PG just doesn't have the same quality SQL-dialect or tools as MSSQL though.

T-SQL sucks, I don't know how on earth you get the feel of "quality" from it. Every time I have to work with it I cry, pl/pgsql is a hell of a lot nicer and I can feel comfortable stating that as an objective.

As far as GUI tooling? Ya got me there, even as terrible as auto-complete is in SSMS it's leaps and bounds better than PGAdmin or DataGrip for pointy-clicky DBA tasks - though I've just learned to use the system tables/views in PostgreSQL like I have MSSQL because I can't stand pointy-clicky stuff.

> Some very basic facilities are missing from PG like snapshot and transaction logs

What? Snapshot isolation is available by default with PostgreSQL, it ONLY uses MVCC for transaction isolation. You want snapshot, set your transaction isolation level to REPEATABLE READ. And transaction logs? What on earth do you mean? Transaction logs are fundamental to PostgreSQL like they are any other RDBMS, you can ship them off for backups, do PITR with them, etc - just like you can SQL server.

> batched triggers

Yup, that's true.

> being able to return multiple resultsets from a single trip to the database server.

Technically doable with stored functions in PostgreSQL, though ill-advised. SQL Server makes this easier if you need it, for sure.

> there's nothing like SQL Server Data Tools.

Yup, although this is really down to a matter of preference. SQL Server Data Tools is great for warehousing projects, but for application databases using the database migration tools provided by your framework of choice work just fine.

And hey, I've got respect for MS SQL Server, it's a well-performing, well-supported enterprise SQL database - not to mention SQL Server Analysis Services kicks some serious ass for business intelligence use-cases (especially considering it's included right in the package, and with the new PowerBI-derived Reporting Services you get a full BI package for a really good deal). But let's make sure we're all educated on tools before we start nitpicking them, eh?

[Neckmeats]

How about some features that really matter for an enterprise... compare the differences of HA options between PostgreSQL and SQL Server. Or maybe something a little more simple like point in time recovery. Backup and recovery is probably the most important feature of an enterprise RDBMS, and I'm sorry, but PostgreSQL doesn't cut it. It's really fun to develop on, but it's an absolute nightmare to administer. That makes it a liability.

[anarazel]

Think there's plenty of missing tooling around postgres. But what are you lacking in backup with http://www.pgbackrest.org/ ? Or is it just that it's a separate package?

[Neckmeats]

The point I was making is the lack of easy to use native features to perform typical administrative operations. When all is said and done, you may end up with a multitude of 3rd party tools to achieve feature parity with SQL Server , which is the particular comparison, and would be a limiting factor to implementing the platform in an enterprise that cannot withstand down time or tight RPO/RTO requirements.

In regards to SQL Server on Linux, I have just spun up (for testing) a three node AlwaysOn cluster with Pacemaker/Corosync, which is working quite well. Failover works as expected, however it is a tad slower than a Windows Failover Cluster. First impressions are very good and I would not hesitate to recommend the solution over a PostgreSQL or MySQL implementation. It is far less complicated and provides excellent HA and recoverability options.

[fulafel]

You can return multiple result sets using a sproc or a view.

Triggers sound strictly less powerful in SQL Server since "for each row" semantics is missing?

Meanwhile there are many PG features missing.

[snuxoll]

> Triggers sound strictly less powerful in SQL Server since "for each row" semantics is missing?

Triggers in MS SQL are batched, you get "for each row" semantics by iterating over the contents of the virtual "inserted/updated/deleted" tables. This is honestly a pretty sane design choice, it helps trigger performance immensely when dealing with bulk data manipulation since you can do things like JOIN's on the whole set of triggered data allowing the execution engine to do one index scan or hash join instead of a bunch of index lookups on individual rows. Ironically, after doing salesforce.com development for a while this is one of the things I really wish PostgreSQL offered.

[fish_fan]

How is the JSON integration and geometry primitives? I had no idea it was competitive with postgres.

[snuxoll]

"JSON" in SQL server is a set of functions layered on top of NVARCHAR(*) columns, so - about where PostgreSQL was when they first introduced the 'json' data-type.

Can't say I've investigated the geometry types, but at a glance they're functional (if uglier to work with).

[jenkstom]

Then again, T-SQL is a developer's nightmare. Especially when compared to PostgreSQL's version of SQL scripting. And when you add on all the supported languages it makes MSSQL quite painful from a SQL developer standpoint.

[Neckmeats]

This sounds like more of a religious issue than anything. All of the dialects of SQL have pros and cons, but if you stick as close to the ANSI standard as possible, the differences are negligible. If today's developers actually cared about relational theory, and spent the time to do it right, they wouldn't really care much of the difference between T-SQL or any other dialect. Like when developers bring up a platform preference based on JSON data type support... you're doing it wrong to begin with.

[snuxoll]

Would I? Yes, I would. We're primarily a .Net / MSSQL shop, our DBA's are all familiar with MSSQL and all but a handful of our applications (internally developed or purchased from a vendor) use it.

We just build a giant new MSSQL cluster using AlwaysOn, configuration management with Windows Server Failover Clustering (which AlwaysOn is built upon for some low level functionality) is a HUGE pain in the butt. If SQL Server 2017 was GA at the time I would have highly suggested we just go with RHEL because Pacemaker/Corosync is a lot easier to deal with.

Out of personal preference, I like PostgreSQL a lot better - but when your business already runs MSSQL having options for deployment outside of Windows Server is a welcome change.

[james_anderson]

As good as Pacemaker is I don't really like the look of using it to achieve HA for SQL Server. Hopefully this will be better in 2018.

[gjvc]

In a previous job we had a central master MSSQL database and several replicas of this database globally. This was all run on Windows (obviously) Were I still there, and were this arrangement still in effect, I would certainly be interested in migrating the installations on Windows to installations on CentOS, if only to make the server fleet that much more homogenous.

So, to answer the question, to eliminate Windows server installations in a predominantly Linux environment, yes.

[james_anderson]

Cool, I've seen this mentioned as a potential reason, so it's good to hear of a real-world example

[thinkMOAR]

Just for my curiosity, do you work for microsoft?

[james_anderson]

No, but I am predominantly a SQL Server DBA. I thought people might think that after I posted.

[manigandham]

Yes. .NET based startup, everything else is already .net core (containers + kubernetes on GKE) so we only have Windows for MSSQL.

Looking at migrating to Postgres but MSSQL in containers sounds great so far. Features are good with both databases but built-in graph, columnstore, hekaton, better backups, etc in MSSQL are strong temptations.

[AnIdiotOnTheNet]

No. It won't save us on licensing costs and it won't be more secure, so the only reason I can see that it exists is for cloud hosting, and my organization has no interest in that.

[dozzie]

> If not, why not?

First most important question to answer: in what way MS SQL Server would be better than PostgreSQL? Fields to consider:

- Easier to install? (apt-get install postgresql-server)

- Easier to use? (initiating the Sybase client library used to be terrible) (EDIT: it was installing the client library, not initiating it)

- Easier to manage? (Postgres is excellent in this field)

- Has important functions or features that PostgreSQL doesn't?

- Works significantly faster than PostgreSQL?

Going out of one's way to use third party software needs to give some benefits to offset the cost of doing so.

[james_anderson]

Yeah, I agree. Each has the odd thing the other doesn't but it is hard to justify the cost of SQL Server over Postgres

[BrentOzar]

> but it is hard to justify the cost of SQL Server over Postgres

For the benefits of readers who don't know, SQL Server Standard Edition is ~$2k USD per CPU core, and Enterprise Edition is $7k.

[snuxoll]

EnterpriseDB is something like $1700/core/year, which targets the same market - "enterprise" shops who want a vendors neck to wring when something breaks.

Honestly, SQL Server is priced very well - even enterprise edition given the features it provides. The biggest problem I have with it is T-SQL being terrible compared to pl/pgsql and the lack of developer-centric features like arrays, composite types, and a lot of other QoL features postgres has that MSSQL doesn't.

If you're going to deploy SQL Server, being able to do it on a platform that doesn't make me as a sysadmin/DevOps Engineer want to strangle myself with cluster configuration nightmares is a welcome change.

[arethuza]

What if I want to run an application that doesn't support Postgres?

[statictype]

Probably not. We use SQL Server for our product running on AWS and we tested it with the Linux edition and it works fine.

But what’s the advantage of moving it over to Linux. It’s anyway sitting on a dedicated box and Windows Server hosting charges are only marginally more expensive than Linux on AWS(depending on the AZ you use).

So it’s cool and works well but there’s no compelling reason for us to move to it yet. At least not one that I could see

[james_anderson]

Do those charges include the cost of Windows licenses?

[polskibus]

Does it have analysis services and Integration services yet? Last time it I checked it didn't. It's a big differentiator against open source stacks. If it was available on Linux then perhaps some of our clients would consider moving to Linux based SQL server.

[way0utwest]

SSIS, yes: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-...

SSAS, not yet, though few people use this and I don't know I'd recommend it on the same box. Is that a reason to move SSIS/SQL Engine to Linux and have SSAS on Windows? You'd have to decide.

[polskibus]

The reason is to get out of Windows licensing that is a mess, if you are trying to offer something on a cloud, outside Azure. In theory and in some interpretation, Windows Authentication (the only accepted auth mechanism by SSAS) requires all direct and indirect users to have a Windows Server CAL (!).

[NDizzle]

My organization is not, because our SQL Server DBAs with 20 years of experience are linux newbies.

[severino]

No, I won't. We use SQL Server only in some legacy projects, but newer ones are using Postgres or Oracle, running Linux.

We know open-source RDBMS like Postgres, and the ones developed by Unix/Linux vendors like Oracle, are unlikely to drop Linux support anytime soon. But can we say the same about MSSQL? Today we got this CEO... but tomorrow?

It's a risk we're not willing to take, specially when there's no SQL Server feature that we cannot find elsewhere.

[kurinj]

If SQL Server is what the clients/users required, sure. I'd much rather run it on a Linux server because that's what I'm familiar with.

[peterwwillis]

"Would you?" Of course.

"Will you?" Of course not.

[slackfan]

I actually have been running SQL Server for linux for the past few months.

In a docker container, no less.

This is entirely for testing a part of our application that needs to connect to legacy mssql servers. The server is spun up, databases are created programmatically, tests are run, and then the container is destroyed.

As for running this in production, ahahaha no.

[coding123]

I was in this thread looking for this reply. If nothing else, the ability to run code on your Mac laptop running MSSQL server in docker is a huge win, even for what may later be a Windows deployment of MSSQL server. I definitely see the use-case for automating QA workloads and running tests against a real MSSQL server on what is otherwise a fully Linux based build system. The cheapest way to run MSSQL server (TCO which includes the human cost) is still likely Windows, but the docker-izing of MSSQL opens up soo many doors now.

That all being said, I would expect that there are also companies moving to use MSSQL in Linux for production as well, but personally, I would wait a couple of years to hear other's experiences.

[slackfan]

Yeah, it's pretty great. We evaluated spinning up RDS MSsql instances for our testing, and found the time that it took to spin up one of those to be extremely prohibitive for use with CI pipelines, I mean something to the tune of 20 minutes for the instance to come up. Whereas with docker and mssql-linux, we only have to (at most) have a 10-15 second delay before the container is fully online. Definitely great for devs to run their local tests against, and great for me to be able to do some minor admining via the CLI tools provided in the container.

[james_anderson]

I've been doing similar stuff with docker in my CI pipelines. So are you testing your app on Linux SQL Servers and then deploying to Windows SQL Servers? Should be OK...

[slackfan]

Data is data, and a stored proc should behave the same across OSes. Otherwise Microsoft is doing a shitty job developing mssql. Here's a hint, they aren't.

[flachsechs]

so is your apprehension because of sql server on linux, or because of docker, or because of the combination of the two? this post is snarky but lacks real information.

[slackfan]

Because if we were to migrate off legacy systems we'd go with postgres.

[lochii]

I'd love to see your dockerfile, would you consider sharing it?

[slackfan]

We just roll the default

https://hub.docker.com/r/microsoft/mssql-server-linux/

So no real dockerfile

[gjvc]

Link to the announcement:

https://blogs.technet.microsoft.com/dataplatforminsider/2017...

[cafard]

Not really. We have a number of SQL Server databases where I am, for a number of reasons. Several are for Microsoft Dynamics. How does that work with Linux? One is for print-management system, which I suspect has hooks into Windows.

[sheraz]

Probably Not for my use cases. If I really wanted sql server then I most likely will consider azure's hosted sql offerings.

One thing I'm not so clear with , however, is linux support for sql server clients. More specifically Django?

[way0utwest]

What support are you looking for? SQL Server on Linux, is for the most part, SQL Server. Django support for SQL Server should be roughly the same on Windows and Linux, they're just client apps.

All my testing, which is inherently limited to the things I look at, show that apps seamlessly port over from Windows to Linux, unless they're doing something in the file system, or have complexities with SQL Agent and other subsystems. Maybe of the Agent limitations have been removed, I just haven't retested.

MS supports Linux the same as Windows, with lots of their systems people having spent the last year+ learning about the differences between Windows and Linux.

Are they all going to be experts on client interactions? No, but they should be able to provide support in most cases.

[Beltiras]

Why would I invite that headache when I can just install PostgreSQL instead?

https://www.pg-versus-ms.com/ says it all.

[scoj]

The certificate has expired, Chrome gives a nice ugly warning. But thanks for the link, I'm curious.

[discreditable]

Another poorly configured Let's Encrypt client. :(

[beefhash]

I just sent an e-mail about that to the person running the site, assuming the e-mail address in the top right is relevant.

[Beltiras]

I sent the author an email. It's just a plain HTML page behind the expired SSL.

[Spivak]

Because you have to buy your PGS support from a 3rd party partner rather than from the vendor.

[olavgg]

No, because PostgreSQL has much better documentation.

[way0utwest]

Perhaps. There are tons of SQL Server sites and articles to provide information and details the docs lack.

Plus, the MS docs are now on Github and we can submit PRs to correct and expand things.

[james_anderson]

That's a new reason, I like it. Books Online has got better over the years to be fair to it.

[grin1dan]

I am new to this and would appreciate an update on where to find a 'how to' for doing this. Thanks very much!

[kelukelugames]

Related question. Where can i run and host a SQL db for free? Or for cheap. It's for a hobby project.

[actuallyalys]

For a hobby project, you can probably use whatever is running the rest of your project. Sqlite in particular is lightweight.

If you need hosting, I've heard good things about Digital Ocean, Vultr, and Linode. You'd have to install the database yourself though.

[equasar]

I don't know a free service but you can run Azure SQL for less than $5 a month.

If you sign up for a 'Developer Program Benefit' in Microsoft, you'll get $25 every month for Azure.

[fmariluis]

Heroku Postgres has a free plan (10k rows): https://www.heroku.com/postgres

[dotancohen]

Depends on the hobby. You can run one on your desktop, or if you are using a web host they most likely include database as part of the plan.

[taf2]

Nope

[igtztorrero]

Nuts

[kapauldo]

I would not consider it today because today it's closed and expensive. I can't get support on stack overflow for example. In a few years I might consider it.

[Garvey]

https://stackoverflow.com/questions/tagged/sql-server+linux

Why can't you get support on Stack Overflow?

[_jezell_]

Depends if Microsoft would consider open sourcing it so it can catch up to everyone else.