Delphix Admin for MS SQL Lab Guide

Table of Contents

• Prerequisite - Join the Delphix Domain
• Optional Advanced Exercise - Perform a Storage Performance Test through the CLI
• Exercise 1 - Delphix Engine Configuration
• Exercise 2 - Configure the DB User on the Source
• Exercise 3 - Take a Backup of the Source Database
• Exercise 4 - Configure the Windows Target/Staging Server
• Exercise 5 - Create a Target Environment
• Exercise 6 - Add the Source Environment
• Exercise 7 - Add the dSource
• Exercise 8 - Provision a VDB
• Exercise 9 - Refresh a VDB
• Exercise10 - Rewind a VDB
• Exercise 11 - Set a New Retention Policy
• Exercise 12 - Create and Save a Hook Operation Template
• Exercise 13 - Provision a VDB with Hook Template
• Exercise 14 - Recovering a deleted table using log sync
• Exercise 15 - Delink a dSource
• Exercise 16 - Create a new User with Delphix_Admin privileges
• Exercise 17- Check the Audit logs for user related actions

Hands-On Lab Guides

• Getting Started
• Oracle
• Self-Service

Lab Exercises

Perform these exercises when instructed by your Delphix Instructor. It is suggested that you use the latest version of the Windows MS SQL lab. This lab guide is designed for version 6.0.6 and on.

Optional Advanced Exercise - Perform a Storage Performance Test through the CLI

In this exercise, you will:

• Log into the Delphix Engine prior to configuration via the Delphix Command Line Interface (CLI)
• Perform a Storage Test
• View the Storage Test results

Steps

1. On your Lab Server desktop, double-click on Terminal
2. Type: ssh sysadmin@10.0.x.10 (‘x’ is your Student Number assigned by your instructor)
   • If you receive a prompt asking you if you are sure you want to connect, enter: Yes
   • Enter the password: sysadmin
   • Escape to the standard CLI prompt by typing: discard
   • You are now at the root of the Delphix CLI as a System Administrator
3. Create a storage test by typing: storage test create
   • List the default storage test parameters by typing: get
   • Override the duration and set it to 5 minutes: set duration=3
   • Check all values by typing ls at the command prompt
   • Begin the storage test by typing: commit
   • Note that this test will take anywhere between 7-10 minutes to complete
   • Note: If you receive an error, it could be because the Delphix Engine is already configured. In this case:
   • Connect to your Delphix Engine using Chrome on your lab server (see the Important IP Addresses section of the Getting Started guide above).
   • Log into the setup console as the sysadmin user
   • Click on the 3 dots in the upper right hand corner and select Factory Reset - this may take several minutes to complete.

Example Storage Test Configuration

1. View the storage test results
   • Get back to the storage test section of the CLI by typing: storage test
   • View a list of completed tests by typing: ls
   • Type “select” followed by the name of the test from the list. For example: select STORAGE_TEST-1
   • Enter the result command by typing: result
   • Then type: commit
   • Scroll up to view the Test Grades section

Example Storage Test Results

Prerequisite - Join the Delphix Domain

From your Student Desktop home screen, double click the RDP icon. This will open Remmina Remote Desktop client. Before we can continue these labs, you must connect to your Windows Source and Target and join them to the delphix.local domain.

Please note, it may take some time for the Windows servers to come online. If the Connecting dialog stays open for more than 5-10 seconds, cancel and try again in a few minutes.

Steps

1. Double click on Windows Source
2. When prompted, enter the password: delphix
3. Once you are logged in, click on Start and then Run. (Note: you might need to expand the screen to see Start on the Windows server.)
4. Type: sysdm.cpl
5. Press Enter
6. Click the Change button
7. Under the Member of section, click the radio button next to Domain
8. Enter the domain: delphix.local
9. Click OK
10. When prompted, enter the login details:
    • delphix_src
    • delphix
11. Wait for the Welcome message and click OK, then click OK again to acknowledge the need to restart
12. Click Close
13. Click Restart Now

Once you have completed these steps, perform the following steps to set up the Windows Target :

1. Go back to your Student Desktop click on Windows Target in your RDP application
2. When prompted, enter the password: delphix
3. Once you are logged in, click on Start and then Run
4. Type: sysdm.cpl
5. Press Enter
6. Click the Change button
7. Under the Member of section, click the radio button next to Domain
8. Enter the domain: delphix.local
9. Click OK
10. When prompted, enter the login details:
    • delphix_trgt
    • delphix
11. Wait for the Welcome message and click OK, then click OK again to acknowledge the need to restart
12. Click Close
13. Click Restart Now

See The RDP Client under Getting Started for more information on using the RDP application.

Exercise 1 - Delphix Engine Configuration

In this exercise, you will:

• Access the Delphix Engine GUI for the first time
• Set up the Delphix SYSADMIN user
• Configure Timezone Preferences
• Configure Network Settings
• Configure Disks
• Complete the Delphix Engine configuration
• Set up the DELPHIX_ADMIN user

Steps

1. Connect to your Delphix Engine using Chrome on your lab server (see the Important IP Addresses section of the Getting Started guide above).
2. Set the new sysadmin password to: delphix
3. Configure the Delphix Engine with the following details:
   • NTP on using pool.ntp.org with your local timezone
   • Default network settings
   • Three 8GB volumes in the data pool
   • Default Serviceability options
   • Default Authentication Service options
   • Appliance marked registered (no support credentials are required for this lab)
   • Completed and saved System setup.
4. Log in with the admin user (password = ‘delphix’)
5. When prompted, set the new admin password to: delphix

You will know this is successful when you see the main Delphix UI screen with a single group (Untitled) on the left hand side.

Related Links The delphix_admin and sysadmin User Roles Setting Up the Delphix Engine

Exercise 2 - Configure the DB User on the Source

In this exercise, you will:

• Connect to the Windows Source
• Configure domain user privileges
• Configure the local Delphix user

Steps

1. Double click “RDP” icon on the Student Desktop
2. Double click “Windows Source” icon
3. Login as the delphix_src user (login details are in the Getting Started section of this guide)
4. On the Windows Source machine, go to Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio

Note: You may need to scroll down on the right-hand side of the RDP window to see the Windows Start button or expand the screen.

• Under Authentication, select “SQL Server Authentication”
• Log in as sa/delphix

1. Configure the domain user privileges
   • Expand the Security folder, then Logins
   • Double Click on DELPHIX\delphix_src
   • Click on “User Mapping”
   • Check the box next to “master”
   • Select “db_datareader” for the database role membership
   • Click OK

1. Right click Logins
2. Select New Login
3. Fill in the following details on the General Screen:
   • Login Name: delphix_db
   • SQL Server Authentication
   • Password: delphix
   • Unclick “Enforce password expiration”
   • Unclick “User must change password at next login”
   • On the left, click “User Mapping”
   • Check master, then select “db_datareader” from the role list

• Check msdb, then select “db_datareader” from the role list

• Check AdventureWorks2008R2, then select “db_backupoperator” from the role list to allow copy only full backups

• Click OK

Exercise 3 - Take a Backup of the Source Database

In this exercise, you will:

• Take a backup of the AdventureWorks2008R2 database
• Verify the backup location share

Steps

1. On the Windows Source machine, go to Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio
   • Under Authentication, select “SQL Server Authentication”
   • Log in as sa/delphix
2. Expand “Databases”
3. Right click AdventureWorks2008R2, then select Tasks -> Back Up…
4. Note the backup destination in C:\Backups and click OK

1. Click OK once the backup is complete
2. Open Windows Explorer and navigate to C:\
3. Right click “Backups” and select Properties
4. Select the Sharing tab
5. Click the “Share” button
6. Ensure the Delphix Source user is the Owner and Delphix Target user has read/write privileges

1. Click Cancel

Exercise 4 - Configure the Windows Target/Staging Server

In this exercise, you will:

• Download the Delphix Connector
• Install the Delphix Connector
• Enable Powershell scripts on the target

