Centralized vs Decentralized Datasources in Reporting Services

By:   |   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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Friday, May 8, 2009 - 12:38:15 PM - mehul Back To Top (3342)

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

 Thanks















get free sql tips
agree to terms