12 ms·

SQL Server 2014 Standard Edition Sucks

123 points by daigoba66 13y ago

cl8ton 13y ago
MS is not changing their tack because there is a vibrant big business community with budgets that depend on MS SQL and it fills the need. Which Open Source DB does everything that MS Enterprise SQL does and why aren’t you using that instead?
- BrentOzar 13y ago
  > Which Open Source DB does everything that MS Enterprise SQL does and why aren’t you using that instead? I used to think the replacement wouldn't have to do everyone MSSQL does - after all, nobody uses all of the features. But getting a core subset often isn't enough either - for example, Windows Azure SQL Database supports a core subset of features and datatypes, but I still constantly hear people say it's not enough to migrate their apps.
  - xradionut 13y ago
    We were skeptical of Azure before NSA revelations, but now; "Hell No!". Besides that, Microsoft has pretty well pissed off all but the most entrenched developers.
    - recursive 13y ago
      It seems to me that the group of "most entrenched developers" is still pretty big. In real life, it's the most common kind I see.
  - jaynate 13y ago
    If you think lock in on SQL server is bad wait til you're locked in on azure.
- elchief 13y ago
  Postgres is not there yet, but getting closer. (Proper) materialized views coming in 9.4.
- rbanffy 13y ago
  > Which Open Source DB does everything that MS Enterprise SQL does They shouldn't fear a competitor that does everything they do. They should fear a bunch competitors each doing a set of features a given customer needs. SQL Server is Microsoft's best software product, but why would I even bother with it since both MySQL and PostgreSQL do everything I need from an RDBMS, for a tiny fraction of the TCO and with comparable if not better performance and much better platform support? And why would I even consider it when non-relational datastores are a much better fit for most of my data? It makes sense if you are into Windows, but makes no sense whatsoever if you are not.
- Farox 13y ago
  That's the thing. It gets the job done and if you need a dev to develop a certain feature that MS SQL has you recouped your licence costs right there. This does suck for small/mid sized businesses though. :/
epochwolf 13y ago
This won't change because large companies don't like open source.
- kaoD 13y ago
  Not my experience. Government dislikes open source (you know, communists).
- wmf 13y ago
  At these prices it seems like you could take Postgres, wrap it in a support contract, and call it EnterpriSQL and still charge less than MS.
  - bsg75 13y ago
    http://www.enterprisedb.com/ http://www.enterprisedb.com/
  - daigoba66 13y ago
    Someone beat you to it: http://www.enterprisedb.com/ http://www.enterprisedb.com/
    - MichaelGG 13y ago
      Actually, most of their prices are "Call us!"[1] Looks to be at least $4000/socket/year. So, more money than SQL Server Standard, less than SQL Server Enterprise. 1: http://www.enterprisedb.com/products-services-training/subscriptions http://www.enterprisedb.com/products-services-training/subsc...
      - piggity 13y ago
        SQL Server Standard is $2K per core - with > 4 cores per socket, things flip around a bit. I'm not a SQL licensing guru; so perhaps I'm confused as well...
        13y ago
taude 13y ago
We figured out we weren't using most of SQL Server's features and didn't want the mental overhead of MSFT licensing. We switched to Postgresql and haven't looked back.
- BrentOzar 13y ago
  How big was the application (like how many developers were involved in the migration, data size, etc)? How long did the migration take? Was the business okay with a feature freeze during that time, or did you take advantage of the switch to rewrite it too? Did you have multiple connected systems, like ETL processes that also hit the database for other reporting systems? Always curious about these kinds of issues because they seem to be what's holding businesses back.
  - deleted 13y ago
    [deleted]
  - Herbert2 13y ago
    I went through a similar switch from MSSQL to postgres. We moved 7 applications of various sizes over and the biggest hurdle was office politics. The migration never took more than a day for each app. ETL and reporting were not affected since we had previously integrated messaging (rabbitmq) pretty heavily into every system at the company and ETL gets their data from there. We also switched the DB servers to over to linux and got a huge performance boost from that. We did rewrite some stuff, but that was mostly to take advantage of postgres features like array types (instead of a article tags table, you'd have an tags array) which we used to speed up some applications. One we sped up by a factor of 10, just by using array types and offloading json generation to postgres: select array_to_json(array_agg(row_to_json(t))) from ( some query ) t
  - taude 13y ago
    It was several smaller apps written with most logic in the app (Hibernate) and had very little T-SQL/stored procs. We also weren't using any of the add-ons like SQL reporting, etc. The big commercial DBs have the advantages when you start using their reporting services, BI/OLAP stuff...and likely when you have several different groups as stakeholders in the process...
yuhong 13y ago
I wonder which edition StackExchange use?
- daigoba66 13y ago
  I'm fairly certain they use Enterprise because they make use of AlwaysOn Availablility Groups.
  - BrentOzar 13y ago
    Before AGs came out, they used a custom log shipping script for DR, and mirroring for the critical DBs. I don't think they would have gone Enterprise just for the AGs, but since they were on EE anyway, AGs were an easy decision.
- BrentOzar 13y ago
  > I wonder which edition StackExchange use? Enterprise for the memory and the ability to do online reindexing. They were in the BizSpark program, so they basically got free licensing.
  - yuhong 13y ago
    And they are on Software Assurance, right?
    - BrentOzar 13y ago
      Yes, when you exit BizSpark, you can just start paying maintenance on the servers you were using during the program.
trackztar 13y ago
Didn't read. Used the word Seriously in the first paragraph. Grow up noobs. Holy fuck!
- BrentOzar 13y ago
  > Used the word Seriously in the first paragraph. Actually, that was the second paragraph.
  - chris_wot 13y ago
    Don't feed the trolls.
corresation 13y ago
I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).... SQL Server has been getting much more expensive because their current userbase is captive (meaning the cost, complexity and risk of migrating to another database system is enormous because of a heavy integration with SQL Server specific features). I don't imagine a large number of new systems are being built around SQL Server, apart from those at shops that are already captive. It is an excellent database, but I do get a chuckle that by far the greatest benefit being pushed for SQL Server 2014 is in memory tables (which is something that SQL had -- at least for temporary tables -- back in the 6.5/7.0 era, but then had removed). While it is hardly identical, an approach we did on one team is to have SQL Server take 64GB (note that it is per instance, and most server deploys see many instances on a single server, so that isn't quite as prohibitive as it might sound) and then have an enormous RAMDISK on which tempdb would be created. Made use of the RAM, and saved enormous amounts of IO (tempdb is the weak link of almost all SQL Server platforms, as everything hits it, especially if you make use of snapshot isolation / row versioning).
- BrentOzar 13y ago
  > I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)... http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)..... Great catch - fixed, thanks. > greatest benefit being pushed for SQL Server 2014 is in memory tables (which is something that SQL had back in the 6.5/7.0 era, but then had removed) Sorta kinda - back then, we had DBCC PINTABLE, and it would cache the data, but it would still require that the transaction logs and data pages be written to disk. Now with Hekaton, you can specify that a table never hits disk, ever. (Don't get me wrong, I don't think people are going to adopt that particular feature due to other limitations around datatypes and supported syntax.)
  - corresation 13y ago
    I was not at all clear, but sorry I was speaking of temporary tables, or basically anything intended to be ephemeral. Back in the 6.5 days you could flag tempdb as being RAM resident automatically - http://support.microsoft.com/kb/115050 http://support.microsoft.com/kb/115050. Load up some global temp tables on db startup and rock and roll. It made tremendous sense because that's often an area of enormous churn (I saw a ServerFault post or something where someone did this and saw 30 iops to it, wondering why it didn't improve their system. That is nothing. One product I worked on saw literally hundreds of thousands of IOPS, and everything that sped tempdb sped the world).
jaynate 13y ago
For years Microsoft-sponsored SQL Server consultancies have been telling us to use the database to its fullest extent. Use Stored Procs and non ANSI SQL features, they're fine! Logic in the database? We love that. That's what ISVs and Enterprises are being told to do and that's exactly what they've done, at least the ones who couldn't see Microsoft's business angle. Now many are left to choose to either accept Microsoft's ever-increasing licensing costs or a high cost to switch. I agree with @BrentOzar, time to find other options.
- sehrope 13y ago
  I see nothing wrong with using stored procs or non-ANSI features. You just need to know what you're getting into. The same goes for any database. Being database agnostic is great but so is actually finishing what you're working on and having it run efficiently. If DB specific features get you there and you're willing to accept the terms of being locked in then it's a sound decision. For our product we use Postgres as the app's database and happen to use some Postgres specific features like stored procs and hstore[1]. The latter in particular is not ANSI at all. There is no equivalent at all in other databases and migrating usage of it to another DB would be a real pain. I know we're tied to Postgres and we're ok with that as it's a joy to use and let's us spend our time elsewhere. You use database specific features because they're useful, not because you're forced to. Of course I'll concede the point that it's a bit apples and oranges to compare being "stuck" on Posgtres vs a commercial closed source stack. [1]: http://www.postgresql.org/docs/9.2/static/hstore.html http://www.postgresql.org/docs/9.2/static/hstore.html
  - eksith 13y ago
    "...so is actually finishing what you're working on and having it run efficiently." A "finished" product is usually a dead one and efficiency is relative. You've seen that incremental improvements to the entire stack can have far greater impact than fiddling with just the storage end alone after all. A product never actually stops evolving if it's going to stay competitive and make money. But I'd say staying with Postgres is one of the best decisions you've made. Besides being a joy, as you say, you can be fairly confident that it won't suddenly become broken, become proprietary and best of all, become an order of magnitude more expensive for the same capability.
vyrotek 13y ago
My company went through BizSpark 3 years ago. We're now running everything on Windows Azure. No complaints here. Sure, I'd like SQL Azure to have a few more features but there haven't been any deal breakers yet. C# is by far my most favorite programming language and is probably what keeps me coming back to .NET & Sql Server. I must admit though, I'm very intrigued by a lot of cool features found in PostgreSQL. Specifically HStore.
- xradionut 13y ago
  Chances are you are not dealing with banking, insurance or medical data while running on Azure. After the last several weeks, we had to assure clients, that none of the data is stored on Azure. Plus with the standard version of SQL Server or Azure you don't get full auditing or encryption.
  - vyrotek 13y ago
    That's correct. I actually used to work for a company which built health information systems for hospitals so I'm aware of the hurdles there. They were .NET based too. Hospital network contracts pay very well but they are absolutely the worst customers.
- joseph_cooney 13y ago
  Postgres works pretty well with C#. I've used Dapper + Postgres stored procs very happily.
akurilin 13y ago
Brent, to which db flavor do you recommend people migrate? Thoughts on Postgres? Btw, loved your vlog post on scaling SO's db layer.
- BrentOzar 13y ago
  > Brent, to which db flavor do you recommend people migrate? Thoughts on Postgres? I'm not the right guy to ask on that one - I just don't have enough experience with alternative platforms. You're in the right forum though - as long as you follow the news on HN, you'll do a good job of choosing the right data storage platform for your needs.
  - akurilin 13y ago
    I'm thinking your post would benefit from a recommendation as far as the "next closest thing" to MSSQL, since you're exhorting DBAs to make a transition, and minimizing friction would be an important aspect of it.
    - BrentOzar 13y ago
      > I'm thinking your post would benefit from a recommendation as far as the "next closest thing" to MSSQL Then by all means, add it in the comments and I'd be glad to point to it. I'm just not qualified to make that recommendation myself. My experience with other platforms amounts to importing data into MySQL for WordPress, heh.
  - statictype 13y ago
    I wonder if that was sarcasm :) If you spend too much time on HN, you'll end porting your RDBMS to Mongo DB or Voldemort.
    - akurilin 13y ago
      Hipster News. I joke I joke, right tool for the right job, right?
- 13y ago
ivanbrussik 13y ago
saved this post for just another reason why open source is going to take over the world
CurtMonash 13y ago
64 GB of RAM is 100,000 times more than what Microsoft used to think people needed. :D Seriously, Oracle's biggest point of losing customers is when it's time to upgrade from Standard Edition to Enterprise Edition ... or else pick an alternative. I imagine something similar is true for Microsoft SQL Server.
MichaelGG 13y ago
The funny thing is how Microsoft used to make fun of Oracle for being restrictive on the CPU licensing types. How "hardware improvements are for our customers' benefit". Now? You pay differently depending on AMD/Intel and type of chip. Oh hey, just like Oracle. It's hypocritical, but I guess it's also just business. At any rate, like this post says, whatcha going to do? The ease of use of SQL Server is fantastic. As much as I would like to use Postgres (despite the HA story looking very confusing, and lack of basic things like materialized views), the tooling is lightyears behind Microsoft's. The fact that I'd truly need to become a DBA to properly use Postgres (oh great, an ini file with poorly documented settings!), whereas I can fumble my way through SQL Server... that's worth a lot.
- xradionut 13y ago
  You have to be a DBA to properly use SQL Server too. Yes you can stumble your way through wizards and SSMS, but you will get tripped up eventually if you don't spend some serious time hitting the books and blogs. (Recovery model, what's a recovery model? Why's my disk space gone?)
  - chris_wot 13y ago
    (Tempdb, what's tempdb? Clustered index, what's that and why the hell do I need one? Escalated locks - what the hell? Isolation modes, why are they important?!?)
    - taspeotis 13y ago
      I'd be happy if the outsourced developers I work with knew what an index was, let alone whether it's clustered or non-clustered. It would be refreshing to see seeks instead of scans in the execution plans...
  - MichaelGG 13y ago
    Well I'm doing alright with 100s of GBs of SQL DBs, replication, tx log shipping, HA clusters, etc. and I wouldn't consider myself a DBA. Basic stuff like transaction log management (recovery model) hardly qualifies me as a DBA. We're both right depending on the value of DBA. I don't think the argument can be made that SQL Server will require vastly less effort to figure out for the majority of cases (and edge cases have people explaining things assuming you're not an expert).
dschiptsov 13y ago
2014 Standard Edition only?))
forgotAgain 13y ago
I wonder if those working in corporate IT realize that they're competing with Microsoft for money. Most corporations see IT as an expense center. It's not a place where the business make money. It's simply a place where they spend it. You can argue the validity of that idea but whether it's right or wrong doesn't matter. It's the way most businesses think. Businesses, of course, try to limit expenses. They especially try to limit any increase in expenses. So basically there is a certain sized pile of money for IT to work with. If the costs of Microsoft infrastructure goes up, the difference is made up by cutting another part of the pile. The part of the pile that can be most easily managed is salaries.
- bWare_iWare 13y ago
  Actually Microsoft spend billions on marketing and employ top notch sales people. It is perfectly reasonable to assume they will do a better job of arguing to increase the size of the pile of money set aside for IT then you would. Once the pile of money is increased it is a much easier job to argue for your share of it.
- incision 13y ago
  >"I wonder if those working in corporate IT realize that they're competing with Microsoft for money." Generally no, but I expect many are quickly realizing it. Microsoft in particular has been very open about trying to sell things like Office 365 and Azure to Enterprises. They aren't afraid to be blunt and say "Hey, you can get rid of your staff if you go with this". It's an odd relationship. In my experience, many in corporate IT are perfectly happy to be simple facilitators - shoppers basically. I believe the perception is "It won't be me", that being increasingly beholden to a vendor might prevent the creation of new positions or filling vacancies but will never eliminate their own positions. The entire industry of internal IT support has hard times ahead. Anecdotally, I see XaaS being adopted surprisingly quickly. A few big VARs I've talked to have noticed this and have been realigning themselves to sell managed services instead of stopping at planning and implementation as they have in the past.
  - corresation 13y ago
