What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

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 - also see this post)
  • 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. DATEFROMPARTS) similar to DateSerial in VB
  • PARSE() / FORMAT() - like their .NET counterparts (but see this post about the latter)
  • 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.

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:

• An Interesting MERGE Bug,
• SQL Server 2008 RTM MERGE Bug and Fix,
• MERGE bug when Foreign Key constraint is created on non-clustered index (2008RTM),
• MERGE statement bug when INSERT/DELETE used and filtered index,
• MERGE query plans allow FK and CHECK constraint violations,
• MERGE statement bypasses Referential Integrity,
• Parameterized DELETE and MERGE Allow Foreign Key Constraint Violations.

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.

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.