Upgrade from MSSQL 2005 to 2008 R2

[Neilly]

Hi,

Here is where I started :

1. vCenter Server 4.1 U2 build 491557

2. on Windows 2008 Std 64 bit

3. using MS SQL 2005 64 bit on a remote Win2k8 std 64 bit server.

I needed to upgrade the MS SQL from 2005 to 2008 R2 in preparation for the upgrade of another app using the same SQL server as my vCenter server.

I did an in-place upgrade of MS SQL 2005 to 2008 R2. No errors were encountered.

Both of the previous 2005 db's are present and are running in compatibility mode 90 (sql 2005).

The vCenter Server starts up fine and has no connectivity nor usability issues. It is still using the sql native odbc driver for 2005 - I have installed the newer driver to support connection to the sql 2008 server, but I have not configured it yet.

But... I don't think I want to be running in compatibility mode forever eh? So how do I 'upgrade' the vcenter server db to 2008 keeping the db on the server it is currently on? Is that even possible?

If there was a document that spelled out all the steps that would be great - been looking but haven't found it yet. Seen lots of docs on moving, migrating, transferring, relocating etc. But nothing about an in-place upgrade... Maybe I just painted myself into a corner...

Thanks for your kind assistance.

Ron "I-aint-no-dba"

[orthohin]

Please have a look..

http://technet.microsoft.com/en-us/library/bb677622.aspx

Regards,
Milton

[Neilly]

Thanks, but I'm not sure what is in the Microsoft documents that would answer my question. As I posted, I have already performed an upgrade of MS SQL 2005 to MS SQL 2008. My question is : How do I upgrade the vCenter Server database files on the MS SQL 2008 server from their current 2005 format/schema to 2008 format/schema in place. Maybe I lack the correct terminology to describe what I am after. How about this:

A) Started with:

1. vCenter Server v4.0 on Windows 2008 Std 64 bit,

2. MS SQL 2005 Std on a remote Windows 2008 Std 64 bit server

B) Upgraded VC

1. upgraded v4.0 to v4.1

2. kept the MS SQL 2005 server

C) Upgraded the MS SQL server

1. inplace upgrade of MS SQL 2005 to MS SQL 2008 R2

So that brings me to today's configuration:

1. vCenter Server v4.1 on Windows 2008 Std 64 bit

2. MS SQL 2008 R2 Std on a remote Windows 2008 Std 64 bit server

   2.1 The vcdb is being run in compatibility mode 90

   2.2 The vCenter Server is using the SQL Native Client for MS SQL 2005

And what I want to do next is upgrade the vcdb files/schema from 2005 to 2008. I would like to do it in-place if possible. How do I do that?

A thought I had was maybe to do this:

1. Shutdown the vCenter services

2. Upgrade the SQL Native Client (ie create a new DSN with the 2008 version of the driver)

3. Start up the vCenter services and ensure that everything is still working

4. Shutdown the vCenter services

5. Change the MS SQL 2008 vcdb database from compatibility mode 90 to mode 100

6. Run the vCenter Server installer and maybe it will provide an option to upgrade the vcdb files/schema in place on the MS SQL 2008 server.

Does that sound like a workable plan?

Thanks,

Ron

[Zarathustra82]

Hey there,

Did you manage to perform the steps you mention in your last comment? I know the post is quite old, but I'm in the same situation at the moment and I'm wondering if it's possible to upgrade the vcdb schema from SQL 2005 to 2008 following your plan .

Thanks.

[jdptechnc]

And what I want to do next is upgrade the vcdb files/schema from 2005 to 2008. I would like to do it in-place if possible. How do I do that?

A thought I had was maybe to do this:

1. Shutdown the vCenter services

2. Upgrade the SQL Native Client (ie create a new DSN with the 2008 version of the driver)

3. Start up the vCenter services and ensure that everything is still working

4. Shutdown the vCenter services

5. Change the MS SQL 2008 vcdb database from compatibility mode 90 to mode 100

6. Run the vCenter Server installer and maybe it will provide an option to upgrade the vcdb files/schema in place on the MS SQL 2008 server.

Does that sound like a workable plan?

Thanks,

Ron

Steps 1-5 are all you need.  The only thing running the vCenter server installer will do, with regard to the database, is upgrade the vCenter bits to the current version (and you've already done that).  With regard to SQL Server, changing the compatibility mode of the database removes deprecated functionality that was last supported in SQL 2005 and adds functionality that would have been introduced in SQL 2008.

[Ytsejamer1]

Glad I ran across this...however I had a couple of things that needed to happen going from remote SQL2005 to a different remote SQL2008R2 on my 5.1 setup.

vCenter Server = W2K3R2 SP2 x64 Standard, vCenter 5.1.0B, Update Manager 5.1

Legacy SQL Server = Windows 2003R2 SP2 Enterprise (x86), SQL 2005 Enterprise

New SQL Server = Windows 2008R2 SP1 Enterprise, SQL 2008R2 Enterprise

1. Shut all the vmware services down on your vcenter server.

2. Databases on 2005 server backed up and restored to 2008R2 server.  For now, keep the 2005 DBs online.

3. Agent rollup jobs manually created on 2008R2 server

4. On vcenter server, install latest SNAC for 2008R2

5. Open Regedit, and backup the entire registry and then minimize

6. Reconfigure the original DSNs using the 2005 SNAC and simply rename them and test functionality.

*Note* On Windows 2003 x64, vCenter may use 32-bit DSN and UpdateManager may use 64-bit DSN - configure in appropriate ODBC applet.

7. Create new DSNs using SQL Server Native Client 10.0 to connect to 2008R2 server.  Configure similar to the older ones, using the same name.

8. In Registry, go to HKEY_LOCAL_MACHINE\SOFTWARE\VMware, Inc.\VMware VirtualCenter\DB (under My Computer).  The fourth string value should likely refer to the 2005 SNAC - "SQL Native Client"...change it to (without quotes) "SQL Server Native Client 10.0"

9. In Registry, go to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\VMware, Inc.\VMware Update Manager\DB (under My Computer). Again, the fourth string value needs to reflect the newer SNAC.  Replace the value with same as above. Close regedit.

10. If on 2003 x64 go to C:\Documents and Settings\All Users\Application Data\VMware\VMware VirtualCenter.  If on 2008+, go to C:\ProgramData\VMware\VMware VirtualCenter. Look for a vcdb.properties file.  For safety's sake, make a backup copy of it somewhere.  Then edit the file in Notepad and replace the SQL server entry in the jdbc connection string.

11. This is key...start SSO service FIRST!!!!!

12. Start vCenter service and all other remaining services...and away you go!