Over the last few weeks, I have had the opportunity to beta test Idera’s newest product, SQL Elements. My initial response? This is both a “fantastic” product and a “tease” of a product, I’ll explain later. Idera has definitely done their homework.
Initial install is easy; all you need is a web server, SQL Server for a data repository, and a Domain Service Account with SysAdmin privileges on the SQL Servers you would like to monitor or SQL account with SA privileges. My environment uses a Domain Security Group called SQL-DBA. Since it includes both my account and all service accounts that need SysAdmin rights, I just used one of these service accounts.
One of the better aspects of SQL Elements (SE) is the “auto discover” feature; it finds the servers for you. It basically scours your network to find all SQL Server instances it can find, including Express Editions. Even if the service account does not have SysAdmin rights, SE will find the server and provide a listing for it, which can help you to realize which SQL Servers, you as a DBA, do not have access to. It sometimes has problems determining the instance name (if you are using named instances example: “Server\SQLExpress,”), but who really uses SQL Express Edition for production anyway? You can always manually add the named instance to avoid that issue. This feature also periodically scan’s the network for any new instances and automatically adds them to the “Discovered Instances” list so you know when someone else installs SQL Server. NICE FEATURE!
SQL Elements uses the concept of “health checks” to determine the status of your SQL Server. These health checks include: DBCC CHECKDB consistency, current backup checks, auto-shrink enabled, and “optimize for ad hoc workloads” just to name a few. Many of the more critical checks have email alerts associated with them to let you know when a database is filling up or when a drive is running out of room.
Once you login to the website, the Dashboard for SE provides a brilliant snapshot of your environment. First and foremost, at the top is the “Health Check Recommendations” that SE has found in your environment. Each Health Check is given a “Level” based on the severity of the problem. Idera provides a brief explanation of why each recommendation is made and a link to a more detailed explanation. Once you review the recommendations, you have the choice of dismissing the alert or refreshing the alert supposedly after you have resolved the issue with the recommendation.
Below the recommendations are two simple graphs listing “Top Databases by Size” and “Top Databases by Activity”. Personally, I wish I could hide this module of the dashboard and move the “Instances” grid below it up. I haven’t found much use for these two graphs, but maybe that is just me. The grid of Instances is very user-friendly; it’s a simple list of what instances are being monitored, their monitoring status, response time, version, # of databases, and total size of databases. Each column is sortable, and the grid works on 20 listings per page which is a very reasonable size to work with.
On the right side menu, is a simple “My Environment” section, which allows you to manage the SQL Servers in your environment. The ability to classify the servers by “tags” is nice, especially if you want to just look at your “Critical 24×7” servers or just your “Test” servers. I really like the concept of “labeling” SQL Servers with a category so I can prioritize the server health check recommendations. I only wish when selecting a “tag” the resulting page showed the “Health Checks” for those specific servers, not the “Explorer” tab.
Clicking on the Instance name actually brings you to a very valuable “Instance Details” page. Again, at the top is the list of Health Check Recommendations for this particular instance. Below that is a grid listing all the databases found on the instance as well their status, recommendations, type, data size, log size, and activity. I would love this grid to include the Compatibility level of the database, because many time developers will restore, move, or copy a database from one server to a higher edition server and not change the compatibility level. On the right side menu, you have simple information pertaining to this particular instance. Clicking on the Server Name, however, will bring you to “Hardware Details”. There is also a link to view the SQL configuration settings.
There are more sources of information found in SQL Elements. I won’t go into those here, but they include an “Explorer” tab, which allows you to explore your environment by filters and tags can be helpful when trying to locate a specific server in a larger environment. As my environment is not that large, I really haven’t used it much.
So, after this “novel” of a review, here is what I think of SQL Elements:
First and foremost, the application is well written, has smooth transitions between pages, and has yet to throw any type of exception error with me. The ability to classify instances is a wonderful concept and I use it everyday. One of the additional features that I truly enjoy is the ability to assign an owner and location to an instance. I assign the “end user” as the owner and either “Data Center” (for physical servers) or “VM Ware” (for well, VM instances). That way, I quickly know if I am dealing with a physical server or not. Monitored instances have to be SQL 2005 SP1 or above, which in my environment leaves me a little frustrated since, unfortunately, we are still running a dozen or so SQL 2000 instances. But it does let me know what SQL 2000 servers I have out there, so I’m able to start my migration plans now!
The only major “flaw” I found with SQL Elements is the lack of producing reports based on data collected. Many times, managers and directors require “physical proof” of why I am asking for another terabyte of drive space for a SQL Server. The “powers that be” like pretty graphs and trends. If a drive is running out of space, we need to be able to show them the trend of drive usage so we can justify that new 1.5 terabyte hard drive. Having participated in the beta forums for SQL Elements, I have faith that Idera will listen to the masses and in the future provide some sort of reporting feature.
I mentioned earlier, that this was a “tease” of a product. The DBA who uses this product needs to remember that this is not a monitoring tool. If you are hoping this will provide full SQL monitoring, then you will be disappointed. For that, I would recommend, Idera SQL Diagnostic Manager. However, if you want a way to know what SQL Instances are in your environment and get a quick overview of your server, then SQL Elements is for you. This is an “Inventory Tool” with some basic monitoring of the most fundamental aspects of a SQL Server: drive size, data integrity, backups, etc. Things that could and will cause major problems if not checked regularly.
This is a very valuable tool for starting DBAs or IT groups that have no clue what they have in their network (which is my group, because we have never had a DBA for our over 100 instances of SQL, before me). I would definitely recommend this product! I only hope I can convince my “powers that be” to get it for me!