Quick Start Guide for SQL Server (Microsoft SQL Server on Windows)
Datasets / SQL Server Environments and Data Sources / Quick Start Guide for SQL Server (Microsoft SQL Server on Windows)

This quick start guide, which is excerpted from the larger User Guide, is intended to provide you with a quick overview of working with SQL Server database objects in the Delphix Engine. It does not cover any advanced configuration options or best practices, which can have a significant impact on performance. It assumes that you are working in a Lab/Dev setting and attempting to quickly test Delphix functionality. It assumes you will use the VMware Hypervisor. It assumes you are running supported combinations of software as explained here: Supported OS, SQL Server, and Backup Software Versions for SQL Server.

Overview

In this guide, we will walk through deploying a Delphix Engine, starting with configuring Source, Staging (aka Validated Sync), and Target database environments on Windows servers. We will then create a dSource, and provision a VDB.

The following diagram describes the engine topology for SQL Server environments. It illustrates the recommended ports to be open from the engine to remote services, to the Delphix Engine, and to the Source, Target and Validated Sync Environments.

For purposes of the QuickStart, you can ignore any references to Replication or Masking, such as the engines shown in the diagram below.

Deploy OVA on VMware

Use the Delphix-supplied OVA file to install the Delphix Engine. The OVA file is configured with many of the minimum system requirements. The underlying storage for the install is assumed to be redundant SAN storage.

  1. Download the OVA file from https://download.delphix.com. You will need a support login from your sales team or a welcome letter.
    1. Navigate to the Delphix Product Releases/<Current Version>/Appliance Images page.
  2. Login using the vSphere client to the vSphere server (or vCenter Server) where you want to install the Delphix Engine.
  3. In the vSphere Client, click File.
  4. Select Deploy OVA Template.
  5. Browse to the OVA file.
  6. Click Next.
  7. Select a hostname for the Delphix Engine.
    This hostname will also be used in configuring the Delphix Engine network. 
  8. Select the data center where the Delphix Engine will be located.
  9. Select the cluster and the ESX host.
  10. Select one (1) data store for the Delphix OS.  This datastore can be thin-provisioned and must have enough free space to accommodate the 300GB comprising the Delphix operating system.
  11. Select four (4) or more data stores for Database Storage for the Delphix Engine.  The Delphix Engine will stripe all of the Database Storage across these VMDKs, so for optimal I/O performance, each VMDK must be equal in size and be configured Thick Provisioned - Lazy Zeroed.  Additionally, these VMDKs should be distributed as evenly as possible across all four SCSI I/O controllers.
  12. Select the virtual network you want to use.
    If using multiple physical NICs for link aggregation, you must use vSphere NIC teaming. Do not add multiple virtual NICs to the Delphix Engine itself. The Delphix Engine should use a single virtual network. For more information, see Optimal Network Architecture for the Delphix Engine.
  13. Click Finish.
    The installation will begin and the Delphix Engine will be created in the location you specified.
  14. Once the installation has completed, power on the Delphix Engine and proceed with the initial system configuration as described in Setting Up Network Access to the Delphix Engine.

If your source database is 4 TB, you probably need 4 TB of storage for the Delphix Engine. Add at least 4 data disks of similar size for the Delphix VM. For example: for a source database of 4 TB, create 4 VMDKs of 1 TB each.

For a full list of requirements and best practice recommendations, see  Virtual Machine Requirements for VMware Platform.

Setup Network Access to Delphix Engine

  1. Power on the Delphix Engine and open the Console.
  2. Wait for the Delphix Management Service and Delphix Boot Service to come online.  
    This might take up to 10 minutes during the first boot. Wait for the large orange box to turn green.
  3. Press any key to access the sysadmin console. 
  4. Enter  sysadmin@SYSTEM  for the username and sysadmin for the password.

  5. You will be presented with a description of available network settings and instructions for editing.
     

    Delphix Engine Network Setup

To access the system setup through the browser, the system must first be configured for networking in your
environment. From here, you can configure the primary interface, DNS, hostname, and default route. When DHCP is
configured, all other properties are derived from DHCP settings.

To see the current settings, run "get." To change a property, run "set <property>=<value>." To commit your changes,
run "commit." To exit this setup and return to the standard CLI, run "discard."

    defaultRoute    IP address of the gateway for the default route -- for example, "1.2.3.4."

    dhcp            Boolean value indicating whether DHCP should be used for the primary interface. Setting this value
                    to "true" will cause all other properties (address, hostname, and DNS) to be derived from the DHCP
                    response

    dnsDomain       DNS Domain -- for example, "delphix.com"

    dnsServers      DNS server(s) as a list of IP addresses -- for example, "1.2.3.4,5.6.7.8."

    hostname        Canonical system hostname, used in alert and other logs -- for example, "myserver"

    primaryAddress  Static address for the primary interface in CIDR notation -- for example, "1.2.3.4/22"

Current settings:

    defaultRoute: 192.168.1.1
    dhcp: false
    dnsDomain: example.com
    dnsServers: 192.168.1.1
    hostname: Delphix
    primaryAddress: 192.168.1.100/24

  6. Configure the hostname. If you are using DHCP, you can skip this step.

    delphix network setup update *> set hostname=<hostname>

    Use the same hostname you entered during the server installation.

  7. Configure DNS. If you are using DHCP, you can skip this step.

    delphix network setup update *> set dnsDomain=<domain>
delphix network setup update *> set dnsServers=<server1-ip>[,<server2-ip>,...]

  8. Configure either a static or DHCP address.

    DHCP Configuration

    delphix network setup update *> set dhcp=true

    Static Configuration

    delphix network setup update *> set dhcp=false
delphix network setup update *> set primaryAddress=<address>/<prefix-len>

    The static IP address must be specified in CIDR notation (for example, 192.168.1.2/24)

  9. Configure a default gateway. If you are using DHCP, you can skip this step.

    delphix network setup update *> set defaultRoute=<gateway-ip>

  10. Commit your changes. Note that you can use the get command prior to committing to verify your desired configuration.

    delphix network setup update *> commit
Successfully committed network settings. Further setup can be done through the browser at:

        http://<address>

Type "exit" to disconnect, or any other commands to continue using the CLI.
  11. Check that you can now access the Delphix Engine through a Web browser by navigating to the displayed IP address, or hostname if using DNS.

  12. Exit setup.

    delphix> exit

Setting Up the Delphix Engine

Once you setup the network access for Delphix Engine, navigate to the Delphix Engine URL in your browser for server setup. 

The welcome screen below will appear for you to begin your Delphix Engine setup.

The setup procedure uses a wizard process to take you through a set of configuration screens:
  • Administrators
  • Time
  • Network
  • Network Security
  • Storage
  • Serviceability
  • Authentication
  • Network Authorization
  • Registration
  • Summary
  1. Connect to the Delphix Engine at http://<Delphix Engine>/login/index.html#serverSetup.
    The Delphix Setup application will launch when you connect to the server.
    Enter your sysadmin login credentials, which initially defaults to the username sysadmin, with the initial default password of sysadmin.  On first login, you will be prompted to change the initial default password.
  2. Click Next.

Administrators

The Delphix Engine supports two types of administrators:

  • System Administrator (sysadmin) - this is the engine system administrator. The sysadmin password is defined here.

  • Engine Administrator (admin) - this is typically a DBA who will administer all the data managed by the engine.

On the Administrators tab, you set up the sysadmin password by entering an email address and password. The details for the admin are displayed for reference. 

The default domain user created on Delphix Engines from 5.3.1 is known as admin instead of delphix_admin. When engines created before 5.3.1 are upgraded to 5.3.1 or later they will retain their old username 'delphix_admin'. To avoid complications Delphix recommends creating users with an admin role and then Disabling delphix_admin.

System Time

The engine time is used as the baseline for setting policies that coordinate between virtual databases and external applications.

Choose your option to set up system time in this section. For a Quick Start, simply set the time and your timezone. You can change this later.

Network

The initial out-of-the-box network configuration in the OVA file is set to use a single VMXNET3 network adapter.

You have already configured this in the initial configuration. Delphix supports more advanced configurations, but you can enable those later.

Storage

You should see the data storage VMDKs or RDMs you created during the OVA installation. Click Next to configure these for data storage.

