Database Storage Location
Applies To: SQL Server 2016
There are often situations when an Analysis Services database administrator (dba) wants a certain database to reside outside of the server data folder. These situations are often driven by business needs, such as improving performance or expanding storage. For these situations, the DbStorageLocation database property enables the Analysis Services dba to specify the database location in a local disk or network device.
The DbStorageLocation database property specifies the folder where Analysis Services creates and manages all the database data and metadata files. All metadata files are stored at the DbStorageLocation folder, with the exception of the database metadata file, which is stored in the server data folder. There are two important considerations when setting the value of DbStorageLocation database property:
The DbStorageLocation database property must be set to an existing UNC folder path or an empty string. An empty string is the default for the server data folder. If the folder does not exist, an error will be raised when you execute a Create, Attach, or Alter command.
The DbStorageLocation database property cannot be set to point to the server data folder or any one of its subfolders. If the location points to the server data folder or any one of its subfolders, an error will be raised when you execute a Create, Attach, or Alter command.
DbStorageLocation specifies the folder where all the database data and metadata files reside, whereas StorageLocation specifies the folder where one or more partitions of a cube reside. StorageLocation can be set independently of DbStorageLocation. This is an Analysis Services dba decision based on the expected results, and many times the usage of one property or the other will overlap.
The DbStorageLocation database property is used as part of a Create database command in a Detach/Attach database commands sequence, in a Backup/Restore database commands sequence, or in a Synchronize database command. Changing the DbStorageLocation database property is considered a structural change in the database object. This means that all metadata must be recreated and the data reprocessed.