Search code examples
sql-serversql-server-2005sql-server-2012ssasbusiness-intelligence

Migrate SSAS Database from MSSQL 2005 to 2012

Want to upgrade SQL Server Analytic Services (SSAS) all cubes and dimensions from MSSQL-Server 2005 to 2012, Along with the SSAS Database data.

I have already upgraded the SSAS Project in Visual Studio(SSDT) using Upgrade Adviser, But I am not sure whether this is the right way or not?

So, please guide/suggest to upgrade SSAS Database from 2005 to 2012. Also is it possible to move Directly from sql-server 2005 to sql-server 2012? or i need to go step by step from 2005→2008→2012 ??

Please guide. Thank you in advance.

Solution

  • It's ok to move from 2005 directly to 2012.

    I usually do this way:

    • create XMLA backup on 2005
    • restore it on 2012

    Two possible issues I faced several times:

    1. if XMLA is not restoring because of "preserve null" (do not remember exact name) for Distinct Count measure -- change this option to "Automatic" in XMLA directly and re-run again. has to work.
    2. for huge cubes (1TB+) I also switch off all calculations (just comment them out). because it is validating at the end of processing and roll everything back if any calculation is not valid because of upgrade.