SQL Overview Part 1

  • 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!!!

  • 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.

  • 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?

  • 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

  • pepelf,

    Part 3 will include all that and more.

    David Bird

  • 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

  • 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

  • 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

  • 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.

  • 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.

  • 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.

  • 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

  • When do you expect to have Part 2 released?

  • 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

  • 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