Your thoughts on MSSQL

[davemateer@gmail.com]

Hi

We have a current system:

Linux / Apache / PHP4.x talking to Microsoft SQL 2000

Thinking about going to:

Windows 2003 / PHP4.x talking to the same Microsoft SQL 2000 box...


Any pain that you have gone through? We will have some relatively high
load, and complex queries going through the database driver.

All the best

Dave.

[Chung Leong]

Re: Your thoughts on MSSQL

davemateer@gmai l.com wrote:[color=blue]
> Hi
>
> We have a current system:
>
> Linux / Apache / PHP4.x talking to Microsoft SQL 2000
>
> Thinking about going to:
>
> Windows 2003 / PHP4.x talking to the same Microsoft SQL 2000 box...
>
>
> Any pain that you have gone through? We will have some relatively high
> load, and complex queries going through the database driver.[/color]

Here's my experience running Apache 2.0 and PHP 4 on Win2K Server:

* Our parallel testing server got completely hosed once. My former
superior had decided that it was okay to run PHP in suicidal mode (i.e.
as CGI). Now I'm in his position :-j

* Ran into a thread-safety bug in 4.3.6 using custom stream wrappers. I
believe it's fixed in 4.3.9 or 10.

* Discovered that the Apache 2 SAPI was leaking one byte for every byte
outputted. Didn't really notice it at first, since Apache restarts the
handler process occasionally. The server started to experience massive
slow-down though after I wrote a script for generating ISO images.
Things returned to normal when I swapped it the SAPI module from 4.3.9.

* Traced another server stability problem to a rogue XSLT script.
Because the web server is multithreaded rather than multiprocess, when
the XSLT engine crashes in one request handler, all other handlers die
too.


We use MSSQL as well and I haven't had any problem with the mssql
functions thus far--except for the 255-character varchar limitation.

[Dikkie Dik]

Re: Your thoughts on MSSQL

> Any pain that you have gone through? We will have some relatively high[color=blue]
> load, and complex queries going through the database driver.[/color]


If you know where the pain is, there must be ways to relieve it.
For complex queries, there are a few options:
- You could put them in stored procedures. This will cause the parsing
of the queries to be dropped at runtime (it was done at create time and
stored).
- If you are used to object orientation, you could implement some lazy
table wrappers for records that are part of many queries.

Best regards

[Steve]

Re: Your thoughts on MSSQL

On Tue, 04 Apr 2006 19:10:13 -0700, davemateer@gmai l.com wrote:
[color=blue]
> Hi
>
> We have a current system:
>
> Linux / Apache / PHP4.x talking to Microsoft SQL 2000
>
> Thinking about going to:
>
> Windows 2003 / PHP4.x talking to the same Microsoft SQL 2000 box...
>
>
> Any pain that you have gone through? We will have some relatively high
> load, and complex queries going through the database driver.
>
> All the best
>
> Dave.[/color]

Personally, I'd change the database ( postgres or look back at the
big boys going free: Ingres, DB2? ), rather than the platform. Increase in
performance, reliability, and plummeting TCO ( get the facts... right ).

Steve

[rlee0001]

Re: Your thoughts on MSSQL

Agreed 100%. Going from Linux to Windows seems counter-productive. I'd
have stayed with Linux but switched to PostgreSQL. Most users will find
PostgreSQL more capable and scalable compared to MSSQL.

-Robert

[Chung Leong]

Re: Your thoughts on MSSQL

Steve wrote:[color=blue]
> On Tue, 04 Apr 2006 19:10:13 -0700, davemateer@gmai l.com wrote:
> Personally, I'd change the database ( postgres or look back at the
> big boys going free: Ingres, DB2? ), rather than the platform. Increase in
> performance, reliability, and plummeting TCO ( get the facts... right ).
>
> Steve[/color]

That really depends on your perspective. Techies tend to think that a
lower TCO means a setup that doesn't require their attention. From a
manager's standpoint though, lowering TCO means getting rid of the
techies. In a countries with high labor cost, the numbers are usually
on the manager's side.

[davemateer@gmail.com]

