Clustered DB Server: clustered

Showing posts with label clustered. Show all posts

Thursday, March 22, 2012

Collation and Win2K3 Clustered Environment.

This past weekend I was challenged a little in resolving what I hoped was a
simple collation issue. We are running Win2K3 Ent. Clustering Services, SQL
Server 2000 Enterprise, b.8.00.760.
On Friday, 2.11, a user migrated an application to this new clustered setup
and immediately received the infamous 446 collation error.
I spent most of Friday evening and all day Saturday reviewing support topics
and news groups. I found that there was 1 issue that could be affecting us -
the build of SQL Server for Win2K3 Ent. Clustering is subtly different than
for Win2KAS Clustering - and all of my research came back to one thing: Run
rebuildm.exe and set the collation of this instance to be the same as the
existing development/production environment. The default setup on the cluster
was different than the previous production setup, as well as different than
the current development environment.
So, I backed everyone's database up, then the master objects, shut the
instance down, executed rebuild and set the collation to what I needed it to
be.
When I brought this user's database online - the same exact error occurs.
Prior to doing this, here is what we were faced with:
Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
The new clustered SQL=SQL Server Enterprise, w/Collate=Latin1_General_CI_AI.
The database/application in question, at the procedure where we receive the
collation error, basically calls a function that creates a temp table, then
another that pulls data for a report - however, the data is collected as a
JOIN on this tempdb temp object and a series of Views the programmer
previously setup. These views in turn hit various other static tables as well.
We were thinking that because of the collation difference on the new Win2K3
setup, as well as Collation/Locale settings differences with Win2K3
Clustering, we should at a minimum change the instance to match the
SQL_Latin1, etc. collation of development. This however, has not solved the
problem.
At this juncture, the Win2K3 Clustered server has the collation
SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
their current production environment, which is 2KAS nonclustered) is
SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now is
the accent sensitivity - but this should not be the issue.
If anyone else out there has any feedback, I'd be grateful for your time.
Thanks...
mhamilton"AT"nusoftsolutions"DOT"com
If the collation names are different you'll get the collation conflict
error -- a difference in accent sensitivity is sufficient to expose the
problem.
One option is rebuild master in dev or test (again) so that the two servers
have the same collation. You almost matched the collation last time, but
the different accent sensitivity setting is also critical.
Another option is to make sure that the T-SQL is written in a way that
makes it immune to the problem. For the scenario you describe you could do
this by making sure that your temp tables inherit the collation of the
current user database, not the collation of tempdb. A "COLLATE
database_default" clause will accomplish this. For example, when creating
the temp table in the stored proc:
CREATE TABLE #temp1 (
c1 int,
c2 varchar (30) COLLATE database_default,
c3 char(12) COLLATE database_default,
)
HTH,
Bart
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Collation and Win2K3 Clustered Environment.
| thread-index: AcUUNUWE2BoB7xCnSeWo8pOsRpOhYg==
| X-WBNR-Posting-Host: 12.227.130.93
| From: "=?Utf-8?B?TWlrZUg=?=" <MikeH@.discussions.microsoft.com>
| Subject: Collation and Win2K3 Clustered Environment.
| Date: Wed, 16 Feb 2005 06:39:08 -0800
| Lines: 51
| Message-ID: <87A19939-8444-4CEC-BBB4-ED092DFFD4D7@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.clustering
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFT NGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.clustering:3114
| X-Tomcat-NG: microsoft.public.sqlserver.clustering
|
| This past weekend I was challenged a little in resolving what I hoped was
a
| simple collation issue. We are running Win2K3 Ent. Clustering Services,
SQL
| Server 2000 Enterprise, b.8.00.760.
|
| On Friday, 2.11, a user migrated an application to this new clustered
setup
| and immediately received the infamous 446 collation error.
|
| I spent most of Friday evening and all day Saturday reviewing support
topics
| and news groups. I found that there was 1 issue that could be affecting
us -
| the build of SQL Server for Win2K3 Ent. Clustering is subtly different
than
| for Win2KAS Clustering - and all of my research came back to one thing:
Run
| rebuildm.exe and set the collation of this instance to be the same as the
| existing development/production environment. The default setup on the
cluster
| was different than the previous production setup, as well as different
than
| the current development environment.
|
| So, I backed everyone's database up, then the master objects, shut the
| instance down, executed rebuild and set the collation to what I needed it
to
| be.
|
| When I brought this user's database online - the same exact error occurs.
|
| Prior to doing this, here is what we were faced with:
| Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
| Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
|
| The new clustered SQL=SQL Server Enterprise,
w/Collate=Latin1_General_CI_AI.
|
| The database/application in question, at the procedure where we receive
the
| collation error, basically calls a function that creates a temp table,
then
| another that pulls data for a report - however, the data is collected as
a
| JOIN on this tempdb temp object and a series of Views the programmer
| previously setup. These views in turn hit various other static tables as
well.
|
| We were thinking that because of the collation difference on the new
Win2K3
| setup, as well as Collation/Locale settings differences with Win2K3
| Clustering, we should at a minimum change the instance to match the
| SQL_Latin1, etc. collation of development. This however, has not solved
the
| problem.
|
| At this juncture, the Win2K3 Clustered server has the collation
| SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
| their current production environment, which is 2KAS nonclustered) is
| SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now
is
| the accent sensitivity - but this should not be the issue.
|
| If anyone else out there has any feedback, I'd be grateful for your time.
|
| Thanks...
|
| mhamilton"AT"nusoftsolutions"DOT"com
|
|||Bart, thanks for getting back to me.
I must confess, moving from 2KAS Clustering to 2K3 Clustering is NOT fun.
Simply because of the nuances I am finding.
Foremost, I did get the application working, and thus far I have not
experienced the infamous 446 collation error.
However...
This particular cluster is only running 6 instances of SQL Server. Each
instance has the Full Text Search engine/component installed - yet only 2 of
the instances are actually using it - and the problem I ran into is on 1 of
these 2 instances.
Specifically, this instance has the full text search active.
Now...
IF - and I say this 'loudly' - IF the full text server was 'offline' when I
did the rebuild - then restarted the instance and tested the app - the app
failed. Most interesting...
It took me a couple days to see what was happening. So... I did the same
with the full text 'online' and voila!!! I have joy... The application worked
fine.
Now... I have a few questions, but I'm sure you're not going to be able to
answer them anymore than I can. This notwithstanding, I find it interesting
that rebuilding the instance - with the SQL Full Text 'offline' when I do it
-could actually keep it from working. Yes, software is software - but this is
quirky at best.
Anyway... Thank you for responding... I will be in contact with PSS and my
manager on this issue, and if you have any other questions regarding the
setup, please feel free to contact me.
"Bart Duncan [MSFT]" wrote:

