This document identifies our permission 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
This diagram depicts the environments and hosts with which we have permissions dependencies. The Delphix architecture with SQL Server environments requires permissions on both the host and database for all Source, Staging and Target environments. This document is organized such that there are separate sections for the host and database for Source, Staging, and Target environment permission requirements.
SQL Server Source Hosts and Databases
Source Host Requirements
The Delphix Engine needs a Windows domain user —entered when adding source environments to the Delphix Engine. The Windows domain user provides remote read-only access to the Windows Registry, which allows the discovery of SQL Server instances and databases. This user must have the following permissions:
Domain User Requirement | Explanation |
Be a member of the Backup Operators on the source host. | Delphix needs Windows remote registry access to check for instances that are running on the source host. This requirement is used for performing discovery and gathering system details. |
Be able to login to each SQL Server instance that the Delphix Engine will communicate with. | Delphix uses the Windows login to perform SQL Server instance and database discovery. |
Have db_datareader role for the master database on each SQL Server instance with which the Delphix Engine will communicate. | Detailed in the section for Master Database Permissions Requirement below. |
If the source cluster environment has AlwaysOn Availability Groups, domain user must have access to certain views. | Required for the SnapSync and discovery of Availability Groups configuration. The domain user must have access to: VIEW ANY DEFINITION and VIEW SERVER STATE. |
The source host and target environments must have appropriate cross-domain trust relationships. For more information see Delphix in Multi-domain Windows Environments. | The source host must belong to the same Windows domain as the target environments or the domain that the source environment uses must have appropriate cross-domain trust relationships established with the target environments' domains. |
Source Database Requirements
Delphix needs a SQL Server database user or domain user to query source database —provided when linking the dSource to the Delphix Engine. We have the following requirements for SQL Server instances:
SQL User Requirement | Explanation |
Login to source database. | Delphix needs to connect through Java database connectivity (JDBC) to the database. This user will not perform any action that could affect production operations, only issuing read-only queries for backup information, database size, recovery mode, etc. |
If Delphix takes copy-only backups, the SQL Server instances must run as either domain users or local service accounts. | The SQL Server instance owner must have access to the Server Message Block (SMB). |
User must have db_backupoperator role. | Required if the Delphix Engine will use Delphix Managed Backups or initiate copy-only full backups of the database |
If the source cluster environment has AlwaysOn Availability Groups, the user must have access to certain views. | Required to discover Availability Groups configuration. The user must have access to: VIEW ANY DEFINITION and VIEW SERVER STATE. |
User must be a member of the ‘public’ group. | We must be able to "use" the desired database. Delphix will periodically run a query to find the size of the database. |
User must be a db_datareader for the master and msdb system databases. | These requirements are outlined in the following two sections for Master Database Permissions Requirement and MSDB Database Permissions Requirement. |
Master Database Permissions Requirement: db_datareader
Delphix requires the db_datareader permission for access to the following system tables in master database instances on the source host:
System table | Justification |
sys.databases | Used to determine the name and recovery model of databases within discover SQL Server instances |
sys.availability_groups | Used for discovering all the availability groups within an Availability Group source environment. |
sys.availability_group_listeners | Used for discovering all the availability groups within an Availability Group source environment.
|
sys.availability_databases_cluster | Used for discovering all the availability groups within an Availability Group source environment. |
sys.availability_replicas | Used for discovering all the availability groups within an Availability Group source environment. |
sys.database_files | Used to determine the size of databases and whether filestream is enabled for a database |
sys.dm_exec_requests | Used to enable Delphix to report backup operation progress |
sys.master_files | Used to determine the primary file of a database |
sys.filegroups | Used to determine the filegroups of a database so that Delphix can configure VDBs with the same filegroups |
MSDB Database Permissions Requirement: db_datareader
Delphix requires the db_datareader permission for access to the following system tables in msdb database instances on the source host:
System table | Justification |
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/Target Hosts and Databases
Staging/Target Host Requirements
Source Database Host Requirement | Explanation |
Must have the ‘Log on as batch’ permission for Source database Domain/Environment User | If an environment user or domain user is used for database authentication during linking, the user requires this permission on the staging host for JDBC calls to source database. This permission is not needed if database user is used for linking. |
| Port 8415 must be open between Delphix Engine and Staging host if Domain/Environment User is used for source database authentication | If Domain/Environment User is used for source database queries, staging host is used to query the source database using windows authentication and it returns the query results to Delphix Engine via DSP calls. Thus, port 8415 must be open between Delphix Engine and Staging host for DSP calls. This port is not needed if database user is used for linking. |
| 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 |
Staging User Requirement | Explanation |
Must be a member of the local administrator's group or be a domain user authorized on the machine | We require this permission for mounting iSCSI LUNs presented by the Delphix Engine to the staging and target hosts. “Membership in the local Administrators group, or equivalent, is the minimum required to run DiskPart” |
Must have the ‘Log on as batch’ permission | We require this permission for remote PowerShell execution. |
Staging and Target Database Requirements
Staging and Target Database Requirement | Explanation |
Must have the server role sysadmin for each SQL Server instance that the Delphix Engine will communicate with. | 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 must retain the sysadmin role to continue to administer the databases. |
Staging Database Requirements
| Staging Database Requirement | Explanation |
|---|---|
| If Delphix takes copy-only backups, the SQL Server instances must run as either domain users or local service accounts |
Requirements for Windows Cluster Environments
The following are requirements for discovering Windows Failover Clusters as environments.
- You must add each node in the Window Failover Cluster individually as a standalone target environment using a non-cluster address. See Adding a SQL Server Standalone Target Environment.
- For a cluster node added as a standalone environment, the Delphix Engine will only discover non-clustered SQL Server instances.
- For a cluster target environment, the Delphix Engine will only discover SQL Server failover cluster instances.
- 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 load the module: import-module failover clusters
- 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.
- Windows PowerShell 2.0 must be installed. To check the current version, type $PSVersionTable.PSVersion in a PowerShell Window.
- Execution of Windows PowerShell scripts must be enabled on the target host.
To enable script execution, enter this command while running Windows PowerShell as an Administrator:Set-ExecutionPolicy Unrestricted.
Supported Roles for Each Instance Type
Failover Cluster Instances, and instances supporting Always On Availability Groups, support a subset of the operations available to Standalone SQL Server instances.
| Environment Role | ||||
|---|---|---|---|---|
| Instance Type | Added As | Source Environment | Staging Environment | Target Environment |
| Standalone Instance | Standalone | Supported* | Supported | Supported |
| Failover Cluster Instance (FCI) | Standalone | Supported* | Unsupported | Unsupported |
| Failover Cluster Instance (FCI) | Cluster | Unsupported | Unsupported | Supported |
| Always On Availability Group (AG) | Cluster | Supported | Unsupported | Unsupported** |
* 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.
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:
- 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 - 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).
- On all nodes of the SQL Server cluster, ensure that the Windows Firewall allows inbound TCP traffic on TCP port 59998.
- Extend the Azure Load Balancer (sqlLoadBalancer) configuration to route connections to the Cluster IP Address.
- Add a Frontend IP Address for the Cluster IP Address, with an IP address matching that configured for the cluster.
- Add a Health Probe for TCP port 59998.
- 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.
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:
Related Topics