Before we start this exercise is important to mention that in Delphix, a Target and Staging server have the same configuration. It’s a Delphix best practice to isolate the Staging workload from the VDB workload, so that’s why it has a different name, but from a requirements point of view, they are the same.

Steps

1. Double click “RDP” icon on the Student Desktop
2. Double click “Windows Target” to connect to the Windows Target server
3. Login as the delphix_trgt user (login details are in the Getting Started section of this guide)
4. Open Google Chrome from your Windows Target desktop
5. Navigate to your Delphix Engine at 10.0.x.10 (‘x’ is your Student Number )
   • Log in as the delphix_admin user
   • Go to Manage -> Environments
   • Click the ellipses (…) and select Add Environment

On the Host and Server tab choose Windows as the Host OS and Target as the Host Type then click Next

Click the “ Download Delphix Connector Installer “

Once it finishes downloading, click Keep

1. Open Windows Explorer and go to Downloads
2. Double click on DelphixConnectorInstaller
   • Click “Run”
   • Click “Next”
   • Agree to the terms and conditions and click Next
   • Accept the default Connector Port of 9100 by clicking Next
   • Accept the default location of C:\Program Files\Delphix\DelphixConnector\ by pressing Next. (Note: you do not need to fill this in, it will auto-populate on the next screen after you press next)
   • Click Next to install
   • Click Yes to allow the installation when the User Account Control (UAC) dialog pops up
   • Once it finishes, click Close
3. Go to Start -> All Programs -> Accessories -> Windows PowerShell
4. Right click on Windows PowerShell and select Run as Administrator

1. Click Yes to run with elevated privileges
2. Type: Set-ExecutionPolicy Unrestricted at the Powershell prompt
3. Press “Y” and enter

Exercise 5 - Create a Target Environment

In this exercise, you will:

• Connect Delphix to your Windows Target server

The target environment is the host where the virtual databases will be created.

Steps

1. Navigate to your Delphix Engine at 10.0.x.10 (‘x’ is your Student Number )
   • Log in as the delphix_admin user
   • Go to Manage -> Environments
   • Click the ellipses (…) and select Add Environment

1. On the Host and Server tab choose Windows as the Host OS and Target as the Host Type then click Next

1. Under the Environment Settings tab, enter the details:
   • Environment Name: WINTARGET
   • Host Address: 10.0.x.60 (x is your Student Number )
   • Delphix Connector port: 9100
   • OS Username: delphix\delphix_trgt
   • OS Password: delphix
   • Click Validate Credentials
   • Click Submit The creation is successful when “Create and discover environment 10.0.x.60” completes without error and WINTARGET appears on your Delphix Environments page

Exercise 6 - Add the Source Environment

In this exercise, you will:

• Connect Delphix to your Windows Source Server

Before users can create their own virtual databases, Delphix needs to connect to source data. In Delphix, an environment is a host that runs database software. The environment is where the Delphix engine will search for available data sources. Credentials to access the host need to be provided while configuring an environment.

Steps

1. Navigate to your Delphix Engine at 10.0.x.10 (‘x’ is your Student Number )
2. Log in as the delphix_admin user
3. Go to Manage -> Environments
4. Click the ellipses (…) and select Add Environment
5. On the Host and Server tab choose Windows as the Host OS, Source as the Host Type and Standalone as the Server Type then click Next
6. Enter the details:
   • Environment Name: WINSOURCE
   • Host Address: 10.0.x.50 (‘x’ is your Student Number )
   • Connector Environment: Click on WINTARGET
   • OS Username: delphix\delphix_src
   • OS Password: delphix
7. Click Validate Credentials
8. Click Submit

The creation is successful when “Create and discover environment 10.0.x.50” completes without error and WINSOURCE appears on your Delphix Environments page

Exercise 7 - Add the dSource

In this exercise, you will:

• Add the AdventureWorks2008R2 dSource into Delphix and bring in the initial backup

