Efficient Historical Reporting Database Implementation

Citrix MetaFrame XPeResource Manager Summary Database Implementation Historical Reporting and Billing

Agenda • Agenda • Customer Requirement • Architecture • Operation • User Interface • Availability • Hands on

Customer Requirement • Currently, RM stores data for 48 hours • Resource Manager on MetaFrame XPe will only store server, session and application metrics for up to 48 hours • Data store, RMLocalDatabase, local to every server • No central repository of data

Customer Requirement • Customer Demand • Customers require data older than 48 hours. • Customers want to be able to bill users/departments for computing time. • This functionality was in CRMS 1.0b, but not in RM.

Summary DatabaseArchitecture

Summary db - Architecture • Summary Database • 1 Summary Database per farm. • Can be the same machine as the Persistent Store (although a different database). • Same databases supported as for the PS (except Access). Summary Database

Summary db - Architecture Summary Database Oracle 8i or 9i, SQL Versions 7 or 2000

Summary db - Architecture • Database Connection Server • 1 Database Connection Server per farm. • DSN connection to database owned solely by this machine. • Solves scalability issues encountered in CRMS. • Locate the DCS close to the Summary Database.

Summary db - Architecture Database Connection Server Summary Database

Summary db - Architecture • Resource Manager Server • Each server to be monitored must have Resource Manager loaded. • Summarized data sent to the DCS server once a day (or on request) as a flat text file. • DCS then writes data to the Summary db server.

Summary db - Architecture Resource Manager Servers IMA event Bus Database Connection Server Summary Database

Summary DatabaseOperation

Summary db - Operation • Resource Manager Server • Data for selected metrics, summarized in memory • Written to a flat text file once an hour • Files written to • Program Files\Citrix\Citrix Resource Manager\SummaryFiles. • This is all done by RMMonitorSS

Summary db - Operation • Resource Manager Server • Session, process appmetrics and events are always stored. • Server metrics can be collected at user definable times of day

Summary db - Operation • Data Collection • A schedule is set from the UI for when data collection should occur.

Summary db - Operation • Data Collection • RMSummaryDBSS on each server sends “time for me to send my log file”

Summary db - Operation • Data Collection • This then gets put in a queue on the DCS Server1 Server2 Server3 Server4 Server5 Server6 Server7

Summary db - Operation • Data Collection • First 4 machines told “Send me your log file now” Server1 Server2 Server3 Server4 Server5 Server6 Server7

Summary db - Operation • Data Collection • As the files arrive the next message is sent

Summary db - Operation 2 • Data Collection • Once a servers’ summary file has been sent, a copy is kept as backup • As a new process starts or session starts, a new log file is started 1

Summary db - Operation 1 2 • Data Collection • Both files are kept until the DCS confirms that it has received and processed that server’s file

Summary db - Operation 2 • Data Collection • Both files are kept until the DCS confirms that it has received and processed that server’s file • The first file is then deleted

Summary db - Operation 1 2 • Data Collection • If no confirmation is received, both files are sent on the next file request and another log file started. 3

Summary db - Operation • Writing data to the Summary db • Once the first file is received the DCS will start writing the information to the Summary Database server. • This is a multi-threaded process, more than one file may be being written at any time.

Summary db - Operation • Note: • The summary database is not automatically created on install as the Local Database is. • The administrator must first create the database on the database server • Following this, the DSN must be created on the DCS

Summary db - Operation • Writing data to the Summary db • On the initial connection 5 stored procedures are loaded into the database. • ADDMETRIC • ADDSESSION • ADDPROCESS • ADDAPPMETRIC • ADDEVENT

Summary db - Operation • Writing data to the Summary db • Once the connection has been made, the DCS parses the text file and passes the arguments onto the stored procedures. • The stored procedure acts on the parameters that it is given and invokes the appropriate stored procedure • ADO is the interface to connect to the stored procedures

Summary db - Operation • Writing data to the Summary db • The RM service is structured so that when no imports are taking place the DSN is freed up

Summary db - Operation • Purging the Summary Database • Data in the Summary db is split into 4 categories • Metrics • Session and Process (Billed) • Session and Process (Not – Billed) • Events

Summary db - Operation • Purging the Summary Database • Each category has its own user definable retention period • This can be indefinite • Data is purged at midnight every day (night?) • The purging is achieved by the relevant stored procedures being invoked by the DCS

Summary DatabaseUser Interface

Summary db – User Interface • Several Additions have been made to the Resource Manager User Interface:

Summary db – User Interface • Process Summary:

Summary db – User Interface • User Summary:

Summary db – User Interface • Server Summary:

Summary db – User Interface • Summary Database:

Summary DatabaseBilling

Summary db – Billing • Billing overview • Billing reports are based on the information stored in the summary database

Summary db - Billing • Billing overview • Configure Fees

Summary db - Billing • Billing overview • Define cost Centers

Summary db - Billing • Billing overview • Add new cost centers

Summary db - Billing • Billing overview • Add list of names

Summary db - Billing • Billing overview • Edit Cost Center

Summary db - Billing • Billing overview • 2 methods of billing on the collected data: • By Cost Center • By Domain

Summary db - Billing • Billing overview • Billing reports by cost center

Summary db - Billing • Bill by Cost Center • Each Cost center selected will have 1 distinct billing report created for it

Summary db - Billing • Billing overview • Billing reports by Domain

Summary db - Billing • Bill by selected Domain • 1 report generated for entire list of users

Efficient Historical Reporting Database Implementation