SQL Migration: Data migration from SQL 2000 to 2008

Can anybody discuss about data migration process from SQL 2000 to SQL 2008??

more ▼

asked Oct 17, 2009 at 05:38 PM in Default

avatar image

Praveen Manoharan
11 1 1 3

What issues are you concerned about?

Oct 20, 2009 at 02:46 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

There are many considerations and the fact that you are migrating the data(base) to 2008 probably also means a server move. This is a good opportunity to review the database schema, permissions, code, maintenance and recovery plans. Here are a few ideas to get your thought process started.

  1. User to Schema migration
    Look at who owns the objects in your database, these user-owners will become schemas in a 2005/2008 database. It makes sense to divide database objects into schemas to make it easier to grant permissions. Just as you wouldn't put every file on the root of C: and try to provision access, nor should you put all objects into the dbo schema and provision access on an object-by-object basis or worse grant more access than necessary by simply giving database-level access.
  2. review security
    Are there SQL logins which can be changed to Windows accounts? Are there individual logins with access for which you can setup a group? Are there database-level permissions that can be reduced to schema-level permissions.
  3. deprecated field types
    Check for tables with legacy field types like TEXT or image. Ensure you have primary keys on all tables and foreign key references for related tables.
  4. legacy code
    Go through the obvious DTS packages needing to go to SSIS packages. Don't just run them in legacy, take the time and convert them. In many case, these packages can be converted to stored procedures or T-SQL run by the SQL Agent. Also review stored procedures or T-SQL agent jobs to check for deprecated syntax (i.e. SET ROWCOUNT) or other things which can be updated. Document the changes using inline comments.
  5. vendor-supported version
    Sometimes, our vendor will only continue support on 2005. Even if you purchase a 2008 license, you can still install 2005. I have not found much vendor support for a 2008 instance but running the database in 2005 mode. Here's an opportunity to review your prefered vendors. If they're not keeping their database up to date, how much do you trust their application?
  6. method used for migration
    How much downtime can you allow? Is a backup / restore too long? Do you need to setup log shipping? Or will you make a clean cutover with the application and synchornize those the tables later?
  7. recovery plans
    Review your disaster recovery plans? For example, with backup compression (in 2008) we are able to keep more days worth of backups on the server (yes, copied to tape just in case). Also, with faster mirroring due to compression, this is a more viable availability option.

The point is there are many new feature in 2005 and 2008 which affects the way you design, secure and administer a database. Don't just "bring over" the old database and call it good because it's on a 2008 instance. This is the perfect opportunity to do (or redo) it right.

more ▼

answered Oct 18, 2009 at 04:19 PM

avatar image

320 3 5 8

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

simply backup your SQl 2000 database and restore it in SQL 2008

more ▼

answered Oct 17, 2009 at 08:59 PM

avatar image

Squirrel 1
1.6k 1 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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Oct 17, 2009 at 05:38 PM

Seen: 3352 times

Last Updated: Oct 20, 2009 at 02:57 PM

Copyright 2018 Redgate Software. Privacy Policy