vSphere Upgrade and migrating from SQL 2000

So, with the introduction of vSphere, SQL 2000 is no longer supported.  For several environments this isn't an issue, but there are a few environments out there that want to upgrade who have kept SQL 2000 around.  Fortunately, after a little research and some trial and error, I found a process that helped me to successfully make the jump.  The main thing to watch out for is the orphaned user which I will discuss below.

• First, create the same username/password combo on the new SQL Server that owns the VMware databases on the original.  
  

• Create a backup of both your Virtual Center DB and your VMware Update Manager DB.  Back these up through SQL Enterprise Manager as .bak files and copy them somewhere accessible to the new SQL Server

•  Using SQL Management Studio, restore these databases into your new SQL Server

• Now you need to make the user account that was created in step 1 db_owner for both databases, however, when doing this you will most likely get an error. This is because of the orphaned user. Thanks to a coworker of mine, I found a quick fix.  To resolve this, you need to run a SQL script through Management Studio like the following

sp_change_users_login 'report'


If your application user is listed there, then run


sp_change_users_login 'auto_fix', 'your_user' (obviously, substitue the actual user for ‘your_user’)


In the first one you can identify user logins that might be a problem. Normally this will be an empty result set, but if a user is identified, you can use the second sp to try to correct their permissions.


You can also use the second sp on a user even if it does not show up in the first sp. The second sp will probably not find anything to fix, and will report that 0 orphaned users were corrected

• You can now assign the db_owner permissions.
  

•  Now, rename your System DSN connections for the Virtual Center and VUM Databases

• Create new System DSN connections matching the original names using the SQL Native Client and point these to the new SQL Database Server

That's it! You should now be ready to rock an roll with your virtual center upgrade without any issues.  Just make sure you keep the backups of  your databases just in case.  Also, as part of the upgrade backup your SSL information as is instructed in the vSphere upgrade documentation.