Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Design Question: What is the best practice for reporting on data that spans instances?


Design Question: What is the best practice for reporting on data that spans instances?

Author
Message
shew
shew
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
We are using SQL Server 2012 SP1, and we have around 30 instances of the database engine installed. We collect performance data throughout the day, and each instance currently stores its own data in its own "dba" database.

At some point, we would like to create some Reporting Services reports that display data from the entire database farm, but we are not sure how to proceed. So far, these options come to mind:

1) Use dynamic data sources so that a single report can connect to the desired database (via a drop down parameter list) at report runtime (this is what we are currently using, but it is awkward to switch between instances over and over again--it would be great to see the whole database farm represented on a single report run)

2) Copy the desired tables from each individual "dba" database to a consolidated database on a daily basis and report on all of the instances using the consolidated data (this sounds like a lot of effort, and I don't want to create something that will be difficult to maintain down the road)

3) Potentially use some unknown (to us) Reporting Services feature that allows a report to consume data from multiple instances without creating a maintenance nightmare of managing lots and lots of data sets

What do other DBAs do for this kind of situation?

By the way, Linked Servers are highly discouraged by our Data Security folks, and we cannot use a non-Production Linked Server with a Production server, or Production Linked Server with a non-Production server, which means that Linked Servers will not be able to present the whole database farm to a report for us.

J



Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
I think option #2 is probably the simplest option since Linked Servers aren't an option. Implementing option 2 could be as simple as a single SSIS package that runs on the central server and consolidates the data. AS long as the table(s) you are consolidating are consistently named, you could have a table in the central server where you store a list of servers to collect the data from and then use a loop in SSIS to connect to each of the servers and load the data, then when a new server is built out, all you have to do is add a row to this table to add it to your consolidation job.

You should search for Rodney Landrum DBA Repository as that might be the kind of template you'd want to follow.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
shew
shew
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
Jack Corbett (2/26/2014)
You should search for Rodney Landrum DBA Repository as that might be the kind of template you'd want to follow.


Thanks for the tip. I found some Rodney Landrum links which I am pasting below in case they would be helpful to anyone.

http://sqlmag.com/sharepoint/readers-and-authors-swap-ideas (which points to the following links)

http://sqlmag.com/sql-server-integration-services/sql-server-integration-services (goes through the creation of a DBA Repository database, which is what we are attempting to do as well)

http://sqlmag.com/sql-server-reporting-services/sql-server-reporting-services

http://sqlmag.com/sql-server-integration-services/use-ssrs-and-ssis-create-dba-repository

http://sqlmag.com/sql-server/dba-repository-2010

J



shew
shew
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
Jack Corbett (2/26/2014)
You should search for Rodney Landrum DBA Repository as that might be the kind of template you'd want to follow.


Jack,

Since you are familiar with Rodney's DBA Repositiory, have you ever seen the following error:

TITLE: Package Validation Error
------------------------------

Package Validation Error

------------------------------
ADDITIONAL INFORMATION:

Error at Backup History [OLE DB Source [46]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "MultiServer" failed with error code 0xC0014041. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error at Backup History [SSIS.Pipeline]: OLE DB Source failed validation and returned error code 0xC020801C.

Error at Backup History [SSIS.Pipeline]: One or more component failed validation.

Error at Backup History: There were errors during task validation.

Error at Populate_DBA_REP: The connection manager "MultiServer" will not acquire a connection because the connection manager OfflineMode property is TRUE. When the OfflineMode is TRUE, connections cannot be acquired.

(Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------



I am getting it in the Multiserver object of the Populate_DBA_REP.dtsx package.

I have googled to no avail. I don't see anywhere to change "the connection manager OfflineMode property." OfflineMode is set to False, and looks grayed out.

Any ideas?

J



Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11014 Visits: 14858
I think that the offline property is a package property pre-SSIS 2012, so you need to look for that property on the package itself.



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
shew
shew
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
Jack,

Sorry... I only see it on the MultiServer connection, and it is False and grayed out. I tried creating a new MultiServer connection, but it defaults to False and grayed out too.

Not sure where else to look.

J



shew
shew
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 929
I'm not sure if this helped or not (because I found some additional links posted below at about the same time), but the description matched my error and the syntax was hard to find. So, I thought I would post it in case it may be useful to someone in the future.

http://www.lifeasbob.com/2008/07/31/DestinationConnectionExcelAndOfflineMode.aspx

The NT-DOS syntax (which requires Admin rights) for SQL Server 2012 at our site is:

%windir%\syswow64\regsvr32 "C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dts.dll"


However, I finally got an SSIS loop to work across instances with the following two links:

http://thesqldev.wordpress.com/2013/02/20/iterate-over-databases-using-ssis-part-1/ (this page misses a step--you need to define *two* variables, "User:BigGrinatabaseName" *and* "User:BigGrinatabaseId", as readonly, not just one)

http://thesqldev.wordpress.com/2013/03/23/iterate-over-databases-using-ssis-part-2/

J



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search