question

Felipe avatar image
Felipe asked

Migration: SQL 2000 to SQL 2008

Friends,

I need to migrate a database server from version 2000 to 2008. I had to rewrite more than 300 stored procedures to reconcile them with the 2008 version

But now, i need to migrate a server.

I thought the following steps:

  • Uninstall the 2000 version
  • Install the version 2008
  • Atachi the database
  • Create jobs
  • Create the logins and users
  • Create other objects (application roles, linked server ...)
  • Reindex all the database
  • Run sp_recompile for all tables

Can anyone give me a hand? agree? Has another plan?

I need criticism and suggestions.

Thank you very much.

sql-server-2008sql-server-2000migration
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Leo avatar image Leo commented ·
If I would, I am not uninstall the SQL 2000 until database are transferred and running fine on SQL 2008.Also I will do Backup the database in a safe place. Create the Jobs, logins and reindex can do after those steps above.
0 Likes 0 ·

3 Answers

·
Nick Kavadias avatar image
Nick Kavadias answered

The biggest criticism is that you don't have a back-out plan. Be aware that once you attach your 2000 data/log files to 2008 that there's no going back. At a minimum I would suggest you make a copy of the files before you attach them to 2008.

You should consider how long the business can live without the application if there's a failure in your migration. You might be ok with your plan if you can have 12 hrs of downtime & have tested instructions for rebuilding the server. If not, then you need to reconsider.

Have you run the Upgrade Advisor on your server? It doesn't catch everything, but it's a start. It's useful even if you don't do an in-place upgrade.

Other Advice? Make sure your SQL Server 2008 install follows best practice eg. tempdb & disk setup, service account permissions, CHECKSUM for page verify.. ect.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

My immediate, Grant-inspired suggestion is not to uninstall the 2000 version until you are completely happy that the 2008 version is working 100%. Otherwise you would have nothing to fall back on if it does go hideously wrong.

7 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Nuts. Now I can't add an answer, but yeah, I agree with this whole heartedly. We count on having the 2000 servers intact as an immediate fallback position in case something goes wrong with the update. It never has yet, but just in case...
0 Likes 0 ·
Felipe avatar image Felipe commented ·
I need uninstall the 2000 version because i need install 2008 version with default instance. Before make in a production environment, i´ll do in an environment of approval. What do you think you can go wrong? Except uninstall the 2000 version you have any more suggestions or criticism?
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
It's your production system. Take on as much risk as you want. We've done all our upgrades side-by-side, with a new server holding the 2008 instance, so that we don't take any extra risk with our production data. If you're going to install it on the same system, you should simply do an in-place upgrade. It'll be faster and carry the same risk as uninstalling SQL Server 2000. Just be sure you have a good, tested, backup before you do the deed.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
I agree with matt and Grant! I wouldn't uninstall sql 2000 until the new server has been in production at least à month. If i am forced to do it, i would send à mail to My boss and explain the risk and that he is responsible for all the consequences
0 Likes 0 ·
Felipe avatar image Felipe commented ·
Thanks for answers about the risk. Apart from uninstalling the 2000 version, you agree with the steps listed above?
0 Likes 0 ·
Show more comments
Fatherjack avatar image
Fatherjack answered

Felipe, if you have to uninstall SQL 2000 then I would look into virtualising the whole server as it is, before you start any upgrade work. That way, if the SQL 2008 does go pear-shaped you can simply turn on the virtual machine image and contnue from there while you consider what caused the issues with the upgrade.

2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Felipe avatar image Felipe commented ·
But if I migrate the database to compatibility level 10, I´ll not have to return it to the level of compatibility 8, right?
0 Likes 0 ·
Fatherjack avatar image Fatherjack ♦♦ commented ·
If the upgrade to 2008 works then it will be as you want it. If there are any issues then you have the virtual image (like a backup from before the upgrade) of the whole server that you can startup and that will be SQL 2000
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.