HalBerenson 13y ago
I'm pretty sure I wrote about the history of the Enterprise vs. Standard decision making on my blog (http://hal2020.com http://hal2020.com) but I can't find the post right now. I'm one of the people responsible for the original philosophy, and I don't think it's changed much. I'll go back and look for it again. Basically you have three dynamics going on. The reality check of course is that the competition is Oracle and IBM DB2, and to a lesser extent open source databases, and various analytics products. Check out Oracle's price list and SQL Server Enterprise remains inexpensive. And Microsoft has introduced cheaper options to keep "free" open source options somewhat at bay, though the truth is that without multiplatform support there is nothing they can do to really capture that segment of the market. Standard Edition exists because I couldn't convince my then boss that we should bifurcate it into a couple of sensible products, one slightly lower in capability and one slightly higher. The slightly higher one would have been a "Small Business Enterprise" edition that included many of the features of Enterprise but somehow retained differentiation from full Enterprise and would have been dramatically less expensive. I had a differentiation, but I don't recall what it was. The reason the bifurcation was rejected was that Standard was the edition that matched earlier versions and we didn't want to piss off customers by forcing them into a more expensive or less functional edition. And we didn't want to complicate the world with yet another two editions. So the status quo was maintained. BTW, this is a late 90s discussion. The next dynamic is that there are a lot of features which cause a crapload to engineer but don't increase product volumes substantially. This is the primary driver of what becomes a candidate for Enterprise rather than Standard. When you are investing $10s of millions in a particular feature's engineering then you want some way to get a return on that investment. It really is that simple. Almost. There is (or was) a re-analysis each version of what goes into each edition. My philosophy was that you introduce new enterprise features in the Enterprise Edition, then examine moving them into Standard Edition in subsequent releases. So there is a constant stream of new high-end features flowing into Enterprise, then as they become part of the mainstream thinking you push them (or appropriate subsets) into Standard. But that philosophy was never adopted and so the effort seems far more haphazard than I'd wanted it to be. Customer and competitive pressure will result in capabilities being pushed into Standard, but it doesn't seem to happen in a rational way. Max memory size and high-availability features were the original differentiators when Enterprise Edition was introduced as a mid-life kicker for SQL Server 6.5. In the case of memory it was an actual technology differentiator back in the mid-90s on 32-bit machines. That it has survived through the 64-bit transition is shocking. But the reality check is likely that very few servers actually have more than 64GB,despite today's hardware prices, and thus Microsoft sees it as an acceptable differentiator. High Availability should remain a differentiator, though a simple subset does need to be in Standard. That the current subset is actually deprecated is, ummm, looney. Customer demand for capabilities in editions other than Enterprise, or competitor moves, will lead to Microsoft changing the balance between Standard and Enterprise. But it isn't a few sophisticated DBAs/developers/etc. calling for the change. Or a niche or flash-in-the-plan competitor. It is an actual shift in market dynamics.
programminggeek 13y ago
I'm sorry, but if you have to read a feature grid to figure out what a product does, you've already lost. Also, isn't this when you look at Postgres or MySQL and go... licensing cost, what licensing cost?
- samgreene 13y ago
  I don't think this is a useful mindset. Grids are very handy comparison tools. Also, licensing costs are only part of the overall cost of running a service. 'Serious businesses' will not run on a piece of software that is not supported by either a vendor or their own staff. Not everyone is comfortable fixing bugs in their DB platform, or relying on the community to do it.
teilo 13y ago
I just took my first plunge into the caustic waters of SQL Server, being forced there because my company is deploying a vendor-supplied shipping system that only runs on it. Since the data set would be growing beyond 5GB, SQL Server Express was not an option. Coming from a Postgres world, Linux, Java world, the whole subject makes me sick. I was disgusted to discover that my 200-strong Mac shop would have to spend $7,000 on SQL Server. I was even more disgusted when I discovered that Microsoft no longer lets me run it on the server I purchased, a dual 6-core machine, because now EVERY core is licensed individually, and you are FORCED to license every core in the machine. You can't just license some of them. That forced to run it in a VM, which is just asinine. And THAT means that I also had to purchase an additional Windows Server license to run a SEPARATE VM for IIS/ASP.NET. And THEN (and this is the part that offends me most of all), I have to purchase a CAL for every damn machine that will be using the shipping system, because it's not a public website, but a private web app.
- WayneDB 13y ago
  SQL Server 2012 Express allows up to 10GB databases. Also, you should only need CALs if your private web app is using Windows Authentication to make each database request on a per-user basis. From http://www.mssqltips.com/sqlservertip/2942/understanding-the-sql-server-2012-licensing-model/ http://www.mssqltips.com/sqlservertip/2942/understanding-the... "you have two choices: purchase per core licenses at $1,793 or purchase a server license at $898 and client access licenses at $209 per client." So, if everyone is going through the web app and the web app is not using Windows Auth (you certainly don't sound like you need to use that) - You should be able to get SQL Server for ~ $1107.
  - teilo 13y ago
    The minimum cores you can license on MSSQL, whether on bare metal, or a VM, is 4. Wherever you run it, you must license all cores available to the OS. Had I chosen to run on bare metal, I would be forced to license 12 cores (6-core Xeon CPU x 2). This app requires SQL Server 2008 R2, so the 4GB limit applies. But I can't buy 2008 R2 anymore directly. I have to license 2012, and use my downgrade rights. You are wrong on the Windows Authentication requirement. This has nothing to do with anything. For MSSQL, you must license the database per core if you are using it in a public web application, because you have an arbitrary number of users. And you must license all cores in the machine. The VM is the only way to license only some cores, namely, as many cores as are in your VM. If you are using MSSQL in a private web application, you may choose CALs. However, if you do so, it doesn't matter how users authenticate. Each user or device needs a license. Microsoft is very clear about this. User count is just that: the number of users using the database, either directly, or through a web application which does everything through a single set of credentials. It's not system accounts. It's users or devices. The same applies for Windows Server licenses. It doesn't matter how the user authenticates. If they are using a web-app hosted on a Windows Server, and that web-app is not publicly accessible (a login page does not count), then you need a CAL for each user or device.

123 points by daigoba66 13y ago

MS is not changing their tack because there is a vibrant big business community with budgets that depend on MS SQL and it fills the need. Which Open Source DB does everything that MS Enterprise SQL does and why aren’t you using that instead?

BrentOzar 13y ago
> Which Open Source DB does everything that MS Enterprise SQL does and why aren’t you using that instead? I used to think the replacement wouldn't have to do everyone MSSQL does - after all, nobody uses all of the features. But getting a core subset often isn't enough either - for example, Windows Azure SQL Database supports a core subset of features and datatypes, but I still constantly hear people say it's not enough to migrate their apps.
- xradionut 13y ago
  We were skeptical of Azure before NSA revelations, but now; "Hell No!". Besides that, Microsoft has pretty well pissed off all but the most entrenched developers.
  - recursive 13y ago
    It seems to me that the group of "most entrenched developers" is still pretty big. In real life, it's the most common kind I see.
- jaynate 13y ago
  If you think lock in on SQL server is bad wait til you're locked in on azure.
elchief 13y ago
Postgres is not there yet, but getting closer. (Proper) materialized views coming in 9.4.
rbanffy 13y ago
> Which Open Source DB does everything that MS Enterprise SQL does They shouldn't fear a competitor that does everything they do. They should fear a bunch competitors each doing a set of features a given customer needs. SQL Server is Microsoft's best software product, but why would I even bother with it since both MySQL and PostgreSQL do everything I need from an RDBMS, for a tiny fraction of the TCO and with comparable if not better performance and much better platform support? And why would I even consider it when non-relational datastores are a much better fit for most of my data? It makes sense if you are into Windows, but makes no sense whatsoever if you are not.
Farox 13y ago
That's the thing. It gets the job done and if you need a dev to develop a certain feature that MS SQL has you recouped your licence costs right there. This does suck for small/mid sized businesses though. :/

epochwolf 13y ago

This won't change because large companies don't like open source.

kaoD 13y ago
Not my experience. Government dislikes open source (you know, communists).
wmf 13y ago
At these prices it seems like you could take Postgres, wrap it in a support contract, and call it EnterpriSQL and still charge less than MS.
- bsg75 13y ago
  http://www.enterprisedb.com/ http://www.enterprisedb.com/
- daigoba66 13y ago
  Someone beat you to it: http://www.enterprisedb.com/ http://www.enterprisedb.com/
  - MichaelGG 13y ago
    Actually, most of their prices are "Call us!"[1] Looks to be at least $4000/socket/year. So, more money than SQL Server Standard, less than SQL Server Enterprise. 1: http://www.enterprisedb.com/products-services-training/subscriptions http://www.enterprisedb.com/products-services-training/subsc...
    - piggity 13y ago
      SQL Server Standard is $2K per core - with > 4 cores per socket, things flip around a bit. I'm not a SQL licensing guru; so perhaps I'm confused as well...
      - 13y ago

taude 13y ago

We figured out we weren't using most of SQL Server's features and didn't want the mental overhead of MSFT licensing. We switched to Postgresql and haven't looked back.

BrentOzar 13y ago
How big was the application (like how many developers were involved in the migration, data size, etc)? How long did the migration take? Was the business okay with a feature freeze during that time, or did you take advantage of the switch to rewrite it too? Did you have multiple connected systems, like ETL processes that also hit the database for other reporting systems? Always curious about these kinds of issues because they seem to be what's holding businesses back.
- deleted 13y ago
  [deleted]
- Herbert2 13y ago
  I went through a similar switch from MSSQL to postgres. We moved 7 applications of various sizes over and the biggest hurdle was office politics. The migration never took more than a day for each app. ETL and reporting were not affected since we had previously integrated messaging (rabbitmq) pretty heavily into every system at the company and ETL gets their data from there. We also switched the DB servers to over to linux and got a huge performance boost from that. We did rewrite some stuff, but that was mostly to take advantage of postgres features like array types (instead of a article tags table, you'd have an tags array) which we used to speed up some applications. One we sped up by a factor of 10, just by using array types and offloading json generation to postgres: select array_to_json(array_agg(row_to_json(t))) from ( some query ) t
- taude 13y ago
  It was several smaller apps written with most logic in the app (Hibernate) and had very little T-SQL/stored procs. We also weren't using any of the add-ons like SQL reporting, etc. The big commercial DBs have the advantages when you start using their reporting services, BI/OLAP stuff...and likely when you have several different groups as stakeholders in the process...

yuhong 13y ago

I wonder which edition StackExchange use?

daigoba66 13y ago
I'm fairly certain they use Enterprise because they make use of AlwaysOn Availablility Groups.
- BrentOzar 13y ago
  Before AGs came out, they used a custom log shipping script for DR, and mirroring for the critical DBs. I don't think they would have gone Enterprise just for the AGs, but since they were on EE anyway, AGs were an easy decision.
BrentOzar 13y ago
> I wonder which edition StackExchange use? Enterprise for the memory and the ability to do online reindexing. They were in the BizSpark program, so they basically got free licensing.
- yuhong 13y ago
  And they are on Software Assurance, right?
  - BrentOzar 13y ago
    Yes, when you exit BizSpark, you can just start paying maintenance on the servers you were using during the program.

trackztar 13y ago

Didn't read. Used the word Seriously in the first paragraph. Grow up noobs. Holy fuck!

BrentOzar 13y ago
> Used the word Seriously in the first paragraph. Actually, that was the second paragraph.
- chris_wot 13y ago
  Don't feed the trolls.

corresation 13y ago

I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).... SQL Server has been getting much more expensive because their current userbase is captive (meaning the cost, complexity and risk of migrating to another database system is enormous because of a heavy integration with SQL Server specific features). I don't imagine a large number of new systems are being built around SQL Server, apart from those at shops that are already captive. It is an excellent database, but I do get a chuckle that by far the greatest benefit being pushed for SQL Server 2014 is in memory tables (which is something that SQL had -- at least for temporary tables -- back in the 6.5/7.0 era, but then had removed). While it is hardly identical, an approach we did on one team is to have SQL Server take 64GB (note that it is per instance, and most server deploys see many instances on a single server, so that isn't quite as prohibitive as it might sound) and then have an enormous RAMDISK on which tempdb would be created. Made use of the RAM, and saved enormous amounts of IO (tempdb is the weak link of almost all SQL Server platforms, as everything hits it, especially if you make use of snapshot isolation / row versioning).

