Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time:
1) Backup and Restore Databases - Is this the best option?
It's an option. If you need the downtime to be less, you can use mirroring or log shipping (or do it by hand) to keep the databases in sync and then migrate during a fast downtime or cutover.
Please note that system databases cannot be log shipped or mirrored and would need to either be frozen or copied right before the cutover.
2) Migrate Logins - use Microsoft KB? kb/918992
If you restore the master database to the new instance, no migration should be needed. All server level logins are stored in the master database.
3) Migrate Credentials/Certificates - what is required for this?
If this is inside of SQL Server, these would be held in their respective databases (and possibly master as well). The one difference would be the service master key (SMK) that would change. You make want to back that up and restore it on the new server if you're relying on automatic key decryption.
4) Migrate SQL Server Agent Jobs - (Object Explorer Details > Select All Jobs > Script Job; Is this process the best option?)
If you restore the msdb system database, all agent jobs will be held in it. There would be no need to script->restore.
5) Migrate SSIS Packages - (How to do this?)
If the SSIS packages are on the filesystem (not default) it would be trivial to create the same location on the new server and copy. By default the SSIS packages are held in the msdb system database and restoring it would get you to the same place, just like #4 and previous.
6) Migrate Database Mail Accounts/Profiles - (Create a script or recreate in SSMS?)
This is also stored in msdb. See #4, #5.
7) Recreate assemblies
These live in their respective databases. If backup and restore is used, this should not be a problem. Any assemblies outside of SQL Server would need copied to the new server.
8) Recreate Linked Servers
These also live in the master database, see #2.
Disclaimer : SQL Server 2005 is out of mainstream support. - extended support ends for sp4 on 04/12/2016
Migration always have a downtime. So depending on how much your downtime is, you can go with setting up either :
Logshipping ( I have used this method and it reduces the downtime considerably)
a. Set up logshipping from the source to destination server as usual. Keep the sync time 1 min (which is minimum).
b. On the day of migration, make sure the logbackups are in sync.
c. Stop all the logshipping jobs and then take the final tail log-backup and put the primary databases in no-recovery mode (this is just in case you need to go back to primary).
d. restore the tail log backups on the secondary with recovery bringing all the secondary databases online.
Database Mirroring : I do not recommend mirroring for migrating 200 databases as you will easily exhaust the worker threads as described in this KB article.. If there were less databases, then this would be a viable option.
If you have enough downtime then I would recommend
backup / restore method (as opposed to detach / attach) as if something goes wrong, you atleast have a good backup to restore back. this script will get you started with moving all the logins and databases. Just modify it to use backup / restore (or take full backups before running the script - as it uses detach/attach method).
Things you can do prior to migration :
Refer to this answer for migration steps. Some may apply to your scenario as well.
script out all the jobs / ssis packages, etc --OR -- As suggested by @shanky, you can restore the system databases from the old server to new server before hand - that will give you all the logins, jobs, etc on the new server.
turn on Instant File Initialization on the new server.
- Have multiple tempdb data files with equal size.
- Enable Trace Flag 1118 - to assist in TEMPDB scalability by avoiding SGAM and other allocation contention points.
- Configure max and min memory correctly. Especially Max memory away from default.
- Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.
- Best is to install sp_Blitz from Brent Ozar. Run it and address the critical and high priority issues reported by it.
- You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.
Edit:
You can use powershell to migrate your all databases from one server to another as described at Use PowerShell to Migrate Entire SQL Server Instance (db, logins, jobs, etc)
Migrates databases with simple or complex file structures via backup & restore or detach & attach. Also migrates users with passwords, SIDs, server & db permission sets, server & db roles, server configuration settings, SQL Agent objects, user objects in systems databases.
Best Answer
Copy 50 to 100 databases to a completely new server with zero downtime? I don't think that's going to happen. When you're talking about a migration, planned downtime is an absolute must.
In this case, depending on the size of the databases, I'd take the scripting route with this. If you are proficient with PowerShell, you can loop through all the databases and take backups to store in accessible storage for the new server. Then on the new server, you can script out the restore of all the databases.
Don't forget about the server-level items (logins, jobs, etc.). You can script these as well, but with this particular requirement SSIS actually makes it very easy. There are tasks to do all of these server-level items.
If you're expecting no downtime, how are the clients going to redirect their requests to the new database server? There are a few external solutions for this, but something to consider when you promise "zero downtime".