Best practices for Source environments and databases

Best practices

Oracle

1. ARCHIVELOG must be enabled: select log_mode from v$database. 

2. FORCE LOGGING should be enabled to ensure VDBs are not missing data. When NOLOGGING redo is applied during provision, the resulting VDB will be missing changes. Tables with NOLOGGING changes will throw corruption errors when scanned. 

3. Block Change Tracking should be enabled to minimize snapsync time. 

4. Consult the documentation for Oracle Standby sources. 

5. If the database is encrypted with Oracle TDE (Transparent Data Encryption) plan your Delphix storage requirements with the expectation of minimal compression. Customer Observation: space usage for a TDE dSource copy was 92% (2.44 TB) of the source database size (2.67 TB). A typical Oracle dSource copy for a non-TDE database consumes 40% of the source database size. 

SQL server

1. If using a FULL recovery model, configure your dSource to stay synchronized using Transaction Log backups. This will usually allow the dSource to stay in sync using much less network and disk IO than Full or Differential backups. 

2. Ensure that the number of Virtual Log Files (VLFs) in the Source database is appropriate. Databases with hundreds or thousands of VLFs will experience slower provisioning times as SQL Server must do more work during recovery. The blog post A Busy/Accidental DBA’s Guide to Managing VLFs has helpful information here. 

3. Review index maintenance and defragmentation operations on your Source database, to ensure that they are not running more often than necessary. Many maintenance operations are logged and can result in extremely large log backups without much benefit to the database server. See Stop Worrying About SQL Server Fragmentation. 

4. If configuring a dSource to use Full or Differential backups, ensure that Source database backups do not run at the same time as database maintenance or large batch operations. This can significantly increase the amount of time required to perform database recovery and will slow down the provisioning of VDBs. 

5. For best compatibility with the Delphix Continuous Data Engine, avoid taking log backups more frequently than every 10 minutes. The validated sync process requires time to detect and validate log backups before they can be applied to a dSource, and extremely high-frequency log backups can make it difficult for a dSource to stay in sync. 

6. If you are using SQL Authentication for database logins, and you want Source database users to be mapped to database logins when provisioning VDBs, ensure that the SID of your database logins is the same between your Source and Target environments (the passwords can be different). For more details, see the Correcting Orphaned SQL Server Database Users (KBA1111) KB article. 

7. To maximize dSource performance, ensure that backups are being taken to a disk drive or network location that can be accessed by the Staging Server at high speed.