July 26, 2011 at 9:16 am
What are the pitfalls and downsides to keeping databases in 90 compatibility mode after upgrading databases from SQL 2005 to SQL 2008 R2?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 26, 2011 at 9:19 am
You can't use the new SQL 2008 features in it, like Merge, Table Value Constructors, Table Variable Parameters, et al.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 1, 2011 at 12:35 pm
Thanks for the reply. A migration from 2005 to 2008R2 is planned but it was proposed that all DBs stay in 90 mode to expedite the actual upgrade, i.e. leaving full application regression testing for a second/later phase. I have heard of many issues running 80 mode on 2005, but have not seen substantial claims of issues using 90 on 2008R2. I have no fundamental objection to the idea of using compat mode as a bridge to get to 2008R2 provided there is a plan to move to 100 mode. I am just wondering if I need to raise any concerns for consideration. I read that compat mode mostly affects the SQL parser so determines language features you can use, as you stated, however real drawbacks are not always listed in the documentation. App testing is my biggest concern. In your opinion does using compat mode on the new platform obviate the need for app testing?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 1, 2011 at 12:39 pm
PS You provide quality advice here so I'm asking for an opinion, even if it's just an abstracted thought on the topic based on your overall knowledge and experience. I won't be looking to hold you to anything 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 4:06 am
Having done an upgrade from 2000 to 2005 with a similar procedure, I can say that leaving the database in compatibility mode for an older version does NOT ensure everything will work the same way. In particular, the application in question was using the "TOP 100 PERCENT" trick to get ordered views in SQL 2000, and that didn't work in SQL 2005 even with the database in compatibility mode 80.
Therefore, I would say that you can't rely on your app still working when you do this upgrade--you still need to test before making the whole thing live!
August 2, 2011 at 9:13 am
I just recently upgraded a database from SQL 2000 (80) to SQL Server 2008 R2 with a process that I created in T-SQL. It was well worth the effort, and none of the previous code broke (i.e. Stored Procedures, Triggers, et al).
I am preparing to do the same process to a SQL Server 2005 (90) database this week. The main things that have already been described to you here are the largest pitfalls you could encounter later on. Thorough testing is a must.
August 2, 2011 at 9:22 am
paul.knibbs (8/2/2011)
Having done an upgrade from 2000 to 2005 with a similar procedure, I can say that leaving the database in compatibility mode for an older version does NOT ensure everything will work the same way. In particular, the application in question was using the "TOP 100 PERCENT" trick to get ordered views in SQL 2000, and that didn't work in SQL 2005 even with the database in compatibility mode 80.Therefore, I would say that you can't rely on your app still working when you do this upgrade--you still need to test before making the whole thing live!
Thanks Paul. I appreciate the response and you sharing your experience. I do agree that "an older version does NOT ensure everything will work the same way" but features can prove to be better from one version to the next, just as they can regress too. Your reply reminded me of the Mark Twain quote in my signature which is why I posted this topic. There is extensive information about the woes associated with running databases in 80 compat on SQL 2005 after upgrading from SQL 2000...however I cannot find the same examples when it comes to the specific situation I asked about. The goal is to cite specific examples of functionality that may be problematic before I object to the proposal, or add the condition that we must do a full regression test in which case we might as well just go straight to 10 mode, which is my preference.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 9:24 am
Rich Yarger (8/2/2011)
I just recently upgraded a database from SQL 2000 (80) to SQL Server 2008 R2 with a process that I created in T-SQL. It was well worth the effort, and none of the previous code broke (i.e. Stored Procedures, Triggers, et al).I am preparing to do the same process to a SQL Server 2005 (90) database this week. The main things that have already been described to you here are the largest pitfalls you could encounter later on. Thorough testing is a must.
Thanks for the reply Rich. Are you running your databases in 80 compat mode on the SQL 2008 R2 instance?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 10:09 am
I hope you did run the upgrade adviser tool. It will point to potential issues and may be a great to prevent failures and prepare migration projects.
Don't start without it !
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
August 2, 2011 at 10:17 am
ALZDBA (8/2/2011)
I hope you did run the upgrade adviser tool. It will point to potential issues and may be a great to prevent failures and prepare migration projects.Don't start without it !
Thanks ALZDBA...absolutely we have! It showed us a few minor buggers for us to fix...mostly simple syntax fixes related to table aliases.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 10:33 am
Then I see no issue(s) on having it in 90 mode for a transition time span 😉
Keep in mind to keep a backup of your SQL2005 db created from a SQL2005 instance because once the files are attached to SQL2008 or >, you cannot attach them to SQL2005 anymore.
Just for safety sake.
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
August 2, 2011 at 10:37 am
I just hate that you used the work attached here.
I'd DEFINITELY go the backup / restore path here. Much safer and cannot fail, as in lose the whole db, if something goes wrong in the upgrade.
August 2, 2011 at 12:54 pm
ALZDBA (8/2/2011)
Then I see no issue(s) on having it in 90 mode for a transition time span 😉
If we use compat mode (I'm still trying for a full regression test and 100 mode right off the bat) then "for a transition time span" is what I'll be asking for, i.e. a plan and a pre-determined date to aim for to change from 90 to 100.
Keep in mind to keep a backup of your SQL2005 db created from a SQL2005 instance because once the files are attached to SQL2008 or >, you cannot attach them to SQL2005 anymore.
Just for safety sake.
No doubt about it. We'll have new machines in a completely different geographic location hosting 2008 R2 and will be using the backup/restore method to upgrade our databases. I think every other step in our upgrade/migration plan is to take a backup 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 2, 2011 at 12:59 pm
opc.three (8/2/2011)
Thanks for the reply Rich. Are you running your databases in 80 compat mode on the SQL 2008 R2 instance?
No, the process actually takes the data and moves it to a temp table...It then creates the table(s) and other objects in 2008 R2 and then copies the data back over. I chose to go this route, and one of the benefits that came from it is that it is hard coded and will catch any schema changes that may have taken place from the time you write it until final implementation (which did happen for me since several months went by from when I first created the process to now).
August 2, 2011 at 2:15 pm
If my perception on the matter is ok running a db in a lower db level than the engine:
- may not produce the same results, sqlplans, .... as with the original platform, so it should be a considered "best effort" approach. So even that will need you to test it to the bone.
- I would suggest to switch to engine dblevel during your tests, you can always put it in lower level if you encounter problems and want to test if these are due to the dblevel.
Keep in mind to apply SP1 !
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply