While we strive to make SQL Server the easiest and most efficient database to manage, we will likely always have some gaps with each release. Frankly, that's where 3rd party tool vendors and partners have a unique ability to help DBAs achieve even greater levels of manageability and efficiency with their SQL Server deployments. In fact many of these companies were started by former DBAs and SQL Gurus. While I was at TechEd I got a chance to have some chats and take a look at some demos of tools from Red-Gate, Quest, Idera and others. I was really impressed by the quality and the effort each of these companies has put into their tools.
For the lawyers in the audience: Please keep in mind these are my opinions and do not represent any formal endorsement or recommendation by Microsoft.
In the interest of full disclosure, I'm a sucker for GUIs and rich visualizations (when they provide value). In my defense of liking this kind of "eye candy" I believe that as the number and complexity of deployments continues to increase (SOAs, multi-tier applications with clustering at multiple layers) it will become increasingly important to provide DBAs and administrators with tools that enable true Management By Exception and visual approaches to monitoring, troubleshooting and narrowing to root cause through large quantities of information.
There was one very unique and inexpensive ($100) little piece of software that I thought was very neat and allowed DBAs to build a quick yet visually compelling monitoring suite. SoftwareFX was demoing PowerGadgets. This offering allows administrators to easily create visual gadgets tied to powershell for monitoring OS or Database (or anything PowerShell accessible). You can use the PowerGadget create a variety of visual gadgets including charts, gauges, and maps based on data accessible by PowerShell (WMI for example). On Windows Vista you can tie these PowerGadgets to the Windows Sidebar so that they run continuously on your desktop. Take a look at PowerGadgets for more information. This is a tool for both the Goowey and the Script Jockey. Please note that I have had some problems running it on my Vista 64bit machine, but keep in mind I tend to run all sorts of pre-release stuff on my workstation.
At the Quest booth I was able to see a demo of Spotlight on SQL Server. Spotlight uses a very rich visualization approach to assist DBAs in multi-server monitoring and performance troubleshooting. Spotlight includes a discovery wizard to find your instances of SQL Server. Spotlight provides a visual way of grouping servers into hierarchies of subgroups into an Enterprise View and provdes a threshold based color coding scheme to quickly identify those servers that need to be looked at it more detail and then provides the mechanism to drill down into that specific server. Once you are looking at a server, Spotlight provides a rich visualization of the "SQL Server stack" and enables a DBA to quickly view Sessions, Processes, memory allocations, Disk storage use of the data and log files and then to drill down into each of these areas (for example looking at blocks, locks). Spotlight keeps historical data so you can look at the history of the performance over the last 24 hours for example. I did speak to a customer who had licensed Spotlight at the show and his feedback was that there was some concern of over a potential performance hit on the production server from turning on the diagnostic server. I asked the folks at Quest about this and they assured me that the latest version of Spotlight has a much smaller impact than previous versions. For those enterprise DBAs that are Goowey inclined and are monitoring a large number of SQL Server instances, this may be a tool worth checking out. You can view an online demo of the system at the Quest web site. I also got a chance to view Lightspeed demo and its ability to recover data at any granularity (database, table, rows) was truly impressive (including ability to connect and query directly against a database backup).
Idera's SQL diagnostic manager also provides the ability to monitor and manage enterprise deployments of SQL Server. Their GUI takes on an Office 2007 look and feel and should feel familiar to most DBAs that use Office and outlook. I found the product very clean and well organized. Like spotlight, SQL diagnostic manager provides an "aggregate" view of alerts across groups of servers and then provides the ability to drill down to a specific server. SQL diagnostic manager also enables DBAs to drill down through the "stack" of potential contributors to performance problems, including the ability to find the worst performing queries, stored procedures, etc. The tool also allows DBAs to set their own custom alerting and notifications. SQL diagnostic manager is "agentless" and does not require any components to be installed on each SQL Server.
Not to forget Microsoft's own offerings in this space, in SQL Server 2008 we introduced performance data collectors and the performance data warehouse with drilldown performance reports. While there may be some overlap in functionality and benefit, I found that both these tools provided a richer user experience and a more interactive experience for drilling down to performance problems. I would expect that in the future tool vendors like Quest and Idera will leverage the data collectors and performance datawarehouse to provide additional capabilities to their customers and to DBAs.
Unfortunately I did not have a chance to view demos of the latest wares from Red-Gate but I have been a big fan of their tools ever since I used them at my own startup. SQL Compare and SQL Data Compare saved my (fill in the blank) more than once and their SQL Toolbelt includes tools for backup and recovery and test data generation. Red-Gate's tagline does say what they are all about "ingeniously simple tools". I hope to have a chance to try out the latest Red-Gate wares shortly and will update my blog accordingly. Please check out their site at www.red-gate.com. Red-Gate has also put out two great e-books, one of the books is on becoming an exceptional DBA by Brad McGehee and the other is by Grant Fritchey on the art of high performance SQL code .
I wish I had had more time to see all the cool tools at the show, it is truly amazing to see how our partners and vendors can build such innovative tools to assist DBAs in managing an ever increasing workload. [Now putting on my marketing hat]. Together with our partners we aim to make SQL Server the most manageable and efficient data platform in the market.
Let me know what tools you are using (Gooweys and Script Jockeys) and share with other DBAs your tricks of the trade.