Hi, Im from, and here we be having a discussion...

Who is the best?
Interbase or MS SQL...the serious problem is that: A university in my city
is trade from Interbase and Linux for Win2K with MS SQL, and has discussions
every time about this.

If anybody had information about these theme, please send to me.

Thx

Ricardo Fava

Why would they move from Linux to Windows?  What would they gain?
If they want to waste money, have them send some to me!

Regaards,
Aage J.

Maybe this link can help you a bit :)
http://www.cvalde.com/document/comparison_ib6_mssql7.htm

Regards
   Fikret

news:3d18abed_2@dnews...

That sound pretty foolish to me,
do they realize how much that is going to cost?
A single MS SQL CAL is over 160 US dollars, not to mention the cost the MS
SQL server.

A better move might to to migrate the ib database to Firebird 1.0.

MS SQL server has more polish and can do several things IB/FB such as temp
tables and the ability to query external databases on the same server.
All of this can be worked around though.

Tony Caduto

news:3d18abed_2@dnews...

We have recently "ported" our IB 5.6 database to MS SQL Server 2000.  Here
is my analysis:

1. Store procedure/trigger language:  you will end up writing 10-100 lines
of code in MSSQL for every line of IB code.  IB has a very clean, concise
implementation of exceptions, generators, cursors, before/after triggers,
etc.  MSSQL is quite bulky from the programming side.  However, if you don't
utilize stored procs and triggers, this is a non-issue.

2. Exception handling/flow of control:  IB is similar to the Delphi model of
handling runtime exceptions (i.e. skip from the point of execution to an
exception handler) whereas MSSQL keeps right on executing each line of code.
It is up to you to check for errors when you suspect they may have occurred.
Additionally, exception handling in MSSQL triggers is problematic (i.e.
bouncing back and forth between stored procs and triggers).  TAKE NOTE:
MSSQL implementation of triggers is MUCH different than IB's.  You can't do
a simple "port" from one to the other.

3. Performance: MSSQL 2000 has a MUCH better optimizer than IB.  Look at my
previous posts in this newsgroup on this issue.  With InterBase, you have to
be careful not to confuse the optimizer.  MSSQL takes advantage of multiple
processors whereas IB (SS on Windows) does not.

4. Admin tools: all the tools for IB are third party/flaky/shaky/buggy junk.
There is no suite of "built-in" admin tools to let you take care of your
database.  I believe that this is because of IB's original claim to be a
self-administering database.  In the enterprise arena, however, you need
much more/better control over your database with native tools.

5. stability: IB can be corrupted easily with no way to recover.  The
validation/repair tools don't work and you run a high risk of damaged/loss
of data if your database server environment is anything less than ideal
(i.e. power supplies, reliable hardware, network, etc.).  MSSQL has
transaction logs which can be rolled up to recover from corruption; IB does
not have this.

6. Awareness: no one (outside this newsgroup) has ever heard of IB.
Everyone has heard of MSSQL.  This is an intentionally hyperbolic statement
that is very close to the truth.

Just my thoughts, FWIW.

Paul J. Mills

I just switched jobs from a MS SQL Server environment to an IB shop, and I'd
like to address a couple of issues...

1.  While writing stored procedures/triggers, I often run into things in IB
that I wish I could do.  MS SQL's "bulky" (how 'bout "rich"?) programming
language has many nice features I sorely miss in IB (SELECT CASE for
example).  However, I do like IB's capability for multiple triggers much
better.

4.  If you look at ALL of the tools available for IB, I might agree with
you.  But IBExpert blows away MS's Enterprise Manager!  And if you do find a
bug?  Just post it on the author's newsgroup and it'll be fixed the next
day!  You'll never get that from MS.

--Just my humble opinion.

David Cornelius
http://CorneliusConcepts.com
http://IBCollection.com

"You never leave a recession on the same technology that you entered it."
--Gordon Moore, Intel.

"Paul J. Mills" <pjmi...@bgint.com> wrote in message
news:3d19e516$1_2@dnews...
We have recently "ported" our IB 5.6 database to MS SQL Server 2000.  Here
is my analysis:

1. Store procedure/trigger language:  you will end up writing 10-100 lines
of code in MSSQL for every line of IB code.  IB has a very clean, concise
implementation of exceptions, generators, cursors, before/after triggers,
etc.  MSSQL is quite bulky from the programming side.  However, if you don't
utilize stored procs and triggers, this is a non-issue.

2. Exception handling/flow of control:  IB is similar to the Delphi model of
handling runtime exceptions (i.e. skip from the point of execution to an
exception handler) whereas MSSQL keeps right on executing each line of code.
It is up to you to check for errors when you suspect they may have occurred.
Additionally, exception handling in MSSQL triggers is problematic (i.e.
bouncing back and forth between stored procs and triggers).  TAKE NOTE:
MSSQL implementation of triggers is MUCH different than IB's.  You can't do
a simple "port" from one to the other.

3. Performance: MSSQL 2000 has a MUCH better optimizer than IB.  Look at my
previous posts in this newsgroup on this issue.  With InterBase, you have to
be careful not to confuse the optimizer.  MSSQL takes advantage of multiple
processors whereas IB (SS on Windows) does not.

4. Admin tools: all the tools for IB are third party/flaky/shaky/buggy junk.
There is no suite of "built-in" admin tools to let you take care of your
database.  I believe that this is because of IB's original claim to be a
self-administering database.  In the enterprise arena, however, you need
much more/better control over your database with native tools.

5. stability: IB can be corrupted easily with no way to recover.  The
validation/repair tools don't work and you run a high risk of damaged/loss
of data if your database server environment is anything less than ideal
(i.e. power supplies, reliable hardware, network, etc.).  MSSQL has
transaction logs which can be rolled up to recover from corruption; IB does
not have this.

6. Awareness: no one (outside this newsgroup) has ever heard of IB.
Everyone has heard of MSSQL.  This is an intentionally hyperbolic statement
that is very close to the truth.

Just my thoughts, FWIW.

Paul J. Mills

Since locking issues are different between MSSQL and IB, I'd like to
know whether you had to redo much in your apps to adjust for this.

Regards,
Aage J.

REALLY good thread -- a very common question that always deserved more
thoughtful answers than it usually gets.  Usually a thread of "How is
product x vs. y" differs wildly based on whether it's in x's or y's
newsgroup; this is a pretty objective thread.

Another few differences:

1.  External documentation and publications 200 to 0 in favor of MS.

2.  IB tends to be more ANSI compliant than MS T-SQL.  Easier ports.

3.  MS has the MSDE (no tools, performance degrades after 5 users and tables
to only 2G) for free applicaiton deployments.

4.  MS supports multi-cpus, but charges a license for each.  NOT SURE HOW
BORLAND WILL HANDLE THIS FOR THE SMP 7.0.

5.  Lot of things a bit "quirky" with IB: Plans, that SET TERM thing, naming
of indexes, connection strings, that delete for x in y issue, etc.  Nothing
may be considered a bug and nothing a show stopper, but...

My own recap on important issues to us:

1.  IB is multi-platform.

2.  MS supports transaction logs.

3.  MS query analyzer is incredible.

