An overview of the similarities and differences between how Delphix is implemented on Oracle vs. SQL Server.
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.
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.
Exact users/permissions. Oracle needs POSIX O/S User and Oracle database users with correct permissions. MS SQL Server needs Windows Users and SQL Server Users with correct permissions. For Oracle, see: Requirements for Oracle Source Hosts and Databases and Requirements for Oracle Target Hosts and Databases. For SQL Server, See: Requirements for SQL Server Source Hosts and Databases and Requirements for SQL Server Target Hosts and Databases and Requirements for SQL Server Validated Sync Targets.
Point of access to DBMS. The Delphix DDP implementation for Oracle uses the RMAN facility connected directly to the Oracle Database server on the Source Host. The Delphix DDP implementation for SQL Server uses data stored at a backup location as well as a connection to the SQL Server Database on the Source Host. See: Requirements for SQL Server Staging Targets. By extension, the users on the Staging Server must have permission to see the backup location of the Source to function properly. The Source Environment must be in the same domain or have a domain trust relationship with the Target Environment.
Staging Server Requirement. A Staging Server is required for MS SQL (as this is where the backups will be mounted for an always-recovering database.) In Oracle, a staging server is not necessary but if it is in place can be used for the Validated Sync feature.
Table of MS SQL Permissions
Target /Validated Sync
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
Execution Policy Set to Unrestricted.
Set to start Automatic in Service.
Read permission to backup share
Installed & addhostgui.cmdexecuted
Delphix SQL DB User
db_datareader permission on master and msdb.
(SQL Authentication Account)
db_backupoperator for user databases
Enable TCP/IP for JDBC
Open firewall for Port 1433(default)
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.
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.
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.
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 of SQL Server: 05, 08, 12, 14. See: Supported Operating Systems, Server Versions, and Backup Software for SQL Server
Supported versions of Oracle: 9208, 10, 11, 12c, 12c MT. See : Supported Operating Systems and DBMS Versions for Oracle Environments.
Note: Delphix recently announced it was sunsetting support for 188.8.131.52
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.
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.