Martyn

Martyn Bullerwell's Blog

SQL Server 2008 Feature Pack

In April I wrote about Reporting Services in SQL 2008 with MOSS 2007 (here), and also a small article on the SQL 2008 feature pack February CTP.  This was all tested on the February CTP, of SQL 2008.  The feature pack is required to get the Add-In for SharePoint Integration.  Andrew has beaten me too it and written a great reference article on the latest RC0 version of SQL 2008 Feature Pack that can be found here.  Between PPS SP1 and the RC0 release, I hope to get round to testing all the new releases as soon as possible.   

 

SQL Server 2008 RC0 and Performance Point SP1

What a treat, I am sure you have all already heard the SP1 has been released for PerformancePoint, which hopefully will enable us to run PerformancePoint on SQL Server 2008.  As well as this it seems that RC0 has been released on TechNet for SQL Server 2008.  I will be getting an environment for testing set up as soon as possible and begin testing compatibility.  Details of availability of this can be found on Andrew Fryer's blog: http://blogs.technet.com/andrew/archive/2008/06/06/sql-server-2008-rc0.aspx

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:

clip_image001

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: 

clip_image002

clip_image003

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.

clip_image004

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. 

Enterprise Library Version 4 and Visual Studio 2008 Service Pack

Have both been released this month!  Visual Studio 2008 Service Pack 1 (As a beta) can be found here:  http://msdn.microsoft.com/en-us/vstudio/products/cc533447.aspx  It includes some enhancements, however most noticeably the improvements in event wiring up in WPF, between the design view and the code behind.

Enterprise Library 4 can be found here: http://msdn.microsoft.com/en-us/library/cc512464.aspx It is for .NET 3.5 and Visual Studio 2008, so fits well with the release of SP1.  The enhancements can be found here: http://www.codeplex.com/entlib/Wiki/View.aspx?title=EntLib4%20Backlog.

SQL Server 2008 Exams

Microsoft Exam 70-446, gave the opportunity to get MCITP status with Microsoft Business Intelligence stack.  This built on the achievement of 70-445, which gave a MCTS.  On searching around for the latest information on SQL Server 2008, I stumbled across this very useful PowerPoint from Microsoft, which outlines the Certification routes for 2008. 

http://download.microsoft.com/download/3/1/9/31972631-4776-448a-9379-8131e27a7eb2/LiveMeeting_SQL_Certification_February_2008.pptx

You may notice that there is no information on a equivalent for 70-446 as yet, which means for BI developers, that want the MCITP, getting 70-446 is still the only way forward.  It looks like these exams will be available from August, and possible at a discount rate to get some people taking them. 

PerformancePoint Service Pack 1 - Not Quite Yet

The rumors were that the service pack for Performance Point (SP1) were that it was due to be released in May.  This looks like it was not correct, apologies for the information in my previous post being incorrect.  We will just keep our eyes peeled for the release and report on this as soon as it comes in, I’m confident it’s well on its way and due any day now.

 

.NET 3.5 Installation Problems

Recently I have had to install .NET 3.5 on a few machines with varying success.  I have found that it is best to download the entire package, prior to installation - this can be found here: .NET Framework 3.5 full package.

Upon installation I found that the package, on certain machines on Windows XP, hangs on the following screen:

   image

Now although this seems a little strange, I found that removing the network cable, therefore disconnection from the internet, kicked the internet back into life.  Before doing so, wait for the installation to hang, this can be done by keeping an eye in task manager and when all activity stops, just disconnect any connection to the network/internet and then reconnect when the next screen appears.

SQL Server 2008 White Papers

Microsoft have released a raft of White papers on SQL 2008.  These can be found here: http://www.microsoft.com/sqlserver/2008/en/us/white-papers.aspx.  Although fairly high level the first business intelligence white paper outlines some of the significant changes made to 2008, in order for it to better compete in the Data Warehousing space.

There are 6 separate white papers dedicated to Business Intelligence alone.  The introduction to new Data Warehousing features has a great deal of information on new features that will further enhance the BI offerings from Microsoft.  This document includes details on the following enhancements:

