Keep in mind I've been a SQL Server DBA for the past 10 years, and while it does come with builtin tools like SSIS/SSRS and stuff, it isn't all sunshine and lollipops. For example, how well does SSRS works for people that don't use Internet Explorer? Or any type of reasonable debugging in SSIS. Yes, there are tools that come with it, but many of them feel like crap thrown together by an MS intern. and yes, some are really nice and refined as well.

That being said, this post was intended for the web app crowd.

My point was that the context of this article is important. SQL Server has its place, and it does what its meant to fairly well. I really just despise these giant sweeping statements without context, so I wanted to add context. I also imagine that many HN readers are probably not very well versed on SQL Server. After all, it's not free (well, BizSpark makes it free for a while), and it's rarely used outside of the enterprise (for good reason).

To answer your questions, SSRS pre-2012 didn't work for non-IE users. In 2012, it works like a charm, though. As for debugging in SSIS, if you're talking about script tasks, again, they fixed it in 2012. Debugging data flows is pretty easy, too--I've never really had a trouble except for the script tasks.

As with any Microsoft product, there are a plethora of quirks that you just have to know about (or a quick googling will tell you), but I've never felt my dev time was stunted because I was using SSIS instead of Informatica or Talend or something else. These things all have their quirks, so none really bother me over the next.

Anyway, I'm not here to fly the banner of SQL Server. Microsoft, sadly, doesn't pay me royalties. I use it, it's a decent tool that does what I need it to do, but I do work on too many different platforms to really care about one or the other. My point was to give context and other info about what SQL Server is.

It's a good list for web apps, though. Certainly things you want to think about when building a web application. Especially pessimistic concurrency. I think a lot of web devs have no clue what concurrency models are. I cannot tell you the amount of `with (nolock)` arguments I've seen over the years.

with (nolock) is amazing. I worked at a very large fortune 500 company where that was the company policy because if you removed nolock, the thread contention would grind the servers to a halt. This also lead to some really interesting bugs, like a 5 million dollar order not flowing through a system because the row wasn't in the result set.

We had to throw nolock into a few things but to be honest, they should have been solved by better architecture and stopping the DB engine doing everything including app logic.

Out of interest, how would you do OLAP on Postgres? (MSSQL comes bundled with an OLAP server...)

http://mondrian.pentaho.com/ Is one I have used in the past

I thought Mondrian doesn't do OLAP cubes, it fakes them by just converting the OLAP queries into SQL queries and hitting the database with SQL directly?

Am I mistaken?

That's correct. You just have an abstraction layer that represents the cube as a specific star schema on a vanilla SQL database.

Other than the way you load data in, what's the difference, though? It's just an implementation detail.

Speed.

You can periodically build a read-only cube that has pre-computed answers to any number that any pivot-table could read from that star schema.

Then, when the multinational supermarket CEO queries for his company's total profit by product line over the past five years, he gets an instant answer.

The other way, the CEO must wait for weeks to get that answer from the RDBMS - or the cube must be replicated the cube inside the RDBMS somehow using tons of materialized views.

