Upgrading Reporting Services Project to SQL 2008

Sometime ago Adatis undertook a small project with Reporting Services, running on SQL 2005.  This project is an ideal candidate to test SQL 2008.  Recently I have had many people asking me about SQL 2008 in the BI space, as our clients are starting to look at this new version.  A key requirement for many clients will be that their existing databases and reports will transfer smoothly to 2008.

So, to begin we must import the SQL 2005 database.  Here, at first, we fail at the first hurdle.  You cannot simply back up a SQL 2005 Database and Restore it to SQL 2008 (so it seems)  – After numerous attempts and fiddling – the following error kept returning to haunt me:

So, as an alternative I tried the Copy Database Wizard.  This was not without issues, these included security issues, and then Issues with creating certain views, and security.  This is because it simply uses scripts to do the tasks in the background so the object have to exist for the Database to be successfully created.  Therefore – at present – there is no easy way to get a SQL 2005 database onto the SQL 2008 platform; however it is achievable with a little tinkering.  One of our favorite tools, (RedGate) would probably aid with this.

After a complete rebuild of the server, with no Side by Side installation of SQL Server 2005, 2008 now allows restores of 2005 databases – phew…  So, given we have a well set  up environment – we can simply restore SQL 2005 Databases to SQL 2008!

To be completely up to date, I will use Visual Studio 2008, to open our reporting Services project.  First thing this tries to do is Update the project, now at this point it is wise to have either backed up and taken your local copy offline (disconnect From Source Safe) or to have branched the code. At a Minimum – ensure that you label the project in source control before the update.

The Upgraded to 2008 should go pretty smoothly. And the First thing to do, after the update, is to connect your project to the new SQL 2008 Database, and build the project – if your project built successfully in 2005, it should build successfully in 2008 too. Now if you try and open a report you may get following message:

It seems that although we converted the project successfully – each RDL also need converting.  Once all your RDL’s are correctly updated, check each report in the Preview pane.  If these work – then you are there.  All that’s left to do if deploy to Reporting Services 2008, which is done in exactly the same way as before.

There are a few fundamental changes between developing RS in the new version.  One of the most obvious in the number of tabs in the report designer window.  The Data Tab seems to have gone in BIDS 2008, making very difficult to manage the data for each report.  At present the only way i can see to create multiple datasets for a single report is in the code behind.  However once the Data sets have been configured for each report – getting the data from each Dataset is a doddle:

The integration with Dundas seems to have bought about property panes for setting up all the aspects of the reports, and also a greater selection of Charts, and new to 2008, Gauges – which has been part of the Dundas suite for some time now.

Also, there is no “debug” way of running the reports, like there was in 2005 – to see the whole thing in action the reports must be deployed.

So, to summarise – we can import and deploy SQL server 2005 Reporting Services reports and re-deploy them onto Reporting Services 2008 without too much hassle at all.  However, judging from BIDS 2008 it is not ready to be used as a development platform, as certain “simple” tasks have been complicated.  We must remember that SQL Server 2008 is still in CTP from February so development is a good 3 months on already – and with the enhancements to the look and feel of the reports and the performance increase, Reporting Services on 2008 should be a worthwhile investment.