Centralized vs Decentralized Datasources in Reporting Services


By:   |   Updated: 2008-02-11   |   Comments (1)   |   Related: > Reporting Services Data Sources

Problem

The Reporting Services datasource is a programming object that holds the configuration information on how to access data for reporting. This includes the server name, database name, the type of data source (i.e. SQL Server, Oracle, text file, etc.) and the credentials of the user accessing the data.

Reporting Services offers many ways to use datasources. They can be localized (embedded) within a report or they can be "shared" (usable by more than one report). When shared, they can be decentralized within a single project (and report folder) or they can be utilized centrally amongst multiple projects and folders.

What is the best method to use?

Solution

In order to decide on the usage of centralized vs. decentralized Datasources in Reporting Services, an analysis of the pros and cons of each type can be helpful for decision making purposes. Based on the information decide which type best fits a particular situation.
 

Pros Cons
Embedded in the report
  • Easily transportable - Can be sent or copied as a stand-alone file
  • Must recreate for every report.
  • If there is a server change (i.e. in a disaster) each report must be reconfigured.

Shared - same folder as reports

  • Centralized control within a solution project.
  • Can change configurations independently from other solutions. (i.e. toggling 'development' vs. 'production')
  • If there is a need to change to an alternate server for an emergency, changes need so be made in multiple places if there are multiple projects.

Shared - centralized location for multiple projects/folders

  • Data connectivity can be configured centrally from a common location. For example, in the event of a disaster or planned server change, all connectivity can be changed in a single location with minimal effort.
  • If you need to toggle back and forth between servers for any reason, you cannot do this without effecting all reports that use the datasource.

Summary

There is no one-size-fits-all solution that fits every situation. With some additional effort, some of the Pros and Cons can be mitigated by using multiple configurations in the Configuration Manager.  Whatever you decide, understanding these issues will help you choose the best datasource type to use for your situation.

Next Steps


Last Updated: 2008-02-11


get scripts

next tip button



About the author
MSSQLTips author Rob Fisch Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, May 08, 2009 - 12:38:15 PM - mehul Back To Top

Hi, how do I use the third option you've mentioned? (Shared - centralized location for multiple projects/folders)

 Thanks



download


Recommended Reading

Creating a SSRS report using a mySQL data source

Developing a SSRS report using a SSAS Data Source

Implement Dynamic Data Sources in SQL Server Reporting Services

Using JSON as a data source for a SQL Server Reporting Services report

Working with Report Snapshots in SQL Server Reporting Services SSRS





get free sql tips
agree to terms


Learn more about SQL Server tools