Migrating to 2005 from 2000 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Migrating to 2005 from 2000

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-03-26 : 10:34:16

Guys! Very very junior in this migration part. Hope the seniors can help out....PLEASE!!!!!! Don't get me wrong asking you guys to spoon feed me. I have read a lot of articles and even BOL and also not to forget read millions of books on this. But just wanted to make sure that I am in the right path and want to do this without any problem. That's why I have decided to ask the Gurus here who have done this in past.

I have this machine now:

Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: )

Migrating to this machine:

Microsoft SQL Server 2005 - (X64)
Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

I have these in the SQL Server 2000 box:
1. Databases, tables (of course)
2. Views
3. Securities(Logins, Roles, Password)
4. DTS packages
5. Jobs
6. Replications
7. Linked Servers
8. Stored Procedures

Every article is different. I was confused at a point of time. Please show me the right way guys!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-26 : 10:53:50

Install DTS support on 2005
Create the logins and linked servers on 2005 that exist on 2000
Backup the user database(s) on 2000 and restore on 2005.
Modify database compatibility level on user databases as necessary.
Update statistics on user databases.
Script out jobs on 2000 and execute scripts on 2005 to create jobs.
Copy DTS packages from 2000 to 2005. You will probably have to modify each package to at least update the server it is connecting to.
Configure replication as needed.
Test everything.

CODO ERGO SUM

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-03-26 : 10:57:41

Thanks for the quick and GREAT reply. You gave me the confidence to do this easier now. Okay, will follow all that. But couple more question. What should I do for the schema? and I hope you saw the difference that the two servers have. the 2000 box is 32 bit and the 2005 box is 64 bit. Will there be any kind of problem?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-26 : 11:04:41

quote:
Originally posted by LOOKUP_BI
...Will there be any kind of problem?

More than likely. That is why I said "Test everything".

CODO ERGO SUM

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-03-26 : 11:31:35

I am sorry but how do I move the Replication to 2005? There are about 25 replications
Also, did you mean downloading DTS Designer in the 2005 box when you said "Install DTS Support"?
Please help me further more! I am totally interested in this migration project but I just want to do it VERY properly.
I know I will face problems but at the same time I just want to avoid any if possible.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 12:30:40

This has helped me a lot.

http://vyaskn.tripod.com/moving_sql_server.htm

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-03-26 : 14:36:43

Sodeep, I think the article you recommended is more on Migrating 6.5 and 7.0 to 2000.

I need to know about the phase and problems I will be facing from 32 bit (2000) to 64 bit (2005). Can somebody help?
What are the steps for me to keep the SAME DB over to a new server?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-26 : 14:53:41

From what angle did you see that? We have done this so many times ,thats why i am giving you directions:

1) Use backup/restore to migrate to 2005(64-bit) and you can change compatibility level to 90( Make sure that there are some features which will not work in 90 if your apps were built in 80 compatibility

2)use microsofts: sp_hexadecimal and sp_revolvelogin2005 to transfer all logins to new server

3)Migrate all DTS packages to SSIS BI studio with DTS migration wizard (make you cahnge connection strings and redevelop in BI studio before you deploy to Server or jobs)

4)You will have no problem with linked server(64-bit) connecting to 2000 but if you are connecting to 32-bit 2000 server with service pack 3 or less ,you will have problem)

5) Replication is clearly mentioned on website i have provided

This is how i tried and its in production working fine like dream

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2008-03-26 : 15:06:22

Really? It works fine? I am going to try this out like what you have said. Of course, I will take extra precautions. We will be migrating next week. So, I will keep posting if I do have anything else. Thanks a million for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources