Later than planned but here it is: part two of moving the Virtual Center Database from SQL2000 to SQL2005.
Remember the steps from part 1:
- collect info about database (jobs/users/maintenance plan)
- detach database (SQL 2000)
- copy database files
- attach database (SQL 2000) (new!)
- attach database (SQL 2005)
- remove orphaned users (if they exist)
- Check ODBC settings
Step 1 Backup
Always make a backup whenever you are planning to do a change involving databases. It just makes live much easier if you have to do a rollback. Im assuming you’re familiar with backing up a SQL database so I’m not going to outline that. If not drop me a line.
Step 2 Collect info about databases (jobs/users/maintenance plan)
Some databases have jobs involved, because you have to make your own database it does not create any jobs but it may well be that someone has a job running involving the Virtual Center(VC)database. Maintenance plans is something else, I’m convinced this should is the first thing you should setup after creating a database, even if it is only taking care of a daily backup (if you’re not using a third party backup solution for SQL backups). Check the properties of each maintenance plan to see which database is involved.
Look at the sqlusers (Logins) defined and look at the properties to see what rights they have. Next look at the users at database level to see what rights the users have.
Step 3 Detach the database (SQL2000)
In this step we are detaching the database in order to be able to copy the ‘Databasename’_log.ldf and ‘Databasename’.mdf files (some databases also have a *.ndf file which also needs to be copied) to the SQL2005 machine.
Click on ‘OK’ twice and your database is detached. Now copy the files mentioned earlier to the SQL2005 server.
Step 4 Attach the original database (SQL2000)
This step was not mentioned in the overview, but can be useful to keep downtime to a minimum. In this step we will attach the original database again so that Virtual Center can continue to run again. After connection
The step is more or less the same as detaching it, just reverse: click on attach database and browse for the files.
Step 5 Attach the copy of the database (SQL2005)
Open the SQL Server Management Studio and attach the database copied from the SQL2000 machine.
Step 6 Check if you have orphaned users
After creating logins, users may not have permissions to access databases that have also been moved. This problem is described as an "orphaned user". If you try to grant the login access to the database, it may fail indicating the user already exists:
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 15023: User or role ‘%s’ already exists in the current database.
To check if there are any orphaned users in the database run a stored procedure by choosing ‘new query’ and entering the following statement:
EXEC sp_change_users_login ‘Report’
When the list shows you have an orphan user fix this by using one of the following statements:
If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login ‘Auto_Fix’, ‘user’
If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login ‘Auto_Fix’, ‘user’, ‘login’, ‘password’
For more information about transfering logins from a SQL Server 2000 to a SQL Server 2005 look at this Knowledgebase article from Microsoft :
Step 7 Check ODBC settings
Finally after you have checked the databases and the logins, it is time to switch databases by changing the ODBC-connection so that it points to the SQL2005 database instead of the SQL2000 database.
When detaching and attaching the database on the SQL2000 machine it can be necessary to stop and start the Virtual Center service to be sure not to get any unsuspected behaviour.
This procedure was never used for the VC database after all (we did successfully use it to move other databases though), a new instance of the VC which used SQL2005 was used. Just created a new datacenter and cluster and imported the hosts into the new instance of VC.
Nevertheless here’s the trick anyway. Have fun with it.
Update: When you want to make use of the opportunity whilst being in the middle of the migration to defragment the database, you should use the backup and restore mechanism. Just backup the database in SQL2000 and restore it in SQL2005. In order to not have any conflicts with dbusers, remove the existing dbusers before making the backup you use for recovery in SQL2005. If not, you will have to use the stored procedure described above to remove any orphaned users. The knowledgebase article from Microsoft I mentioned earlier also mentions DTS as an option to migrate the data from the databases (preferred if using role based security, but not applicable in this case: Virtual Center). If you use DTS you also have to import the users as described in the Microsoft KB-article. It will take a while if your database has grown a lot. The advantage doing it with DTS is that the table automatically is reindexed and of course is not fragmented. On the other hand for most situations reindexing can be achieved by scheduling it as part of a maintenance plan (In my opinion it should be part of it).