Best practices for Target environments and databases

Target database application settings

1. Oracle:

   1. Provision with 3 x 5GB online redo logs (minimum) to avoid pause when transaction logs wrap around.

   2. Provision in NOARCHIVELOG mode to reduce transaction log IO. Masking, Testing, and QA VDBs rarely need point-in-time rewind

   3. Always check initialization parameters inherited from a parent, and remove any expensive or irrelevant parameters.

      1. DB_CACHE_SIZE, SGA_TARGET: set based on the target system being compared to.

      2. FILESYSTEMIO_OPTIONS to SETALL. Any other setting inherited from the source is probably wrong.

      3. DB_BLOCK_CHECKSUM, DB_BLOCK_CHECKING, DB_LOST_WRITE_PROTECT, DB_ULTRA_SAFE: set to default values to minimize impact.

      4. PARALLEL_DEGREE_POLICY to AUTO, PARALLEL_MAX_SERVERS default, PARALLEL_EXECUTION_MESSAGE_SIZE to 32768 (maximum): improve PQ performance.

      5. FAST_START_MTTR_TARGET: drives steady write activity. Set based on the target system being compared to.

      6. Consider non-durable commits for Masking, Test, QA, UAT: set COMMIT_WAIT = NOWAIT, COMMIT_LOGGING = BATCH

   4. Use Oracle Direct NFS (dNFS) for 11.2.0.4+ (unstable on older releases):

      1. Recommended documentation:

         • Oracle Direct NFS configuration

         • Configuration examples and troubleshooting blog from Helmut Hutzler

      2. Set DNFS_batch_size = 128 (default is 4096). This is a good starting point and sufficient for most workloads.

      3. Tune TCP stack: set tcp_adv_win_scale = 2 to workaround hard-coded Oracle dNFS TCP buffer size.

      4. Check the Alert Log, V$DNFS_SERVERS, V$DNFS_FILES, V$DNFS_STATS to verify proper working (sample here).

Memory and CPU

We suggest deploying the Target hosts with resources that initially resemble your production environment, monitoring the load during the VDB workload(s), and then reducing the resources as needed. The database type, VDB workload, and various other factors can cause significant differences in the required target resources.

If the Target host is being shared or is also a Staging host, ensure that resources can support the combined load of all ingestion policies and VDB workloads.

Windows and MSSQL-specific

The SQL Server Instances hosted on the Targets should have a Maximum Memory set. Also ensure that at all times, at least 10% of total memory and at least 4GB of available memory is available for OS operations.

Network requirements

Target hosts should have < 1ms latency to the Delphix Engine.

Target Host OS Settings

1. Existing documentation on Target host OS practices: Target host configuration options for improved performance

2. HP-UX 11.31+

   • Async NFS direct I/O: HP-UX requires Oracle disk_asynch_io turned off for filesystems

3. IBM AIX:

   1. Consult IBM documentation on AIX TCP Tuning

   2. AIX TCP tuning prezo

4. Windows:

   1. Anti-virus programs can impact both performance and operation. Delphix recommends anti-virus scanning exclude folders where Delphix files are maintained, in addition to the normal exclusions put in place for MSSQL operation.

   2. Delphix Connector (aka DX Connector):

      1. Plan 3-5GB for the Delphix Connector installation.

      2. Windows does not yet have ssh, so Delphix developed the "DX Connector for Windows target host communication.

      3. The connector must be installed on all Windows Target hosts.

      4. The connector supports two modes – v1 and v2 both use the same application binaries.

      5. The connector v1 process is used to bootstrap the v2 process on a target. This opens a DSP session back to the Delphix Continuous Data Engine (the same thing is done via SSH on *nix Target hosts)

      6. v2 mode is required to enable SQL hooks

      7. The connector can always be downloaded from a local Delphix Continuous Data Engine at: http://<delphix_engine>/connector/DelphixConnectorInstaller.exe.

      8. The connector is backward compatible, so it is not always necessary to upgrade it during a Delphix upgrade.

   3. iSCSI connections:

      1. Read the following for general awareness of iSCSI limits

      2. In addition to the hard limits on iSCSI connections, consideration must be given to the RAM, CPU, and Network to provide sufficient resources for the load on any Target host or Staging host.

      3. To increase the iSCSI timeout on both Target and Staging hosts.

      4. In certain circumstances, it's possible that the iSCSI startup will not complete before the SQL Service attempts to start a database. In such circumstances, it can be helpful to ensure the SQL service depends on the iSCSI service.

         •  Example: c:\> sc config "MSSQLServer" depend="Microsoft iSCSI Initiator Service"

      5. Note that any changes to iSCSI are system-wide and could potentially impact other applications also leveraging that feature.

   4. Enable receive side scaling (RSS) on each network interface that the Delphix Continuous Data Engine will be connecting to.