A website by Richard Feich

Lately I have been involved with migrating websites from one production server to another. All of these websites are CMS applications with databases on the back-end; implemented with Microsoft Technologies. This post will discuss the database aspect of website migration. There are a number of different methods to copy or move a database: detach-move-attach, backup-move-restore, export-import, DTS, Copy Database Wizard. Since we are not only moving the databases, we are also upgrading from SQL Server 2000 to SQL Server 2005, I decided to give Copy Database Wizard (CDW) a try…it’s a tool under SQL Management Studio full version. In theory CDW sounds great, but in practice you’ll pull your hair out getting it to work in some cases. CDW is very finicky about set-up, permissions, etc.

Preparation

• Check the source database ownership. If needed, change it to an account with sysadmin membership. For this example I used the sa account. To change DB ownership execute the following code in Query Analyzer:

use [currentdatabase]
go
sp_changedbowner 'yourusername', 'true'
go

• Check the source database for orphaned users and resolve or delete them. Here is an excellent article about orphaned users along with a Stored Procedure for identifying them:
  http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
  Orphaned users will cause a failure at the destination during the DB copy.
• On the source server add an entry to the LMHOSTS database file which is the IP address and NTLM name of the destination server. For additional info: http://support.microsoft.com/kb/314108
• To avoid data loss or inconsistency, you must be sure there are no active sessions attached to the database being moved or copied. It’s probably a good idea to do it off-hours and take the website off line during the migration.
• Make sure the SQL Server Agent has the proper user account setting…should be set to the Local System Account (NT AUTHORITY\System).

Process

1. Login to the destination server with admin credentials, fire-up Management studio and the CDW tool. Right click on a db, CWD is under tasks. CDW will step you through various screens to build the SSIS[DTS] package.
2. Specify the Source SQL Server by name or IP, use SQL Server Authentication, enter credentials of the saaccount – ID and password.
3. Specify the SQL Destination by name or IP, use SQL Server Authentication, enter sa account credentials of the sa account — ID and password.
4. Select Transfer Method, use the SQL Management Object method. This will allow the DBs to stay online and operational during the copy.
5. Select databases to be copied — use the copy checkbox…don’t move it.
6. Configure destination database. For this example I just used the default destination name and location. I also selected “drop any database at destination if one exists” to overwrite it.
7. Select the database objects. Leave the logins selected and nothing else. This will bring over the logins, so your connection strings should work at the destination with only a server name change.
8. Configure Package screen. Select logging options. Sometimes it’s good to direct logging to a file for better debugging.
9. Schedule package or run immediately — I have been just running immediately.
10. Verify choices — go back if needed.
11. Run the job — all steps should be all green [pass] or study the log file if debugging is needed.
12. Refresh Management Studio database list. Right click top server node and select refresh. The new databases should now appear. Check tables, users, etc.
13. Run sp_updatestats against the database on the destination server to
    update statistics and make sure that the copied database is performing optimally.
14. Update connection strings and test application.

Trackback URI | Comments RSS