Answered by:
Migrating

Question
-
Hi,
Which method is the best for fast migration of database?
Sunday, November 17, 2013 5:44 PM
Answers
-
i personally prefer Backup restore to migrate the databases
sp_rev_logins stored procedure to migrate users
script copy and execute the Jobs or using ssis to migrate SQL Jobs
plese give the size of database for best migration plans
Ramesh Babu Vavilla MCTS,MSBI
Sunday, November 17, 2013 6:49 PM -
I assume you want to migrate db to another sql instance of the same (or newer) version. "The best" depends on do you want the fastest method or method with minimal downtime.
The fastest method would be:
1) detach database
2) disconnect LUN on SAN storage from old SQL instance server
3) connect LUN to the new SQL instance server
4) attach database
No matter how big your database is, this will be blazingly fast, because there is no copy-ing of data. The downside is, there will be a short downtime (e.g. 1 minute), and prerequisite is that both sql instances use the same SAN storage.
The method with shortest downtime (no actual downtime, just one reconnect from the clients):
1) Create a synchronous mirror database on the other sql instance, initialize from a backup, without a witness.
2) Once mirror is in "synchronized" state, manually failover, so mirror becomes new principal
4) Stop/drop the mirroringIt requires a bit knowledge about mirroring.
More basic method would be:
1) full backup on old instance
2) restore with NORECOVERY on new instance
3) on old instance: tran log backup WITH NORECOVERY, so called "tail of the log" backup. That will prevent any modifications to the old database from now on. Db is unavailable from this moment, in "RECOVERING" state.
4) restore the log backup on the new instance with RECOVERY. Db is online.
Of course, in all methods clients must be redirected to the new instance.
Monday, November 18, 2013 12:21 AM
All replies
-
backup restoreSunday, November 17, 2013 6:06 PMAnswerer
-
detach attachSunday, November 17, 2013 6:28 PM
-
detach attach will have some downtimeSunday, November 17, 2013 6:32 PMAnswerer
-
i personally prefer Backup restore to migrate the databases
sp_rev_logins stored procedure to migrate users
script copy and execute the Jobs or using ssis to migrate SQL Jobs
plese give the size of database for best migration plans
Ramesh Babu Vavilla MCTS,MSBI
Sunday, November 17, 2013 6:49 PM -
I assume you want to migrate db to another sql instance of the same (or newer) version. "The best" depends on do you want the fastest method or method with minimal downtime.
The fastest method would be:
1) detach database
2) disconnect LUN on SAN storage from old SQL instance server
3) connect LUN to the new SQL instance server
4) attach database
No matter how big your database is, this will be blazingly fast, because there is no copy-ing of data. The downside is, there will be a short downtime (e.g. 1 minute), and prerequisite is that both sql instances use the same SAN storage.
The method with shortest downtime (no actual downtime, just one reconnect from the clients):
1) Create a synchronous mirror database on the other sql instance, initialize from a backup, without a witness.
2) Once mirror is in "synchronized" state, manually failover, so mirror becomes new principal
4) Stop/drop the mirroringIt requires a bit knowledge about mirroring.
More basic method would be:
1) full backup on old instance
2) restore with NORECOVERY on new instance
3) on old instance: tran log backup WITH NORECOVERY, so called "tail of the log" backup. That will prevent any modifications to the old database from now on. Db is unavailable from this moment, in "RECOVERING" state.
4) restore the log backup on the new instance with RECOVERY. Db is online.
Of course, in all methods clients must be redirected to the new instance.
Monday, November 18, 2013 12:21 AM