asked
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.
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.
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.
@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.
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.
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.
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&Submit=search&t=question
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/
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/