SQL Server Relational DBMS DW Improvements:

  • Star Join
  • Partitioned Table Parallelism.
  • Partition-Aligned Indexed Views
  • GROUPING SETS
  • MERGE
  • Change Data Capture
  • Minimally Logged INSERT
  • Data Compression
  • Backup Compression
  • Resource Governor

Integration Services Improvements:

  • Lookup Performance
  • Pipeline Performance

Analysis Services Improvements:

  • MDX Query Performance: Block Computation
  • Query and Write back Performance
  • Analysis Services Enhanced Backup
  • Scalable Shared Database for AS

Reporting Services Improvements:

  • Reporting Scalability
  • Server Scalability

SQL Server 2008 Reporting Services Integration with MOSS 2007

So, time to see how SQL Server 2008 Reporting Services gets on with integration with MOSS 2007.  It all starts very well, SQL 2008 install has been redesigned, and all for the better.  Makes it much easier and consistent to install and all in all very good. 

clip_image001 clip_image002

The Reporting Services set up only gives you a few options on install.  The following screen shot gives an idea of how simple it is, and allows you to be prepared to integrate with MOSS straight out of the box:

clip_image003

This all goes very well , and installs smoothly (may be worth mentioning that our test environment has SQL 2005 on it, and therefore 2008 was being installed as an instance that also went smoothly).  This is unfortunately where the pleasure ends, and the pain begins.  On opening SQL manager, it seems that it wants to set up Reporting Services straight away, however falls over with a raft of errors - as this is still a CTP, I'm sure MS are resolving this!  The first question you get is the following:

clip_image004

Beyond it falling over the first time it runs, subsequent attempts result in nothing at all happening.  However do not panic, the reporting services configuration manager still exists.  This has been cleaned up somewhat, but in essence it appears to be the same tool.  Confirm that this is set up correctly by clicking on the URL in the Web Service URL part.  This should go to a basic page with as if you are looking at the file view. 

Next, if you haven't already done so - install the Microsoft SQL Server 2008 Feature Pack - can be found here for download:  http://www.microsoft.com/downloads/details.aspx?FamilyId=D68DE3C9-60A9-49C9-A28C-5C46BBC3356F&displaylang=en

The file you are going to need is called rsSharepoint.exe, download this and install it on your SharePoint 2007 Server.

Now, if you are lucky, unlike me, you will be able to go the Central Administration Console and under "Application Management" you will see a reporting services tab, if you do then great, but if you do not have it - don’t despair this is quite common.

Go to the Central Administration console for SharePoint services, and click site actions on the top right.  The Select Site Settings, and click Site Collection Features, you may see a whole host of options, depending on what you have already installed.  Look for "Report Server Integration Feature" and click activate.  All done, now you will see a Reporting services section in Application management. 

clip_image005

Ok, so we have reporting services set up and running in integration mode, and have SharePoint able to connect to the Report Server.  Get the address of the report server, ensuring that it is not pointing to local host.  Now Click "Manage Integration Settings" in Application management of SharePoint Central Administration:

You can either use Windows Authentication, or Trusted account, note that if you want to use Windows authentication you may need Kerberos set up for cross server authentication.  Also If SharePoint is configured to use Forms Authentication, this setting will be ignored.

clip_image006

Now you need to grant database access, this allows the report server to access the SharePoint Content Database, and needs both read and write permissions to the content database.  Once this is done you are ready to go. 

PerformancePoint Service Pack 1 - Not long now!

I stumbled across this little nugget of information today http://bimvp.com/blogs/bsm/archive/2008/04/07/performancepoint-server-2007-service-pack-1.aspx, stating that the PPS Service Pack 1 is now in Beta, and also hinting that it's release should be in May (If I read it right, the 5th of May).  Its compatibility with Windows Server 2008 is said be fixed, and the following page highlights what is, isn't and will be, supported by Windows Server 2008; http://support.microsoft.com/kb/948680.  You will find PPS in the "Applications that will be compatible in the future", stating SP1. 

More to come on this in May hopefully!