BrentOzar 13y ago
> I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)... http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)..... Great catch - fixed, thanks. > greatest benefit being pushed for SQL Server 2014 is in memory tables (which is something that SQL had back in the 6.5/7.0 era, but then had removed) Sorta kinda - back then, we had DBCC PINTABLE, and it would cache the data, but it would still require that the transaction logs and data pages be written to disk. Now with Hekaton, you can specify that a table never hits disk, ever. (Don't get me wrong, I don't think people are going to adopt that particular feature due to other limitations around datatypes and supported syntax.)
- corresation 13y ago
  I was not at all clear, but sorry I was speaking of temporary tables, or basically anything intended to be ephemeral. Back in the 6.5 days you could flag tempdb as being RAM resident automatically - http://support.microsoft.com/kb/115050 http://support.microsoft.com/kb/115050. Load up some global temp tables on db startup and rock and roll. It made tremendous sense because that's often an area of enormous churn (I saw a ServerFault post or something where someone did this and saw 30 iops to it, wondering why it didn't improve their system. That is nothing. One product I worked on saw literally hundreds of thousands of IOPS, and everything that sped tempdb sped the world).

jaynate 13y ago

For years Microsoft-sponsored SQL Server consultancies have been telling us to use the database to its fullest extent. Use Stored Procs and non ANSI SQL features, they're fine! Logic in the database? We love that. That's what ISVs and Enterprises are being told to do and that's exactly what they've done, at least the ones who couldn't see Microsoft's business angle. Now many are left to choose to either accept Microsoft's ever-increasing licensing costs or a high cost to switch. I agree with @BrentOzar, time to find other options.

sehrope 13y ago
I see nothing wrong with using stored procs or non-ANSI features. You just need to know what you're getting into. The same goes for any database. Being database agnostic is great but so is actually finishing what you're working on and having it run efficiently. If DB specific features get you there and you're willing to accept the terms of being locked in then it's a sound decision. For our product we use Postgres as the app's database and happen to use some Postgres specific features like stored procs and hstore[1]. The latter in particular is not ANSI at all. There is no equivalent at all in other databases and migrating usage of it to another DB would be a real pain. I know we're tied to Postgres and we're ok with that as it's a joy to use and let's us spend our time elsewhere. You use database specific features because they're useful, not because you're forced to. Of course I'll concede the point that it's a bit apples and oranges to compare being "stuck" on Posgtres vs a commercial closed source stack. [1]: http://www.postgresql.org/docs/9.2/static/hstore.html http://www.postgresql.org/docs/9.2/static/hstore.html
- eksith 13y ago
  "...so is actually finishing what you're working on and having it run efficiently." A "finished" product is usually a dead one and efficiency is relative. You've seen that incremental improvements to the entire stack can have far greater impact than fiddling with just the storage end alone after all. A product never actually stops evolving if it's going to stay competitive and make money. But I'd say staying with Postgres is one of the best decisions you've made. Besides being a joy, as you say, you can be fairly confident that it won't suddenly become broken, become proprietary and best of all, become an order of magnitude more expensive for the same capability.

vyrotek 13y ago

My company went through BizSpark 3 years ago. We're now running everything on Windows Azure. No complaints here. Sure, I'd like SQL Azure to have a few more features but there haven't been any deal breakers yet. C# is by far my most favorite programming language and is probably what keeps me coming back to .NET & Sql Server. I must admit though, I'm very intrigued by a lot of cool features found in PostgreSQL. Specifically HStore.

xradionut 13y ago
Chances are you are not dealing with banking, insurance or medical data while running on Azure. After the last several weeks, we had to assure clients, that none of the data is stored on Azure. Plus with the standard version of SQL Server or Azure you don't get full auditing or encryption.
- vyrotek 13y ago
  That's correct. I actually used to work for a company which built health information systems for hospitals so I'm aware of the hurdles there. They were .NET based too. Hospital network contracts pay very well but they are absolutely the worst customers.
joseph_cooney 13y ago
Postgres works pretty well with C#. I've used Dapper + Postgres stored procs very happily.

akurilin 13y ago

Brent, to which db flavor do you recommend people migrate? Thoughts on Postgres? Btw, loved your vlog post on scaling SO's db layer.

BrentOzar 13y ago
> Brent, to which db flavor do you recommend people migrate? Thoughts on Postgres? I'm not the right guy to ask on that one - I just don't have enough experience with alternative platforms. You're in the right forum though - as long as you follow the news on HN, you'll do a good job of choosing the right data storage platform for your needs.
- akurilin 13y ago
  I'm thinking your post would benefit from a recommendation as far as the "next closest thing" to MSSQL, since you're exhorting DBAs to make a transition, and minimizing friction would be an important aspect of it.
  - BrentOzar 13y ago
    > I'm thinking your post would benefit from a recommendation as far as the "next closest thing" to MSSQL Then by all means, add it in the comments and I'd be glad to point to it. I'm just not qualified to make that recommendation myself. My experience with other platforms amounts to importing data into MySQL for WordPress, heh.
- statictype 13y ago
  I wonder if that was sarcasm :) If you spend too much time on HN, you'll end porting your RDBMS to Mongo DB or Voldemort.
  - akurilin 13y ago
    Hipster News. I joke I joke, right tool for the right job, right?
13y ago

ivanbrussik 13y ago

saved this post for just another reason why open source is going to take over the world

CurtMonash 13y ago

64 GB of RAM is 100,000 times more than what Microsoft used to think people needed. :D Seriously, Oracle's biggest point of losing customers is when it's time to upgrade from Standard Edition to Enterprise Edition ... or else pick an alternative. I imagine something similar is true for Microsoft SQL Server.

MichaelGG 13y ago

The funny thing is how Microsoft used to make fun of Oracle for being restrictive on the CPU licensing types. How "hardware improvements are for our customers' benefit". Now? You pay differently depending on AMD/Intel and type of chip. Oh hey, just like Oracle. It's hypocritical, but I guess it's also just business. At any rate, like this post says, whatcha going to do? The ease of use of SQL Server is fantastic. As much as I would like to use Postgres (despite the HA story looking very confusing, and lack of basic things like materialized views), the tooling is lightyears behind Microsoft's. The fact that I'd truly need to become a DBA to properly use Postgres (oh great, an ini file with poorly documented settings!), whereas I can fumble my way through SQL Server... that's worth a lot.

xradionut 13y ago
You have to be a DBA to properly use SQL Server too. Yes you can stumble your way through wizards and SSMS, but you will get tripped up eventually if you don't spend some serious time hitting the books and blogs. (Recovery model, what's a recovery model? Why's my disk space gone?)
- chris_wot 13y ago
  (Tempdb, what's tempdb? Clustered index, what's that and why the hell do I need one? Escalated locks - what the hell? Isolation modes, why are they important?!?)
  - taspeotis 13y ago
    I'd be happy if the outsourced developers I work with knew what an index was, let alone whether it's clustered or non-clustered. It would be refreshing to see seeks instead of scans in the execution plans...
- MichaelGG 13y ago
  Well I'm doing alright with 100s of GBs of SQL DBs, replication, tx log shipping, HA clusters, etc. and I wouldn't consider myself a DBA. Basic stuff like transaction log management (recovery model) hardly qualifies me as a DBA. We're both right depending on the value of DBA. I don't think the argument can be made that SQL Server will require vastly less effort to figure out for the majority of cases (and edge cases have people explaining things assuming you're not an expert).

dschiptsov 13y ago

2014 Standard Edition only?))

forgotAgain 13y ago

I wonder if those working in corporate IT realize that they're competing with Microsoft for money. Most corporations see IT as an expense center. It's not a place where the business make money. It's simply a place where they spend it. You can argue the validity of that idea but whether it's right or wrong doesn't matter. It's the way most businesses think. Businesses, of course, try to limit expenses. They especially try to limit any increase in expenses. So basically there is a certain sized pile of money for IT to work with. If the costs of Microsoft infrastructure goes up, the difference is made up by cutting another part of the pile. The part of the pile that can be most easily managed is salaries.

bWare_iWare 13y ago
Actually Microsoft spend billions on marketing and employ top notch sales people. It is perfectly reasonable to assume they will do a better job of arguing to increase the size of the pile of money set aside for IT then you would. Once the pile of money is increased it is a much easier job to argue for your share of it.
incision 13y ago
>"I wonder if those working in corporate IT realize that they're competing with Microsoft for money." Generally no, but I expect many are quickly realizing it. Microsoft in particular has been very open about trying to sell things like Office 365 and Azure to Enterprises. They aren't afraid to be blunt and say "Hey, you can get rid of your staff if you go with this". It's an odd relationship. In my experience, many in corporate IT are perfectly happy to be simple facilitators - shoppers basically. I believe the perception is "It won't be me", that being increasingly beholden to a vendor might prevent the creation of new positions or filling vacancies but will never eliminate their own positions. The entire industry of internal IT support has hard times ahead. Anecdotally, I see XaaS being adopted surprisingly quickly. A few big VARs I've talked to have noticed this and have been realigning themselves to sell managed services instead of stopping at planning and implementation as they have in the past.
- corresation 13y ago

HalBerenson 13y ago