For my money, there's no better multidimensional database I've used for operational analytics than SSAS. JasperSoft, Pentaho, Essbase, Cognos, etc all just don't cut it. Essbase if you're doing GL (SSAS doesn't aggregate Parent-Child very well), but SSAS is an incredibly powerful tool. You can use Postgres with it, so you don't need SQL Server RDBMS to go with it, and you can just pay the license for your SSAS server (which needs more memory than CPU, anyway).

But if you've already got SQL Server for the SSAS, would you go through the bother of installing Postgres?

I think that's Microsoft's advantage in the database market: their system has all the tools out-of-the-box.

And the licensing's really not that expensive - especially compared to the heart-attack inducing prices coming out of Oracle, IBM, SAS, Teradata, Kx etc etc etc

I have just started to get into Postgres. I have been using SQL Server across its various versions.

SQL Server really is a great database but is it worth the Microsoft tax? I've come to realize that its just not. For my own projects, I am starting to go with Postgres. I still have doubts on the performance side but based on my understanding these have largely been addressed in the latest releases.

I think using Postgres on a linux box and ASP.NET MVC as the web platform is a good compromise. Are you doing this, or have you entirely moved to Ruby on Rails (or something else)? I would love to get your thoughts on this.

I think your statement around Microsoft's reporting and BI offerings is harsh but isn't entirely off the mark. The tools are good for the simple cases but for real BI, Microsoft can't stand up to Oracle's (and others) offerings. I am sure there are companies that base their entire BI on the Microsoft stack but based on my experience, its just not common. A lot of the systems that hook into the BI stack tend to be legacy or non-Windows data sources, so in a lot of companies, purely using Microsoft isn't viable.

It's also a dangerous thing to do as Microsoft tends to use SharePoint as a glue and I would be extremely hesitant to bring SharePoint in an environment (unless its for its out-of-the-box functionality).

I think you're a tad off the mark as far as Microsoft BI goes. I've seen some very large firms use it as their entire BI stack. And I've had a lot of work in it.

Then again, you really only want to do this if you already have SharePoint, since that's the web presence (it's a module in SharePoint called PerformancePoint). But SharePoint has sold a metric shitton of licenses (I don't know why). You're right to be hesitant to bring SharePoint in, but for those who have, it works all right. Even for "real BI" (whatever that means).

The backend stuff works well, and there are a lot of tools that can pick it up--my personal favorite being Tableau. Also, PowerView in Excel 2013 is pretty nifty, and it's what I've been waiting on since Microsoft acquired ProClarity in 2006.

In a previous life, I did a lot of MS BI & SharePoint work. On the BI side MS was just starting a push into the BI world and was gaining quite a bit of traction. I've worked on huge MS BI implementations and you are correct there. A lot of large firms use it for their whole BI stack.

On the SharePoint side of things, while I believe MS did oversell its capabilities, the problem was (could still be) that most places thought it was plug and play. It seriously wasn't. I don't know if things have gotten better but documentation those days was severely lacking. Add to that enterprises that were in the mindset that SharePoint was just another ASP .Net application and their in house devs could just pick it up without some sort of guidance.

There were a bunch of places where I was brought in to clean up the mess that happened because of those two things. I'm not sure if the landscape has changed or not but SharePoint is a beast unto itself and I've personally seen it implemented well at 30k+ seat organizations and the largest I've done was 50k seat org across 3 continents. It's not cheap to set up or customize but when it is done correctly, it does work.

Everyone owns SharePoint because of the CAL bundles. Many places were scared by the impending doom of Exchange public folders 10 years ago.

Others just got it in a licensing transaction... they signed up for the Core CAL, and took care of their Windows, Exchange, SharePoint and SCCM licensing, even if they didn't use all of them. If you owned any two products (Exchange/SMS, Exchange/SharePoint), you actually saved money in the process.

I don't disagree that the stack is good.

I also see that the term "real BI" is a bit meaningless :).

In terms of the companies using it, it looks like I might be a bit off, but its probably somewhat dependent on the industry.

One company that I am following is http://www.domo.com/. The founder has a great blog (www.joshjames.com) and seems to be trying to shake up the BI space a bit. I would love to know their back office stack.

I've used Domo for mobile stuff, and it's worked very well. I've been very impressed with it, and it makes a great impression. I've really only used it for quick demos and minor things--not for a full project, sadly. So, I can't give you a full on review of it. Sorry!

Yeah, we have issues with SSRS and Crystal Reports in cross-browser environments. i.e. the "preview" never looks like what you intend. Instead, we work around it by exporting directly to disk, which seems to keep the WYSIWYG aspect.

I'm more interested to know how MS people have integrated PostGres into their environments. Which ORMs work? What are the gotchas, etc?

Nhibernate. No issues.

To be honest we're shifting to java. Our license fees are well into 7 figures for MS stuff for no gain. This is the first step. Some subsystems are moving from asp.net web forms+mvc to spring, tomcat, hibernate, postgres. Cost savings are huge, staff available are orders of magnitude better and reliability is higher as to be honest, windows has been a piece of shit to manage over the last 10 years.

Fortunately we've got a pretty well designed system so we can move it over piecemeal as and when we update major subsystem features.

Wow, my experience has been a bit different. Every Java project I've worked on has been an exercise in frustration... Though a lot of the .Net projects I've worked on I can say the same.

I think that NodeJS + MongoDB has been the least resistance I've seen so far... though some of its' limitations are really frustrating (non-indexed result limitations). I like Postgres at least as much as MS-SQL Server. And the Entity Framework generators have been close to a blessing. That said, I'm much more of a fan of the DB as dumb storage, and not a big fan of SSIS. So changing from one DB to another is mostly transparent when things are how I prefer them.

Well, we can't shift to Java--we're consultants. We're a total MS and esri (GIS) shop. I was hoping for some hints that ASP.NET could integrate smoothly w/ PostGres in order to offer something else to clients.

