If you could give every developer a copy of the database, would you?

The simple truth is that databases can’t be given to every developer like source code.

Databases can be large, complex, and extremely difficult to copy. It is rare to see developers or even development groups that have a copy of the full production database to use for their tasks.

On the other hand, developers almost always have access to a full copy of the application code stack. Source control methods like git make it easy to clone, fork, and track code from the component level to the entire codebase.

For databases, the typical strategies are:

1. Share a full copy of the source database across a group
2. Provide small subsets of data from the source database to developers
3. Allocate schema only ‘databases’ per developer

Each of these strategies presents their own problems.

Sharing a database across a group of developers means that any change one developer makes can impact the other developers. There is no automated way to protect a group of developers from the changes of a single developer. To protect development problems that can be caused by new changes, the changes have to be reviewed. Reviews can take up to a couple of weeks, meaning the coder who wants to implement these changes has to wait. The first in a long line of delays as a result of inadequate database resources.

If developers are given subsets of the database (partial copies, only some data) or empty skeleton schemas, it will easily lead to code that has not been tested on the full range of data that is present in production. This of course opens the door to untested bugs born from outliers in the data and queries that are not optimized on the full set of data. Finding these bugs late in the development cycle is more costly by far than finding them immediately with the developer writing the code.

With Delphix you can........

Version data like code

How does it work?

According to one of our customers...

"Delphix maintains a continuous streaming backup of our production databases and allows us to create as many full copies as we want.  Copies can be masked or unmasked and are created in minutes, using minimal storage.   Delphix enables us to deliver projects faster whilst saving us time and money.”

Agile Archive

When most customers install Delphix they typically add source databases by ingesting an up-to-date backup and then establishing a timeflow of continuous changes from there, however it is possible to ingest existing backups stretching back for as long as you require.

Why would you want to ingest older backups you have already into Delphix?

There are several challenges managing backup archives, one of the biggest being trying to use them.

1.     Unless you know the required date how do you know which backup to restore, assuming you are not simply restoring production from the latest backup?

2.     The time taken to restore involves copying the files and applying recovery, usually this takes a while and requires time from Database and maybe Storage Administrators.

3.     How can you be sure you can restore from a given backup unless the restore process has been tested.

4.     In many cases access to older backups is only required for reporting and compliance reasons.  These are generally not heavy duty tasks so why restore an entire physical copy for a relatively lightweight use case?

Using Delphix as an Agile Archive

1.     The Delphix ingest process will implicitly test that your backups are valid.

2.     You can provision a Virtual Database (VDB) in minutes from any of your ingested backups.

3.     You could build a catalogue of contents of each backup by starting a VDB against the oldest version, querying the content and refreshing the VDB to the next version for each backup in turn.  You could add this metadata to your version control system.

4.     You can publish those backups via self-service to anyone who may need to access, query or run a report against an archived backup.

5.     You could optionally delete your backups once copied into Delphix as Delphix will retain them for as long as required, store them much more economically, using block de-duplication and compression, and can protect them via replication, or backup.

The process to use Delphix for Agile Archiving is very straightforward.

Using SQL Server as an example you would create a Delphix dSource specifying the initial load as a specific full backup, you would then enter the backup UUID of the oldest backup you want to ingest.

For example:

  

Then you can use the Delphix Command Line Interface (CLI) to ingest a set of backups in chronological sequence, starting from the oldest.  An example command would be:

database "AdventureWorksLT2008R2" sync; set type=MSSqlSyncParameters; set loadFromBackup=true; set backupUUID="4688F509-33E1-404D-A692-F9A0373CEF75"; commit

The UUIDs can refer to full or incremental backups which you query from SQL Server.  You can run several of these CLI commands from a single script and you will see in the Delphix UI a set of discrete timecards corresponding to each ingest.

Once your ingest sequence has completed Delphix will automatically stay in synchronization with the source database when new backups, optionally including transaction log backups, are taken.

A similar approach can be used with other database types.  For example for SAP ASE (Sybase) the CLI script to ingest a sequence of archived backups might look like:

database "pubs3" update sourcingPolicy; edit sourcingPolicy; set logsyncEnabled=false; commit

database "pubs3" sync; set type=ASESpecificBackupSyncParameters; set backupFiles=pubs3.full.10293; commit

database "pubs3" sync; set type=ASESpecificBackupSyncParameters; set backupFiles=pubs3.full.31019; commit

database "pubs3" sync; set type=ASESpecificBackupSyncParameters; set backupFiles=pubs3.full.31499; commit

database "pubs3" sync; set type=ASESpecificBackupSyncParameters; set backupFiles=pubs3.full.32083; commit

database "pubs3" sync; set type=ASESpecificBackupSyncParameters; set backupFiles=pubs3.full.34057; commit

database "pubs3" sync; set type=ASESpecificBackupSyncParameters; set backupFiles=pubs3.full.34071; commit

database "pubs3" update sourcingPolicy; edit sourcingPolicy; set logsyncEnabled=true; commit

In this example we disabled and enabled LogSync before and after the ingest sequence.

Delphix Agile Archiving provides faster and more economical access to validated, archived backups and guarantees these can be accessed near instantly and on demand. 

Accelerate ETL by Leveraging Delphix Virtual Databases

Many downstream database and analytics systems need to be routinely refreshed with processed versions of the latest changes from production.   Typically the new data needs go through an ETL process to prepare it for downstream use in an Analytics Engine, Warehouse, Data Mart, Reader Farm, etc.

There are several major benefits Delphix offers in this scenario.

Firstly Delphix maintains its own up-to-date copy of source data by seamlessly synchronising with the source databases and retaining this in a secure central data hub.

Second, all ETL processes can be run directly from virtual databases (VDBs) provisioned by Delphix, completely offloading production and without the need for physical copies.  You can create as many VDBs as you want to run these processes in parallel.

Third, if you wish to keep pace with new transactions you can use Delphix Live Sources, which uses an Oracle Standby to keep the Delphix dSource copy continuously updated but also enables reporting using Oracle Active Dataguard.  If synchronising with a target Hadoop cluster you can run Apache Sqoop jobs against a virtual data warehouse that is constantly being updated from the physical data warehouse.  You can run your ETL directly on the changed data stored in the virtual data warehouse and import only the processed changes into your Hadoop cluster.

In addition it is also possible to use Delphix to help identify deltas between two versions of a source database by creating two VDBs based on two point-in-time snapshots.

If the two VDBs (snap1 and snap2) are based on an Oracle source database, first you simply create a database link connecting to snap1 from snap2.

On snap2 you create tables to store:

1.     INSERTS, rows that have been added since the old version (snap1)

2.     UPDATES, rows that have been modified between the two versions

3.     DELETES, rows that no longer exist in the new version (snap2)

You can identify INSERTS, UPDATES and DELETES between the two versions using the MINUS set operator between the corresponding tables in the two VDBs.

For example to find and populate my local table “delta_inserts” with new rows, I use something like:

           

/* create empty table with correct structure */

create table delta_inserts as select * from table where 1=0;

alter table delta_inserts nologging;

/* populate table with new rows only */

insert /*+ append */

  into delta_inserts

  with new_rows as

  (

    select id from table@snap2

     minus

    select id from table@snap1

)

/* fetch entire row from latest version */

select latest.*

  from table@snap2 latest,

       new_rows delta

 where latest.c_id = delta.c_id;

The SQL is similar for UPDATES and DELETES.  However for UPDATES we need to compare the entire row in the minus operation since any column value might have changed.  For DELETES we only need to store the key or id we will use to identify the rows to delete during the apply process.

Now we have two tables containing copies of changed rows and one with a list of rows to be deleted.

We can now run our ETL processing on only the changed rows held in delta_inserts and delta_updates making this process substantially more efficient than running against the full dataset.

The final phase is to implement an apply process to merge the processed deltas with the target database.  The options for applying the changes will depend on the nature of the changes and the type of the target database which might be relational, Hadoop (HDFS), etc.

For Hadoop you can use Apache Sqoop to perform incremental imports and merges.  For relational databases there are more options.

The apply process would usually

1.     delete all the rows in the target table having the keys saved in delta_deletes

2.     insert or append all the rows to the target table having processed rows saved in delta_inserts

3.     merge or update all the rows in the target table having processed rows saved in delta_updates

If there are foreign key dependencies then you might need to disable and re-enable them before and after the apply process.

In summary, Delphix is ideal for provisioning data sets for ETL processing.  Delphix Virtual Databases (VDBs) can also underpin the process to identify changes between two versions of the same database. ETL jobs can then operate against only the changed rows and finally once processed these changes can be used to refresh the target database using a simple apply process.

Learn more at Delphix Agile Data Management