Centralized vs Decentralized Datasources in Reporting Services

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

Try making a localized datasource connection using some or all of the configurations set as parameters.
For an example of parameterized datasource connections, download the solution from the Reporting Services Report - Determining Free Space Per Database to see some examples.
Check out all of the Reporting Services tips on MSSQLTips.com!

About the author

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

Article Last Updated: 2008-02-11

Comments For This Article

Friday, May 8, 2009 - 12:38:15 PM - mehul

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

Thanks