Upgrading a vCenter SQL Express database
December 3, 2012 3 Comments
The other day I got my hands on a full vCenter SQL 2005 SP2 Express database. The vCenter database filled up the 4GB allowed for SQL 2005 Express DBs.
So as the shop I was in had no SQL’s to work with, it was decided to upgrade to SQL 2008 R2 SP2 Express, which has a 10GB limit per database.
The environment was running on vSphere 5.0, and I had upgraded it recently from 4.1 to 5.0. There’s a quite an increase of tables between 4.1 and 5.0, so this will happen to most environments sooner or later.
Note this procedure will only work if you will still be using the same vCenter server as in the beginning. Not to be used for whole vCenter relocations.
So the way to do this is quite easy, and you don’t need to be a SQL admin. :)
You will need to break this procedure into 3 parts: 1) Preparation 2) Upgrade 3) Test
- ODBC connections: Make sure to check what the ODBC connection is configured to Integrated Windows or SQL.
- Services: Make sure to check what user is used to run the Virtual Center Server service. Most likely System or a domain/local admin.
- Name of the Database: I recommend not to change the name of the database. Most likely the name will end at SQL*\SQLEXP_VIM.
- Get the installation files for SQL 2008 R2 Express and also for SQL Server Management Studio Express.
- Open up the SQL instance using SQL Management Studio, and note who the DBOwner is for each database that will be moved. If a SQL user note that down as well.
- Stop all vCenter related services
- vSphere Web Client
- VMware VirtualCenter Server Delayed
- VMware VirtualCenter Management Webservices Delayed
- VMware vSphere Update Manager Service.
- VMware vSphere Profile-Driven Storage
- vCenter Inventory Service
- Put all stopped services to disabled.
- This is done as you will need to restart the server after a SQL upgrade and you will not want the services to start when you do.
- Open up the old SQL 2005 Express database using the SQL Management Studio.
- Backup each database (e.g. if you got vCenter and Update Manager databases).
- Right click the database, go to Tasks and select Backup. Backup to a known location.
- Go the the DATA folder for the SQL instance, for 32 bit its in c:/Program Files/Microsoft SQL Server//…, and for 64 bits in c:/Program Files (x86)/….
- There you will find all the database and log files for the vCenter server.
- Names are most likely VIM_VCDB.ldf for logs, and VIM_VCDB.mdf for the database itself.
- Detach the database. Make sure you stopped the vCenter services.
- Right click the database, go to Tasks and select Detach.
- Move the database and log file to another location.
- Though you can upgrade 2005 Express to 2008 Express, I find it much “cleaner” to just uninstall 2005 and install a new SQL 2008 R2 Express instance
- Remove the SQL 2005 Express instance. (you will need to turn off the SQL service)
- Install a new SQL 2008 R2 Express instance.
- When installing a new database make sure you write down the sa account password and/or give a domain/computer account sysadmin privileges to the instance.
- Make sure you name the instance as SQLEXP_VIM. Otherwise you will need to change a registry setting for the VirtualCenter service to start (pointing it to the new name).
- Just to make sure, restart again.
- Move the database and log file to the new folder for the 2008 R Express instance.
- Login to the instance using SQL Studio Manager.
- Right click databases and select Tasks->Attach. Attach the database. You don’t need to attach another log file when the pop-up appears, theres only 1 log file already associated with the database.
- Go to properties of the vCenter database and make sure the DBO (database owner) is the same one as on the 2005 instance.
- You might need to add the user in the Login section of the instance.
- Create a new file using notepad, save it as connections.udl (must end in udl). Go to properties and to Connection. There you can try out the SQL connection. This is a handy tool to use with SQL connections test. This will be used in the next sections.
- Go to SQL Server Configuration Manager (should available in the Start menu).
- Select SQL server network configuration and enable both Named pipes and TCP/IP.
- Go to Properties on TCP/IP. Select IP Addresses and go to the bottom where you see a section called IPAll. Put in 1433 in TCP port. Push OK.
- Go to both ODBC managers (32bit and 64 bit: C:\Windows\SysWOW64 for 32bit and C:\Windows\system32 for 64bit, yes they have conflicting names…).
- Make sure you have a connection to the database. 32 bit is for Update Manager.
- The user that connects to the database, needs to be a user that has access to Database through the SQL Studio Manager. Best practice is a domain system account, that is a DBO on the vCenter database, and is the one that starts the vCenter service as well.
- Open SQL Studio Manager and open up the vCenter Database
- Navigate to vCenter DB > Programmability > Stored Procedures.
- Ensure that the stored procedures listed in this KB are still there: Verifying jobs and stored procedures installed in VMware vCenter Server 5.0
- Put all the services to their former startup selection.
- Restart the server, or go through restarting the services. I find it easier just to restart it.
- After restarting make sure the vCenter server service starts and all your performance data is showing.
Notes (stuff you should know about vCenter SQL Express databases):
- Rollup jobs (the jobs that move performance data between week->month->year) are not running as a separate job, so you should not need to fix those. They are being run by the VirtualCenter service and are a part of the database (located in vCenter DB > Programmability > Stored Procedures). This is only the case for SQL Express instances.
- I always recommend putting vCenter Databases on real SQL servers. But I’ve seen small environments of at least 100 machines run for years on an Express database (NOT SUPPORTED).
- Most misconfigurations on SQL Express DB’s are user related. Double check the user that runs the VirtualCenter service, and who is the DBO, and ODBC connections.
KBs used in this blog post:
- Verifying jobs and stored procedures installed in VMware vCenter Server 5.0
- Moving the vCenter SQL database
- And all the links under this KB.