473,725 Members | 2,244 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Migrating to 2005, need advice

Help. I have been tasked with upgrading a 2000 instance to 2005.

I have actually done this before, but it was a long time ago, and I
didn't do it alone. Now, the sitation is little different, and I need
to know the EXACT steps to take.

Does anyone have a FAQ or link that outlines migration steps? I found
one on sql server central, but it isn't very detailed.

One of the important things I need to know is, how do I create a
rollback plan if I am upgrading from 2000 to 2005 on the same server
(instance)?

Also, why can't I seem to find a comprehensive list of TO DO's when
upgrading? Doesn't microsoft provide this? You would think so. I will
run upgrade advisor first, but isn't there also documentation
somewhere?

I seem to recall lots of permissions issues that arose with 2005.

HELP

Thanks

Feb 12 '07 #1
4 2948
(to*******@gmai l.com) writes:
Help. I have been tasked with upgrading a 2000 instance to 2005.

I have actually done this before, but it was a long time ago, and I
didn't do it alone. Now, the sitation is little different, and I need
to know the EXACT steps to take.

Does anyone have a FAQ or link that outlines migration steps? I found
one on sql server central, but it isn't very detailed.

One of the important things I need to know is, how do I create a
rollback plan if I am upgrading from 2000 to 2005 on the same server
(instance)?
It's certainly not a bad idea to install a second instance on the
same machine, and the migrate by BACKUP/RESTORE. This is great if
you run into performance issues and want ot compare query plans. The
drawback if you install a new instance is that a lot of clients will
be affected.

A second alternative is to install SQL 2005 on a new machine, and when
the install has completed, you change the names and IP-address of the
machines, so that the clients can't tell the difference. Of course,
this means that you need to shop for hardware.

This makes it sounds like an in-place upgrade should be avoided at
all cost, but it's not that bad. But I will have to admit that I
have never done one, nor do I plan to. If you have to go that path,
I recommend that you start with installing a second instance of SQL 2000,
and restore databases on this instance. Yes, I still think it is a
good idea to keep SQL 2000 for reference for a while.

In any case, you should first set up a test environment, so that you
can test doing in-place upgrades, and at least conduct some testing
of your applications.

Here is a short list of must-do:

1) Change the compatibility level of all databases to 90. If too many
things break, you may to move back to 90, but be optimistic.

2) Run sp_updatestats on all databases. Old statistics are voided by
the upgrade.