With an environment set up, users can then sync databases into Delphix. The Delphix engine will read the source database and create a dSource (a custom object). The dSource is not a functional image of the database but a storage-efficient object from which virtual databases can be created. When creating a dSource, Delphix will pull over the complete data set using standard database protocols. Subsequent sync operations, as governed by user-defined policies, will pull only incremental changes.

Steps

1. Navigate to Manage -> Datasets

2. Click the Plus  and choose Add dSource from the drop-down

1. On the Add dSource wizard, review the information and pre-requisites on the Preparation tab then click Next

2. On the Source tab click the AdventureWorks2008R2 Data Source to select it and enter the information below

3. Environment User: delphix\delphix_src

4. Select the Database User radio button

5. Database Username: delphix_db

6. Database Password: delphix

7. Click Validate

1. Click Next

2. Click the “Add Dataset Group” link

3. Enter the Group Name: MS SQL Databases

1. Click OK and select the new group in the list

2. Click Next

3. Select “Use the most recent full or differential backup”

4. For Backup Path, enter: \\10.0.x.50\Backups (‘x’ is your Student Number )

5. For Staging Environment, select WINTARGET

6. For Validated Sync Mode, select Transaction log backups and click Enabled next to LogSync

1. Click Next

2. Click Next

3. Verify your settings and click Submit

1. Go to the main Delphix page by clicking Delphix in the top left corner of the GUI

2. Monitor the AdventureWorks dSource addition via the progress bar and the Actions pane

Exercise 8 - Provision a VDB

In this exercise, you will:

• Create a VDB called devdb

• Log into the VDB

The objective of Delphix virtualization is to provide easy access to virtualized databases (VDBs) that resemble production and other data systems. VDBs are fully functional database images that can be created from dSources.

Steps

1. Click the AdventureWorks2008R2 dSource in the MS SQL Databases group

2. Select the most recent snapshot and click Provision

1. Select WINTARGET on the left pane of the wizard and click Next.

1. Enter the Database Name: devdb

2. Click Next

1. Select the dataset group “MS SQL Databases group”

2. Click Next

3. Keep the defaults values for retention and click Next

4. Keep the defaults for hooks and click Next

5. Verify settings and click Submit

It may take a couple minutes for the VDB creation to complete. You can monitor the progress on the left hand side of the screen next to the devdb object in the MS SQL Databases group. On the Actions pane on the right hand side of the screen, you should see the Provision virtual database “devdb” item move to the Recently completed pane without error. Once the VDB is created, you can verify that the VDB is operational by:

1. Log into the Target via RDP

2. Click Start and go to SQL Server Management Studio

3. Click Connect to log in as delphix\delphix_trgt

4. Expand Databases

5. Note the “devdb” virtual database

6. Note the validated sync database (named with a GUID)

Exercise 9 - Refresh a VDB

In this exercise, you will:

• Create a new table on your source database

• Snapshot the dSource

• Refresh your VDB - devdb

• Verify the new table appears on the VDB

VDBs can get out of sync as new data comes into the source system. Refreshing a VDB will re-provision it from the dSource. Refreshing a VDB will delete any changes that have been made to it over time.

Steps

1. Log into the Source via RDP

2. Go to Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio

3. Under Authentication, select “SQL Server Authentication”

4. Log in as sa/delphix

5. Expand “Databases”

6. Right click AdventureWorks2008R2, then select New Query

7. Run the following command (by clicking Execute):

select * into sourcetab from AdventureWorks2008R2.HumanResources.vEmployee;

1. Take a transaction log backup, by right-clicking the AdventureWorks2008R2 folder, selecting Tasks and Back Up, and then choose Backup Type = “Transaction Log.”

1. Go back to the Delphix Engine GUI

2. Within a couple of minutes a new timecard will appear for the dSource

1. Select the devdb VDB and click the Refresh button

1. Refresh the devdb VDB using the latest snapshot from the AdventureWorks2008R2 dSource

1. Click Submit button

Once the refresh has completed, you can log into devdb to confirm.

1. Log into the Target via RDP

2. Go to Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio

1. Under Authentication, select "SQL Server Authentication"

2. Log in as sa/delphix

1. Expand “Databases”

