Migrating DB, ETL, Reports, Cubes from SQL Server 2005 to SQL Server 2012. Need to know the challenges? RRS feed

  • Question

  • Hi All,

    We have plan to Migrate

    • SQL Server DB from 2005 to 2012
    • SSIS ETLs from 2005 to 2012
    • SSRS Reports from 2005 to 2012
    • SSAS Cubes from 2005 to 2012

    I would like to know the challenges faced by other people during Migration. I have main concern about SSRS Reports & SSAS Cubes. Is SSRS report developed in 2005 can be opened directly in 2012?

    Please suggest if any migration Tools that can help us in doing the same.

    Thanks Shiven:) If Answer is Helpful, Please Vote

    Wednesday, March 20, 2013 12:36 AM


  • Hi,

    Generally speaking, due to backward compatibility support in higher versions of the product, most of the functionality should work out-of-the-box, but certainly, there are some considerations and maybe additional work needed that you should take in count before migrating.

    Database Engine:

    * Everything should work except your are using some deprecated functionality in SQL2008 and SQL 2012. Most of the standard features / objects (tables, views, SPs, triggers, etc.) are fully compatible. Deprecated features for 2008 and 2012 can be found at http://technet.microsoft.com/en-us/library/ms143729(v=sql.100).aspx and http://msdn.microsoft.com/en-us/library/ms143729.aspx respectively.


    * From the MSDN documentation http://msdn.microsoft.com/en-us/library/cc280546.aspx: "When you run a SQL Server 2005 or SQL Server 2008 package using the dtexec utility (dtexec.exe) that is installed with the current release of SQL Server, the temporary package upgrade increases the execution time. The rate of increase in package execution time varies depending on the size of the package. To avoid an increase in the execution time, it is recommended that you upgrade the package before running it."

    * SSIS 2012 brings a new deployment model called "Project Deployment Mode" which a lot of new functionality. You can migrate your packages to this new deployment mode (the default one) but in addition you may have need to redesign some functionality to map it to the new features (parameters, project level variables, shared connections, etc.). You can also select the Legacy Deployment model to maintain your packages in a similar way as today but you will loss the new functionality that at the end of the day are the justification of migrating to a new version of the tool.

    * In any of the upgrade options, you will need to change your connectionstrings in case you use Native Client connectivity to SQL Server and re-compile all the script task to use the new version of the .NET Framework.


    * You will need to migrate the server side and the reports http://msdn.microsoft.com/en-us/library/ms143747.aspx. For reports, it should be as easy as opening the reports in the new SQL Server Data Tools (the new version of Business Development Studio). For the server side migration, there are some considerations such as minimum requirements (2005 SP4).

    * Starting with SSRS 2008, you can also have 2 deployment modes of SSRS: SharePoint Integrated Mode and the default Stand Alone mode.


    * SSAS 2012 introduces a new server mode called "Tabular Mode". The 2005 and 2008 modes (UDM) are now called "Multidimensional Mode". Both models are now known as Business Intelligence Semantic Model (BISM).

    * Assuming you will stay in multidimensional mode, the wizard will popup if you open your 2005 project in SQL Server Data Tools and will migrate your project to 2012 automatically.

    If you have any other specific question, please let us know.


    Microsoft Business Intelligence and more...

    Alan Koo Labrin | "Microsoft Business Intelligence y más..." http://www.alankoo.com

    • Proposed as answer by Alan Koo Wednesday, March 20, 2013 7:40 PM
    • Marked as answer by Allen Li - MSFT Wednesday, March 27, 2013 9:38 AM
    Wednesday, March 20, 2013 7:40 PM