multiple instances in 1 cluster pair (please advise)

Dear all,
I tried to post this question yesterday but seems like didnt get thru,
so I will again. If duplicate, please ignore this.
Currently there I have a live production SQL server cluster (SQL2000) in
non-Case sensitive mode. The server is rather powerful with 8 CPU. Now
only running at 20% cap. Recently we bought a new software and required
the db to be in Case sensitive mode.
It was recommended to create a new instance for the new s/w. Can anyone
help to advise on the following
1) If I create 1 more new instance on the existing DB, how do I
configure it for failover? I cant find any guide to configure the new
instance for failover. Any link to recommend?
2) Any impact I need to note?
Thank you,
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
You don't need a new instance - just a new database. SQL Server 2000 can
have multiple databases, each with its own collation.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Francis Ang" <angcheek@.starhub.net.sg> wrote in message
news:%23Q8AqPn3EHA.3380@.TK2MSFTNGP09.phx.gbl...
Dear all,
I tried to post this question yesterday but seems like didnt get thru,
so I will again. If duplicate, please ignore this.
Currently there I have a live production SQL server cluster (SQL2000) in
non-Case sensitive mode. The server is rather powerful with 8 CPU. Now
only running at 20% cap. Recently we bought a new software and required
the db to be in Case sensitive mode.
It was recommended to create a new instance for the new s/w. Can anyone
help to advise on the following
1) If I create 1 more new instance on the existing DB, how do I
configure it for failover? I cant find any guide to configure the new
instance for failover. Any link to recommend?
2) Any impact I need to note?
Thank you,
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Tom,
Thank you for your reply. I have tried that, although it is working, the
software vendor does not support that configuration. They recommended
new instance. Not sure why either.
Regards,
Francis
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Sounds like the vendor doesn't have much experience with SQL Server 2000.
Well, if they insist, then you're forced to install the extra instance. As
long as you have enough memory and CPU power, then you'll be OK. It's just
that you have to manage two instances, instead of one.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Diamond Star" <Diamond@.Star.com> wrote in message
news:u6hW3Pr3EHA.3316@.tk2msftngp13.phx.gbl...
Hi Tom,
Thank you for your reply. I have tried that, although it is working, the
software vendor does not support that configuration. They recommended
new instance. Not sure why either.
Regards,
Francis
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||But Tom, the DBA dont know how to install a additional instance over the
existing clustered servers. Do you have any guide or link to recommend?
I would really appreciate your help.
Thank you,
Francis Ang
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||It's not that difficult. Just install from the CD as you did for the first
instance. However, the second instance will be a named instance. For
example, if the first instance was called MYSQLSERVER, then you can call the
second one MYSQLSERVER\INST2. The BOL have all the details.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Diamond Star" <Diamond@.Star.com> wrote in message
news:eezVUm33EHA.3336@.TK2MSFTNGP11.phx.gbl...
But Tom, the DBA dont know how to install a additional instance over the
existing clustered servers. Do you have any guide or link to recommend?
I would really appreciate your help.
Thank you,
Francis Ang
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi Tom,
THanks for the advise. I will inform the DBA of this. If you dont mind,
I have a last question. Do the DBA set up the Clustering services for
the new instance just like the first instance.
THank you for the help,
Francis
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Since Cluster Services are already set up, you won't need to set it up
again.
One more thing I should add is that the new SQL Server instance will need
its own disks, independent from the first SQL service. Here's what I mean.
Let's say you have 2 physical nodes - NODE1 and NODE2, each with a drive C:
(nonclustered). You have 2 SQL Server instances - SQL1 and SQL2. Both of
these will need a drive for data and another for logs. Thus, you could have
SQL1 with drive D: and E: and SQL2 with drive F: and G:.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Diamond Star" <Diamond@.Star.com> wrote in message
news:unPVf8B4EHA.2404@.TK2MSFTNGP14.phx.gbl...
Hi Tom,
THanks for the advise. I will inform the DBA of this. If you dont mind,
I have a last question. Do the DBA set up the Clustering services for
the new instance just like the first instance.
THank you for the help,
Francis
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Multiple Cascade Paths - Fails on SQL Server 2000

This is an old issue, which I chose to ignore, since it was for a temporary
app with a handful of users (famous last words, I know).
The "multiple foreign keys" post today got me thinking about it, and I
figured it is worth looking for a solution.
I will post DDL at the end, but the theory is the main point here, so I hope
my description will suffice.
Please forgive the camel case naming convention, and the silly tbl prefixes.
I do not like it either, but it is a standard in our department.
tblParameter has two FK constraints.
One references tblReport.strReportID
One references tblLookupProcedures.strLookupProcedure
Both tblReport and tblLookupProcedures have a FK reference to
tblDatabaseConnections.strConnectionName
Ideally, all of these constraints have ON UPDATE CASCADE, but the second
constraint on tblParameter will error out with this message:
****
Introducing FOREIGN KEY constraint 'FK_tblParameter_tblLookupProcedures' on
table 'tblParameter' may cause cycles or multiple cascade paths. Specify ON
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
constraints.
****
I believe the error is caused because updates to the PK in
tblDatabaseConnections triggers updates to the FK in two tables, each of
which are referenced by tblParameter. Now the references are not on the
same columns, but SQL Server seems to think this could lead to circular
references.
The questions are:
1. Is this against RD theory, or is it just a quirk of SQL Server?
2. Is there a setting that will allow this?
3. What is the usual workaround? A trigger is the only thing that I could
think of.
Here is the DDL, scaled down to demonstrate the issue.
CREATE TABLE [dbo].[tblDatabaseConnections] (
[strConnectionName] [varchar] (10) PRIMARY KEY NOT NULL ,
[strDatabase] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblLookupProcedures] (
[strLookupProcedure] [varchar] (50) PRIMARY KEY NOT NULL ,
[strProcedureDescription] [varchar] (100) NOT NULL ,
[strConnectionName] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblReport] (
[strReportID] [varchar] (10) PRIMARY KEY NOT NULL ,
[strReportDescr] [varchar] (50) NULL ,
[strConnectionName] [varchar] (10) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblParameter] (
[strReportID] [varchar] (10) NOT NULL ,
[strParamID] [varchar] (20) NOT NULL ,
[strLookupProcedure] [varchar] (50) NOT NULL
PRIMARY KEY ([strReportID],[strParamID])
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblLookupProcedures] ADD
CONSTRAINT [FK_tblLookupProcedures_tblDatabaseConne
ctions] FOREIGN KEY
([strConnectionName]
) REFERENCES [dbo].[tblDatabaseConnections] ([strConnectionName]
) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblReport] ADD
CONSTRAINT [FK_tblReport_tblDatabaseConnections] FOREIGN KEY
([strConnectionName]
) REFERENCES [dbo].[tblDatabaseConnections] ([strConnectionName]
) ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tblParameter] ADD
CONSTRAINT [FK_tblParameter_tblReport] FOREIGN KEY
([strReportID]
) REFERENCES [dbo].[tblReport] ([strReportID]
) ON UPDATE CASCADE
GO
--This constraint fails due to a perceived cyclical reference
ALTER TABLE [dbo].[tblParameter] ADD CONSTRAINT
[FK_tblParameter_tblLookupProcedures] FOREIGN KEY
([strLookupProcedure]
) REFERENCES [dbo]. [tblLookupProcedures]([strLookupProcedur
e]
) on update cascade
GO
-- if we remove on update cascade, the constraint gets created
ALTER TABLE [dbo].[tblParameter] ADD CONSTRAINT
[FK_tblParameter_tblLookupProcedures_NOC
ASCADE] FOREIGN KEY
([strLookupProcedure]
) REFERENCES [dbo]. [tblLookupProcedures]([strLookupProcedur
e]
)
GO
drop table [dbo].[tblParameter];
drop table [dbo].[tblReport];
drop table [dbo].[tblLookupProcedures];
drop table [dbo].[tblDatabaseConnections];I sketched it all out on some scrap paper. I think the real issue is that th
e
row that would get deleted on tblParameter depends upon which path the
cascade took down the tree. I can see how deleting a value of
strConnectionName from tblDatabaseConnections could result in different
rows being deleted in tblParameter through the cascade depending on the path
,
because the foreign keys in tblParameter point to different columns in
tblLookupProcedures and tblReport. (I hope that made sense).
Another thing is that SQL will determine if any constraints would be
violated by the cascading action, and rollback any transactions that
initiated the cascade. What if one path violated some constraint on
tblParameter, but the other path didn't?
Just thinking out loud.
"Jim Underwood" wrote:

> This is an old issue, which I chose to ignore, since it was for a temporar
y
> app with a handful of users (famous last words, I know).
>|||Interesting points, but I am using cascade update, not cascade delete, so
the updates would not filter down through the levels, they would stop at the
first table, since different columns are affected.
Just for chuckles I tried adding
ON DELETE NO ACTION
to the constraints but the results were the same.
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:5C7F6B3D-DC1B-4F64-9F4A-16EE6F66D51C@.microsoft.com...
> I sketched it all out on some scrap paper. I think the real issue is that
the
> row that would get deleted on tblParameter depends upon which path the
> cascade took down the tree. I can see how deleting a value of
> strConnectionName from tblDatabaseConnections could result in different
> rows being deleted in tblParameter through the cascade depending on the
path,
> because the foreign keys in tblParameter point to different columns in
> tblLookupProcedures and tblReport. (I hope that made sense).
> Another thing is that SQL will determine if any constraints would be
> violated by the cascading action, and rollback any transactions that
> initiated the cascade. What if one path violated some constraint on
> tblParameter, but the other path didn't?
> Just thinking out loud.
>
> --
> "Jim Underwood" wrote:
>
temporary
>|||As far as relational set theory goes, an update is really just a delete /
insert pair. (There's no 'updated' virtual table you can use inside a
trigger).
"Jim Underwood" wrote:

