Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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




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


Learn more about SQL Server tools