Migration from SQL Server 2005 to SQL Server 2008 R2

20 Steps to Migrate from SQL server 2005 to SQL Server 2008 R2.

Here is the HIGH LEVEL PLAN to migrate from SQL Server 2005 to SQL Server 2008 R2. I have included all in below 20 steps.

Let me share high level plan to migrate from SQL server 2005 to SQL Server 2008 R2. Again it depends how much downtime you can have to do the migration. But here are the plans that can help to reduce the downtime very less as far as databases are concerned.

Here are the plans step by step.

1.      First check with the business, what could be the maximum downtime and when the migration will be done. Generally it happens at weekend after business hours.

2.      Install upgrade advisor tool in SQL server 205 server. This tool is free from Microsoft and you can download the same from here. Analyze the output of upgrade advisor tool very carefully. Find is it safe to move the databases from 2005 to 2008 R2. This will give you the list of blockers that may block you doing migration. Resolve all the blockers and then proceed just to be in safe side.

3.      Get the new server according to your configuration. From the DBA side make sure you install SQL server 208 R2 in the server (Edition depends on your organization’s License) and apply recent patch of SQL server 2008 R2. It won’t heart doing some test against the storage drives by using SQLIO. SQLIO is the tool from Microsoft to test storage hard drives or SAN performance.

4.      Take the full backup of all the databases 1 day prior to migration. After taking full backup schedule differential backup and transaction log backup immediately in the existing SQL server 2005 server. Disable all the previous backup schedules if any. Since you already took the full backup and you scheduled differential and transaction log backup again there is no meaning in keeping previous backup schedule.

5.      Copy the full backup files taken in step 4 in the new SQL server and start restoring all the databases with NO RECOVERY option. Do this before down time and make sure this gets completed before downtime starts. Remember this process may take time if you have the databases of bigger size.

6.      Start moving all the SQL logins to the new server by using sp_help_revlogin. We can do this before downtime starts.

7.      Start moving all the SQL jobs, Maintenance plan, SSIS packages to new server. SQL jobs we can move by generating the SQL script. As far as SSIS packages and maintenance plans are concerned, you can export the same to new server. Remember your all SQL jobs, maintenance plan are in disable state else all will start running and failing unnecessary. Again you can do this before downtime starts.

8.     Once the downtime starts make sure all the connections are closed. Ask application team to stop all the services. Do not trust them if they say YES we have stopped all the services. Use below commands and suggestions.

9.      Once downtime starts make sure all the databases in SINGLE USER mode. You can use below command to kill all the session forcibly and bring the database in single user mode.

ALTER DATABASE <DB_NAME> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

This make sure that your database is in single user mode and no transaction can happen even if application team has left some services running by mistake as they always do.

10.   Take transaction log backup now manually for all the databases and disable all the backup jobs you created in step 4.

11.   Move last differential backup and all the transaction log backups including latest one of all the databases to the new server.

12.    Restore last differential backup with NO RECOCVERY and after that restore all the log backups sequentially with NO RECOVERY except last one. Finally restore last transaction log backup with RECOVERY. This you may need for all the databases.

13.   Now all you databases are ONLINE in new server and you made sure you did not miss any transaction. It will be very good idea to run DBCC CHECKDB for all the databases now to make sure there are no consistency error in the database(s).

14.   Change the database COMPATIBILITY LEVEL to 100.

15.   Ask Application team to change the connection string to point to new server.

16.   Enable all the SQL jobs and maintenance plan jobs. If you need new maintenance plan this is the best time to create it.

17.   Discuss with Business about the backup policy and implement in the new server.

18.   Verify backup jobs are scheduled and running fine. Do not forget to schedule transaction log backup of the databases for which recovery model is FULL and Bulk Logged.

19.   Verify Database maintenance jobs like Re indexing, update statistics and consistency checker jobs and make sure all are running fine.

20.   Talk to application team and see if they have any issues connecting to the new server. If any fix it.

These are the 20 steps for Migration I can think of. This way I am not going to take much downtime from SQL perspective. Most of the things that take hell of time, I would prefer to do in prior.

Hope this is helpful for the DBAs planning to work in Migration.