October 15, 2004 at 1:53 pm
I have a Database Migration Project coming up soon. I have to migrate Databases from the 4 existing servers to 4 new servers. I have put together a Database Migration Methodology. Would you please scan the steps and see if I am covering everything. I want this to be a successful project.
I have not decided which method (refer to Step 7 in the Database Migration Methodology) I am going to use to transfer the Databases. I am leaning towards the DTS Import/Export Wizard or DTS Copy SQL Server Objects Task since both copies the DB Users, Roles, Server Logins and Object-Level Permissions. I did not see any differences between these two methods. Are there any? I want to have less work as possible after the transfer.
I was going to Backup/Restore the User Databases but I heard I would need to do the same for the Master Database and Microsoft would not support a server which has had it's Master Database restored from another server. Is this true? Would I have to restore the Msdb also? Would this cause any system problems? I am using SQL Server 2000, SP3. Thanks in advance.
Here is are the steps in my Methodology:
Database Migration Methodology
1. __ Identify/Document all Server and Database Settings.
2. __ Script all Databases and Objects. Store to a Network Folder.
3. __ Clean-up and Shrink Databases.
4. __ Ensure the most recent Server and Database Backups are available.
5. __ Request users to log out of Databases. (If necessary, use Sp_who and Kill Spid or set the Database in Single User Mode.)
6. __ DTS Transfer Logins Task. (Optional, depends on which Database Transfer Method is used.)
7. __ Use one of the methods to transfer the Databases.
a. DTS Import/Export Wizard. (Copies DB Users, Roles, Server Logins and Object-Level Permissions.)
b. DTS Copy SQL Server Objects Task. (Copies DB Users, Roles, Server Logins and Object-Level Permissions.)
c. Copy Database Wizard. (Copies Logins, Shared SP from the Master DB, Jobs from Msdb and User-defined error messages to be copied.) Allow multiple Databases to be copied.
d. DTS Transfer Database Objects Tasks (Copies server-wide information not necessarily found in individual databases. These Tasks are for situations not addressed by the Copy SQL Server Objects Task. These Tasks are: Transfer Database, Transfer Error Messages, Transfer Logins, Transfer Jobs and Transfer Master Stored Procedures.)
e. Use the Backup/Restore Commands in SQL Query Analyzer.
8. __ Check for and relink any orphaned users with the sp_change_users_login stored procedure.
9. __ Verify Server Options and Database Options.
10. __ Open DTS Packages on old server and save each package to the new server.
11. __ Recreate DTS Packages’ Jobs (Schedule DTS Packages first.)
12. __ Shutdown the old server and rename the new server (My Computer, Network Identification.)
13. __ Define Custom Operators on new server. (That was previously on old server.)
14. __ Define Alerts on new server. (That was previously on old server.)
15. __ Define Accounts and support for Microsoft Exchange Mail. (That was previously on old server.)
16. __ DTS Transfer Jobs Task. (Optional, best to manually recreate Jobs.)
17. __ Reschedule Other Jobs (Backups, Maintenance Plans, etc.)
18. __ Execute the Test Plan.
a. Verify all objects in the Database were properly migrated. Use this list to identify potential hazards in the migration. (Create a list of objects to be migrated before the migration.)
b. Query database tables using SQL Query Analyzer.
c. Point front-end applications to migrated databases and have users thoroughly test the reconfigured applications to verify consistency with old servers and databases.
October 18, 2004 at 7:20 am
Couple things.
Rename - be sure you rename the SQL Server as well as the computer.
The copy objects wizard has been flaky for me (and the task). I'd back and restore the databases. You don't need to do the system databases. When you create the new dbs on the new server, that does the entries for the dbs. The copy logins can work or get sp_help_revlogin from MS for that. You are moving packages, and jobs, so you don't need to move msdb.
If you care about MSDB job history, move it or extract it out for future reference.
May need statistics/index updates at the end to be safe. Alsop recompiles.
Be sure to keep an eye on config/settings if this is a different size server.
October 18, 2004 at 10:33 am
I agree with Steve that you won't get any problems with the backup/restore of user database as both servers are using the same version.
If you have a lot of users and/or databases then you might consider scripting the logons and users and then use the script to apply the change to the new servers as fixing orphans can be a bit time consuming if the matching is not obvious.
You can also script jobs, which is my preference for moving them.
October 19, 2004 at 7:08 am
In addtion, two things
When you transfer logins they will not necessarily retain their default database.
If you transfer sql logins be careful of passwords. If the destination server has a different collation to the source the encryted passwords will not work.
Far away is close at hand in the images of elsewhere.
Anon.
October 19, 2004 at 10:11 am
Very good point about default database. SQL2k for some application is very senstive about default login so I use this simple query to generate the script from source server and apply to target server.
-- EXEC sp_defaultdb 'Victoria', 'pubs'
use master
select 'EXEC sp_defaultdb ' + '''' + name + '''' + ', ' + '''' + dbname + '''' + char(13) + 'GO' + char(13)
from syslogins
WHERE dbname is not null
October 19, 2004 at 11:52 am
Yep, forgot about that one. Definitely be sure you script everything out in case you forget somthing and need to find out what it was. Permission is the main one here.
October 24, 2004 at 3:52 am
Here's the MS KB for more info if you need it.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;314546
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply