Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

Upgrading SQL Server Business Intelligence

Continuing my series of posts on upgrade, I thought it might be good to have a look at the business intelligence components of SQL Server, so here’s a simple grid that I have adapted from Scalability Experts for use in the upgrade workshops and presentations I give:


Along the top is the upgrade path e.g. SQL Server 2000 – 2005, with the various components down the left hand side.  Green is good red means significant re-work while amber is somewhere in between.

Analysis Services had a complete redesign in SQL Server 2005, and although you can convert a cube during upgrade the result will not be an optimal design or make best use of the powerful new features in SQL Server 2005/8.  SQL Server 2008 just has extra features on top of 2005, such as performance improvements and better design tools.  So in my opinion you need to completely redesign your cubes, and then adapt the calculated members to account for the subtle changes in MDX in SQL Server 2005.

Reporting Services was developed for SQL Server 2005, but then bought out for SQL 2000 before 2005 was released.  The basic story here is that reports can be upgrade all the way along form 2000/5/8 but if you have been using custom security or making calls to the 2000 SOAP API then there this work will need to be redone as the architecture of SQL Server 2008 is completely different e.g. there is no dependency on IIS.

Data Transformation Services (DTS)  was replaced by Integration services (SSIS) in SQL Server 2005.  You can still run DTS packages in SQL Server 2005/8 as they were but there were a number of reasons for the replacement:

  • Performance SSIS runs in memory in the same way as many other tools of this type and so is much faster.
  • Manageability.  It was difficult to debug DTS packages and it was quite difficult to move them around
  • Flexibility.  Loops and other control flows were very difficult to get working in conjunction with good error reporting.
  • Collaboration. Only one person could work on a DTS package at a time to the point that if you left the package open it would prevent it being run by someone else.
  • SSIS Fixes all that and all that has happened in SQL Server 2008 is that certain type of process are much faster and you can write your own transformations in C# using Visual Studio Tools for Applications (VSTA).

Of all of these it is DTS that is the most widely used and is the biggest barrier to upgrading.  The interface and approach to design is very different and the huge amount of work that has gone into to DTS are the other contributors to this.  DTS packages can be converted to SSIS during a SQL Server upgrade and this will work for about 70% of the time.  Of course it is the most complex packages that won’t upgrade; those with custom activeX transformations for example. 

So as I posted a few days ago you could leverage the power of DTS xChange or continue to run the packages as is until they need to be changed anyway.  There are intermediate options like calling a DTS package from SSIS, but if you are going to start to do any work like this wouldn’t it make sense to start with a clean sheet of paper and leverage the power of SSIS form the outset?