The development/production environment includes both Oracle and Microsoft SQL Server databases.
Some of the challenges I'm seeking to address with a Data Governance process / tooling includes answering the following types of questions:
- What changes occurred from the previous to the next version of the schema?
- How can we migrate data from one version of the schema to the next?
- How can we migrate data from one environment to another?
- If we need to migrate data from a Prod to a Dev environment, how can we ensure that data is reliably 'cleansed' or 'masked' to avoid Personally Identifiable Information (PII) from'leaking' out of a Prod environment.
- How to avoid having to recreate large volumes of existing test data from scratch - when major schema and/or data changes result from application changes
Some of the desired features in a schema comparison tool would include the following:
- Command Line interface for automatic generation of reports
- DDL import of the previous/next schema definition files
- Generation of a report in PDF, HTML, RTF formats to document both schemas
- Reports to identify the deltas between the two schemas
- Generation of the DDL to alter the previous schema to look like the future schema
- Migrating data - and supplying the transformation rules as needed
- Splitting data from 1:N fields
- Combining data from N:1 fields
- Populating new data fields (via default value, lookup value, etc.)
- Computing new values f(x):y
My initial inclination is to suggest the Altova Database Schema Differencing Tool (DiffDog(R) 2012)
- http://www.altova.com/diffdog/database-schema-diff-tool.html
- ...which includes support for:
- Microsoft® SQL Server® 2000, 2005, 2008
- IBM DB2® 8, 9
- IBM DB2 for iSeries® v5.4, 6.1
- Oracle® 9i, 10g, 11g
- Sybase® 12
- MySQL® 4, 5
- PostgreSQL 8
- Microsoft Access™ 2003, 2007
Other Resources to Consider
- Oracle SQL Developer - has a schema diff capability
- Note: Requires (?) the Change Management license for your Oracle database- possibly expensive additional licensing costs?
- Oracle's dbms_metadata utility package
- Red Gate Software
- Deployment Suite - for Oracle, Schema Compare and Data Compare
- http://www.red-gate.com/products/oracle-development/deployment-suite-for-oracle/
- Schema Doc for Oracle
- Devart.com - dbForge Schema Compare
- SchemaCrawler
- SchemaSpy [recommended for documenting a schema]
- http://schemaspy.sourceforge.net/
- sample
- SchemaSpyGUI
- Apache DblUtils and scriptella
- Open DBDiff
- http://opendbiff.codeplex.com/
- Specific to SQL Server 2005/2008
