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.
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...
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.
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.
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.
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?...
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.
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
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.
More on this reference, the brilliant slam down of Oracle by Bryan Cantrill: https://youtu.be/-zRN7XLCRhc?t=1981
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.
Can't say I've investigated the geometry types, but at a glance they're functional (if uglier to work with).
Triggers sound strictly less powerful in SQL Server since "for each row" semantics is missing?
Meanwhile there are many PG features 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.
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?
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.
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.
So, to answer the question, to eliminate Windows server installations in a predominantly Linux environment, yes.
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.
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.
For the benefits of readers who don't know, SQL Server Standard Edition is ~$2k USD per CPU core, and Enterprise Edition is $7k.
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.
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
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.
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.
"Will you?" Of course not.
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.
https://hub.docker.com/r/microsoft/mssql-server-linux/
So no real dockerfile
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.
https://blogs.technet.microsoft.com/dataplatforminsider/2017...
One thing I'm not so clear with , however, is linux support for sql server clients. More specifically Django?
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.
https://www.pg-versus-ms.com/ says it all.
Plus, the MS docs are now on Github and we can submit PRs to correct and expand things.
If you need hosting, I've heard good things about Digital Ocean, Vultr, and Linode. You'd have to install the database yourself though.
If you sign up for a 'Developer Program Benefit' in Microsoft, you'll get $25 every month for Azure.
Why can't you get support on Stack Overflow?