Migrating from Intebrase to SQL Server

Hi.
We've been having many problems with our current Interbase database
(v6.0.2.0). These are mainly corruption issues with the constant need to
backup and restore, and the fact that it is almost impossible to reliably
change metadata in a live environment.
We need to change servers, and our two options are either upgrading to
Interbase 7.1, or migrating to SQL Server 2000.
These are our requirements:
- the ability to reliaby and efficiently cope with huge amounts of data
- the server must be able to cope seemlessly with clients not disconnecting
cleanly and transactions left open (IB seems to have trouble with this), or
at least provide some mechanism to view such transactions and kill them
- the ability to change metada in a live environment with many connected
users (create/drop tables, foreign keys, etc - is this possible with any DB
at all?)
- it is critical that the database is up 24/7 - we can afford no downtime -
is this something SQL Server can provide?
I have also read a lot about how Interbase is unique in that it provides a
multi-generational architecture - presumably this means SQL Server does not,
but what are the implications of this? Could someone explain how SQL's
architecture differs from IB's with respect to this?
Has anyone else had any experience (good or bad) migrating from IB to SQL?
Perhaps our problems would not be solved by this and therefore it would be a
waste of time and money?
We basically need as much info on the subject as possible so we can make an
informed decision.
MikeAs you haven't gotten any replies yet, I'll only chime in with my little comment (I haven't worked
with Interbase):
> I have also read a lot about how Interbase is unique in that it provides a
> multi-generational architecture - presumably this means SQL Server does not,
> but what are the implications of this? Could someone explain how SQL's
> architecture differs from IB's with respect to this?
The multi-generation stuff means that when you start reading, you get a "personal snapshot" of the
data. You work with that over time, and other modifying the real data will not block you (but you
have the be extremely careful if you then have to do modifications based on this old data). SQL
Server doesn't have this, so you have blocking. If you try to read something that someone else has
modified but not committed, you will be blocked until the modifier ends the transaction. So, in SQL
Server, you want to use short transactions.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike Tremendous" <noone@.hotmail.com> wrote in message news:ONxqCMmrDHA.2432@.TK2MSFTNGP10.phx.gbl...
> Hi.
> We've been having many problems with our current Interbase database
> (v6.0.2.0). These are mainly corruption issues with the constant need to
> backup and restore, and the fact that it is almost impossible to reliably
> change metadata in a live environment.
> We need to change servers, and our two options are either upgrading to
> Interbase 7.1, or migrating to SQL Server 2000.
> These are our requirements:
> - the ability to reliaby and efficiently cope with huge amounts of data
> - the server must be able to cope seemlessly with clients not disconnecting
> cleanly and transactions left open (IB seems to have trouble with this), or
> at least provide some mechanism to view such transactions and kill them
> - the ability to change metada in a live environment with many connected
> users (create/drop tables, foreign keys, etc - is this possible with any DB
> at all?)
> - it is critical that the database is up 24/7 - we can afford no downtime -
> is this something SQL Server can provide?
> I have also read a lot about how Interbase is unique in that it provides a
> multi-generational architecture - presumably this means SQL Server does not,
> but what are the implications of this? Could someone explain how SQL's
> architecture differs from IB's with respect to this?
> Has anyone else had any experience (good or bad) migrating from IB to SQL?
> Perhaps our problems would not be solved by this and therefore it would be a
> waste of time and money?
> We basically need as much info on the subject as possible so we can make an
> informed decision.
> Mike
>