Migrating from SQL 2000 to SQL 2005

By | August 31, 2007

It’s probably a bit of a weird post. Especially since SQL Server 2008 is already anounced. But in the company I work for we are only now making the switch from SQL Server 2000 to SQL Server 2005.

So here are some of the things that I encountered:

  • There are no incompatabilities in the database engine itself. So that is smooth sailing, thank god!!!
  • The Analysis Services are completely different, which means you will have to rewrite all cubes and dimensions.
  • If you’re using PivotCharts and Excel to connect to your cubes you’re gonna need to rewrite huge parts of them

Let me explain point two. Analysis services has the capability to migrate all the cubes you had in Analysis Services 8, but since the service has changed so much it’s useless.  After the migration you won’t be able to edit the cubes any more. So you’re better of starting from scratch.

I’ve been working on the migration non-stop for three weeks now. It’s mostly that slow because I have to process the cubes every time I make a change to fix a bug. And for some cubes this takes a few hours.

2 thoughts on “Migrating from SQL 2000 to SQL 2005

  1. Vidas Matelis

    You said you have to process cubes every time you make a change. That is very true in many cases. But if you do changes just to MDX calculations tab, try BIDS Helper utility (free, you can find it on codeplex). I found it very helpful as it deploys MDX changes very fast. I am sure you already set Deployment Mode to “Deploy changes only” and Processing option to “Default”.

    But as you said, SSAS 2000 and 2005 are completely different products. It took me about 6 months to do my project migration, as structure had to be completely redesigned.

  2. Jongerius Post author

    Thanks for your advice on the MDX calculation changes. That sure reduces time alot.

    As it is I have converted almost all cubes to SSAS 2005, and only two of them are reporting different numbers to the old SSAS.

    But this is most likely a mistake made by creating duplicate entries in a foreign key relationship.


Leave a Reply