February 26, 2014 at 1:33 pm
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
February 26, 2014 at 8:37 pm
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
Consultant - Straight Path Solutions
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
February 27, 2014 at 12:20 pm
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
February 27, 2014 at 12:25 pm
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
February 27, 2014 at 12:43 pm
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
Consultant - Straight Path Solutions
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
February 27, 2014 at 1:09 pm
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
March 4, 2014 at 5:28 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply