8 ms·

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 lol

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.

endersshadow 14y ago
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.
- dev360 14y ago
  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.
  - hp50g 14y ago
    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.
- ucee054 14y ago
  Out of interest, how would you do OLAP on Postgres? (MSSQL comes bundled with an OLAP server...)
  - sendob 14y ago
    http://mondrian.pentaho.com/ http://mondrian.pentaho.com/ Is one I have used in the past
    - ucee054 14y ago
      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?
      - pdpi 14y ago
        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.
        ucee054 14y ago
        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.
        endersshadow 14y ago
        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).
cpher 14y ago
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?
- hp50g 14y ago
  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.
  - tracker1 14y ago
    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.
  - cpher 14y ago
    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.
- HarshaThota 14y ago
  PetaPoco (http://www.toptensoftware.com/petapoco/ http://www.toptensoftware.com/petapoco/) and ORMLite (https://github.com/ServiceStack/ServiceStack.OrmLite https://github.com/ServiceStack/ServiceStack.OrmLite) both work with Postgres.
avenger123 14y ago
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).
- endersshadow 14y ago
  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.
  - avenger123 14y ago
    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/ 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.
corresation 14y ago
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.
- codeulike 14y ago
  A quick plug for my open-source alternative front end for SSRS - CrissCross https://github.com/codeulike/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.
- xradionut 14y ago
  "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".
- crescentfresh 14y ago
  > 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).

endersshadow 14y ago

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.

dev360 14y ago
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.
- hp50g 14y ago
  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.
ucee054 14y ago
Out of interest, how would you do OLAP on Postgres? (MSSQL comes bundled with an OLAP server...)
- sendob 14y ago
  http://mondrian.pentaho.com/ http://mondrian.pentaho.com/ Is one I have used in the past
  - ucee054 14y ago
    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?
    - pdpi 14y ago
      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.
      - ucee054 14y ago
        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.
        endersshadow 14y ago
        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).

cpher 14y ago

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?

hp50g 14y ago
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.
- tracker1 14y ago
  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.
- cpher 14y ago
  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.
HarshaThota 14y ago
PetaPoco (http://www.toptensoftware.com/petapoco/ http://www.toptensoftware.com/petapoco/) and ORMLite (https://github.com/ServiceStack/ServiceStack.OrmLite https://github.com/ServiceStack/ServiceStack.OrmLite) both work with Postgres.

avenger123 14y ago

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).

endersshadow 14y ago
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.
- avenger123 14y ago
  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/ 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.

corresation 14y ago

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.

codeulike 14y ago
A quick plug for my open-source alternative front end for SSRS - CrissCross https://github.com/codeulike/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.
xradionut 14y ago
"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".
crescentfresh 14y ago
> 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).