> If the collation names are different you'll get the collation conflict
> error -- a difference in accent sensitivity is sufficient to expose the
> problem.
> One option is rebuild master in dev or test (again) so that the two servers
> have the same collation. You almost matched the collation last time, but
> the different accent sensitivity setting is also critical.
> Another option is to make sure that the T-SQL is written in a way that
> makes it immune to the problem. For the scenario you describe you could do
> this by making sure that your temp tables inherit the collation of the
> current user database, not the collation of tempdb. A "COLLATE
> database_default" clause will accomplish this. For example, when creating
> the temp table in the stored proc:
> CREATE TABLE #temp1 (
> c1 int,
> c2 varchar (30) COLLATE database_default,
> c3 char(12) COLLATE database_default,
> )
> HTH,
> Bart
> --
> Bart Duncan
> Microsoft SQL Server Support
> Please reply to the newsgroup only - thanks.
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> --
> | Thread-Topic: Collation and Win2K3 Clustered Environment.
> | thread-index: AcUUNUWE2BoB7xCnSeWo8pOsRpOhYg==
> | X-WBNR-Posting-Host: 12.227.130.93
> | From: "=?Utf-8?B?TWlrZUg=?=" <MikeH@.discussions.microsoft.com>
> | Subject: Collation and Win2K3 Clustered Environment.
> | Date: Wed, 16 Feb 2005 06:39:08 -0800
> | Lines: 51
> | Message-ID: <87A19939-8444-4CEC-BBB4-ED092DFFD4D7@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.clustering
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: TK2MSFTNGXA01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFT NGXA03.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.clustering:3114
> | X-Tomcat-NG: microsoft.public.sqlserver.clustering
> |
> | This past weekend I was challenged a little in resolving what I hoped was
> a
> | simple collation issue. We are running Win2K3 Ent. Clustering Services,
> SQL
> | Server 2000 Enterprise, b.8.00.760.
> |
> | On Friday, 2.11, a user migrated an application to this new clustered
> setup
> | and immediately received the infamous 446 collation error.
> |
> | I spent most of Friday evening and all day Saturday reviewing support
> topics
> | and news groups. I found that there was 1 issue that could be affecting
> us -
> | the build of SQL Server for Win2K3 Ent. Clustering is subtly different
> than
> | for Win2KAS Clustering - and all of my research came back to one thing:
> Run
> | rebuildm.exe and set the collation of this instance to be the same as the
> | existing development/production environment. The default setup on the
> cluster
> | was different than the previous production setup, as well as different
> than
> | the current development environment.
> |
> | So, I backed everyone's database up, then the master objects, shut the
> | instance down, executed rebuild and set the collation to what I needed it
> to
> | be.
> |
> | When I brought this user's database online - the same exact error occurs.
> |
> | Prior to doing this, here is what we were faced with:
> | Development / Previous Production: Server=Win2K, SQL=SQL Server 2000
> | Enterprise w/Collate=SQL_Latin1_General_CP1_CI_AS.
> |
> | The new clustered SQL=SQL Server Enterprise,
> w/Collate=Latin1_General_CI_AI.
> |
> | The database/application in question, at the procedure where we receive
> the
> | collation error, basically calls a function that creates a temp table,
> then
> | another that pulls data for a report - however, the data is collected as
> a
> | JOIN on this tempdb temp object and a series of Views the programmer
> | previously setup. These views in turn hit various other static tables as
> well.
> |
> | We were thinking that because of the collation difference on the new
> Win2K3
> | setup, as well as Collation/Locale settings differences with Win2K3
> | Clustering, we should at a minimum change the instance to match the
> | SQL_Latin1, etc. collation of development. This however, has not solved
> the
> | problem.
> |
> | At this juncture, the Win2K3 Clustered server has the collation
> | SQL_Latin1_General_CP1_CI_AI, and the development environment (as well as
> | their current production environment, which is 2KAS nonclustered) is
> | SQL_Latin1_General_CP1_CI_AS... The only difference in the two right now
> is
> | the accent sensitivity - but this should not be the issue.
> |
> | If anyone else out there has any feedback, I'd be grateful for your time.
> |
> | Thanks...
> |
> | mhamilton"AT"nusoftsolutions"DOT"com
> |
>

Sunday, March 11, 2012

Code Review

I need to get information on all the unique constraints for a table. I
needed info on whether a constraint is clustered or not, what filegroup
it resides in, columns that comprise the unique index (in a comma
separated list) and it's name. So I wrote the code (see below).
However, SQL is not my first language, so I was looking to see whether a
seasoned SQL expert can recommend anyway to speed up this query. Or
maybe I should take change my approach?
SET NOCOUNT ON
create table #UniqueConstraints
(
ID int IDENTITY,
Name varchar(255),
IndexID int,
IsClustered bit NULL,
FileGroup varchar(255),
Columns varchar(255) NULL
)
create table #ColumnList
(
ID int IDENTITY,
Name varchar(255)
)
-- set to non-clustered by default
insert #UniqueConstraints(Name, IndexID, FileGroup, IsClustered)
SELECT I.name,I.indid,FILEGROUP_NAME(I.groupid) as filegroup, 0
FROM sysindexes I, sysconstraints c
WHERE I.id=OBJECT_ID('dbo.Customers') AND (((I.status & 0x800)=0x800) OR
((I.status & 0x1000)=0x1000))
and i.id = c.id
and (C.status & 0xf)=2
and OBJECT_NAME(C.constid) = I.name
ORDER BY I.indid
-- mark clustered indexees
update #UniqueConstraints
set IsClustered = 1
where IndexID = 1
-- now get columns
DECLARE @.min_ID int, @.indexID int, @.minColumn_ID int
DECLARE @.columnList varchar(255), @.columnName varchar(255), @.comma
varchar(1)
SELECT @.min_ID = min(ID) from #UniqueConstraints
WHILE @.min_ID IS NOT NULL BEGIN
-- get the index id
SELECT @.indexID = IndexID from #UniqueConstraints where ID = @.min_ID
-- freshen up this table
truncate table #ColumnList
-- get the list of involved columns
INSERT #ColumnList(Name)
SELECT COL_NAME(id, colid)
FROM sysindexkeys
WHERE id=OBJECT_ID('dbo.Customers') AND indid=@.indexID ORDER BY
keyno
-- convert the table contents into a comma separated list
SELECT @.minColumn_ID = min(ID) from #ColumnList
-- reinitialize
SET @.comma = ''
SET @.columnList = ''
WHILE @.minColumn_ID IS NOT NULL BEGIN
SELECT @.columnName = Name FROM #ColumnList WHERE ID = @.minColumn_ID
SET @.columnList = @.columnList + @.comma + @.columnName
set @.comma = ','
SELECT @.minColumn_ID = min(ID) FROM #ColumnList WHERE ID >
@.minColumn_ID
END
UPDATE #UniqueConstraints
SET Columns = @.columnList
WHERE ID = @.min_ID
-- get the next value from the table
SELECT @.min_ID = min(ID) FROM #UniqueConstraints WHERE ID > @.min_ID
END
select Name, IsClustered, FileGroup, Columns from #UniqueConstraints
drop table #UniqueConstraints
drop table #ColumnListThe first part of your requirement can be solved in an easier way, like
this:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
The comma separated list of the columns that are part of each unique
constraint is a little bit more complicated. I would use an UDF, like
this:
CREATE FUNCTION dbo.IndexColumns(@.id int, @.indid int)
RETURNS nvarchar(4000)
AS BEGIN
DECLARE @.List nvarchar(4000), @.ColName sysname
DECLARE Columns CURSOR LOCAL READ_ONLY FOR
SELECT COL_NAME(id,colid) FROM sysindexkeys
WHERE id=@.id AND indid=@.indid
ORDER BY keyno
OPEN Columns
WHILE 1=1 BEGIN
FETCH NEXT FROM Columns INTO @.ColName
IF @.@.FETCH_STATUS<>0 BREAK
SET @.List=ISNULL(@.List+',','')+@.ColName
END
CLOSE Columns
DEALLOCATE Columns
RETURN @.List
END
GO
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup,
dbo.IndexColumns(i.id,i.indid) AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
Another way would be to rely on the fact that there are a maximum of 16
columns for an index:
SELECT i.name,
INDEXPROPERTY(i.id,i.name,'IsClustered') as IsClustered,
g.groupname as FileGroup, (
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=1
)+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=2
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=3
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=4
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=5
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=6
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=7
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=8
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=9
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=10
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=11
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=12
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=13
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=14
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=15
),'')+ISNULL(','+(
SELECT COL_NAME(id,colid) FROM sysindexkeys k
WHERE k.id=i.id AND k.indid=i.indid AND k.keyno=16
),'') AS Columns
FROM sysobjects o
INNER JOIN sysindexes i ON i.id=o.parent_obj AND i.name=o.name
INNER JOIN sysfilegroups g ON i.groupid=g.groupid
WHERE o.xtype='UQ'
AND o.parent_obj=OBJECT_ID('dbo.Customers')
There is a small difference between the above solutions and your
solution: in the above solutions, the columns in the comma separated
list are presented in the order that is used when the constraint was
defined; in your solution, they are presented in the order in which
they appear in the table.
Razvan

