| Author |
Topic |
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
 Posted - 2007-12-10 : 22:15:59
|
| What is the correct way to move applications from one server (2000) to another server(SQL 2005). I have to move all the databases as well as applications from one server to another.Here is my procedure:1) Backup all 65 databases in first server(600GB total)2)copy all the backup files from one server to another(destination)3)Make all database(single user)and take transaction Log backup4) Restore all database to destination server in norecovery mode and restore all transaction log in Recovery mode)5)stop server 1 and point the application to another serverSo how much downtime i have to face since it is 24/7 production server. and Can you please give the correct details so i can apply. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-10 : 22:38:12
|
| The app uses all 65 dbs? If not, better to move db one by one to reduce app down time. |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-11 : 09:45:03
|
| ALL 65 DBs uses application.SO i am concerned about the logins and role stuff. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-12-11 : 10:29:28
|
| backup master, model and msdb.you can detach, move and then reattach all databases to the new server.note that all paths on the both servers have to be same.also note that when you detach a db and for wahtever reason can't reattach it back you've in big trouble.if this is a problem then use backup/restore_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-12-11 : 11:22:43
|
| Sodeep...Your approach is good:1. but restore using standby instead of norecovery so you can verify that the database will definitely go online once you apply that last log backup2. this will allow you to remap the logins in advance and you can do a separate test for connecting to the databases from another test application3. make sure you've also planned sql jobs (as implied by the msdb backup and restore Mladen is suggesting)Also, I'd skip the change to single user mode on the source databases since you still want users to access the databases while preparing for the move.--------------------keeping it simple... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-11 : 11:38:31
|
| " make sure you've also planned sql jobs (as implied by the msdb backup and restore Mladen is suggesting)Also, I'd skip the change to single user mode on the source databases since you still want users to access the databases while preparing for the move."Sorry i didn't get this .Can you explain clearly.Is there advantage with Standy mode. Next thing, Should i keep all databases in Read/Only mode but can i restore with this optionsThanks--------------------keeping it simple...[/quote] |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-12-11 : 11:55:16
|
| standby means it is in readonly mode and you can still restore transaction log backups--main advantage is that you know that the backup is working and not just in a state of restore... viewing records give me that assurance that the restore is successful (it might be different in your case so use whatever recovery mode which puts you at ease)yes, you can leave them at this state until you've applied the last transaction logI've used this whenever I'm preparing to move dbs from one server to another.. downtime is less than a minute... the datasource file is changed in advance as well and saved in a different filenameso all it takes is applying the last transaction log with recovery and a change of filename at the application server (with restart of the application service if needed)--------------------keeping it simple... |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-11 : 12:25:51
|
| Thanks,How do you handle Logins and role issues when moving all databases from SQL server 2000 to completely new server SQL 2005.I mean remap logins and role. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-11 : 22:32:55
|
| SSIS has task to move sql logins, db backup/restore will take care of db roles. I still don't believe single app uses 65 dbs (db can't uses app). |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-12-12 : 00:51:42
|
| to remap logins you need to issueexec sp_change_users_login with update_one optionafter creating the logins on the server levelcheck BOL for more detailsroles are not affected unless they're server roles, then you need to create them on the new server--------------------keeping it simple... |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-12-12 : 01:08:48
|
quote:
Originally posted by rmiao
I still don't believe single app uses 65 dbs (db can't uses app).
i'm guessing it's one of those "one database per customer" situations. it works fine when you have 1 customer. not so great when you have 65. 
elsasoft.org |
|
|
|
|
Correct way to move applications