Getting a Tivoli Data Warehouse to communicate with Microsoft SQL Server

by bluemedora_editor on July 15, 2009

Introduction

My name is Brian Demers and I am currently an Intern here at Blue Medora. I am a Computer Science major at Grand Valley State University and will be graduating this December. This internship has exposed me to numerous new types of technology that are not a part of the CS curriculum at GVSU. Recently I was given the task of setting up a sandbox environment to setup and run IBM’s Tivoli Common Reporting application on. While performing typical Google searches to investigate my new assignment, I was surprised at how I was unable to find any resources on getting the IBM Tivoli Monitoring (ITM) Tivoli Data Warehouse (TDW) working with Microsoft SQL Server and Oracle. It was at this point I was encouraged to get the technique down to a science and blog about it. This post is going to cover how to get TDW working with Microsoft SQL Server 2005. I will be posting another how-to detailing how to setup TDW to function with Oracle.

Versions

  • Windows Server 2003 SP2 running in a VM on a VMware vSphere 4 Server
  • IBM Tivoli Monitoring v.6.2.1 IF2
  • Microsoft SQL Server 2005
  • jTDS MS-SQL and Sybase JDBC Driver v.1.2.2

Pre-requisites

This guide assumes that you have already successfully installed ITM and Microsoft SQL Server 2005.

Disclaimer

This guide does not cover the steps required to configure the Tivoli Enterprise Portal Server (TEPS) to use Microsoft SQL. Also note that this is not the definitive way to setup Microsoft SQL and make it work with ITM, rather this is an example of how I got it working.

Walkthrough

1. Open Microsoft SQL Server Management Studio, choose Windows Authentication, then clickConnect to login.[vc_single_image image=”2952″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]Create a new user by navigating to Security and right-clicking on Logins. Click New login. Set the Login name to ITMUser with a password of itmpswd1 (the font makes the last character look unusual; it is the number “one”). Select SQL Server authentication and uncheck all three of the password boxes[vc_single_image image=”2953″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]2.Create the database by right-clicking on Databases, selecting New Database and entering the name WAREHOUS.[vc_single_image image=”2954″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]3.Create a new user in the DB by navigating to WAREHOUS, expanding out Security, right-clicking onUsers and selecting New Login. Choose the name ITMUser and then browse to find the high-level user ITMUser.[vc_single_image image=”2955″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]4. Create a new schema by right-clicking on Schema and selecting New Schema. Make the nameITMUser and browse to find the DB user ITMUser.[vc_single_image image=”2956″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]Set the Default schema for ITMUser from dbo to ITMUser.[vc_single_image image=”2957″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]5. Change the Database role membership of ITMUser to include db_owner by right clicking on the high-level user ITMUser, selecting Properties, navigating to User Mapping, selecting the WAREHOUSDB then checking the db_owner box below.[vc_single_image image=”2958″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”][vc_single_image image=”2959″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]6. Change the owner of the DB WAREHOUS from whatever it is initially set to, to the user ITMUser by right-clicking on the DB WAREHOUS, selecting Properties, navigating to the Files page, then changing the Owner by browsing to find the user ITMUser.[vc_single_image image=”2960″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]7. Open up the Manage Tivoli Monitoring Services application and right-click on Warehouse Summarization and Pruning Services then select Configure using defaults otherwise Reconfigure.[vc_single_image image=”2961″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]Select MSSQL from the Database drop-down menu. Replace <server>:<port>/<database> with the information for your system. Finally, add a JDBC driver. If you do not have one at the ready, follow this link to download the jTDS – SQL Server and Sybase JDBC Driver. Enter the user we created earlier,ITMUser, as the Warehouse User as well as the password we entered earlier, itmpswd1.[vc_single_image image=”2962″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]Verify the connection by clicking Test database connection.[vc_single_image image=”2963″ alignment=”center” border_color=”grey” img_link_large=”” img_link_target=”_self” img_size=”full”]Once you have verified the connection to be working, you will be able to connect to the TEPS and be able to configure Historical Data Collection (HDC) for any agent you wish.

If you have any questions, please do not hesitate to contact me at brian.demers@bluemedora.com.

Useful links

JDBC Driver download: http://sourceforge.net/projects/jtds/files/[vc_column width=”1/3″ el_class=”sidebar”][ultimatesocial_false count=”” align=”left” skin=”default” share_text=”Share: ” networks=”facebook, twitter, linkedin, reddit”]

Get started

Try BindPlane for free. No credit card required.

Sign up
True Visibility
BindPlane for VMware vRealize Operations

True Visibility allows cloud management teams to use VMware vRealize’s powerful machine learning and capacity planning engine across their entire hybrid cloud environment.

Azure Monitor...everything
BindPlane for Microsoft Azure Monitor

Make Azure Monitor your first-pane-of-glass across your entire multi-cloud, multi-database or hybrid platform environment.

Thank you for contacting us. Your information was received. We'll be in touch shortly.