[blahi]

To be honest with you, if you have to ask that question, you don't need SQL Server.

In order not to mistake my comment with an attack (it really isn't) one reason is tooling. Postgres doesn't have 1/10th of the tooling available for SQL Server. Another is BI. There isn't a better BI stack out there than Microsoft's. Also the price if very small compared to the competition and licensing costs are dwarfed by implementation costs, in general. So no, SQL Server is not expensive at all. Also the documentation is actually very, very good.

[dccoolgai]

Also, SQL Server gets you some pretty nice benchmarks virtually out of the box. You can beat it on PostGres sometimes, but only after considerable configuration/optimization. And, like you said, the toolchain. SQL Studio will spoil you.

[serguzest]

Correct me if I am wrong, MS Sql server isn't true MVCC database and there is some support and it is not default, you have to set READ_COMMITTED_SNAPSHOT ON for the database. They implemented it in 2005. Most microsoft shops don't bother to develop their softwares for this, they are using archaic locking transactions. I've seen many Sql Server deployments keep choking on locking transactions and and people are buyin bigger servers, more Windows and Sql Server licences for each cpu/core to deal with their choked database server.

So while Postgresql's defaults are all about technical concerns, Sql server's defaults are just convenient for Microsoft sales team.

[AaronFriel]

Both are true! MS SQL Server is a "true MVCC" database (no-true Scotsman?) and you are correct. SQL Server supports snapshot/MVCC isolation for transactions, but it must be enabled. They are determined to maintain 20 years of backward compatibility, and while I think that's wrong, they leave snapshot isolation disabled by default.

https://msdn.microsoft.com/en-us/library/ms173763.aspx

[serguzest]

It uses tempdb for tracking versions, it is not true mvcc in the engine.

[br1]

That's an internal detail. It's much cleaner than postgresql that leaves old tuples in disk and has to vacuum them later.

[greggyb]

It also offers an optimized in-memory transactional engine that is lockless and compiles stored procedures to native code.

[kbenson]

I can't comment on whether this is correct, but on the MSSQL box I integrate with, lock contention and the server automatically killing deadlocked queries is a regular occurrence. Like 50+ times a day regular.

[WayneBro]

You also get SSDT with SQL Server. PG has nothing like it - https://msdn.microsoft.com/en-us/library/hh272686(v=vs.103)....

[jdright]

But you need to reboot on install, updates and uninstall, all these operations that can take up to two hours for no reason. And the express edition is capped at 10gb db. So, no, SQL server is nothing better than alternatives at least if you aren't in the big ones and DBA is your job.

[jdright]

Nice, downvoted for what? facts?

This is a fact, I work with this thing for about 5 years (this is my karma) and there is not a single day where this doesn't cause a problem or makes somebody to lose 3-5 hours of work.

We have about 3000 people and this is overkill for our needs.

You know what the reason? Because people say that it is awesome, then a naive engineer decide to use it basically due to this propaganda in a LOCAL but central piece of our pipeline (something that SQLite would fit perfectly!) and then everything after becomes a problem.

Now we're stuck with it to day and nobody can change without a lot of work and trouble. I'm basically one of the people responsible in a strike team to fix SQL server issues in production.

[bartzz]

My team is managing around 500 production instances of SQL Server and few times that of non production environments. Various clients various configurations. SQL Server - when configured properly - isn't generally causing any problems. If it is, it's pretty easy to troubleshoot thanks to system views which allow you to see what is happening in sql engine internals with plenty of detail. Try this with Postgres. All our engineers working with both MSSQL and PG/MSSQL praise MSSQL for this visibility into system internals. Reboots? You can work around those. 2 hours for an upgrade? Boy what are you running this MMSQL on? Should take 20 min tops, and with cluster it's zero downtime (well, few seconds maybe). Express is capped at 10GB. Yes. For user databases (a hint). So sure, PG stack may be better for some uses, but MSSQL is simply too good overall to just dismiss it the way you're doing it. Also, did someone mention excellent BI stack which is included in price?

[clappski]

I have to say, I work for a bulge bracket bank ran entirely (well, mostly) on MSSQL 2008/2012, and I don't think I've ever had any issues with it, apart from the one set of DBs that someone decided to turn into a backend for an object store that seemingly didn't know anything about database design.

[jdright]

Yes, but as I said in my comment our use case is nothing like a Bank. See, using SQLExpress to build a desktop LOCAL database doesn't look like a smart move.

[AlexC04]

I suspect the downvotes (I didn't, but I can see why someone would) are related to the impression you're giving that this is useless in its entirety because it isn't something that is perfect for you.

     > But you need to reboot on install, updates and uninstall,   

this may be true, but can be mitigated with planning. MS's patch cycle is some predicatable multiple of "weekly" (I think it's either every thursday or every second thursday, whatever)

Our tech team schedules one member to work from midnight to 2 AM on "patch day"

As an alternative you could architect for rolling updates across load-balanced systems.

I'm not personally fussed about the drawbacks either way, the mitigation is easily planned for. It becomes a part of the cost-benefit analysis.

Now consider the world of containers, startup is actually ridiculously quick. In addition they need to be patched far less frequently. They have less "cruft" from the get go.

I'd recommend this entire video, but this graph highlights some of the advantages: https://youtu.be/XVtsw-uzovA?t=500

70% fewer reboots required on containers. 90% fewer critical patches.

     > all these operations that can take up to two hours for no reason. 

the workflow can be: spin up the newly pathed server while the old one is running, then "flip the switch" so new requests point to the patched machine.

They both point to the same physical files. When the old machine finishes its current cycle, it shuts down gracefully.

Fully mitigated by a container workflow.

     >  And the express edition is capped at 10gb db. 

yes, but this is not actually something that needs to be all things to all people, or even something specifically for you. It still has value.

The express edition is intended to be used by developers and "small data" applications.

I don't see what the express edition license is from this link, but I'm sure it is expressly not intended to be an enterprise database.

If I were downvoting you, it would likely be because you're throwing the baby out with the bathwater on this.

Once we get SQLSERVER running in containers, there's nothing to say we can't swap out to the enterprise edition virtually seamlessly.

     >  So, no, SQL server is nothing better than alternatives at least if you aren't in the big ones and DBA is your job.

Again, you're coming across as crapping on "a good thing" because it isn't the perfect thing for you. That comes across as petulant, selfish, and irrational ... all of which tend to be features that the Hacker News community prides itself on edit: discouraging.

So, with respect, I'd suggest you're not getting downvoted for "facts" but for "tone"

Even your follow up comes across as, I almost want to say "entitled"

I'm sorry, I don't want to start a flame war with you, but I'm letting you know what my "read" of your post comes across as.

If you're offended by it I'm happy to delete.

[jdright]

Thanks for your reply.

Yes, but we should be able to share personal experiences in comments doesn't? Or else how can we share each other points of view?

I still fail to see what is wrong with my tone in that wording, may be a language issue as English is my third language.

By that I mean mostly: To our experience, there are better alternatives if you're not a DBA as full day job or Google, Facebook, Microsoft, etc.

And, besides that, even with container and schedule and procedures, I fail to see how rebooting still something acceptable now-days. This is highly counter productive.

[AlexC04]

Absoloutely! We should 100% be able to discuss issues and our experiences.

English as a third language would explain the "tone" issue as the "nuance" (or subtlety) of you point would likely be harder to communicate.

I think acknowledging the good parts before delivering constructive criticism might help, but then, internet voting sites are also tremendously fickle and difficult to predict.

I think this post above does manage to communicate your point without sounding as negative.

In my case, I work at an agency - so our choice of database is in some ways driven by more than just the best tool for the job, but one that inspires our clients' trust AND is cost effective to work with. MSSQL does an excellent job of "inspiring trust" but it is also amazingly easy to develop for.

Our clients aren't worried about "overkill" or buying more database than they need. In the scope of a multi-million dollar project, the overpowered database is worth the piece of mind.

None of us on this end are "married" to SQL SERVER, but since we already own the license for one, and offer shared hosting of the database server or give a pass-through-cost for a dedicated server, it seems like a perfectly reasonable choice.

But we've both got our perspective and yours offers a good perspective. "As a DBA, SQLSERVER is often overkill" but if the client prioritises stability, recover-ability, maintainability, over cost (a "one time cost" of roughly 25% of one employee's salary) & two hours of downtime a week it's not such a bad choice.

[UK-AL]

Normally you run SQL server in some kind of cluster, so you flip the cluster to run off the other server, do your updates, then flip back. This is fully automated for us.

Seamless from user perspective.

I assume other database products require downtime for updates, and use similar strategies to avoid user issues.

[krallja]

> you need to reboot on install, updates and uninstall

I think you're missing the point of having this in a Docker container.

[jdright]

Not really. This is why I read the post. But the comments aren't discussing the container but the SQL being better or not than something else.

Docker would help my case if it wasn't required to run on Windows Server! This could be an interesting solution if it worked on Windows 7 and Windows 10.

But even then our legacy code would require some changes to be able to communicate with it.

So, psql, mysql, sqlite or no matter what is better in any aspect for our point of view.

[iamcreasy]

Just curious, What are the toolings that makes SQL server stand out? Also what is BI?

[knz]

Business Intelligence - https://en.wikipedia.org/wiki/Business_intelligence

[syshum]

>>>Also what is BI?

Business Intelligence helps corporations analyze on their core competency in order to synergize growth strategies globally for cloud-centric and client-focused innovation

[nocman]

Wow, the first thing that sentence made me think of was this:

https://www.youtube.com/watch?v=GyV_UG60dD4

xD

[iwintermute]

Can you elaborate on BI stack? How MS covers all chain?

[darylteo]

SQL Server, SSIS, SSAS, SSRS.

[petepete]

and PowerBI

https://powerbi.microsoft.com

[blahi]

And Excel has cube formulas (= MDX) which can process both SSAS and Power BI datasets.

It's not even funny how far ahead MS are in the BI space (ask $DATA shareholders). It's not healthy to be honest.

[madenine]

Loving Power BI.

Dashboards, reports, etc are a pain - but its what the clients want. They don't really care what we're doing in the background, they just want their metrics and analytics reports. Power BI has made that part of my job 100x better.

[thomasthomas]

user management is a little cumbersome but overall i agree. theyre constantly adding integrations with google analytics ,tableau, marketo, etc too

[zaroth]

It looks really pretty. But, am I right in saying that this all works by sending all your critical business data to Microsoft servers to run the analysis and generate the reports? Businesses actually do this?

Why would you send data offsite to generate pretty graphs of it? Is this really not available as a self-hosted service?

[greggyb]

Microsoft is cloud-first in development now.

There is a Power BI Desktop program that is 100% free as in beer available, which runs a private instance of an in-memory columnstore OLAP engine, Power Pivot (same internals as SSAS). It also includes the reporting layer and Power Query, a lightweight ETL tool.

There is an on-prem version of Power BI that the team has committed to including as a feature of SSRS in SQL Server 2016, but the timeline is still fuzzy.

The point of the Power BI hosted portal is not the pretty visuals, but a low-maintenance collaboration portal that includes natural language Q&A on your data, automatic analysis to identify trends in the data, and tight integration with Office 365.

If you want to go on-prem, there is a third party named Pyramid Analytics which offers a solution that allows Power BI hosting.

Power BI is actually a continuation of a number of products. If you want a somewhat equivalent experience on-prem, you can use Power Pivot in Excel (2010+), with Power View on SharePoint (SSRS 2012+, SharePoint 2010+), which gives you an older Silverlight-based report engine that has similar capabilities.

The real useful piece, though is the semantic layer in Power Pivot, which is identical to SSAS. The power in any BI tool is not the reporting layer (though Power View is a pretty good one for the use cases it targets), but in the power and expressivity of the data model. This is an area where Microsoft shines.

I could go on for hours about this - I work in technical presales (and do at least 50% time in a technical delivery role) for an analytics consulting firm that is a Microsoft partner.

[blahi]

Of course they have on-prem versions. A lot of people are content with using cloud services though, so on-prem solutions cost a pretty penny.

[greggyb]

See my sibling. There is no Microsoft offering for on-prem Power BI. You can get close to it with sibling products on SharePoint.

There is a third party offering an on-prem solution.

[eloff]

PowerBI seems to work with other SQL databases as well looking at the front page? Does anybody know anything about that?

[greggyb]

Power BI can connect directly to many database engines. Queries are generated on the fly by the reporting engine in a proprietary language called DAX, and DirectQuery is a technology that translated DAX into SQL queries.

This is a mode of operation that has been supported by Microsoft's SSAS OLAP engine for years.

It works quite well, but you do run into some performance overhead with the transpiling step.

Feel free to reach out if you have more detailed questions. I work for an analytics consultancy and we're a Microsoft partner. I live in the Microsoft data platform (SQL Server and all related products).

[broknbottle]

I believe he is referring to Business Intelligence.

[iwintermute]

Yes, I got it, there's SSIS, DB/OLAP, but after that? and I'm not sure, that SSIS & etc. is present in Express version.

[coredog64]

Not sure about 2016, but the 2012 version of Express did have a SKU that included SSRS. There are some limitations to the version. A looong time ago I wrote a utility[0] to work around some of those limitations as we had the licensed version but we were working with partners who had Express.

There are also some questions/answers on StackOverflow [1] that suggest you can mostly get by with doing SSIS stuff against the Express version as well, but you don't get all the capabilities.

[0] https://github.com/spc-ofp/RelinkSSRS [1] http://stackoverflow.com/questions/292564/can-i-run-ssis-pac...

[UK-AL]

This is for developers building apps locally, you create your own dockerised container for prod.

Express is basically free, its the real production versions that cost.

[kemiller2002]

What's interesting about MS too, is that depending on what you do, the production versions still aren't that much money. If you do any sort of commercial software development, MS will pretty much give you a bunch of free software (including MSSQL to a point) just to keep you on the stack and to advertise that you use their products over others. Between BizSpark and the Partnership Network, it's very reasonable.