I also think LLBLGEN PRO (http://www.llblgen.com/) is highly underrated.

There are commercial providers but npgsql- http://npgsql.projects.pgfoundry.org/, works well.

As someone who works on a project that extensively uses LLBLGen Pro I'd have to disagree with you.

Would you mind elaborating on this a bit? I would love to hear about it.

I understand that some people get turned off by the code generation aspect of it, but what in particular didn't you like about it.

PetaPoco (http://www.toptensoftware.com/petapoco/) and ORMLite (https://github.com/ServiceStack/ServiceStack.OrmLite) both work with Postgres.

I've looked at PetaPoco and Massive as a replacement for Linq2Sql, etc but don't know what experience others have had with it. We're mostly a MSSQL shop, but are seeing more Oracle projects. And now that PostGres is becoming stable in our GIS environment I'd like to know how people are dealing with multiple DB backends. Have you built anything in production with PetaPoco?

I would really recommend you check out Llblgen Pro. The name is just weird but its a really good ORM.

The support it has for all the major databases (SQL Server, Oracle, Postgres) and others is really solid.

Also, there is a full Linq provider that works well.

I would recommend BLToolkit (and the newer lighter version Linq2Db -- not to be confused with Linq to SQL). You keep an abstraction level above raw SQL via LINQ and with multiple DB providers you can easily switch between databases without rewriting your queries (provided you don't use any DB-specific features).

Check out the lightweight ServiceStack postgres ORM for light-weight usage, consider it more on par featurwise with Linq2SQL: http://nuget.org/packages/ServiceStack.OrmLite.PostgreSQL

Edit: just noticed someone posted this below. Sorry.

In most scenarios, end users aren't hitting SSRS' interface directly: The product offers a robust web service that is an excellent way of add very rich, capable reporting to your own application.

The comment about "thrown together by an intern" seems suspect (though not as much as criticizing SQL Server for defaulting to data integrity. If ever there was a non-complaint, that is it). I've been using SQL Server since 6.5, and generally find the toolset to be excellent, and anything other than "thrown together" (quite contrary, the product moves far slower than I would prefer).

They both have their place. In SQL Server land the things that they limit to enterprise edition generally are functions that only really benefit data warehouses (partitioning and compression being two of the most obvious), and given that they know that the people who use data warehouses tend to have warehouses of cash, it comes with a cost.

Worth noting that SQL Server has had clustered indexes forever -- psql still has a rather hacked variant of it -- along with materialized views. Both are of tremendous value to web developers. Far more valuable than the default transaction isolation level.

A quick plug for my open-source alternative front end for SSRS - CrissCross

https://github.com/codeulike/crisscross

It uses the standard report viewer but aims to handle multi-pick parameters and reports with lots of optional parameters more intelligently.

"They both have their place. In SQL Server land the things that they limit to enterprise edition generally are functions that only really benefit data warehouses (partitioning and compression being two of the most obvious), and given that they know that the people who use data warehouses tend to have warehouses of cash, it comes with a cost."

Redmond could throw column level encryption into the lower tiers as a security measure, but oh, no!, that's "enterprisy".

> end users aren't hitting SSRS' interface directly

They'll hit the rendered output though. Which in HTML4.0's case is horrid (at least in 2008).

We plugged spring integration, spring batch and jasper in and we're sorted.

The main problem with SSIS and SSRS is that you just can't put it in version control or scale it up inexpensively. Plus to be honest the tooling is shitty and unreliable (says me who spent all day fighting T-SQL debugger HRESULT crashes in SSMS whilst trying to reverse engineer a huge shitty black box SQL sproc into something scalable and testable in Java). Ugh.

We run both massive SQL server and postgres installations.

To save me a lot of reading time, is spring batch and integration more or less a drop in for SSIS?

I am looking at Postgres at the minute and the biggest stop for me is mass data imports from our customers on a nightly basis.

I've been keeping an eye on Postgres since version 9 as it has (reportedly) the same features as SQL but for a lot less.

We currently pay £250 a month per processor license and this shoots up costs a lot when you have a cluster of servers. If it's unavoidable then we carry on paying but if there are free alternatives out there then I'll jump at the chance.

The rest of the DB is used for serving sites, no other BI work done really.

EDIT: Typo

Re mass data imports - Postgres offers parallel restore of dumps since version 8.4:

(Quoting the documentation for pg_restore):

-j number-of-jobs --jobs=number-of-jobs Run the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multi-processor machine.

Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server.

The optimal value for this option depends on the hardware setup of the server, of the client, and of the network. Factors include the number of CPU cores and the disk setup. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases. Of course, values that are too high will lead to decreasing performance because of thrashing.

Only the custom archive format is supported with this option. The input file must be a regular file (not, for example, a pipe). This option is ignored when emitting a script rather than connecting directly to a database server. Also, multiple jobs cannot be used together with the option --single-transaction.

http://www.postgresql.org/docs/8.4/static/app-pgrestore.html

Its not a drop in replacement. You have to write code/configuration (but not much). Its definitely suitable for your use case - we use it for the same thing.

Feature set is comparable. We tend to avoid specific platform features as they are a migration risk.

Ha - our SQL license fee is around £60k a machine one off per major drop. We're not dropping that again for 2012. No way. Not when we have 8 machines :)

We have dug ourselves the hole really by using feature specific code and relying on certain features in our structure.

We are slowly digging ourselves out the hole with dependency injection in our code from a Entity point of view and want to go a similar route with our DB.

We are nowhere near yourselves but we have spent £18k on SQL licenses in the past 2 years. It's a lot of cash and I don't think we see the full benefit to be fair. Coupled with the abundance of other Microsoft licenses we pay out for, more than half our server costs are licenses.

I appreciate your comment, it's taken me a step in the right direction to sorting out DBs out.

Out of curiosity, how comprehensive is your test suite? For one company I worked for, we actually found PostgreSQL was outperforming Oracle and because of how comprehensive our test suite was, the lead dev connected to PostgreSQL and got 80% of the test suite passing in one evening. A strong test suite allows you to instantly find out where your app breaks down.

Side note for those who don't believe PostgreSQL can outperform Oracle: we needed a custom data type that we were aggregating over. The projected table size was over a billion rows (last I heard it had reached over 4 billion rows). As I recall, in Oracle, you were limited to writing user-defined data types in SQL or Java (now you can use C). PostgreSQL, being open source, allowed the leave to implement the custom data type in C. The query we needed in Oracle took several minutes to run due to the need to constantly serialize/deserialize the data over the aggregation. The PostgreSQl version returned in a few seconds.

We even hired a well-known Oracle performance consultant who did wonderful things to all of our queries ... except for this one custom data type which left him stumped.

The code change (and risk) can potentially be quite substantial if you're relying on default SQL server behaviour (i.e. not MVCC/snapshot isolation). Code that relies on blocking when it hits rows that are locked by another process get a bit of a nasty surprise during that kind of switch :-).

Always have deterministic known behavior :)

We shot ourselves when we first implemented NHibernate by setting our transaction boundary at the wrong place. This caused all sorts of portability problems.

I would recommend Talend Open Studio for the ETL portion. You'll get the same SQL bulk load feature as SSIS with any Database. http://www.talend.com/products/talend-open-studio

Choose Big Data or Integration downloads.

Thanks for the link, I've been looking around for software that does similar things.

I have been taking a look at SQL Web Edition in the view of coming away from standard that supports SSIS so it's good I'm getting a few options of other DBs too.

Although a majority of our stack is Microsoft based, I would love to get off the Microsoft train when it comes to the DB. It's a massive cost to our business

In a competitive world, you can either compete in having the best product, or try to change focus on bundled extras. Fast food restaurants was an early adopter with this when they started the "a toy in each meal" campaign and stopped competing for the best food. Has SQL Server started go down on that road, focusing more on the bundled tools than the main product?

> Has SQL Server started ... focusing more on the bundled tools than the main product?

Yes, quite a while ago. SQL Server has an outstanding SQL engine, but it had that a couple of versions ago, so they need a new unique selling point. This may be because a SQL engine is now a very mature technology. They always tweak it, but gains here are marginal.

Is there still a shared tempdb across ALL databases in SQL Server?

I don't know, try google. Is it important?

Yes, there's still the idea of tempdb, what's the issue though one DB eating it all up?

I totally understand your points in here - I have experience with both SQL Server and PostgresSQL. (+ Oracle, but that's not relevant in this case).

I wouldn't call myself advanced in both, more like an average user from DBA point of view + some more advanced BI things. One of the last tasks which I was doing on SQL Server was creation of a set of SSIS / SSRS packages to replace odd SQL queries scattered around dozens of utilities. I had chosen SSIS for the ETL tool as I had heard lots of positive things about this one and I must say it was really pleasant to use ... to a point when it became tedious trying to deal with wrong export formats of columns. Not mentioning the fact that when I was writing code for Script Tasks, sometimes the code was mysteriously disappearing, that signing of packages setting up agent tasks and all the boilerplate for that one to work was a task on it's own. I was blaming my lack of knowledge at that point about the whole stack - you don't usually master tool that big in a week.

Yes, PostgresSQL is harder to pick up, it's harder for your average and not IT-centric company to implement as a BI tool. Microsoft is providing a ready-made full-fledged and powerful Behemot which can solve most of your problems with a snap of a finger. Unless there's some magic happening and lack of capabilities to have total insight into the tools is driving you furious. Postgres has it's quirks - at least for me it was harder to pick up than other database engines, but I'm quite happy with all the additional features which it provides and additional understanding of the whole system which I'm gaining as I'm learning.

You could use something like greenplum for doing BI with postgres. Unfortunately they do not list prices, which leads me to believe it's not cheap. I wonder how it compares in price to a similar SQL Server install.