Announcing SQL Server on Linux
blogs.microsoft.com
blogs.microsoft.com
I am an equal opportunity consumer of database technology - something we all should be (e.g. use the right tool for the job when appropriate). I have used MANY different databases, open source and commercial, and there are better ones and worse ones across the spectrum. I think it is naive to criticize this move in any way - it is giving more choice so should be supported.
That said, SQL Server has a low barrier to entry, a very good implementation of the SQL language, an advanced optimizer, strong storage engine, management tools and a lot of nice BI features. Although you don't hear about it much, SQL Server also has Parallel Data Warehouse (PDW) which is their MPP database - ironically which used to be built using Ingres and Java of all things.
In my experience, SQL Server <-> PostgreSQL is the closest commercial to open source comparison (core database engine-wise) I can think of. This makes sense when you consider - both of them started as Ingres! Of course, Postgres and SQL Server have totally different user experiences (database management, tooling, etc.) and many different features.
I've built a Rails app atop a large legacy SQL Server, and I really haven't run into anything that wouldn't run. (aside from `rails db`)
It sounds like you are talking about the XML conversion method for concatenating groups. There is one other way around it using custom .NET CLR functions, but it is definitely cumbersome as well.
MS SQL is missing some very basic SQL functionality.
Microsoft doesn't need to make SQL Server look like MySQL. Quite the other way around.
And it's very useful in a ton of situations which is why so many other SQL dialects have added it. It's not like SQL Server is completely standards compliant so I'd rather have the functionality then have to use MERGE because it's "standard".
I work with both on a daily basis and there is a mile of difference. Yes I can accomplish whatever I want in sql server but there are so many small things that could improve.
Some of other pet peevees are: You can't use a lot of left joins, it slows down the query dramatically. If there is only one to one relation, use subselects instead.
Thou shall not do queries like this: select foo into baz from bar where fooid not in (select fooid from bar) They are the death of the engine.
And many "unwritten rules" like that.
Postgres just accepts the queries and run them..
We are on SQL server 2014. I have had a query go from 2-3 hours down to 7 minutes by replacing left joins with subselects within the query for instance (and yes all the indices are in place as recommended by sql server query planer)
The one with a insert based on a query on the same table I have never managed to run at all and I have to use a temporary table as an intermediary instead.
https://blogs.msdn.microsoft.com/psssql/2010/09/01/slow-quer...
Microsoft rewrote the code so there was no more Sybase code, but it still followed Transact SQL standards. T-SQL is not as complete as PL/SQL and others. It is just that Microsoft is that 800 pound gorilla so people use their products.
My last job I did migration from Excel and MS-Access to SQL Server using VB 6.0 code and ADO recordsets to copy and sync up data. They key was to use a column named date modified that had a time stamp when it was last modified to figure out which record was the newest and back up the old record to a history table in case it needed to be reverted later on.
I suppose Microsoft is pretty popular in beetween those 2 markets.
There are a few obvious cases which don't work (e.g. scalar UDFs aren't inlined - always use table UDFs), but the performance battle Oracle and Microsoft had circa 2000 really shows. The vast majority of the time you can code your intent and the query optimizer will simply do the right thing.
The main reason I would choose SQL over any of the competition is DataDude/VSSQL. We started using it where I work (~5100 schema objects) and it's revolutionary. T-SQL becomes a first-class language in Visual Studio:
- Full editor integration: intellisense and errors as you type.
- Build system: build (including static analysis) and deploy from Visual Studio. MSBuild-based integration for CI.
- Schema delta: you write/update your schema as though you are writing it for the first time and, as part of the build, the ALTER script will be generated for you. You rarely have to write migration scripts yourself (I've only had to when migrating data across columns/tables).
I have been thinking about solving this for PostgreSQL because I'm now completely unable to work without it - I just wish I had the time. Any other form of SQL development now feels like VBScript development in Notepad.
Update: Seems like this has become SQL Server Data Tools (but not the BI tools of the same name?). I'll have to look into this more.
You can also get it to reverse-engineer an existing database into a project[2].
Getting it to work for CI isn't turn-key. When you build a SQL/DB project it results in both a CREATE/ALTER script (depending on whether you do a diff build or a CREATE build) as well as a schema file (.dbschema). You need to track previous versions of the dbschema yourself (we use branching to track this file across releases), so that it can it has the base for diffing during the build.
It's a good idea to add a database reference to [sys] and [master] in all your projects (which is not done by default).
Knowing the right keywords I Googled a bit for you:
[1]: https://visualstudiomagazine.com/articles/2015/01/01/visual-...
[2]: https://www.youtube.com/watch?v=kWKllVyozOg (talks about SQLAzure, but these tools do work on-premise)
[3]: http://www.codeproject.com/Tips/998465/Creating-a-SQL-Server...
[4]: https://blogs.msdn.microsoft.com/ssdt/2014/07/24/sql-server-... (if you use TFS CI, otherwise you can invoke MSBUILD directly from whatever CI you use)
I've read that SQL Server code was originally based on Sybase code they licensed. Not sure.
Sybase is not in anyway related to Ingres that I know of other than the fact that Bob Epstein and Mike Stonebraker worked on the Ingres project at Cal. Sybase in fact was considerably different from Ingres in many ways such as use of SQL instead of QUEL, network access to the SQL Server, and specialized features like stored procedures. The Sybase founders did not come directly from Cal or even Ingres but rather from Britton Lee.
Can you store / query JSON-like data in SQL server? I'd like to be able to store a structured object and query a keypath.
http://blog.sqlauthority.com/2012/04/27/sql-server-introduct...
This is unfortunate as I'd love to have better document-db style functionality in SQL Server.
Or was the RDS implementation part of how MS figured out how to release this??
The big difference with RDS is that you don't get the full gambit of clustering capabilities that SQL server supports. This is mainly due to the fact they don't have a shared storage model that SQL cluster failover requires.
It's naive to support it just because of the assumption that more choice must be good.
On the contrary the past few years on Windows have been full of data corruption bugs in SQL 2012 and 2014; and while 2016 looks amazing with new features Microsoft has continued to push ahead instead of just - you know - fixing the fucking bugs we all keep reporting and upvoting on Connect and getting ignored.
More choice is IMHO not a good thing. It's a bad thing. In the same way people who "multi task" are more often than not churning out shit work and running on luck - I'd prefer Microsoft focus on what they know best.
We don't really care that much about the OS -- never did, never will. A windows license is very cheap, and we only need maybe 20 windows licenses to run the whole network.
The much bigger cost are SQL Server licenses, for which we care very much about!
All in all we'll keep on using SQL, but on the most performant platform -- my bet is that it's going to be Windows for a while though.
Regarding .Net Core: the platform is not ready yet (e.g. lack of support for Security IIRC), but we'll adopt it when it is -- again on the fastest platform.
Both in the case of SQL Server and .Net core, we are working publicly on GitHub with Microsoft by testing and providing advice where needed to make sure the next version of our ecosystem is the best possible.
I'm a big fan of SQL Server but the main thing that hurts it is the cost. Licensing our two servers today would cost a quarter of a million dollars. The OS cost is a rounding error. If I'm paying that much money for SQL Server I don't think I'd choose the Linux version for at least another 5 years from now. It needs to prove itself and there's almost no benefit to offset the risk.
And if you are a start-up, you will upgrade your DB, in which case you might only get a few years free anyway.
Conversations I had with some senior SQL server team members in the last two years suggested they were ready for no reboot patching but were limited by the OS and felt it was holding them back. If it wasn't for Windows, SQL server uptime would be much better (was the implication)
I'm looking forward to trialing it.
> Are you talking more in terms of support?
Support is not tied to the product's license (which is common for proprietary software).
That's still significantly better than "noone but a single vendor can and you're at their mercy", but just being FLOSS doesn't mean it can't suffer from product lock-in, just that with the hurdle being complexity and not copyright.
Natural complexity, when the domain is simply complex to model, can't be gotten rid of regardless of the product.
If you overcame that and made a patch, but it's not accepted by the upstream - there are maintenance costs. How long would you be able to maintain your own fork of a large software project, keeping up with the upstream? I've tried with a few small ones (say, at a small ISP company, I just needed some custom pppd patches for in-house billing logic), and found it to be not a pleasant experience. I believe it's not just because I'm a lazy ass - I saw tons of forks on GitHub that were slowly rotting away, maintained for some time but eventually forsaken by their authors and far behind their upstreams.
> If you overcame that and made a patch, but it's not accepted by the upstream
As for how well the FLOSS projects are managed, how's your luck at debugging let alone getting a patch accepted in MS-SQL, for instance?
> I saw tons of forks on GitHub that were slowly rotting away
I saw people with food, who weren't eating it because they were full, and I decided that food was stupid. Right?
> How long would you be able to maintain your own fork of a large software project, keeping up with the upstream?
Far longer than a binary patch... You know, to block a certain cipher suite or something in a closed-source product.
Duh, none or nearly none, of course.
I'm not saying that proprietary binary blobs are better. There are exceptions, but generally they're significantly worse in this regard.
I'm only saying that just because software is FLOSS doesn't mean one can successfully hack on it and adapt it to their needs, or easily migrate to other FLOSS solutions. Sometimes, they'll be stuck for a while - I think that's also can be considered as a lock-in.
> Nobody made their code complex because they felt like it.
Maybe. But there's a term "over-engineering" too. It's not that someone decides to make things unnecessarily complex, but sometimes they really do.
https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...
It's my understanding that the Postgres team has decided to change their historical stance of considering clustering solutions outside the scope of the core project, but I'm unclear exactly how far that has come.
The variety of different 3rd party implementations in various states of maturity make this a bit tedious to sort through.
That said, I always pick PG for my personal projects. I just have a hard time getting sign-off on it from my corporate overlords for anything at work.
Async Multimaster is in the list of replication solutions.
I have not personally implemented it myself.
Edit, As I am reading this more closely it may be listing third party implementations and is not 'out of box'
It will significantly reduce the licensing implications of using SQL Server for a small project. I know people who use MSSQL in their day job but postgres/other for personal projects and one of the reasons is needing to license Windows if a personal project becomes public and needs hosting. This way a small project can use SQL Server Express on Linux just as cost free as progres (or mysql if you must) on Linux.
The choice of OS will no longer dictates the choice of DB, that decision can be entirely made on the needs of the project and its target users.
Small projects using Express edition may grow into larger ones needed standard/enterprise features, and people exposed to SQL Server through projects using it on Linux are less likely to dismiss ti later because the know postgres/other only. I'm not sure how much we'll see brand new larger projects use SQL Server on Linux any time soon though, but I imagine "get them in at the ground floor and create familiarity as early as possible" is the intention here much like cheap educational licensing for Windows, Office, & full VS and the availability of VS Express.
That said, I use MS SQL both at work and on my personal projects thanks to BizSpark.
There's also Azure, which, to be fair, is still more expensive than an equivalent AWS stack with Postgre or something.
(I actually remember using Sybase SQL server for a while until the tool that would eventually be called Enterprise Manager was released.)
Well, I might KEEP my SQL Server, but switch from Windows to Linux as the host OS - to save $$ on licensing and hardware.
If you aren't locked into their eco-system stay clear away from it. Otherwise you will regret it, as the licensing costs are very expensive and per CPU core (think Oracle). Better switch to MySQL or Postgres or Lucene, etc.
For example, OLAP, distributed transactions, cluster scalability and so on.
SqlServer has always been the best performing and least troublesome database in terms of weird edge cases, backup, restoration, and (especially!) performing expensive SELECTS on databases that have grown into the 100's of Gigabytes in size.
Based on my experience, for all the (often deserved) hate on M$, SqlServer is really a shining star.
That being said, I am a little scared to support SqlServer on Linux, as I imagine it will have to be different in quite some potentially fundamental ways to work on Linux - I'd love to hear if anyone has opinions on that aspect.
That said, I think that this will probably come with a large amount of caveats, for instance, I wouldn't be surprised if when it launches it's just the core SQL engine and not the extra pieces (SSIS,SSAS,SSRS,MDS, et cetera).
http://blogs.msdn.com/b/slavao/archive/2005/02/05/367816.asp...
What is the reason for this?
Using raw partitions instead of file system files for database storage was common practice with Oracle and other DBs at one time. Not checked lately.
A textbook example is a file buffer. The OS provides write(2) or similar. It affords the abstraction of a linear sequence of bytes. The caller can write 1 byte or many, and the OS will take care of allocating/locating the block(s) and updating them. And write(2) doesn't even write! If you want to change the bits on the platter, you need fsync (and some faith in the hardware).
A DBMS by contrast is typically organized around pages, the size of which is a function of the disk blocks. Some of those pages maintain critical state information and are sync'ed to the disk frequently. The DBMS must also guard against data loss because of (say) power failure. It may choose to keep two copies of such data -- Jim Gray's so-called "ping-pong" algorithm -- so that even if the last one is corrupted due to insufficient voltage, the prior one will be OK. The DBMS's view of the disk is one of blocks, not bytes, that are strategically situated to minimize time lost waiting for it to spin or seek. The metaphor for writing data is less one of a stream and more like whack-a-mole: pop, pop, pop, done!
The astute reader will note Posix offers no services in support of splatting strategically situated blocks on the disk. I never read the code, but that would explain why Sybase in 1986 and 2016 lets the user define the database outside the filesystem.
Regarding Microsoft's Linux offering, we're coming full circle. The code Microsoft licensed from Sybase included an OS isolation layer in which Sybase implemented all those OS-like services. To VMS, for example, Sybase was just 1 process, and Sybase itself managed the multiple connections and tasks. Microsoft incrementally, version by version, stripped that away and insinuated the DBMS into the OS. Doubtless there is kernel support in Windows specifically for SQL Server.
Later, mirabile dictu, we're told SQL Server has "its own OS". I suspect that layer was created as much for organizational reasons as technical ones. It lets the two groups define an API for use by the DBMS. The DBMS group gains some control, and the OS group isn't at their beck and call. But it turns out to be useful beyond that. I have to imagine one reason "SQL Server on Linux" is still a year away is that adapting that OS-interface layer to Linux is a nontrivial effort.
Conway's Law strikes again.
So you're right, I do think it will be an engineering challenge, but as parent mentioned, the fact that SQL Server kind of treats the OS itself as abstract means I think they might have a path forward. Otherwise, I don't think they would have announced this.
Another way of thinking about this is, maybe the low-level refactoring necessary to make this run on Linux will actually be good for the SQL Server engine, although it will most likely go through a period of serious instability while they figure out how!
SQLOS isn't an abstraction layer over the operating system necessarily. It's more of an abstraction over the _machine_ that allows scaling between 8GB and 512+GB of RAM, 4 to 64+ cores, etc.
That said, since SQLOS does handle all memory management and file I/O for SQLServer, it probably did make porting slightly easier.
http://blogs.msdn.com/b/slavao/archive/2005/02/05/367816.asp...
"SQL Server on Linux" really means "More developers will be able to offer compatibility with SQL Server" - those who use Mac OS X and Linux as a primary environment.
My software supports PgSQL, MySQL, H2 and Oracle because they can run on my Macbook, or at least in a Linux VM. I explicitly exclude MS SQL because I'm not able to install MS SQL Server to test (at least not with a legal license). It's the same for continuous testing: It's easy to launch a Linux VM on a VPS, it's much less easy to launch and execute automated tests on a Windows machine.
"The spokesperson also acknowledged that Microsoft won't be bringing all of the same capabilities in SQL Server 2016 to Linux; it will be just the "core relational database capabilities."
As somewhat expected this will be a limited release catering mostly to cloud customers (Azure in particular) - who knows down the road they will port all features to Linux including the UI management tools but I would be surprised if they don't keep the niche features Windows-only.
Welcome announcement anyway - having a supported mainstream DB other than Oracle on Linux is a good thing.
For some definition of niche, which in this case may very well include "enterprise class features" such as replication...
This is a way for MS to increase exposure and draw people into their ecosystem (at least for a part of their infrastructure) in an increasingly OS agnostic world. I don't think it makes sense for them to cannibalize their current revenue sources to do so, and a part of that is requiring expensive Windows Server licenses for high-end MSSQL features.
"We are bringing the core relational database capabilities to preview today, and are targeting availability in mid-2017."
so you will have the core capabilities in 2016, but by mis 2017 i am expecting the full thing
I have colleagues on Windows, Mac and iOS where skype is getting updated, but it mostly seems to frustrate them by having an unpleasant user experience. Just today I was sharing my screen but they couldn't actually find it in the skype user interface!
This is Microsoft's business to lose. But network effects mean you have to keep more of the audience satisfied because excluding people means they take themselves and those they interact with away to an alternative.
I don't think it's going anywhere for a while... and I rarely trust MS with anything cross platform.
And also a few months ago they posted to the Django developers mailing list about developing MSSQL as a first-class database for Django: https://groups.google.com/forum/#!topic/django-developers/Fb...
It seems pushing MSSQL for Linux is the next step.
Meanwhile, I hope they can get things running ported to FreeBSD, since that's what I use for the server.
I'm a 20-year Linux fan, but my main project right now is running on SQL Server in Azure. I've got a side project running to convert it to Rails using the tiny_tds driver, but I'll wait for a couple/few years and see how this REALLY works out.
The more Microsoft tries to brand themselves as "loving Linux," the more I'm getting suspicious. If they really want to wear that crown, then they need to release Office for Linux, in DPKG and RPM formats. The product matters a whole lot less these days, in this cloud-based, app-driven world, but that's the ONE thing that would actually prove it to me.
don't have much experience with postgres, but my money is still on mssql
I find mysql to be very immature: transaction isolation is a joke, stored procedures are a joke... etc.
That's one hell of a benchmark, up there with "eating paste is better than being on fire" and "missing a hand is better than missing a head".
> don't have much experience with postgres, but my money is still on mssql
Obviously, since postgres is FOSS.
* A list of advantages vs mysql? (Bonus if you can admit faults of MSSQL as well) * Some blogs or articles perchance? * Performance metrics or % of SQL standard it implements vs the accused?
I'm genuinely curious if it is good but one half-screamed pledge is hardly trustworthy.
This site lists many of the issues: http://grimoire.ca/mysql/choose-something-else
[1] http://dev.mysql.com/doc/refman/5.7/en/group-by-handling.htm...
MySQL is not that bad, it was used in facebook and wikipedia.
FB also uses php, so appealing to it being used is no argument.
https://www.reddit.com/r/programming/comments/2mhpwp/postgre...
MySQL is hardly a relational database, it's not quite the benchmark you're looking for.
MySQL < MSSQL < Oracle < PostgreSQL.
Basically PostgreSQL is king of relational databases right now.
So yes, Oracle replication is probably better if you use Golden Gate. But Golden Gate is pretty expensive (17k per CPU?).
No way. Maybe Postgre is nicer than MSSQL for you. I don't know. But Oracle? Oracle is worse than MSSQL in just about every way that matters.
Having used both, I'd take that bet. MSSQL is in my opinion the best DB out there in terms of ease of use, documentation, integration, and tools. PostgreSQL is a distant second, and SQLite is probably a distant 3rd (though it's really not in the same category).
The pg documentation is quite through and informative.
> integration
Meaning? "Integration" in and of itself is meaningless because that depends on what we're integrating with. Rust integration? pg is much better. Some obscure windows-only business tool that most people have never heard of? well, it integrates with what they want it to (probably mssql). Some obscure BSD-only business tool that most people have never heard of? well, it integrates with what they want it to (probably pg or mysql).
> tools
Again, meaningless unless you discuss what the actual differences are. Being able to be used on BSD makes pg infinitely superior in terms of tooling for my cost function.
Also, Postgres is missing multiple result sets, which Sql Server seems to have, right?
Yes.
NOTE: this is not a bash or nag on postgres, I really like it's JSON integration, and plv8 is awesome. That said, even simple replication stories in pgsql have me pulling my hair out, mainly because I don't WANT to know every intricate detail about my dbms, I just want to build applications on top of it, and ms-sql is pretty nice in that regard.
PostgreSQL: Just nice. Every once in a while, you learn about a feature that makes things easier.
MSSQL: Plain vanilla, slightly outdated. Works. Nothing fancy. Expensive.
MYSQL: Every once in a while you learn about a feature that makes things harder.
Pricing can be expensive if you need the standard, BI, or Enterprise editions but otherwise it's not. It's no where near the cost of Oracle though.
>It sounds like you haven't used MSSQL in quite a while. >The 2012-2016 editions have progressed significantlyDefinitely not in the same category: the goals for SQLite are very different to those of MSSQL and Postgres.
It is impressive how well SQLite performs and scales so you sometimes see it do the job of a "larger" engine because a project accidentally grew and hasn't been re-factored in that respect yet, but if you are starting a new project I can't think of any cases where you would ask yourself if you should use it instead of MSSQL/Postgres. For an integrated storage engine for your app with SQL semantics, ACID, and so forth: SQLite wins hands down. For a fuller database and anything that needs any significant concurrency SQLite is not what you want and isn't trying to be that.
I still wouldn't use SQLite for a large scale app (assuming that means one that needs concurrent data access for multiple users) though: it is not the right tool for that sort of job.
Then you have full text search, built in columnstore, in memory tables, native procedures.
That's not counting all the non engine stuff. There is no adequate free software equivalent to reporting services or analysis services.
Sorry PG isn't even close. PG is no doubt extremely important and the best of the free software pack. And you will pay your life to MS for adequate licensing. But there really is no comparison.
MySQL on the other hand is a flat out joke.
Err, Postgres has full text search, columnstores and native procedures. Memory tables is slightly different, but doable.
It's an open source project with extensions, you can't just classify it by the core.
Hmmm. I use ZFS snapshots for backup of my PostgreSQL instances. It's faster, as reliable as it can be, and I can run a full backup every fifth minute and keep the snapshot history on a database that weights 2TB.
What? You have no idea what you're talking about.
> you have full text search, built in columnstore, in memory tables, native procedures.
Postgres has all of these (column stores only available as extensions).
I don't think you're very familiar with Postgres.
Well, gosh. I'm convinced.
(Full disclosure: I abhor MySQL. But if you're paying attention, you'll notice that my position is a statement of opinion, while yours is a statement of "fact". The reality is, every RDBMS has strengths and weaknesses, and should be evaluated for their applicability to a given project on a case-by-case basis — at which point, I'm sure, you'll conclude that PostgreSQL is the best choice, anyway.) ;)
My biggest problem with myssql is that it is very immature. Biggest problems I have with it are (right now, b/c that's what I'm facing atm): transaction isolation and stored procedures. I'm pretty sure pg has the same lame transaction isolation issues as well.
As just one example: "range locking". Look it up.
http://www.postgresql.org/docs/current/static/sql-savepoint....
PostGreSQL kicks both of them into the trash.
It is a very big deal... and an area Posgres is sorely lacking, but progress is made, and in another 2 releases may catch up to MS-SQL 2005.
I love what pg offers for dev, but it really isn't enough from the operations side. That said, I'd be more inclined to reach for RethinkDB for most situations these days, if it were up to me.
I've been leaning towards nosql options for a while though... imho RethinkDB is one of the nicer options out there for most use cases... ElasticSearch and Cassandra are also great options depending on your needs.
http://www.computerworld.com/article/3041378/microsoft-windo...
Keep in mind that Microsoft sells tech support to companies with a special hotline they can call and get answers and support for enterprise level software. Porting SQL Server to Linux means more copies of SQL Server being used out there and more tech support phone calls that companies pay for.
With Office 2000 there was the MSDE on the CD-ROM that had a developer version of SQL Server that could run on Windows 2000 Pro to test out databases without using Windows Server. It was limited in number of connections and other things.
That said, SQL is rarely the first tool I look for regarding my DB needs.
I obviously have no idea what went into the decision, but it felt like it was an attempt to get people committed to the product then strong arm them to Windows.
Even today, for mainstream products like Excel, they still hold back key features from a major platform like OS X. (E.g., https://support.office.com/en-GB/article/Compare-Excel-2016-...)
They have another decade worth of good behaviour owed before I'm willing to consider giving them the benefit of the doubt. But that's me.
I don't think "hold back" is the right term. It's my understanding they share some code between the platforms but for a lot of the GUI they do not so it's expected many of the OS X features are simply not done / lower priority than other features.
"If Microsoft ever does applications for Linux it means I've won." -Linus Torvalds
It looks like as an industry they are starting to loosen up a little now, but those MS SQL databases are going to be around for a while, and it'll be nice if I can at least standardise onto a single set of skills and install patterns for the platform OS.
Now I'm finished.
I'm not saying MSSql isn't really nice. I'm just saying I found the timing of reading your comment amusing.
Speaking of which, linked servers (cross OS, cross SQL type database queries) are native in MSSQL, but require a third party odbclink in postgres, which does not appear to have been updated since 2010[0].
but stuff like "Does not compile with PostgreSQL >= 9.2!", "A patched, but completely untested version", and "The current version does not yet support WHERE clause push-down, column push-down, JOIN push-down, or write operations."[0] are riskier (and a harder sell to management) than having the functionality backed in the core engine and admin interfaces.
Edit: Useful if both tables in join are on remote (linked) server
http://www.benjaminnevarez.com/2011/05/optimizer-statistics-...
> To create the best query plans when you are using a table on a linked server, the query processor must have data distribution statistics from the linked server. Users that have limited permissions on any columns of the table might not have sufficient permissions to obtain all the useful statistics, and might receive a less efficient query plan and experience poor performance
I think a lot of such nonsense is going to go away very soon.
Otherwise I am pretty happy with the product.
But it means having a separate windows-server team set up to handle builds and all that. The crappy thing is that the rest of IT uses Linux or Unix based OSs for their software, so Cygwin ends up getting installed on most Win Servers to open them up to the NFS shares that drive most of the data movement. I could see this being pretty huge to be able to migrate the various SQL server versions and Win OS versions over to Linux and ditch the need for dual environments while still supporting a RDBMS that most of the business is comfortable using and has tons of legacy data and apps built against them.
But.. yea I agree. If you just need a database, drop Postgres on a linux box and role. It's free and there is a huge community. If you've got the money and need something managed for you with support built in and lots of horsepower then go with something like Teradata. SQL Server feels like the Sears/k-mart of RDBMS. I'll just go to Walmart, thank you very much.
For a non-american, what's the difference between Sears/k-mart and Walmart?
In other words: MS isn't just trying to acquire new users, it tries to keep existing ones.
I'm really happy to see this.
I dearly hope the SmartOS and FreeBSD folks can make it work on their respective platforms.
On the other hand, at my current job I've been in charge of some search tech that's Windows only and being the lone Windows user has been a bit painful. We liked the results it gave us but recently developed our own algorithms on top of ElasticSearch so we didn't have to deal with Windows anymore. The Dev work to switch was more than the cost saved on the license for the old software, so this wasn't a case of open source saving is money on the solution implementation. We really just didn't want to focus on Windows Dev ops. I think this is the concern the SQL Server team had and why they're supporting Linux, especially in Microsoft's increasingly Azure focused world.
My income depends on SS but it saddens me when I think of how much is spent on it by applications which could so easily run on something that is free.
If that happens then I'm packing up and heading for the hills, this world is done for.
https://www.gnu.org/licenses/gpl-faq.en.html#DoesTheGPLAllow...
I'd argue otherwise[1].
Also, as of ~2 years ago, CentOS is just another Red Hat supported project.
Some people probably said that about the .NET framework. Now almost all of it (the new stuff anyway, and minus Visual Studio) is fully open source.
If someone is licensing something under GNU GPL, they are also giving away their intellectual property/patent rights (or what ever they call that) to any software developed in GPL, which Microsoft is never ever going to do.
MIT/X11 license is not giving other developers and users such a patent protection from its original developers.
B) Modern MS-RSL "reference source license" is now OSS compliant. The remaining difference between .NET Full Framework's Reference Source and what's happening with .NET Core is .NET Core has moved development to the open and Github as primary development platform. .NET Core is the future and .NET Full is "stable".
C) One example: http://referencesource.microsoft.com/#PresentationFramework/...
There's nothing stopping you from using the Reference Source today to build your own WPF fork (or, let's face it, you probably mean SWF). You can even start with Mono's existing mostly-there cross-platform SWF or Moonlight codebases and save a bit of time.
You know why you don't hear about lots of people doing that? I'm fairly certain it is because no one is building cross-platform GUIs in anything but HTML(5) these days.
Basically, http://yourdatafitsinram.com/
Sybase runs happily on Unix, Linux or Windows.
Well, that's 23 years ago...
But even at mssql V7[0], most of the systems had internal rewrites that made them different than Sybase.
[0] - https://blogs.msdn.microsoft.com/euanga/2006/01/19/sql-mythb...
As an aside, back in the early 2000s I was using the free version of Sybase ASE on Linux for webapps. It was great; painless to run and manage, and fast. Of course, MSSQL blew it out of the water even back then.
I can't call a stored procedure from a select query? Ok, write a user-defined wrapper function. Oh wait, I can't call a stored procedure from there either. Now I know why the consultants use a VBA script in Excel to load data... it's actually the easiest way, ridiculously convoluted as it is, if your logic is implemented as stored procedures.
I remember the first time I wrote a stored procedure in PostgreSQL. It was the point at which I decided I would never willingly choose MSSQL for a project again. If you write a lot of stored procedures / TSQL in MS SQL Server, do yourself a favor and check out how it is done in PostgreSQL. It's an order of magnitude simpler and saner.
Of course, bits and pieces might be tied to a platform and the dependency chain might drag other parts down, but overall Windows, Office, etc. generally have some sort of abstraction at the lower level that could theoretically allow porting to any platform.
There are a lot of other bits, such as Integrated Security Security, Windows Perf Counters, Windows Event Log, that are probably stripped out.
But I also suspect that that the Linux implementation is based on the implementation that they use for Azure SQL Databases.
Back then alot of that stuff was C. Hard to imagine Microsoft continued writing SQL server in C.
I don't really see many other choices being available. Sure we have Rust, Go and other languages now, but most of the current SQL servers are 20 years old or more by now. C is a well known language, there are good tooling, the compilers generate fast code and you can do very precise memory control.
The Natural Keyboards [0] have been getting less and less durable as the years go by. My first one lasted for ~five years. My second, two or three. My third is less than a year old and already suffering from intermittent failure to register keypresses, as well as surprisingly rapid breakdown of the faux leather material that covers the bottom portion of the keyboard. (The faux leather breakdown is a first for me... all the other keyboards suffered mechanical failure long before I saw any sort of wear on the bottom portion material.)
[0] Specifically, the MSFT Natural Ergonomic Keyboard X000 models that have the stupid, useless zoom slider.
But I agree with you. My next one will be a kinesis.
And I agree with you on both counts. :D
Are you thinking about one of the models with the bowl curves for the keys? If you are, have you typed on one? If you did, what did you think of it?
It's tough to adjust back to a laptop keyboard, however. And switching regularly may create new tissue in your cerebellum.
But the baseball mouse really hurts my hand; and I tried using it for a month before giving up.
I'm quite impressed by the possibility of running SQL Server on Linux; as a T-SQL developer immediately out of university, I have come to appreciate its speed and efficiency. It's not perfect and it does have its annoying quirks, but in general it's quite a sturdy RDBMS. MySQL has demonstrated some much more WTF-y quirks (try inserting a varchar into an int column!) and there are places I prefer T-SQL syntax (e.g. temp tables). Management tools don't really factor into it, you can get third-party tools that support intellisense etc. on different RDBMSs. In short, I would have few reservations about using SQL Server for my own projects (since I run exclusively Linux at home).
Licensing is another story though; I dread to think how much it'll cost, but this is a pretty amazing step in the right direction, adding to a recent history of such steps, for Microsoft.
NOTE: This type of thing is what made me hate Microsoft for so long, and even today I find it difficult to recommend them as a solution provider for anything, on any platform. So, while I'm trying to give them the benefit of the doubt, I'll continue working to move everything I can, including databases, to another platform.
https://www.microsoft.com/en-us/legal/intellectualproperty/t...
There is a lot of software that targets a limited subset of databases due to SQL incompatibilities/features and sometimes that is a subset of one. Getting some of those backends on Linux would be nice for people who have concentrated on unix/linux skills to the detriment of their Windows knowledge.
This eats Windows OS sales to some extent but I am sure they can make it up with database licenses and market share if they take the product seriously.
But there are often times when you just don't have a choice. Sometimes it's SQL Server or bust, and you have to work with it. Hell, sometimes, it's MySQL, and you don't have a choice.
I don't hate it at all. I wish it had more of the PL parts of PL/SQL because that would make my life sometimes easier.
I had to implement some very crazy/stupid statistical procedures with SQL Server. It kind of sucked in ways that would have been a lot better in Postgres.
Lots and lots of fun with dynamic SQL where you're stuffing SQL into variables and interpolating column names from variables into those strings.
It was awful. But it worked.
If SQL Server would bring in even some of the PL features of Postgres or Oracle, I would use in a heartbeat.
The tooling that MS provides for SQL Server is second to none. I love me some open source, but the tooling we have available for Postgres and MySQL is just absolute crap compared to SSMS.
BTW if you reply to this comment with a "Microsoft smackdown" comment then your comment is not very interesting.
I'm interested to hear some thoughtful replies, not Linux fanboy pile-on and jeering.
Maybe this is meant to fight off postgres and -- more importantly -- Oracle.
Is it actually used by a meaningful amount of people and businesses?
On average I've seen better windows server setups then Linux setups simply because windows servers tend be used in 'enterprise' solutions.
Come deployment time, they decide to deploy on Windows to minimize unnecessary differences with their dev stack and there are better deployment tools for MSSQL on Windows than on Linux.
I don't think Microsoft is trying to stop people from using Windows as a back-end server (especially when you look at efforts like Windows servers as Docker container farms), more that they are trying to even further lower the barriers to entry that a small "garage" company can spin up something like SQL Server. Maybe as that company grows past the "cheapest VPS on the market" phase they look to running their own Windows servers or (I presume, better yet to Microsoft) let Azure manage SQL Servers for them...
That said, they'd still have to come up with a much cheaper license for SQL Server to entice that "cheapest VPS on the market" crowd, but certainly this appears a step in that direction.
Not sure about the accuracy of my prediction, but I'll bet I'm closer to right than wrong by that time.
I wonder if this is the beginning of a similar change in the industry.
http://php.net/manual/en/ref.pdo-dblib.php
I can't speak to the IIS integration anymore-- I quit following that at a low level back in the Exchange '03 timeframe. I would suspect there's a clear line between IIS and Exchange, however, that could be used to facilitate an adapter layer for a flexible HTTP(S) server like nginx. The SMTP, POP, and IMAP services, though, would probably involve a lot of re-implementation.
Can't wait to try this one, a Redis-like service with all the consistency of a mature SQL database (in theory at least).
Don't get me wrong, I've being deploying Redis in production and I'm love with it, but it's nice to have an alternative with referential integrity not to mention the "isomorphic" effect (you'll be able to switch from non-volatile to volatile persistence without the wizardry required by the typical in-memory databases).
How great would it be to have first-class sql-server-management-studio on your Mac/Linux desktop?
I wonder if SqlExpress is going to be available on Linux too?
Only cost conscious developers care about running db server on the same machine as application server to keep costs low. Such people wont buy SQL Server at all for the cost related reasons.
Non cost conscious developers can always run SQL Server on a separate Windows machine while keeping their application servers on Linux.
Except Microsoft intends to make SQL Server free and make it replace Mysql among small developers and later help them buy SQL Server instances in cloud and make money.
From memory I remember when they "acquired" the source from Sybase and one of the selling points was that they could strip out all the multi-platform complexity from the source code. Being windows-only meant it was simpler, less complex code, faster and less buggy.
It also confuses my view of what MS has been doing. Maybe Azure is more important than Windows now? Its going to be kinda weird to see SQL Server on Linux in Azure.
So you don't think that, maybe-just-maybe, it's gotten a little easier to write cross-platform code in the last twenty-three years?
Their bet evidently is they can make more money selling SQL Server without Windows than with it.
If you're interested in using docker+.NetCore+EF a "proper" database was the only piece missing.
however http://www.npgsql.org/ is not yet a "first class" driver.
Notice the tags on there, "Stable" release is inaccessible and unstable has warnings (also it is labelled as unstable)
I would be less likely to feel secure in trusting the unstable release of a third party driver even though I know that the .NET team mentioned NPG during the last community standup. My understanding was that they said members of the Entity Framework team are working with the team at NPG.
I do think that it will be usable very soon, but the SQLServer drivers are working now.
I wish someone would step up and write a better .net postgres driver.
https://www.microsoft.com/en-us/download/details.aspx?id=364...
Can't really help you with the open source side of things.
What are the effects of this? Does anyone have experience migrating SQL Server clients away from Windows? Do Oracle, PostgreSQL, MySQL or others have database-clients with more rich feature sets? Or are these Native Client features barely used and not missed on non-Windows clients?
It's not completely self-serving. Back when magazines were made of paper, "DBMS Faceoff!" articles sold a lot of copy, and generated more heat than light. A DBMS is a complex beast, enough so that it's impossible to say if X is faster than Y for all workloads.
(I can't believe I'm actually saying that.)
would I use it for my own project's? no.
would I use it for some random software that requires mssql? yes.
MS is extending the olive branch in so many ways, and we in the open source community can learn to forgive. Still, it will be hard to get over the mutual hostility from decades past.
http://www.huffingtonpost.com/trudy-bourgeois/what-george-wa...
Linux desktop still is very low market share so an Office client doesn't make sense there, whereas Linux server is high market share so SQL Server does make sense.
What would be the memory usage difference?
https://www.youtube.com/watch?v=4F4qzPbcFiA
With that out of the way, I wonder if they will port their other software like Mcrosoft Visual Studio, Microsoft Office, various games, etcetera.
PostgreSQL for me thanks.
Wait I remember sun one asp server .
Please tell me this isn't sql server written in c# using mono !
We already have fairly superior commercial solution, like Informix Dynamic Server family, which is superior in everything, except compatibility with MS, that lost "competition" (to meme-based popularity) to crap like MySQL or bloatware like Oracle.
This is, again, good publicity and excellent PR action, but my bet is that performance and especially stability will be poor, like it is with .Net port or Skype port. To bring a server to the market requires expertise with target platform which very few UNIX shops has.
Open sourcing IDS, which has remarkable technology inside, which is partially related to Postgres, would be a much better move.)
Hacker news is a startup/technology oriented news aggregator with a comments section.
Not a den of criminals. (depending on your views about capitalism i suppose)
Thanks, Scott ` This is why there is a comma in grammar. `me Satya Nadella, X and Y` is different from `me, Satya Nadella, X and Y`
MS SQL relies heavily on Windows OS API. How are they going to pull this off? With some kind of VM layer running under linux with a stripped down Window OS which will run MS SQL?
MS Should announce that they are going to fork a version of Linux with their brand MS-Linux. Hell - If they port every piece of software over to Linux, they would rule the world & back-end server market.
Any way. Good luck!