Blog

September 5, 2017

How Does the Delphix Data Platform Support Oracle vs. SQL Server?

Woody Evans
Woody Evans

Data Management,

DevOps

One of our premier partners shot me a message last week to help him walk through the differences between how Delphix is implemented on Oracle vs. SQL Server.  If you are unfamiliar with the Delphix Data Platform (DDP), this blog won’t make sense to you until you’ve read through Oracle Support and Requirements.  This blog provides an overview of those differences through the key perspectives that are of interest to technical folks implementing or explaining it.

Permissions for Oracle vs. SQL Server

  • Similarities
    • Access. Both Oracle and MS SQL Server need users that can access data.  Both need basic permissions to read backup data from and access the Source (usually production) host and database server.  When the Source and either the Staging or Target Hosts are in different places, there may need to be extra permissions.
  • Differences

Table of MS SQL Permissions

Component

Requirements

Method

Source

Target /Validated Sync

Environment

Delphix OS User

Windows Domain User

√

√

 

Member of Backup Operator or Local Administrators

√

√

 

db_datareader permission on master

√

√

 

Sysadmin role on SQL Server Instance

 

√

SQL Instance Should Run As

Domain Users or Local service accounts

√

√

PowerShell Privileges

Execution Policy Set to Unrestricted.

 

√

iSCSI service

Set to start Automatic in Service.

 

√

Read permission to backup share

 

 

√

Delphix Connector

Installed & addhostgui.cmdexecuted

 

√

Database

Delphix SQL DB User

db_datareader permission on master and msdb.

√

 

(SQL Authentication Account)

db_backupoperator for user databases

√

 

Network

Enable TCP/IP for JDBC

Open firewall for Port 1433(default)

√

√

Shared Memory

 

 

√

Data Collection & Connection for Oracle vs. SQL Server

  • Similarities
    • Native Backup. In general, the Delphix Data Platform (DDP) ingests data through native backup.
    • Recovery Model. Generally, we need to develop an understanding of how often the backups run, where they live, and how we gain access to those backups so that we are able to do that ingestion.
    • Use of Database Primitives. Most databases keep a pointer (aka database primitive) to identify transactions.  Backups are often keyed to these primitives.  For example, you typically must be able to have the continuous stream of transactions associated to these primitives to maintain consistency, and if you break the chain you effectively push the reset button (In Oracle, breaking the chain forces a reset logs event, e.g.) and your next backup looks like a new database.
  • Differences
    • Backup Facility.  Oracle’s native backup facility is RMAN in its various modes (Level 0, Level 1, etc.).  In SQL Server, the Delphix DDDP relies on the customer’s own native SQL Server backups in its various Recovery Models (Simple, Full) which may include T-Logs. The Delphix DDP can use pre-existing or new native SQL, Lightspeed, and RedGate backups located on an SMB share.
    • Need for Staging Server.  The Delphix DDP implementation for Oracle does not need a Staging Server.  We read directly from the Oracle database server using the RMAN facility in modes that mimic both backup and log streaming.  In SQL Server, we must use a staging server where we can ingest those backups.  That staging server has storage allocated directly from the Delphix engine.  It is this storage that allows us to manipulate the data (after its has been ingested) through an always-recovering staging database.  The Staging Server must contain an instance of SQL Server which matches the version found on the Source (but doesn’t have to exactly match the Target). To the Delphix DDP, there is no difference between the staging and the target server functionality-wise except that the O/S user that owns the instance on the “Staging” server needs to be able to find prod.  On the target server, that same owner does not need to be able to do that.  So, the staging server O/S user has a superset of the privileges that a target server owner would have.
    • Name of Database Primitive.  In Oracle, the database primitive is called SCN (System Change Number) whereas in Microsoft SQL Server it is called LSN (Logical System Number).
    • Type of Backups. The type of backups you are doing affect the freshness and granularity of the Delphix DDP TimeFlow.  See: Delphix TimeFlow in Oracle vs. SQL Server. For SQL Server, the Delphix DDP also provides the capability for Delphix to take its own copy-only backup which has no impact on the log chain.
    • Connector/Point of Access to Host.  Unlike adding an Oracle source, when we add MS SQL databases the Delphix DDP needs to use a connector (a small app that allows Delphix to communicate to the server).  We want to be as un-intrusive as possible with Delphix.  So, we don’t want to install a connector on your prod server since we only need the backup.  Instead, we install the connector on the Staging Server and the Target server.   On this Staging server, the Operating system owner of the SQL Instance into which we will be recovering your production data needs to have the capability to go and find your db and the backups for your db and be able to read them and ingest them into that staging server.  This is usually not a big deal if you are in the same Data Center, LAN, and domain.  Customers with different domains for their target, or that have a separation between Staging and Production requires permissions be granted either across domains (a cross domain trust) or specific to that user so they can access those backups on the production side.

Data Presentation for Oracle vs. SQL Server

  • Similarities
    • Common Delphix Features. Delphix Virtual Databases are generally treated the same within the Delphix DDP in terms of their ability to utilize the controls and features, particularly the data control features: Reset, Refresh, Rollback, Bookmark, Branch, etc.
  • Differences
    • Protocol.  SQL Server VDBs are presented to Target Hosts via iSCSI.  Oracle VDBs are presented via NFS v3.  Whereas the Delphix DDP uses NFS v3 for POSIX environments such as Oracle, it uses iSCSI for Windows O/S environments.  Crucially, the iSCSI that the Delphix DDP uses is NOT a hardware solution; we use a software based iSCSI.  This may require some configuration of the ISCSI services on the staging environments servers.

Supported Versions

Delphix Features: TimeFlow

  • Similarities
    • The Delphix DDP uses TimeFlow to represent the state of the database (or of a Container) in 2 ways:
      • SnapSync Cards – These represent the equivalent of a complete backup of a dataset as of a specific point in time.
      • LogSync Transaction Level Points – these represent each of the individual transaction boundaries uniquely identified by the database primitive.
  • Differences
    • Log Sync.  Log sync for Oracle is forward-facing; Log Sync for SQL Server is backward-facing depending on the last time time a new T-log was opened.  Since Log Sync can take advantage of Oracle Online and redo logs, it can build the TimeFlow in front of the last SnapSync card that was taken.  For SQL Server, TimeFlow can be granular but the granularity is a function of the last time the T-log was taken and never increments past that border.