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 Values to single parameter

Hi,
I have a situation where in I got to pass more than one value to a single parameter like I want to see all the employees of department 5,6 & 7 & so on, my query goes like this "Select firstname,lastname, title from employees where departmentid=@.departmentid". In this variable @.deparmentid I need to pass more than one value. is it possible? if it can, dude's pull it fast to me.

Hi adonis

It is possible I use SQL to check for a comma seperated list. Create this function and pass it a comma seperated list, it will return a table with the values. Change your SQL to something like this:
"Select firstname,lastname, title from employees where departmentid IN ( SELECT [ENTRY] FROM ListToTable(@.departmentid))". It should return the desired result. However you should add maybe an IF to check if the user want all employees and not just a few

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ListToTable') DROP FUNCTION ListToTable GO

CREATE FUNCTION ListToTable
(
/*
** Usage: select entry from listtotable('abc,def,ghi') order by entry desc
*/
@.mylist varchar(8000) )
RETURNS @.ListTable TABLE
(
seqid int not null,
entry varchar(255) not null
)

AS

BEGIN
DECLARE @.this varchar(255),
@.rest varchar(8000),
@.pos int,
@.seqid int

SET @.this = ' ' SET @.seqid = 1 SET @.rest = @.mylist SET @.pos = PATINDEX('%,%', @.rest) WHILE (@.pos > 0) BEGIN set @.this=substring(@.rest,1,@.pos-1) set @.rest=substring(@.rest,@.pos+1,len(@.rest)-@.pos) INSERT INTO @.ListTable (seqid,entry) VALUES (@.seqid,@.this) SET @.pos= PATINDEX('%,%', @.rest) SET @.seqid=@.seqid+1 END set @.this=@.rest INSERT INTO @.ListTable (seqid,entry) VALUES (@.seqid,@.this)
RETURN
END
, Hope this helps
l0n3i200n

|||

I really appriciate your effort. but dont you think it all a work around we are fiddling the query here, Insted I was looking for any option in the reporting services thru wich we can send multiple values. Your solution is feasible when you have a small query but you Iam playing with arount more that 25 tables the queries are so complex that if I fiddle those than Its gona suck me.

Any ways thanks and if U have any thing coming up please update me.

Multiple Return Values

I have a situation where I need two values (both are integers) returned from a stored procedure. (SQL 2000)

Right now, I use the statement "return @.@.Identity" for a single value, but there is another variable assigned in the procedure, @.NewCounselingRecordID that I need to pass back to the calling class method.

I was thinking of concatenating the two values as a string and parsing them out after they are passed back to the calling method. It would look something like "21:17", with the colon character acting as a delimiter.

However, I feel this solution is kludgy. Is there a more correct way to accomplish this?

Thanks in advance for your comments.check out BOL for OUTPUT parameters..

hth|||Yes, that helps...I can't believe I brain farted on output parameters. (Duh :P)

Thanks