Skip to main content
Skip table of contents

Overview of requirements for SQL Server environments

SQL server staging hosts and databases

This document identifies the requirements for interactions between the Delphix Engine and SQL Server environments and outlines the set of system tables to which we currently require access.

Delphix SQL server architectural diagram - Traditional pull architecture

This diagram depicts the environments and hosts with which the Delphix Engine interacts. Each type of environment has different requirements, which are described below. 

Delphix SQL server architectural diagram - staging push architecture

This diagram depicts the Staging and target hosts with which the Delphix Engine interacts. Note that the Delphix Engine does not interact with the Source Database. For more details on the Staging Push mechanism, see Staging Push Mechanism with SQL Server and Staging Push Implementation for SQL Server.

SQL server source hosts and databases

Source host requirements

Windows servers that will be added as Source Environments must meet the following requirements:

Source Host Requirement

Explanation

To allow Delphix-initiated backups, the service account running each SQL Server instance (the Instance Owner) should be one of:

  • A domain user (e.g. MYDOMAIN\accountname) (RECOMMENDED)

  • A Managed Service Account or Group Managed Service Account ( MYDOMAIN\accountnameundefined)
    (requires Windows 2012 and later, and SQL Server 2008R2 or later)

  • The LOCAL SYSTEM account (NT AUTHORITY\SYSTEM)

  •  The NETWORK SERVICE account (NT AUTHORITY\NETWORK SERVICE)

Backups initiated by the Delphix Engine (Delphix Managed Backups or manual snapshots which request a backup) will fail if the service account cannot access backups created by the Source database instance

The source host, proxy and staging environments must have appropriate cross-domain trust relationships

For more information on these requirements, see the document Delphix in Multi-domain Windows Environments.

Recommended source Windows user requirements

Aligning to our zero-trust approach, “Delphix OS” user permissions on the Source can now be configured with the least privilege necessary from previous super-user “Backup Operator” requirements.

Recommended Source Windows User Requirements

Explanation

Have the "Log on as batch" privilege on the source host

