Tuesday, March 24, 2015

SQL Monitoring Tools - Idera

That post will start the series of SQL Monitoring Tools' reviews. Product installations and overall experience.

Will start with Idera (V.9.0.0.1189 x64).
Why "Idera"? - because that company is almost always sponsoring SQL Saturday events, which help to develop local SQL talents.


At first, I've looked at Idera.com and found "SQL Diagnostic Manager".
In order to download I had to register and provide my email and phone number.

When I click to download ZIP file the page was loading for several minutes before the actual download started.

After extracting Executable file and starting it I was asked for another place to unpack. That seems not very convenient, everything could be packed into the ZIP file, now I have two copies of installation.

When installation started and I got following window I was little bit confused what do I have to do:
After I've figured out which option to choose I've got the window that I couldn't comprehend:
In my assumption here I have to specify parameters of my workstation.
Two big questions are not answered: Why do I have to create "Idera_Service_Account" with no rights and what port 9292 stands for?

After creating dummy "Idera_Service_Account" and specifying in the form, I was asked to accept the License Agreement.
Then I provided installation path and hit another bump:
The installer just asked me for a service account a minute ago.
Can I use the same account or it should be different?
Why it can't run under current system account? HELP!!!!

Obviously, the same account did not work. I had to put my domain account.
Can anybody guarantee that info was not sent immediately to hackers or not stored locally in the easy to break format?

Mystery of port 9292 is solved:


Have a bump again:

After Specifying the SQL Server instance Installer was satisfied with previously entered credentials.
Two more screens/clicks and installation has started and finished almost immediately.

What do I have to do next?
Search in Start Menu brought me hidden "Idera Dashboard", which was actually a link to following location: https://localhost:9291

Now I have to provide my credentials again...
 After logging in I've finally got to "Idera Dashboard". Is it the one?
In order to try Idera SQL Monitoring I went to "Manage Instances" tab:
I did not expect to see anything in the list, but I couldn't locate "ADD" button anywhere.

However I've found "Help" button.
Search on "Add SQL Server Instance" lead me to only one page: "http://wiki.idera.com/pages/viewpage.action?pageId=38339658" - Why?

Then I realized that there are two search options and only one of them really works:


Finally I've found how to Register new SQL Server:
Right here I've stopped. Help document states that I have to do something obvious, which does not exist in the menu.

Almost gave up when found hidden installation window. It looks like installation is not finished yet.

That installer looks differently, it asked me to accept license agreement again. Why?

It looks like  previous install was "Idera Dashboard" installation and that one is actual "Diagnostic Manager":

After couple of screens I had to provide my local server name again:

But live is not so easy. While Idera Dashboard easily found my server "Diagnostic Manager" gave me following message:

Very nice. After I Enabled "TCP/IP" protocol it recognized my server and asked for my credentials again. I could use SQL server Authentication, but was too lazy to create a new account.

It looks like it was the last challenge.The fact that two installers do not pass data to each other and connect to servers.

Finally got to "Diagnostic Manager" screen.
Added new server. After all previous stupidity it was pretty straight.

At start, "Diagnostic Manager Console" immediately scared me by red crosses:

 I've tried following simple query:
SELECT
    physical_memory_kb/1024 AS [Physical Memory (MB)],
    committed_target_kb/1024 AS [Committed Target Memory (MB)]
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

It returned me the following result:
That means Server Memory is not allocated 100% to the SQL Server.
However, it is possible that other processes eat the rest of the memory and server is paging to the disc.
I've logged to the server and checked current state of the memory:
Obviously, we still have 9 Gb of free memory.
What 100% "Diagnostic Manager Console" refers to?

Looking at "Memory branch" to discover the problem:

There is no case that 100% of memory is used. As you can see, SQL Server is actually using just little over 60% of all allocated memory. Page Life Expectancy is huge. Everything looks healthy.



Now I'm trying to discover tool's functionality.
That is a real time view of SQL connections:

Real time view of system resources:

I also could go through all databases' configurations, their sizes, file allocations, tables' and indexes' sizes, backups and mirroring.
Tool allows to rebuild indexes and update statistics.

Next area is Services: Agent, Full-Text Search and Replication. There is "Critical Error" warning sign because the monitor can't access to the server for service monitoring. Who said it should?

And the last one is logging area for SQL Server and Agent logs.

Here is the full tree of available metrics:
As part of my evaluation I've tried to chat with help desk regarding "100% Memory usage" alert.
Unfortunately, the guy at the help desk couldn't help because all evaluations are serviced only by sales personal. So, I have to use email or phone and that would be "advertizement service", not the real one  I was trying to evaluate.

At the end I want to measure my experience for the product on the scale of 10:

Installation 5 (because of 5 problems and reasonable questions during the process)
Web interface (Dashboard): 1 (can't use it at all)
Diagnostic Manager: 8 (false memory alert and inability to acces to services alert)

General impression: Idera Diagnostic Manager provides pretty accurate real time monitoring of Key SQL components, but it is not very usable to do monitoring from the historical perspective.
That is "nice to have" tool when you constantly dealing with problems, but in stable environment you can use it only as alerting tool.