What is different between SQL SERVER 2008R2 and SQL SERVER 2012

Question

Dear SQL Server expert,

Can you tell be the different between SQL server 2008 R2 and SQL server 2012. I design my product with SQL 2008, but when I buy a lincence, Microsoft said they wont sell it anymore, buy 2012 instead. So I need a proof that our system can run with 2012. i know its backward compatible but I need official proof. Please help me

Answer 1

A few things you'll want to watch out for:

• Changes to memory manager mean some changes to some dynamic management views - http://sqlblog.com/blogs/sqlos_team/archive/2011/01/04/sql-server-memory-manager-changes-in-denali.aspx

• fn_virtualfilestats no longer takes -1 for parameters - http://sqlblog.com/blogs/aaron_bertrand/archive/2011/06/28/sql-server-v-next-denali-breaking-change-to-fn-virtualfilestats.aspx

• you may no longer be able to identify system databases simply by DB_ID between 1and 4 - http://sqlblog.com/blogs/aaron_bertrand/archive/2011/07/08/sql-server-v-next-denali-breaking-change-to-system-databases-database-id-db-id.aspx

• Temp tables now get negative object IDs, so make sure you don't have code that checks for object id > 0 - http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/09/sql-server-11-denali-using-temp-tables.aspx

• sys.dm_exec_sessions has a new column, authenticating_database_id

For an official list (it doesn't include all of the above items, and there may be others that aren't coming to mind):

http://technet.microsoft.com/en-us/library/ms143179(v=SQL.110).aspx

You may also want to check the discontinued features page:

http://technet.microsoft.com/en-us/library/ms144262

And also run the Upgrade Advisor against your instance, this is exactly what it's for:

http://technet.microsoft.com/en-us/library/ms144256.aspx