Need advice on which SQL Server version I need to use for live websites

As per Sponge Bob's suggestion, if there is flexibility in the choice of DB I'd opt for a free DB. My personal preference is Postgres.

However, to answer your specific questions :

1. A SQL Server backend for a Web site requires a processor license.
2. The licencing is done per socket, not per core. So, if you have a single quad-core processor, then you need a single-processor license.

Regards,

Liam

To the OP:

If you are putting this into a DataCentre check with them first. Most can offer machines with SQL server installed for a nominal monthly fee instead of forking out 4k + euro.

You can indeed use Work group edition and SQL express for hosted sites.

Paul

Mysql or Postgres tbh.

liamo wrote:

Having looked at the license quickly, I was surprised to not find anything forbidding use of SQL Express for live web sites. As such, Flamegrill is absolutely correct. You may use SQL Express for hosted sites.

You also have no limit on client connections.

It's really the hardware limits which are used to convince people to use the expensive versions:
1 Processor, 1 Gig Ram, max DB size is 4GB (per DB). You also don't get the SQL Agent, which limits your management of the server.
So for anything which is going to need to process any serious amount of work, you'll need to buy a proper version.

You really need to look at all aspects before making a decision on what version to buy - size of the databases, expected workload, projected increase in sizes/workload, and so on.

A CAL (client access licence) allows one machine to connect to the server.

There are two types of CAL - per device or per user.
One per-user CAL is used whenever any user connects to the server. One per-device is used whenever a device connects to the server.

You can also get a Per-Server licence. This basically allows any number of people to connect to the server, but each server must have its own per-server licence. They are also far more expensive.

Personally I think access licences are pure extortion. You pay serious money for the OS and the server software and then MS think it's OK to charge you for each person who connects to both the OS and any server software installed on it. You cannot use the software without the CALs.

This is why I'd always go for a good free alternative where possible.

L31mr0d wrote:

I'm thinking free might be our only option. On DELL its rounding out around 8.5K for the machine i'm looking at with a big chunk going on the SQL Server license.

How does the likes of MySQL fair against SQL Server 2005?

It really depends what you're doing with them. Very large systems (Google AdWords, for instance) have been built on MySQL, but it lacks some fancier features. Postgresql has more features, but is, I think, generally a bit slower than MySQL. MySQL also requires some minor attention in configuration; you'd be amazed how many people are running MySQL on giant machines and restricting it to 32MB memory or so...

If you have an existing application, porting may or may not be easy depending on what platform you're using, how much non-standard SQL you're using, and so on.

There are also licensing issues with MySQL. They don't effect most users, but if you want to sell a system using MySQL to clients to install on their own machines, there may be issues; both MySQL _and its client library_ are GPL. (The client library used to be LGPL, but they changed that to encourage people to buy licenses). Postgresql uses a more liberal license, I believe.

If you're a masochist, Ingres is free, too.

L31mr0d wrote:

How simple is it to migrate from MS SQL to MySQL? Can MySQL be run in compatibility mode with MS SQL?

There's no compatibility mode, no. Difficulty of migration really depends on what platform your application is using to an extent, and also on how many non-standard SQL statements and stored procedures and such you're using.

For an application using the JDBC or similar abstraction layer, with no stored procedures or weird SQL, migration should be very easy. With certain ORM libraries, migration could be particularly trivial.

For an application using, say, PHP's db-specific functions, with lots of stored procedures, migration could turn into a bit of a nightmare.

If you are considering migration, of course, it is very important that you learn the basics first. You'd be amazed how many people happily create large MySQL tables entirely without indexes, for instance.