2. Right click devdb, then select New Query

3. Run the following command:

select count(*) from sourcetab;

If this returns a count of rows, the snapshot/refresh was successful.

Exercise10 - Rewind a VDB

In this exercise, you will:

• Take a snapshot of the devdb VDB

• Delete a table

• Rewind the devdb VDB to recover the table

Rewinding a VDB rolls it back to a previous point in its Timeflow and re-provisions the VDB. The VDB will no longer contain changes after the rewind point. it can be triggered when changes to the VDB do not need to be saved.

Steps

1. Take a snapshot of the devdb VDB and note the time

1. Log into the Target via RDP

2. Go to Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio

1. Under Authentication, select "SQL Server Authentication"

2. Log in as sa/delphix

1. Expand “Databases”

2. Right click devdb , then select New Query

3. Run the following commands:

drop table sourcetab; select count(*) from sourcetab;

You will receive an error “Invalid Object name ‘sourctab’. Do not close the query window. Now we will rewind the VDB to the last good snapshot to recover the table.

1. Select the devdb VDB

2. Select the snapshot card associated with the date/time you recorded prior to dropping the table.

3. Rewind the VDB to the snapshot card.

Once the rewind operation is complete, you can confirm the table has been recovered: Go to SQL Server Management Studio on Target SQL server and run the following command. select count(*) from sourcetab; The should receive a count of the table.

Exercise 11 - Set a New Retention Policy

There are four types of Policies in Delphix. In this exercise, you will:

• Create a Retention Policy

• Set the new policy to keep snapshots and logs for 30 days, along with 3 monthly snapshots

• Apply the policy to the VDB we created in the previous exercise

Both dSources and VDBs timeflow is governed by snapshots, which are either created manually or through policies. Retention policies govern the lifespan of such snapshots and help clean older ones that are no longer relevant.

Steps

1. Navigate to Manage -> Policies

2. Create a new retention policy (by going to the “Retention” tab and clicking the “+ Retention” blue button) for devdb with the following details:

1. Name: Long Term

2. 30 days of snapshot and log retention

3. 3 monthly snapshots taken on the 1st of the month

1. To do this click on + Retention and then fill the fields as shown in the screenshot below:

1. Click Next and select devdb on the bottom of the pop up and then click Submit.

Exercise 12 - Create and Save a Hook Operation Template

In this exercise, you will:

• Create a Hook Operation Template called: CREATE APPUSER

• Insert code into the template that will create a Login and database user named appuser

Hook operations allow users to execute custom operations at select points during linking sources and managing virtual datasets.

Steps

1. Create a new Hook Operation Template called: Create APPUSER

2. Click on the Manage menu

3. Click on Hook Templates

4. Click on Plus sign

5. Name - CREATE APPUSER

6. Type - Powershell Script

7. Contents (enter exactly - opening the lab in the lab server and copy/pasting this is highly recommended):

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')
    $sqlserver = "."
    $dbname = $env:VDB_DATABASE_NAME
    $name = "appuser"
    $Server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver
    if (-Not $Server.Logins.Contains($name))
    {
    $Login = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Login -ArgumentList $Server, $name
    $Login.LoginType = 'SqlLogin'
    $Login.Create('delphix')
    }
    $database = $server.Databases["$dbname"]
    $user = new-object ('Microsoft.SqlServer.Management.Smo.User') $database, $name
    $user.Login = $name
    $user.Create()

—|—

IMPORTANT: Make sure the carriage returns you see here are the same in the pasted contents.

1. Click Create

2. Click Close

Exercise 13 - Provision a VDB with Hook Template

In this exercise, you will:

• Create a VDB called qadb

• Use the Hook Operation Template we created previously

• Log into the Target Instance

• Verify the Hook Operation Template was successful

Steps

1. Click the AdventureWorks2008R2 dSource in the MS SQL Databases group

2. Select the most recent snapshot and click the Provision icon

3. Select WINTARGET on the left pane of the wizard and click Next

4. Select the dataset group “MS SQL Databases group”

5. Enter the Database Name: qadb

6. Click Next to accept default policies

7. Click Next to proceed without masking

8. On the Hooks tab add a Configure Clone Hook (1. Click on the Plus sign. 2. Click on Create from Template)

1. On the Hook Operation dialog box, verify that Configure Clone is selected for the Hook Point

2. Enter a name - APPUSER

3. Click Create

1. Verify settings and click Submit

It may take a couple minutes for the VDB creation to complete. You can monitor the progress on the left hand side of the screen next to the qadb object in the MS SQL Databases group. On the Actions pane on the right hand side of the screen, you should see the Provision virtual database qadb item move to the Recently completed pane without error. Once the VDB is created, you can verify that the VDB is operational by:

1. Log into the Target via RDP

2. Go to Start -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio

3. Under Authentication, select “SQL Server Authentication”

4. Log in as appuser/delphix

5. Expand “Databases”

6. Select qadb -> Security -> Users

This will verify that the VDB is online and that the APPUSER user was created by our hook.

Exercise 14 - Recovering a deleted table using log sync

• Create a table

• Delete the table

• Backup transaction log

• Recover the table from transaction log(log sync)

Steps

1. Create a table as follows on source server select * into Logsync_tab from AdventureWorks2008R2.HumanResources.vEmployee; ( Note the time )

2. Wait for few minutes (to give time for the table to be created)

3. Drop the table called Logsync_tab and note the time.

drop table Logsync_tab;

1. Take a transaction log backup (right-click on AdventureWorks2008R2 folder, click Tasks –> Back Up, and select Transaction Log from the dropdown labeled “Backup Type”)

2. Wait until the dSource Timeflow reflects this Transaction log time

3. Create a VDB by selecting date and time from the Timeflow

Choose the transaction log prior to latest log (Latest log is the log that has create and drop table actions).

1. Expand the log by clicking on “Open LogSync” (second icon)

1. Change the date time to the time after the table creation

1. Create VDB by clicking on “provision VDB “(First icon). Name the VDB - RecoverTable

1. After the VDB is created, look for the table on target server.

• Log into the Target via RDP

• Click Start and go to SQL Server Management Studio

• Click Connect to log in as delphix\delphix_trgt

• Expand Databases

• Expand the “ RecoverTable “ virtual database

Exercise 15 - Delink a dSource

In this exercise you will

• Delink a dSource

• Note the changes to Timeline of the dSource caused by the delink operation

• Note the VDB provisioned from the delinked dSource is unaffected.

Steps

1. Login to the Delphix Admin UI as a user with Delphix_Admin privileges.

2. Navigate to Manage  Datasets and click on the dSource “Adventureworks2008R2”

3. Unlink the dSource - Click on the ellipses (…) next to snapshot button and click on “Unlink dSource”

1. Click Unlink on the confirmation box that pops up.

2. Once action completes note the status of the dSource on the right hand side has changed to Detached. You will be unable to take additional snapshots of a detached dSource.

3. Note changes in the display. Click on Status and Configuration tabs and notice the changes.

4. Connect to the target VDB and confirm it is still running.

Exercise 16 - Create a new User with Delphix_Admin privileges

In this exercise you will

• Create new user

• Assign delphix_admin privileges to the new user.

Steps

1. Login to the Delphix UI as a delphix_admin

2. Click on Manage  Users

1. Click on the + sign to add user

2. Create a new Engine Administrator user called DA_User (set in “User Type” dropdown). You can set password as ‘delphix’

3. Log out and re-login to the Delphix UI as DA_User

Exercise 17 - Check the Audit logs for user related actions

In this exercise you will:

• Explore options available to check the audit logs

• Identify the record representing user creation of DA_User user from previous exercise

Steps :

1. Login to the Delphix UI as the admin user.

2. Navigate to SystemAudit

1. Display audit records for the past week. From the Predefined Range drop down pick 1 Week to show Audit logs from the past week.

2. Filter the records for USER. In the filter box on the top right hand side of the Audit page enter User , to filter out only the user action related records.

1. Look through the records displayed to identify logins by the DA_User