Disk, Raid Groups, File Group & Cluster config.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Disk, Raid Groups, File Group & Cluster config.

Author

Topic

Dinky
Starting Member

37 Posts

Posted - 2009-09-24 : 23:59:37

Question 1 - Disk, Raid Groups and File Group layout:
---------------------------------------------------
SQL Server 2005.
Developing new enterprise web application
I can divide SAN into 3 or 6 disks drives.
One application working against this db
Standard edition

Is it beneficial to have 6 filegroups or just 3 is good enough.

Here is the plan. Is it making sense. Please advice.
---------------------------------------------------
Tables Data_FG J:/Data RAID 10
History tables History_FG J:/History RAID 10
Primary (System tables) Primary_FG J:/Primary RAID 10

Images & Text columns Image_FG N:/Images RAID 10
(need to be in DB)
Indexes Index_FG N:/Indexes RAID 10

Log Log_FG L:/Logs RAID 1

Question 2 - Cluster:
--------------------
Also we have one web application and SQL Server 2005 Standard Edition. Since Cluster cannot provide any load balancing, I think Single-Instance 2 Node Active-Passive cluster is all what I need. Let me know if setting cluster any differently can be beneficial or advisable.

ANy benefit of using Active-Active (will need atleast 2 licenses)
Any Benefit of Multi Instance (Have only one application working against this db)
Any Benefit of using more than 2 nodes (even if I can move Standard edition to enterprise)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-25 : 16:43:01

Ideally all of these should be separated on their own set of disks:

Data files
Log files
Tempdb
Backups

It is also beneficial to separate indexes from the primary data files. We also separate other system databases.

We use mount points on our SQL Server 2005 clusters.

Yes there's a benefit to using active/active. Almost all of our clusters are active/active with our 4-node cluster being active/active/active/active. With active on all nodes, you aren't wasting hardware but you do have to license all of the nodes.

We also use multi instances on our nodes. We typically put large applications on their own instances so that we can configure different memory settings, different backup times, ...

We will never use more than 2-node cluster again. We are getting rid of our 4-node cluster. The more nodes in the cluster, the longer it takes to patch it such as with the monthly Microsoft security patches or even the SQL patches and service packs. We are moving from a 4-node cluster to 3 2-node clusters. By using more clusters, we can launch multiple installations at the same time.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Dinky
Starting Member

37 Posts

Posted - 2009-09-26 : 12:38:49

Q. So Is Active Active awlays beneficial even if there is only single application and single cluster db.
Q. Isn't Single instance 2 node cluster is as good as 2 instance 2 node cluster and will give same performance for single application.
Q. Is it good idea to put on same file group & disk but diff folder
Tables, History Tables (Data Auditing) , System tables
Q. Is it good idea to put on same file group & disk but diff folder
Image Data andText Data , Indexes
Q. Is it good idea to put Logs on seperate file group on Raid 1

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-09-26 : 14:09:14

Well if you only have one SQL instance and one SQL database, it cannot be active-active. An instance can only be active on one node and only one instance can access a database at a time, so to do active-active clustering there have to be two instances with two databases, one instance on each node.

Clustering is not for performance. Clustering is for high availability

From a performance stand, if tables, history and system tables are on the same disk, it doesn't make the slightest difference how they're separated. If there're in multiple filegroups, there's potentially a backup/restore advantage (partial database availability), but not a performance one.

--
Gail Shaw
SQL Server MVP

Dinky
Starting Member

37 Posts

Posted - 2009-09-26 : 15:41:46

That exactly is the question and confirmation I needed:

Since we have only one application, what we need or make sense is -

Single Instance 2 node Active Passive Cluster - We don't need anything more that that (no active active, no multi instances - doesn't buy us anything in our case) Is It correct?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-09-28 : 13:04:28

That's correct.

If your application uses more than one database, then you could perhaps put them on separate instances which in essence would mean you could load balance across the nodes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Dinky
Starting Member

37 Posts

Posted - 2009-09-28 : 19:02:07

Q. Is Active Active in any way beneficial for single application and single db environment.
Q. Isn't Single instance 2 node cluster is as good as 2 instance 2 node cluster and will give same performance for single application.
Q. Is it good idea to put on same file group & disk but diff folder
- Tables, History Tables (Data Auditing) , System tables
Q. Is it good idea to put all this (Image Data and Text Data and Indexes) on same file group & disk but different folder
Q. Is disadvantages in putting Logs on its own seperate file group on Raid 1

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 2009-09-29 : 12:53:09

- To quote tkizer

quote:
Originally posted by tkizer
Ideally all of these should be separated on their own set of disks:

Data files
Log files
Tempdb
Backups

It is also beneficial to separate indexes from the primary data files. We also separate other system databases.

We use mount points on our SQL Server 2005 clusters.

Yes there's a benefit to using active/active.

And gilamonster

quote:

Well if you only have one SQL instance and one SQL database, it cannot be active-active. An instance can only be active on one node and only one instance can access a database at a time, so to do active-active clustering there have to be two instances with two databases, one instance on each node.

Clustering is not for performance. Clustering is for high availability

From a performance stand, if tables, history and system tables are on the same disk, it doesn't make the slightest difference how they're separated. If there're in multiple filegroups, there's potentially a backup/restore advantage (partial database availability), but not a performance one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources