I was recently involved with the migration of a Native mode SQL Server Reporting Services (SSRS) database from a non-domain server to a domain-based server. The migration itself was fairly straightforward (as it usually is) thanks to the inherent portability of the Report Server database and required configurations, such as encryption keys and .NET config files. However, after getting everything up and running on the new server, and successfully testing the reports manually, I discovered that the report Subscriptions were failing.

Sooner or later, with any SQL Server database of a suitably large scale, you’re going to run into a situation where you start hitting hardware limitations. It may be disc space, it could be IO issues caused by concurrent access of multiple filegroups and databases on the same controller. Either way, you’re likely to hit the point where you make the decision that you need to move some filegroups around to distribute the load across several drives. Alternatively, you may just want to reorganise your DB files into a more logical structure.

This is very straightforward to do normally via backup and restore, or simple detach and attach.  However, when your database is under replication, either as a publisher or subscriber, this simple process becomes a little more difficult.  Thankfully, there’s a very simple solution to this, which I came across the other day while spreading our DB across multiple drives.

I was asked recently how one could use SQL Server Reporting Services (SSRS) Report Builder construct a report using a SQL Server Analysis Services (SSAS) cube as it’s data source.  The good news is that is just as simple as connecting to a SQL Server database, there’s really very little difference, at least if you using the GUI route.

The 2012 version of SQL Server has brought with it a number of new features, not least the new, Visual Studio 2010 shell-based Management Studio. However, the changes are more than just aesthetic, as I found out when I came to try and upgrade some SQL Server Integration Studio (SSIS) packages from SQL server 2008 to 2012. Most packages upgraded fine, but there were some issues when I tried to upgrade any packages that used Script Tasks containing references to external DLLs.

I’ve been looking at migrating some SQL Server Integration Services (SSIS) packages lately, and as part of the move, am parameterising a lot of settings to improve the ease of future configuration changes.  One of these changes involved a package that reads from an XML source and uses an XML Schema Definition (XSD) file to validate the source file.  However, with the XML source being in the data flow, you’re not allowed to set an expression on this property to assign a value at runtime.  Or at least not directly.  So, if you want to assign the XSD location from a variable or project parameter, just follow these simple steps.

Just a quick one for a Wednesday afternoon.  I was recently asked if it was possible to enable remote errors in SQL Server Reporting Services (SSRS), so that people (and applications) could return full error details from RDL execution, without having to consult the local log files.  It’s an incredibly easy task to enable this.  Here’s how:

So, you’ve finally got around to setting up that SQL Server 2012 environment and you’re playing about with PowerPivot and SQL Server Analysis Services.  You’ve set up your sharepoint site and you’ve created and uploaded a new spreadsheet using PowerPivot that contains all that tasty cube data.  However, in your haste, you haven’t set up SQL Server Reporting Services Report Builder on the Sharepoint site, so when you open Report Builder on a separate server (or from a local install), and want to connect into your new quasi-cube (via your PowerPivot Excel file), how do you do it?  The Report Builder side of things is not too dissimilar to using a traditional SSAS cube as a Data Source.

The answer, is deceptively simple if you know where to look.

As I mentioned in my last post, I was recently over in Dublin attending SQL Saturday 105, a SQL Server conference and networking event hosted by PASS.  For anyone considering attending a future SQL Saturday event, I can definitely recommend it for developers, Database Administrators (DBA) and Business Intelligence (BI) developers of any level, from enthusiastic amateurs all the way to expert consultants.  Given the fortunate timing on Saturday’s event, it also doubled as the European technical launch for SQL Server 2012 (codenamed Denali during development).

Featuring a total of six sessions covering 3 tracks (Developer, DBA and BI), the event was packed, as over 200 SQL Server professionals from all over Ireland (as well as our intrepid 3 from Edinburgh) flocked to learn about some of the new features of SQL Server 2012 or just to pick up some tips and tricks from some of the industry’s best.