Serviceability

Choose your options to configure serviceability settings.

For a Quick Start, accept the defaults. You can change this later.

Authentication

Choose your options to configure authentication services.

For a Quick Start, accept the defaults. You can change this later.

Registration

If the Delphix Engine has access to the external Internet (either directly or through a web proxy), then you can auto-register the Delphix Engine:
  1. Enter your Support Username and Support Password.
  2. Click Register.

If external connectivity is not immediately available, you must perform manual registration.

  1. Copy the Delphix Engine registration code in one of two ways:
    1. Manually highlight the registration code and copy it to clipboard. Or,
    2. Click Copy Registration Code to Clipboard.
  2. Transfer the Delphix Engine's registration code to a workstation with access to the external network Internet. For example, you could e-mail the registration code to an externally accessible e-mail account.
  3. On a machine with access to the external Internet, please use your browser to navigate to the Delphix Registration Portal at http://register.delphix.com.
  4. Login with your Delphix support credentials (username and password).
  5. Paste the Registration Code.
  6. Click Register.

Although your Delphix Engine will work without registration, we strongly recommend that you register each Delphix Engine as part of the setup. Failing to register the Delphix Engine will impact its supportability and security in future versions.

To regenerate the registration code for a Delphix Engine please refer to,  Regenerating the Delphix Engine Registration Code. Delphix strongly recommends that you regenerate this code and re-register the engine regularly to maximize the Support Security of the Delphix Engine. Delphix recommends doing this every six months.

Summary

The final summary tab will enable you to review your configurations for System Time, Network, Storage, Serviceability, and Authentication. 

  1. Click the Back button to go back and to change the configuration for any of these server settings.
  2. If you are ready to proceed, then click Submit.
  3. Click Yes to confirm that you want to save the configuration.
  4. Click Setup to acknowledge the successful configuration.
  5. There will be a wait of several minutes as the Delphix Engine completes the configuration.

Source Environment Requirements

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.

  • 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.

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.

Windows Domain User Requirements

The source environment must have a Windows Domain user that the Delphix Engine can use – for example, delphix_src. This is the user that you provide when adding the source environment to the Delphix Engine. The user provides remote read-only access to the Windows Registry for discovering SQL Server instances and databases. This user must meet the following requirements:

  • Be a member of the Backup Operators or Administrators group on the source host to allow Windows remote registry access
  • If Delphix-initiated copy-only backups are used for the database, the user must be a member of the Administrators group on the source host
  • If the source host belongs to a cluster, the user must have these privileges on all hosts that comprise the cluster
  • Have access to any SQL Server instances the Delphix Engine needs to discover and link databases from

Database User Requirements

There must be a database user (Sql Server user or Domain user, for example, delphix_db or ad\delphix_db) for each source database that will link to the Delphix Engine. Note that this user will not perform any action that could affect production operations; the Delphix Engine uses this user only to issue queries for database names, database sizes, recovery mode and backup information. This user must meet the following requirements:
  • Be able to login to the SQL Server instance hosting the source database using SQL Server Authentication over a JDBC connection to the instance.
  • If a source environment user or domain user is used for linking, the user must have "log on as batch job" permission on the staging host, as for windows authentication, staging host is used for JDBC calls to source database.
  • For the master database in the source instance: have the database role db_datareader
    • To set this role, access the SQL Server Management Studio and select Security > Logins > delphix_db > User Mappings.
  • For the msdb database in the source instance:  have the database role db_datareader for reading backup history
    • To set this role, access the SQL Server Management Studio and select Security > Logins > delphix_db > User Mappings.
  • If the Delphix Engine will initiate copy-only full backups of the source database, the user must have the database role db_backupoperator for the source database
    • To set this role, access the SQL Server Management Studio and select Security > Logins > delphix_db > User Mappings
  • Be able to "use" the desired database. For example, the Delphix Engine will periodically connect to the source database user to determine its size using this query:  (SELECT SUM(size) FROM sys.database_files;). The results of the query is displayed in the Configuration tab of the dSource
  • If the source host belongs to an AlwaysOn Availability Group, the user must be granted access to the following views:
    • VIEW ANY DEFINITION

    • VIEW SERVER STATE

    • Exclusively give privilege to AG as "GRANT view Definition on AVAILABILITY GROUP::[aglname] TO [OS_user];" 

Target Environment Requirements

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 sys.dm_db_persisted_sku_features dynamic view.

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 RequirementExplanation
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 TypeAdded AsSource EnvironmentStaging EnvironmentTarget Environment
Standalone Instance
StandaloneSupported*SupportedSupported
Failover Cluster Instance (FCI)
StandaloneSupported*UnsupportedUnsupported
Failover Cluster Instance (FCI)
ClusterUnsupportedUnsupportedSupported
Always On Availability Group (AG)
ClusterSupportedUnsupportedUnsupported**


* 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:

  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:

  

Related Topics

Windows User Requirements

There must be a Windows user for the target host that the Delphix Engine can use – for example, delphix_trgt. This user can be a Windows domain user or a local user. However, using a local user account will prevent the target host from being used as a Staging Target. This user must meet these requirements:

  • Be a member of the local Administrators group for access to discovery operations on source hosts, and for mounting iSCSI LUNs that the Delphix Engine presents to the target host.
  • Have the server role sysadmin for each SQL Server instance with which the Delphix Engine will communicate.
    To set this role for the user: In SQL Server Management Studio, navigate to Security > Logins > delphix_trgt > Server Roles. 
  • Have Log on as a batch job rights so the Delphix Engine can remotely execute commands via Powershell
    To set this: Using the secpol.msc security policy editor, navigate to Local Policies > User Rights Assignment > Log on as a batch job.

Validated Sync Environment Requirement

This topic describes additional requirements for SQL Server environments that will be used as targets for validated sync. You must configure a staging (Validated Sync) environment as a target, with a few additional requirements.

Requirements for SQL Server Validated Sync Target Environments

Each SQL Server target environment used for validated sync must meet these requirements:

  • Only standalone target environments can be used as validated sync target environments. Windows Failover Cluster target environments and SQL Server Failover Cluster instances cannot be used.
  • The SQL Server instance must be the same version as the instance hosting the source database. For more information about compatibility between different versions of SQL Server, see SQL Server Support Matrix
  • The owner of the SQL Server instances on the target environment that are used for the staging databases must have SMB read access to the location containing the backup images of the source databases
  • If the source database is backed up with third-party backup software like LiteSpeed or Red Gate SQL Backup Pro, you must install the backup software on both the source and the validated sync environment. For backup software compatibility requirements, see  SQL Server Support Matrix

Add the Validated Sync Environment

The order is important. Add the validated sync environment as the first step in setting up the SQL Server topology.

  1. From the machine that you want to use as a target, start a browser session and connect to the Delphix Management application.
  2. Click Manage.
  3. Select Environments.
  4. Next to Environments, click the Actions menu and select Add Environment.
  5. In the Add Environment wizard Host and Server tab, select:
    1. Host OS: Windows
    2. Host Type: Target.
    3. Server Type: Standalone.
  6. Click Next.
  7. In the Environment Settings tab click the download link for the Delphix Connector Installer.
    The Delphix Connector will download to your local machine.

  8. On the Windows machine that you want to want to use as a target, run the Delphix Connector installer. Click Next to advance through each of the installation wizard screens.

    The installer will only run on 64-bit Windows systems. 32-bit systems are not supported.

    1. For Connector Configuration, make sure there is no firewall in your environment blocking traffic to the port on the target environment that the Delphix Connector service will listen to.
    2. For Select Installation Folder, either accept the default folder, or click Browse to select another.
    3. Click Next on the installer final 'Confirm Installation' dialog to complete the installation process and then Close to exit the Delphix Connector Install Program.
  9. Return to the Delphix Management application.
  10. Enter the Environment Name, Host Address, Delphix Connector Port, OS Username, and OS Password for the target environment.
  11. To provide your own Oracle Java select the Provide my own JDK checkbox and click Next.
  12. In the Java Development Kit tab enter the absolute path to your Oracle JDK and click Next. 
  13. Click Submit.

As the new environment is added, you will see two jobs running in the Delphix Admin Job History, one to Create and Discover an environment, and another to Create an environment. When the jobs are complete, you will see the new environment added to the list in the Environments panel.

Add the Target Environment

Repeat the procedure for your target environment.

  1. From the machine that you want to use as a target, start a browser session and connect to the Delphix Management application.
  2. Click Manage.
  3. Select Environments.
  4. Next to Environments, click the Actions menu and select Add Environment.
  5. In the Add Environment wizard Host and Server tab, select:
    1. Host OS: Windows
    2. Host Type: Target.
    3. Server Type: Standalone.
  6. Click Next.
  7. In the Environment Settings tab click the download link for the Delphix Connector Installer.
    The Delphix Connector will download to your local machine.

  8. On the Windows machine that you want to want to use as a target, run the Delphix Connector installer. Click Next to advance through each of the installation wizard screens.

    The installer will only run on 64-bit Windows systems. 32-bit systems are not supported.

    1. For Connector Configuration, make sure there is no firewall in your environment blocking traffic to the port on the target environment that the Delphix Connector service will listen to.
    2. For Select Installation Folder, either accept the default folder, or click Browse to select another.
    3. Click Next on the installer final 'Confirm Installation' dialog to complete the installation process and then Close to exit the Delphix Connector Install Program.
  9. Return to the Delphix Management application.
  10. Enter the Environment Name, Host Address, Delphix Connector Port, OS Username, and OS Password for the target environment.
  11. To provide your own Oracle Java select the Provide my own JDK checkbox and click Next.
  12. In the Java Development Kit tab enter the absolute path to your Oracle JDK and click Next. 
  13. Click Submit.

As the new environment is added, you will see two jobs running in the Delphix Admin Job History, one to Create and Discover an environment, and another to Create an environment. When the jobs are complete, you will see the new environment added to the list in the Environments panel.

Add the Source Environment

Delphix does not require running the Connector on your source. Instead, you'll use the Validated Sync environment as a connector environment to discover the source by proxy.

  1. Login to the Delphix Management application.
  2. Click Manage.
  3. Select Environments.
  4. Next to Environments, click the Actions menu and select Add Environment.
  5. In the Add Environment wizard, Host and Server tab select:
    1. Host OS: Windows
    2. Host Type: Source
    3. Server Type:
      • If you are adding a Windows Server Failover Cluster (WSFC), add the environment based on which WSFC feature the source databases use:
        • Failover Cluster Instances
          Add the environment as a standalone source using the cluster name or address.
        • AlwaysOn Availability Groups
          Add the environment as a cluster source using the cluster name or address.
      • Otherwise, add the environment as a standalone source.
  6. Click Next.
  7. In the Environment Settings tab select a Connector Environment.
    Connector environments are used as proxy for running discovery on the source. If no connector environments are available for selection, you will need to set them up as described in Adding a SQL Server Standalone Target Environment. Connector environments must:
    • have the Delphix Connector installed
    • be registered with the Delphix Engine from the host machine where they are located.
  8. Enter the Environment Name, Node Address, OS Username, and OS Password for the source environment.
  9. Click Submit.

As the new environment is added, you will see multiple jobs running in the Delphix Admin Job History to Create and Discover an environment. In addition, if you are adding a cluster environment, you will see jobs to Create and Discover each node in the cluster and their corresponding hosts. When the jobs are complete, you will see the new environment added to the list in the Environments panel. If you don't see it, click the Actions menu and select Refresh All.

Linking a SQL Server Data Source (dSource)

Linking a dSource will ingest data from the source and create a dSource object on the engine. The dSource is an object that the Delphix Virtualization Engine uses to create and update virtual copies of your database. As a virtualized representation of your source data, it cannot be managed, manipulated, or examined by database tools. 

For an overview of all dSource related actions, please Managing Data Sources and Syncing Data.

When linking a dSource from a SQL Server source database, Delphix offers several different methods of capturing backup information:

  • SQL Server Managed Backups, where the SQL Server source database schedules and initiates backups and the Delphix Engine captures them
    • Full backups
    • Full or differential backups
    • Transaction log backups (with LogSync disabled)
    • Transaction log backups (with LogSync enabled)
  • Delphix Managed Backups, where the Delphix Engine schedules and initiates the backups from the source database, and captures them

