Cluster Server Planning.

I would like some advise on my plan to install an active/active sql cluster.
The plan is to build 2 boxes with W2k3 server and SQL2003. I'll have 13
drives on a shared disk storage (two sets of mirrored drives) (two sets of
Raid 5 [4 drives each]) (1 global hot spare). I'll create a 1 Gig logical
drive (Q & P)on each of the mirrored sets (P will be a right-off but will
keep everything looking the same). The Q drive is for the Quorum. So I'll
have C: and D: on each machine, (Q: Emirrored drives F: Raid5 (P: G
mirrored drives H: Raid5. SQL1 will own Q: E: and F:, SQL2 will own P: G:
and H:. E: and G: will be for transaction logs while F: and H: are for the
databases. I'll install instances of sql running on each server and spilt
the databases between the two (we have around fifty). Is this a sound plan,
or have I just wasted my time?
That sounds pretty good. I would seriously look at RAID 10 rather than RAID
5. The difference in write performance can be huge.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:9AD5CF79-5664-466C-AB15-F48C4F99972C@.microsoft.com...
> I would like some advise on my plan to install an active/active sql
cluster.
> The plan is to build 2 boxes with W2k3 server and SQL2003. I'll have 13
> drives on a shared disk storage (two sets of mirrored drives) (two sets of
> Raid 5 [4 drives each]) (1 global hot spare). I'll create a 1 Gig logical
> drive (Q & P)on each of the mirrored sets (P will be a right-off but will
> keep everything looking the same). The Q drive is for the Quorum. So I'll
> have C: and D: on each machine, (Q: Emirrored drives F: Raid5 (P: G
> mirrored drives H: Raid5. SQL1 will own Q: E: and F:, SQL2 will own P:
G:
> and H:. E: and G: will be for transaction logs while F: and H: are for
the
> databases. I'll install instances of sql running on each server and spilt
> the databases between the two (we have around fifty). Is this a sound
plan,
> or have I just wasted my time?
|||I do not understand the Q and P drive assignment.
I have a Q (Quorum) in a seperate cluster resource group (with seperate
ip-address an networkname)
I have a X (MSDTC) in a seperate cluster resource group (with seperate
ip-address an networkname)
The Q and X drive do not come back in the SQL cluster resource group's
You do not mention a seperate cluster resource group for MSDTC. You should
do that.
Gr. G
(more info on MSDTC: http://sswug.org/blogging/gbrander/)
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:9AD5CF79-5664-466C-AB15-F48C4F99972C@.microsoft.com...
>I would like some advise on my plan to install an active/active sql
>cluster.
> The plan is to build 2 boxes with W2k3 server and SQL2003. I'll have 13
> drives on a shared disk storage (two sets of mirrored drives) (two sets of
> Raid 5 [4 drives each]) (1 global hot spare). I'll create a 1 Gig logical
> drive (Q & P)on each of the mirrored sets (P will be a right-off but will
> keep everything looking the same). The Q drive is for the Quorum. So I'll
> have C: and D: on each machine, (Q: Emirrored drives F: Raid5 (P: G
> mirrored drives H: Raid5. SQL1 will own Q: E: and F:, SQL2 will own P:
> G:
> and H:. E: and G: will be for transaction logs while F: and H: are for
> the
> databases. I'll install instances of sql running on each server and spilt
> the databases between the two (we have around fifty). Is this a sound
> plan,
> or have I just wasted my time?
|||I am not planning going to use and entire physical disk for the Quorum. I'm
going to make a partition on a mirrored set (that will be a physical disk)
that will be the Q: drive. The P: drive is just my way of keeping
everything looking the same. My plan is to have only 2 cluster groups.
Group 1 be will the Cluster IP, Cluster Name, the Physical Disk (E: Q, the
Physical Disk (F, the MSDTC, and the first instance of SQL. Group 1 will
be owned by server1. Group 2 be will the Physical Disk (G: P, the
Physical Disk (H, and the second instance of SQL. Group 2 will be owned by
server2. Will this work ?
"Gé Brander" wrote:

> I do not understand the Q and P drive assignment.
> I have a Q (Quorum) in a seperate cluster resource group (with seperate
> ip-address an networkname)
> I have a X (MSDTC) in a seperate cluster resource group (with seperate
> ip-address an networkname)
> The Q and X drive do not come back in the SQL cluster resource group's
> You do not mention a seperate cluster resource group for MSDTC. You should
> do that.
> Gr. Gé
> (more info on MSDTC: http://sswug.org/blogging/gbrander/)
> "Wayne" <Wayne@.discussions.microsoft.com> wrote in message
> news:9AD5CF79-5664-466C-AB15-F48C4F99972C@.microsoft.com...
>
>
|||I too and lost by your wording I think, or maybe cause its Monday here. Are
you saying you will split a LUN into 2 or more partitions and then try to
use different partitions with different nodes? Clustering does not deal with
partitions, only drives. So a node or instance will not be able to share a
drive (and one or more partitions) with another node/instance.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:3AA6C951-0E13-4619-906F-BB0B0DB0C463@.microsoft.com...[vbcol=seagreen]
>I am not planning going to use and entire physical disk for the Quorum.
>I'm
> going to make a partition on a mirrored set (that will be a physical disk)
> that will be the Q: drive. The P: drive is just my way of keeping
> everything looking the same. My plan is to have only 2 cluster groups.
> Group 1 be will the Cluster IP, Cluster Name, the Physical Disk (E: Q,
> the
> Physical Disk (F, the MSDTC, and the first instance of SQL. Group 1
> will
> be owned by server1. Group 2 be will the Physical Disk (G: P, the
> Physical Disk (H, and the second instance of SQL. Group 2 will be owned
> by
> server2. Will this work ?
> "G Brander" wrote:
|||Good catch Rodney.
Clustering looks at physical disks (LUNs in SAN-speak). If you partition
the disk, clustering still sees the underlying physical disk.
Also, you don't want your Quorum disk to be part of your SQL Resource group.
That is a Low-Availability approach. Putting MSDCT in with a SQL instance
is an even worse approach. You need one disk for the Quorum, preferably one
disk for MSDTC (although it can be the same as the Quorum disk), and at
least one (preferably two or more) disks per SQL instance. These must be
physical disks or separate LUNs from a SAN device. Anything else will
compromise availability to the point that a cluster won't buy you any higher
availability.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:3AA6C951-0E13-4619-906F-BB0B0DB0C463@.microsoft.com...
> I am not planning going to use and entire physical disk for the Quorum.
I'm
> going to make a partition on a mirrored set (that will be a physical disk)
> that will be the Q: drive. The P: drive is just my way of keeping
> everything looking the same. My plan is to have only 2 cluster groups.
> Group 1 be will the Cluster IP, Cluster Name, the Physical Disk (E: Q,
the
> Physical Disk (F, the MSDTC, and the first instance of SQL. Group 1
will
> be owned by server1. Group 2 be will the Physical Disk (G: P, the
> Physical Disk (H, and the second instance of SQL. Group 2 will be owned
by[vbcol=seagreen]
> server2. Will this work ?
> "G Brander" wrote:
should[vbcol=seagreen]
13[vbcol=seagreen]
sets of[vbcol=seagreen]
logical[vbcol=seagreen]
will[vbcol=seagreen]
I'll[vbcol=seagreen]
G[vbcol=seagreen]
P:[vbcol=seagreen]
for[vbcol=seagreen]
spilt[vbcol=seagreen]
|||Rodney and Geoff, I admit my terminology was bad. I 'AM' going to have 2
physical disks (LUNs in SAN-speak) per instance of SQL. One for databases
and one for transaction logs. I was trying to see if I could cheat and 'NOT'
use an entire disk for the quorum, but looks like that will not work (or not
work very well) in a multiple instance cluster. So it looks like I'll need
14, probably 15 disks on my shared storage to make this work.
So how about plan B:
Setup one physical disk (probably mirrored) for the quorum and the MSDTC.
Setup one physical disk (mirrored) for the transaction logs for each instance
of SQL. Setup one physical disk (Raid 5 or maybe 10) for the databases for
each instance of SQL. And if I can afford it a global hot spare. Better to
get it right in the planning stage than looking like an idiot trying to get a
bad design to work.
Thanks
"Geoff N. Hiten" wrote:

> Good catch Rodney.
> Clustering looks at physical disks (LUNs in SAN-speak). If you partition
> the disk, clustering still sees the underlying physical disk.
> Also, you don't want your Quorum disk to be part of your SQL Resource group.
> That is a Low-Availability approach. Putting MSDCT in with a SQL instance
> is an even worse approach. You need one disk for the Quorum, preferably one
> disk for MSDTC (although it can be the same as the Quorum disk), and at
> least one (preferably two or more) disks per SQL instance. These must be
> physical disks or separate LUNs from a SAN device. Anything else will
> compromise availability to the point that a cluster won't buy you any higher
> availability.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Wayne" <Wayne@.discussions.microsoft.com> wrote in message
> news:3AA6C951-0E13-4619-906F-BB0B0DB0C463@.microsoft.com...
> I'm
> the
> will
> by
> should
> 13
> sets of
> logical
> will
> I'll
> G
> P:
> for
> spilt
>
>
|||I like plan B, and not just cause I fully understand it. Question, are your
SQL applications going to use MSDTC? If so, for performance reasons you may
want to have a mirror just for the log and extend the default log size.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:4B6F25F5-FB78-4023-AE65-6F4335DA4940@.microsoft.com...[vbcol=seagreen]
> Rodney and Geoff, I admit my terminology was bad. I 'AM' going to have 2
> physical disks (LUNs in SAN-speak) per instance of SQL. One for databases
> and one for transaction logs. I was trying to see if I could cheat and
> 'NOT'
> use an entire disk for the quorum, but looks like that will not work (or
> not
> work very well) in a multiple instance cluster. So it looks like I'll need
> 14, probably 15 disks on my shared storage to make this work.
> So how about plan B:
> Setup one physical disk (probably mirrored) for the quorum and the MSDTC.
> Setup one physical disk (mirrored) for the transaction logs for each
> instance
> of SQL. Setup one physical disk (Raid 5 or maybe 10) for the databases for
> each instance of SQL. And if I can afford it a global hot spare. Better
> to
> get it right in the planning stage than looking like an idiot trying to
> get a
> bad design to work.
> Thanks
> "Geoff N. Hiten" wrote:
|||Rodney, yes I plan on the SQL applications using the MSDTC. Where can I find
info on how to extend the default log size.
Thanks for the heads up.
"Rodney R. Fournier [MVP]" wrote:

> I like plan B, and not just cause I fully understand it. Question, are your
> SQL applications going to use MSDTC? If so, for performance reasons you may
> want to have a mirror just for the log and extend the default log size.
> Cheers,
> Rod
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering
> http://msmvps.com/clustering - Blog
> "Wayne" <Wayne@.discussions.microsoft.com> wrote in message
> news:4B6F25F5-FB78-4023-AE65-6F4335DA4940@.microsoft.com...
>
>
|||First follow http://support.microsoft.com/kb/817064 on each machine BEFORE
you install the cluster service.
Then on each node - open component services - Computers - My Computer -
Properties - MSDTC tab - Capacity = 12 or 16 or anything larger then 4 MB,
close it out. Make both machines the same size log.
Install Microsoft clustering.
Finally follow
http://support.microsoft.com/default...b;en-us;301600
Then Install SQL in the Cluster.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering
http://msmvps.com/clustering - Blog
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:AD40F458-65B9-45DC-8CA0-5031975CD3DD@.microsoft.com...[vbcol=seagreen]
> Rodney, yes I plan on the SQL applications using the MSDTC. Where can I
> find
> info on how to extend the default log size.
> Thanks for the heads up.
> "Rodney R. Fournier [MVP]" wrote:

Client connection error while trying to create maintenane plans

Error: Ole db error 0x80004005 Client unable to establish connection

This happens when trying to create a maintenance plan in sql 2005 sp 1

It has now happend with 3 installs - one fresh install and two upgrades from 2000 to 2005. The startup account for sql server is a domain user account and has not been changed. This happens on both windows 2000 and windows 2003.

This however does not happen if the sql startup account is in the local admin group for the machine

Any help is greatly appreciated

Thanks

KR

Refer this link , i hope it will solve u r problem.

http://support.microsoft.com/kb/253500/

also upgrade to SP2 , there are lot of enhancement done Maintenance plan wizards in SP2

Refer : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1266171&SiteID=1

Madhu

|||

This happens when the client is on the same machine, so the first link would probably not apply in this case as it deals with remote connections.

Thanks

KR

clearing execution plan cache

Hi,

We have an application which fetches data from a table which has approximately 1 million records.

1. Nearly 25 users will be using this application concurrently.

2. frequent updations will be done to the records in the geographyrolecurriculum table.

3. This table has 1 clustered index and 4 nonclustered index bounded to it.

Problem Statement:

1. Application runs smoothly for 15 - 20 days and after that all the screens throws timeout errors.

When i clear the sys.syscacheobjects its working fine again and screens get loaded quickly.

Please tell me how clearing the syscacheobjects makes the execution fast? and is this the correct way to solve the timeout issue or is there any other alternative?

2. Will the stored procs timeout if the tempdb is full ?

Thanks,

Arunprasad

Hi,

1. Not sure if you're doing this but you should NEVER manually make changes to the system tables, I am hoping that you are running something like DBCC FREEPROCCACHE to clear out the cache.

What I reckon is happening when you clear out the cache is you are basically forcing all the queries to generate new execution plans, and the new plans seem to be faster than the older ones.

What you need to do to ensure that the query optimiser has enough information to determine the correct query plan is to:

a. Maintain you indexes

DBCC INDEXDEFRAG

ALTER INDEX ….. REBUILD / DBCC DBREINDEX

b. Keep your statistics up-to-date

sp_createstats / CREATE STATISTICS

sp_updatestats / UPDATE STATISTICS

2. Managing you tempdb is a task in itself. This is a very interesting read:

http://www.microsoft.com/technet/prodtechnol/sql/2005/workingwithtempdb.mspx

Basically if you sps are creating temp tables or you are using order by, group by, etc you will be using your tempdb. There are issues around the tempdb but they can be managed.

|||

Hi xrayb,

Thanks for the suggestion and we did what you have adviced us to do but the customer says its a hectic process.

So can you suggest me a workaround?

1. i have update STATISTICS ON for all the databases( DATABASE LEVEL) my server has will this have an effect on the table as well?

(Ex: Update statistics <tablename>)

2. Will the new execution plan be always better the older ones always? because this is happening very frequently in my application.

3. Do you have any other suggestion or findings why this timeout happens this frequently.

4. I appreaciate the effort you have put in to clear my doubt.

Thanks,

Arun

|||

1. I have a couple of issues with turning the create / update stats feature on at DB level, although they may not be an issues in your environment but I see them as:

a. You have no control over when it's run.

b. The update is triggered when they are already out of date, and I think this is based on a percentage (I’ve heard 10% and 20%), this is not a problem when you 1000 rows in your table but if you have 50,000,000 then 10% is 5,000,000 which is an awful long time to wait for the stats to be updated. BTW you can enable trace flag 205 to check how often this happens.

Also compare your estimated and an actual graphical execution plans, this will also let on if the stats are current.

Updating stats should be run each time there's a substantial change to the data / indexes, also the ALTER INDEX can be an online operation (although I would only run it during the working day under duress).

Use the DBCC SHOW_STATISTICS command to check individual tables and see what their current state is regarding stats.

2. The execution may be different and hopefully it will be quicker and more efficient (but not always). You may need to do some research into why this is happening, again, like managing your tempdb, performance tuning is an art in its self. Have a look at the link below it will give you enough information to query the DMVs and check how your query is being treated by the query optimiser (like the query plan).

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx

I have slightly modified one of the queries and this returns the top 50 overview of which currently cached batches or procedures are using the most CPU and how many times they are being executed.

Code Snippet

select top 50

sum(qs.total_worker_time) as total_cpu_time,

sum(qs.execution_count) as total_execution_count,

count(*) as number_of_statements,

qs.plan_handle,

eqp.objectid,

OBJECT_NAME(eqp.objectid)

from

sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS eqp

group by qs.plan_handle,eqp.objectid

order by sum(qs.total_worker_time) desc, sum(qs.execution_count) desc

3. You basically need to identify what queries are performing badly, you can capture them (with the above mentioned script) or with server side traces or profiler. You have options to return long running queries. Once you have identified these queries you can start working on them individually.

From what you have described I reckon you have some poorly written queries and as soon as there's a load on the server or the stats are slightly outdated they just collapse in on themselves. I would try and capture the offending query and examine it, you won’t have to go through many before you realise if it’s poorly written.

Have a look at the DMVs too.

From BOL:

Find the 10 missing indexes with the highest anticipated improvement for user queries

SELECT TOP 10 *

FROM sys.dm_db_missing_index_group_stats

ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC;