Migration from SQL2000 to SQL2005 failed.

[RobBuxton]

Hi All,

Attempts to move our VirtualCentre database to a SQLServer 2005 have failed.

Closed down VirtualCentre, took the DB offline and copy the database files.

On the SQLServer 2005 side, attach a database using the copied files.

Looking at the database from MS SQLServer Management Studio

Create a the user used by the VirtualCentre DSN to access the database.

The user created to access the database has its user and schema mapped to dbo.

The same user has been set as the DB Files owner again from within the SQL Server UI.

I've run the command EXEC sp_changedbowner @loginame = '', @map = 'true'

where vclogin equals my SQL Server user.

I've reconfigured the DSN and it is using the MS SQLServer ODBC (not the new Native Client) and taht connects and tests okay using the database user created above.

But, when I start the service it immediately stops with:

\[2007-03-12 10:35:34.677 'App' 4008 info] Starting VMware VirtualCenter 2.0.1 build-33643

\[2007-03-12 10:35:34.677 'App' 4008 info] Account name: SYSTEM

\[2007-03-12 10:35:34.677 'App' 4008 info] \[VpxOsLayer] Enabled low-frag process heap.

\[2007-03-12 10:35:34.677 'App' 4008 info] \[VpxOsLayer] Enabled low-frag crt heap.

\[2007-03-12 10:35:34.677 'App' 4008 info] \[VpxLRO] 32 max LROs

\[2007-03-12 10:35:34.677 'App' 4008 info] \[VpxLRO] 6 reserved internal LROs

\[2007-03-12 10:35:34.677 'App' 4008 info] \[VpxLRO] 6 reserved blocker LROs

\[2007-03-12 10:35:34.677 'App' 4008 info] \[VpxLRO] 6 reserved short LROs

\[2007-03-12 10:35:34.677 'App' 4008 info] \[VpxLRO] 2 reserved long LROs

\[2007-03-12 10:35:34.677 'App' 4008 info] \[VpxLRO] 600-second task lifetime

\[2007-03-12 10:35:34.802 'App' 4008 error] "ODBC error: (42S02) - \[Microsoft]\[ODBC SQL Server Driver]\[SQL Server]Invalid object name 'vpx_sequence'." is returned when executing SQL statement "select id from vpx_sequence where name = ?"

\[2007-03-12 10:35:34.802 'App' 4008 error] Failed to intialize VMware VirtualCenter. Shutting down...

\[2007-03-12 10:35:34.802 'App' 4008 info] Shutting down VMware VirtualCenter now

\[2007-03-12 10:35:34.802 'App' 4008 error] SetServiceStatus failed: The handle is invalid

There's obviously some error somewhere, and I'm still struggling with the oddities of SQLServer 2005.

Any clues as to what may still be mis-configured?

The SQL 2005 article I've read talks of deleting "VCUser", but all attempts to delete the original DB Owner have failed as schemas and then objects are owner.

It seems to be tripping up as the SQL User I want to use has the same name in the SQL2005 and SQL 2000 implementations.

The other option I have is to just reinstall VirtualCentre and create a new database from scratch.

Any idea what the risks of this might be?

[bretti]

I'm no SQL expert, but I have a script at the office that get's me out of a jam once in a while. Especially when moving databases and using the same logon. I will find it tomorrow and post it here.

Just out of curiosity, I see that you are detaching the database files and moving them to SQL 2005. Is that a common upgrade path? Have you tried using a DTS package to copy the data and tables from one SQL server to the other?

[jo_strasser]

Hi!

I have the same problem!!!

I tried to import data as task with the sql import wizzard, but it fails.

The data was copied, but the vc service goes down...

i set the user to "sa", but it fails...

How i can upgrade the mssql2k database to an SQL 2005 DB???

Have anyone an idea???

Or an HowTo??

I´ve open a call, but at this moment, everyone is planless...

cu Jay

[bretti]

OK, this is the script that I have used before on other database moves.

sp_change_users_login 'Update_One', 'sysdba', 'sysdba'

Run this on the target DB. Replace sysdba with the account name you are trying to update.

The issue this resolves is that the account name on the source SQL server DB and on the Target SQL server DB may be named the same, but they are still different as far as SQL server is concerned.

This will replace the old name with the new name from the new SQL server.

[RobBuxton]

Alas, so near yet so far.

I can get the above command to work, but I still get the error;

ODBC error: (42S02) - \[Microsoft]\[ODBC SQL Server Driver]\[SQL Server]Invalid object name 'vpx_sequence'." is returned when executing SQL statement "select id from vpx_sequence where name = ?"

We were on the base version of SQL 2005 and during testing I've upgraded to SP2, the behaviour changed a bit but not the overall result.

If I attach the database and change the owner of the database to 'VCLogin' during the attach then the sp_change_users_login fails as the user is already a "User" in the database. - "The login already has an account under a different user name."

If I attach the database using a different user, then the command works but I then cannot change the owner of the database.

So, depending on which route I take I either get the DB owned by VCLogin, but the tie in with the original VCLogin is broken or the tie in seems to be okay but the ownership is broken.

Either way access from the VC service fails as it cannot get access to the tables.

