Multiple Instance cluster

Hi,
I am going to setup 2 instances of SQL Server 2000 in a 2-nodes clustered
environment with each instance running on each node in a normal operation.
Each node will act as a failover of the other node. I am not very certain
that I understand how to do it. Here's what I am going to do.
1) Setup the 2 nodes cluster.
2) Create 2 SQL Cluster group2 with each group having their own resources
(e.g. disk, IP, DTC, etc)
3) Install the 1st instance and assign it with a virtual name.
4) Install the 2nd instance and assgin another virtual name.
Do I miss out anything or anything that I need to take note?
Thanks You,
AlexLooks like you are well on your way. Make sure and set the correct
preferred nodes order on the cluster administration tool so that each
instance knows where it wants to live under 'normal' operating conditions.
Also, make sure you don't overcommit memory. You will need to leave enough
memory so you can 'stack' both instances on a single server in a failure
condition.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex" <Yukon@.nospam.nospam> wrote in message
news:%231IbTEUrEHA.3848@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I am going to setup 2 instances of SQL Server 2000 in a 2-nodes clustered
> environment with each instance running on each node in a normal operation.
> Each node will act as a failover of the other node. I am not very certain
> that I understand how to do it. Here's what I am going to do.
> 1) Setup the 2 nodes cluster.
> 2) Create 2 SQL Cluster group2 with each group having their own resources
> (e.g. disk, IP, DTC, etc)
> 3) Install the 1st instance and assign it with a virtual name.
> 4) Install the 2nd instance and assgin another virtual name.
> Do I miss out anything or anything that I need to take note?
>
> Thanks You,
> Alex
>|||Thanks for the advice :o)
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uwY11RUrEHA.3288@.TK2MSFTNGP12.phx.gbl...
> Looks like you are well on your way. Make sure and set the correct
> preferred nodes order on the cluster administration tool so that each
> instance knows where it wants to live under 'normal' operating conditions.
> Also, make sure you don't overcommit memory. You will need to leave
> enough
> memory so you can 'stack' both instances on a single server in a failure
> condition.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex" <Yukon@.nospam.nospam> wrote in message
> news:%231IbTEUrEHA.3848@.TK2MSFTNGP14.phx.gbl...
>> Hi,
>> I am going to setup 2 instances of SQL Server 2000 in a 2-nodes clustered
>> environment with each instance running on each node in a normal
>> operation.
>> Each node will act as a failover of the other node. I am not very
>> certain
>> that I understand how to do it. Here's what I am going to do.
>> 1) Setup the 2 nodes cluster.
>> 2) Create 2 SQL Cluster group2 with each group having their own resources
>> (e.g. disk, IP, DTC, etc)
>> 3) Install the 1st instance and assign it with a virtual name.
>> 4) Install the 2nd instance and assgin another virtual name.
>> Do I miss out anything or anything that I need to take note?
>>
>> Thanks You,
>> Alex
>>
>|||Hi,
FIrst of all, I would show my gratidute for MVP Geoff N. Hiten's
suggestions!
Additionally, I would love to show some useful documents for your reference
and hope this helps
SQL Server 2000 Failover Clustering White Paper
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
Support WebCast: Introduction to Microsoft SQL Server 2000 Clustering
http://support.microsoft.com/default.aspx?scid=kb;en-us;325106
INF: Frequently Asked Questions - SQL Server 2000 - Failover Clustering
http://support.microsoft.com/?id=260758
Installation order for SQL Server 2000 Enterprise Edition on Microsoft
Cluster Server
http://support.microsoft.com/?id=243218
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Thanks Mingqing.
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:jBO9LobrEHA.2500@.cpmsftngxa06.phx.gbl...
> Hi,
> FIrst of all, I would show my gratidute for MVP Geoff N. Hiten's
> suggestions!
> Additionally, I would love to show some useful documents for your
> reference
> and hope this helps
> SQL Server 2000 Failover Clustering White Paper
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/failclus.mspx
> Support WebCast: Introduction to Microsoft SQL Server 2000 Clustering
> http://support.microsoft.com/default.aspx?scid=kb;en-us;325106
> INF: Frequently Asked Questions - SQL Server 2000 - Failover Clustering
> http://support.microsoft.com/?id=260758
> Installation order for SQL Server 2000 Enterprise Edition on Microsoft
> Cluster Server
> http://support.microsoft.com/?id=243218
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Alex,
Thanks for your prompt updates!
If you encountered any questions or concerns on setup or configuration
multiple instances clustering environment. Don't hesitate to let me know.
We are always here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Multiple indexes on the same column

Is it possible to create one clustered index and one nonclustered index on the same column of a table?

For example, if we have a table Employees (EmpID, EmpName), then will SQL Server execute the following commands successfully?

Create clustered index indx1 on Employees (EmpID)
Go

Create nonclustered index indx2 on Employees (EmpID)
Go

yes, it is possible but it is not clear why you would want to do that. Here is the output of sp_help on the table empoyee and you will notice that there are two indexes one clustered and other non-clustered on the same key.
Name Owner Type Created_datetime

-- -- - --

Employees dbo user table 2005-10-28 09:30:42.733

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation

-- -- -- -- -- -- -- -- -- --

EmpID int no 4 10 0 yes (n/a) (n/a) NULL

EmpName varchar no 100 yes no yes SQL_Latin1_General_CP1_CI_AS

Identity Seed Increment Not For Replication

-- -

No identity column defined. NULL NULL NULL

RowGuidCol

--

No rowguidcol column defined.

Data_located_on_filegroup

--

PRIMARY

index_name index_description index_keys

-- -

indx1 clustered located on PRIMARY EmpID

indx2 nonclustered located on PRIMARY EmpID

No constraints are defined on object 'employees', or you do not have permissions.

No foreign keys reference table 'employees', or you do not have permissions on referencing tables.

No views with schema binding reference table 'employees'.

|||Well, I was asked this question in an interview :)
Even I am not aware about any practical aspect of the question.

Thank you for your response, anyway.|||Thinking more about it, I think it can be useful under following scenarios
(1) you have range queries on empid that retrieves large number of columns in the table so it may make sense to have clustered index on empid.
AND
(2) you have range query on empid where you need only a subset of columns. you can make those subset of columns as included columns such that your query is covered by the index. This will eliminate access to the data page.

thanks