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.
- 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.
- 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.
- 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.
- 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.
- 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?
- 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?
- 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.
Oct 18, 2009 at 04:19 PM