question

oliverjbullock avatar image
oliverjbullock asked

Difference between database features - SQL Server 2008 R2 navtive vs SQL Server 2012 with SQL Server 2008 compatibility set?

I am investigating the impact of upgrading from SQL Server 2008 R2 to SQL Server 2012. To reduce the impact of the upgrade, I am planning to restore / create our application database onto SQL Server 2012 with compatibility level 100 (SQL Server 2008 and SQL Server 2008 R2). Are there any differences in feature support for database running in native mode on SQL Server 2008 R2 vs a database installed on SQL Server 2012 with compatibility level 100 set?
sql server 2012
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
The compatibility level allows the database to effectively operate in certain capacities at a lower version of SQL Server. This is primarily syntax related although there are certain instances where certain portions of the engine will operate differently, such as the query optimizer between SQL 2014 & lower versions. Were you looking for a specific feature/component? Personally, I don't like leaving my databases in a lower compatibility level if I absolutely don't have to. This tends to leave things hanging around. I would recommend that you test the migration by upgrading the compatibility level to SQL 2012 and see if anything in the application breaks. If not, leave it in the 2012 compatibility level. Resource: https://msdn.microsoft.com/en-us/library/bb510680(v=sql.120).aspx Hope this helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.