IIS & MSSQL or Apache mySQL on Win Platform

[mariavernon]

Hello,

Can anyone give some guidance on what is the best Sugar platform to install on a Win Server, would you advise on using IIS & MSSQL or Apache mySQL or another?

I am looking for the most stable and the best performing.

Thanks in advance.

Maria

[kbrill]

An almost impossible question to answer. If you factor out money (meaning taking for granted you already own MSSQL) then go with IIS7/MSSQL.

If you don't own it but are still comfortable in it then it might be worth the money because my advice is always to go IIS7/MSSQL on Windows.

If you are thinking of going with the MSSQL express product or are not fluient in MSSQL then just go Apache/MySQL, you will be happier in the end.

[sugarexpert]

I find it very easy to answer the question, and my opinion is just the opposite. If you want a fast and reliable system, you cannot beat IIS and SQL Server. This is especially true when the person implementing is a novice. While I'm certain that an Apache and MySQL expert does not mind editing text files, the typical Windows user prefers to have nice pretty user interfaces with embedded help. And while there is a MySQL admin tool, it does not come anywhere close to the features, quality and ease-of-use of SQL Server Management Studio.

Addressing the money issue, SQL Server Express is free, and so is IIS, so there is no cost to go with IIS7/MSSQL.

[Angel]

FWIW, I disagree.

There are some important points that have been sidestepped/overlooked.

For one, use Apache. IIS works, but also presents issues. I had a client with a reasonably sized Sugar install on IIS with MSSQL and when presented with performance issues, Sugar directly told us that we should consider switching to Apache if other suggestions did not yield positive results.

Those other suggestions included installing WinCache and others that had already been tried but yielded mixed results. At the time, WinCache was still in Beta. Certainly not something I would feel comfortable recommending to a customer for a production system. It is version 1.1 now, so we could argue it is ready for production, but then again, there is a reason why people like to state that MS doesn't get things right until version 3.0 (of anything).

All this is an aside to the various MSSQL specific oddities we discovered. In fairness, I should also state that both the customer and I were wanting to tune the database first, but the topic did not evolve further than a discussion point when the performance matters were discussed with Sugar.

The other important point to note is that SQL Express is not engineered for large volumes of data or users. There is a hard limit of 8 GB on any database it serves out. If you are going to use MSSQL, buy a copy of Standard Edition or better.

Now, the point about MySQL. There are equally robust admin tools for MySQL as there are for MSSQL. I personally use SQLyog and have yet to come across something I needed to do in it that it didn't allow me to do, but for which there was an equivalent in Management Studio. There are certainly differences, but they tend to relate to more advanced features. If one is a novice user, the omission of advanced features should be irrelevant. At least I would think so. See my post on some of my favorite things about SQLyog here: http://cheleguanaco.blogspot.com/200...ke-sqlyog.html

Lastly, on the negative side, the one thing I don't like about the Windows platform as a whole is that if one is using 64 bit architecture, 64 bit versions of Apache and PHP are not easy to come by, which is annoying.

[mariavernon]

Good response, very helpful - thanks all.

Can you advise on the best PHP setup when running on WIN 2008 / IIS 7, should it be FastCGi or just PHP.

Really need some guidance on this point please?

Maria

[clint]

FWIW, I disagree.

There are some important points that have been sidestepped/overlooked.

For one, use Apache. IIS works, but also presents issues. I had a client with a reasonably sized Sugar install on IIS with MSSQL and when presented with performance issues, Sugar directly told us that we should consider switching to Apache if other suggestions did not yield positive results.

Those other suggestions included installing WinCache and others that had already been tried but yielded mixed results. At the time, WinCache was still in Beta. Certainly not something I would feel comfortable recommending to a customer for a production system. It is version 1.1 now, so we could argue it is ready for production, but then again, there is a reason why people like to state that MS doesn't get things right until version 3.0 (of anything).