4.  MS has the MSDE.  (Summary an opensource thread, you could have the
support of the full IB and deploy with FB, but aren't guaranteed that
things'll be exact.)

we have some experience with customers, who worked with both
databases.

One of the points:

one had simple commands started 1000 times per hour and he did not use
parameters, he recreated the whole sql statement. These were Select, insert,
update and delete statements for only one table, which had around 5000
records. the total space used for data was less than one mb.

when he started this application on ms sql server, it took less than one day
to
ms sql to stop work because of no more virtual memory. The machine had over
1GB physical RAM.

The same done with the same application on ib used only a few mb of ram
without any problems.

in this case you could say, the problem was not to use params, but
when you have to dynamically create your sql command, because
the database structure is also dynamic, you cannot used fixed statements
so easy, when you are in the development progress.

Sure, there are some issues, that sound interesting for ms sql, but when
you know how to use stored procedure language in interbase, you will
definitly not have a problem with IB Optimizer, which is really sometimes
a little bit strange in his decisions.

When you used for select procedures, exceptions, recursions and other
points,
which are easy to learn for interbase, you will never see a reason for a
temptable.
(why to pump the data in any other table)

Another point:

Take a MS SQL Database and pump all the data in an InterBase Database.
You will see, that it will take much less space for the same data. When you
are
working on the same hardware with the same amount of ram, you will see,
that typical customer database Servers (Pentium whatever, less than 1 Ghz,
less than 256 MB RAM, ...) will run really fast on ib, in a lot of
operations
faster than ms sql server with the same data.

A typical customer will also calculate the money for buying a new server
with
your application and this dedicated server is definitly useless for any
other
application, when ms sql server is running on it (this is a difference to
ib).
I would also recommend to use a dedicated server with ib, but it works
really fast on older models.

did you every try to measure speed, when you have not simple
benchmarks, but real operations, such as a lot of real user are writing
and reading data and there are some long running snapshots
from different users at the same time? Perhaps while running an
online backup at the same time?

Another point:

Why is there so many documentation for ms sql server? you need it!
because there are so many things to understand how it works.

There are no professional database-administrators specialised on ib,
because it would be a very boring job. When you will get performance
problems with interbase, it is a problem of the programmer, we never
found a reason in the database (typical problem are long running
transactions, but this could be easily find in Interbase, but you have
to look for the reason in the application, not on the server).

By the way: Wasnt the Optimizer Issue not a point, when Larry Ellison
made his famous offer of a lot of dollars, that some typical operations
are more than 100 times faster on oracle than on mssql.

regarding Pauls Comment on stability:

i know some defect databases, but these were typical problems on
machines, which you will not use as a database server for critical
applications. When somebody switches off his server and does not use
forced writes, he will perhaps have a problem.

I other cases, the people were using buggy ib versions (for example 5.5
or 5.11, which were only created to have a new version with the next
delphi cd) or did not defined secondary files with older ib versions
(before 64 Bit IO was implemented).

There are so many examples, where the server was started months ago
and never had any problem.

These were only some of the things i wanted to write here, and as
you will see, our product ibexpert gives us a lot of customer stories
regarding ib (but we also do delphi trainings with other db systems,
from where we know some differences)

Holger

--
IBExpert - The most Expert for InterBase ---  http://www.ibexpert.com
HK Software - Holger Klemt - Huntestrasse 15 - D-26135 Oldenburg
Telefon Telefax +49 700 IBEXPERT (42397378)  www.h-k.de
Schulungen - Projektuntersttzung - Delphi - InterBase - AS/400

"Ricardo Fava" <rica...@dourados.br> schrieb im Newsbeitrag
news:3d18abed_2@dnews...

We didn't have to redo anything with locking because of our original design
with IB.  We use explicit transaction control with READ COMMITTED isolation,
yes, we're still using the BDE :(, and we always check the record version
prior to updating it to make sure it has the same signature.  Specifically,
every time a record is updated it is marked with the username and current
timestamp; when the next user goes to update the record his copy of it is
compared with what's currently in the db (just the username and timestamp
fields) and only allows the update if the "signature" matches.  This is
possible for us since we do not use data aware controls and every record is
pre-selected prior to the update so that we know if someone has changed it
out from underneath us.  This mechanism is database platform independent, so
it works just as well with MSSQL as it does with IB.

I'd be glad to elaborate more if you are interested...

All of our "porting" work had to do with working around exception handling,
flow of control, and eliminating the reliance on triggers since MSSQL's
triggers are so different.  Our db is heavily dependent on stored procedures
so this was really a lot of work.  Our table/index/constraint metadata was
converted in 1 day (just 70 tables).  Our stored procedures (250) took
another 3 months.

hope that helps,
Paul J. Mills

Hi Paul,

Just listening in & wondered if you would elaborate on your locking
solution.  Am currently looking for one.

TIA

Stuart.

news:3d1a92b7$1_2@dnews...

Hi Holger,
Good points :-)

Do you know if MS SQL server supports recursion in it's Stored Procs ?

I myself really like IB/Firebird, but the dependencies can really be a pain
sometimes i.e. changing field names etc that are referenced in a stored
proc.
Do you have any advice on that?

Thanks,

Tony Caduto

Paul,
How come you didn't try Firebird 1.0 first before going to the dark side :-)

You could have also run FB on Linux which works well with multiple CPUs

I have been running a production db on Linux for over a year with great
success.

Just wondering,

Thanks,

Tony Caduto

Thank you for the comments (which were sufficient).  

Regards,
Aage J.

In article <3d19e516$1_2@dnews>, Paul J. Mills says...

Hi,

Not disputing your findings, but...

Well,  I think some of the third party tools are pretty good, and there
are free/commercial ones to suit everyone taste.  Whilst none are
perfect,  I can think of a great deal more I am missing.

For instance?

Not the experience of most people.  I have been using IB/FB for years
and have never experienced corrections in production that have not been
the fault of 'user error'

Bit of a secret weapon then <g>

J