If there's a way it must be a rather convoluted series of steps carried out in a very particular order.

[bretti]

I was playing around with this SQL statement "select id from vpx_sequence where name = ?" on our SQL 200 server and vcenter database.

When I run this - select id from vpx_sequence where name = '?'

I get this returned - Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'vpx_sequence'.

So I changed it a little bit. I added the database name.

select id from vcenter[/b].vpx_sequence where name = '?'

That processed and returned - (0 row(s) affected)

When I looked in that table there is no value in the name column for ?, I wonder what exactly it's looking for.

My question is, did you change the name of the database during the process of moving it to the 2005 server?

Also, can you run that SQL command manually with Query Analyzer on the 2005 server? What does it return for you?

To answer your other question about starting fresh, you will loose most of your setup in virtual center. It can be re-created, but is time consuming. Old Performance data will be gone, Permissions will be gone, folder and datacenter structure will be gone, Alarms will be gone. If you only have a handful of hosts it may be easier to start over.

[RobBuxton]

Thanks for the response.

I'm seeing the same behaviour. I think the query would just return a blank which indicates to VC that the connection is there.

But because I cannot get the ownership resolved correctly the call fails.

I just cannot tie things together so that it doesn't need the VCLogin prefix.

The DB name is the same, it all seems related to the art of trying to get SQL Server 2005 to accept the VCLogin is the owner of the database. There's gotta be a trick somewhere but it's frustrated me an the DBA. Both of us new to the what seems to be the black arts of SQL 2005.

There also seems to be other threads in a similar vein, but I've not seen an answer that works. They talk about dropping the user, but I can't as it owns items within the database!

[bretti]

OK. I think you may have said this already, but is the user account already created on the SQL server before you bring over the database or after?

If you've tried this already, please ignore... This would be my next try in this order.

-On the 2005 SQL server, remove any trace of the vcenter DB and VCLogin from the server.

-On the 2005 SQL server, create a sql logon account that matches the name exactly of the vcenter database owner on the 2000 SQL server.

-Mount up your DB files from the 2000 SQL server onto the 2005 SQL server.

-Once the DB is started, connect with Query Analyzer as SA and run the update one SQL script noted above to change the DB owner to the same user account.

-Disconnect Query Analyzer from the Database as SA.

-Reconnect to the database with query analyzer with the account you setup as the DB owner.

-If you can connect, run a simple select * from vpx_sequence to see if you get any rows returned. Then it should be working.

-If you can NOT connect, then the account is still broken.

I think the real key to this is having the same user account created on the target server before moving the database over.

I have not tried SQL 2005 at all, so use with caution...

[RobBuxton]

I'll give it a go in a few days - just off for a few days of music at a WOMAD festival - so I'll leave the joys of SQL behind for a bit!

Pretty sure I've created the DB with and without the user being present.

[bretti]

Have a good time! Didn't know what WOMAD was, so I had to look it up. It sounds like a lot of fun. Forget about SQL...

[RobBuxton]

Eureka - must've been the break away, but I've successfully moved the database across.

Here are the steps, including a couple of possible false steps. I'm not sure if these impacted on the overall result.

Shutdown VC and then took the SQL 2000 DB Offline.

Copied the mdf and ldf files to the SQL Server 2005 Server.

On the SQL 2005 Server

Create a user (e.g. VCLogin) which is the same name as the user on the SQL 2000 server that accesses the database.

Attach the database files and change the owner of the files to VCLogin during the attach.

Tried to run the script:

sp_change_users_login 'Update_One', 'VCLogin', 'VCLogin'

But this failed with:

Msg 15063, Level 16, State 1, Procedure sp_change_users_login, Line 143

The login already has an account under a different user name.

Changed the database owner to SA and repeated the above query which now worked.

Under Schemas for the VirtualCentre DB, changed the owner of db_owner to dbo.

Under security changed VCLogin to have VirtualCentre as its default DB.

Connected to the Query Analyzer using VCLogin and found that the select * from vpx_sequence now worked.

So, the DB is owned by sa.

The DB User VCLogin, uses the VCLogin schema and is has a db_owner role.

The VCLogin user has VC as its default database and the user and schema are also VCLogin.

Then changed the DSN Link to point to the new DB Server, tested the connection and then started teh VC Service.

[bretti]

Nice Job Rob! I'm happy it worked out for you.

Thank you for sharing those steps, I'm sure others will find it very helpful.

Way to go! :smileygrin:

[Maxzanna]

good job RobBuxton ,

it works fine for me too.

V.C. 2.0.1 and move from slq 2000 to sql 2005 sp2

[COWIT]

Just a follow-up to this:

I encountered this same problem after having to restore the DB from a backup. The key to the resolution was making the default database your Virtual Center database for the ODBC user.

You noticed that the script would not execute manuallyl unless you placed the db name in front of the command, and that was the same problem that Virtual Center was having when trying to start up. Unless you specify a db name in your SQL command that you are executing, SQL automatically assumes that the default db is the one on which you want to execute the command. Therefore, vpxd was executing that transaction on whatever the default database was for your ODBC user account in SQL.

Hope that this ties up some loose ends...