SQL server API use cases

SQL server link/ingest environment dSource

For the Window Target environment, the dSource delphixdb in MSSQLSERVER instance will be linked/ingested into the Delphix Engine. It will appear in the Windows_Source group below.

Filename: link_sqlserver.ps1

Copy

PS> . .\link_sqlserver.ps1
Authenticating on http://172.16.160.195/resources/json/delphix
Login Successful ...
group reference: GROUP-34
sourceconfig reference: MSSQL_SINGLE_CONFIG-26
env reference: WINDOWS_ HOST_ENVIRONMENT-7
repository reference: MSSQL_INSTANCE-4
database link API Results: {"type":"OKResult","status":"OK","result":"MSSQL_DB_CONTAINER-114","job":"JOB-819","action":"ACTION-1659"}
DB Container: MSSQL_DB_CONTAINER-114
Job # JOB-819*****    waiting for status    *****
Current status as of 09/05/2016 11:41:13 : COMPLETED : 100.0% Completed
Job COMPLETED Successfully.
JOB JOB-820
waiting for status ***** 
Current status as of 09/05/2016 11:41:23 : RUNNING : 5.0% Completed
Current status as of 09/05/2016 11:41:44 : RUNNING : 9.0% Completed
Current status as of 09/05/2016 11:41:54 : RUNNING : 56.0% Completed
Job COMPLETED Successfully. Done ...

Successful dSource linked/ingested into the Delphix Engine.

SQL server provision

The example below is done from the command line once you know the parameters and reference object names.

Filename: windows_sqlserver_provision.txt

Create these 3 JSON text files:

Copy

session.json{
    "type": "APISession",
    "version": {
        "type": "APIVersion",
        "major": 1,
        "minor": 5,
        "micro": 3
    }
}

login.json{
    "type": "LoginRequest",
    "username": "delphix_admin",
    "password": "delphix"
}

provision.json{
    "type": "MSSqlProvisionParameters",
    "container": {
        "type": "MSSqlDatabaseContainer",
        "name": "Vbitt00",
        "group": "GROUP-36",
        "sourcingPolicy": {
            "type": "SourcingPolicy",
            "loadFromBackup": false,
            "logsyncEnabled": false
        },
        "validatedSyncMode": "TRANSACTION_LOG"
    },
    "source": {
        "type": "MSSqlVirtualSource",
        "operations": {
            "type": "VirtualSourceOperations",
            "configureClone": [],
            "postRefresh": [],
            "postRollback": [],
            "postSnapshot": [],
            "preRefresh": [],
            "preSnapshot": []
        }
    },
    "sourceConfig": {
        "type": "MSSqlSIConfig",
        "linkingEnabled": false,
        "repository": "MSSQL_INSTANCE-1",
        "databaseName": "Vbitt00",
        "recoveryModel": "SIMPLE",
        "instance": {
            "type": "MSSqlInstanceConfig",
            "host": "WINDOWS_HOST-1"
        }
    },
    "timeflowPointParameters": {
        "type": "TimeflowPointSemantic",
        "container": "MSSQL_DB_CONTAINER-23",
        "location": "LATEST_SNAPSHOT"
    }
}

This works on Windows Powershell Command Prompt

Use curl, curl.exe or modify the default alias.

Copy

curl --insecure -c cookies.txt -i -X POST -H "Content-Type: application/json" -d "@session.json" http://172.16.160.153/resources/json/delphix/session

curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@login.json" http://172.16.160.153/resources/json/delphix/login

curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@provision.json" http://172.16.160.153/resources/json/delphix/database/provision

Plug in the returned JOB #

Copy

curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.153/resources/json/delphix/notification?channel=JOB-428

Get Example

Copy

curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.153/resources/json/delphix/system

Complete example.

Provision the newly created delphixdb dSource in the Windows_Source group to a virtual database VBITT in the Windows_Target group.

Filename: provision_sqlserver.ps1

Variables ...

Copy

$nl = [Environment]::NewLine$BaseURL = " http://172.16.160.195/resources/json/delphix "$cookie = "cookies.txt"$delphix_user = "delphix_admin"$delphix_pass = "delphix". . .

Required for Provisioning Virtual Database ...

Copy

$SOURCE_SID="delphixdb"          # dSource name used to get db container reference value
$VDB_NAME="VBITT"                # Delphix VDB Name
$TARGET_GRP="Windows_Target"     # Delphix Engine Group Name
$TARGET_ENV="Window Target"      # Target Environment used to get repository reference value
$TARGET_REP="MSSQLSERVER"        # Target Environment Repository / Instance name
##############################################  NO CHANGES REQUIRED BELOW THIS POINT  ##############################################

Sample Run Output

Copy

PS> . .\provision_sqlserver.ps1
Authenticating on http://172.16.160.195/resources/json/delphix
Login Successful ... 
group reference: GROUP-37
container reference: MSSQL_DB_CONTAINER-114
env reference: WINDOWS_HOST_ENVIRONMENT-7
repository reference: MSSQL_INSTANCE-4
database provision API Results: {"type":"OKResult","status":"OK","result":"MSSQL_DB_CONTAINER-115","job":"JOB-822","action":"ACTION-1664"}
DB Container: MSSQL_DB_CONTAINER-115
Job # JOB-822
jobState RUNNING
percentComplete 0.0
***** waiting for status *****
Current status as of 09/05/2016 11:43:51 : RUNNING : 0.0% Completed
Current status as of 09/05/2016 11:44:01 : RUNNING : 3.0% Completed
Current status as of 09/05/2016 11:44:12 : RUNNING : 11.0% Completed
Current status as of 09/05/2016 11:44:22 : RUNNING : 18.0% Completed
Current status as of 09/05/2016 11:44:32 : RUNNING : 18.0% Completed
Current status as of 09/05/2016 11:44:52 : RUNNING : 75.0% CompletedJob COMPLETED Succesfully. Done ...

SQL server refresh

The following are curl commands that can be issued from the Powershell command line. For inclusion within a Powershell script, see the masking example, masking.ps1.

Filename: windows_sqlserver_refresh.txt

MS SQL Server Refresh Example ...

Session ...

Copy

curl --insecure -c cookies.txt -i -X POST -H "Content-Type: application/json" -d "@session.json" http://172.16.160.179/resources/json/delphix/session

Filename: session.json

Copy

{
  "type": "APISession",
  "version": {
    "type": "APIVersion",
    "major": 1,
    "minor": 5,
    "micro": 3
  }
}
PS> *curl --insecure -c cookies.txt -i -X POST -H "Content-Type: application/json" -d "@session.json" http://172.16.160.179/resources/json/delphix/session*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Set-Cookie: JSESSIONID=8DE0362F5BBD73E6BFA9E13FF111E78C; Path=/resources/; HttpOnly
Content-Type: application/json
Content-Length: 179
Date: Thu, 16 Jun 2016 07:24:34 GMT
{"type":"OKResult","status":"OK","result":{"type":"APISession","version":{"type":"APIVersion","major":1,"minor":5,"micro":3},"locale":null,"client":null},"job":null,"action":null}
PS>

Login ...

curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@login.json" http://172.16.160.179/resources/json/delphix/login

Filename: login.json

{
  "type": "LoginRequest",
  "username": "delphix_admin",
  "password": "delphix"
}  
PS> *curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@login.json" http://172.16.160.179/resources/json/delphix/login*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Content-Length: 76
Date: Thu, 16 Jun 2016 07:25:39 GMT
{"type":"OKResult","status":"OK","result":"USER-2","job":null,"action":null}
PS C:\Users\Administrator>

List Databases ...

Copy

curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.179/resources/json/delphix/database
PS> *curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.179/resources/json/delphix/database*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Content-Length: 4062
Date: Thu, 16 Jun 2016 07:27:14 GMT
{
  "type": "ListResult",
  "status": "OK",
  "result": [
    {
      "type": "MSSqlDatabaseContainer",
      "reference": "MSSQL_DB_CONTAINER-37",
      "namespace": null,
      "name": "Vdelphix_demo",
      "group": "GROUP-35",
      "provisionContainer": "MSSQL_DB_CONTAINER-36",
      "creationTime": "2016-06-16T07:09:06.222Z",
      "currentTimeflow": "MSSQL_TIMEFLOW-38",
      "previousTimeflow": "MSSQL_TIMEFLOW-37",
      "description": null,
      "runtime": ...
    },
    {
      "type": "MSSqlDatabaseContainer",
      "reference": "MSSQL_DB_CONTAINER-36",
      "namespace": null,
      "name": "delphix_demo",
      "group": "GROUP-35",
      "provisionContainer": null,
      "creationTime": "2016-06-16T07:07:49.939Z",
      "currentTimeflow": "MSSQL_TIMEFLOW-36",
      "previousTimeflow": null,
      "description": "",
      "runtime": ...
    }
  ],
  "job": null,
  "action": null,
  "total": 6,
  "overflow": false
}
PS>

Need Reference Object from Database Information ...

For Parent Source Database delphix_demo, reference object is MSSQL_DB_CONTAINER-36

For Virtual Database Vdelphix_demo, reference object is MSSQL_DB_CONTAINER-37

[Optional: Get Database Info ...]

Copy

curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.179/resources/json/delphix/database/MSSQL_DB_CONTAINER-37
PS> *curl --insecure -b cookies.txt -i -X GET -H "Content-Type: application/json" -k http://172.16.160.179/resources/json/delphix/database/MSSQL_DB_CONTAINER-37*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Content-Length: 696
Date: Thu, 16 Jun 2016 07:35:42 GMT
{
  "type": "OKResult",
  "status": "OK",
  "result": {
    "type": "MSSqlDatabaseContainer",
    "reference": "MSSQL_DB_CONTAINER-37",
    "namespace": null,
    "name": "Vdelphix_demo",
    "group": "GROUP-35",
    "provisionContainer": "MSSQL_DB_CONTAINER-36",
    "creationTime": "2016-06-16T07:09:06.222Z",
    "currentTimeflow": "MSSQL_TIMEFLOW-38",
    "previousTimeflow": "MSSQL_TIMEFLOW-37",
    "description": null,
    "runtime": {
      "type": "MSSqlDBContainerRuntime",
      "logSyncActive": false,
      "preProvisioningStatus": null,
      "lastRestoredBackupSetUUID": null
    },
    "os": "Windows",
    "processor": "x86",
    "sourcingPolicy": {
      "type": "SourcingPolicy",
      "logsyncEnabled": false,
      "loadFromBackup": false
    },
    "performanceMode": "DISABLED",
    "delphixManaged": true,
    "masked": false
  },
  "job": null,
  "action": null
}
PS>

Refresh Vdelphix_demo using parent delphix_demo (MSSQL_DB_CONTAINER-36) with the latest timecard ...

Copy

curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@refresh.json" http://172.16.160.179/resources/json/delphix/database/MSSQL_DB_CONTAINER-37/refresh
=== POST /resources/json/delphix/database/MSSQL_DB_CONTAINER-37/refresh ===
refresh.json
{
  "type": "RefreshParameters",
  "timeflowPointParameters": {
    "type": "TimeflowPointSemantic",
    "container": "MSSQL_DB_CONTAINER-36"
  }
}
PS> *curl --insecure -b cookies.txt -i -X POST -H "Content-Type: application/json" -d "@refresh.json" http://172.16.160.179/resources/json/delphix/database/MSSQL_DB_CONTAINER-37/refresh*
HTTP/1.1 200 OK
Server: Apache-Coyote/1.1
Content-Type: application/json
Content-Length: 82
Date: Thu, 16 Jun 2016 07:40:44 GMT
{
  "type": "OKResult",
  "status": "OK",
  "result": "",
  "job": "JOB-60",
  "action": "ACTION-167"
}
PS>

[ Observer Delphix GUI Action ]

Done with SQL Server VDB Refresh ...