Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Design Question: What is the best practice for reporting on data that spans instances? Expand / Collapse
Author
Message
Posted Wednesday, February 26, 2014 1:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:05 AM
Points: 391, Visits: 795
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



Post #1545568
Posted Wednesday, February 26, 2014 8:37 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #1545678
Posted Thursday, February 27, 2014 12:20 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:05 AM
Points: 391, Visits: 795
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



Post #1546064
Posted Thursday, February 27, 2014 12:25 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:05 AM
Points: 391, Visits: 795
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



Post #1546068
Posted Thursday, February 27, 2014 12:43 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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

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
Post #1546082
Posted Thursday, February 27, 2014 1:09 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:05 AM
Points: 391, Visits: 795
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



Post #1546096
Posted Tuesday, March 4, 2014 5:28 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 6:05 AM
Points: 391, Visits: 795
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::DatabaseName" *and* "User::DatabaseId", as readonly, not just one)

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

J



Post #1547262
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse