Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other.

Our requirements:

  • Standard edition (for financial reasons and a lack of need for enterprise features)
  • OLTP workload (this means we don't need the new windowing functions and column store indexes)
  • Database size of 10-100 GB
  • No business intelligence features needed. Only the relational engine is required
  • Synchronous Database Mirroring

Currently, the following reasons are known to me:

SQL Server 2012 Denali

  • Newest version available

SQL Server 2008 R2

  • Proven technology

I can't seem to find a lot of technical reasons to prefer one over the other. Basically, it comes down to choosing proven technology that is running successfully vs. the newest and greatest version available.

What are objective reasons to make the decision?

share|improve this question
I think as long as the per-core licensing change doesn't alter your budget, and you don't have to worry about slow vendor catch-up, using SQL Server 2012 is a no-brainer. This is based on proven technology so it shouldn't be considered a complete re-write and/or a V1. –  Aaron Bertrand Mar 9 '12 at 20:42
Based on my experience with previous three releases, I would wait at least one service pack. With 2008 I waited until R2, and still it has defects. Just from the top of my had: in 2008 R2 I can bypass a trusted FK constraint and insert orphan rows, I can run a script in SSMS and part of it executes against the wrong database. –  AlexKuznetsov Mar 10 '12 at 3:13
@AaronBertrand I think your analogy is false. If you saw one and the same person texting and driving more than once, several times, it's reasonable to conclude that that person in a terrible driver. This is how the OP is generalizing, not like you suggested. –  AlexKuznetsov Mar 10 '12 at 3:18
Alex, let me know when any complicated RDBMS platform ships without any bugs. IIRC your FK repro was pretty convoluted and wasn't a common scenario. Also let me know if you think software companies can't possibly get better over time, or if you expect a slammer-type vulnerability in SQL 2012, then in the version after that, and the version after that... at some point you have to let go of these old wives' tales and take a platform on its own and not judge it by some bug in a previous version... –  Aaron Bertrand Mar 10 '12 at 4:04
The "wait for the service pack" is an old myth and FUD –  gbn Mar 11 '12 at 12:25
show 10 more comments

migrated from stackoverflow.com Mar 9 '12 at 20:26

This question came from our site for professional and enthusiast programmers.

4 Answers

up vote 48 down vote accepted

Everybody's excited about AlwaysOn and ColumnStore, but many of the benefits of SQL Server 2012 are not exclusive to high-end editions. I don't want to sound like a spokesperson, but I've given plenty of presentations on SQL Server 2012 and I think it has a lot to offer at whatever edition suits you.

  • Partially Contained Databases which allow you to move databases between servers or environments with a few less shackles (namely server-level logins and server collation dependencies - future versions will handle thornier items like linked servers and Agent jobs).

  • Management Studio is now a much better tool, aligned with Visual Studio. IntelliSense is better and a whole bunch of other features make editing easier. Now of course you can have 2008 R2 on your server and use the 2012 version of SSMS, but I'm not sure how that works licensing-wise, and some shops don't want mixed versions (I prefer having the most recent tools on my workstation even to manage downlevel servers). I blogged about the changes early on, when there were still bugs, so please ignore the negatives since most or all are fixed as of RTM. I shudder now when I have to use an earlier version of SSMS.

  • Metadata enhancements allow you to inspect resultsets of objects and ad hoc queries, and also allow you to better shape the output of queries.

  • Custom Server Roles allow you to define a much more granular set of permissions for users at a role level instead of granting/revoking one by one, or just giving in to complexity and giving them sysadmin.

  • FileTable lets you manage a folder like a table of documents, but still have external control over the contents (so imagine being able to do this with T-SQL, and imagine how hard it would be to do in cmd or PowerShell: UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = 'Bob' AND Created < '20100101';)... think FileStream meets WinFS and gets some usability to boot.

  • T-SQL Enhancements allow you to do many things that were a pain in previous versions:

    • THROW (think of it as re-raise)
    • OFFSET / FETCH (simpler, ANSI-standard paging)
    • SEQUENCE (centralized IDENTITY mechanism, like in Oracle)
    • Windowing/Framing enhancements (various things here, such as awesome running totals performance)
    • IIF() / CHOOSE() / CONCAT() / EOMONTH()
    • Date/Time Constructors (e.g. DATETIMEFROMPARTS) similar to DateSerial in VB
    • PARSE() / FORMAT() - like their .NET counterparts
    • TRY_CONVERT() / TRY_PARSE() - return NULL if CONVERT / PARSE fail
  • Extended Events has an enhanced UI for configuration / viewing, and finally completely covers trace / audit functionality (including much better causality tracking).

  • Lots of new DMVs, system procedures and ShowPlan enhancements for diagnostics and performance troubleshooting. Also take a look at what CSS is calling "The Black Box Recorder."

  • Server Core allows you to run on a bare-minimum server without all the UI components (smaller surface area means it is more secure, and reduced maintenance since less parts of OS are subject to Windows Update).

  • Full-Text Search gets some important underlying performance enhancements, as well as semantic search (think keywords) and customizable proximity / NEAR.

  • AWE is no longer supported, meaning your SQL Server instance on x86 with 32GB of RAM is only going to be able to use 4GB - so you will finally have motivation to get off your old 32-bit hardware.

share|improve this answer
Re: the powershell comment, it's still pretty simple: gci c:\users | where-object {$_.Author = 'Bob' -and $_.creationdate -lt '1/1/2010'} | %{$_.Readonly = 1} or something similar - but not as simple and readable as the 2012 option! –  JNK Mar 9 '12 at 20:44
The great thing about PS is you can do anything (almost) in one line. The bad thing is it's hard to read that line :) –  JNK Mar 9 '12 at 20:47
Is SSMS 2012 that much better?? –  Thomas Stringer Mar 9 '12 at 20:56
Yes, I love it. I should do another blog post about it. A few highlights: snippets are awesome, IntelliSense is way better, region editing is very powerful, tab strip is great for multi-monitor, and there is zoom built in. –  Aaron Bertrand Mar 9 '12 at 20:57
I could also just summarize that bullet as: "x86 is going to suck even more than it already does." –  Aaron Bertrand Mar 9 '12 at 21:57
show 8 more comments

The following is just a few examples regarding "actual evidence for or against reliability in the first version of any new release", as requested. This is not meant to be a complete analysis, but rather a suggestion on what you might want to research.

You can google up "List of issues that are fixed by SQL Server 2008 Service Pack 1" and "List of issues that are fixed by SQL Server 2008 Service Pack 3" on MSDN website. Compare the number and severity of issues in both lists. IMO the first list is longer, and it has more items that could ruin my day, such as:

  • Error message when you connect to a named instance of SQL Server on a client computer that is running Windows Vista or Windows Server 2008
  • The Log Reader Agent skips some transactions when the Log Reader Agent runs to replicate transactions
  • Error message when you run a query that involves an outer join operation in SQL Server 2008
  • Error message when you perform an update or a delete operation on a table that does not have a clustered index created in SQL Server 2008
  • A query that uses parameters and the RECOMPILE option returns incorrect results when you run the query in multiple connections concurrently in SQL Server 2008

Let us drill down one more level and consider just one command, the MERGE. It was released as part of SQL 2008 with several problems, described in the following links:

As such, at the time of the original release of SQL 2008, I decided against using MERGE. I am using MERGE a lot now, on 2008 R2, and I think it is a really great feature.

Edit: here is the list of defects in SQL 2012 that were recently fixed. Hope it helps.

Another edit: I have chosen MERGE for more detailed analysis, because it is a very important improvement. In fact, it is a major step in catching up with Oracle, and it does improve our productivity. As such, MERGE has been marketed a lot at the time of SQL 2008 release. Yet it was not completely ready to use in serious production systems when it was originally released, and there was no easy way to know it from the presentations/articles/blog posts and such.

Similarly, snapshot isolation is an awesome new feature which just works, but invoking scalar UDFs in CHECK constraints does not work in all cases and as such should not be used in production when we need data integrity. However, both new features were recommended in "What is new in SQL xxxx" presentations, as well as in books, articles etc, and with similar enthusiasm.

We need to be very careful with new features - not all of them are going to be useful/reliable/performant.

share|improve this answer
I saw the list. Didn't really perceive any as show-stoppers, and almost all issues mentioned affect both 2008 R2 and 2012. –  Aaron Bertrand Apr 12 '12 at 14:25
Here's another potential MERGE bug that causes deadlocks. –  Nick Chammas May 2 '12 at 18:43
@NickChammas yes, right, thanks for mentioning this. We are using sp_getapplock to get around. –  AlexKuznetsov May 2 '12 at 20:45
Seeing how one of my posts is used as a reason to avoid new stuff above, let me add a counter-example: sqlblog.com/blogs/paul_white/archive/2011/12/04/… For my part, I would choose 2012 RTM every time, as I find Aaron's arguments much more persuasive, on balance. –  Paul White May 3 '12 at 8:50
add comment

One point that hasn't been mentioned here is completely irrelevant to the feature set. If you're doing a new build you can put off a database upgrade for quite a bit longer, so this will save in migration costs.

For a greenfield project you have some breathing space to work around bugs and raise them with the vendor if they do turn up, so it's not a completely uncontrolled process. I was involved in one of the first data warehouse projects on SQL Server 2005 just as it went to RTM and we got away with it.

If the feature set of 2008R2 will do what you want then the decision is down to some risk of bugs/workarounds vs. the value of postponing the necessity to upgrade and saving an upgrade cycle.

share|improve this answer
add comment

When you are buying new the choice is far different than when you are considering upgrading. Buying new it is my belief that you should always buy the newest version you can get. 2008 version will be no longer supported far earlier than the 2012 version. Better to start new with the lastest as you will be using this backend for a long time.

As to the need to the first service pack, it will be out before you know it and since you are doing new development, the problems it fixes will likely not affect you as much a legacy database with millions of records would face.

Now if you are just getting a new server but putting an old database on it, then the question becomes what are you upgrading from? If the database is already a 2008 database, it will be significantly less risky to use the same version. If you are upgrading, check to see if you can upgrade directly to 2012 from your version.

share|improve this answer
There is no upgrade. This is a new app on new hardware. –  usr Mar 9 '12 at 22:28
right.......... –  garik Jul 1 '13 at 9:22
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.