February 5, 2015 at 9:51 am
I just restored a 2005 db to 2012 and had to change it's compatability to 2012 because of some replication issue. My question is what would be the reason to not alway change the compatability to the highest level that the new db was restored to? That being said why wouldn't it automatically change to 2012. Is it because if u change it to 2012 u cant restore it back to the 2005 source if u had to?
February 5, 2015 at 10:58 am
Only reason would be if you want some older behaviour of the query execution engine. An example there was that the *= join syntax is only available in compatibility 80 and lower.
As for restoring back to SQL 2005, that's not possible at all. A database attached to a SQL 2012 instance is a SQL 2012 database and cannot be restored to an older version
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2015 at 1:49 am
as Gail stated it is only intended to mimic sql server behaviour of an older level.
It is supposed to be used for a "short" time to be able to move on ( upgrade ) with the database engine and fix applications afterward ( which isn't the optimal way IMHO ).
Best is to use SQLServer upgrade advisor (MS freeware) to try to get an inventory of potential issues and fix those up front on a testing environment, running the db in the instance dblevel.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 24, 2015 at 5:32 am
GilaMonster (2/5/2015)
Only reason would be if you want some older behaviour of the query execution engine. An example there was that the *= join syntax is only available in compatibility 80 and lower.As for restoring back to SQL 2005, that's not possible at all. A database attached to a SQL 2012 instance is a SQL 2012 database and cannot be restored to an older version
So I migrate a 2008R2 database to 2014 and do not alter the compatibility mode. I have in end effect a database running on a 2014 Server that emulates the 2008R2 Environment.
I have two questions:
1. When an application requires a 2008R2 database is it enough to install it on 2014 and reduce the compatibility Level?
2. In your experience, how do vendors view this Approach?
Regards,
Kev
February 24, 2015 at 5:36 am
kevaburg (2/24/2015)
So I migrate a 2008R2 database to 2014 and do not alter the compatibility mode. I have in end effect a database running on a 2014 Server that emulates the 2008R2 Environment.
No, you have a SQL 2014 database that in some aspects of query behavior behaves like a SQL Server 2008 database. It still allows most or all of the new 2014 features, it's still a SQL 2014 database in all aspects
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 24, 2015 at 5:41 am
GilaMonster (2/24/2015)
kevaburg (2/24/2015)
So I migrate a 2008R2 database to 2014 and do not alter the compatibility mode. I have in end effect a database running on a 2014 Server that emulates the 2008R2 Environment.No, you have a SQL 2014 database that in some aspects of query behavior behaves like a SQL Server 2008 database. It still allows most or all of the new 2014 features, it's still a SQL 2014 database in all aspects
Ho-hum.....it was worth a go.....
Many thanks for the quick answer.
Regards,
Kev
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply