NOTE: Only documentation and tutorials are available publicly. Please email us for the source code or if you have questions. Full release coming soon.

These tools and utilities for SQL Server are in use every day for the automated and centralized administration of production databases and servers. Each tool tries to go the extra steps that most add-on, third-party or native SQL Server tools tend to leave out. SQL 2008 has implemeneted some of these in a similar way, but SQL 2005 is what this tool is designed for and where it can bring the most benefit as intended. Suggestions are welcome and questions will be addressed, please write to alek@applydatasystems.com

Project Description:

(see the screencast: links above each tool's pic for a quick tutorial of the tool.)
Tools for SQL Multi-Server Centralized Administration:

* Performance
* Indexing
* Backups
* Windows event logs
* SQL Agent Jobs
* SQL error logs
* Database Statistics
* Security
* Compliance Auditing
* Change Management
* Scripts Library
* Multi-Media Library

"SPID Activity Screen All Servers"

Get all activity from all servers by SPID. Find blocking spids and identify cross server blocking issues and localized blocking issues. Get the command text and total i/o consumption for each spid. Sort by clicking on the column name i.e. server name, spid, last batch, etc.
Screencast: SPID Activity Screen All Servers

"Latest Backups"

Reports all usual backup statistics, but adds "Find It!" which goes to every backup file and makes sure it's really there on disk and returns the unc path to the file. Also adds a "Verify File!" function to actually run a RESTORE VERIFY ONLY on the file assuring the entire backup file is readable and viable for a restore.
Screencast: Latest Backups - all databases on all servers - with Find Now and Verify

"NT Logs Event Comb"

We use SSIS and a custom implementation of the free Microsoft EventCombMT utility to accurately comb through a list of remote servers, pulling warnings and errors from event logs, and adds quick event archiving and creates an issue/event audit trail, provides quick access to the remote event viewer, ability to sort by time across all servers to correlate and identify system wide issues. A great (and free) remote event log aggregator developed, tested and in production. This is an invaluable tool to proactively monitor all your servers event logs.
Screencast: NT Event Logs - Multi Server Event Review And Archiving

"Security Principles Membership Tree"

Find all security memberships for all principles (logins) on all servers in any nt or active directory group, local machine group, and sql roles both server and database. Easily find who has sysadmin rights and answer many other important security audit questions.
Screencast: Security Principles Membership Tree

"Database Statistics - All DBs All Servers"

Current statistics for all databases on all servers. File izes, locations, free spase (in files), etc. Highlighted warnings for files on system drive, using simple recovery. This makes a good data flow for general database trends, and the warnings catch deployment gaps.

"Wait Stats Analysis"

Charts that provide a single-dynamic-view bottleneck identification graph. Easily identifiable bottlenecks with trendlines and trend analysis over days, weeks, months. This tool has helped resolve performance issues in a fraction of the time, but nice without constant monitoring efforts and is available real-time.

"SQL Execute On Multiple Servers"

Run sql code (like in query analyzer) but on many servers with results being returned for each command run on each server. Easily deploy to multiple servers, get diagnostics from all servers, get locking and other troubleshooting info across all servers without having to change connections each time.
Screencast: SQL Execute On Multiple Servers Screencast

"Scripts & Snippets"

Easy adding to and retrieval of snippets and canned SQL scripts. Categorize and parameterize.
Screencast: Scripts Library Screencast

"My Activity Trace Table"

Sort, search and find the sql commands for all trace data captured with the stored proc (as shown in the screencast) and saved in the MyActivityTraceTable (also as shown). Usefull for archiving and searching through large amounts of trace data using filtering, time correlation and user/spid/application groupings (as an example).
Screencast: My Activity Trace Table Screencast

more coming in incremental feature releases...
Media library and associated pics and screencasts for the project are here...

Last edited Sep 16, 2009 at 10:35 AM by AlekKirstein, version 39