In SQL Server 2012 Have a fact table that needs partitioning. Lets say for simplicity columns are CustomerID, OrderID,OrderDateID,ProductID, Items,Cost . Default partitioning strategy for the physical drives used to be partitioning by months/years of the date column (in the fact table in the example was OrderDateID). How is the recommended strategy for partitioning data on the SAN?
In most cases the same.
SAN offers you much better up time, recovery and often better performance, but core mechanics is the same.
You can have more complicated rules for assigning LUNs to file groups, and if you have layered SAN you may consider using fastest ones for current and recent partitions and slower ones for historical partitions. It all depends on data usage patterns specific for your installation.
Hi Piotr, thak you for the response. I do have one SAN that is being used as storage for the data. My question is since one disk will be created by a layer cut from a multitude of SAN disks, but the SAN controller is the same , what is the benefit to split the data on different logical disks layered from the same disks from the SAN ? Lets say , I end up with 10 intervals for the date partioning , and all these partitions are created as logical cuts from the same SAN phyisical disks allocated to the environment, is it a real benefit in creating logical drives and associating partitions to these drives.