Clustering with W2K3 and SQL 2005 (64bit) - best practice setup

Hi
I'm a little confused how best to setup a new 2005 deployment. I'm
moving from a 2000 environment, which used Legato's AAM software to
support failover. The new environment will use MS clustering.
The 2000 setup has multiple instances on each node, and each instance
can run on 3 out of 4 of the 4-node setup. With MS, it looks like each
instance has to have it's own 'virtual server'. So, in this setup,
named instances seem a bit redundant, since you can name the virtual
server. EG Why use Server1\Inst1, Server2\Inst2, when
Server1\<default>, Server2\<default> is just as informative
So, my questions are: 1) is it good practice to use named instances in
a clustered setup;
and 2) what is the rationale behind restricting a virtual server to a
single instance
Thanks.
You only get one default instance per cluster, not per virtual server.
Personally, I only use named instances to avoid name style confusion.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"steve" <stevester@.freeuk.com> wrote in message
news:e4c23b50-bc07-450c-94f7-b71ed051c4bc@.e67g2000hsc.googlegroups.com...
> Hi
> I'm a little confused how best to setup a new 2005 deployment. I'm
> moving from a 2000 environment, which used Legato's AAM software to
> support failover. The new environment will use MS clustering.
> The 2000 setup has multiple instances on each node, and each instance
> can run on 3 out of 4 of the 4-node setup. With MS, it looks like each
> instance has to have it's own 'virtual server'. So, in this setup,
> named instances seem a bit redundant, since you can name the virtual
> server. EG Why use Server1\Inst1, Server2\Inst2, when
> Server1\<default>, Server2\<default> is just as informative
> So, my questions are: 1) is it good practice to use named instances in
> a clustered setup;
> and 2) what is the rationale behind restricting a virtual server to a
> single instance
> Thanks.
|||On 3 Dec, 14:23, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> You only get one default instance per cluster, not per virtual server.
> Personally, I only use named instances to avoid name style confusion.
> --
> Geoff N. Hiten
> Senior SQL Infrastructure Consultant
> Microsoft SQL Server MVP
> "steve" <steves...@.freeuk.com> wrote in message
> news:e4c23b50-bc07-450c-94f7-b71ed051c4bc@.e67g2000hsc.googlegroups.com...
>
>
>
>
> - Show quoted text -
Thanks Geoff. So I can't use deafult instances with each additional
virtual server.
I'm still puzzled why you can only have one instance per virtual
server tho. - seems overly restrictive.
|||It has to do with how SQL organizes binaries for different instances. Or
mor accurately, how multiple instances were handled in SQL 2000.
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"steve" <stevester@.freeuk.com> wrote in message
news:1a8e02f1-ca6b-44b3-84ee-b0589802b2c8@.j20g2000hsi.googlegroups.com...
> On 3 Dec, 14:23, "Geoff N. Hiten" <SQLCrafts...@.gmail.com> wrote:
> Thanks Geoff. So I can't use deafult instances with each additional
> virtual server.
> I'm still puzzled why you can only have one instance per virtual
> server tho. - seems overly restrictive.
|||"steve" <stevester@.freeuk.com> wrote in message
news:e4c23b50-bc07-450c-94f7-b71ed051c4bc@.e67g2000hsc.googlegroups.com...

> The 2000 setup has multiple instances on each node, and each instance
> can run on 3 out of 4 of the 4-node setup. With MS, it looks like each
> instance has to have it's own 'virtual server'. So, in this setup,
> named instances seem a bit redundant, since you can name the virtual
> server. EG Why use Server1\Inst1, Server2\Inst2, when
> Server1\<default>, Server2\<default> is just as informative
Each virtual server must be an instance. You can only have one default
instance per cluster and the rest have to be named instances.

> So, my questions are: 1) is it good practice to use named instances in
> a clustered setup;
You should only used named instances in a cluster. There are some known
issues around patching that can cause problems, for example, if you service
pack a named instance before the default.

> and 2) what is the rationale behind restricting a virtual server to a
> single instance
Each instance requires its own resources, thus, each requires its own
virtual server.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
Dec 10 - 13 in Denver
Jan 18 - 31 in Denver
|||Thanks for your answers - very helpful.
Steve

Clustering of SQL Server 2000(SE)

We

are looking for clustering of SQL Server 2000 [standard Edition] using

third party software, any inputs in this regard would be of great help

Microsoft does not support clustering SQL Server 2000 standard edition.

SQL Server 2005 standard edition supports 2 node clusters.

|||Kevin,
are there any third party softwares which can help us in cluster creation in SS2000(SE)?

Clustering of SQL Server 2000 SE

We are looking at clustering of SQL Server 2000 [standard Edition] using third party software, any inputs in this regard would be of great helpPlease post your question to the right forum: SQL Server Disaster Recovery and Availability

Clustering concepts...

We have just setup two servers running a copy of SQL 2000 on Windows 2000
server. Each of these servers is running vendor software that interrracts
with the databases. One server is primary and one is backup. Clients only
interract with the vendor process on the servers and not the SQL directly.
Some general questions on SQL 2000 high availability options...
Does Active/Passive and Active/Active refer to clustering options that
require that the servers be clustered on the OS level? Is this the only
option that keeps the databases syncronized in real time?
Is replication a good method if the failover will be done manually?
Thanks!
Karl Albrecht
Active/Active and Active/Passive are obsolete concepts from SQL 7.0 when
each SQL instance had a primary owner host node and a partner owner node.
Under SQL 2000, all nodes are interchangable in a cluster. Some people
mistakenly use teh terms to refer to single and multiple instance SQL
clustering.
Clustering involves a single data store on a 'shared' storage system (SCSI
Array, SAN, etc. NOT NAS). Shared is not exactly correct since the cluster
software arbitrates ownership so that only one host node owns the
resource(s) at a time. Therefore, you have to have a working MSCS cluster
in order to create a SQL cluster.
Replication is not a good technique for creating a warm standby server.
Replication has VERY limited ability to handle schema changes without a
total resynch. It also does not replicate stored procedures or views.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Karl Albrecht" <karlman@.pacbell.net> wrote in message
news:O8QGJUplEHA.2340@.TK2MSFTNGP11.phx.gbl...
> We have just setup two servers running a copy of SQL 2000 on Windows 2000
> server. Each of these servers is running vendor software that interrracts
> with the databases. One server is primary and one is backup. Clients only
> interract with the vendor process on the servers and not the SQL directly.
>
> Some general questions on SQL 2000 high availability options...
> Does Active/Passive and Active/Active refer to clustering options that
> require that the servers be clustered on the OS level? Is this the only
> option that keeps the databases syncronized in real time?
> Is replication a good method if the failover will be done manually?
>
> Thanks!
> Karl Albrecht
>
|||Geoff, you can replicate stored procs and views.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:u%23O18vplEHA.3712@.TK2MSFTNGP15.phx.gbl...
Active/Active and Active/Passive are obsolete concepts from SQL 7.0 when
each SQL instance had a primary owner host node and a partner owner node.
Under SQL 2000, all nodes are interchangable in a cluster. Some people
mistakenly use teh terms to refer to single and multiple instance SQL
clustering.
Clustering involves a single data store on a 'shared' storage system (SCSI
Array, SAN, etc. NOT NAS). Shared is not exactly correct since the cluster
software arbitrates ownership so that only one host node owns the
resource(s) at a time. Therefore, you have to have a working MSCS cluster
in order to create a SQL cluster.
Replication is not a good technique for creating a warm standby server.
Replication has VERY limited ability to handle schema changes without a
total resynch. It also does not replicate stored procedures or views.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Karl Albrecht" <karlman@.pacbell.net> wrote in message
news:O8QGJUplEHA.2340@.TK2MSFTNGP11.phx.gbl...
> We have just setup two servers running a copy of SQL 2000 on Windows 2000
> server. Each of these servers is running vendor software that interrracts
> with the databases. One server is primary and one is backup. Clients only
> interract with the vendor process on the servers and not the SQL directly.
>
> Some general questions on SQL 2000 high availability options...
> Does Active/Passive and Active/Active refer to clustering options that
> require that the servers be clustered on the OS level? Is this the only
> option that keeps the databases syncronized in real time?
> Is replication a good method if the failover will be done manually?
>
> Thanks!
> Karl Albrecht
>
|||> Clustering involves a single data store on a 'shared' storage system (SCSI
> Array, SAN, etc. NOT NAS).
Actually, you can cluster to a shared resource on a NAS. We have a NetApp
880 Filer and have been running SQL clusters quite nicely using Snapdrive
over Gig fiber for a couple of years using the procedures outlined at this
NetApp link:
http://www.netapp.com/tech_library/3248.html#2.
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:u%23O18vplEHA.3712@.TK2MSFTNGP15.phx.gbl...
> Active/Active and Active/Passive are obsolete concepts from SQL 7.0 when
> each SQL instance had a primary owner host node and a partner owner node.
> Under SQL 2000, all nodes are interchangable in a cluster. Some people
> mistakenly use teh terms to refer to single and multiple instance SQL
> clustering.
> Clustering involves a single data store on a 'shared' storage system (SCSI
> Array, SAN, etc. NOT NAS). Shared is not exactly correct since the
> cluster
> software arbitrates ownership so that only one host node owns the
> resource(s) at a time. Therefore, you have to have a working MSCS cluster
> in order to create a SQL cluster.
> Replication is not a good technique for creating a warm standby server.
> Replication has VERY limited ability to handle schema changes without a
> total resynch. It also does not replicate stored procedures or views.
>
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Karl Albrecht" <karlman@.pacbell.net> wrote in message
> news:O8QGJUplEHA.2340@.TK2MSFTNGP11.phx.gbl...
>

Clustered sql binn directory

Hi I do not have any experience of clustered servers
I have some software that needs some dll's placed into the directory below
which an extended strored proc uses to verify and run internal code.
C:\Program Files\Microsoft SQL Server\MSSQL<instancename>\Binn
this works perfect on single Sql server instances or named instances
If two servers are set as clustered and both are active. Does anyone know
how the structure of the sql binn directories are laid out. If I have an
extened proc that checks a dll in the sql instance binn directory where or
how is this named for clustered servers.
Or has anyone used extended procs with clustered servers?
thanks for any advice
Sammy
I use extended stored procedures with clustered SQL regularly. The SQL
clustered and non-clustered binary structure is exactly the same. They only
difference is the structure is paralleled on each node. Therefore, you have
to place the appropriate DLL in the same directory on all the nodes.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:EF42B68E-1D9B-4D2C-91D7-74273953E5CD@.microsoft.com...
> Hi I do not have any experience of clustered servers
> I have some software that needs some dll's placed into the directory
> below
> which an extended strored proc uses to verify and run internal code.
> C:\Program Files\Microsoft SQL Server\MSSQL<instancename>\Binn
> this works perfect on single Sql server instances or named instances
>
> If two servers are set as clustered and both are active. Does anyone know
> how the structure of the sql binn directories are laid out. If I have an
> extened proc that checks a dll in the sql instance binn directory where or
> how is this named for clustered servers.
> Or has anyone used extended procs with clustered servers?
> thanks for any advice
> Sammy
>
>
>
>

clustered ix and non on same field

