Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Upgrading a Database SQL 2000 to SQL 2005 Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 5:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, February 27, 2014 10:08 AM
Points: 48, Visits: 3,646
Hi Victor,

Below are the steps

1) Install SQL Server 2005 as a different named instance. (If you have done it then you can ignore this step.)

2) Once installed, install the latest service pack (SP2) for sql server 2005.

3) You can also apply any hotfixes released after sp2. For the list you can check microsoft website.

4) Take a backup of database from the sql server 2000 instance and restore it in SQL SERVER 2005 INSTANCE. This is very important for u. I think here you are doing the mistake. I think you are restoring it in OLD instance of SQL server 2000 only and viewing it from SQL server management studio.

MAKE SURE You are connected to the new named instance of SQL server 2005 only.

6) Change the compatibilty level of the database restored in the sql server 2005. (you can do it from SQL server management studio or can also execute the command mentioned in the article.)

And you are done :).

Make sure to create any dependent job or linked server if requried.

Below are my comments for the issues.

1. Outer join operators *= and =* are not supported in 90 compatibility mode


In SQL Server 2005, old method of writing Left outer join or Right outer join are not supported any more. You have to replace them with "Left outer join" and "Right Outer join" respectively.


2. Upgrading will cause Full-Text Search to use instance-level, not global, word breakers and filters by default.


Incase you have full text index in your database then drop them before upgradin into SQL server 2005, else your upgradation can fail.

Please read the article. I have mentioned the script to delete all full text indexes.

Once the database is upgraded recreate the FT indices which were removed. So make sure to note tables and columns which have full indices before deleting them so that you can create them later.


3. Column aliases in ORDER BY clause cannot be prefixed by table alias


If you have any query like,

Select studentid, count(studentid) as the CountofStudents from students group by studentid order by CountofStudents

Doing an order by on alias created "CountofStudents" can work in SQL server 2000 but in case of SQL server 2005, it won't. So you have to mention the exact column name i.e "count(studentid)" and not the alias name.

Hope that helps.

Regards
Sachin
Post #449300
Posted Wednesday, January 30, 2008 2:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 18, 2009 3:10 PM
Points: 4, Visits: 18
Thank you so much. You are very kind! Two more issues:

1. I am installing the SQL 2005 instance on the same physical box containing SQL 2000 instance. Are there any special precautions i need to take?

2. How do i decomission the SQL 2000 instance on the same box without affecting the 2005 instance?

Thanks once again for your time. Expecting your reply,

Victor
Post #449699
Posted Wednesday, February 6, 2008 1:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 17, 2011 9:15 AM
Points: 44, Visits: 3,626
Hi Victor,

Please find my reply below.


1. I am installing the SQL 2005 instance on the same physical box containing SQL 2000 instance. Are there any special precautions i need to take?


Installation of another sql server instance is completly independent activity.
Still you can check for the below.

a) Make sure you have enough space on the drive where you are isntalling sql server 2005.
b) Make sure to choose a different instance name other than SQL server 2000.



2. How do i decomission the SQL 2000 instance on the same box without affecting the 2005 instance?



Again, as SQL server 2005 is a differnet setup, it will not affect sql server 2000 instance. Once sql server 2005 instance is installed, shutdown sql server 2000 services and see if you are still able to access you new sql server 2005 instance and database. I think that will be safer than removing the database

Regards
Sachin



Post #452045
Posted Wednesday, February 6, 2008 2:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 18, 2009 3:10 PM
Points: 4, Visits: 18
Thank you very much. I succesfully upgraded one of our DBs to 2005 today in a test environment. I will watch it for some days before going LIVE! Thanks for your help.

Victor
Post #452456
Posted Wednesday, May 7, 2008 3:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
Good summary article, but is no substitute for MS's Technical Reference. If you are doing an upgrade be sure to read it.
Post #496145
Posted Friday, July 25, 2008 5:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, September 25, 2012 5:03 AM
Points: 71, Visits: 102
:)
EXCELLENT article Sachin,keep it up.
u have just simplified a complicated task ,before reading this i was totally unaware of inplace and side by side upgrade techniques,now it is clear to me
it helped a lot in understanding and seems to be a simple job.:P




Thanks,
Syed Shahbaz Ali
Post #540831
Posted Monday, August 11, 2008 10:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 14, 2008 7:18 PM
Points: 2, Visits: 7
Hi Sachin,

I am in mids of upgrading from SQL server 2000 to 2005.

Wanted to get some advise from you guys, on what kind of way shall i use.
Let me inform you my current info.

- Having 2 servers to Upgrade from 2000 to 2005, 1 production server, 1 reporting server
- Both running on SQL 2000 now perfectly.
- just need to upgrade from SQL 2000 to 2005.
- Not a very complex and Large DB

What do you advise me to do?

Copy DB wizard? or Backup and Restore? or others
How to do, step-by-step would be great.

I would really appriciate all the help and support you can give me.
Sharing knowledge is one of the best deeds a person can do, and looking at your previous replies. you helped Victor alot. You are a good person.
Maybe 1 day i can share this knowledge with others.
Thanks Sachin,

Kiran
Post #550741
Posted Thursday, November 27, 2008 8:37 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:51 AM
Points: 98, Visits: 1,103
Hi Everyone,

this thread and the journal for upgrading has been extremely helpful in my quest as a very new sql dba and researching on how to upgrade from sql 7 to sql 2005.

Can you tell me when running the Upgrade Advisor what kind of extra load does it place on the server its running on? Also can it be done while all the databases are online?

Thx in advance.:)
Post #609859
Posted Thursday, December 4, 2008 4:35 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
The upgrade advisor documentation answers this. Install UA on another server and run the analysis of your target server from there.
Post #614191
Posted Tuesday, June 16, 2009 1:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 7:48 AM
Points: 7, Visits: 68
Good article. Thanks for writing. Keep on writing.
Post #735971
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse