As an IT manager can you recall the last system outage caused by a database schema change? Do you sometimes consider how to better serve project staff with tools to reduce the inevitable disruption?
If not, then congratulations are in order because your change control and your remediation procedures are excellent!
Unfortunately, most of us can recall recent problems stemming from database changes.
... And every time it happens, we reassess how changes that are poorly documented or poorly communicated can be destructive.
Typically, our DBAs support dozens of schemas, each with several database environments and sometimes across more than one RDBMS. They build, they share, and they deploy database object creates, alters and drops to stay current with application upgrades and patches. Often up to thousands of DDL changes per month.
And how to prepare project teams for upgrading downstream systems?
DBAs manually document and share schema changes – they post to email distribution lists to make sure that everyone has ample time to update their code.
Unfortunately, the occasional change will “fall through the cracks”, causing some process to fail, some error or performance problem, or worse, complete system outage. Ouch.
Our post-mortem reviews often show that the change was not fully documented or was missed entirely by downstream project teams.
Sometimes DBAs will try to solve this problem with database triggers or object registration that capture schema change events. But these methods impact performance and require manual preparation to filter and make sense of change.
We have learned to accept the myth that a fluid database schema will occasionally lead to system failures.
Until now. Now we have SchemaTrack – a full-stack software solution employing its meta-capture job to detect, record and share the create, alter and drop operations on tables, views, indexes and constraints.
Meta-capture invokes high performance queries on system views to do this work for Oracle, SQL Server and DB2 databases across the enterprise.
SchemaTrack guarantees project teams are always one or two clicks away from planned, deployed and historic database changes.
A tool that accelerates respect between IT leadership, DBAs and programming staff, SchemaTrack brings new heights to project team spirit, cohesiveness and productivity.
Not sure about this? Ask any programmer what it feels like to chase a bug or a performance issue for half the day only to find the root cause to be a schema change they were not made aware of. Ouch!
Sample for yourself how your technical staff will use a comprehensive view of planned, deployed and historic schema changes. Without any registration or delay, click Live Demo for our Amazon cloud (AWS) release of SchemaTrack. Instantly see how your own project teams will gain confidence and appreciation in the new “DBA transparency platform”.
As a DBA, when you release an upgraded DEV environment, do you include documentation of the creates, alters and drops? Or do you rely on a programmer’s compile to flush out schema changes? If it’s just the compile, do you also include that awkward guilty feeling?
There has got to be a better way. These programmers are on our team and they deserve better.
And it's especially true for software packages since vendors rarely if ever include documentation on back end changes.
When we have in-house development or customizations, we DBAs do our best to document and share the schema changes for tables, views, indexes and constraints. Unfortunately, we build these documents manually and rarely do they stay in sync with our DDL deployments.
Can we run a few diff queries to identify differences between new DEV and old DEV? Certainly. Do we maintain these diff queries or do they slide around from project to project, creating a maintenance burden? Typically, we end up coding last minute adjustments to “get to the next project”.
And what about automated email alerts for changes?
Our project teams should automatically get an email alert to database changes - packaged as a group in a thoughtful browser presentation that include before and after definitions. When changes go into DEV, they get an email. UAT, another email. PROD, a third email, etc.
Some of us have tried to provide this automation with database triggers. But database triggers are cumbersome and rife with problems, especially sending email alerts. Again, over time they become a burden and we tend to avoid them altogether.
SchemaTrack is a full-stack software package that solves all these problems - without triggers, tracing, auditing or even connecting to the schemas being tracked. Your 60-day trial can be downloaded right now and be up and running in about an hour in your organization.
See for yourself how your programmers will be one or two clicks away from planned, deployed and historic database changes. Without any registration or delay, click Live Demo for our Amazon cloud release of SchemaTrack. Instantly see how your own project teams will gain appreciation and confidence in the new “DBA transparency platform”.
We are always on guard for disruption caused by a schema change that we were not prepared for.
Sometimes we get lucky because our code throws a compile error which points directly to the schema object that was changed. In this case, it only takes a minute to make a quick code change and we are back up and running.
Other times the change is not obvious - our code still compiles but the schema change disrupts what used to be a well performing query.
Ever had the column sequence of an index changed?
Hopefully we get lucky again by quickly identifying the full table scan. But often for a complex query with over 100 tables, views, virtual queries and sub-queries, we waste precious head-down time figuring out which index was changed.
There are many kinds of creates, alters and drops that will impact what was once a stable system. If we are not made aware of them, our application code breaks down and our users suffer the consequences.
Our DBAs provide documentation of changes, but this task becomes unwieldy as their DDL scripts continue to be changed.
Shouldn't there be a system that makes use of structured meta data to solve these problems?
Great news - SchemaTrack does exactly this. With a periodic meta-capture job that invokes high performance queries on system views to detect and record creates, alters and drops on database object types that programmers care most about – tables, views, indexes and constraints.
Without using tracing, auditing or database triggers, without any mining or object registration, SchemaTrack provides web pages that allow project teams to “watch” their schemas evolve via email alerts, drill-down search, sort and compare features as well as useful count and summary links.
With SchemaTrack Web UI, you can “describe” an index or constraint just like describing a table or view. All four object types have new features never before seen in a database tool – like an audit dropdown that shows for example…
1. Here is what MY_TABLE looked like when it was first created during the application 1.0 installation.
2. Next, we see before and after definitions of MY_TABLE when it was altered during the 2.0 upgrade. The yellow highlights draw our eyes directly to columns that were added or removed as well as columns that had nullable change, data type or length change, etc.
3. Lastly, we see the definition of MY_TABLE when it was dropped during the 4.1.2 patch.
Programmers are never left out of the loop as SchemaTrack’s automated email alerts provide notification of changes - by schema and by database environment.
There are two ways to sample these features right now. Without any registration or delay you can use Live Demo (AWS), or your DBA can download a free trial of SchemaTrack and be up and running in about an hour in your organization.
GDPR readiness is supposed to be a “step change” for personal data protection and management.
Our data architects tell us that the biggest challenge will be unstructured data while readiness for structured data will be quicker.
OK, so let’s take the first step and handle structured data. We'll make progress and lessen the chance of an audit.
Oracle, SQL Server and DB2 databases all have similar meta data so we should be able to perform discovery of personal data sources quickly.
Why then, are we told that even for structured data, this will take weeks, maybe months to complete?
Because until now we did not know about SchemaTrack.
By lunch time, you can show all databases across the enterprise with tables and views that have a column name containing LAST_NAME, SOCIAL_SECURITY, etc.
Still before lunch time - the same list but this time with table or view name containing PERSON or ACCOUNT, etc.
How about one more filter... only for tables and views that have had DDL change (create, alter or drop) in the last 24 hours? The last 30 days? For the lifetime of the schema? Done!
The above examples include all Oracle, SQL Server and DB2 instances across enterprise IT. With browser page response times under 1/4 second, and Without the use of database triggers, tracing, DBMS.compare(), log mining, script mining and without even connecting to the schemas that are being tracked.
SchemaTrack consolidates meta data into its own persistence layer with high performance queries on system views.
Your DBA can deploy a free trial copy of SchemaTrack in about an hour in your organization. To sample how you will perform enterprise-wide searching, click Live Demo for our Amazon cloud release of SchemaTrack.
Let's show the data architects what can be accomplished by lunch time.
Project team access to thorough technical reviews of planned, deployed and historic database changes across the enterprise.
Rich web browser UI for project teams to Search, sort and compare current and changed database object definitions for object types that programmers care most about - tables, views, indexes and constraints.
Automatic email alerts to database object create, alter and drop operations - without using database triggers and without connecting to the schemas that are being tracked.
Push back on new costs, slash traditional costs. Awesome new tool for systems research, impact analysis and data governance.
This software is available and there are two ways to use it right now. Use the live demo (AWS) on this website, or download your free trial and be up and running in about an hour in your organization.
In a single day, the DBA drops and re-creates a table 10 times until it fits the requirement. Should we keep track of all these changes? No... What we should do is keep track of net change. Schematrack's relational algebra captures the table definition at the end of the day minus the table definition at the beginning of the day. Net change means we can ignore most changes and it means we can provide useful presentations with before and after definitions that can be used across IT initiatives. This is achieved with Schematrack’s periodic meta-capture job for an unlimited number of schemas selected by the DBA.
What if a schema has 650 views to end the day and 600 views at the beginning of the day? Meta-capture records Views Created(50). Reverse the equation for Views Dropped and add more detail to the equation for Views Altered.
All content is derived from system views - Schematrack never connects to schemas that are being tracked.
When DBAs provide this kind of transparency, they up their game. Everyone else’s tech. goes up too.
GDPR search tool for structured meta data - "Show me all tables and views for PROD schemas with the name containing ACCOUNT that have a column name containing SOCIAL_SECURITY", etc.
SOX API for COBIT reporting - stored procedures fit your favorite reporting tools.
SchemaTrack automates the periodic capture and persist of database object definitions for static and fluid RDBMS schemas. This content is the source for web application research, notification and governance tools that keep IT project teams compliant and up to date with their databases.
SchemaTrack invokes queries on system views without using triggers or agents. The full-stack software starts with batch job “meta-capture” and ends with user and administration web applications. SchemaTrack can be installed on-premise or in the cloud.
Meta-capture is the iterative routine that records current and changed schema object definitions for object types that programmers care most about - tables, views, indexes and constraints. Current definitions are objects found by the most recent meta-capture whereas changed definitions are the creates, alters and drops occurring between meta-captures. Meta-capture loops through local and remote system views to determine this information for an unlimited number of schemas selected by the DBA.
Change-group is SchemaTrack's lowest level grouping tag that mirrors release code or version number of the schema's front-end application. Typical values might be 1.0, V22.214.171.124, Rel2, etc.
Change-groups are declared by the DBA to organize schema changes with application upgrades and patches. Additionally, adhoc change-groups can be used with descriptive names such as "Refresh from PROD", "Hot fix SalesReport error", etc. In all cases the schema’s evolution is made clear with change-groups sequenced in the way that changes are occurring.
To understand how SchemaTrack works requires some familiarity with meta-capture and how/when it runs. The DBA can run it manually from the UI, and can set it to run automatically via meta-capture's schedule interval setting. The interval defines when and how often meta-capture will run. Typically, meta-capture interval is set to run nightly though it can be configured to whatever interval is needed. For example, for a schema in the DEV environment, meta-capture might be set to run nightly which will consolidate the DBA’s daily changes, whereas the same schema in PROD might be set to run hourly or every minute to provide quicker notification of database changes.
Meta-capture employs relational algebra to compress and simplify each schema’s evolution. To illustrate, suppose a simple schema has three tables, tbl1, tbl2 and tbl3. After some time, the DBA creates a new table, tbl4. This DBA runs DDL scripts throughout the day until he/she is satisfied that tbl4 is complete. The following morning, the only database changes that project teams will care about are that tbl1, tbl2 and tbl3 are still there and were not changed (assume no RI or IDX tbl4), and there is a new table, tbl4. Regardless of what DDL ran the previous day, meta-capture will report net change as Tables Created (1). To achieve net change without compromising performance, meta-capture’s nightly run captured the four tables in the schema, subtracted the three tables from the previous meta-capture and recorded the difference as Tables Created (1). Reversing the equation provides Tables Dropped (0) and adding more detail to the equation provides Tables Altered (0). Though very simple, this example does illustrate how SchemaTrack determines most types of change for each of the schema object types.
* Annual license includes unlimited user seats. On-prem or cloud software is for Oracle, SQL Server, or both (similar to the live demo on this site). DB2 will be available soon.
** For Gold Support option details, please call SchemaTrack Support, (925) 980-3217.