I have a SS 2000 database that was created from a purchased, commercial
software product. I.e. I had no input into the design. This is your typical
OLTP and DSS database. I notice on many tables that there is a clustered
index and non-clustered index on the same column. I feel like this is a
mistake and I should delete every non-clustered index that has a clustered
index, assuming the clustered index is the primary key. Is it ever
advantageous to have both a CIX and nonCIX on the same field so that the
optimizer can use one for range/sorts (CIX) and the other for single record
lookup (nonCIX)?
TIA - nickThe only case I can think of is when the NC covers the query and you have low selectivity. SQL
Server can now read the NS index pages only which will be fewer pages than the CL pages for the
corresponding data-range over the column. However, I suspect that this is the PK and possibly even
some surrogate key, so an NC index on this column will probably not cover that many queries.
I've seen stranger things with commercial software databases... :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:31AC2451-7141-4573-8E4D-C3E87FCA9623@.microsoft.com...
>I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typical
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single record
> lookup (nonCIX)?
> TIA - nick|||In addition to Tibor's reply: such an index can also help if there is no
other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
I would consider it a bad practice to blindly add a nonclustered index
of the clustered index to each and every table. Only if a query like the
mentioned examples are very important (above average) one could
incidentally consider such an extra index. Otherwise, it is mostly
wasting space and adding overhead for index maintenance.
Gert-Jan
Nick wrote:
> I have a SS 2000 database that was created from a purchased, commercial
> software product. I.e. I had no input into the design. This is your typical
> OLTP and DSS database. I notice on many tables that there is a clustered
> index and non-clustered index on the same column. I feel like this is a
> mistake and I should delete every non-clustered index that has a clustered
> index, assuming the clustered index is the primary key. Is it ever
> advantageous to have both a CIX and nonCIX on the same field so that the
> optimizer can use one for range/sorts (CIX) and the other for single record
> lookup (nonCIX)?
> TIA - nick|||> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
which is a case where the index covers the query, but possibly an important example worth pointing
out explicitly. :-)
Also, it can be worth to mention that any NC index will cover above query, and probably SQL Server
will pick the NC index over the most narrow column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467AEDBF.806A445F@.toomuchspamalready.nl...
> In addition to Tibor's reply: such an index can also help if there is no
> other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
> I would consider it a bad practice to blindly add a nonclustered index
> of the clustered index to each and every table. Only if a query like the
> mentioned examples are very important (above average) one could
> incidentally consider such an extra index. Otherwise, it is mostly
> wasting space and adding overhead for index maintenance.
> Gert-Jan
>
> Nick wrote:
>> I have a SS 2000 database that was created from a purchased, commercial
>> software product. I.e. I had no input into the design. This is your typical
>> OLTP and DSS database. I notice on many tables that there is a clustered
>> index and non-clustered index on the same column. I feel like this is a
>> mistake and I should delete every non-clustered index that has a clustered
>> index, assuming the clustered index is the primary key. Is it ever
>> advantageous to have both a CIX and nonCIX on the same field so that the
>> optimizer can use one for range/sorts (CIX) and the other for single record
>> lookup (nonCIX)?
>> TIA - nick|||Tibor Karaszi wrote:
> > In addition to Tibor's reply: such an index can also help if there is no
> > other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
> which is a case where the index covers the query, but possibly an important example worth pointing
> out explicitly. :-)
> Also, it can be worth to mention that any NC index will cover above query, and probably SQL Server
> will pick the NC index over the most narrow column.
Yes, and this is a somewhat interesting, because the NC index that
matches the clustered index will (by definition) always be the
narrowest, because the clustered key is part of any nonclustered index
key. So for SELECT COUNT(*) FROM the_table, this type of index is
perfect (however small the difference with another NC index).
Gert-Jan|||> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest,
How true, Gert-Jan.
Good catch, an easy thing to overlook (just as I did). :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:467BF4AB.FB8F8F07@.toomuchspamalready.nl...
> Tibor Karaszi wrote:
>> > In addition to Tibor's reply: such an index can also help if there is no
>> > other nonclustered index and there is a SELECT COUNT(*) FROM the_table.
>> which is a case where the index covers the query, but possibly an important example worth
>> pointing
>> out explicitly. :-)
>> Also, it can be worth to mention that any NC index will cover above query, and probably SQL
>> Server
>> will pick the NC index over the most narrow column.
> Yes, and this is a somewhat interesting, because the NC index that
> matches the clustered index will (by definition) always be the
> narrowest, because the clustered key is part of any nonclustered index
> key. So for SELECT COUNT(*) FROM the_table, this type of index is
> perfect (however small the difference with another NC index).
> Gert-Jan