x

Upgrading SQL 2000 to SQL 2008

I have joined a new project in my company and my new client is going to upgrade their existing SQL Server 2000 to SQL Server 2008.

I searched for several articles and they were really helpfull. We have create a checklist (before and after) and steps to be performed for this upgrade.

Can anyone help me with any such checklist and steps by step guide. The problem is we are don't have time, we have to start this activity from Monday onwards.

Please help me. Thanks.
more ▼

asked Oct 13, 2010 at 06:02 AM in Default

Vishal gravatar image

Vishal
77 5 5 7

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Seriously, you're starting the upgrade process, but you don't have time to plan it out so that you're protecting your systems? Really? That's practically an actionable statement if you're working as a consultant. Posting it on a public forum could get you into serious trouble, especially if you have problems with the upgrade later.

That said, what kind of upgrade are you doing? Are you planning an in-place or side-by-side upgrade? The different approaches will result in different checklists. The safest approach is the side-by-side upgrade. You can do this either by using a backup and restore or dettach/attach of the database files. I'd suggest using the backup & restore as the safer approach. Here are the things I'd do:

  1. Run Upgrade Analysis tool from Microsoft. Address any issues raised there, first.
  2. Identify DTS packages. These must be migrated by hand, unless you buy PragmaticWorks excellent software. Rebuild the DTS packages as SSIS.
  3. Script out all SQL Agent jobs.
  4. Script out all security
  5. Backup the systems and validate the backups (preferably by restoring them to another system)
  6. Run the security script on the new system
  7. Run the restore on the new system.
  8. Validate the databases by running DBCC
  9. Manually update all statistics
  10. Run the SQL Agent script
In a nutshell, that's it. There may be other details that others will add, but that's basically the approach I've been taking and it works. But you'd better plan, test, and practice before you start hitting production systems if you've never done this before.
more ▼

answered Oct 13, 2010 at 06:18 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.7k 19 21 74

+1 @Grant! Btw do you use chrome? I wasn't able to post so i had to login using Firefox to post my answer below
Oct 13, 2010 at 06:23 AM DaniSQL

The problem is, there were other guys who were working with this client and in this week only I started working with this client and when I looked into the documentation part they have done likely nothing.

This upgrade will be Side-by-Side and the activity will start by coming weekend, may be identifying the difference between the versions, DBA as doing their job and we developer have to look for Development and DTS only, as far as we have planned till now. The final migration of production will be in January but before that we have to go with trial runs. We have setup different team of DBA, SQL Developer, .Net Developer and Testers and they will be looking after of different tasks and activities.

What I see is, there are so enhancements in SQL 2008 and we need to compare that with SQL 2000 to identify everything because there are many SQL Server's built-in objects which will be removed from later versions, so we have to take care of them as well.

Anyways, I thanks for your help. though I am going through Microsofts recomendations as well as articles on SSC.com.
Oct 13, 2010 at 06:31 AM Vishal

Be careful here, it sounds like you could have a case of too many cooks spoiling the broth. If you have disparate teams looking at the upgrade process and acting on their own, you will have some serious problems keeping everyone on the same page.

If the upgrade is going to happen in January you can take your time. Don't expect to retro-fit all old code with snazzy new 2k8 code by January (unless it is a tiny DB). I would take the Upgrade Analysis output and fix the broken parts and then try a test upgrade. You can still do the performance enhancing after proving an upgrade will work.
Oct 13, 2010 at 06:35 AM WilliamD

Oh, so you're starting trial runs on Monday. That's totally different. Phew!

The Microsoft recommendations are pretty complete, although, last time I checked, they didn't talk about updating stats, manually.

I agree with @WilliamD. I wouldn't start with 2008 functionality until the production migration is complete. Don't overcomplicate the process.
Oct 13, 2010 at 07:07 AM Grant Fritchey ♦♦

@Grant, a harsh intro, and yet very true!

One thing I did not notice in your list was to check for anything that was version specific to 2000 that your code was relying on. I doubt there would be much moving from 2000 to 2008, but I have seen it come up, especially in some of the older versions like 6.5. I previously had to run a database in 6.5 compatibility mode for a long time until we had the resources to rewrite the application it was supporting.
Oct 13, 2010 at 09:19 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

There are many resources in this site: search for [upgrade][1]

Here are some of the things you have to remember: Run upgrade advisor first and make sure nothing breaks due to upgrade, TEST throughly, Make sure you have a solid backup, make sure you run DBCC and update your statistics after upgrade, if it is a side by side upgrade dont forget to move logins by scripting them out ......

[1]: http://ask.sqlservercentral.com/search/?q=upgrade⋐mit=search&t=question
more ▼

answered Oct 13, 2010 at 06:19 AM

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

+1 Test, test, test and then test some more.
Oct 13, 2010 at 06:27 AM Grant Fritchey ♦♦
Thanks. Yeah you are right we are going with all precautions. And we want to have multiple test runs, may be that help us to indetify the time for one conversion process.
Oct 13, 2010 at 07:55 AM Vishal
(comments are locked)
10|1200 characters needed characters left

One of the [first articles on my blog][1] contains a handy checklist. Even now I keep finding things to add to it...

[1]: http://thelonedba.wordpress.com/2010/06/08/sql-server-upgrades-ahoy/
more ▼

answered Oct 13, 2010 at 06:23 AM

ThomasRushton gravatar image

ThomasRushton ♦
33.8k 18 20 44

(comments are locked)
10|1200 characters needed characters left

We just migrated from 2000 to 2008 R2. I have not published a checklist, but rather high level bullet points pertaining to our particular migration.

[SQL Server: Migrating from 2000 EE X86 to 2008 R2 SE X64][1]

[1]: http://ozamora.com/2010/10/sql-server-migrating-from-2000-ee-x86-to-2008-r2-se-x64/
more ▼

answered Oct 13, 2010 at 10:20 AM

ozamora gravatar image

ozamora
1.4k 2 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1834
x473
x68

asked: Oct 13, 2010 at 06:02 AM

Seen: 3179 times

Last Updated: Oct 13, 2010 at 09:13 AM