January 7, 2008 at 2:37 pm
Thanks for sharing this with us. I had developed something pretty complicated to monitor my database instances. Not only have I now gotten more experience with SSIS, but I have a feeling your following articles will show me how to monitor them easier. Thanks!!!
January 7, 2008 at 7:18 pm
Got everything working great. Just wondering if in your future articles that you will be adding current dbsize, logsize and maybe when the last backup was conducted. I see alot of potential for this and It can make my life easier, since we close to 10 different sql servers running different databases and applications.
thanks again looking forward to your future articles.
January 7, 2008 at 8:36 pm
This is a great tool but i'm having a problem. It all works fine except that all the databases that the package finds throughout the network are listed against one server. Is this caused by the fact i'm building this on my workstation and not on the server?
January 7, 2008 at 8:53 pm
I've dug a little deeper and it appears that the package is listing every single SQL database we have against every SQL server it has found but only listing one server, the one on which i am running this from, as the host of all SQL instances.
Help
January 8, 2008 at 6:32 am
pepelf,
Part 3 will include all that and more.
David Bird
January 8, 2008 at 6:47 am
So far there have been two reoccurring problems caused by the instructions assuming there is a local instance.
An "OLE DB error" on step 7.1.3 when clicking preview
Cause: The Variable SRV_Conn is not set with a value of an existing instance.
Retrieving the same databases from the same server
Cause: OLE DB Source container referencing the wrong connection. The connection should be MultiServer
Until I can get the article's instructions changed, please substitute these instructions in the article
Define Variables
SRV_Conn
Scope: SSIS_MultiServers_Package
Data Type: String
Value: (local) or server\instance name
* The instance name where the SQL_Overview Database is
Collect Database Status Container
7.1.1
1. OLE DB connection manager: MultiServer
2. Change Data access mode to SQL Command
3. SQL Command Text: See article
4. Click Preview to verify the SQL and then click Close when done
5. Click OK
David Bird
January 8, 2008 at 9:09 am
Corrections to error trapping instructions
1) Before setting connection to ADO.SQL_Overview, change ConnectionType
Here is the correction:
Under "Create OnError Event Handler"
- Settings - Double Click on Icon
General
* Name: Capture Errors Task
* ConnectionType:ADO.NET
* Connection: to ADO.SQL_Overview
2) The wrong variable name for mapping System::ErrorDescription
Here is the correction:
Under "Parameter Mappings
* Variable Name> System::ErrorDescription
* Data Type > String
* Parameter > @ErrorDescription
Thanks rsconnolly for pointing this out to me.
David Bird
January 9, 2008 at 7:29 am
Hi,
Had the same problem as everyone else here, but really powerfull and expandale solution. Thank for sharing !!
Reagrding security across several domain I think the best would be to use a SQL Login for unified access.
//SUN
January 9, 2008 at 3:37 pm
I can't agree with you more. The environment i'm currently working in though is a mess. None of the 12+ mainstream servers have been tuned at any point and the SCOM installation that is being trialled is not operation at the moment.
As such i am very impressed with solution as i have seen a similar solution done with the undocumented sp_msforeachdb then run as a scheduled DTS to record the growth of each database on the server. Only problem is that i trying to scale this solution from 1 server to 50+ isn't very practical when i've a mixture of SQLExpress, MSDE2000, SQL2K Ent and SQL2K5 Ent.
I have successfully modified the Article so far so that it successfully talks to all of SQL2K and SQL2K5 based servers. I did this using the "Microsoft OLD DB Provider for SQL Server" instead of the "SQL Native Client" for the MultiServer connection. I left the QASRV connection as a SQL Native as this package will reside on one of our mainstream SQL2K5 Ent boxes.
I'm looking forward to scaling this solution out to cover all of my servers successfully and developing reports for it.
January 9, 2008 at 11:18 pm
Hi,
One thing that I was thinking to change as well, since I also need to scale it to around 40-50 servers all ranging from the odd 6.5 to s2k5.
The following changes, system for having version info in the server list table, and being able to run against only servers of a certain version, a mix or all.
And perhaps a table with the queries, then one only needed to parse the arguments to the ssis package. In this way less changes to the ssis package, since the changes for added functionality would be on table level.
//SUN
cy.harrild (1/9/2008)[/b
I can't agree with you more. The environment i'm currently working in though is a mess. None of the 12+ mainstream servers have been tuned at any point and the SCOM installation that is being trialled is not operation at the moment.As such i am very impressed with solution as i have seen a similar solution done with the undocumented sp_msforeachdb then run as a scheduled DTS to record the growth of each database on the server. Only problem is that i trying to scale this solution from 1 server to 50+ isn't very practical when i've a mixture of SQLExpress, MSDE2000, SQL2K Ent and SQL2K5 Ent.
I have successfully modified the Article so far so that it successfully talks to all of SQL2K and SQL2K5 based servers. I did this using the "Microsoft OLD DB Provider for SQL Server" instead of the "SQL Native Client" for the MultiServer connection. I left the QASRV connection as a SQL Native as this package will reside on one of our mainstream SQL2K5 Ent boxes.
I'm looking forward to scaling this solution out to cover all of my servers successfully and developing reports for it.
January 10, 2008 at 5:13 am
Hi,
Playing a bit with the posibility for having a table with diffrent queries i ran into the problem that the table transformation also needs to be dynamic. Any ideas how to achive this. I would of cause like to have the output into seperate tables depending on the query. My test query was this:
SELECT CONVERT(NVARCHAR(128), SERVERPROPERTY('Servername')) AS [Server], ISNULL(RTRIM(CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceName'))), 'Default') AS InstanceName, @@version as Version
One solution could of cause be just to have one table, with a layout like servername, instance name , output, queryid
Then dump all query output into the output column and then format it depending on the query run.
January 10, 2008 at 6:59 am
It is great to see the potential for this package’s capability has mushroomed.
In part two, I address how to handle the differences between SQL Server 2000 and 2005 when retrieving the Error Log files on each instance. It uses a store procedure to extract all the error messages and then transfer the rows to the SQL_Overview database.
Soren stay tune for part three.
David Bird
January 10, 2008 at 3:37 pm
When do you expect to have Part 2 released?
January 11, 2008 at 6:44 am
David,
Thanks for taking the time to put this article together. I am looking forward to Parts 2 and 3!
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 14, 2008 at 9:27 am
David
I've got the package working as expected and its saved me a lot of time in getting up and running with SSIS.
However I thing I'm not clear on why its done. In the Foreach Loop Container - Collect Database Status, in the Variable Mappings, User:SRV_Conn exists as index 0. Why does this need to get added here. I can't see why its relevant here.
many thanks, Justin.
Viewing 15 posts - 16 through 30 (of 56 total)
You must be logged in to reply to this topic. Login to reply