Database physical files storage locations

Im setting up a SQL 2008 Cluster of 3 active ndoes. I have 2 partitions on the SAN availab开发者_如何转开发le for each node. I just wanted to know the ideal configuration of how i should use these partitions for each node, as in what .mdf and .ldf file should be where ?

I agree this is one for serverfault, however I will answer.

When building SQL clusters you should separate your requirements for SQL and requirements for the OS. So you have a three node multi-instance failover cluster. You didn't specify how many instance of SQL Server you want, but I will take a guess at three?

I, for instance, have many clusters some are five nodes, with four instances. Some are four nodes, with four instances, and I have some test ones with three nodes and four instances.

Your SAN disks need to be allocated to the instance, not the node, because the shared disks (and the SQL instances) can move to any node in the cluster, depending on how you are configuring it. As an example, let's say we have a three node cluster with three instances and you want to be able to have any instance to run on any node. You boot from SAN, and you have a SQLData drive and a SQLTxLogs drive for each instance. You would create LUNs on the SAN as follows:

• C: drives for each node (these are not shared) (3 required in your case)

Following drives are shared and need to be presented to all nodes in your cluster:

• M: drive for MSDTC resources
• Q: drive for quorum
• D: For Instance A SQLData (mdfs)
• E: For Instance A SQLTxLogs (ldfs)
• F: For Instance B SQLData (mdfs)
• G: For Instance B SQLTxLogs (ldfs)
• H: For Instance C SQLData (mdfs)
• I: For Instance C SQLTxLogs (ldfs)

That's a total of 11 drives required on the SAN (if booting from SAN). You don't have to use those drive letters, I have provided those as an example configuration. You could for example use mount points for your shared LUNs and only have one drive letter per instance (this is what I do).

Hope this helps.