May 4, 2011 at 2:15 pm
Everybody,
I have to migrate T-SQL stored procedures and functions from SQL 2000 to SQL 2008. Is there any list of incompatibility issues or a tool that I can use to find the SQL 2000 code that will not work in SQL 2008?
I'm not asking about all differences between SQL Server 2000 and 2008. I need to identify only the issues while migrating my T-SQL code (around 700 objects) quickly.
Many thanks for your help.
Lev
May 5, 2011 at 5:38 am
This should be what you're after:
May 5, 2011 at 5:42 am
Nuts. I'm slow. Yes, the Upgrade Advisor is the best source for this information. It's the required first step to perform this kind of upgrade anyway. Follow Microsoft's documentation. It's good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2011 at 7:30 am
Thank you for your advice.
I tried the Microsoft SQL Server 2008 Upgrade Advisor and it helped. But it turned out it couldn't identify all issues. For example, the SELECT statements with SQL 2000 syntax for HOLDLOCK were not discovered with the Upgrade Advisor, e.g.
SELECT @last_used_nbr = last_used_nbr
FROM next_workorder_nbr HOLDLOCK
SQL 2008 requires the statement to be like that:
SELECT @last_used_nbr = last_used_nbr
FROM next_workorder_nbr WITH (HOLDLOCK)
Are there alternative tools? Is there a list of the differences made for T-SQL in SQL 2008 vs. 2000?
Thanks again,
Lev
May 5, 2011 at 7:45 am
I doubt it's 100% perfect, but in that specific example, it's deprecated rather than incompatible, so it will work if the compatibility of the database was still set to 80 after you upgraded (which it would be by default)
May 5, 2011 at 8:17 am
Howard,
That's true that the mentioned statement works with the 80 compatibility mode. The problem is that our server upgrade assumes the 100 compatibility mode to be set. The reason is that with the 80 compatibility we'll lose new features.
That's why I have to repeat my questions:
- Are there alternative tools?
- Is there a list of the differences made for T-SQL in SQL 2008 vs. 2000?
Thanks,
Lev
May 5, 2011 at 8:19 am
yes, it's called quality assurance. It's the part of the plan where you rerun all the parts of your app on a test server and see what breaks.
This is also why I never build an app without unit tests. Makes migration so much easier.
Sorry to be so blunt but there's no shortcut here... only you knows you application. Nobody else on the planet does.
May 5, 2011 at 8:23 am
The lists of deprecated/discontinued features for SQL 2008 are linked from here:
http://weblogs.sqlteam.com/derekc/archive/2008/06/06/60618.aspx
You'll also have to consider SQL 2005's versions of the same lists as you're jumping two versions.
No, there's no alternative tool - in any case, any of these tools are a first pass, not a certification of compatibility. There's no alternative for thorough testing after upgrade.
May 5, 2011 at 8:28 am
You might as well run the R2 upgrade advisor while you're at it... might save you some work on the next upgrade and future devs.
May 5, 2011 at 8:32 am
Howard,
Thank you very much. It helps.
Regards,
lev
May 5, 2011 at 8:35 am
Ninja,
Thanks for your advice. I'll consider it.
Regards,
Lev
May 5, 2011 at 10:23 am
There is no alternative tool. That's the best one.
You will absolutely need to plan for thorough testing. One of the issues we found early on when migrating to 2005, but it was also there for migrating to 2008, was that certain ADO calls that worked in 2000 absolutely did not work in 2005/2008. No amount of database testing identified that. It required us to also test the app. Please take that into account.
If the Upgrade Advisor missed something, I'd strongly suggest you open a Connect item on it. I'll be Microsoft will be reasonably quick to answer those. They want people to upgrade.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2011 at 10:46 am
Grant,
Thanks for your advice. We'll be definitely testing our apps.
What do you mean by opening a Connect item? What is the Connect item? Where should it be opened?
Lev
May 5, 2011 at 11:00 am
lev.gurevich (5/5/2011)
Grant,Thanks for your advice. We'll be definitely testing our apps.
What do you mean by opening a Connect item? What is the Connect item? Where should it be opened?
Lev
It's the official Microsoft bug report tool.
May 5, 2011 at 11:05 am
Thanks. I'll submit an item there.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply