SQL Server on Linux public preview
blogs.technet.microsoft.com
blogs.technet.microsoft.com
> If Microsoft ever does applications for Linux it means I've won.
I'd say the simple fact that an enterprise Microsoft product is running natively on Linux is a pretty big win. I guess hell has frozen over.
[1] - https://code.visualstudio.com
[2] - https://github.com/PowerShell/PowerShell
Also, hilarious that they offer visual studio in not just RPM and DEB but tar-ball as well, never mind 32-bit versions. Thats much more thorough than certain web browsers these days...
I should note that Django isn't planning to add more backends to the core project, and has actually discussed moving some into separate packages. But that doesn't mean a backend couldn't be developed by the Organisation.
Of course, don't expect either of them to be open source. Which means an open source ORM backend that depends on them can't be in an official Debian/Ubuntu/RH repository.
Officially, Debian contrib is not Debian, but there is no practical difference.
Now that Microsoft's JDBC driver is open source and on Maven Central I might switch to it, though thankfully MSSQL is starting to lose favor in my team as we become more familiar with PostgreSQL.
So while SQL server is unavailable, free client drivers will hopefully begin popping up more and more and will become accessible through e.g. Debian repositories, with time.
Add another to the list. I've had to resort to using hs-odbc who's forced to use freetds, meaning there are weird issues like this:
https://github.com/hdbc/hdbc-odbc/issues/17
I did find one pure haskell mssql implementation and was trying to use it here:
https://github.com/hdbc/hdbc-odbc/issues/17
Oh cool, it looks like it has commits over the past few weeks!
Also Microsoft are deprecating the old bindings and are creating new ones I believe will be open source. I might be off slightly.
I would strongly recommend you check SqlServer license terms first; the last thing you want is to make any of your downloaders liable for license payments as soon as they start the image. I really don't think what you suggest is possible, because you would be redistributing SqlServer - maybe with the Express version, but that was pretty crippled last time I checked.
You can connect to SQL Server from Windows, Linux and Mac using Python, Node.js, PHP, Java and C#. This includes integration with frameworks like Django, Laravel, Entity Frameworks, Hibernate etc.
wow
I've worked with all major RDBMS systems at one point or another and SQLS is by far the nicest.
The ease of use also makes it suitable for smaller shops as well. At the lowest level you install it, and it just works, and it's back by a great tooling ecosystem, and excellent documentation. It really does stand in absolutely stark contrast to, for example, Oracle.
I wouldn't call it idiot-proof, but it's certainly friendlier than most RDBMS. (That's not to say there aren't issues and annoyances with it, mind, but that's true of any product.)
Beyond this, if you need a relational store and you really, really don't want to have to give a monkey's about it, I'd say SQL Azure is probably the way to go.
(Saying all of this I recognise that lots of people will be able to tell war stories about the torrid time they had with SQL Server, or for any other data store. My experience has largely been it's a winner though.)
Then I went to MySQL because my startup couldn't afford a database and it was a pretty large step down.
Then at the next startup, I got used to PostgreSQL and by now I'd have a pretty hard time to not being able to use JSONB, window functions or CTEs - made me realize how bad most other DB systems are by now. That is, except for a really nice GUI for PostgreSQL I'm a pretty happy camper by now.
How has SQL Server kept up in the meantime?
The biggest advantage IMO is that you can get strong performance out of SQL Server with relatively little DBA knowledge compared with Oracle. Installation is easy, integrated authentication is easy, and backups are easy. Running a cluster and doing replication is an area that all RDBMS systems need serious improvement on, but initial setup and working with a relatively stagnant schema is straightforward.
The one area that SQL Server has been lacking is having a native linux client (which is hopefully alleviated now). Not having one is the only real drawback that I run into.
Contrast this with some of the NoSQL platforms where immediately you're forced to think about topics such as sharding and clustering (I'm looking at you Couchbase) and the pain in the ass factor is much lower.
Key point: you don't need much of a clue about SQL Server administration to start using it, but you perhaps do with Couchbase, Elasticsearch, etc. From an ease of use point of view with NoSQL, I'd probably point to MongoDB as having the most approachable learning curve.
There's also no BS about having to keep everything in memory to keep performance acceptable (I'm looking at you, again, Couchbase - that's two strikes). The whole point of SQL Server and other RDBMSs is that they continue to work well when the amount of data you need to store vastly outweighs the amount of physical memory you have. Of course, if you can get everything into physical memory it will perform better but, even if you can't, it will still perform well.
Of course, if you're using SQL Server you do need to learn SQL (true for any RDBMS), which can be off-putting. It's absolutely not a beautiful language - in fact it's bloody aggravating when you write a lot of it - but it is extremely powerful and excellent for working with sets of data arranged in rows (i.e., tables). Nothing else really comes close.
1) Lack of deferred constraints. To me it's natural to think that database constraints shouldn't apply while you're shuffling things around during a transaction. Other databases support this, apparently
2) Lack of multiple cascade paths. Why should I have to implement this functionality in triggers?
3) Why can't you create a column in the middle of a table? I know it has something to do with the column order being tied to the actual physical storage order, but why are they connected in the first place? Just show me the columns in the order I specify, I don't give a damn what order they're actually stored in (or should I?)...
EF will deal with much of this for you because it uses "empty" rows so FKs references can always be satisfied during a transaction, but this obviously slows things down.
With (2) I'd question what you're doing. I'm not saying absolutely don't use them, or that what you're doing is "wrong", but TRIGGERs often result in side-effects that cause problems further down the line. They can be OK for auditing (but consider other options such as event sourcing if you have this kind of requirement), but the moment you start putting business logic in them, or hanging it off data modified by a trigger, you've started down a potentially dark path. The reason for this is that suddenly DML operations have side effects that may not be anticipated by people working with the database in future. In fact, depending upon how permissions are set for developers, they may not even be aware that triggers exist, leading to unexpected results, "weird" performance problems, etc. I've run across this recently in a moderately sized client database: ~500 tables, ~2500 stored procedures, and a few dozen magic triggers that most people are unaware of, which manifested themselves when we collected execution plans for some query tuning ("Where's all this stuff coming from? ... Oh.").
With (3) I'd just be interested in hearing your use case.
Some other massive improvements are Columnstore indexes for absolutely ridiculous OLAP data reads and compression (we are seeing 90% compression and < 1 sec instant lookup times on multi-billion-row fact tables).
No master/master - that's the only thing I would consider missing compared to Oracle. It scales up, but not out. Peer to peer replication technically is master/master, but calling that clunky would be a kindness.
I second that. I am not a big fan of Windows, but SQL Server has not given me the slightest trouble so far, except in cases where I was kind of asking for it.
What about supported applications? Most apps I've worked with (eg: django) support MSSQL as a second-class citizen, while other (eg: postgres) are much better supported.
The problem has generally been the license and the environment. Any framework wanting to support MSSQL needed to have MS Windows and MSSQL licences for CI (+developers), plus the infrastructure to run windows on the CI (I've no idea how you'd do that, so there's also a knowledge barrier).
Hopefully this will change now.
And Oracle support contracts (which are basically mandatory) are really expensive.
Now, the one thing I've heard from everyone is that the SQL Server tooling is beyond belief, and I believe it. If there is one weakness in the open source RDBMS world it's tooling. With such as large and obvious gap how is it that no one has filled it yet? Will no one pay for tooling? Are there tools available but the quality is not there? Seems like a good candidate for someone to fill a niche and possible make a successful business.
Microsoft has just been polishing the integrated set of tools with SQL Server for a long time, especially with GUI tools that make the job of administering SQL Server more accessible for a different class of user (e.g. the ones who don't compile their own kernels and spend all day in a shell).
i do hope thought they do provide it and i really hope they provide SSAS and SSIS development tools on linux
But getting the features of these tools (cubes/OLAP = SSAS, reports = SSRS, ETL = SSIS) for other databases such as Postgres is possible with open source BI tools.
Open source BI platforms like Pentaho, BIRT, Jaspersoft (now owned by TIBCO) roll together families of different open source tools to give the same type of functionality for any DB platform. For example, things like Jaspersoft use the Mondrian OLAP cube engine underneath.
And also, with all the newer engines for data processing (Apache Spark, Hadoop, Ignite, etc.) you also have a lot of new backend options for analytical/report/BI processing also.
That's a pretty big deal. (Also not sure what the ORM has to do with it... Unless it's just the fact that ORMs dumb down the queries that are possible)
I haven't seen this anywhere else in this thread, so here's The Register saying that the Linux version would be lacking some features compared to the Windows version, though it doesn't mention what those might be.
http://www.theregister.co.uk/2016/03/11/sql_server_linux_201...
It doesn't surprise me; I can imagine there's quite a bit of stuff in the server software, as a whole, that is dependent on native OS integration. I'll be interesting to see a matchup once it starts getting reviewed.
Balmer was jumping across the stage yelling Developers, Developers, Developers! Now someone in Redmond is executing and pushing the applications and tools platforms. It will be interesting to see where this leaves Windows in the medium term.
We see the 85%/15% split in programming and developing as well. When something is just too good to pass up, a lot of us switch over and start working on the next hotness. Sometimes, those things switch and become the embedded tech you have to learn to get in there. Arduino is one such thing. As is C, and Linux. For DB's, it used to be MySQL, and now PostgreSQL, and large heapings of good/bad for MongoDB and Redis.
Where MS lost for a long time, was the insane amount of bad will towards protocol obfuscation, protocol "extensions" that break IETF protocols, and of course the monopolist behaviors that they were found guilty for.
The problem is they ran away people who'd develop for their platform, and make crazy awesome things. Can't say I blame them. I'm one who ran, and really haven't looked back. And recently, we had a "party" at work, because I was able to wipe the last 2 windows boxes we had. To Linux.
Active directory is not a separate product like the others it is very much a core part of Windows Server. Unless you think AAA parts of an OS are a separate product from the OS, AD is very much tied to Windows.
You could turn AD into just another identity management platform, but you would pretty much lose everything that makes AD awesome. AD is impressive because it is so integral and closely tied to Windows. It begins to lose its lustre when you integrate other platforms because when you lose that close coupling, there's nothing but a distributed user authentication store.
I'd make some long argument about the Linux market longing for an easy-to-use directory server, but I see that Novell Directory Services still lives on as NetIQ, and I've never heard of anyone using it, so maybe the market just isn't there. (Another victim of Microsoft's monopoly.) So maybe it doesn't matter that AD isn't platform independent. I guess companies running a lot of Linux are happy to hassle with the nightmare that is OpenLDAP.
Which is aggravating when you want to setup a test environment.
Also, what would happen (if anything) if the Linux results crushed the Windows ones? Would that be embarrassing to Microsoft? And to take the thought to the next level of paranoia, let's say Microsoft already ran those benchmarks in-house and found the Linux version vastly faster. To avoid embarrassment, would they slow it down to be more in line with the Windows version?
Here's a high level overview: http://arstechnica.com/information-technology/2016/12/how-an...
That will have made an impact. For example, if thread creation is faster on an OS, starting threads for smaller tasks becomes a win; if locking primitives are slower, it may be better to take a full-table lock more often.
IIRC fibers were added to the NT kernel specifically for SQLS.
https://appdb.winehq.org/objectManager.php?sClass=applicatio...
...but it's unfortunately not even installable.
Sybase 11.0.3.3 for Linux was made free for production use somewhere around 2002. It is still useful for some applications, if you can find the binaries, http://froebe.net/blog/2013/03/10/howto-installing-and-runni...
Microsoft SQL Server has since diverged to the point of being a completely different codebase, whereas Sybase stagnated and were acquired by SAP.
I've always thought that Microsoft's operating systems were the Albatross around their neck. Their apps and systems are OK. Having those available on a superior OS, like Linux would be good for the world, and MS.
Buying canonical would probably be the quickest way.
So easy for some ;) Not to not applaud their efforts though.
* Open Sourcing it is probably unrealistic at this point.
Now if they can just fix windows, I might start using that too. Maybe WindowsX? I do enjoy using VSCode on Mac!
One reason is, Paid support and excellent integration with existing Microsoft products. The features are totally worthy to use SQL server.
Old school client server database applications is still very much alive.
I love Postgres and have been using it for a long time. However if you need good support for, for example, self-updating efficient materialized views, then Postgres won't really do (yet).
If I would venture for a new product without definitive advantage, one needs better "googlability", which seems still in much favor to MySQL.
Besides that, you have tools that works fine with MySQL and people who are used to using it which also needs conversion.
I'd like to know how you would convince any average MySQL users the switch?
Do they write a lot of queries? The breadth and depth of SQL features MSSQL supports blows MySQL out of the water. I work mostly with MySQL now, and it's painful to go back. For example, I really like windowing functions and table-valued functions. But there's a lot, lot more.
Do they want much better query performance? MSSQL again.
Are they a business type? They will probably like stuff like SSRS, SSIS, and SSAS, and all the additional tooling around them.
There really is no comparison between MySQL and MSSQL. Postgres is great, and generally what I use if I have a choice because it's free and better in many respects than MySQL, but even there I have problems thinking of things that Postgres does better than MSSQL, though there are a few. There's just _so much_ MSSQL does and so much it does right, and the stuff it does wrong is generally getting fixed (no more XML PATH, they finally added STRING_AGG!!!).
But if they're happy with what they're got, well, it'd be silly to spend the money on an MSSQL license.
IME, MSSQL is pretty google-able - that's really how I learned it. Do you have any specific problems with its googlability?
Some features which PostgreSQL have which I believe are missing for MSSQL. This are features I use all the time in my every day work.
- Transactional DDL concurrently with snapshot isolation
- Exclusion constraints: a generalized form of unique constraints
- "Writable-CTEs": the ability to use RETURNING from an UPDATE, INSERT or DELETE in a CTE
- Regular expression support (I think fixed in 2016)
- JSON support (I think fixed in 2016)
- Many small things like lack of DISTINCT ON and array_agg
And I love it, but I wouldn't be expecting it to show up in MSSQL.
That being the case then, yes, the tooling is light-years ahead of anything else.
MS have a long history of encouraging third party developers to create tooling around their platforms to help sell those platforms, and keep people using them.
Don't know how it compares to Oracle as I've never used it.
Of course, a lot of these come from Microsoft - but there are a lot of 3rd party applications that support SQL Server. There are also a lot of advantages of standardizing on a single database platform within an organization.
Edit. I understand that it weighs against development time but that is a different matter than money in the bag.
Having used MySQL and SQL Server, there's not much of a contest. You can do everything in both, pretty much, but you can do it more quickly and more maintainably in SQL Server. The developer tools are all really good, the functionality is outstanding, it requires less care and feeding. There are plenty of cases where the added money isn't worth it. But your time has value, too.
I keep hearing about Microsoft SQL's tooling. Can someone explain to me, at length even, what "tooling" is?
This is coming from someone who has written dozens of web applications for PostgreSQL. These are business applications with complex rules and complex SQL like window functions, recursive queries, PL/pgSQL functions, and so on. The only tools I had were vim and psql, and I've been completely happy.
Every time I have had to write an application that uses Microsoft SQL (because it was already set up for a related application) I cringe, because it is a hundred times harder.
SQL Server Management tool is a great core user interface to managing database servers and databases. Other tools that are commonly used by developers include the rather awesome query planner, query profiler, performance analyser and data import and export tool.
There are lots more told available for data warehousing / OLAP etc that are not so commonly used by most developers. SQL server is undoubtedly tool rich.
So you could basically trace a production workload and then tune it offline.
Doing things like backups in SQL server using the agent, and running various workflows, also works really well. To this day, as far as I can tell, doing backups for most open source databases are a hodgepodge of bash scripts, cron jobs, and *dump executables, which every admin reinvents every single time.
These are all GUI apps that, albeit have barely been updated in a decade, but still better than most first party (or even third party) open source DB management tools out there.
I've heard all the high availability stuff is really good too but I've also heard that it's a pain to setup and until recently, only available in the really expensive enterprise license.
Agent is nice because of the surrounding infrastructure -scheduling, notifications, etc - that said, it still often ends up being a hodgepodge that every admin reinvents, just using different underlying structures (cron=scheduling, system mail=notifications, etc).
And yeah, the HA stuff is good - with the setup becoming gradually easier with each new version, but those license costs are definitely reflecting that.
While SQL Server does have some great features - it's also missing a fair bit of functionality. I.e., there's no real 'overview' of how the system is performing. Look at SQLSentry for an idea of what I mean - I only manage one 'major' database, but without that tool I'd be hard pressed to keep up. There's also things that have been broken for years now which they've failed to address - i.e., one of the most popular add-ons for SQL Studio is SQL Prompt to get actual working intellisense. They can manage it for multiple extensible languages in visual studio, but flub it for years with the fairly static TSQL. I actually thought it must have been a fairly complex problem until I saw JetBrains implement it for multiple variations of SQL in DataGrip.
I'd probably pick PostgreSQL over either of the other two today for any case where my constraint was that I had to have the application run on Linux. Otherwise, I'm going Azure SQL Database or SQL Server if on-prem (as in, non-cloud) is needed. This will change soon with the OP about SQL Server on Linux.
SQL Server Management Studio is an incredible tool and beats the pants off of equivalent free tools like MySQL Workbench and pgAdmin. (SSMS is now freely available without an MSDN subscription or SQL Server license.) Yes, it requires Windows, but compared to the other tools on Windows it is second to none.
Azure SQL Database has been nothing but a good experience to work with. You automatically get 3 highly-available replicas of your database for no extra cost, built-in transparent data encryption, query auditing support, threat detection alerts, near 100% compatibility with SQL Server, and no need to keep the underlying OS or software up to date and secure. All of this for $30/mo (Standard S1 we've found to be adequate for most small businesses with line-of-business type apps.) Depending on the needs of the application, Azure SQL Database can either be a good replacement for an existing SQL Server instance, or a stepping stone towards a larger SQL Server installation. We're seeing many small businesses power down their on-prem/colo SQL Server installs and moving to Azure SQL Database to save on TCO.
SQL Server Express Edition is a free edition that supports up to 10GB databases, so it is perfect for your local development environment (assuming the size constraint allows that). In 2016 SP1, they even added all of the Standard and Enterprise edition features to Express edition like memory-optimized tables and columnstore indexes. With integrated Windows authentication, it is incredibly easy to have your team use a single connection string and for a new dev just install SQL Express and go.
SQL Server Database Projects in Visual Studio are by far the biggest reason for me to prefer Azure SQL Database or SQL Server. This is the most elegant way of storing your database schema in source control that I've ever seen. You define the schema as just CREATE scripts, and when you build you generate a DACPAC with the entire schema defined as if you're creating it from scratch. But upon deployment it determines what objects need to be created, altered, or (if you want) removed to make the target database schema match the normative schema in the DACPAC. So it builds its own migration script based on the schema of the target database, and it won't allow any operations that cause data loss by default. This is super handy in a team environment because you don't need to worry about writing migrations by hand, you just define the schema "as it should be" and no matter what version of the database your team members had on their machine it will get caught up. Also dealing with merge conflicts is easier, because you're just doing a line-by-line merge of the e.g. CREATE TABLE statement rather than having to worry about which order your migrations run in. If anyone knows of something equivalent for MySQL or PostgreSQL I'd love to know!
You can get it here: https://msdn.microsoft.com/en-us/library/mt238290.aspx
In addition to that, SQL Server Developer Edition is also free-free now. (You do have to log in to get it, though.) Link here: https://myprodscussu1.app.vssubscriptions.visualstudio.com/D...
Note that Standards do not have more than one replica. Only Premiums do.
[1] https://azure.microsoft.com/en-us/blog/fault-tolerance-in-wi...
This only works if you're inside their rails. If you do have a change that requires a migration of data and risks data loss, you're then in the realm of creating pre and post-deploy scripts, and you're back on the migration train.
I've been playing around with a tool called sqitch ( http://sqitch.org/ ), but I'm not familiar with it enough to have an opinion on it yet.
re: "these days" meaning would these companies still choose MySQL if founded today? Impossible to say for certain, but some of them migrated to MySQL recently. Most have the resources to change databases if there was a compelling reason to do so.
Postgres has many appealing qualities, but so does MySQL (as well as SQL Server). Use the right tool for the task.
I'm a bit dismayed by the downvotes on my original post above. The parent asked "who would choose Microsoft or MySQL over PostgreSQL" and I answered factually and literally with who chose MySQL :/
So I guess if you need in-memory data, SQLite or Redis are better options.
For OLAP use cases it is years ahead of Postgres.
Anyway, this is a very interesting news. Given the "not so good" relationship between SAP and Oracle, this option will give SQL Server a boost when it comes to SAP Customers, although it may take some time for SAP to fully support the Linux-MSSQL option.
Thank Microsoft... good one
SQL 2016 Standard 2-pack of Core Licenses $3,717 ($1,859 per core)
Not cheap, but perhaps attractive compared to Oracle.