3) If you move databases from another server, you need to rematch
users with logins. (This applies even if you don't make upgrades.)
This includes setting the database owner, if the owner is not sa.

There a few things that can break. Here is a list of the most
probable cases:

* Old-style outer-join, *= and =*. Caught by the Upgrade Advisor,
and can be avoided with compat level 80.

* WITH is now required for hints with more than one work. Caught by the
Upgrade Advisor, and can be avoided with compat level 80.

* Views that uses SELECT TOP 100 PRECENT ORDER BY. In SQL 2000 a
SELECT without ORDER BY from this view seemed to get the order of
the ORDER BY in the view definition. This was mere chance, and it
does not happen that often on SQL 2005. This is *not* caught by
the Upgrade Advisor, as far as I know, and you cannot save the
day with compat level 80.

* Passwords are now always case-sensitive.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 12 '07 #2
On Feb 12, 5:52 am, tootsu...@gmail .com wrote:
Help. I have been tasked with upgrading a 2000 instance to 2005.

I have actually done this before, but it was a long time ago, and I
didn't do it alone. Now, the sitation is little different, and I need
to know the EXACT steps to take.

Does anyone have a FAQ or link that outlines migration steps? I found
one on sql server central, but it isn't very detailed.

One of the important things I need to know is, how do I create a
rollback plan if I am upgrading from 2000 to 2005 on the same server
(instance)?

Also, why can't I seem to find a comprehensive list of TO DO's when
upgrading? Doesn't microsoft provide this? You would think so. I will
run upgrade advisor first, but isn't there also documentation
somewhere?

I seem to recall lots of permissions issues that arose with 2005.

HELP

Thanks
The only reliable way to catch all issues is to script out your SQL
2000 databases and then rebuild them on a SQL 2005 database with
compatibility level 90. This method is much better than using the
Upgrade Advisor alone.

For tools that make this process easy please visit www.dbghost.com

Regards,

Malcolm

Feb 13 '07 #3
Mork69 (ml****@bigfoot .com) writes:
The only reliable way to catch all issues is to script out your SQL
2000 databases and then rebuild them on a SQL 2005 database with
compatibility level 90. This method is much better than using the
Upgrade Advisor alone.
I agree that running the scripts is a good idea, because you can
catch all compilation errors.

However, for the actual migration, I strongly recommend to use
backup/restore or detach/attach. Scripting is a more complex and a
process more prone to errors.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 13 '07 #4
On Feb 13, 10:10 pm, Erland Sommarskog <esq...@sommars kog.sewrote:
Mork69 (mle...@bigfoot .com) writes:
The only reliable way to catch all issues is to script out your SQL
2000 databases and then rebuild them on a SQL 2005 database with
compatibility level 90. This method is much better than using the
Upgrade Advisor alone.

I agree that running the scripts is a good idea, because you can
catch all compilation errors.

However, for the actual migration, I strongly recommend to use
backup/restore or detach/attach. Scripting is a more complex and a
process more prone to errors.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se

Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
In fact, if both approaches are used then the migration should be
perfect. i.e. You script out and build in order to highlight and fix
all the problems. If you keep the scripts for the objects that were
fixed then you can do the detach/attach and then recreate them.

Feb 14 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
8518
by: Bernardo Robelo | last post by:
Hi, I am interested in migrating Microsoft Access database to Postgres database. But I do not have idea of like initiating. Maybe some tool exists for this problem. Thanks you. Bernardo
11
2262
by: Neil | last post by:
We are running SQL 7, using Access 2000 as a front end. Our network person is wanting to migrate to Windows 2003 (we're currently on Windows 2000), and wants to know if we should migrate to SQL 2003 at the same time. Are there major changes between SQL 7 and SQL 2003, and how hard of a task would it be to migrate our single database to a new version of SQL? Thanks, Neil
9
1970
by: skulkrinbait | last post by:
Hello, I need some help please. I need to migrate some applications from OS2 to Windows, the source code is C. I believe this can be done using Lex and Yacc and have installed Cygwin with Flex and Bison which I believe are compatible but I'm having trouble getting Flex to process the C file. I assume that I need to write some rules for Flex to use but as I'm not familiar with C I've no idea what the rules should be. Apologies for...
13
2442
by: Matt Fielder | last post by:
First off, if this is better posted in another group that qualifies as a manged group, please let me know. I currently have an application written in VB.Net using MSDE as the database. Current install is via InstallShield. As I've just found out that MSDE is not supported under Vista, I need to migrate to SQLExpress. I am also potentially interested in migrating to using an installer built in Visual Studio. I have a few questions:
9
2752
by: wandii | last post by:
Hi, I have written couple of projects which include several crystal reports in VS.net 2003 (VB.Net) and company would like to move to VS 2005. What does it take to convert to Visual Studio.Net 2005? Can the 2003 project just be opened up in 2005? Will that convert it. The interface for 2005 very different from 2003. I got the VS 2005 professional edition - can 2003 and 2005
4
2319
by: =?Utf-8?B?QXJqdW4=?= | last post by:
Hi, After migrating my application from VC++ 6.0 to VC++ 2005, I receive the error C2593 'operator +=' is ambiguous. It refers to the following line: Name += pManager->GetAgentName(); where Name is a string and GetAgentName also returns a string.
3
1878
by: mesut | last post by:
Hi colleagues, I need your advice... I have approx 1,5 years experience with ASP.NET/VB.NET 2005 and I have to switch over into C# 2005 language. I don't have experience with C# 2005 language. (but 10 years experience in mainframe languagues). My question is: I need a good book can someone advice it? I'm only interested in ASP.NET not in windows pages.
0
2599
by: cathy25 | last post by:
Hi, we are planning on migrating our sql from version 2000 to 2005 (I know SQL 2005 is in market from a while). As a part of testing, i have installed sql server 2005 on my development box and able to restore all databases from the production. When it came to DTS packages, I have used SSMS to migrate them by going into Management --> Legacy --> Data Transformation Services --> RC and selected Migrate Wizard. I have gone through the wizard and...
0
9393
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9164
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9092
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8072
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6695
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4775
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3212
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2622
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2151
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.