Saturday, May 5, 2012

Oracle DBA Tracking Center

For the code or questions: Please contact us at After-Hours Coders

When having to track multiple Oracle databases spread across multiple servers and tying the database to an application, it is difficult to follow all the instances.  This tool, is a comprehensive solution to have one common location for DBAs and other personnel in an IT department to know the status of their Oracle database instances.

The tool uses a SQL Server database, the SQL Server Integration Services ETL tool and the SQL Server Reporting Services reporting tool to compile and present the data.  You will need at least SQL Server Standard Edition to run the SSIS component.  

The design of the solution is meant to answer the following questions:

·         What are my Oracle databases?
·         On what server are they running?
·         What platform is the server?
·         What Oracle version are they?
·         When did the database last startup?

Through a minimum amount of data entry, you can add data to help find out:

·         What application is tied to this database?
·         Is it Production or Development?

This ETL can be scheduled to run as often as every couple of minutes to keep information up to date. 
What do You Need?:

·         SQL Server 2005 Standard Edition or Higher.  This is built in SQL Server 2008 R2.  Install with the Database Engine, Integration Services and Reporting Services.
·         Oracle 10 Client or higher on the same server or computer you will run the ETL package.

There is a database in SQL Server to store information about the Oracle databases. The database name is “DBTRACK” on a SQL Server database instance. When the ETL Runs to completion, updates can be viewed by a running a collection of SQL Server Reporting Services Reports.

Once installed, all you have to do is enter the Oracle instance or database. This tool does the rest!

Here is the ETL in SSIS:

There are two reports available:

The first is the “Applications” report that shows all applications and the related database and type, development or production as well as server.

The second report shows the detailed database information.  The parameters asks for application and type, but defaults to all.

For the code or questions:
Please contact us at After-Hours Coders

Thursday, March 8, 2012

Consulting Services

I’d like to inform people of my consulting services available.  We are available for consulting nationwide but can work onsite in limited engagements in the Milwaukee, Madison and Chicago areas. We are based in the Milwaukee area.

We can complete the bulk of your work offsite through remote connection or offline.

We specialize in:

SQL Server Databases:
  • SQL Server Database administration and Data Warehouses.
 Business Intelligence:
  • ·         Microsoft:
o   SQL Server Integration Services (SSIS)
o   SQL Server Reporting Services (SSRS)
  • ·         SAP:
o   SAP Crystal Reports
o   SAP Business Objects Enterprise

Please see my website for more information and details: