Return to Answer

Disclaimer : SQL Server 2005 is out of mainstream support. - extended support ends for sp4 on 04/12/2016

2. 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.

• 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.

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)

Disclaimer : SQL Server 2005 is out of mainstream support. - extended support ends for sp4 on 04/12/2016

2. 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.

• 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.

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)

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).

• 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 http://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.

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).

• 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.