When did you get that message of Apache over IIS on Windows from SugarCRM? We were recommending that a year plus ago, but no longer since FastCGI and Wincache have stabilized. We are actively recommending IIS + Wincache now.

Basically Sugar needs a PHP opcode cache to perform at its best. With an opcode cache plugin to PHP, Sugar (and any PHP app) is up to 10x faster than with vanilla PHP. Wincache is the opcode cache that Microsoft has built for the Windows platform. APC is the open source opcode cache, but it is only stable on Linux, not on Windows.

So to get the best performance of Sugar on Windows, I certainly recommend IIS+Wincache.

[Angel]

Clint,

The time frame you mention sounds about right. It was around this time last year when that call occurred.

In this specific case, the customer is still on IIS (can't remember for sure if they are using WinCache or not) , and in recent months worked with Sugar to further optimize some code that was causing performance problems in recent builds of Sugar (6.x). I believe those issues related to the queries for History.

That's not the first time that area has been examined, as we had discovered another problem in the 5.5 days that caused serious performance problems as well. Those are the problems that actually led to the call where WinCache was recommended.

I generally feel a bit apprehensive about MSSQL to be honest. I know it works and it is supported, but at the same time, it doesn't seem like it gets enough exposure/usage by the community at large as does MySQL. When issues are discovered on MSSQL, it doesn't seem like there is an equal depth of response/knowledge for those issues as there are for MySQL matters.

I've actually resorted to handing out a 'disclaimer' of sorts to clients that insist on using MSSQL because it almost seems inevitable that at some point they'll run into some odd issue that requires some very low level troubleshooting/patch and is specific to MSSQL.

[Angel]

I meant to mention an example of the depth issue...

I have a customer that is using MSSQL and PHP 5.3. The latter requires the use of the MS Driver for PHP. All joking aside, that driver is version 1.0.

I am willing to bet that only a very small percentage of implementations actually use that combination. Given I've run into problems with other clients using the FreeTDS drivers which were in wider use, I am certain something will come up that is specific to the MS driver. It is only a matter of time in my opinion.

[kbrill]

Good response, very helpful - thanks all.

Can you advise on the best PHP setup when running on WIN 2008 / IIS 7, should it be FastCGi or just PHP.

Really need some guidance on this point please?

Maria

My current stack that so far I have been very happy with. I am a Linux guy and would never run this for my production site, but none the less it is stable and fairly quick even given that I really don't know how to optimize a IIS system.

Any 32 bit Windows Server version (or just run IIS in 32 bit mode)
IIS7
Wincache 1.1 (I only see 32 bit versions of this, hence the above)
Microsoft MSSQL Driver for PHP version 2.0.1
Sql Server 2008
PHP 5.3.5
FastCGI

[kbrill]

I meant to mention an example of the depth issue...

I have a customer that is using MSSQL and PHP 5.3. The latter requires the use of the MS Driver for PHP. All joking aside, that driver is version 1.0.

No, its version 2.0.1, it hasnt been 1.0 for a while now.

Now, I am not a big fan of IIS/MSSQL for many reasons but the only one that matters is that if you know nothing about how to set up a server that SugarCRM will work on and you just run the installers.... Loading a WAMP stack will always be better than muddling through the IIS/MSSQL install.

If you know what you are doing, IIS/MSSQL can be installed and the result can be every bit as good as a Linux/Apache/MySQL setup and yes I do have to choke a bit to get that out. But the fact is that you have to know quite a bit. Installing MSSQL is a bit like taking an exam after an all night kegger. It asks questions that it knows you dont have the answer to and the help feature is a copy of War and Peace in techish.

I also find it difficult to understand how Windows is so widely used for web servers when web pages are usually made up of thousands of tiny files (like SugarCRM is) and NTFS is just awful at handling tiny files. Anyone who has unzipped a copy of SugarCRM in both Windows and Linux knows what I am talking about. But I guess thats what Wincache is for.