About the the clustering: I'm sure it will help for failover, but it
won't help much when loading the database. You see, the bottleneck is
the disk - it doesn't matter whether you have one or two server
instances running. It does help, however, if you get better disks.

About the three possible solutions, I think A is the best. Using B or C
can get you into trouble if you need to deal with BLOBs, as they cannot
handle binary information.

Option A *shouldn't* be so slow. Make sure you check the numerous
whitepapers on performance; maybe you are using incorrect transaction
properties, or a very small page size, or you are building indexes while
importing data, or something of the sort. If you decide to pursue these
ideas, however, it'd get off-topic for this list.

About failover solutions for InterBase: your ideas should work OK.
However, bear in mind that during a failover, current connections become
unavailable - this is impossible to avoid. To find about what MS SQL 2K
offers and how it goes about it, you can check
http://www.microsoft.com/sql/evaluation/features/reliable.asp. About the
only feature that cannot currently be done with IB is the active-active
configuration (some databases are handled by a server, some by other,
and one can take up the other's load on failure; this cannot be done
without knowledge of clustering inside the engine to present the cluster
as a unified server and internally dispatch requests to server handling
a particular database).

Note: this is off my head - I have never implemented a system with
automatic failover in practice.

Marcelo Lopez Ruiz


montgomery@... wrote:

><snip/>
>At first glance of various SQL Server user testimonials, it seems SQL
>Server 2000 with clustering will solve both of my problems outlined
>below.
>
>The First Problem:
>
>Most of my clients have very small datasets on our server, with
>perhaps 10,000 records (at most) within their data space. There are
>some prospective clients we would really like to get (and who really
>want to be able to use our services) who have databases containing 2-
>3 million records of equivalent data in their possesion that would
>need to be imported into our system. Getting this type of client
>will more than pay for the transition to SQL Server and its
>additional administration costs.
>
>To import data, I can: a) use an external application using IBO to
>perform a series of parameterized inserts, b) use a script file of
>insert statements containing a whole bunch of insert statements
>created from the clients data, or c) use the 'External Files'
>approach (which is somewhat of an arcane process).
>
>Option 'C' can be safely ruled out because it uses every last bit of
>CPU power on the IB server until it completes (which takes many
>hours - leaving our other clients with an unacceptable level of
>system performance until the import is completed).
>
>Option 'A' is INCREDIBLY slow. It might take a week to import a
>clients data using that option.
>
>Option 'B' seems to be in the middle of 'A' and 'B', as long as the
>script has the 'Yield' property set to True, otherwise it behaves
>much like Option 'C'.
>
>Overall, I don't believe the long-term management of such a large
>number of records will be difficult, but just getting those records
>into the database to begin with seems nearly impossible.
>
>The Second Problem:
>
>I really, really want some type of automatic fail-over redundancy if
>my primary IB server crashes. IB really doesn't seem to make this
>easy to accomplish.
>
>The only (seemingly) viable theory I've worked out to accomplish this
>is:
>
>1) Put all data files onto a shared disk array, connected to a
>Primary and Backup IB server
>2) Replicate the ISC4 database from the Primary server to the Backup
>server in real time.
>3) Use a hardware load balancer which will route all client requests
>to the Primary server unless the Primary server is COMPLETELY
>unavailable, in which case requests will be directed to the Backup
>server.
>
>Does this theory seem like it is worth pursuing? Am I missing some
>fundamental aspect that I should be considering?
>
>Let me be very clear: I really want to continue using IB! Any
>thoughts, suggestions, comments, or rants would be greatly
>appreciated.
>
>Best Regards,
>
>David Montgomery
>montgomery@...
>