This permission is required for remote PowerShell execution. This privilege can be assigned through the Local Security Policy (Local Policies → User Rights Assignment → Log on

Have read permission for "Computer\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\ Control\SecurePipeServers\winreg"on the source host

This permission is required to have access to the remote registry. Delphix uses this privilege to discover SQL Server instances and gather system details, using Windows remote registry access.

Be a member of the Users group on the Staging Host

In order to discover and query SQL Server instances as the Source Windows User, scripts are run on that user at the Staging Host.

Have the "Log on as batch" privilege on the Staging host

This permission is required for remote PowerShell execution. This privilege can be assigned through the Local Security Policy (Local Policies → User Rights Assignment → Log on as batch job).

Be able to login to each SQL Server instance that the Delphix Engine will communicate with

These requirements are described in the Source Database Login Requirements section below.

This recommended permission requires editing the registry on each Source host(s) so that membership of the Backup Operators group is not required. The Backup Operators group confers additional permissions that are not used by Delphix, such as being able to shut down the host.

Deprecated source Windows user requirements

Source Windows User Requirement

Explanation

Be a member of the Backup Operators group on the Source Host

Delphix uses this privilege to discover SQL Server instances and gather system details, using Windows remote registry access.

Be a member of the Users group on the Staging Host

In order to discover and query SQL Server instances as the Source Windows User, scripts are run on that user at the Staging Host.

Have the "Log on as batch" privilege on the Staging host

We require this permission for remote PowerShell execution. This privilege can be assigned through the Local Security Policy (Local Policies → User Rights Assignment → Log on as batch job).

Be able to login to each SQL Server instance that the Delphix Engine will communicate with

These requirements are described in the Source Database Login Requirements section below.

Source database login requirements

The Delphix Engine requires SQL Server logins to be created on each SQL Server instance that the Delphix Engine will communicate with:

  • A database login for Environment discovery and monitoring, specified when Adding an Environment. This must be a Windows Authentication login for the Source Windows User configured in the previous section.

  • A database login for dSource (Source Database) monitoring and interaction, specified when Linking a dSource. This user can be:

    • The same as the Source Windows User;

    • A different Windows Authentication login (this user must also have Log on as a Batch Job privileges on the Staging host); or

    • A SQL Authentication login

These users must have the following permissions on each instance:

Object

Privileges Required

dSource User

Environment User

Purpose

Server

CONNECT SQL

Y

Y

Access to the SQL Server instance

Database: master

db_datareader

Y

Y

Access to information about attached databases

Database: msdb

db_datareader

Y


Access to the backup history

Each user database to be linked

PUBLIC

Y


Delphix will periodically run queries to check the current size of the database

Each user database to be linked

db_backupoperator

Y


Optional: Required for backups to be initiated by Delphix (using Delphix Managed Backups, or when Delphix initiates a backup during a manual Snapshot)

Server

VIEW ANY DEFINITION

Y

Y

Optional: Required for the discovery of databases in Availability Groups

Server

VIEW SERVER STATE

Y

Y

Optional: Required for the SnapSync and discovery of Availability Groups

Object: master.dbo.sqbutility

EXECUTE

Y


Optional: Required when using backups created by Red Gate SQL Backup

Object: master.dbo.xp_sqllightspeed_version

EXECUTE

Y


Optional: Required when using backups created by Quest LiteSpeed for SQL Server

List of source tables accessed by the Delphix engine

Using the db_datareader permission, the Delphix Engine accesses the following system tables in the master and msdb databases on the source host:

System table

Justification

master.sys.databases

Used to determine the name and recovery model of databases within discover SQL Server instances

master.sys.availability_groups

Used for discovering all the availability groups within an Availability Group source environment.

master.sys.availability_group_listeners

Used for discovering all the availability group listeners within an Availability Group source environment. 

  • A requirement for dSource linking of SQL Server clustered databases (replicas) is to provide an AG (Availability Group) listener for AG cluster source discovery. This is implemented on AG cluster source discovery as a failsafe if AG cluster source database authentication configuration change down the line, ensuring the Delphix engine has a way to reach the cluster and continue certain operations.

master.sys.availability_databases_cluster

Used for discovering all the availability group clusters within an Availability Group source environment.

master.sys.availability_replicas

Used for discovering all the availability group replicas within an Availability Group source environment.

master.sys.database_files

Used to determine the size of databases and whether filestream is enabled for a database

master.sys.dm_exec_requests

Used to enable Delphix to report backup operation progress

master.sys.master_files

Used to determine the primary file of a database

master.sys.filegroups

Used to determine the filegroups of a database so that Delphix can configure VDBs with the same filegroups

msdb.dbo.backupset

Used to determine new backups that have been taken. This table is regularly queried to find out if a new backup image has been taken and needs to be synchronized with Delphix.

msdb.dbo.backupmediafamily

Used to determine the physical device names of the backup files comprising a backup.

SQL server staging hosts and databases

Staging host requirements

Windows servers which will be added as Staging Environments must meet the following requirements:

Staging Host Requirement

Explanation

The service account running each SQL Server instance (the Instance Owner) must be one of:

  • A domain user (e.g. MYDOMAIN\accountname) (RECOMMENDED)

  • A Managed Service Account or Group Managed Service Account ( MYDOMAIN\accountname$)(requires Windows 2012 and later, and SQL Server 2008R2 or later)

  • The LOCAL SYSTEM account (NT AUTHORITY\SYSTEM)

  • The NETWORK SERVICE account (NT AUTHORITY\NETWORK SERVICE)

Access to existing database backups for Snapshots and Validated Sync operations will fail if the service account cannot access backups created by the Source database instance.

The source host, proxy and staging environments must have appropriate cross-domain trust relationships

For more information on these requirements, see the document Delphix in Multi-domain Windows Environments.

The edition of the installed SQL Server instance(s) must support all database features used by linked Source databases

SQL Server may raise an error during some dSource operations if the Staging SQL Server Instance does not support features used by the Source Database. 

This is most easily addressed by using the same edition of SQL Server as the Source database.

Features in use on the Source database can be checked using the sys.dm_db_persisted_sku_features dynamic view.

Must have both the Source and Staging Windows Users configured as local Windows users

See the sections Source Windows User Requirements and Staging Windows User Requirements for more detail.

Delphix Connector software is installed and running

See Installing the Delphix Connector Service on the Target Database Servers for instructions on installing the Delphix Connector.

Recommended iSCSI Registry settings must be in place

See Requirements for Windows iSCSI Configuration and Knowledge Base Article KBA1251 for instructions on applying these settings*.

*Please note that in 6.0.x, these performance settings are subject to change based on further tuning efforts by Delphix Engineering.

sqlcmd command line utility must be installed on the servers hosting VDBs and staging databases. The utility should be in the Delphix operating system user’s PATH environment variable.

Delphix does not run sqlcmd (or any other process) directly on the source SQL Server instance. It runs sqlcmd on the staging/connector host.

Staging Windows user requirements

The Delphix Engine needs a Windows domain user — for example, MYDOMAIN\delphix_os — to be specified when adding Staging environments to the Delphix Engine. This user must have the following permissions:

Staging User Requirement

Explanation

Be a member of the Windows "Local Administrators" group on the Staging Host

We require this permission for mounting iSCSI LUNs presented by the Delphix Engine to the staging and target hosts.  Microsoft utilities used by the Delphix Engine, such as diskpart, require membership of this group.

Have the "Log on as batch" privilege on the Staging host

We require this permission for remote PowerShell execution.  This privilege can be assigned through the Local Security Policy (Local Policies → User Rights Assignment → Log on as batch job).

Be able to access existing backups from the Source Database

If accessing existing native, Red Gate, or LiteSpeed backups, the following permissions are required: 

  • The Staging Windows User and the service account running SQL Server must both have permission to access the database backups via SMB (Windows file sharing) 

  • The Staging Windows User and the service account running SQL Server must both have NTFS Permissions to access the database backups 

 Separate documents describe requirements if Linking a dSource from a NetBackup SQL Server Backup or Linking a dSource from a Commvault SQL Server Backup.

Be able to login to each SQL Server instance that the Delphix Engine will communicate with

These requirements are described in the Staging Database Login Requirements section below.

Staging database login requirements

The Delphix Engine requires a Windows Authentication login to be created for the Staging Windows User on each SQL Server instance that the Delphix Engine will communicate with, with the following permissions:

Object

Privileges Required

Delphix OS User (Windows Login)

Purpose

Server

CONNECT SQL

Y

Access to the SQL Server instance

Server

sysadmin

Y

The staging and target databases are managed and administered completely by Delphix. Our functionality requires many administrative operations on those databases and requires full access to them. 

Since database ownership can be changed by customers as part of configuring virtual databases, we require the sysadmin role to continue to administer the databases.

SQL server target hosts and databases 

Target host requirements

Windows servers which will be added as Target Environments must meet the following requirements:

Target Host Requirement

Explanation

Delphix Connector software is installed and running

See Installing the Delphix Connector Service on the Target Database Servers for instructions on installing the Delphix Connector.

Recommended iSCSI Registry settings must be in place

See Requirements for Windows iSCSI Configuration and Knowledge Base Article KBA1251 for instructions on applying these settings.

The edition of the installed SQL Server instance(s) must support all database features used by linked Source databases

SQL Server may raise an error during some dSource operations if the Staging SQL Server Instance does not support features used by the Source Database.

This is most easily addressed by using the same edition of SQL Server as the Source database.

Features in use on the Source database can be checked using the sys.dm_db_persisted_sku_features dynamic view.

sqlcmd command line utility must be installed on the servers hosting VDBs and staging databases. The utility should be in the Delphix operating system user’s PATH environment variable.

Delphix does not run sqlcmd (or any other process) directly on the source SQL Server instance. It runs sqlcmd on the staging/connector host.

Target Windows user requirements

The Delphix Engine needs a Windows domain user — for example, MYDOMAIN\delphix_os — to be specified when adding Target environments to the Delphix Engine. This user must have the following permissions:

Target User Requirement

Explanation

Be a member of the Windows "Local Administrators" group on the Target Host

We require this permission for mounting iSCSI LUNs presented by the Delphix Engine to the staging and target hosts.  Microsoft utilities used by the Delphix Engine, such as diskpart, require membership of this group.

Have the "Log on as batch" privilege on the Target Host

We require this permission for remote PowerShell execution.  This privilege can be assigned through the Local Security Policy (Local Policies → User Rights Assignment → Log on as batch job).

Be able to login to each SQL Server instance that the Delphix Engine will communicate with

These requirements are described in the Target Database Login Requirements section below.

Delphix Connector software is installed and running

See Installing the Delphix Connector Service on the Target Database Servers for instructions on installing the Delphix Connector.

Target database login requirements

The Delphix Engine requires a Windows Authentication login to be created for the Target Windows User on each SQL Server instance that the Delphix Engine will communicate with, with the following permissions:

Object

Privileges Required

Delphix OS User (Windows Login)

Purpose

Server

CONNECT SQL

Y

Access to the SQL Server instance

Server

sysadmin

Y

The staging and target databases are managed and administered completely by Delphix. Our functionality requires many administrative operations on those databases and requires full access to them. 

Since database ownership can be changed by customers as part of configuring virtual databases, we require the sysadmin role to continue to administer the databases.

Supported Roles for Failover Cluster Instances and Always On Availability Groups

Failover Cluster Instances and Always On Availability groups cannot be used as Staging Environments, and VDBs cannot be provisioned into Availability Groups.

When adding a Failover Cluster Instance or Always On Availability Group, all nodes of the cluster must meet the requirements described in this document.

The following table shows how different SQL Server instance types should be added from the Add Environment screen:

Color

Supported?

Y

Yes

N

No

Environment Role

Instance Type

Added As

Source Environment

Staging Environment

Target Environment

Standalone Instance

Standalone

Y *

Y

Y

Failover Cluster Instance (FCI)

Standalone

Y *

N

N

Failover Cluster Instance (FCI)

Cluster

N

N

Y

Always On Availability Group (AG)

Cluster

Supported

N

N **


* Databases that are participating in Availability Groups will not be discovered during the discovery of a Standalone environment.

** VDBs cannot be provisioned into availability groups. However, SQL Server instances that participate in an Availability Group can also be added as Standalone Instances.

Using a Failover Cluster Instance as both Source and Target

A Failover Cluster Instance added as an environment once (as either a Source or Target environment) cannot be used as both a Source and Target.

If this is required, the environment can be added twice:

  • Once as a Standalone Source environment

  • Once as a Cluster Target environment

The Standalone Source environment can be used for linking dSources, and the Cluster Target environment is used for provisioning VDBs.

As suggested by the Best Practice note earlier in this article, this is not a recommended configuration. Where possible, SQL Server failover cluster instances that the Delphix Engine will use as a target should not be used to host databases other than Delphix VDBs.

Requirements for failover cluster target environments

The following additional requirements exist for Windows Failover Cluster Instances added as Target Environments, to be used for VDB Provisioning.

  • You must first add each node in the Window Failover Cluster individually as a standalone target environment, using a non-clustered address. See Adding a SQL Server Standalone Target Environment.

    • The Delphix Engine may show a warning that it cannot discover the Failover Cluster Instances on each standalone host. This is expected.

  • Each clustered SQL Server instance must have at least one clustered disk added to the clustered instance resource group, which can be used for creating mount points to Delphix storage.

    • The clustered drive must have a drive letter assigned to it.

    • The clustered drive must be formatted using the "GUID Partition Table (GPT)" partition style, in order for the Delphix Engine to automatically discover the drive letter as a valid option for the cluster instance. An MBR-formatted disk requires manual verification outside of Delphix that the disk has been correctly added to the MSSQL clustered resource group prior to creating the VDB. When provisioning the VDB, you must manually specify the desired MBR disk, because it will not appear in the Delphix GUI.

    • The clustered drive must be added to the clustered instance resource group as a dependency in the Failover Cluster Manager.

  • Each node in the cluster must have the Failover Cluster Module for Windows PowerShell feature installed.

    • While running Windows PowerShell as an administrator, enter this command to test that the module is available: Import-Module FailoverClusters

  • An additional target environment that can be used as a Connector Environment must exist. 

    • This environment must not be a node in the cluster.

    • This environment should be part of the same Active Directory domain as the cluster.

Additional requirements for azure SQL server availability groups

Microsoft's tutorial and deployment template for building a SQL Server AlwaysOn Cluster in Azure does not include the full range of network connectivity that is available in on-premise deployments.

In addition to the connectivity provided by this template:

  • The Staging Server must be able to connect to the Cluster IP Address using the RPC / Remote Registry port TCP 445; and

  • The Availability Group Listener must be configured with a TCP Port, such as the SQL Server default 1433

Connectivity to the Cluster IP Address can be tested using the following PowerShell command. This command should be run from the Staging Server, and specify the Cluster Hostname:

New-Object System.Net.Sockets.TcpClient("aodns-fc.mydomain.local", "445")

For Azure clusters, Delphix does not support DNN (Distributed Network Name) at the WSFC level. However, DNN (Distributed Network Name) is supported for creating a SQL AG listener in SQL clustering.

To allow this connectivity on the marketplace template, the following additional steps may be required:

  1. On a node of the SQL Server cluster, use PowerShell to define a "probe port". The active cluster node will open this port so that the Azure Load Balancer can detect it.
    Get-ClusterResource "Cluster IP Address" | Set-ClusterParameter -Name "ProbePort" -Value 59998

  2. From the Failover Cluster Manager, ensure that the Core Cluster Resource has a valid IP address for its subnet (this may be configured with an invalid IP address such as 169.254.1.1).

  3. On all nodes of the SQL Server cluster, ensure that the Windows Firewall allows inbound TCP traffic on TCP port 59998.

  4. Extend the Azure Load Balancer (sqlLoadBalancer) configuration to route connections to the Cluster IP Address.

    1. Add a Frontend IP Address for the Cluster IP Address, with an IP address matching that configured for the cluster.

    2. Add a Health Probe for TCP port 59998.

    3. Add a Load Balancing Rule for Port 445, using the newly created Frontend IP Address and Health Probe.More complicated Azure AlwaysOn deployments, such as those including multiple networks, may require additional steps to allow this connectivity.

  5. The default Availability Group Listener is not configured with a TCP Port. Current versions of Delphix require this to be configured with a valid port number, such as the default port 1433. This can be changed via the AlwaysOn object tree in SQL Server Management Studio:



JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.