Re: Your thoughts on MSSQL

Chung

Many thanks for the reply.. I'm actually thinking of using IIS6 as a
webserver. I'm interested why you stuck with Apache?

The varchar 255 limit... any workarounds you know of?

rlee0001 and Steve

Hmm, not sure I agree. One of the reasons I'm moving from Linux to a
Windows environment is the TCO. This is a sub 1000 person company with
only 1 linux webserver, and a 1 other IIS webserver. All other boxes
Windows based (including the SQL server). So the cost of having to
maintain expertise in Linux, and of disaster recovery is fairly high.

Dikkie Dik

Yeah we've got most of our app running through Stored Procs and Views..
although some is still on a 2 tier level. I've got some very complex
SP's which run.

[Chung Leong]

Re: Your thoughts on MSSQL

davemateer@gmai l.com wrote:[color=blue]
> Chung
>
> Many thanks for the reply.. I'm actually thinking of using IIS6 as a
> webserver. I'm interested why you stuck with Apache?[/color]

We use Win2K, so we only have IIS5. The decision to go with Apache was
made only a few months after the Code Red worm. There has never been
any issue so there was no reason to switch. Getting PHP to work on IIS
can also be tricky.
[color=blue]
> The varchar 255 limit... any workarounds you know of?[/color]

As text columns don't suffer from the limitation, you can do a
"CAST(colum n1 AS text) AS column1" to retrieve columns wider than 255.
Sort of annoying but not a big deal.

I have heard that it's possible to compile FreeTDS for Win32. Haven't
tried it though. You can also try going through ODBC.
[color=blue]
> Hmm, not sure I agree. One of the reasons I'm moving from Linux to a
> Windows environment is the TCO. This is a sub 1000 person company with
> only 1 linux webserver, and a 1 other IIS webserver. All other boxes
> Windows based (including the SQL server). So the cost of having to
> maintain expertise in Linux, and of disaster recovery is fairly high.[/color]

That's the same reason why we're sticking with Windows. Discussions
about migrating to Linux would end inevitably with the question "who
will back up the box?" By my estimate we've spent just over $50K in
software licensing fee on our servers. That's way cheaper than having a
Linux guy on staff.

[Jerry Stuckle]

Re: Your thoughts on MSSQL

davemateer@gmai l.com wrote:[color=blue]
> Chung
>
> Many thanks for the reply.. I'm actually thinking of using IIS6 as a
> webserver. I'm interested why you stuck with Apache?
>
> The varchar 255 limit... any workarounds you know of?
>
> rlee0001 and Steve
>
> Hmm, not sure I agree. One of the reasons I'm moving from Linux to a
> Windows environment is the TCO. This is a sub 1000 person company with
> only 1 linux webserver, and a 1 other IIS webserver. All other boxes
> Windows based (including the SQL server). So the cost of having to
> maintain expertise in Linux, and of disaster recovery is fairly high.
>
> Dikkie Dik
>
> Yeah we've got most of our app running through Stored Procs and Views..
> although some is still on a 2 tier level. I've got some very complex
> SP's which run.
>[/color]

I use Linux for three reasons:

1. More reliable (fewer crashes and fewer worms and viruses)
2. Faster with the same hardware
3. No licensing fees for most of the software.

As for Chung's comments (later) about $50K for licensing software being cheaper
than hiring a Linux person. That's true if the only thing he did was monitor
the web server. But it's not a 40-hour a week job. Linux specific stuff might
be 8-10 hours per month. The rest of the time that person could be doing other
work.

And I'd love to make $50K/yr. for a few hours a month of work.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Scott]

Re: Your thoughts on MSSQL

On Tue, 2006-04-04 at 19:10 -0700, davemateer@gmai l.com wrote:[color=blue]
> Hi
>
> We have a current system:
>
> Linux / Apache / PHP4.x talking to Microsoft SQL 2000
>
> Thinking about going to:
>
> Windows 2003 / PHP4.x talking to the same Microsoft SQL 2000 box...
>
>
> Any pain that you have gone through? We will have some relatively high
> load, and complex queries going through the database driver.
>
> All the best
>
> Dave.
>[/color]