I'm pretty sure I wrote about the history of the Enterprise vs. Standard decision making on my blog (http://hal2020.com http://hal2020.com) but I can't find the post right now. I'm one of the people responsible for the original philosophy, and I don't think it's changed much. I'll go back and look for it again. Basically you have three dynamics going on. The reality check of course is that the competition is Oracle and IBM DB2, and to a lesser extent open source databases, and various analytics products. Check out Oracle's price list and SQL Server Enterprise remains inexpensive. And Microsoft has introduced cheaper options to keep "free" open source options somewhat at bay, though the truth is that without multiplatform support there is nothing they can do to really capture that segment of the market. Standard Edition exists because I couldn't convince my then boss that we should bifurcate it into a couple of sensible products, one slightly lower in capability and one slightly higher. The slightly higher one would have been a "Small Business Enterprise" edition that included many of the features of Enterprise but somehow retained differentiation from full Enterprise and would have been dramatically less expensive. I had a differentiation, but I don't recall what it was. The reason the bifurcation was rejected was that Standard was the edition that matched earlier versions and we didn't want to piss off customers by forcing them into a more expensive or less functional edition. And we didn't want to complicate the world with yet another two editions. So the status quo was maintained. BTW, this is a late 90s discussion. The next dynamic is that there are a lot of features which cause a crapload to engineer but don't increase product volumes substantially. This is the primary driver of what becomes a candidate for Enterprise rather than Standard. When you are investing $10s of millions in a particular feature's engineering then you want some way to get a return on that investment. It really is that simple. Almost. There is (or was) a re-analysis each version of what goes into each edition. My philosophy was that you introduce new enterprise features in the Enterprise Edition, then examine moving them into Standard Edition in subsequent releases. So there is a constant stream of new high-end features flowing into Enterprise, then as they become part of the mainstream thinking you push them (or appropriate subsets) into Standard. But that philosophy was never adopted and so the effort seems far more haphazard than I'd wanted it to be. Customer and competitive pressure will result in capabilities being pushed into Standard, but it doesn't seem to happen in a rational way. Max memory size and high-availability features were the original differentiators when Enterprise Edition was introduced as a mid-life kicker for SQL Server 6.5. In the case of memory it was an actual technology differentiator back in the mid-90s on 32-bit machines. That it has survived through the 64-bit transition is shocking. But the reality check is likely that very few servers actually have more than 64GB,despite today's hardware prices, and thus Microsoft sees it as an acceptable differentiator. High Availability should remain a differentiator, though a simple subset does need to be in Standard. That the current subset is actually deprecated is, ummm, looney. Customer demand for capabilities in editions other than Enterprise, or competitor moves, will lead to Microsoft changing the balance between Standard and Enterprise. But it isn't a few sophisticated DBAs/developers/etc. calling for the change. Or a niche or flash-in-the-plan competitor. It is an actual shift in market dynamics.

programminggeek 13y ago

I'm sorry, but if you have to read a feature grid to figure out what a product does, you've already lost. Also, isn't this when you look at Postgres or MySQL and go... licensing cost, what licensing cost?

samgreene 13y ago
I don't think this is a useful mindset. Grids are very handy comparison tools. Also, licensing costs are only part of the overall cost of running a service. 'Serious businesses' will not run on a piece of software that is not supported by either a vendor or their own staff. Not everyone is comfortable fixing bugs in their DB platform, or relying on the community to do it.

teilo 13y ago

I just took my first plunge into the caustic waters of SQL Server, being forced there because my company is deploying a vendor-supplied shipping system that only runs on it. Since the data set would be growing beyond 5GB, SQL Server Express was not an option. Coming from a Postgres world, Linux, Java world, the whole subject makes me sick. I was disgusted to discover that my 200-strong Mac shop would have to spend $7,000 on SQL Server. I was even more disgusted when I discovered that Microsoft no longer lets me run it on the server I purchased, a dual 6-core machine, because now EVERY core is licensed individually, and you are FORCED to license every core in the machine. You can't just license some of them. That forced to run it in a VM, which is just asinine. And THAT means that I also had to purchase an additional Windows Server license to run a SEPARATE VM for IIS/ASP.NET. And THEN (and this is the part that offends me most of all), I have to purchase a CAL for every damn machine that will be using the shipping system, because it's not a public website, but a private web app.

WayneDB 13y ago
SQL Server 2012 Express allows up to 10GB databases. Also, you should only need CALs if your private web app is using Windows Authentication to make each database request on a per-user basis. From http://www.mssqltips.com/sqlservertip/2942/understanding-the-sql-server-2012-licensing-model/ http://www.mssqltips.com/sqlservertip/2942/understanding-the... "you have two choices: purchase per core licenses at $1,793 or purchase a server license at $898 and client access licenses at $209 per client." So, if everyone is going through the web app and the web app is not using Windows Auth (you certainly don't sound like you need to use that) - You should be able to get SQL Server for ~ $1107.
- teilo 13y ago
  The minimum cores you can license on MSSQL, whether on bare metal, or a VM, is 4. Wherever you run it, you must license all cores available to the OS. Had I chosen to run on bare metal, I would be forced to license 12 cores (6-core Xeon CPU x 2). This app requires SQL Server 2008 R2, so the 4GB limit applies. But I can't buy 2008 R2 anymore directly. I have to license 2012, and use my downgrade rights. You are wrong on the Windows Authentication requirement. This has nothing to do with anything. For MSSQL, you must license the database per core if you are using it in a public web application, because you have an arbitrary number of users. And you must license all cores in the machine. The VM is the only way to license only some cores, namely, as many cores as are in your VM. If you are using MSSQL in a private web application, you may choose CALs. However, if you do so, it doesn't matter how users authenticate. Each user or device needs a license. Microsoft is very clear about this. User count is just that: the number of users using the database, either directly, or through a web application which does everything through a single set of credentials. It's not system accounts. It's users or devices. The same applies for Windows Server licenses. It doesn't matter how the user authenticates. If they are using a web-app hosted on a Windows Server, and that web-app is not publicly accessible (a login page does not count), then you need a CAL for each user or device.