Multiple-Instance Cluster

Situation: Two nodes, two nodes are active, two virtual
servers, two separate applications. One active and one
wait on each. Active on each fails over to wait on each
other server so that I should only lose one application at
a time.
Question: Two SQL instances on each node/virtual server?
Each SQL instance has its own IP address? The active and
wait have their own IP addresses?
Ok, so you want to run a 2 node cluster, with both nodes being active at the
same time. Two databases for two applications.
You will only need 1 instance of SQL on a node, unless the application
requires something different.
Each Node will have an IP addr, the cluster will have an addr, and each
instance (1 on each server) will have an IP addr.
With all the said, I never recommend an Active/Active cluster. SQL optimizes
memory. When you have a failure, both instances of SQL and its IP addr will
then be on one box. This will change the way SQL can use and optimize
memory. This process takes time, during that time, the system is much
slower. Performance on most Active/Active SQL Clusters suffer during a
failed state.
If at all possible, run both apps on the same node, at the same time. Making
sure of course, that each system has ample resources.
Hope this helps.
Cheers,
Rod
"Lucian" <anonymous@.discussions.microsoft.com> wrote in message
news:1d2b101c42329$d3d501c0$a401280a@.phx.gbl...
> Situation: Two nodes, two nodes are active, two virtual
> servers, two separate applications. One active and one
> wait on each. Active on each fails over to wait on each
> other server so that I should only lose one application at
> a time.
> Question: Two SQL instances on each node/virtual server?
> Each SQL instance has its own IP address? The active and
> wait have their own IP addresses?
|||With SQL Server 2000 you can install up to 16 instances (1 default and 15
named or 16 named). The number of nodes that can be a possible owner for
each instance depends on your edition/version of Windows. If you have a
typical 2-node cluster, each node can own 0,1, or many of the installed
instances.
Whether it makes sense to install multiple instances (something that used
to be called active/active when you could only have two instances) depends
totally on your environment - your hardware, your application, your
business needs, etc.
If you want to know more about clustering, check out BOL or the MSPress
book on High Availability.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Multiple statement handles on one connection

I want to use multiple concurrently active statement
handles on a single connection handle. An ODBC call to
SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
indicating that there is a limit of 1 active statement on
a single connection. If I try to SQLExecDirect on another
statement handle on the same connection handle I get an
error complaining that there is another active statement
on the same connection.
Is there any way to configure either SQLServer or its ODBC
driver to allow multiple concurrently active statement
handles on the same connection? This is pretty common
database programming practice to Exec one query, then use
a loop with SQLFetch and then execute multiple SQL
statements on another statement handle within the loop.
Even lowly MSAccess allows multiple concurrently active
statement handles on the same connection.
My ODBC driver version is 2000.85.1022.00.
My SQLServer version SQL Server Developer Edition 8.00.194
(RTM)This is not possible with current versions of SQL Server or the SQL Server
ODBC Driver, when using the default "firehose" cursor. You can use
server-side cursors, which allow you to fetch a single row at a time from
the server (and therefore free up the connection between each row), or you
can cache the data from the first statement yourself (therefore freeing up
the connection). And of course, you can open up a second connection for the
second statement.
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Lee Scheffler" <anonymous@.discussions.microsoft.com> wrote in message
news:b47e01c4077d$ef011af0$a101280a@.phx.gbl...
> I want to use multiple concurrently active statement
> handles on a single connection handle. An ODBC call to
> SQLGetInfo with the SQL_ACTIVE_STATEMENS key returns 1,
> indicating that there is a limit of 1 active statement on
> a single connection. If I try to SQLExecDirect on another
> statement handle on the same connection handle I get an
> error complaining that there is another active statement
> on the same connection.
> Is there any way to configure either SQLServer or its ODBC
> driver to allow multiple concurrently active statement
> handles on the same connection? This is pretty common
> database programming practice to Exec one query, then use
> a loop with SQLFetch and then execute multiple SQL
> statements on another statement handle within the loop.
> Even lowly MSAccess allows multiple concurrently active
> statement handles on the same connection.
> My ODBC driver version is 2000.85.1022.00.
> My SQLServer version SQL Server Developer Edition 8.00.194
> (RTM)