azure-sql-databasemigrationsql serversql-server-2008-r2sql-server-2012
I need to move about 150 databases from one server to another server.
I was planning on moving them one at a time using RedGate Packager, however this will take a while.
Is there a faster and easier way?
since you are using SQL Server 2008 Web edition, your best bet is to use LOG SHIPPING.
Your database size is 100GB, so initializing the secondary from a backup should be considered. Dont use GUI to set up initial logshipping.
SO your steps should be :
WITH NORECOVERY.Launch Logshipping wizard and make sure to select secondary is already initialized. Configure logshipping to be every 1 min(this is the minimum you can get).
Move all the logins, Jobs, SSIS packages.
During the switch to new server :
stop all the webservers connecting to the database
stop all the backup, copy and restore jobs of logshipping.
take a final Tail log backup and restore it on secondary WITH RECOVERY.
Sync the logins.
Change the web.config of the application to point it to the new server. This way you dont have to change the IP address of the new server to match the old one.
I have given the post restore steps in more detailed manner here.
Another option (similar to what happens in replication but more manual but less setup) is to do a FULL backup, restore over the network (with NORECOVERY) and continuously do Transactio Log backups with automatic restores. This would cut your downtime to only the last Tranlog Restore...
Best Answer
The best way to do this effectively I think is to backup all the databases and transfer via Site to Site VPN (setup between your location and East Coast Azure Datacenter).
To backup all databases, setup a SQL agent job with below step. Remember it's a CmdExec type and not a T-SQL statement.
And simple enough, copy all the .bak files from C:\SQL Backups to the remote Azure location. and use a restore script to restore all databases at once.
I recently found a tool (Teracopy) that does the copy/paste much faster than traditional Windows server copy functionality.
Hope this helps.