Hello,
We're are in the basic design process for a cluster. We have some confusion
as to whether SQL Server clustering supports a share nothing scenario - that
is where the clustered machines have their own storage and do not share
storage.
We're trying to find out if this is supported, and if it is, what are the
downsides to this approach versus using a shared storage device.
Any help or comments greatly appreciated.
Thank you,
Greg Hayes
Funny you should use that term. Microsoft uses the shared nothing model,
only it means that the controlling node does not share with any other
node(s). One node owns a resource at a time.
You can get what you need from Majority Node Set (MNS) read all about it
here:
http://www.microsoft.com/windowsserv...lustergeo.mspx
or view an excellent webcast here:
http://support.microsoft.com/default...b;en-us;838612
lastly Tech Net has some great information here:
http://www.microsoft.com/technet/pro...g/majnode.mspx
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Greg Hayes" <grandgousier@.verizon.net> wrote in message
news:eYUZj8qaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> Hello,
> We're are in the basic design process for a cluster. We have some
confusion
> as to whether SQL Server clustering supports a share nothing scenario -
that
> is where the clustered machines have their own storage and do not share
> storage.
> We're trying to find out if this is supported, and if it is, what are the
> downsides to this approach versus using a shared storage device.
> Any help or comments greatly appreciated.
> Thank you,
> Greg Hayes
>
|||But as far as I know, SQL 2000 does not support MNS.
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
Specialist SQL Server Solutions and Consulting
MVP Program: http://www.microsoft.com/mvp
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:ewrY7QsaEHA.1732@.TK2MSFTNGP09.phx.gbl...
> Funny you should use that term. Microsoft uses the shared nothing model,
> only it means that the controlling node does not share with any other
> node(s). One node owns a resource at a time.
> You can get what you need from Majority Node Set (MNS) read all about it
> here:
>
http://www.microsoft.com/windowsserv...lustergeo.mspx
> or view an excellent webcast here:
> http://support.microsoft.com/default...b;en-us;838612
> lastly Tech Net has some great information here:
>
http://www.microsoft.com/technet/pro...g/majnode.mspx[vbcol=seagreen]
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> "Greg Hayes" <grandgousier@.verizon.net> wrote in message
> news:eYUZj8qaEHA.3804@.TK2MSFTNGP10.phx.gbl...
> confusion
> that
the
>
|||That is true. So third party time it is ;)
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uci3tezaEHA.3752@.TK2MSFTNGP12.phx.gbl...
> But as far as I know, SQL 2000 does not support MNS.
> --
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Epprecht Consulting (PTY) LTD
> Johannesburg, South Africa
> Mobile: +27-82-552-0268
> IM: mike@.epprecht.net
> Specialist SQL Server Solutions and Consulting
> MVP Program: http://www.microsoft.com/mvp
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:ewrY7QsaEHA.1732@.TK2MSFTNGP09.phx.gbl...
>
http://www.microsoft.com/windowsserv...lustergeo.mspx
>
http://www.microsoft.com/technet/pro...g/majnode.mspx[vbcol=seagreen]
scenario -[vbcol=seagreen]
share
> the
>
Showing posts with label share. Show all posts
Showing posts with label share. Show all posts
Friday, February 24, 2012
Clustering SQL servers
Does anyone have any information on how to link 2 separate computers with
SQL server to share the load of 1 database? [I think this is called a
cluster?]
In other words, I want to have 1 front-end application machine and 2
back-end database machines that share 1 database.
Thanks.
It might at first seem similar, but this is not really clustering;
clustering involves two or more SQL Server nodes and a shared disk array but
no load balancing. EG in an active/passive configuration the load is not
shared as, at any one time, only one SQL Server machine is in control of the
shared resource which holds the database file. For load balancing in SQL
Server 2000, there is a different technology called 'distributed partitioned
views', (in Enterprise Edition) - plenty of details in BOL.
Also, if you have a requirement to distribute processing where there is a
significant reporting element, you might want to look at having a reporting
server and using log-shipping to transfer changes from the production system
to the reporting server. In this case there is some latency involved and
your application will need to distinguish between servers for different
queries (using the 2 different connection strings).
HTH,
Paul Ibison
SQL server to share the load of 1 database? [I think this is called a
cluster?]
In other words, I want to have 1 front-end application machine and 2
back-end database machines that share 1 database.
Thanks.
It might at first seem similar, but this is not really clustering;
clustering involves two or more SQL Server nodes and a shared disk array but
no load balancing. EG in an active/passive configuration the load is not
shared as, at any one time, only one SQL Server machine is in control of the
shared resource which holds the database file. For load balancing in SQL
Server 2000, there is a different technology called 'distributed partitioned
views', (in Enterprise Edition) - plenty of details in BOL.
Also, if you have a requirement to distribute processing where there is a
significant reporting element, you might want to look at having a reporting
server and using log-shipping to transfer changes from the production system
to the reporting server. In this case there is some latency involved and
your application will need to distinguish between servers for different
queries (using the 2 different connection strings).
HTH,
Paul Ibison
Clustering SQL servers
Does anyone have any information on how to link 2 separate computers with
SQL server to share the load of 1 database? [I think this is called a
cluster']
In other words, I want to have 1 front-end application machine and 2
back-end database machines that share 1 database.
Thanks.It might at first seem similar, but this is not really clustering;
clustering involves two or more SQL Server nodes and a shared disk array but
no load balancing. EG in an active/passive configuration the load is not
shared as, at any one time, only one SQL Server machine is in control of the
shared resource which holds the database file. For load balancing in SQL
Server 2000, there is a different technology called 'distributed partitioned
views', (in Enterprise Edition) - plenty of details in BOL.
Also, if you have a requirement to distribute processing where there is a
significant reporting element, you might want to look at having a reporting
server and using log-shipping to transfer changes from the production system
to the reporting server. In this case there is some latency involved and
your application will need to distinguish between servers for different
queries (using the 2 different connection strings).
HTH,
Paul Ibison
SQL server to share the load of 1 database? [I think this is called a
cluster']
In other words, I want to have 1 front-end application machine and 2
back-end database machines that share 1 database.
Thanks.It might at first seem similar, but this is not really clustering;
clustering involves two or more SQL Server nodes and a shared disk array but
no load balancing. EG in an active/passive configuration the load is not
shared as, at any one time, only one SQL Server machine is in control of the
shared resource which holds the database file. For load balancing in SQL
Server 2000, there is a different technology called 'distributed partitioned
views', (in Enterprise Edition) - plenty of details in BOL.
Also, if you have a requirement to distribute processing where there is a
significant reporting element, you might want to look at having a reporting
server and using log-shipping to transfer changes from the production system
to the reporting server. In this case there is some latency involved and
your application will need to distinguish between servers for different
queries (using the 2 different connection strings).
HTH,
Paul Ibison
Clustering SQL servers
Does anyone have any information on how to link 2 separate computers with
SQL server to share the load of 1 database? [I think this is called a
cluster']
In other words, I want to have 1 front-end application machine and 2
back-end database machines that share 1 database.
Thanks.It might at first seem similar, but this is not really clustering;
clustering involves two or more SQL Server nodes and a shared disk array but
no load balancing. EG in an active/passive configuration the load is not
shared as, at any one time, only one SQL Server machine is in control of the
shared resource which holds the database file. For load balancing in SQL
Server 2000, there is a different technology called 'distributed partitioned
views', (in Enterprise Edition) - plenty of details in BOL.
Also, if you have a requirement to distribute processing where there is a
significant reporting element, you might want to look at having a reporting
server and using log-shipping to transfer changes from the production system
to the reporting server. In this case there is some latency involved and
your application will need to distinguish between servers for different
queries (using the 2 different connection strings).
HTH,
Paul Ibison
SQL server to share the load of 1 database? [I think this is called a
cluster']
In other words, I want to have 1 front-end application machine and 2
back-end database machines that share 1 database.
Thanks.It might at first seem similar, but this is not really clustering;
clustering involves two or more SQL Server nodes and a shared disk array but
no load balancing. EG in an active/passive configuration the load is not
shared as, at any one time, only one SQL Server machine is in control of the
shared resource which holds the database file. For load balancing in SQL
Server 2000, there is a different technology called 'distributed partitioned
views', (in Enterprise Edition) - plenty of details in BOL.
Also, if you have a requirement to distribute processing where there is a
significant reporting element, you might want to look at having a reporting
server and using log-shipping to transfer changes from the production system
to the reporting server. In this case there is some latency involved and
your application will need to distinguish between servers for different
queries (using the 2 different connection strings).
HTH,
Paul Ibison
Friday, February 10, 2012
clustered index
I have question about clustered index.
If you look at share point portal server database you will see, that primary
key clustered index is composed of one unique identifier column and 2
columns of nvarchar(255) data type.
Isn't that too long clustered index?
I thought that clustered indexes should be as narrow as possible? For
example, identity column is perfect for that.
So, I would create identity clustered index and put other columns in
nonclustered index if needed.
Can someone explain me why is so extensive clustered index there?
Second question:
I always thought that every table should have primary key, but I found a lot
that there are tables without it.
Why?
I found a table, which had one unique index, 3 not unique indexes and no one
of them was clustered.
So table without primary key and without any clustered index. What could be
the benefit of that?
What is the difference if you have primary key (which by default creates
unique clustered index on columns of PK) or just unique clustered index
without primary key?
I think that this is pretty the same.
Thank you for some explanation about that,
SimonI wouldn't use the SharePoint database as an example of how to best do
things in your db. First off I don't agree with some of their techniques and
you always have to understand what they are using the indexes for and how
they use the data. This is different for each application. Usually wide
clustered indexes are discouraged but that does not mean they can not be
used effectively in some cases. And in general every table should have a
clustered index but there will always be exceptions. The point is to not
look at someother application as an example of what you should do without
understanding why they did it. Try to understand what a clustered index is
best for and apply that to your schema the way that makes the most sense.
This might help:
http://www.sql-server-performance.c...red_indexes.asp
As for PK vs. unique you should always have a PK constraint defined. The
main difference physically between the two is that a PK can not have any
nulls where as a Unique can have one.
Andrew J. Kelly SQL MVP
"simon" <simon.zupan@.iware.si> wrote in message
news:fJ_Sf.996$oj5.378456@.news.siol.net...
>I have question about clustered index.
> If you look at share point portal server database you will see, that
> primary key clustered index is composed of one unique identifier column
> and 2 columns of nvarchar(255) data type.
> Isn't that too long clustered index?
> I thought that clustered indexes should be as narrow as possible? For
> example, identity column is perfect for that.
> So, I would create identity clustered index and put other columns in
> nonclustered index if needed.
> Can someone explain me why is so extensive clustered index there?
> Second question:
> I always thought that every table should have primary key, but I found a
> lot that there are tables without it.
> Why?
> I found a table, which had one unique index, 3 not unique indexes and no
> one of them was clustered.
> So table without primary key and without any clustered index. What could
> be the benefit of that?
> What is the difference if you have primary key (which by default creates
> unique clustered index on columns of PK) or just unique clustered index
> without primary key?
> I think that this is pretty the same.
> Thank you for some explanation about that,
> Simon
>|||If there's anyone that knows differently, please feel free to correct
me.
DDL:
CREATE TABLE Transactions
(
TransactionClass NVARCHAR(255)
, TransactionDate DATETIME NOT NULL
, ...<more columns>
)
CREATE CLUSTERED INDEX IXC_TransactionClass_TransactionDate ON
Transactions(TransactionClass, TransactionDate)
In certain cases, it may be beneficial to have a wide clustered index.
Lets say you have a table with billions of rows. Each row represents
data specific to a transaction. (notice that I'm carefully avoiding
using the term "record" here as whether the data included in the row
constitutes a complete record is irrelevant for the example).
Continuing, lets say there are hundreds of thousands of distinct values
in the TransactionClass column and you frequently look up data based on
this column. And rarely, if ever, research based on data in any other
column, it may be beneficial to cluster based on the TransactionClass
column. (other readers, please correct me here if I'm wrong) The width
of your clustered index only becomes a factor when you have other
nonclustered indexes defined, as the clustered index is included as the
row pointer in each row of your nonclustered index. In short, if you
have the following schema:
CREATE TABLE t
(
a DATATYPE_A
, b DATATYPE_B
, c DATATYPE_C
, d DATATYPE_D
)
If you cluster on a and DATATYPE_A is long, but you have no
nonclustered indexes defined, there is little implication. However,
when you add a nonclustered index, say on b and c, your index can be
thought of as a table with the following schema:
CREATE TABLE index_t_b_c
(
b DATATYPE_B
, c DATATYPE_C
, a DATATYPE_A
)
as [a] must be included with your nonclustered index. The implications
here are as follows:
>From my understanding, when SQL Server fetches data from disk, it
fetches one 64KB extent at a time. When your rows are narrower, the
fetch will return more rows than if they're wider. If your clustered
key is 90 bytes and your index row is 100 bytes (lets forget about
other data overhead for the time being), then 90% of your index row is
the clustered key. If your clustered key is 4 bytes (INT), then only
4/14, or about 30% of your index is your clustered key, meaning that
with each physical fetch from disk, seven times as many index entries
may be fetched, dramatically increasing performance. When nonclustered
indexes don't exist on your table, this doesn't matter.
-Alan
If you look at share point portal server database you will see, that primary
key clustered index is composed of one unique identifier column and 2
columns of nvarchar(255) data type.
Isn't that too long clustered index?
I thought that clustered indexes should be as narrow as possible? For
example, identity column is perfect for that.
So, I would create identity clustered index and put other columns in
nonclustered index if needed.
Can someone explain me why is so extensive clustered index there?
Second question:
I always thought that every table should have primary key, but I found a lot
that there are tables without it.
Why?
I found a table, which had one unique index, 3 not unique indexes and no one
of them was clustered.
So table without primary key and without any clustered index. What could be
the benefit of that?
What is the difference if you have primary key (which by default creates
unique clustered index on columns of PK) or just unique clustered index
without primary key?
I think that this is pretty the same.
Thank you for some explanation about that,
SimonI wouldn't use the SharePoint database as an example of how to best do
things in your db. First off I don't agree with some of their techniques and
you always have to understand what they are using the indexes for and how
they use the data. This is different for each application. Usually wide
clustered indexes are discouraged but that does not mean they can not be
used effectively in some cases. And in general every table should have a
clustered index but there will always be exceptions. The point is to not
look at someother application as an example of what you should do without
understanding why they did it. Try to understand what a clustered index is
best for and apply that to your schema the way that makes the most sense.
This might help:
http://www.sql-server-performance.c...red_indexes.asp
As for PK vs. unique you should always have a PK constraint defined. The
main difference physically between the two is that a PK can not have any
nulls where as a Unique can have one.
Andrew J. Kelly SQL MVP
"simon" <simon.zupan@.iware.si> wrote in message
news:fJ_Sf.996$oj5.378456@.news.siol.net...
>I have question about clustered index.
> If you look at share point portal server database you will see, that
> primary key clustered index is composed of one unique identifier column
> and 2 columns of nvarchar(255) data type.
> Isn't that too long clustered index?
> I thought that clustered indexes should be as narrow as possible? For
> example, identity column is perfect for that.
> So, I would create identity clustered index and put other columns in
> nonclustered index if needed.
> Can someone explain me why is so extensive clustered index there?
> Second question:
> I always thought that every table should have primary key, but I found a
> lot that there are tables without it.
> Why?
> I found a table, which had one unique index, 3 not unique indexes and no
> one of them was clustered.
> So table without primary key and without any clustered index. What could
> be the benefit of that?
> What is the difference if you have primary key (which by default creates
> unique clustered index on columns of PK) or just unique clustered index
> without primary key?
> I think that this is pretty the same.
> Thank you for some explanation about that,
> Simon
>|||If there's anyone that knows differently, please feel free to correct
me.
DDL:
CREATE TABLE Transactions
(
TransactionClass NVARCHAR(255)
, TransactionDate DATETIME NOT NULL
, ...<more columns>
)
CREATE CLUSTERED INDEX IXC_TransactionClass_TransactionDate ON
Transactions(TransactionClass, TransactionDate)
In certain cases, it may be beneficial to have a wide clustered index.
Lets say you have a table with billions of rows. Each row represents
data specific to a transaction. (notice that I'm carefully avoiding
using the term "record" here as whether the data included in the row
constitutes a complete record is irrelevant for the example).
Continuing, lets say there are hundreds of thousands of distinct values
in the TransactionClass column and you frequently look up data based on
this column. And rarely, if ever, research based on data in any other
column, it may be beneficial to cluster based on the TransactionClass
column. (other readers, please correct me here if I'm wrong) The width
of your clustered index only becomes a factor when you have other
nonclustered indexes defined, as the clustered index is included as the
row pointer in each row of your nonclustered index. In short, if you
have the following schema:
CREATE TABLE t
(
a DATATYPE_A
, b DATATYPE_B
, c DATATYPE_C
, d DATATYPE_D
)
If you cluster on a and DATATYPE_A is long, but you have no
nonclustered indexes defined, there is little implication. However,
when you add a nonclustered index, say on b and c, your index can be
thought of as a table with the following schema:
CREATE TABLE index_t_b_c
(
b DATATYPE_B
, c DATATYPE_C
, a DATATYPE_A
)
as [a] must be included with your nonclustered index. The implications
here are as follows:
>From my understanding, when SQL Server fetches data from disk, it
fetches one 64KB extent at a time. When your rows are narrower, the
fetch will return more rows than if they're wider. If your clustered
key is 90 bytes and your index row is 100 bytes (lets forget about
other data overhead for the time being), then 90% of your index row is
the clustered key. If your clustered key is 4 bytes (INT), then only
4/14, or about 30% of your index is your clustered key, meaning that
with each physical fetch from disk, seven times as many index entries
may be fetched, dramatically increasing performance. When nonclustered
indexes don't exist on your table, this doesn't matter.
-Alan
Subscribe to:
Posts (Atom)