SQL Schema Governance

ExaOps easily exports all key aspects of a database in an automated and configurable manner for use in the development of all kinds of software.

It is important, however, to consider what are the key aspects of a database that relate to software development.  In general, most people think of the schema but there are at least three types of information to keep in mind:

  • Schema – table definitions, indexes, stored procedures, triggers and all the other myriad DDL (Data Definition Language) that can be used to fully recreate a database on a blank server.
  • Configuration data – control tables or configuration tables containing data that starts a system up(???). These tables can include state names, tax rates, country codes or other data that are not transactional in nature. Typically these configuration tables are small in size, but these critical elements can often be missed when exporting only the schema of a database.
  • References – content that is stored elsewhere but referred to by a database.  Databases may contain file paths stored within a table that refer to files on a file share, or URLs that refer to content stored within SharePoint.

ExaOps makes capturing such complex information straightforward.  In addition, it includes tools that can automate extraction of these types of content, and tools which help identify the content already stored within Enterprise systems.

Exporting Database Schema and Configuration Table Rows

Exporting a Database Schema

Exporting an entire database schema to SQL is simple with ExaOps.  In addition, we provide a wide range of features that enable the export process to match common business requirements.  These include:

  • Running the schema export on a timed / daily basis – Scheduled Tasks
  • Command line and PowerShell support
  • Exporting the schema to files as well as other output storage types
  • Content filtering for export – including or excluding elements or item patterns, e.g. “Exclude: log*,temp*”; excluding database object types; including or excluding Views of a particular type
  • Advanced filtering includes:
    • Type of object: the item of database object such as Table, View, Stored Procedure, UDF
    • Schema: DBO and any other schemas the database uses
    • Name: Name patterns such as “Audit*”

Exporting Configuration Data

Databases typically include near-static data tables that relate to the configuration of the system (what system?).  This data consists of information such as lists of states NSW / VIC (Australia) or CA, TX, MA (US) etc.  ExaOps’ functionality permits this data to be exported in addition to the schema.   

ExaOps can identify these configuration tables and export the content as part of a configurable backup strategy for all important elements of a database including the schema.

The content can be exported in a form that is suitable for tracking changes such as JSON files, XML or other easy to use textual formats.

Exporting Referenced Information

Databases can include data rows that reference information stored in other systems such as files on file shares. A data row may include a row such as “TemplateFile:\\templateshare\docs\Template.Docx”.  ExaOps provides features to follow these referenced links to the file mentioned and copy the contents to a release directory or source code repository.

Recommended Posts