Migrate SQL Server

Question

Can I migrate 32bit SQL Server 2005 database to 64bit SQL Server 2008 database without too much hassle or is there somethign I need to think about first?

Answer 1

• Download and run the SQL Server 2008 Upgrade Advisor
• Check if you're currently using any deprecated feature in SQL 2005 that might have be discontinued in SQL 2008, see What deprecated features am I using?
• Look at the Backward Compatibility topic of the feature(s) you use (Engine, SSAS, SSRS, SSIS, Replication) and see if there is anything that applies to your deployment
• Define your upgrade strategy: side-by-side or in-place
• Read and review the Considerations for Upgrading the Database Engine. At the very least follow the quick checklist in that topic:

• When upgrading from a 64-bit edition of SQL Server to a 64-bit edition of SQL Server 2008, you must upgrade Analysis Services before you upgrade the Database Engine.
• Back up all SQL Server database files from the instance to be upgraded, so that you can restore them, if it is required.
• Run the appropriate Database Console Commands (DBCC) on databases to be upgraded to ensure that they are in a consistent state.
• Estimate the disk space that is required to upgrade SQL Server components, in addition to user databases. For disk space that is required by SQL Server components, see Hardware and Software Requirements for Installing SQL Server 2008 R2.
• Ensure that existing SQL Server system databases - master, model, msdb, and tempdb - are configured to autogrow, and ensure that they have sufficient hard disk space.
• Ensure that all database servers have logon information in the master database. This is important for restoring a database, as system logon information resides in master.
• Disable all startup stored procedures, as the upgrade process will stop and start services on the SQL Server instance being upgraded. Stored procedures processed at startup time might block the upgrade process.
• Stop Replication and make sure that the replication log is empty.
• Upgrading an instance of the SQL Server 2000 Database Engine retains the configuration value for max worker threads. However, we recommend that you change the max worker threads value to 0 before upgrading, to let the Database Engine calculate the optimal number of threads. For more information, see max worker threads Option in SQL Server Books Online.
• Quit all applications, including all services that have SQL Server dependencies. Upgrade might fail if local applications are connected to the instance being upgraded.
• If you utilize Database Mirroring, see How to: Minimize Downtime for Mirrored Databases When Upgrading Server Instances in SQL Server Books Online.

Answer 2

Not much to worry about really. You can either detach the db, move and reattach or backup and restore. Does the application that uses the db support 2008? If not then leave it in 90 compatability mode.