April 24, 2009 at 1:40 am
Dear All,
We are using SQL Server 2000 in our project.
While migration from sql server 2000 to sql server 2005 what are the code level changes required in database objects like function,procedure,view,table,sql query , sql dynamic query etc...
Please advise me for the needful.
Thanks in Advance.
Regards,
Sumanta Panda
April 24, 2009 at 2:36 am
Have you run the SQL 2005 upgrade advisor?
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
April 24, 2009 at 4:36 am
No.
As a developer our job is to focus on code level changes.But DBA will take care server upgradation.
Could you please pointer me any Url or if you have any idea in code level compatibility from migration of sql server 2000 to sql server 2005 then please advise me for the needful.
Thanks for your response.
Please share your input.
Regards,
Sumanta Panda
April 24, 2009 at 6:15 am
Please ask your DBA to run the upgrade advisor on the database that you want to upgrade. It will pick up most (as I recall) of the issues.
Also see here - http://support.microsoft.com/kb/906773
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
April 24, 2009 at 8:41 am
From the upgrades I've done, follow Gail's advice. The Upgrade Advisor will identify all the coding issues on the database side of the upgrade.
However, it's possible to have written a data access layer that won't work with 2005 (I know because two different development teams in my company did it). You will need to test your application against the database after it's upgrade is complete.
"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
April 24, 2009 at 9:41 am
Grant Fritchey (4/24/2009)
However, it's possible to have written a data access layer that won't work with 2005 (I know because two different development teams in my company did it). You will need to test your application against the database after it's upgrade is complete.
Or preferably test it before you upgrade, by setting up the DB on a test server with 2005 on.
Though, I must admit, there's little that gets adrenaline pumping more than application downtime as a result of an upgrade. All those users yelling and screaming.... 😀 😉
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
April 24, 2009 at 11:45 am
Oh, yeah, I wasn't at all clear. I meant that you upgrade to a test server first, of course.
Yeah, it actually would be fun just to tear through the servers running the install software everywhere and then watch the phone light up like a Christmas tree.
"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
April 24, 2009 at 11:48 am
Or preferably test it before you upgrade, by setting up the DB on a test server with 2005 on.
Though, I must admit, there's little that gets adrenaline pumping more than application downtime as a result of an upgrade. All those users yelling and screaming....
I knew you were an adrenaline junky - just knew it...:w00t:
Edited to supply the correct quote - not sure why this happens, but every once in a while it quotes a different post and I miss it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 24, 2009 at 1:08 pm
Jeffrey Williams (4/24/2009)
Or preferably test it before you upgrade, by setting up the DB on a test server with 2005 on.
Though, I must admit, there's little that gets adrenaline pumping more than application downtime as a result of an upgrade. All those users yelling and screaming....
I knew you were an adrenaline junky - just knew it...:w00t:
😀
I actually had that happen when we moved to 2005, though it was not intentional.
All tests on dev were fine, all tests on UAT were fine. We upgraded production to 2005 on the sunday and by 3pm monday the server was virtually dead.
We'd run into a memory-related 'bug' in RTM that only showed up on large servers (20GB+ memory). The dev and UAT servers weren't big enough to show the problem.
Not a fun week.
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
April 24, 2009 at 1:10 pm
Grant Fritchey (4/24/2009)
Yeah, it actually would be fun just to tear through the servers running the install software everywhere and then watch the phone light up like a Christmas tree.
The day before month end, for greater impact.
Preferably with a couple days leave scheduled straight after.
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
April 24, 2009 at 1:20 pm
GilaMonster (4/24/2009)[/b:-D
I actually had that happen when we moved to 2005, though it was not intentional.
All tests on dev were fine, all tests on UAT were fine. We upgraded production to 2005 on the sunday and by 3pm monday the server was virtually dead.
We'd run into a memory-related 'bug' in RTM that only showed up on large servers (20GB+ memory). The dev and UAT servers weren't big enough to show the problem.
Not a fun week.
Oh, we had a similar issue - but, this one was not found in our testing because the testers did not test the full process with all of the data. They tested a very small subset - which worked okay but the same process against the full load of data brought the server to its knees.
Definitely not a fun week - and for us we ended up rolling back to 2000 and that system is still on 2000 with no plans to try it again.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 26, 2009 at 10:29 pm
Dear All,
As your valuable advise(To use SQL Server Upgrade tool) really helped me to finding the necessary changes because of SQL Server 2005 migration.
Apart from that i am facing some problem in my application level java program where i have used sql query.For example *=,=* join syntax are not supported in sql server 2005.
For SQL Server 2005 migration,Could you please guide me what are necessary changes i will do in my java programming level where i have wriitten sql query?
Thanks in advance.
Regards,
Sumanta Panda
April 27, 2009 at 6:46 am
Here's the documentation on it from Microsoft
"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
April 28, 2009 at 12:19 am
Dear All,
As per your advise i am using MS Upgrate tool it will report the details we need to change database level.It is ok to implement it.
But in my java program i am written few sql queries which might problem me.
Please look at the example given below:-
SELECT FIELD_ID, FIELD_ORDER, FIELD_DISPLAY, FIELD_VALUE ,A.STATUS
FROM MRU_LIST M,ABC_USER A --Comma syntax is not supported in SQL Server 2005
WHERE M.FIELD_VALUE *= A.LOGIN_NAME --*= or =* join syntax is not supported sql 2005
AND M.LOGIN_NAME = 'testap1' AND (A.STATUS='A' or A.STATUS IS NULL ) ORDER BY M.FIELD_ID, M.FIELD_ORDER.
Like above two example code level compatibility is not supported while migratiing from SQL Server 2000 to SQL Server 2005.
Please advise me if you know any exact code compatibilty senario while migrating to sql server 2005.
Thanks in advance.
Regards,
Sumanta Panda
April 28, 2009 at 2:28 am
sk.panda (4/28/2009)
Please advise me if you know any exact code compatibilty senario while migrating to sql server 2005.
Did you look at the link that Grant gave?
There are a lot of changes and they are clearly documented. The link that Grant posted it to the "Upgrade Technical Reference Guide", a 350 page word doc that covers, among other things, T-SQl changes.
FROM MRU_LIST M,ABC_USER A --Comma syntax is not supported in SQL Server 2005
Comma syntax is supported in SQL 2005 and 2008. This runs fine.
SELECT TOP 10 * FROM
sys.objects a, sys.objects b
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply