Alternative to full-text indexes when using MSDE

Basically I'm writing small ISAPI based applications which use MSDE as the
backend database which require a full-text search option which is
unavailable in MSDE. What alternative solutions are out there which are free
or low cost?

Currently in order to use full-text search on an Internet based application
I'd have to buy a CPU license for around £3,000 which makes the application
completely unviable. Are there any plans to include full-text searching in
SQL Server Express 2005, or offer it as a low cost option?

Currently the only alternative that I'm aware of is to completely abandom
Microsoft products and move to a linux/apache/mysql based solution.

Ian.

"Ian" <Ixpah@newsgroup.nospam> wrote in message
news:Ouezpkv0EHA.1400@TK2MSFTNGP11.phx.gbl...
> Basically I'm writing small ISAPI based applications which use MSDE as the
> backend database which require a full-text search option which is
> unavailable in MSDE. What alternative solutions are out there which are
> free or low cost?

Depends on your database design and your app requirements. If you can store
your text in the file system, you can use Index Services which are built
into Windows, or 3rd party products like dtSearch. You could choose either
to maintain redundant copies of the text in both your database fields and in
external files, or you could simply maintain pointers (e.g. a database field
which contains a filename where the text is stored, and/or a
header/attribute in each file which identifies a database record associated
with the file).

> Currently in order to use full-text search on an Internet based
> application I'd have to buy a CPU license for around £3,000 which makes
> the application

This is true as long as you mean "public web site" when you say "Internet
based." Merely running a small extranet via the Internet or having a small
number of remote intranet users can be covered by CALs. If you are
considering MSDE at all, then I assume you expect to have a small number of
users. MSDE contains a performance governor which restricts the query
execution engine to 5 concurrent batches which might be suitable for up to
20-25 concurrent users in a typical app. In named-user scenarios, CALs don't
become more expensive than CPU licensing until approximately 20-25 users.

By the way, I'm not sure how UK pricing works, but if I take the US price
and convert to pounds at today's exchange rate, I get £2,520. That's not a
huge difference, but there's obviously something about your project budget
that I don't understand. On this side of the ocean, ISAPI programmers don't
work cheap. The cost of a SQL Server CPU license is roughly equivalent to 1
w's worth of work from a developer with solid ISAPI experience, in any
major US metropolitan area. It has been a few years since I last managed an
ISAPI development project, but if I had to do it again, I'd save money on
the solution architecture (such as using a .NET http handler or http module
rather than ISAPI) instead of trying to save on the database.

> completely unviable. Are there any plans to include full-text searching in
> SQL Server Express 2005, or offer it as a low cost option?

Microsoft has stated that SQL SQL Express 2005 will NOT support full-text
search.

> Currently the only alternative that I'm aware of is to completely abandom
> Microsoft products and move to a linux/apache/mysql based solution.

That's a bitter and spiteful reaction. MySQL runs on Windows and can be
queried from your ISAPI app via ODBC or OLE DB. Not being able to use MSDE
or SQL Server is not (by itself) a good reason to abandon Windows and IIS.

There are also any number of low-cost database alternatives supporting
full-text search on the Win32 platform such as VistaDB. They will typically
fall short of SQL Server in other areas, but if you are considering MySQL,
your app probably doesn't need much capability in other areas. If it does,
PostgreSQL with OpenFTS is free, open-source, and cross-platform like MySQL,
but PostgreSQL is far more functional and mature.

Since you don't seem concerned about learning curve from switching
development environments, Rubicon is a low-cost product which provides
full-text search on Win32 for common Delphi-oriented databases including
Firebird (the free open-source version of InterBase) as well as many other
databases via BDE (Borland Database Engine), ODBC, and OLE DB. Even though
Rubicon is cheap and can be used with free/cheap databases, you will need
Delphi. Unfortunately, Delphi is not cheap, but you might consider this
approach if you already own it (or an older version which can be upgraded
cheap).

Oddly enough I am concerned with the learning curve of switching to a
different development environment which is why am using ISAPI/MSDE in the
first place because C++ and SQL Server are what I'm familiar with. My
current application will be a public website so a CPU license would be
required, however I believe I can support around 100 concurrent users using
MSDE so a CPU license would only become necessary if a site became really
busy. The cost is an issue because I'd like to be able to sell my "product"
to numerous end users and I'd be competing with $500 php/MySQL solutions.

Maybe it comes across as a bitter and spiteful rejection of Microsoft
products, but in all honesty if I switched to MySQL; I may as well ditch
Windows at the same time in order to compete on a more level playing field.
Another problem is that hosting companies tend to charge considerable more
for Windows hosting plus if they do offer SQL Server the database size is so
limiting it's unusable for all but a noddy database. I've never seem them
offer full-text searching so I assume this isn't even an option.

Ian.

"Ronny Ong" <ronnyong@killspam-bigfoot.com> wrote in message
news:O0b$EQa1EHA.3000@TK2MSFTNGP15.phx.gbl...
> "Ian" <Ixpah@newsgroup.nospam> wrote in message
> news:Ouezpkv0EHA.1400@TK2MSFTNGP11.phx.gbl... 
>
> Depends on your database design and your app requirements. If you can
> store your text in the file system, you can use Index Services which are
> built into Windows, or 3rd party products like dtSearch. You could choose
> either to maintain redundant copies of the text in both your database
> fields and in external files, or you could simply maintain pointers (e.g.
> a database field which contains a filename where the text is stored,
> and/or a header/attribute in each file which identifies a database record
> associated with the file).
> 
>
> This is true as long as you mean "public web site" when you say "Internet
> based." Merely running a small extranet via the Internet or having a small
> number of remote intranet users can be covered by CALs. If you are
> considering MSDE at all, then I assume you expect to have a small number
> of users. MSDE contains a performance governor which restricts the query
> execution engine to 5 concurrent batches which might be suitable for up to
> 20-25 concurrent users in a typical app. In named-user scenarios, CALs
> don't become more expensive than CPU licensing until approximately 20-25
> users.
>
> By the way, I'm not sure how UK pricing works, but if I take the US price
> and convert to pounds at today's exchange rate, I get £2,520. That's not a
> huge difference, but there's obviously something about your project budget
> that I don't understand. On this side of the ocean, ISAPI programmers
> don't work cheap. The cost of a SQL Server CPU license is roughly
> equivalent to 1 w's worth of work from a developer with solid ISAPI
> experience, in any major US metropolitan area. It has been a few years
> since I last managed an ISAPI development project, but if I had to do it
> again, I'd save money on the solution architecture (such as using a .NET
> http handler or http module rather than ISAPI) instead of trying to save
> on the database.
> 
>
> Microsoft has stated that SQL SQL Express 2005 will NOT support full-text
> search.
> 
>
> That's a bitter and spiteful reaction. MySQL runs on Windows and can be
> queried from your ISAPI app via ODBC or OLE DB. Not being able to use MSDE
> or SQL Server is not (by itself) a good reason to abandon Windows and IIS.
>
> There are also any number of low-cost database alternatives supporting
> full-text search on the Win32 platform such as VistaDB. They will
> typically fall short of SQL Server in other areas, but if you are
> considering MySQL, your app probably doesn't need much capability in other
> areas. If it does, PostgreSQL with OpenFTS is free, open-source, and
> cross-platform like MySQL, but PostgreSQL is far more functional and
> mature.
>
> Since you don't seem concerned about learning curve from switching
> development environments, Rubicon is a low-cost product which provides
> full-text search on Win32 for common Delphi-oriented databases including
> Firebird (the free open-source version of InterBase) as well as many other
> databases via BDE (Borland Database Engine), ODBC, and OLE DB. Even though
> Rubicon is cheap and can be used with free/cheap databases, you will need
> Delphi. Unfortunately, Delphi is not cheap, but you might consider this
> approach if you already own it (or an older version which can be upgraded
> cheap).
>
>
>

I doubt that I could find anywhere in the UK selling SQL Server 2000 1 CPU
License for £2,520. The way pricing seems to happen in the UK is that the
exchange rate is always fixed at $1=£1 regardless of the real rate.

I did have a look at amazon.co.uk and they seem to be selling it for just
over £4k a rather substantial difference I think you'd agree? It was hard to
tell exactly what they were selling as it wasn't described very well.

Ian.