> Interesting points, but I am using cascade update, not cascade delete, so
> the updates would not filter down through the levels, they would stop at t
he
> first table, since different columns are affected.
> Just for chuckles I tried adding
> ON DELETE NO ACTION
> to the constraints but the results were the same.
> "Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
> news:5C7F6B3D-DC1B-4F64-9F4A-16EE6F66D51C@.microsoft.com...
> the
> path,
> temporary
>
>|||Ahhhh...
Now that makes sense.
"Mark Williams" <MarkWilliams@.discussions.microsoft.com> wrote in message
news:6F31E2E5-9B2F-4EF3-8D53-2B44F6403D0B@.microsoft.com...
> As far as relational set theory goes, an update is really just a delete /
> insert pair. (There's no 'updated' virtual table you can use inside a
> trigger).
> "Jim Underwood" wrote:
>
so
the
message
that
different
the|||Jim,
I have had discussions about this (in one of these newsgroups) when it
was introduced in SQL Server 2000. SQL Server really takes a very
simplistic view. It acts as if each table only has one column when it
determines if there is a potential circular reference. This is really
silly if you ask me, because it means there is hardly any real life
application that makes it worth your while to use it.
AFAIK there is not setting around this. You must avoid this supposed
circular reference. One way of doing that is to define the 'offending'
foreign key constraint as not cascading. But obviously that does not
achieve the desired cascading.
Of course triggers are always an options, but than means disabling the
foreign key constraint. So it is always a compromise...
By the way: has any of this changed in SQL Server 2005?
Gert-Jan
Jim Underwood wrote:
> This is an old issue, which I chose to ignore, since it was for a temporar
y
> app with a handful of users (famous last words, I know).
> The "multiple foreign keys" post today got me thinking about it, and I
> figured it is worth looking for a solution.
> I will post DDL at the end, but the theory is the main point here, so I ho
pe
> my description will suffice.
> Please forgive the camel case naming convention, and the silly tbl prefixe
s.
> I do not like it either, but it is a standard in our department.
> tblParameter has two FK constraints.
> One references tblReport.strReportID
> One references tblLookupProcedures.strLookupProcedure
> Both tblReport and tblLookupProcedures have a FK reference to
> tblDatabaseConnections.strConnectionName
> Ideally, all of these constraints have ON UPDATE CASCADE, but the second
> constraint on tblParameter will error out with this message:
> ****
> Introducing FOREIGN KEY constraint 'FK_tblParameter_tblLookupProcedures' o
n
> table 'tblParameter' may cause cycles or multiple cascade paths. Specify O
N
> DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
> constraints.
> ****
> I believe the error is caused because updates to the PK in
> tblDatabaseConnections triggers updates to the FK in two tables, each of
> which are referenced by tblParameter. Now the references are not on the
> same columns, but SQL Server seems to think this could lead to circular
> references.
> The questions are:
> 1. Is this against RD theory, or is it just a quirk of SQL Server?
> 2. Is there a setting that will allow this?
> 3. What is the usual workaround? A trigger is the only thing that I could
> think of.
>
[snip]|||DB2 had this problem in a very early version. If you had cascade
chains like A-->B, B-->C and A-->C, the final values in C would be
whoever got there last to overwrite the value. Today DB2 has a pretty
good cycle detector and allows some things that it did not before.
The relational rule is that all possible cascade paths must leave the
DB in the same state when they finish and that state has to be validate
under all the constraints.
This is hard to implement in theory -- remember graph theory? So real
products give up at some point, using a combination of a graph and
constraints. SQL Server happens to quit very early :)|||Thanks for the response. I don't have a 2005 environment to test in, so I
am not sure if the issue is the same or not.
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:446A3C9E.BE700DFF@.toomuchspamalready.nl...
> Jim,
> I have had discussions about this (in one of these newsgroups) when it
> was introduced in SQL Server 2000. SQL Server really takes a very
> simplistic view. It acts as if each table only has one column when it
> determines if there is a potential circular reference. This is really
> silly if you ask me, because it means there is hardly any real life
> application that makes it worth your while to use it.
> AFAIK there is not setting around this. You must avoid this supposed
> circular reference. One way of doing that is to define the 'offending'
> foreign key constraint as not cascading. But obviously that does not
> achieve the desired cascading.
> Of course triggers are always an options, but than means disabling the
> foreign key constraint. So it is always a compromise...
> By the way: has any of this changed in SQL Server 2005?
> Gert-Jan
>
> Jim Underwood wrote:
temporary
hope
prefixes.
second
on
ON
could
> [snip]|||Thanks. At least I know that my logical model is ok, I just need to change
the implementation a bit.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147828111.648849.30010@.u72g2000cwu.googlegroups.com...
> DB2 had this problem in a very early version. If you had cascade
> chains like A-->B, B-->C and A-->C, the final values in C would be
> whoever got there last to overwrite the value. Today DB2 has a pretty
> good cycle detector and allows some things that it did not before.
> The relational rule is that all possible cascade paths must leave the
> DB in the same state when they finish and that state has to be validate
> under all the constraints.
> This is hard to implement in theory -- remember graph theory? So real
> products give up at some point, using a combination of a graph and
> constraints. SQL Server happens to quit very early :)
>