none
PowerPivot for SharePoint - change Analysis Services Configuration Data Directories location RRS feed

  • Question

  • Hello, 

    We have an existing 'PowerPivot for SharePoint' installation in SQL Server 2012. We are consolidating our data drives, and need to change the data directory locations (data, Log, Temp, Backup). How should I do that without losing existing data?


    Marcel



    Thursday, December 6, 2018 6:35 PM

Answers

  • After changing the drive location of just the BackupDir (not DataDir), and after restarting powerpivot instance of analysis services, I noticed these databases were no longer there!?

    Performing a PowerPivot refresh (Manage PowerPivot Data Refresh), recreates these databases. So it looks like there is no need to move these databases manually.



    Marcel

    Friday, December 14, 2018 4:43 PM

All replies

  • Further investigation shows we have 2 instances of Analysis Services - the default instance and the PowerPivot instance. You can see which you have by using SSMS and connecting to Analysis Services - the dropdown shows your instances.

    Open SSMS as an administrator; connect to your analysis services instance(s);

    right click the server at the top of your object explorer and click on Properties and then 'General', select 'Show Advanced Properties' to see the locations of: AllowedBrowsingFolders, BackupDir, DataDir, LogDir and TempDir.

    In addition to changing theses defaults, by following the directions in the links mentioned previously, we also need to move any existing Analysis Services databases that already exist. https://blogs.technet.microsoft.com/mdegre/2012/10/03/moving-ssas-database-to-a-new-drive-on-same-server/ and  https://docs.microsoft.com/en-us/sql/analysis-services/multidimensional-models/move-an-analysis-services-database?view=sql-server-2017 

    Open SSMS as an administrator; connect to your analysis services instance(s); 

    Expand the databases folder - to see which databases will need moving 

    PROBLEM: when copying the existing PowerPivot databases files to their new location I get "the source file names(s) are larger than is supported by the file system." - the only solution I have found is to shorten the path of the target - trying this ('E:\MSSQL\MSAS11.POWERPIVOT\OLAP\Data' is now 'f:\data' and I continue to have the issue - ANY SUGGESTIONS?


    Marcel



    Wednesday, December 12, 2018 6:10 PM
  • After changing the drive location of just the BackupDir (not DataDir), and after restarting powerpivot instance of analysis services, I noticed these databases were no longer there!?

    Performing a PowerPivot refresh (Manage PowerPivot Data Refresh), recreates these databases. So it looks like there is no need to move these databases manually.



    Marcel

    Friday, December 14, 2018 4:43 PM