VMware Cloud Community
cryptonym
Enthusiast
Enthusiast

Beware of SQL Express & undocumented loss of some important features in Virtual Center, confirmed by VMware

Just a quick note to warn people away from "upgrading" to SQL Express with VirtualCenter 2.5. If you do, you will probably be sorry. Due to Micro$oft removing the SQLAgent in this "lite" version, there is no automation. This means that there is no way for VirtualCenter to do things like roll up the daily data into weekly and monthly views, or perform scheduled truncations, cleanup or backups.

They will be noting (some of) this in some future release of the documentation, but it's not in there today.

Please see http://communities.vmware.com/message/832082 for details on my problems with missing performance data. In that thread you will find reference to a couple service tickets that you can reference in your communications with them, if you bump into this or related issues.

In the mean time, please request that they make a Linux version of Virtual Center available. Also, please ask that they support MySQL as a database. They have an web form off of the "contact us" page to do requests.

Sorry, but this problem deserved it's own top level topic I think.

Warren

Reply
0 Kudos
23 Replies
RParker
Immortal
Immortal

First, they strongly discourage using SQL Express anyway. So you didn't read the documentation.

Second, they specifically say install on a separate SQL Instance, either SQL 2000 or SQL 2005. They ALSO state and give you a stern warning that installing a database in the same machine as your VC is to be for TESTING purposes only, and they strongly advise against..

Linux would be great, however, there are several hundred thousand customers using this same VC/Windows setup, and we are all just fine.. maybe perhaps we follow directions and the instructions?

So you got a snafu, it happens. But don't blame VM Ware. Also if you READ the documentation for SQL Express it CLEARLY states that the Express version of 2005 is for LIGHT SQL and testing also, so not only did you skip past the part in the VC setup, and not only did you ignore the warnings, but you also didn't read the same for SQL Express...

VM Ware can only help people when they at least follow the instructions.

And you don't understand how relational databases work if you expect them to support MySQL, that's even worse than SQL Express.

Reply
0 Kudos
wila
Immortal
Immortal

Linux would be great, however, there are several hundred thousand customers using this same VC/Windows setup, and we are all just fine.. maybe perhaps we follow directions and the instructions?

Comm'n this is like "I have been using Windows for umpteen years and have never been bitten by a virus, you must be doing something wrong"

And you don't understand how relational databases work if you expect them to support MySQL, that's even worse than SQL Express.

Excuse me? Why are you repeating old facts ? MySQL 5.x has all the required relational database features and has been released for years now. What is the feature that you think is so great in MSSQL that is missing in MySQL? Imho, there's NO real difference anymore between MySQL and MSSQL, the difference is in the tools that you get with them. Even there, the mysql tools are getting good.

But let me rephrase his question, why don't we get a VC with PostgreSQL support? It sure is a better database as MSSQL express...

| Author of Vimalin. The virtual machine Backup app for VMware Fusion, VMware Workstation and Player |
| More info at vimalin.com | Twitter @wilva
cryptonym
Enthusiast
Enthusiast

> First, they strongly discourage using SQL Express anyway. So you didn't read the documentation

Discourage based on size of deployment. Our setup is smallish. And, I do read manuals.

> Second, they specifically say install on a separate SQL Instance, either SQL 2000 or SQL 2005. They ALSO state and give you a stern warning that installing a database in the same machine as your VC is to be for TESTING purposes only, and they strongly advise against..

Well, it was on it's own instance in SQL Express. Once it goes outside of my control, which by requiring SQL2005 Server it will, it goes to the DBA's to decide how to implement it, and on what instance. All I can do is hand them the docs and say "here ya go".

> Linux would be great, however, there are several hundred thousand customers using this same VC/Windows setup, and we are all just fine.. maybe perhaps we follow directions and the instructions?

I was working just fine with the MSDE setup too. I doubth they have "several hundred thousand customers" on the VC2.5 (SQLExpress) yet. As to directions, my setup is small, and it is in the test lab. For a whole years worth of collections the size was projected to be < 1.3GB. That seems pretty lightweight to me. I've worked with databases where that was a daily rate, not annual. Anyone running on SQLExpress is not "just fine", but are crippled, even if they are only monitoring 3 VMs on 2 Hosts (well within the recommended sizing). So read all you want, this fact isn't in the docs.

> So you got a snafu, it happens. But don't blame VM Ware.

VMware decided to include SQLExpress with their product and failed to disclose the liability it imposed. They acknowledged this fact, and thanked me for providing the duplicatable scenario's that helped them quickly identify the issue in their labs. They said that their current documentation did not state that functionality would be lost if SQLExpress is used, but that future documentation would do so. That disclosure would have provided a concrete reason to pursue other courses of action.

> And you don't understand how relational databases work if you expect them to support MySQL, that's even worse than SQL Express

No, that would be you not understanding. MySQL is a very robust database now, very capable, and deployed widely. It has had most of the capabilities that MSSQL has for quite a while now. Yes, the MS tools are nicer, but there has even been headway on that front. And, if the actual data application is working right, there should be very little need to use any database tools.

> VM Ware can only help people when they at least follow the instructions

VMware techs did help, and were quite efficient and dedicated to understanding the problem, as they always are. You on the other hand need to take a chill pill or something -- or do you just work for Microsoft, in which case your opinionated behavior would seem pretty normal. I see you're not a VMware employee at least...lucky for all of us I guess, with that attitude of yours.

Reply
0 Kudos
YoMarK
Contributor
Contributor

Great info cryptonym.

I thought the only limitation(for running a single "simple" database like virtualcenter) of SQL Express was the database size(this can be a real problem on larger sites).

Will keep this in mind.

And RParker, you really could use some tea to cool down.

Besides that, my opinion on seperating database and virtualcenter is al little different.

I think it's better to keep it on the same machine, especially when databaseserver and/or virtualcenter are running on your virtual infrastructure.

(Multiple points of failure)

Reply
0 Kudos
epping
Expert
Expert

Hi

Please can you point out where you have got your information from. I have read the installation guide, and fail to see many of you objections.

SQl Express should not be used for above 50 VMs, but this is fine for small shops

I can see nothing stating that the DB should not be installed on the same server as VC (i think this is better in many cases for DR)

http://www.vmware.com/pdf/vi3_35/esx_3/r35/vi3_35_25_installation_guide.pdf

Reply
0 Kudos
akmolloy
Enthusiast
Enthusiast

I agree with cryptonym. From the manual link above it says:

"NOTE Microsoft SQL Server 2005 Express is intended to be used for small deployments

of up to 5 hosts and 50 virtual machines."

The old manual did say that MSDE was only for evaluation. VMware now clearly supports people to use Express for small deployments.

I could be wrong, bu my hunch is that the lack of features including the lack of rollup of daily data caught VMWare by surprise. I'm also willing to bet they underestimated the amount of people who were using MSDE behind VC, and will now be crippled by this upgrade.

For my smallish shop, we don't even have an official DBA, nor do we have a SQL installation anywhere. We do have someone who maintains a production MySQL server, so having MySQL would be awesome for me.

I'm heading over now to make a feature request, and I'd advise others to do the same if MySQL/Linux is important to you:

https://www.vmware.com/contact/contactus.html?department=feature-request

-Tony

Reply
0 Kudos
pkadmin1
Contributor
Contributor

I'm w/ you on this, I did request I hope we get enough so it can atleast be a bullet point in a meeting

Reply
0 Kudos
dmanconi
Enthusiast
Enthusiast

Hi

I have used this program at http://www.sqldbatips.com/showarticle.asp?ID=29 to help with SQL2005 Express and backups, maintenance etc. This should help with any tasks that need to run.....

Cheers

David

Reply
0 Kudos
akmolloy
Enthusiast
Enthusiast

I just remembered something. Before I upgraded my VC to 2.5, I decided to do the upgrade to SQL 2005 Express first from MSDE. I let this sit for a week to be sure that things were OK in the new database, and then did the upgrade to 2.5.

During this time with VC 2.02 talking to Express 2005, I don't remember seeing any of the rollup problems that I see now. I can't say that I remember specifically looking at performance data for more than a day, but that's something we do fairly often here, and there were no problems reported during this time.

Is anyone able to verify this? If so, wouldn't that say that the problem wasn't actually with Express, but with VC itself?

Reply
0 Kudos
mreferre
Champion
Champion

> And RParker, you really could use some tea to cool down.

