Compact DBA

PDA solution to remotely manage, analyse and monitor any server or SQL Server

Compact DBA is a full featured PDA solution to remotely manage, analyse and monitor the performance and availability of any server or SQL Server on your company's network. It was designed to run on any Pocket PC device but is most useful on a Pocket PC phone as this allows complete remote coverage from anywhere (versus a WiFi only device which is limited to signal "hotspots"). Compact DBA consists of over 10 screens packed full of information on every aspect of server performance and availability. Compact DBA is specifically targeted toward the SQL Server DBA to: Reduce downtime through proactive alerting and providing the DBA with the ability to rapidly apply corrective actions Allow DBA's / administrators to each handle more servers by removing any monitoring and checking they previously performed on a daily basis Reduce callouts / overtime / shift work as monitoring and corrective actions can be performed whilst not at work Improve server response time by suggesting award winning corrective actions to be taken.

Compact DBA - Features

Compact DBA is a full featured PDA solution to remotely manage, analyse and monitor the performance and availability of any server or SQL Server on your company's network. It was designed to run on any Pocket PC device but is most useful on a Pocket PC phone as this allows complete remote coverage from anywhere (versus a WiFi only device which is limited to signal "hotspots"). Compact DBA consists of over 10 screens packed full of information on every aspect of server performance and availability.

Features include:

The Server screen provides an overview of the overall performance of the system as well as the load it is being asked to process.

As can be seen the screen is broken down into 5 areas: 1. The first frame shows the number of users as well as the number of processes these users are utilizing. The number of SQL Batches per second and Transactions per second being generated by these Users / Processes is also shown. 2. The second area shows the network traffic both from and to the server. The stress the network card(s) are under is also shown by the number of network requests which are currently queued. 3. The next frame shows the stress the CPUs as well as memory are under. The efficiency of memory usage is also displayed in the form of the sizes of the Buffer and Procedure caches as well as their respective hit-ratios. 4. The following area displays the data traffic moving between the CPU / memory subsystem and the disk I/O subsystem. For convenience this has been broken down into the various forms of reads (page reads as well as read-ahead reads) as well as writes (page writes, log writes and checkpoint writes). 5. The last area provides an overview of just how hard the disk I/O subsystem is working as well as the stress its currently under in terms of the number of disk reads / writes currently queued for execution.

The Action screen is like having a full featured Query Analyzer right there on your PDA. This screen allows you to:

• Reboot the server
• Stop the server
• Issue command line statements such as Ping, Tracert, ipconfig, NET commands (such as NET Send, NET Session), batch files etc.
• Stop / Start / Pause any service (including SQL Server, SQL Agent, DTC etc.)
• Run TSQL DML (Inserts, Deletes, Updates, permissions etc.).
• Run TSQL DDL (Create tables, drop databases, issue DBCC commands etc.).
• Parse any set of TSQL statements.
• Make use of TSQL batches (GO statement)
• Execute only a portion of the TSQL batch by highlighting it.
• Save any TSQL statement into a library for reuse
• Copy the output (or a portion thereof) to paste into a report, email or SMS

In addition, over 100 standard templates are provided (its like having Books Online on your PDA) as well as many prepared TSQL batches to:

• Check when database backups were last taken for each database
• A list of the current database sizes of all databases (split into data file and log file) with the percentage this represents of the grand total
• Check for open transactions which may prevent effective transaction log offloads
• List the 30 largest tables in a database showing their space used broken down into data, indexes and text
• View the SQL Server Error Log which we have conveniently reordered to show the most recent entries first
• Show the status of all of the SQL Server services such as SQL Server, Agent, DTC and Search
• List the available disk space on all fixed drives / RAID solutions.

The Agent Jobs screen provides full monitoring, alerting and control of SQL batch jobs:

• The view of jobs can be tailored to show either All Jobs, Only Running Jobs or Only Jobs which have Failed or have been cancelled.
• Jobs can be viewed for a single server or simultaneously for all servers being monitored.
• Job details include: current status, last run outcome, enabled, next run date.
• The status of the Agent service is shown.
• The Agent service can be stopped or started from this screen.
• Jobs may be started (if not already running) or stopped if they are running.
• The detail steps of a job may be examined together with the execution history

The Details screen allows a full fledged historical graphic drilldown into more than 50 performance metrics which Compact DBA gathers and stores:

• Available graphs include (amongst others):
  • Memory Allocation
  • Network Traffic
  • Disk I/O
  • Disk Stress
  • Processor Load
  • Stress Indicators
  • Lock performance
  • SQL User Load
  • Operating System Stress
• The "high-water" levels can also be graphed alongside the normal values in order to see just how close the current values are to the highest limit the server has ever reached.
• The user is able to select between viewing the graph in 2D vs. 3D as well as if the values are to shown in a pie chart, line chart or bar chart format.

The Processes screen provides a comprehensive view of all SQL Server processes:

• In addition to all of the standard process list columns available on Enterprise Manager Compact DBA has two additional columns, CPU and I/O which display the change in these fields since the last refresh. This is highly useful as simply viewing the standard CPU column will not afford you the opportunity to determine if the process is currently working "hard" and therefore placing excessive load on the server.
• You can set the view to display:
  • All Processes.
  • Only Active processes.
  • Only highly active processes (consuming > 50% of the CPU or requesting more than 50 physical I/Os per second).
  • Only processes which are blocking or are themselves being blocked.
  • Only the process at the head of the blocking chain.
• Processes can be viewed for a single server or simultaneously for all servers being monitored.
• System processes can be removed from the display in order to provide a shortened list containing only user processes.
• Holding the stylus on any process provides a wealth of information about its status and reason therefore.
• Clicking on a process displays the SPID screen which allows you to view an immense volume of information about the current execution statistics of the SPID.

The SPID screen allows the DBA to view the precise execution status and statistics of a specific SPID (process). Color-coding certain potentially problematic values draws the user's attention to items which may require urgent intervention

• If a process is currently executing then the TSQL at the bottom shows the exact statement currently being executed.
• Note that the DBA has an opportunity to kill a problematic process on this screen should they so need to.
• The image below shows the screen viewing a process which is currently blocked. Note the additional highlighted fields which no only show who is blocking this process but also on which object name the block is occurring.

The Configuration screen displays both the hardware and software configuration of the SQL Server as well as the underlying server / cluster.

Information displayed includes:

• All of the SQL Server configuration setting information
• Available disk space for each of the server's disk spindles or virtual RAID drives (should a RAID or SAN solution be used).
• How the server is clustered. Will be either Not Clustered or if clustered will display the number of nodes (e.g. Clustered 4 Nodes).
• What audit level the server is running. Can be one of none, success, failure or all).
• What licensing scheme the server is using. Can be Per Processor ( xx processors allowed) or Per Seat ( xx clients allowed)
• What types of logins the SQL Server server allows. Can be either Fixed or Windows Authentication
• How much physical memory the server has at its disposal.
• How many CPU's the server runs.
• What version and build of SQL Server is running.
• What version and build of Windows the server is using.

The Alerts screen displays a list of alerts which have been generated by Compact DBA and sent as popup balloon alerts to the DBA on his PDA over the last 30 days, for all of the services which this DBA monitors. The alerts belong to one of 3 categories: Critical alerts are generated whenever a server is unreachable (due to network failure or the server being down) or the SQL Server service is stopped. Warning level alerts are generated whenever an alert level is exceeded. Alert levels can be manually set on over 50 key performance areas using the Alert Settings screen (see below) or automatically set using the Compact DBA Alert Wizard (see below). Informational alerts are created whenever the Compact DBA service itself is stopped or started. As DBA's will come to rely on the Compact DBA service to alert them, its important to know when it was stopped and / or started.

Some of the alerts which can be set include:

• Agent jobs which fail.
• A process which has been blocked for longer than a specified length of time.
• A scheduled Agent job which has been running for longer than a specified length of time.
• When a database cannot grow by more than xx percent or xx MB before available disk space runs out.
• When a transaction log cannot grow by more than xx percent or xx MB before available disk space runs out.
• When the CPU% remains at an abnormally high value.
• When the system suffers from abnormal substained paging.
• When any of the services (SQL Server, SQl Agent, DTC, Search) is suddenly stopped.
• When the disk subsystem works abnormally hard for extended periods or the queue of disk requests reaches excessive levels.
• When the network remains saturated for extended periods.

The Alert Balloon pops up alerting the user to the fact that one or more alerts have been generated. The alerts belong to one of 3 categories:

• Critical alerts are generated whenever a server is unreachable (due to network failure or the server being down) or the SQL Server service is stopped.
• Warning level alerts are generated whenever an alert level is exceeded. Alert levels can be manually set on over 50 key performance areas using the Alert Settings screen (below) or automatically set using the Compact DBA Alert Wizard (below).
• Informational alerts are created whenever the Compact DBA service itself is stopped or started. As DBA's will come to rely on the Compact DBA service to alert them, its important to know when it was stopped and / or started.

In addition to the balloon popping up the Alert Balloon is accompanied by:

• A buzzing sound (which the user can adjust to any other ringtone or sound).
• The phone vibrating.
• The phone's LED will flash red and continue to flash until the alert is acknoledged. Even if you return to you phone after a few hours you can immediatly see from the light if an alert was generated.

Compact DBA - System Requirements

Mobile Device

• OS - Pocket PC 2002 / 2003 PDA or Phone Edition (not a SmartPhone or a Palm or a Blackberry)
• CPU - 200MHz (preferably 400MHz)
• Memory - 32MB (preferably 64MB)

Back-end service monitoring machine (monitors target SQL machines and communicates with mobile clients)

• OS - Windows 2000 Server, XP Professional, Windows 2003 Server
• Memory - 256MB (preferably 512MB)
• Disk Space - 130 MB for the installation (including MSDE & .NET) + 11MB per monitored server
• Permissions - Service must run with sufficient admin privileges to be able to interrogate the performance counters of all target SQL Server / NT machines

Target SQL Server or NT machines (to be monitored)

• SQL Server - 2000, 2005 (Yukon)
• CPU Usage - < 0.5%

Compact DBA runs only on the Windows Mobile for Pocket PC 2002 and 2003 operating system and therefore will not work on any:

• Windows Smartphone
• Palm
• Blackberry
• Symbian device (Nokia & Sony / Ericsson)