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 databases cannot be in Read-Only mode at the time that a backup is taken. While the snapshot will succeed, attempts to provision VDBs from those snapshots may fail with an error during the provision process.
Temporarily setting the database to read-write mode, and taking a new backup and snapshot while the database is in in this state, will allow VDBs to be provisioned successfully.
Source Host Requirement | Explanation |
---|---|
To allow Delphix-initiated backups, the service account running each SQL Server instance (the Instance Owner) should be one of:
| 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 | 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 |
| Y | Y | Access to the SQL Server instance |
Database: |
| Y | Y | Access to information about attached databases |
Database: |
| Y | Access to the backup history | |
Each user database to be linked |
| Y | Delphix will periodically run queries to check the current size of the database | |
Each user database to be linked |
| 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 |
| Y | Y | Optional: Required for the discovery of databases in Availability Groups |
Server |
| Y | Y | Optional: Required for the SnapSync and discovery of Availability Groups |
Object: |
| Y | Optional: Required when using backups created by Red Gate SQL Backup | |
Object: |
| 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.
|
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:
| 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 |
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:
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 |
| Y | Access to the SQL Server instance |
Server |
| 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 |
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 |
| Y | Access to the SQL Server instance |
Server |
| 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
When considering the deployment of nodes within an AG cluster, please note that the Continuous Data Engine does not support the use of nodes as standalone targets — especially for VDB deployment purposes. This is particularly significant due to potential complications with integrity of engine metadata.
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.
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:
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 59998From 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: