Toolbox: New Products for IT Professionals
This month’s tools help you with two perennial IT tasks: managing SQL Server and automating network inventory.
Greg Steen
SSMS Tools Pack
Microsoft SQL Server management tools have come a long way since the early days of SQL Server. Your primary tool is still the SQL Server Management Studio (SSMS)—your “one-stop-shop” for SQL Server database management, analysis services and reporting services.
While SSMS is certainly full-featured, there’s always room for improvement and enhancements. Microsoft encourages these improvements through extensions, plug-ins and add-ons. One add-on that can give you some additional functionality on top of what SSMS already provides is the SSMS Tools Pack written by Slovenian SQL Server MVP Mladen Prajdić.
The SSMS Tools Pack can help you write better, clearer, standardized and performance-optimized SQL queries—and do so faster. The SSMS Tools Pack integrates a number of features directly into the SSMS interface.
The Execution Plan Analyzer extends the integrated Execution Plan tool to help you easily find the most “expensive” operations via a sorted and color-coded navigation bar. You can define “cost” by a number of different statistics, such as I/O, CPU utilization, the number of rows affected, cardinality and other options. It also includes a search tool so you can easily explore an execution plan without having to scroll through the plan diagram. The Execution Plan Auto Analyzer extends the built-in analysis tool, finding common issues and giving you detailed solutions. The SSMS Tools Pack also lets you save the plan results for future reference.
SQL Snippets let you create custom editor shortcuts, which can save you tons of keystrokes. For example, you can define “CA” plus the Enter key to insert a snippet for CROSS APPLY. The SSMS Tools Pack also has a number of code-generation helpers, including those to create stored procedures for your basic Create, Read, Update and Delete operations. You can also generate insert statements from result sets, tables or databases.
The New Query Template tool will keep your standard query items in place for when you want to write a new query. For example, you could have SET NOCOUNT ON followed by BEGIN TRAN/ROLLBACK as your template. The Format SQL command will help you keep your code clean and standardized by auto-indenting and transforming database objects to their proper case. In can also transform keywords to either uppercase or lowercase, depending on your preference.
The Window Connection Coloring feature uses color coding to immediately inform you about server connections, so you don’t run a query in the wrong place. If your servers follow standardized naming conventions, you can define a regular expression to color code based on those conventions, which could color the query window for production machines red, staging machines orange and development machines green.
You can add regular maintenance activities or data queries to the Object Explorer context menu with the SSMS Tools Pack. There are also query templates replaced with active settings when you want them executed (such as database, server and table). If you want to run a query on multiple servers at the same time, you can also choose “Run on Multiple Targets.” Then choose all the databases and servers upon which you want to run the query and execute simultaneously.
The SSMS Tools Pack helps you search query results in Grid Mode, highlighting each matching grid cell. You can also search tables and views with simple search terms using the item on the SSMS Tools context menu.
There are a number of archival features in the SSMS Tools Pack as well. You can periodically save each query tab session so you don’t lose your work. You can restore your last session when you launch SSMS the next time. The Window Content History feature saves all text in all opened SQL Windows every few minutes so you can see what has run.
The Current Window History feature provides time-stamped execution history for the current query window so you can immediately see the query sequence. You can also reuse statements from those previous executions. Similarly, the Query Execution History feature lets you log every SQL statement executed, so you’ll have a running history of every change made. If you want to standardize settings across machines, you can also import and export settings.
The SSMS Tools Pack will work with Microsoft SQL Server Management Studio 2005, 2008, 2008 R2 and the forthcoming SQL Server 2012, including the Express editions of SQL Server. If you’re looking for some of these additional features to help round out your SSMS experience, the free SSMS Tools Pack is definitely worth adding to your IT Toolbox.
Network Inventory Advisor
Automating the process of taking inventory of your systems, devices, and software can save you a lot of time and headaches. There are a number of tools out there with various approaches to helping you stay on top of your infrastructure. One of those tools is the Network Inventory Advisor from ClearApps LLC. Network Inventory Advisor is a full-featured software, hardware, and network device inventory application with automated discovery and agent-free operation.
Network Inventory Advisor automatically discovers Windows, Mac, Linux and SNMP-powered devices across network segments, saving you from having to enter all that information manually. You’ll have to provide credentials for remote access to all machines and devices you wish to include. You can schedule inventory scans to add new devices and update inventory reports and alerts. You can also scan individual nodes or a subset of nodes, or you can add devices manually.
After a scan, Network Inventory Advisor automatically groups results into their respective domains, work groups and types. You can configure groups and group hierarchy, so you can add your own levels and names to suit your needs.
Network Inventory Advisor gathers software inventory data on the OS, hotfixes, software, licenses, Program Files directory contents, startup and logon auto-run settings. It also collects security details, such as antivirus, firewall, anti-spyware and automatic update status. For hardware inventory, it gathers all the details on processors, motherboards, memory, attached storage, video cards/chips, peripherals, network adapters, USB and FireWire devices, multimedia devices like audio and video codecs, and even modems.
Depending on the remote OS, you can also gather information on shared resources, running processes, installed services, disk usage and utilization, local users and groups, and environment variables and settings. You can add your own notes if there are some details missing that you need for your own auditing procedures. You can also import and export audit information for reuse on other systems or for delivery via another medium.
When drilling down into a machine or device for more detailed data, you can use the tabbed interface to jump to the area of interest. Items that are cause for concern—such as a lack of anti-virus software—will show up in the Alerts section. You can custom configure alert settings, so you can define what should trigger a warning, such as low disk space thresholds, no firewall enabled, or even changes to software or hardware.
No inventory application would be complete without reporting. Network Inventory Advisor has many built-in, customizable reports. You can also define your own report templates. The reports are split into two categories: summary and detail.
The application has a report-building wizard to help guide you through custom report creation. You can schedule and automatically deliver reports as well. Some of the pre-defined reports include general inventory, configuration and licensing. There’s also disk usage, services, systems under Alert or hardware changes. You can export report results to HTML, CSV, Excel, XML or directly to a database.
Pricing for the ClearApps Network Inventory Advisor starts at $89 for up to 25 nodes. There are discounts on the per-node pricing as you go up in volume. A “nationwide” unlimited node and installation license runs $989. The company also offers a 30 percent discount for government and educational institutions as well as competitive upgrade discounts of 50 percent. The next version of Network Inventory Advisor is due out in early 2012. If you purchase the current version, you’ll get an automatic upgrade when ClearApps releases version 4.0.
.jpg "Greg Steen")
Greg Steen* is a technology professional, entrepreneur and enthusiast. He’s always on the hunt for new tools to help make operations, QA and development easier for the IT professional.*