Why not VirtualCenter with SQL 2005 Express?

As of VirtualCenter 2.5 (VC) MSDE was replaced by SQL 2005 Express as the default installation database. This post explores the idea of running small and medium business (SMB) VMware virtual infrastructure (VI) environments with the free SQL 2005 Express version instead of a fully licensed instance of SQL 2005/2000.

To start with VMware’s VI3.5 and VC2.5 Installation Guide states:

The Microsoft SQL Server 2005 Express database package is installed and configured when you select Microsoft SQL Server 2005 Express as your database during VirtualCenter installation or upgrade. This is shown in Installing VMware Infrastructure Management Software. No additional configuration is required.

If Microsoft SQL Server 2005 Express is already installed, review the required database patches specified in Table 2-1. If you do not prepare your database correctly, the VirtualCenter installer might display error and warning messages.See www.microsoft.com/sql/editions/express/default.mspx.

Note: Microsoft SQL Server 2005 Express is intended to be used for small deployments of up to 5 hosts and 50 virtual machines.

5 ESX hosts and 50 VMs is definitely enough support for most small business deployments. Frankly, with modern hardware pricing the 50 VM limit will be reached before ever deploying 5 ESX hosts. 16 guests on a host is a common scenario these days which, if you do the math, creates a 3 host ESX Cluster.

So why would a company that does not expect to grow it’s number of VMs beyond 50 not use SQL 2005 Express? Here’s the 2 best reasons I could think of, and then a counter argument for each.

4 GB database size limit of SQL 2005 Express - The VirtualCenter database size calculator shows that even an agressive 20:1 consolidation ratio for 5 hosts and 100 VMs doesn’t even come close to the 4 GB size limitation.

VC as a VM - A lot of SMBs choose to run VC as a VM. If you follow best practices then you should separate the SQL instance from the VC server. However, I’m willing to bet the intent of that configuration is meant for larger deployments where processor, RAM, and disk cycles are more intense. Once again, based on the numbers shown in the database calculator (and past experience) just give your VC VM 4 GB of RAM and 2 vCPUs and you should be OK with the SQL 2005 Express instance installed locally.

Let me know if there is a better reason not to use SQL Express for VirtualCenter in small implementations.

Tags: sql, sql 2005 express, VI 3.5, vi3, vi3.5, virtualcenter, vmware

This entry was posted on Friday, January 2nd, 2009 at 12:43 am and is filed under vc2.5, vi3, vmware. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Alastair Cooke

Reasin number one misses an important point. In order to achieve the forecast database size the statistics rollup needs to run, this requires the SQLAgent service which is not part of the SQL 2005 Express install! Database growth under SQL 2005 Express will be much higher than forecast using teh VI Client, so a 5 server 50 VM environment may well reach 4 GB in a year.
Having said that my 3 host, 16 VM envoronment has grown to 126MB in the 11 months since it was installed, so a 5 host, 50 VM environment shoud be under 4 GB after two years.
Remind me to fing the SQL commands to run the database rollups using a scheduled task.

Al.

Tom

Why do you want 2 years' historical data??

What is really needed is a way to export historical data out of SQL Express and save it to another database for review, thus keeping the main 6 months / 1 year of data always available, then using the second historical database only when necessary.

Anyone have a script for doing this??

I did make a comment with several links to freeware etc. tools and stuff pertaining to SQL Express, but I don't see it here...I don't know why it was deleted.

Thank you, Tom

rbrambley

Tom,

I never saw your post with the links. I do hold for review any post
with more than 2 links because of spam, but I do not recall seeing
yours. Maybe it fell into my spam comments and I'll double check. In
the meantime can I get you to repost the comment?

Andrew

@Alastair - VC will manage the roll up jobs itself if it detects it's using SQL Express, which basically means that VC will execute the roll up jobs and remove data on it's own.

http://kb.vmware.com/kb/1003570

"Note: The SQL Server Agent is not available in Microsoft SQL Server 2005 Express. Historic data is managed by the VirtualCenter Server service."

rbrambley

Alastair,

Thanks for the info on the SQLAgent. I was aware that the SQLAgent was not included in SQL Express, but did not realize it's role in maintaining the db size. Maintenance via a scheduled task are a must for these SMB implementations staying below 4 GB!

tom

Regarding SQL Agent, there are various freeware tools which enable running rollups AND cleaning out old data.

I found some info on this topic but I don't have it with me as I write this comment, I will try to remember to find and post it here next week.

Alastair, it would be very nice if you would kindly post the SQL commands.

Dave Lawrence

Another point: The VC stats config screen shown above has logging levels of 1 in all categories. To maximize the size possibilities we should put the stats level to 4 in all categories and save the daily rollup for 5 years. With this "worst case" scenario and 5 hosts and 50 VM's we would get a DB size of 2.69GB. If use 5 hosts and increase the VM's to 100 the estimator says the DB size would be 5GB, larger than the 4GB allowed by SQL Express.

rbrambley

Andrew,

Thanks for the KB link.

So, the VC Service must already "know how to" rollup the historic data. I'm now curious to see SQL 2005 Express db sizes of customers after 1 yr, 2 yrs, etc for implementations with 40 to 50 VMs.

stu

Less than 5 hosts... would you even bother with VC? Going free ESXi and using free Veeam Monitor would be a pretty appealing cost effective option, especially in these financial times!

tom

Free Veeam Monitor saves only 24 hours\' data, it\'s better used in conjunction with VC, and it\'s not hard to make sure VC doesn\'t go over 4 GB, one just has to learn how to trim out old data.

rbrambley

Stu,

I would want VC for VMotion, DRS, deploy from templates, Cloning, and all the other good stuff - even in small ESX Clusters!

Phil Howard

For what it's worth I have a 4 host, 75VM cluster that has been running happily for a year on an SQL express DB. Most of my VM's are pretty low IO but I do have a pair of Exchange servers and a big file server in the environment. All of that said, my VCDB is only 1Gb. I have an OSQL batch file that runs nightly to handle my backup and clear the transaction log. It's a little pokey at times, but pretty stable. Even without the benefit of SQL agent you can take care of all the management tasks with an OSQL script and the task scheduler

Phil Howard

If anyone is curious here's how I do backups. Drop this line into a batch file and run at your leisure.

OSQL -E -D "VMware VirtualCenter" -n -Q "BACKUP DATABASE vcdb TO DISK = '\\backup\SQLBackups\VirtualCenter.bak' WITH INIT, SKIP"

Leo Raikhman

Rich,

just because it's a small implementation does not mean it's important. I use the full SQL 2005 because then I can at least create a database mirror (if the customer doesn't want a cluster) to preserve some of the more esoteric configurations like folders, permissions, annotations.

You can't do that with SQL Express.

rbrambley

Leo,

Good point, but it depends on the customers budget. You definitely need a backup of your SQL dbase no matter what version. By no means did I mean to come across as saying that small implementations were not as important!

Phil,

Thanks for the backup script

tom

Some suggestions about tools to use with SQL Express for smaller VM clusters:

http://communities.vmware.com/thread/183551

Purge old VC data, from VMware:

http://kb.vmware.com/selfservice/microsites/sea...

Gotchas to watch for:

http://kb.vmware.com/selfservice/microsites/sea...

http://communities.vmware.com/thread/119906

http://communities.vmware.com/thread/120390

HTH Tom

Why not VirtualCenter with SQL 2005 Express?

Related Posts

www.vmetc.com by Richard Brambley is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License.