November 22, 2008 at 8:36 pm
We are planning to migrate SQL Server 2000 to SQL server 2005. We shall be doing in-place upgrade, where the server which has currently SQL 2000, I shall upgrade that 2000 to SQL 2005 database.
I am trying to find out what challenges we may face? what differences we may have? Will it affect the business environment?
If you can refer me to some good article that would be greatly appreciated. Thanks!
November 22, 2008 at 11:04 pm
Have you run the upgrade advisor? Has it found any potential problems? Have you tested your app and database completely on SQL 2005?
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
November 22, 2008 at 11:09 pm
The advisor did'nt find any issues, I tested in our test environment, so far we did'nt have any issues. But just curious what challenges do we usually face during the upgrade. Thanks.
November 22, 2008 at 11:21 pm
Queries that work on 2000 but throw errors on 2005, either due to syntax that's now invalid or changes to the optimiser is the main one I've seen.
Another fun one is corruption messages because checkdb is far more strict in 2005 than it was in 2000.
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
November 23, 2008 at 12:13 am
GilaMonster (11/22/2008)
Queries that work on 2000 but throw errors on 2005, either due to syntax that's now invalid or changes to the optimiser is the main one I've seen.Another fun one is corruption messages because checkdb is far more strict in 2005 than it was in 2000.
Can you please tell me what kind of queries were erroring out. Thanks for your reply.
November 23, 2008 at 12:32 am
No, I can't.
The optimiser's changed so queries the previously ran in one way may run in another way after upgrade. Sometimes that causes errors like conversion. There's no way to tell by looking if a query will be affected, the only way is to test.
There are also things that the 2000 parser considered valid and the 2005 does not. Most are documented. Again, testing will find those.
If you've tested your app completely, then you should be fine. Make sure that after upgrade you update all statistics and run a DBCC CheckDB
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
November 23, 2008 at 2:46 am
i always prefer a fresh install, that way the original server remains available
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 23, 2008 at 7:15 am
Also check for orphan users, change compatibility level to 90 after upgrade, rebuild indexes.
November 23, 2008 at 1:28 pm
sqlizer (11/23/2008)
Also check for orphan users, change compatibility level to 90 after upgrade, rebuild indexes.
What do you mean by orphan users?
November 23, 2008 at 3:34 pm
Two more things to watch out for that I've seen:
1) Parameter sniffing. I've had some queries which are rock solid in 2000 completely tank in 2005 because of parameter sniffing. (Use query plan guides to fix this, BTW)
2) Syntax problems from inline SQL. If your environment allows inline SQL watch out for queries in compiled code that break because of syntax issues.
The best way to find your problems is to try it. If you have the luxery of another server handy, backup and restore your databases and see what kinds of problems come up before you go live.
November 24, 2008 at 4:56 am
The in-place upgrade can sometimes fail after it has disabled SQL Server 2000 and before SQL Server 2005 is working. If this hapens you will have to uninstall SQL Server 2000 and do a clean install of SQL Server 2005.
The circumstances where this can happen are not properly understood. I have run an in-place upgrade on two 'identical' servers (according to the Windows Admin people) and one worked while the other failed.
You need to consider the risks and impact of such a failure when you upgrade. If you decide to go ahead, you need to plan what to do if the upgrade fails. This must include scripting all user objects from the system databases before you start the install, as they will not be accessible if the upgrade fails.
IMHO, always do a clean install of SQL Server 2005. Script out any user objects from system databases from SQL Server 2000 and uninstall SQL Server 2000. Then install SQL Server 2005 and script them back in.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
November 24, 2008 at 5:53 am
Please run the SQL server upgrade advisor,
and after migration apply run update statistics in all database.
November 24, 2008 at 5:55 am
orphun users means users which are exists in database but disassociated with login, we have fix those users in database with logins.
November 24, 2008 at 4:43 pm
Please read the attached doc.
MJ
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply