Side effects of storing an Analysis Services partition data in a non-default location

In 2005 and 2008, SQL Server Analysis Services allows users to store partition data in a non-default location. That is something we could not do in 2000 version. To store a partition data in a non-default location means that you can have the partition storage location set to some folder other than the default data folder (see the screen shot below). Some users choose to use the feature because it can off load some IO to different drives. It helps balancing IO activities. Others choose to use it simply because they don’t have enough disk space in the default location.

Indeed, storage location is a wonderful feature that provides us flexibility.  However, in order to make good use of this feature option, you  need to be aware its side effects:

1. Synchronization: In normally scenario, synchronization can perform incremental synch between 2 servers. That's means only the data that is affected will be synchronized. However, if your partitions storage are at non-default locations. Every time you sync, it will sync all the partitions data regardless any changes at all. This known behavior can have negative performance impact, especially for some very large databases.

2. Backup/Restore: if you have non-default location partitions within a database backup, you will need to restore the database to another non-default location. You can't restore its partitions back to the default data file folder. The only way to move the database back to the  default file location is to redeploy the project without setting the storage location in each partition properties. And then re-process the whole database to get back all the data files.

If you have accumulated couple hundred GB of partition data, reprocessing is not always that easy. Knowing the side effects ahead, it can help you make a better architecture decision.

C S John Lam | Premier Field Engineer - SQL Analysis Services