Thursday, March 8, 2012

Code Review

Saturday, February 25, 2012

Clustrer DB engine and SAS?

Is it possible to create a SQL Server 2005 cluster and run SAS 2005 clustered on the same servers?

In particular I want to run a cluster of 6 servers as follows:-

* 3 active servers running mulitple instances of SQL Server 2005
* 1 active server running Reporting Servies and Analysis Services
* 2 passive servers for failover

Is this feasible and pratical?

Thanks
Ken
Yes, it is. SSAS will install into a cluster just like the Database Engine will. You generally don't cluster Reporting Services though since it simply relies on a database which would already be covered by clustering the SQL Server instance that is hosting the repository database.

ClusterService Log On Account and SQL2000

It is my understanding that this Active Directory account takes on the
role(s) of SQL's BuiltIn Administrator in a clustered environment.
Should the ClusterService Log On account password be reset in AD, without
reseting in the properties tab for the service on the physical nodes cluster
fail overs can still occur because the ClusterService has already been
started. BUT, what activity in SQL2000 could the AD account be doing on
behalf of the SQL BuiltIn Administrator account that would affect the
cluster and initiate a failover if it could not authenticate on the Domain?
Dave
That is not right. By default (God, I hate Microsoft's defaults), the
Cluster Service MUST BE a member of the Local Administrators group on each
cluster node, by which it gains access to the SQL Server installations
through the default BUILTIN\Administrators membership to the SQL Server
system admin default server role.
If you follow the security best practices, and you should, you will remove
the BUILTIN\Administrators group for the installation; however, the Cluster
Service account will still need access to run the resource monitor "Is
Alive" check, which, in the case for the SQL Server resource, is nothing but
the SELECT @.@.SERVERNAME query; thus, the Cluster Service account merely
needs access to the installation, but no special rights beyond this, as the
guest database user in the master database has permissions to execute this
query.
Sincerely,
Anthony Thomas

"David Currie" <decurrie@.rogers.com> wrote in message
news:%23aT3qmQBHHA.204@.TK2MSFTNGP04.phx.gbl...
> It is my understanding that this Active Directory account takes on the
> role(s) of SQL's BuiltIn Administrator in a clustered environment.
> Should the ClusterService Log On account password be reset in AD, without
> reseting in the properties tab for the service on the physical nodes
cluster
> fail overs can still occur because the ClusterService has already been
> started. BUT, what activity in SQL2000 could the AD account be doing on
> behalf of the SQL BuiltIn Administrator account that would affect the
> cluster and initiate a failover if it could not authenticate on the
Domain?
> Dave
>

Friday, February 24, 2012

Clustering with sql and third party database softwares

I have a clustered enviroment made of 2 win2k advanced servers
clustered in a network. Servers are running sql 2000 that hosts a third
party database X. Now X was working perfectly until these servers were
clustered. Users accessed X via a citrix client. Any idea as to why
accessing this third party database (X) became slower after being
placed in a clustered enviroment? Please feel free to come back with
any solutions.
Thanks
Hi
Did you have the exact same hardware setup before you clustered? A badly
implemented SAN can blow your performance.
Is each instance allocated the same amount of RAM as before clustering?
Run performance monitor and see what type of bottlenecks are shown. You did
not give much information about what is slower so I can't really pin point
the problem.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
<dafeuwotu@.yahoo.co.uk> wrote in message
news:1121623805.177041.48990@.f14g2000cwb.googlegro ups.com...
>I have a clustered enviroment made of 2 win2k advanced servers
> clustered in a network. Servers are running sql 2000 that hosts a third
> party database X. Now X was working perfectly until these servers were
> clustered. Users accessed X via a citrix client. Any idea as to why
> accessing this third party database (X) became slower after being
> placed in a clustered enviroment? Please feel free to come back with
> any solutions.
> Thanks
>

Clustering with NAS

Has anyone clustered SQL server 2000 using a NAS server (eg. Dell PowerVault
745N) as the shared storage? I know that SAN is recommended but costs are
vastly higher. Any reasons as to why it shouldn't be done would be helpful.
thanks - pete.
Hi
NAS is not fully supported. If you are going to build a cluster, make sure
that all the components are on the Windows Hardware Compatibility List for
Clustering. If not, don't expect support from Microsoft when things go bad.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pete Waters" <petewaters008@.yahoo.com> wrote in message
news:e7$cBT9XFHA.3300@.TK2MSFTNGP10.phx.gbl...
> Has anyone clustered SQL server 2000 using a NAS server (eg. Dell
> PowerVault 745N) as the shared storage? I know that SAN is recommended but
> costs are vastly higher. Any reasons as to why it shouldn't be done would
> be helpful.
> thanks - pete.
>
|||Not being supported by Microsoft is a good enough reason for me to not try
it...
There are cheaper failover options that are less hardware strict, such as a
product called double-take (www.nsisoftware.com no affiliation...)
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
www.experts-exchange.com - experts compete for points to answer your
questions
"Pete Waters" <petewaters008@.yahoo.com> wrote in message
news:e7$cBT9XFHA.3300@.TK2MSFTNGP10.phx.gbl...
> Has anyone clustered SQL server 2000 using a NAS server (eg. Dell
> PowerVault 745N) as the shared storage? I know that SAN is recommended but
> costs are vastly higher. Any reasons as to why it shouldn't be done would
> be helpful.
> thanks - pete.
>
|||Hi Mike,
Thanks for the response. Whilst I appreciate that clustering with NAS is not
supported by Microsoft, I'm interested to find out if people are actually
doing it. If they are and it seems to be reliable enough then i'd imagine it
would be worth the cost saving.
thanks - pete.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:OQXq%23c9XFHA.3464@.TK2MSFTNGP10.phx.gbl...
> Hi
> NAS is not fully supported. If you are going to build a cluster, make sure
> that all the components are on the Windows Hardware Compatibility List for
> Clustering. If not, don't expect support from Microsoft when things go
> bad.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Pete Waters" <petewaters008@.yahoo.com> wrote in message
> news:e7$cBT9XFHA.3300@.TK2MSFTNGP10.phx.gbl...
>
|||Hi Kevin,
I'm looking for an Active/Active cluster solution - not a passive failover
one.
thanks - pete.
"Kevin3NF" <KHill@.NopeIDontNeedNoSPAM3NF-inc.com> wrote in message
news:eBgCxe9XFHA.584@.TK2MSFTNGP15.phx.gbl...
> Not being supported by Microsoft is a good enough reason for me to not try
> it...
> There are cheaper failover options that are less hardware strict, such as
> a product called double-take (www.nsisoftware.com no affiliation...)
> --
> Kevin Hill
> President
> 3NF Consulting
> www.3nf-inc.com/NewsGroups.htm
> www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
> www.experts-exchange.com - experts compete for points to answer your
> questions
>
> "Pete Waters" <petewaters008@.yahoo.com> wrote in message
> news:e7$cBT9XFHA.3300@.TK2MSFTNGP10.phx.gbl...
>
|||Basic Question...what is more important to you? Data or Price?
If you don't mind loosing data in a supposedly "high availability" scenario,
then do it the cheap way.
Is your network and switches as reliable as 99.999%? If not, when they fail,
and you end up with data corruption.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pete Waters" <petewaters008@.yahoo.com> wrote in message
news:u6trGh9XFHA.3620@.TK2MSFTNGP09.phx.gbl...
> Hi Mike,
> Thanks for the response. Whilst I appreciate that clustering with NAS is
> not supported by Microsoft, I'm interested to find out if people are
> actually doing it. If they are and it seems to be reliable enough then i'd
> imagine it would be worth the cost saving.
> thanks - pete.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:OQXq%23c9XFHA.3464@.TK2MSFTNGP10.phx.gbl...
>
|||The internal network and switches are certainly 99.999% reliable (or at
least have been over the last year!). I'm more interested in peoples
practical experiences rather than the obvious value of data argument. I see
that Microsoft are beginning to support iSCSI - any experience with that?
thanks - pete.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:uSShGp9XFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Basic Question...what is more important to you? Data or Price?
> If you don't mind loosing data in a supposedly "high availability"
> scenario, then do it the cheap way.
> Is your network and switches as reliable as 99.999%? If not, when they
> fail, and you end up with data corruption.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Pete Waters" <petewaters008@.yahoo.com> wrote in message
> news:u6trGh9XFHA.3620@.TK2MSFTNGP09.phx.gbl...
>
|||Well, I've been at 3 customers who used non-HCL equipment, 2 of them used a
NAS. Well, they all had one option, revert to yesterday's good backup. One
was an investment bank that lost about US$ 50 million due to the failure.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pete Waters" <petewaters008@.yahoo.com> wrote in message
news:OjS0X39XFHA.3876@.TK2MSFTNGP10.phx.gbl...
> The internal network and switches are certainly 99.999% reliable (or at
> least have been over the last year!). I'm more interested in peoples
> practical experiences rather than the obvious value of data argument. I
> see that Microsoft are beginning to support iSCSI - any experience with
> that?
> thanks - pete.
>
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:uSShGp9XFHA.1240@.TK2MSFTNGP14.phx.gbl...
>
|||Hi Mike,
Ah - that's not good! That's the kind of info i'm after - real world cases -
thanks. Have you had any experience with using iSCSI in these setups?
pete.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:%23M0e0C%23XFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Well, I've been at 3 customers who used non-HCL equipment, 2 of them used
> a NAS. Well, they all had one option, revert to yesterday's good backup.
> One was an investment bank that lost about US$ 50 million due to the
> failure.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Pete Waters" <petewaters008@.yahoo.com> wrote in message
> news:OjS0X39XFHA.3876@.TK2MSFTNGP10.phx.gbl...
>
|||Hi
No. Not iSCSI. After the IT Directors got a good beating by the
shareholders, they all went SAN.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pete Waters" <petewaters008@.yahoo.com> wrote in message
news:OrxoIL%23XFHA.2756@.tk2msftngp13.phx.gbl...
> Hi Mike,
> Ah - that's not good! That's the kind of info i'm after - real world
> cases - thanks. Have you had any experience with using iSCSI in these
> setups?
> pete.
>
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:%23M0e0C%23XFHA.3320@.TK2MSFTNGP12.phx.gbl...
>

Clustering Question...

Say I have a current standalone SQL 2005 node that is SAN attached.
How can I get it clustered by adding a passive secondary node to it ? Can I
do so ? Or do I have to setup an entirely 2 new clustered set of servers and
move the database from the standalone server to it ?
I am trying to avoid buying 2 new servers and move the database across but
would like to just add another node to the existing standalone server and
get it clustered. If this is possible, how can I do it ?
Clusters have built from the ground up. You can't make it a cluster after
the install of SQL. Migrate to a new cluster, built from the ground up, that
is the best option.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://www.msmvps.com/clustering - Blog
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23PKJjfJHHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Say I have a current standalone SQL 2005 node that is SAN attached.
> How can I get it clustered by adding a passive secondary node to it ? Can
> I do so ? Or do I have to setup an entirely 2 new clustered set of servers
> and move the database from the standalone server to it ?
> I am trying to avoid buying 2 new servers and move the database across but
> would like to just add another node to the existing standalone server and
> get it clustered. If this is possible, how can I do it ?
>
>
|||Just to expand on what Rodney said, it is possible to create a cluster from
existing equipment, but it is neither desirable nor supported. The
potential disruption during the install and migration process will likely
cause some unplanned downtime, which defeats the entire purpose of a
cluster. It is best to start from a clean slate and migrate the data into
the new system.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:ODTKVxJHHHA.4804@.TK2MSFTNGP03.phx.gbl...
> Clusters have built from the ground up. You can't make it a cluster after
> the install of SQL. Migrate to a new cluster, built from the ground up,
> that is the best option.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://www.msmvps.com/clustering - Blog
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23PKJjfJHHHA.3872@.TK2MSFTNGP06.phx.gbl...
>
|||What you can do, however, if you are willing to live with a new network
name, is to build a new single-node cluster and install SQL Server on it.
After migrating the databases to this new host, you can then rebuild the
original server and then have it joined to the new cluster as a new cluster
node.
This last bit has very specific steps. Look them up in Books Online or
TechNet. I even think I have a detailed list lying around this new group
somewhere. If I find it, I'll shoot it out to you.
Anyway, this should save you the cost of buying a third server.
Sincerely,
Anthony Thomas

"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23WajdlSHHHA.960@.TK2MSFTNGP04.phx.gbl...
> Just to expand on what Rodney said, it is possible to create a cluster
from[vbcol=seagreen]
> existing equipment, but it is neither desirable nor supported. The
> potential disruption during the install and migration process will likely
> cause some unplanned downtime, which defeats the entire purpose of a
> cluster. It is best to start from a clean slate and migrate the data into
> the new system.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:ODTKVxJHHHA.4804@.TK2MSFTNGP03.phx.gbl...
after[vbcol=seagreen]
Can[vbcol=seagreen]
server
>

Sunday, February 19, 2012

Clustering installation issue

I'm trying to install SQL 2000 on a newly clustered Win2000 system.

I'm getting the following error:
The drive chosen for the program files installation path, C:, is not valid on all nodes of the cluster. Choose another drive to install the program files.

I've read Microsoft's explanation of the error, and that the one server can't access the C: drive of the other server if file and print sharing isn't setup correctly. However...

I'm logged on as Administrator, and have full control of both machines.
I'm telling the SQL install to use the administrator account.
If I go to start->run-> and type \\serverB\C$ the share opens just fine.
I've also check the registry entries that MS suggests: HKey_LocalMachine\Software\Microsoft\Windows\Curre ntVersion and make sure that the value for CommonFilesDir and ProgramFilesDir is the same on both the nodes. They're the same

I am trying to install the data files within the same group as the quorom drive, but that should just give me a warning (and it does) and shouldn't be this problem. I'm just trying to install to the system drive (C:) which certainly exists on both machines.

I've seen a couple other posts about this, but no solutions. Any help would be great.

Thanks,
MattReview this http://support.microsoft.com/default.aspx?scid=kb;[LN];Q279642] to take care of further installation.|||Satya,

I tried the link that you provided, but got to a Microsoft page that said that page no longer exists.

If it's the KB article:
Microsoft Knowledge Base Article - 279642

I have already gone through that without any luck. But if it's something else, I'd be happy to try it.

Also, I have since been able to get the installation to work if instead of using the C: drive, I use the drive dedicated to the page file. Obviously, this isn't something I'd want to do, but hopefully it provides more information that might help someone figure out what my issue is.

Thanks,
matt|||You cn install the program files to the C:\ drive, but all data has to be installed on a shared drive. Are you selecting custom install with Data going to a shared drive?|||Further help from here (http://www.sql-server-performance.com/wndows2000_clustering_install_main.asp)

HTH|||Well, I have no idea what this issue was, but to get around it you can:

Install SQL Server locally to one of the nodes of the cluster.
Be sure to put the data files on a drive that is part of the cluster.
Then upgrade the local instance to a clustered instance.

All worked for me, using that strange process.

Clustering different versions of SQL Serv 2005

Is it possible to have different versions of SQL Server 2005 clustered as
various nodes. ie:
Node 1 - Sql server 2005 Enterprise
Node 2 - Sql server 2005 Standard
Node 3 - Sql server 2005 Standard
You have a slight misunderstanding of how clustering works.
In a clustered environment, there are SQL instances and host nodes. The two
entities are entirely independent. An Instance can run on one or more
nodes. A node can host zero or more instances at any given time.
Generally, I set up clusters with each instance on a preferred node, just so
I can find it easily. So, what you are looking for is a cluster with three
nodes and three SQL instances, each instance normally running on a separate
node. In addition, the instances may be different editions of the SQL
Server product.
Since instances are independent of each other as well as the host nodes, you
can mix and match versions and editions on the same cluster, just like on a
stand-alone system.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"divensail" <divensail@.discussions.microsoft.com> wrote in message
news:8AB54FC9-696B-43E5-8877-BB30E0B0083A@.microsoft.com...
> Is it possible to have different versions of SQL Server 2005 clustered as
> various nodes. ie:
> Node 1 - Sql server 2005 Enterprise
> Node 2 - Sql server 2005 Standard
> Node 3 - Sql server 2005 Standard
|||Hi
Just to add to Geoff's comments.
Since you have 3 nodes, Standard Edition will not install on all 3 as it
supports 2 node clusters only.
So you have to pick the possible owner nodes very carefully.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"divensail" <divensail@.discussions.microsoft.com> wrote in message
news:8AB54FC9-696B-43E5-8877-BB30E0B0083A@.microsoft.com...
> Is it possible to have different versions of SQL Server 2005 clustered as
> various nodes. ie:
> Node 1 - Sql server 2005 Enterprise
> Node 2 - Sql server 2005 Standard
> Node 3 - Sql server 2005 Standard

Clustering and SA Role

Sorry for the re-post.
In a clustered SQL Server 2005 environment you must assign SQL Server, Agent
and Full Text Search to domain groups. These groups then appear in the Sys
Admin fixed role. With that, anyone who can add members to groups in the
domain can also become SQL Server SAs. How can this be prevented? I don't
think anyone with ability/access to add members to groups (even domain
admins) should be allowed to automatically make him/herself a SQL SA be
inheritance.
Hi
It comes down to processes within your organization and also how you secure
your AD.
You can grant someone to only manage objects in a certain tree, so not
giving them rights to the SQL Server accounts that should be in their own
tree would be the correct way of doing it.
Do you trust the same person not to give themselves Enterprise Admin rights
in the domain?
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jay" <msnews.microsoft.com> wrote in message
news:uf6d0$L6FHA.1148@.tk2msftngp13.phx.gbl...
> Sorry for the re-post.
> In a clustered SQL Server 2005 environment you must assign SQL Server,
> Agent
> and Full Text Search to domain groups. These groups then appear in the
> Sys
> Admin fixed role. With that, anyone who can add members to groups in the
> domain can also become SQL Server SAs. How can this be prevented? I
> don't think anyone with ability/access to add members to groups (even
> domain admins) should be allowed to automatically make him/herself a SQL
> SA be inheritance.
>
>
|||I guess the issue is that in SQL2000 one could remove local admin group from
the sysadmin role, thus preventing the server admin from easily and
legitimately getting into SQL Server. So why can't we have that in SQL2005?
In practice, of course, I bet in many places one would find that the
accounts of the DBAs are placed into a group and that group is granted
access to SQL Server. So if a server admin really wants, he can add himself
to that group, thus gaining full access to SQL Server. So in the end, it
does come down to trust. Trust for sure simplifies management in many
scenarios. Without trust, one would have to go to excessive length to get
things down or prevent things from happening. And in most places, I'd say if
you don't have trust, you have a bigger problem than keeping the server
admin out of SQL Server.
Nevertheless it's nice to, at least, have the option available to keep the
server admin out, if necessary. Unfortunately, the cluster domain groups
required in SQl2005 are not thoroughly documented in current SQL2005 BOL. I
underdstand that the December BOL refresh will have more materials on these
groups. I hope it can shed some light on the issue being discussed here.
Linchi
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:Ok%23Y3jN6FHA.2816@.tk2msftngp13.phx.gbl...
> Hi
> It comes down to processes within your organization and also how you
> secure your AD.
> You can grant someone to only manage objects in a certain tree, so not
> giving them rights to the SQL Server accounts that should be in their own
> tree would be the correct way of doing it.
> Do you trust the same person not to give themselves Enterprise Admin
> rights in the domain?
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jay" <msnews.microsoft.com> wrote in message
> news:uf6d0$L6FHA.1148@.tk2msftngp13.phx.gbl...
>
|||You do have to remember too that Domain Admins, Enterprise Admins, User
Admins, and OU Admins have the ability to reset the passwords to user
accounts in the AD as well as add users to Global and Resource (Domain
Local?) Groups. So, again, it comes down to trust . . . and audits.
It helps if your Domain/Enterprise Administrators, Server Administrators,
Security Administrators, AD Administrators, Exchange Administrators, Web
Server Administrators, Application Server Administrators, Message Queue
Administrators, and SQL Server Administrators, etc., etc., etc., all be
managed by separate groups by managers of equal rank, all with elevated
privileges, all auditing the activities of the other groups: peer review,
within and throughout the organization to provide checks and balances to the
Change Control Process.
Sincerely,
Anthony Thomas

"Linchi Shea" <linchi_shea@.NOSPAMml.om> wrote in message
news:u3MQcxN6FHA.2888@.tk2msftngp13.phx.gbl...
> I guess the issue is that in SQL2000 one could remove local admin group
from
> the sysadmin role, thus preventing the server admin from easily and
> legitimately getting into SQL Server. So why can't we have that in
SQL2005?
> In practice, of course, I bet in many places one would find that the
> accounts of the DBAs are placed into a group and that group is granted
> access to SQL Server. So if a server admin really wants, he can add
himself
> to that group, thus gaining full access to SQL Server. So in the end, it
> does come down to trust. Trust for sure simplifies management in many
> scenarios. Without trust, one would have to go to excessive length to get
> things down or prevent things from happening. And in most places, I'd say
if
> you don't have trust, you have a bigger problem than keeping the server
> admin out of SQL Server.
> Nevertheless it's nice to, at least, have the option available to keep the
> server admin out, if necessary. Unfortunately, the cluster domain groups
> required in SQl2005 are not thoroughly documented in current SQL2005 BOL.
I
> underdstand that the December BOL refresh will have more materials on
these[vbcol=seagreen]
> groups. I hope it can shed some light on the issue being discussed here.
> Linchi
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:Ok%23Y3jN6FHA.2816@.tk2msftngp13.phx.gbl...
own[vbcol=seagreen]
the[vbcol=seagreen]
SQL
>

Clustering and Hyperthreading

Hi,
We had a problem with a clustered sql box a month or so ago and contacted MS product support. Anyway, we got a patch which appears to have fixed our problem. However, one thing was curious. I didn't speak to Moft but the person who did said MS said NOT to
turn hyperthreading on SQL clusters on! Now that shocked me. Does anyone know why? We turned it off to see whether it was reponsible for some performance issues and results were inconclusive. So we were going to turn it back on. We were strongly advised
not to do so. So does anyone know why or could point me in the right direction? I've never heard this mentioned before.
Thanks
DaveK
http://www.sqlporn.co.uk
The only time you turn it off, as far as I know of, is when you have the max
number of processors installed already.
Example: Windows XP support only 2 processors, if you have two already, turn
off Hyperthreading.
Example: Windows Enterprise supports 8 processors, if you have 8 already,
turn off Hyperthreading.
So, how many processors are in your nodes?
Cheers,
Rod
"DaveK" <anonymous@.discussions.microsoft.com> wrote in message
news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
> Hi,
> We had a problem with a clustered sql box a month or so ago and contacted
MS product support. Anyway, we got a patch which appears to have fixed our
problem. However, one thing was curious. I didn't speak to Moft but the
person who did said MS said NOT to turn hyperthreading on SQL clusters on!
Now that shocked me. Does anyone know why? We turned it off to see whether
it was reponsible for some performance issues and results were inconclusive.
So we were going to turn it back on. We were strongly advised not to do so.
So does anyone know why or could point me in the right direction? I've never
heard this mentioned before.
> Thanks
> DaveK
> http://www.sqlporn.co.uk
|||Not exactly.
I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16 virtual
processors just fine. I believe that Service Pack 2 contained the code to
fix the virtual/physical processor count issue. Build 816 and 910 (post SP3
hotfixes) contain some code to improve performance on Hyperthreaded systems.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> The only time you turn it off, as far as I know of, is when you have the
max
> number of processors installed already.
> Example: Windows XP support only 2 processors, if you have two already,
turn[vbcol=seagreen]
> off Hyperthreading.
> Example: Windows Enterprise supports 8 processors, if you have 8 already,
> turn off Hyperthreading.
> So, how many processors are in your nodes?
> Cheers,
> Rod
> "DaveK" <anonymous@.discussions.microsoft.com> wrote in message
> news:1AF8D235-E366-4CF0-8F9F-23FBBE1D25F4@.microsoft.com...
contacted
> MS product support. Anyway, we got a patch which appears to have fixed our
> problem. However, one thing was curious. I didn't speak to Moft but the
> person who did said MS said NOT to turn hyperthreading on SQL clusters on!
> Now that shocked me. Does anyone know why? We turned it off to see whether
> it was reponsible for some performance issues and results were
inconclusive.
> So we were going to turn it back on. We were strongly advised not to do
so.
> So does anyone know why or could point me in the right direction? I've
never
> heard this mentioned before.
>
|||HP recommends to turn it off on XP if you have two processors. I don't have
time to lookup the article right now. With it on or off on XP, I did not see
a difference.
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> Not exactly.
> I have a cluster with 8-way Hyperthreaded hosts. SQL handles the 16
virtual
> processors just fine. I believe that Service Pack 2 contained the code to
> fix the virtual/physical processor count issue. Build 816 and 910 (post
SP3
> hotfixes) contain some code to improve performance on Hyperthreaded
systems.[vbcol=seagreen]
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:%23PHgs2%23REHA.3344@.TK2MSFTNGP12.phx.gbl...
> max
> turn
already,[vbcol=seagreen]
> contacted
our[vbcol=seagreen]
on![vbcol=seagreen]
whether
> inconclusive.
> so.
> never
>
|||I agree with the XP recommendation. For a dedicated SQL server, I see a
significant difference between off and on. (Unisys ES7000 8x2.8GHz) Average
CPU 26% on, 40% off.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see[vbcol=seagreen]
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do
>
|||Sweet, I know I turn it on with only 2 procs, have not run into an 8 way
with it on or off. Great info, thanks!
Cheers,
Rod
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:eQtgTU$REHA.3300@.TK2MSFTNGP09.phx.gbl...
> I agree with the XP recommendation. For a dedicated SQL server, I see a
> significant difference between off and on. (Unisys ES7000 8x2.8GHz)
Average[vbcol=seagreen]
> CPU 26% on, 40% off.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
> message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> have
> see
code[vbcol=seagreen]
> to
(post[vbcol=seagreen]
> the
> already,
fixed[vbcol=seagreen]
> the
clusters[vbcol=seagreen]
> do
I've
>
|||Hopefully your not trying to run a cluster on WinXP anyway<g>.
Andrew J. Kelly
SQL Server MVP
"Rodney R. Fournier [MVP]" <rod@.die.spam.die.nw-america.com> wrote in
message news:O6nr$E$REHA.3504@.TK2MSFTNGP09.phx.gbl...
> HP recommends to turn it off on XP if you have two processors. I don't
have
> time to lookup the article right now. With it on or off on XP, I did not
see[vbcol=seagreen]
> a difference.
> Cheers,
> Rod
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
> news:eKGGZ6%23REHA.1312@.TK2MSFTNGP12.phx.gbl...
> virtual
to[vbcol=seagreen]
> SP3
> systems.
the[vbcol=seagreen]
already,[vbcol=seagreen]
> already,
> our
the[vbcol=seagreen]
> on!
> whether
do
>
|||Dave,
We put 910 into place less than a week ago when we found out why we had to have it: previously - 818 - SQL 2000 allows all logins access to the system before tempdb comes online. Well, we are a web-based company. We are running an active/active SQL cluste
r on a Win2K3 server ent ed cluster with HT enabled. So, I'm concerned to hear your comment about this option. Now, yesterday we see odd behavior on the system. Specifically the following error:
Transaction (Process ID 350) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction..
Sure, we've had deadlocks before but, not this type "communication buffer".
Another thing we've noticed is that if a process wants to go parallel, we've seen it go to 49 degrees of parallelism. You can imagine all of our CPUs are then spiking (2 servers x 8 CPUs each x HT), which in turn doesn't make the rest of the processes hap
py.
We only have SQL Server running on these servers. All web servers (over 40) can hit this cluster at any given time.
Would love to have a discussion with you. Microsoft told us that we are only the second company that reported needing the 910 patch. So, I'm expecting you are the other company. Are you web-based? Or, have lots of web server activity against your database
servers?
We've seen some other odd behavior as well. But, this e-mail is getting lengthy.
I see you are in the UK. We are in the USA.
Your reply is appreciated,
Doug
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||910 was built specifically for us after we reported a bug via Unisys.
Technically the build was for Unisys under their Premier agreement, but we
were the end customer.
I have also seen extra-high parallelism. I have limited the degree of
parallelism intentionally since I prefer higher response to the many
transactional queries rather than the few analysis-type queries.
Yes, we are very web-based. About 85-90% of our pages are .Net based. We
are the number one job board in the US (we recently passed Monster).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Doug Thomas" <dougt@.ecollege.com> wrote in message
news:uRIk$jTVEHA.3332@.tk2msftngp13.phx.gbl...
> Dave,
> We put 910 into place less than a week ago when we found out why we had to
have it: previously - 818 - SQL 2000 allows all logins access to the system
before tempdb comes online. Well, we are a web-based company. We are running
an active/active SQL cluster on a Win2K3 server ent ed cluster with HT
enabled. So, I'm concerned to hear your comment about this option. Now,
yesterday we see odd behavior on the system. Specifically the following
error:
> Transaction (Process ID 350) was deadlocked on lock | communication
buffer resources with another process and has been chosen as the deadlock
victim. Rerun the transaction..
> Sure, we've had deadlocks before but, not this type "communication
buffer".
> Another thing we've noticed is that if a process wants to go parallel,
we've seen it go to 49 degrees of parallelism. You can imagine all of our
CPUs are then spiking (2 servers x 8 CPUs each x HT), which in turn doesn't
make the rest of the processes happy.
> We only have SQL Server running on these servers. All web servers (over
40) can hit this cluster at any given time.
> Would love to have a discussion with you. Microsoft told us that we are
only the second company that reported needing the 910 patch. So, I'm
expecting you are the other company. Are you web-based? Or, have lots of web
server activity against your database servers?
> We've seen some other odd behavior as well. But, this e-mail is getting
lengthy.
> I see you are in the UK. We are in the USA.
> Your reply is appreciated,
> Doug
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Clustering and Hyperthreading

Clustering Advice - different locations

Hello - We are considering implementing a clustered SQL scenario over 2
different datacentre locations. At the moment we are thinking of probably
going down the hot standby situation where we run the primary server in one
datacentre and the failover in the other with some replication software
running on both servers to keep the SQL files the same in realtime. What
we'd really like to do is to have both servers live at the same time in an
Active/Active cluster but are not sure of the best way to get around the
replication issue. I know that shared storage is the norm for SQL clusters
but we really seperate storage in 2 different physical locations. Does
anyone have any advice on the best way or any options we have in tackling
this?
Thanks in advance, ian.
Hi
Veritas offers clustering that allows for Geographically dispersed nodes. We
are not using it at the moment, but might be investigated in the next few
months.
We use a 2+1 cluster. 2 Nodes of a cluster in one location, and with EMC
SRDF, we keep the 2nd location real-time updated. The 2nd location has a
single server, setup as a cluster, and if location 1 goes down, we do a
SPLIT at EMC level, and bring up the cluster. We will not loose any data.
Don't forget, with a SAN, if one node writes garbage onto the drives, your
other node is not much use to you either.
Log shipping is generally the least intrusive form of DR as replication at
data level does impose requirements on the DB and table structure
SQL Server 2005 will offer another solution, called database mirroring.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Ian Rogers" <ianr0gers@.hotmail.comm> wrote in message
news:OlaGnIDUFHA.2172@.TK2MSFTNGP15.phx.gbl...
> Hello - We are considering implementing a clustered SQL scenario over 2
> different datacentre locations. At the moment we are thinking of probably
> going down the hot standby situation where we run the primary server in
> one datacentre and the failover in the other with some replication
> software running on both servers to keep the SQL files the same in
> realtime. What we'd really like to do is to have both servers live at the
> same time in an Active/Active cluster but are not sure of the best way to
> get around the replication issue. I know that shared storage is the norm
> for SQL clusters but we really seperate storage in 2 different physical
> locations. Does anyone have any advice on the best way or any options we
> have in tackling this?
> Thanks in advance, ian.
>

Thursday, February 16, 2012

clustered-index issue

hi,
yesterday i realized (by those helpfull explenations of a
few guys of this forum) that clustered index contains all
of the fields of the table (it looks like the index is
actually the table itself...?), and also that updateing
varing field types like varchar may change the the length
of a row, and therefor may cause a page-split in the index.
in the first place i described a case of a clustered index
on an IDENTITY column (IDENTITY_INSERT is never ON so it
is actually always auto-incremented), so the page split
may happen only because of an Update, and never because of
an Insert.
well, my table is acutally has only fields of
int,float,and datetime types, and only 1 vharchar(40). the
maximum length of a raw is exaclty 200 (so the minimum is
160 when the varchar is actually empty), so 40 is 20% of
200.
finally, for my questions:
1. if i want to avoid any chance for page-split, should i
set the fill-factor of the clustered index to 80?
2. what would be considered as better performance for
reading & updating (amount of storage place is not an
issue): A. chaging the type of the varchar(40) to char
(40), and setting fill-factor to 100. B. leaving the
varchar(40) as it is, and setting fill-factor to 80.
Thanks in advance.
edo.
edo
Setting fillfactor=100 is sutiable for 'read-only' tables because the page
is 100 percent full and I/O is lower as well
By having clustrered index on the table, you eliminate page splitting
entirely because all new records will be added to the end of the table. But
only do this if you know that a clustered index on the primary key is the
best option for you when it comes to query performance. But just thinking if
you have datapage with 1,5,7 index structure and a new row (let me say 3) is
added to index page. Then 5 and 7 to be moved on a new datapage (created by
SQL Server and allocated anywhere) in order to make room for 3. Now, your
data is not in logical order (external fragmentation).
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>
|||> clustered index contains all of the fields of the table (it looks like the
index is actually the table itself...?),
It is the table itself, and the keys in the clustered index determine how
the rows are ordered. And you only avoid page splits during inserts if the
primary key of the table is also the clustered index keys of the table i.e.
a clustered primary key, as you could have a non-clustered primary key.
I'm not too sure of the various options you are contemplating, though they
appear sound. I'll probably go the char(40) route since space is not an
issue and I believe there are (slight) overheads when dealing with varchar
columns.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>
|||> it probably save place for at least 1 WHOLE row, so it will be able to
place it as a whole without page-split.
Why would it do that if the clustered index is also your primary key?
Say a page is 8020 bytes. If all new rows reached their max size during
inserts, you could've inserted 32 rows and wasted 1604 bytes. If all new
rows were created with no value for the varchar column, you could've
inserted approx. 40 rows. Now if all 40 rows were then updated to fill the
varchar column to 40 chars, you would need 1600 bytes, which still fits on
your page. So the worst case scenario is to waste 1604 bytes per page, and
the best case is to fill the page entirely. NOTE: above calculations
ignored page overheads.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d3bd01c45367$8b11c130$a001280a@.phx.gbl...
> hi,
> i guess i made a basic mistake while trying to "calculate"
> the fill-factor percentage needed:
> i guess the server probably does not gain sagnificant
> improvment by saveing only 20% space of the possible row
> size. it probably save place for at least 1 WHOLE row, so
> it will be able to place it as a whole without page-split.
> actually, i believe 20 percent means that there is enough
> space for much more than 1 row.
> so, as to my questions:
> 1. may be i can even set fill-factor to 99% and still be
> sure that there is no chance for a split-page at all?
> 2. same question with no principle changes.
>
> thanks again.
> edo.
|||hi,
you wrote:
But just thinking if you have datapage with 1,5,7 index
structure and a new row (let me say 3) is added to index
page....
i'm not sure i understand this line.
i ensured that 3 can not come after 7, since the clustered
index in on an IDENTITY column, and i never intend to set
IDENTITY_INSERT to ON.
so, maybe you are talking about a kind of a temporary
result-set built by the server as SELECT SQL query? or
what?
anyway, you mentioned "query performance". as for that,
one of the most common tasks my application is doing is to
find raws accordig to this field, like for example:
select * from my_table where recid=X
thanks again,
edo.
|||edo
Yep, I was assuming that you don't have an Identity property on the table.
Does recid column has a clustered index?
If your output is large set I suggets you to create a clustered index on
this column otherwise I would create a non clustered.
Also run SET STATISTICS IO ON to see what is going on when SQL Server is
perfoming the query.
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1ccff01c4536a$2327dbb0$a301280a@.phx.gbl...
> hi,
> you wrote:
> But just thinking if you have datapage with 1,5,7 index
> structure and a new row (let me say 3) is added to index
> page....
> i'm not sure i understand this line.
> i ensured that 3 can not come after 7, since the clustered
> index in on an IDENTITY column, and i never intend to set
> IDENTITY_INSERT to ON.
> so, maybe you are talking about a kind of a temporary
> result-set built by the server as SELECT SQL query? or
> what?
> anyway, you mentioned "query performance". as for that,
> one of the most common tasks my application is doing is to
> find raws accordig to this field, like for example:
> select * from my_table where recid=X
> thanks again,
> edo.
>
>
>
|||hi,
well, it's much more clear for me now.
indeed, it seems that the bottom line is that in order to
avoid page-split at all, the fill-factor should be
(minimum_row_size/maximum_row_size)*100.
another question, if i may:
thinking of the search issue ALONE, would it be faster for
the SQL-Server to find a row if the index has larger fill-
factor, or it's only a metter of a wasted space?
for example, given two identical tables, both filled
exactly with the same data, but the fill-factor of the
first is 50% and the fill-factor of the second is 100%.
dose a search in the second table would be faster
(significantly or at all)?
another issue, just to make sure: do a fixed-size type
(like Integer) may influence the actual row size if it
alows NULL values? dose a NULL value take the same amount
of space as "real" Value? if not, in order to ensure a
minimum_row_size of 160, i must set all the fields to NOT
NULL, right?
thanks a lot.
edo.
|||I guess it all boils down to what Uri mentioned in the other post i.e. I/O.
The less I/O SQL Server has to read, the faster your operations can
complete. Thus, the more data that can fit on a page, the faster your
search will execute. Whether it is significant would depend on the
difference in the number of pages between the 2 tables, taking into account
SQL Server reads 8 pages at a time (extents), the OS probably reads it in
bigger chunks, and your disk controller probably has its own caching system.
Not forgetting that the pages may be cached in SQL Server buffers where size
permits, further diminishing the difference if the entire table can fit into
the cache.
Re your second question, based on a simple (unscientific) test I did, a NULL
value does not take up more space than a real value. If you're interested,
look up the DBCC PAGE command to view the contents of a data page.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d0fd01c45374$6ad1c2a0$a501280a@.phx.gbl...
> hi,
> well, it's much more clear for me now.
> indeed, it seems that the bottom line is that in order to
> avoid page-split at all, the fill-factor should be
> (minimum_row_size/maximum_row_size)*100.
> another question, if i may:
> thinking of the search issue ALONE, would it be faster for
> the SQL-Server to find a row if the index has larger fill-
> factor, or it's only a metter of a wasted space?
> for example, given two identical tables, both filled
> exactly with the same data, but the fill-factor of the
> first is 50% and the fill-factor of the second is 100%.
> dose a search in the second table would be faster
> (significantly or at all)?
> another issue, just to make sure: do a fixed-size type
> (like Integer) may influence the actual row size if it
> alows NULL values? dose a NULL value take the same amount
> of space as "real" Value? if not, in order to ensure a
> minimum_row_size of 160, i must set all the fields to NOT
> NULL, right?
> thanks a lot.
> edo.
>
|||
>If you're interested,
>look up the DBCC PAGE command to view the contents of a
Data page.
i'll check this out.
thanks (again),
edo.
|||There is a bit for each field which allows null, and if the field is null
the flag is turned on... storage for nulls is very very small.
One thing you should consider before changing the varchar into char is
whether or not you intend to index the column... The index on the char
column would be larger than the varchar, and may slow performance..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>

Thursday, March 22, 2012

Collation and Win2K3 Clustered Environment.

Sunday, March 11, 2012

Code Review

Thursday, March 8, 2012

Code Review

Saturday, February 25, 2012

Clustrer DB engine and SAS?

ClusterService Log On Account and SQL2000

Friday, February 24, 2012

Clustering with sql and third party database softwares

Clustering with NAS

Clustering Question...

Sunday, February 19, 2012

Clustering installation issue

Clustering different versions of SQL Serv 2005

Clustering and SA Role

Clustering and Hyperthreading

Clustering and Hyperthreading

Clustering and Hyperthreading

Clustering Advice - different locations

Thursday, February 16, 2012

clustered-index issue

Clustered DB Server

Blog Archive

About Me