I'm sure everyone will agree that Linux is a better platform for PHP.
However, I have used PHP on windows servers running IIS for about 5
years now, so I know it can be done with reasonable performance.

As for MSSQL, I haven't used it much with PHP. However, the last
dedicated database server I used had MSSQL and MySQL running on it.
MySQL had much greater usage, but MSSQL usually consumed the most
resources. MSSQL was used by some of our 3rd party ASP apps, so I really
couldn't say whether or not the crappy performance by MSSQL was due to
poor database usage or not.

Scott

[rlee0001]

Re: Your thoughts on MSSQL

> By my estimate we've spent just over $50K in[color=blue]
> software licensing fee on our servers. That's way cheaper than having a
> Linux guy on staff.[/color]

Only in the windows world is a server admin a full time position. In
the linux world the same position is a PRN position, total cost per
year: less than $4,000.00. You get one of your PHP programmers or DBAs
to maintain the servers when it is needed instead of actually trying to
find a PRN system admin. It only takes maybe 120 hours a year to swap
out harddrives, install memory and upgrade motherboards.

Linux is like an Airbus. It pretty much flies itself.

-Robert

[Chung Leong]

Re: Your thoughts on MSSQL

rlee0001 wrote:[color=blue]
> Only in the windows world is a server admin a full time position. In
> the linux world the same position is a PRN position, total cost per
> year: less than $4,000.00. You get one of your PHP programmers or DBAs
> to maintain the servers when it is needed instead of actually trying to
> find a PRN system admin. It only takes maybe 120 hours a year to swap
> out harddrives, install memory and upgrade motherboards.[/color]

I'm not advocating one platform over another. I'm just trying to
explain the method to what some of you see as madness.

Administration of a Windows server isn't a full-time position either.
That's really the whole point. In an organization with a large Windows
deployment, you can easily assign this part-time job to one of your
MCSEs. In an ad hoc operation maybe you can delegate the task to a
programmer, but in most organizations employees have well-defined areas
of responsibilitie s. You can't just let someone handle something
because he can.
[color=blue]
> Linux is like an Airbus. It pretty much flies itself.[/color]

But would you fly on an Airbus piloted by a flight attendant? I don't
think so.

[rlee0001]

Re: Your thoughts on MSSQL

Oh ok, so what you are saying is that TCO for Windows is on par with
Linux save for Windows' $50,000 licensing fees. Then you conclude that
this makes Windows a great deal. Interesting.

-Robert

[onedbguru@firstdbasource.com]

Re: Your thoughts on MSSQL

What I find most interesting about TCO is the fact that most businesses
neglect to include employee idle-time during system unavailabilty due
to constant security patching or system crashing or really slow
performance due to undersized apps and systems. Licensing is
definately one of the most costly pieces of TCO. Hardware is "cheap",
but when you have to replace it every 2-3 years - it becomes nothing
more than a tax write-off. I know of servers (not Intel) that have
been in production 24x7x365 for more than 10 years. Companies also
raise the TCO by spending tons of $$$ to code a project and then throw
it out 2 years later because it is not "latest technology". I know of
certain very-high-transaction-rate systems that execute 10 to 20 times
faster using and old green screen system. There are times where
green-screen can significantly reduce TCO.

But, too many CIO's only buy the latest "buzz-word".

[Chung Leong]

Re: Your thoughts on MSSQL

rlee0001 wrote:[color=blue]
> Oh ok, so what you are saying is that TCO for Windows is on par with
> Linux save for Windows' $50,000 licensing fees. Then you conclude that
> this makes Windows a great deal. Interesting.[/color]

You can't really arrive at the T in TCO without counting labor cost.
Obviously Linux would win everytime if you only count cost of
acquisition. As Dave said, the cost of maintaining Linux expertise
can't be justified when you have one or two Linux machines in a sea of
Windows boxes.

The bulk of the money mentioned actually went to buying a hefty
firewall and an IDS. MSSQL is also expensive but the self-optimization
and data-recovery features are worth the money.