I have to agree. As long as people do not offend others with their posts they do deserve respect even though they bring up very stupid and basic questions (which was clearly NOT EVEN the case with Warren's post - I think he had a very good point).

Without getting into the technical matter I would also want to add that I don't agree that we should keep whatever VMware says / suggest as a "given". "We'd like you to use the enterprise version Vs the the small one, please use a dedicated server for this and that etc etc etc". Sure it is easy for them to force users to use the most advanced platforms to support their add-on's ....... but let me make the point that if we were to strictly follow what the ISV's were suggesting in the first place we were to run all physical servers and not a single vm (or did you come across many ISV's suggesting to use a vm rather than a dedicated phsyical server?).

I am not going to suggest customers to use unsupported configurations ...... but I do suggest customers to challenge VMware when they want them NOT to use something without a specific reasons (obviously everybody knows that SQL Enteprise is better than SQL Experess...... but why shouldn't I use SQL Express for small setups?). This is clearly just an example.

Massimo.

P.S. Thanks Warren for your post.

Massimo Re Ferre' VMware vCloud Architect twitter.com/mreferre www.it20.info
Reply
0 Kudos
abalch
Contributor
Contributor

So, what is the bottom line for small implementations such as mine (2-3 hosts, <30 VM's), use SQL Express or not? Are you running VC/SQL Express as a VM (and a single VM)?

Reply
0 Kudos
mreferre
Champion
Champion

Well if you ask me I would go with Express all the day (for such a deployment scale).

I don't want to mislead you or any other: I am not up to date on the very current "VMware paranoid support statement of the day" but I'll take what Tony took from the VMware document and pasted already above:

"NOTE Microsoft SQL Server 2005 Express is intended to be used for small deployments

of up to 5 hosts and 50 virtual machines."

Well you seem to fall into that range so save the money of SQL for better use.

My opinion.

Massimo.

Massimo Re Ferre' VMware vCloud Architect twitter.com/mreferre www.it20.info
Reply
0 Kudos
akmolloy
Enthusiast
Enthusiast

I have 3 hosts and am currently at 25 VMs. My VC is a VM, and it's running SQL Express on the VM as well.

This has worked out so far just fine (8 months in production), but if we add hosts, I will most likely move to an external SQL Enterprise DB. We are bringing a SQL server up for a Sharepoint implementation, so I'll be moving to that. Not because of performance or anything, I'd just have all my DBs managed from one place.

-Tony

Reply
0 Kudos
cryptonym
Enthusiast
Enthusiast

abalch,

If performance history is not important to you, then SQLExpress may work for you. I haven't seen any other feature broken by SQLExpress.

If you need to be able to look back more than 24 hours, then you need to go with the SQL2005 Standard, Enterprise, or an Oracle database. In my case, I have to be able to profile utilization over about 72 hour periods, so in actual use here, SQLExpress just didn't cut it.

Warren

Reply
0 Kudos
cryptonym
Enthusiast
Enthusiast

akmolloy,

With you saying "8 months in production", you are definitely on a different version of VC than I am. I have deployed Virtual Center 2.5, which introduced this problem during the forced upgrade to SQLExpress. VC2.5 just shipped in mid-december. VC2.0 probably used different procedures to perform the "roll up" of the performance data. Only VMware can explain this. Anyone who does experience a problem, should file a ticket with vmware support, if nothing else, then to make vmware look deeper into solving the problem. Previously I was on VC 2.0 on MSDE with no issues at all.

My problem is definitely not size related, it is a design problem within Virtual Center, or flat out a bug.

Reply
0 Kudos
akmolloy
Enthusiast
Enthusiast

I was running VC 2.02 on MSDE before I upgraded to Express. With that version, I had all the performance history I needed for day/week/month/year.

2 weeks ago I upgraded to VC 2.5 and SQL Express. I started having the CPU spike problems, so 1 week ago I reinstalled VC and pointed to a blank Express Database. As of today, I'm currently able to get performance history for the past week, up to the point of where I installed the new DB. Do you think I will lose the ability to keep history for more than a week?

I'm attaching screenshots of performance history for real time and the past week. It looks to me as though I will be able to keep performance history as I did with MSDE. I guess I'll know more as time goes on Smiley Happy

-Tony

Reply
0 Kudos
cryptonym
Enthusiast
Enthusiast

I've seen similar reports here, that for fresh installs the behavior is different than on an upgrade. More and more, that makes me think that this is a installer bug, not strictly a limit of SQLExpress. Unfortunately, recreating the security of my systems is no small undertaking. I'd suspect your system will keep on going, since at least the first roll up is running successfully for you. Once week 1 rolls to week 2, you should see data in the monthly view I believe. Keep us posted please. I may reopen the ticket and point them back to this forum again.

Thanks,

Warren

Reply
0 Kudos
zemotard
Hot Shot
Hot Shot

So for you, with a fresh sql express database installation, the problem with the historical data disappears ?

No problem to keep data since few weeks / months ?

Best Regards If this information is useful for you, please consider awarding points for "Correct" or "Helpful".
Reply
0 Kudos
akmolloy
Enthusiast
Enthusiast

As far as I can tell, I will have historical data as I did with MSDE.

Another thing to keep in mind:

I upgraded from MSDE to SQL Express before I upgraded to 2.5. I ran VC 2.02 against SQL Express for almost a month, and had no problems with "roll-ups" during that time. I could see historical data back to when I first installed 8 or so months ago. It wasn't until I installed 2.5 that I had problems with the database.

-Tony

Reply
0 Kudos