We have a production SQL Server 2005 server on which we are hosting 200 databases.
We want to host these databases on our new server. On new server SQL Server 2005 is installed and is ready.
I want to know the best and the most convenient way to migrate the whole data includes:
- Security and permission
- Users and memberships
- All of the databases and ..
In one word I want to have exactly the same copy of the current SQL Server 2005 on our new instance on the new server.
I will be thankful if you guide me on this case.
Best regards
Best Answer
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.
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)