Moving VC Database from sql express to SQL 2005

[Guv]

We have Virtual Center 2.5 using a SQL Express 2005 database installed on the same machine. I now have a separate SQL 2005 server so I would like to move the Virtual Center database to it. These are the steps I took:

1. Stop Virtual Center service.

2. Backup current SQL Express 2005 database. This can be done using Microsoft SQL Server Management Studio Express which I have installed on the Virtual Center server

3. Restore the database on my SQL 2005 server using Microsoft SQL Server Management Studio. Setup the Sql account on the new sql 2005 server.

4. Update ODBC connection on VC server with new SQL server name and sql account

5. Connection tested successful.

Now when I got to start Virtual centre it does not start and in event viewer I get the error message Event id: 1000 which says something about not having the necessary DLL files which maybe a standard windows error message.

Is there any other step that I need to do or I have missed.

[RParker]

It looks like everything is in order. On your VC instance, is the database local? If and when you start just the SQL 2005 instance, do you get the same error? It could be something wrong with VC, because it seems to cooincide with the starting of the service, since ODBC is already started. Maybe you need to reinstall VC.

[NTurnbull]

Hi Guv, if you check your ODBC DSN properties is it using the ODBC driver for MSSQL or the MSSQL Native driver? VC 2.5 requires the Native driver, if its using the older one then you'll have to recreate the DSN (rename existing and create a new one with the same name and settings). Oh, also can you test the DSN from the VC server as when you move DB's from one MSSQL server to another using SQL Mixed mode authentication you can run into orphaned SQL usernames. Basically the DB contains the username along with the MSSQL server name so when you move the DB it can kinda go Meh?? If you get this there is a stored proceedure you can run to re-sync the SQL users: sp_change_users_logon I can't remember the exact syntax but a quick google should sufice (think its along the lines of sp_change_users_logon auto_fix "username" , "password")

Thanks,

Neil

[Guv]

The SQL server is a remote server and I have installed the native driver from sql 2005 server CD and rcreated the ODBC drive with the SQL native client and it connects successfully. But still the VC service does not start.

[NTurnbull]

Hi Guv, other things to check would be permissions to the VC database, the SQL user needs db_owner. Also you could check the SQL user isn't orphaned via running:

USE Database_name

sp_change_users_login 'update_one', 'SQL_Username', 'Password'

GO

Thanks,

Neil

[TCP]

Hi Guv,

This error indicates VC service can't start, because VC service can't connect to VC Database.

Check free disk space on SQL server, if low - delete unnecessary data and try to start VC service.

Try to reinstall VC with connection to SQL server.

[Guv]

I reinstalled Virtual Centre and it picked up the database. But when I now open up VC ,all my esx server hosts appear as not responding even though they are up.

Is there anything else I can do to get them seen by VC.

[NTurnbull]

Hi Guv, have you tried to re-connect the hosts from VC by right clicking on them -> Connect?

Thanks,

Neil

[Guv]

The only option I see on the ESX servers is disconnect.

I have tried restarting my ESX servers and still the only option is disconnect.

What should I do next.

[TCP]

Try to disconnect and when connect hosts again.

[RParker]

What should I do next.

OK, do this for each ESX host that's not responding.

-Disconnect from VC (right click Disconnect)

-go to the VIC users, and remove vpxuser and vimuser

-login to the ESX host service console, go to /etc/vmware/ssl (remove all the files, there should be 2 certificates) rm * -r -f

-restart agent services (service mgmt-vmware restart).

-reconnect to VC

Then wait a few minutes see if they come back

[zmclean]

Any update to this question? I want to move mine to a SQL 2005 server.

Z-Bone

[RParker]

> Any update to this question? I want to move mine to a SQL 2005 server.

Moving SQL is a pretty straight forward process IF you follow ALL the documentation on setting up SQL Server for VC Database. It's all in the ESX/VI documentation. These types of problems are rare, so if you followed ALL of the steps you should not have a problem.

ODBC is basically an IP pointer. The native SQL client allows you to connect to the remote SQL Database. So moving the location is a matter of changing WHERE the database is, but nothing else changes with respect to vCenter. So if it stops working, one of 2 things have happened. You don't have proper permissions (from documentation) or you didn't follow procedure to setup SQL Server (from documentation).

[zmclean]

I have quite a bit of SQL experience i just wanted to see if he had fixed his problem. I figured its almost too easy.

Z-Bone