Procedure

  1. Login to the Delphix Management application.
  2. Navigate to Manage > Datasets.
  3. Click the plus icon and select Add dSource.
  4. In the Add dSource wizard, select the source database with the correct environment user-specified.
  5. Select user type for source database authentication and enter the login credentials. Enter username and password for Database user or Domain (Windows) user. For Environment User, select a source environment user from the dropdown list and click Next.
  6. Enter a name and select a group for your dSource.
    Adding a dSource to a dataset group lets you set Delphix Domain user permissions for that database and its objects, such as snapshots. See the topics under Users and Groups for more information.
  7. Select the Data Management settings needed. For more information, Data Management Settings for SQL Server Data Sources.
  8. Select the Staging environment and SQL Instance that will be used to manage the staging database used for validated sync of the dSource.
  9. Select any policies for the new dSource.
  10. Enter any scripts that should be run on the Hooks page.
  11. Review the dSource Configuration and Data Management information, and then click Submit.

Provisioning a SQL Server Virtual Database (VDB)

  1. Login to the Delphix Management application.
  2. Click Manage.
  3. Select Datasets.
  4. Select a dSource.
  5. Click Timeflow tab.
  6. Next to a snapshot select theProvision VDB icon.
    The Provision VDB panel will open, and the Database Name and Recovery Model will auto-populate with information from the dSource.
  7. Select a target environment.
  8. Select an Instance to use.

  9. If the selected target environment is a Windows Failover Cluster environment, select a drive letter from Available Drives. This drive will contain volume mount points to Delphix storage.

    Windows Cluster Volume Management Software Requirements

    Only cluster volumes managed by the native Windows Volume Manager are supported. For example, cluster volumes managed by Veritas VxVM are not supported.

    If you use third-party volume management software, create a new LU (recommended to be 10GB in size) and add this LU as a clustered resource to the SQL Server instance using native Windows volume management tools. Assign a drive letter for this LU. You can then use this LU as the volume mount point location for Delphix VDB provisioning.

  10. Enter a VDB Name and select a Target Group for the VDB.
  11. Enable Auto VDB Restart to allow the Delphix Engine to automatically restart the VDB when it detects target host reboot.
  12. Click Next.
  13. Select a Snapshot Policy for the VDB. Click Next.
  14. Specify any Pre- or Post-Scripts that should be used during the provisioning process.
  15. Click Next.
  16. The final summary tab will enable you to review your configurations.
  17. Click Submit.

When provisioning starts, the VDB will appear in the Datasets panel. Select the VDB and navigate to the Status tab to see the progress of the job. When provisioning is complete, you can see more information on the Configuration tab.

You can select a SQL Server instance that has a higher version than the source database and the VDB will be automatically upgraded. For more information about compatibility between different versions of SQL Server, see SQL Server Support Matrix.

Provisioning by Snapshot or LogSync

When provisioning by snapshot, you can provision to the start of any particular snapshot, either by time or LSN.

You can take a new snapshot of the dSource and provision from it by clicking the Camera icon. 

Provisioning By SnapshotDescription
Provision by Time

You can provision to the start of any snapshot by selecting that snapshot card from the TimeFlow tab, or by selecting and entering a value in the time entry fields. The values you enter will snap to the beginning of the nearest snapshot.

Provision by LSNYou can use Provision by LSN control to open the LSN entry field. Here, you can type or paste in the LSN to which you want to provision. After entering a value, it will "snap" to the start of the closest appropriate snapshot. Provisioning a SQL Server VDB Procedure

Next Steps

Congratulations! You have provisioned your first virtual database!

Now, perform some simple functional tests with your application. You can connect your app to the VDB using standard TNS/JDBC techniques. Delphix has already registered the VDB for you on the target listener.

We suggest the following next steps:

  1. Drop a table and use the VDB Rewind feature to test the recovery of your VDB.
  2. Take a snapshot of your dSource and refresh your VDB to quickly get fresh production data.
  3. Provision a new VDB from your VDB to test sharing data quickly with other teams.
  4. Mask your new VDB to protect sensitive data. Provision new VDBs from that masked VDB to quickly provide safe data to development and QA teams.

Related Topics