|
The Gnosis Business Rule Manager application manages the schema of an Auditable Database and a Gnosis Transaction Database. For an Auditable database, the schema of the on-line database can be extracted directly for the creation of the Auditable database, and hence an Auditable database (transaction subscriber) can be setup very quickly by utilising the Migrate Schema capability that is provided.
The Business Rule Manager offers the Migrate Schema capability, that can take an existing SQL database schema and import all Tables, Columns, Rules and Constraints into the Gnosis Database definition.
|
The Gnosis Business Rule Manager Migrate Database Schema form, in this case migrating the Northwind database into the Gnosis Logical Database GnosisEvaluation. |
After a schema definition is migrated into the Gnosis Business Rule Manager the following steps are required to create a Gnosis Auditable or Gnosis Transaction database.
- Check database definition. The Business Rule Manager requires a schema to obey a set of rules, before it may be generated into a database.
- Generate database. The Business Rule Manager must generate the database objects before the database is available for use.
- Migrate/Synchronise Data. An auditable database subscriber requires a copy of the on-line publisher database to be brought across before auditing of transactions can begin.
- Define Auditable Database Archive(s). To implement an Auditable Database an archive must be defined and created.
- Generate Publisher/Subscriber scripts. To implement an Auditable Database (as an off-line Transaction Subscriber), scripts will need to be applied to the on-line (publisher) database. These scripts are automatically created for you from the Business Rule Manager application.
|
Technical Considerations
The following sections review some technical considerations that are important for database migration.
Defining Identical Structures
A Gnosis database is comprised of Gnosis-tables, which
generate into sql-server view objects. In a Gnosis Transaction database these sql-server view
objects ustiliseinstead of triggers that are designed
to make the sql-server view object behave similarly to a table.
Hence when an existing schema is migrated to Gnosis Transaction database,
existing structures are duplicated as sql-views and many
SQL Statements will be able to run unchanged. A Gnosis auditable database works in a similar fashion, in the case of a Auditable database subscriber, the instead of triggers do not allow changes to data.
|
This diagram tries to illustrate a typical
application interfacing to a typical database of tables. |
 |
This diagram illustrates a Gnosis database, where
the table structure of the original database is duplicated
using sql-server views, which utilise instead of
triggers to manage data in underlying (SQL) tables. |
Migrating Applications
Applications migrate best when they use a stored procedure interface between the application and the database. The Gnosis database will contain sql-view objects of the same structure as the original tables, hence most stored procedures will port to the gnosis sql-views and run, of course, thorough testing of your applications stored procedures will be required.
Migration difficulties will arise where there are application controls that bind directly to tables, in such cases, we suggest that your application be rewritten to use stored procedures.
Migrating Web Applications
The same porting issues will arise for web applications as for windows applications, and thorough testing will be required.
Where your web server uses one database session for many web-user sessions, some changes are required to be made to identify the user to the gnosis database components. This can be achieved by calling a stored procedure that saves the web-user and @@spid into a table in the database. Depending on how your system already works, this can easily be written into existing stored procedures or programmed from the web-server.
Partitioned Tables
The Gnosis Business Rule Manager generates an Auditable Database to use partitioned tables. The partitions are based on audit archives, and on audited transactions that are stored within an archive.
Migration Risks
For a Gnosis Transaction database existing trigger processing will need to be investigated. We suggest that existing trigger processing be moved to stored procedures. Systems that employ a lot of trigger processing will be difficult to migrate.
The most significant risk associated with migrating to a Gnosis Auditable database will be in poor query performance plan choices made by the SQL-Server query optimiser. Where these situaltions occur you will need to write specific query processing and we have suggestions and tips on how to best do this.
Replication
The Gnosis Auditable database is designed to work Transactional Replication from the on-line system to the Auditable database. The on-line system requires a column to be added to each table, 3 additional tables and processing in instead of triggers. The Gnosis Business Rule Manager provides/generates scripts for the implementation of these changes to your on-line system.
|
