SQL-Hero
SQL-Hero is a mature tool suite that is built by SQL developers, for developers. It includes the following functional areas:
- Audit History
- Maintain a record of all schema changes including who did them, from what machine, full-text of the applied command (and "resolved" full object text), and more.
- Optionally capture all data manipulation commands (DML).
- Associate changes with work items from your ALM system.
- Search for changes using relevant filters, with multiple reporting options (grid, graphical, emailed).
- Use optional simple native source control in a team environment over your SQL objects. Optionally integrate with other third-party source control systems.
- Leverage history to reconstruct a schema to point-in-time, even with no backups!
- Detect and visualize model drift, along with over 20 possible "events" in the life of your databases.
- Use change data to "claim" objects for the purpose of build management. This is just one of many possible ways of doing build management, but one that is relatively simple.
- Code Generation
- Generate various output types from many types of database objects, as a source. For example, generate SQL to create an audit history infrastructure based on existing tables. Or create .NET code that understands the structure of tables, views, stored procedures, constraints, and more. The possibilities are nearly limitless, and we've included dozens of "canned" templates with SQL-Hero: you're free to create your own as well.
- Integrates with SQL Editor to support "snippets" that understand context.
- The template engine isn't strictly used to generate code: it's used in other places throughout SQL-Hero. For example, with monitoring you can "run templates" as part of triggered events, where your templates can do virtually anything.
- The template engine supports in-line scripting using C# 4.0. (Hence our claim about "do virtually anything!")
- The template engine supports "user-defined properties" (UDP) which can be associated to most types of SQL object. These can originate from extended properties, although the preferred approach is to source directly from SQL-Hero since UDP's are strongly-typed, can have defaults, and can optionally be scoped to cross databases (eg. properties for "Customer" may be the same in development and staging databases).
- Templates can be fully "self-supporting": they describe needed parameters, which object types that can act as input, and what UDP's are needed.
- Global Connection Management
- Instead of dealing in connection strings, the entire system deals in aliases you can assign, per database.
- Credentials can be defined globally or by user.
- Connection details are hidden from non-SQL-Hero-admins: your DBA doesn't need to share any passwords (if using SQL authentication).
- SQL Editor
- Streamlines dozens of repetative tasks: from quickly navigating to objects of interest, to building a live list of changes made, to doing sophisticated text searches, to writing SQL more effectively - and much, much more.
- Perform useful actions against result sets: from visualizing, to using filters, to comparing result sets - and much, much more.
- Get proactive warnings about possible versioning issues when objects are opened / accessed.
- Get proactive warnings about possible object breakages when object changes are committed.
- Use a powerful object filtering tool: base on lists, regular expressions, and more.
- Schema Compare
- Compare truly material differences (i.e. option to ignore spacing and/or casing).
- Intelligent scripting that deals effectively with dependencies and replication. Tested through years of production builds.
- Make allowances for some kinds of textual differences (e.g. different file groups names used in different regions).
- Schedule compares and deliver reports.
- Data Compare
- Easy-to-understand color-coding of compare results.
- Create scripts to reconcile data, including addressing constraints.
- Option to use alternate keys for comparison.
- Compare data in objects that have different names (or schemas).
- Create (Test) Data
- Create data in the form of bulk insert scripts that can be reused to establish consistent baselines for testing (and with good performance).
- Rule engine can support creating simple to complex data: anywhere from very fake, to very realistic.
- Understands relationships between tables to ensure proper processing order, uniqueness and check constraints.
- Rule engine includes settings for: cardinality, data distribution (eg. base on any existing data), randomization strategies, and more.
- Some rules offer an "exponent parameter" which can control favoring of larger or smaller numbers / dates, to match any expected distribution.
- Validation expressions can be written in C# to apply cross-table rule checking.
- Option to "scramble" existing data (eg. all or just a subset of fields that might be considered sensitive).
- Ability to include realistic names for people, companies, cities, streets, etc.
- Option to partition data files to support parallel bulk loading.
- Easy to add new data to an existing set of data.
- Able to include these steps inside automation solutions.
- Unit / Performance Testing
- Most programmatic objects (stored procedures, UDF's) can have one or more tests established - either manually or automatically generated through a rule engine.
- Tests are exercised when objects are changed and warnings provided proactively if there are failures.
- Objects can define "expected results."
- Objects can have performance targets set.
- Tests can be scripted for manual execution or automatically added during schema compare scripting to ensure schema changes have not broken objects involved.
- Test reporting allows the automatic rule engine to be applied over all objects, thus offering a way to test all objects (has been used successfully during SQL migration testing to find "problem objects").
- Testing can be run on a schedule with results available via report or notification alert.
- Tracing Tools
- Collect trace details via either SQL Profiler or Extended Events.
- Option to store trace data in the Central Repository where it can be used for reporting, searched against (eg. full-text), and retrieved.
- Trace details loaded in SQL-Hero can be analyzed in multiple ways including average timings, resource usage, and more.
- Trace details loaded in SQL-Hero can be color coded, exported in various formats, copied to clipboard for easy sharing, searched using regular expressions and other complex criteria, and more.
- Monitoring Tools
- Run a number of preset health queries or construct your own and run through the same engine which allows you to see changes in values highlighted over a sample interval.
- Fire triggers based on criteria you define related to the health queries being run.
- If using the Central Repository, capture monitoring results for future use and reporting.
- Gain a very complete picture of all system activity with reasonably low overhead (depending on your health query).
- Performance Data Warehouse
- Ad hoc reporting capabilities over many elements available in the Central Repository using an SSAS model.
- Deeply understand performance tuning efforts by comparing "before vs. after" metrics easily.
- Slice-and-dice metrics in ways to uncover issues and develop understanding of your systems.
- Command-line Automation
- Run templates, perform schema compares and apply changes, and more: many of the key SQL-Hero tasks that you'd want to automate.
- Web Portal
- Tasks related to build management (ie. "claiming objects") and reporting can be done by users who don't have SQL-Hero installed. (Requires Central Repository.)
- Reports include "issues" (test failures, performance target misses, etc.) and "activity" (object changes).
- Notification Engine
- Generate alerts (inside SQL-Hero or emailed) on certain triggers: performance targets not met, object changes, test failures, code generations, and more.
- Flexible distribution: for example, object-based notifications can go out to only those who have ever modified the objects reported on, etc.
- xSkrape
- xSkrape for SQL Server - write queries against "live" external data sources such as web pages / API's / data feeds / spreadsheets.
- xSkrape for SSIS - load data from external sources such as web pages / API's / data feeds / spreadsheets.
- xSkrape functionality is also available right within the template engine - examples are included in our template library. (For Developer edition and above.)
System Requirements
- General: OS supporting .NET framework 4.5
- Central Repository - SQL Server: SQL Server 2012 and up
- Central Repository - IIS: IIS 7.0 and up
- Central Repository - SSAS (optional): SQL 2012 and up SSAS Tabular Model
Learn more...